오래된 개발자!!!

DB복원 시 문제로 DB 가 단일사용자모드 라 뜨면서 엑세스가 안될때는

alter database [DB NAME] set multi_user;

당황하지 안코 명령만 실행하면 끗....

가상상황 )

MEMBER 테이블의 NAME 을 MEMBER_ETC 의 테이블에 넣어야 한다.

먼저 Join 문을 작성 한다.

select
a.NAME, b.NAME
from MEMBER_ETC a left join MEMBER b on ( a.ID = b.ID )
where a.TYPE ='1'

이제 위의 Join 문을 활용하여 Update 문을 작성한다.

( from 이후 는 같다 ... )

update MEMBER_ETC set

NAME = b.NAME

from MEMBER_ETC a left join MEMBER b on ( a.ID = b.ID )
where a.TYPE ='1'

20140101 을 2014.01.01 이나 2014-01-01 로 표현하고자 한다면

format 을 사용할 수가 있다.

사용법

select format(20140101, '####\.##\.##')

=> 2014.01.01

select format(convert(int,'20140101'), '####\.##\.##')

=> 2014.01.01

select format(convert(int,'20140101'), '####-##-##')

=> 2014-01-01

프로시져 기초

CREATE PROCEDURE [dbo].[SP_TEST] ( @AAA char(4), @BBB varchar(20) )
AS
BEGIN 

  DECLARE @CCC varchar(2000)
  SELECT @CCC = ''

END

회원 테이블의 목록과 전체 회원수를 동시에 조회 하여 뒤에서 사용하고 자 할 때....

select
  NAME
, count(*) over() as TOT_CNT
from MEMBER

결론,

count(*) over() 로  group by 없이 사용할 수 있음....

MS-SQL 에서

랜덤 하게 5개만 뽑아 올려면

select top (5) *
from TABLE_NAME
order by newid();

ms sql 에서 lpad 구현 꼼수

1. right 를 이용한 간단 구현

1 => 0001  형태의 4자리로 바꿔 준다.

select convert(varchar(255), right(N'0000'+rtrim('1'),4)  ) AS NO

2. replicate 를 이용한 복잡한 구현

00001234 형태의 8자리

1) 오라클 용
LPAD(TRIM(TO_CHAR(NO,'99999999')), 8, '0') NO

2) MS SQL 용
REPLICATE('0', 8 - DATALENGTH(CONVERT(VARCHAR(4), NO ))) + CONVERT(VARCHAR(4), NO ) NO

ORACLE

select '0' CNT from dual

MS SQL 에서는

select '0' CNT

로 할 수 있다.

그러나, 꼭!!!!! from dual 형태로 구현을 해야 한다면

select '0' CNT from ( select '1' as dual) a

로 해결하면 된다!!!!!!

YYYY-mm-dd 포맷으로 가져올때

SELECT CONVERT(CHAR, GETDATE(), 23)

0.    09 30 2013  3:00PM
1.    09/30/10
2.    10.09.30
3.    30/09/10
4.    30.09.10
5.    30-09-10
6.    30 09 10
7.    09 30, 10
8.    17:35:47
9.    09 30 2013  5:36:16:863PM
10.   09-30-10
11.   13/09/30
12.   130930
30.   30 09 2013 11:20:57:550
14.   11:21:11:317  
20.   2013-09-30 11:21:40 
21.   2013-09-30 11:21:48.397 
22.   09/30/10 11:21:57 AM 
23.   2013-09-30  
24.   11:22:20   
25.   2013-09-30 11:22:28.240 
100.    09 30 2013 11:22AM 
101.    09/30/2013  
102.    2013.09.30  
103.    30/09/2013  
104.    30.09.2013  
105.    30-09-2013  
106.    30 09 2013  
107.    09 30, 2013  
108.    11:23:46  
109.    09 30 2013 11:23:55:880AM
110.    09-30-2013  
112.    2013/09/30  
112.    20130930  
130.    30 09 2013 11:24:33:270 
114.    11:24:40:677  
120.    2013-09-30 11:24:50 
121.    2013-09-30 11:24:57.880 
126.    2013-09-30T11:25:05.193 

잠금 요청 제한....

deadlock 이란다...  교착상태

http://msdn.microsoft.com/ko-kr/library/ms177433(v=sql.100).aspx

이런게 있구나... ㅠ.ㅠ

MS SQL 에서 row number 를 사용하기 위해서는 아래와 같이

select
   ROW_NUMBER() OVER( ORDER BY COL_NAME ) RNUM
 , COL_NAME2
from TBL_NAME
ORDER BY COL_NAME

ROW_NUMBER 에 선언한 order by 와 쿼리 order by 를 다르게 하면 row number 가 꼬임

create function [dbo].[function_name] ( @param char(2) )
returns INT
AS
BEGIN
declare @returnVal;

return @returnVal;
END

-----------------------------------------------------------------
수정할때는 alter
-----------------------------------------------------------------

아래는 요일을 숫자로 바꿔주는 함수... 월 을 1로 리턴해준다.

Create FUNCTION [dbo].[convYoiltoNum] (@pYoil varchar(2))
RETURNS INT
AS
BEGIN
 declare @nYoil int;

 if @pYoil = '월' set @nYoil = 1;
 else if @pYoil = '화' set @nYoil = 2;
 else if @pYoil = '수' set @nYoil = 3;
 else if @pYoil = '목' set @nYoil = 4;
 else if @pYoil = '금' set @nYoil = 5;
 else if @pYoil = '토' set @nYoil = 6;
 else if @pYoil = '일' set @nYoil = 7;
    
 return @nYoil;
END

-----------------------------------------------------------------
함수 호출 방법

select dbo.convYoiltoNum('월')

결과 1

[SQLServer 2000 Driver for JDBC] Underlying input stream returned zero bytes

JDBC 처리 시 DB 컬럼 타입이 TEXT 이고, 리턴되는 값이 0 byte 일 경우 오류 이다.

BIGO 컬럼 이 text 라면  0byte 를 ' ' 로 바꿔야 된다.

select

CASE DATALENGTH(BIGO)
                 WHEN 0 THEN ' '
                 ELSE BIGO
          END as BIGO

from TBL_NAME

select 를 날렸더니

잠금 요청 제한 시간이 초과되었습니다.

 라고 나온다. 이럴때 해결은

EXEC SP_LOCK 

실행하면 SPID 번호는 같고 여러개인 것이 나온다.

DBCC INPUTBUFFER( SPID )

찾은 SPID 로 명령 실행 하면 EventInfo 에 뭔가가 나온다... ㅠ.ㅠ

KILL SPID

뭐가 뭔지 모르겠지만 SPID 를 KILL 해버리면 된다.

SPID 를 잘 못 넣어 엉뚱한걸 KILL 해 버리면 곤란! ^^

오라클 SQL 문

select *
from TEST.MENU A
          START WITH A.UP_MENU_NO    = '1'
          CONNECT BY PRIOR A.MENU_NO = A.UP_MENU_NO 
          ORDER SIBLINGS BY A.SORT_NO

=========================================================================>

위의 오라클 sql 문을 ms-sql 문으로 변환

MS-SQL  문

;WITH CTE (UP_MENU_NO, MENU_NO,  MENU_LEVEL , MENU_NAME, SORT ) AS (

  SELECT 
       UP_MENU_NO AS UP_MENU_NO
       , '0' AS MENU_NO
       , MENU_LEVEL
       , MENU_NAME 
       , convert(varchar(255), right(N'00'+rtrim(SORT_NO),3)  ) AS SORT        //  1을 001 형태의 3자리 로 바꾸준다.
    FROM  MENU

    WHERE   UP_MENU_NO = '1' 
    UNION ALL 
    SELECT
         B.UP_MENU_NO AS UP_MENU_NO
         , B.MENU_NO AS MENU_NO
        , C.MENU_LEVEL + 1 AS MENU_LEVEL
        , B.MENU_NAME
        , convert(varchar(255), convert(nvarchar,C.SORT) + '|' +  right(N'00'+rtrim(B.SORT_NO),3)  ) AS SORT
    FROM    MENU B
    INNER JOIN CTE C ON B.UP_MENU_NO = C.MENU_NO
)
SELECT * FROM CTE order by SORT