[SQL Server] Chapter 11. SQL프로그래밍

* 본 포스팅은 개인의 학습 내용을 정리한 것입니다.

 

1. 읽기 쉬운 코드 작성

 (1) 주석 처리 : SQL서버에서는 다음과 같이 주석 문자열을 지원한다. 주석을 이용하여 설명을 기술할 수 있다.

    1) '--' (이중 하이픈) : 행 단위에 사용하는 주석처리 문자

    2) /* ... */ (슬래시-별표 문자 쌍) : 여는 주석문자(/*)와 닫는 주석문자(*/)사이가 모두 주석처리 됨. 단, 이 안에는 GO 문자열을 추가할 수 없다.

 

 (2) 주요 절을 줄로 분리하고 탭으로 간격을 맞춘다

 (3) 키워드는 대문자로, 또한 개체명의 규칙을 정의하여 사용.

   아래는 자주 쓰이는 약어이다.

명	약어	명	약어
Date	Dt	Process	P
Create	Crt	Time	tm
Name	Nm	Change	chg
Employee	Emp	Number	no
Brend	Brd	identify	id
Detail	D	sequence	seq
Select	S	insert	i
Update	U	delete	d

 (4) 기억하기 쉬운 별칭을 사용하고, 우선순위는 명시적으로 표시하자

 (5) 문자열 안에 작은따옴표는 ''를 이용하여 표시

 

2. 세션과 일괄처리에 대한 이해

 (1) 세션 : 클라이언트에서 네트워크를 통해 SQL Server와 연결되어 쿼리를 주고 응답을 받을 수 있는 채널을 의미

 (2) 컨텍스트 : 세션에서 배치가 처리될 때의 해당 배치에서 사용하는 여러 정보를 관리하는 영역

 (3) 일괄처리 : 한번에 실행되는 T-SQL문의 집합. GO문이 포함될 경우 GO문을 기준으로 일괄처리 단위를 나눔. 그러나 이 GO문이 트랜잭션을 보장하지는 않는다.

   1) 일괄처리의 규칙

    - CREATE문은 반드시 일괄처리를 시작해야 한다. 다른 구문과 결합 안됨

    - 동일한 일괄 처리에서 테이블을 변경한 후, 새 열을 참조할 수 없다.

    - 일괄처리의 첫 문이 EXECUTE문이면 EXECUTE키워드는 필요 없지만, 아닐 경우 필요하다.

 (4) GO : 일괄처리를 구분하는 구분자. T-SQL문이 아님.

 (5) 컴파일 오류와 런타임 오류

    1) 구문오류와 같은 컴파일 오류가 발생시, 일괄처리의 모든 문이 실행되지 않는다.

    2) 런타임 오류 (제약조건 위반, 오버플로우등)가 발생시 다음과 같은 결과가 발생

      - 개체 참조 오류와 같은 일부 런타임 오류는 발생한 문장 다음의 구문을 중지한다.

      - 제약조건과 같은 런타임 오류는 해당 문만 중지되고, 나머지 문은 모두 실행됨.

 

3. 변수 선언과 사용

 (1) 변수 : 값을 전달하거나 값을 대입할 때 사용. 일반적으로 '@'로 시작한다. '@@'의 경우 해당 키워드로 시작하는 내장 함수가 있으므로, '@'를 이용하여 시작하는 것이 좋다.

 

 (2) 변수의 선언과 초기화

   - 변수의 선언 : DECLARE 키워드

   - 변수의 초기화 : 기본은 NULL로 초기화 된다. SELECT, SET, UPDATE구문의 결과, INTO등을 대입하여 값 할당 가능

      변수에 쿼리의 결과를 대입할 수 있는데, 여러행이 결과로 나온 경우, 마지막 행의 결과가 변수에 대입된다.

 

 (3) UPDATE문으로 갱신한 값을 변수에 초기화 하기

   - UPDATE ~ SET @변수 = 컬럼 = ... 형태로 변수에 값을 대입할 수 있다.

 

 (4) 테이블 변수 사용

   - 테이블 변수는 결과 집합을 저장하는 특수 데이터 형식. table변수는 임시테이블과 유사하다. 하지만, 임시테이블의 경우 세션이 종료되거나 DROP으로 제거하여야 하지만, table변수는 해당 변수가 정의된 함수, 프로시저, 배치작업이 종료되면 자동으로 정리된다.

   - 테이블 변수에서 허용되는 제약조건의 유형 : PRIMARY KEY, UNIQUE KEY, CHECK, DEFAULT

 

 (5) 지역 변수의 사용 범위 : 선언된 일괄처리 안에서만 사용 가능함.

 

 (6) 자주 사용되는 함수(전역변수)

   1) @@ROWCOUNT : 마지막 명령문의 영향을 받은 행 수를 반환. 특정 작업의 영향을 받은 행 수를 여러번 적용하고자 할 때에는 별도의 변수를 만들어 @@ROWCOUNT값을 저장한 후, 재사용 할 것

   2) @@ERROR : 마지막으로 실행된 T-SQL문의 오류 번호를 반환

   3) @@IDENTITY: IDENTITY속성으로 설정된 테이블에 자료가 삽입 되었을 때, 마지막 증가값을 반환

 

 (7) 임시테이블, 임시저장 프로시저의 선언과 사용

   임시 테이블의 경우 로컬(#로 시작)과 글로벌(##로 시작)로 구분된다. 로컬의 경우 세션에서만 사용 가능하고, 글로벌은 다른 세션에서도 사용 가능하다.

 

4. 변수와 임시 테이블의 인식 가능 영역

 현재 영역과 하위 영역. 현재 영역은 일괄처리를 하는 영역이고, 하위 영역은 일괄처리에서 호출되어 수행되는 영역을 의미한다. 아래 구문을 보자

DECLARE @id AS int  EXEC ( 'SELECT @id = 3')

 위 구문을 실행하면 @id는 정의되지 않았다는 에러 (메시지 137)이 반환된다. 현재 영역에서 선언된 변수는 하위 영역에서 사용할 수 없다.

 이번에는 아래 구문을 보자.

CREATE TABLE #Emps( id int PRIMARY KEY, name nchar(5))  EXEC( 'INSERT INTO #Emps VALUES(1,''우철웅'')')  SELECT * FROM #Emps

 위 구문은 동작한다. 그러나 CREATE TABLE을 하는 부분을 EXEC로 호출하면 208에러 (유효하지 않은 개체명)이라고 에러를 반환한다. 하위 영역에서 생성한 임시테이블은 현재 영역에서 접근할 수 없다. 반대로 현재 영역에서 생성한 임시테이블은 하위 영역에서 사용 가능하다.

 그러나 하위 영역에서라 하여도, 전역 임시 테이블 (##로 시작)의 경우 현재 영역에서 접근이 가능하다.

 

5. 흐름 제어 문

 (1) IF...ELSE  

IF boolean_expression     { 참일때 실행할 구문(또는 블럭) }  [ ELSE     { 참이 아닐때 실행할 구문(또는 블럭) }]

   위 구문에서 주의해야 할 것은, IF나 ELSE의 경우 바로 다음 구문만 IF또는 ELSE에 속한 구문으로 인식한다.

 

IF 조건문    PRINT '0. 자료가 없습니다.'    PRINT '1. 확인해 주세요'  ELSE    PRINT '2. 자료가 있습니다'

  위의 예에서 PRINT '1...'은 IF문에 속한 구문이 아니라 IF문 밖에 있는 구문으로 인식된다. 만약 위 구문을 블럭을 지정하고 싶다면, BEGIN...END를 사용해야 한다. 

IF 조건문  BEGIN    PRINT '0. 자료가 없습니다.'    PRINT '1. 확인해 주세요'  END  ELSE    PRINT '2. 자료가 있습니다'

  위에 처리한 것 처럼 BEGIN/END로 묶어주어야 하나의 블럭으로 인식한다. (IF문 안의 구문으로 인식)

 

 (2) BEGIN...END

  위의 예처럼 블럭을 지정하여 줄 때 사용한다. C에서 {와 }를 이용하여 블럭을 지정하는 것을 생각하자

 

 (3) WHILE, BREAK, CONTINUE 

WHILE boolean_expression  { sql구문}   [BREAK]   {sql구문}   [CONTINUE]

  WHILE 구문은 boolean_expression이 참(true)인 경우에만 수행한다. 만약 특정 조건에서 루프를 중단하고 싶으면 BREAK를 이용하여 루프를 빠져 나가고, 루프의 처음으로 돌아가서 다시 수행하려면 CONTINUE를 이용하면 된다. WHILE문에는 BEGIN,END를 이용하여 블럭 지정을 해주는 것이 좋다. (C에서 while, break, continue를 생각하자)

 

 (4) GOTO문 

레이블명:   ...   GOTO 레이블명

  위와 같이 사용한다. 이 역시 c에서 사용하는 goto문의 문법과 같다

 

 (5) WAITFOR

WAITFOR DELAY 'hh:mm:ss'

   지정한 시간만큼 대기한다. 일 단위로 지정은 안된다. 최대 24시간까지 대기 가능

 

 (6) RETURN

   현재 실행중인 작업을 종료한다. RETURN이후의 구문은 수행되지 않는다. 지정된 값이 없으면 0을 반환한다

 

6. 세션 환경 설정

 (1) 세션 환경의 종류는 다음과 같이 세 단계에서 관리할 수 있다.

   - 서버 전체 영역

   - 데이터베이스 영역

   - 세션 영역

 (2) 서버구성옵션에서 전체가 적용될 세션 환경 설정

   - sp_configure를 이용하여 관리할 수 있다.

     자세한 내용은 링크 참조(http://msdn.microsoft.com/ko-kr/library/ms188787.aspx)

 (3) 데이터베이스 옵션에서 적용될 세션 환경 설정

   -  sp_dboption을 사용하여 설정할 수 있다.

     자세한 내용은 링크 참조 (http://technet.microsoft.com/ko-kr/library/ms187310(v=sql.105).aspx)

 

7. SET옵션

 (1) 날짜및 시간옵션

   1) DATEFIRST : 일주일의 첫 번째 요일을 1(일)~7(토)사이의 숫자로 설정

   2) DATEFORMAT : 날짜 입력의 순서를 정함. mdy,dmy, ymd,ydm,myd,dym등이 있음

 (2) 잠금옵션

   1) DEADLOCK_PRIORITY : 교착상태에서 세션의 우선순위를 설정

   2) LOCK_TIMEOUT : 잠금 해제시까지 대기해야할 시간. 단위는 초

 (3) 기타옵션

   1) CONCAT_YIELDS_NULL : OFF일 경우 NULL과 문자열 연결에서 값이있는 문자열로 반환 (아닐경우 NULL)

   2) CURSOR_CLOSE_ON_COMMIT : ON일경우 트랜잭션 커밋 시 세션이 자동으로 닫힘

   3) FIPS_FLAGGER : FIPS127-2와의 호환성 확인을 지정

   4) IDENTITY_INSERT : ON일경우 IDENTITY속성 컬럼에 값을 지정할 수 있다.

   5) LANGUAGE : 언어 환경을 지정하여 datetime형식과 시스템 메시지 언어를 변경할 수 있다.

   6) DISABLE_DEF_CNST_CHK : 중간지연위배 확인을 지정. 이전버전과의 호환성 용으로 포함됨

   7) OFFSETS : T-SQL에서 지정한 키워드의 오프셋을 반환(DB라이브러리 응용프로그램에서만 사용)

   8) QUOTED_IDENTIFIER : 인용부호 구분 식별자 및 리터럴 문자열에 대해 ISO를 따르도록 변경

    (http://msdn.microsoft.com/ko-kr/library/ms174393.aspx)

 

 (4) 쿼리 실행

   1) ARITHABORT : ON일경우 오버플로우나 0으로 나누기 오류 발생 시 쿼리가 일괄종료됨

   2) ARITHIGNORE : 오류 메시지의 반환 여부만 제어

   3) FMTONLY : 조회시 컬럼명만 반환한다.

   4) NOCOUNT : 수행한 쿼리로 적용된 행수를 결과에 반환되지 않도록 할지(ON) 결정

   5) NOEXEC : ON일경우 쿼리 실행 없이 컴파일만 수행. 구문 오류 확인할 수 있다.

   6) NUMERIC_ROUNDABORT : 식의 반올림에서 정밀도가 손상될 경우 오류발생(ON) 여부

   7) PARSEONLY : ON일 경우 구문 파싱만 수행한다. 컴파일과 수행은 하지 않음

   8) QUERY_GOVERNOR_COST_LIMIT : 쿼리를 실행할 수 있는 시간의 상한값을 지정. 수행하는 최대 시간(초)

   9) ROWCOUNT : 지정된 행 수만 처리하도록 지정. TOP의 경우 SELECT에만 적용되지만, 이 설정은 모든 구문에 적용

   10) TEXTSIZE : SELECT문을 실행해 반환된 text및 ntext의 크기를 지정 (최대 2G, 0으로 설정하면 기본값인 4KB)

 

 (5) SQL-92 설정

   1) ANSI_DEFAULTS : ANSI_NULLS, ANSI_NULL_DFLT_ON, ANSI_PADDING, ANSI_WARNINGS, CURSOR_CLOSE_ON_COMMIT, IMPLICIT_TRANSACTIONS, QUOTED_IDENTIFIER를 설정

   2) ANSI_NULL_DFLT_OFF : ON일때, 생성되는 컬럼에 null허용이 명시되지 않은 경우 기본으로 NOT NULL로 설정.

   3) ANSI_NULL_DFLT_ON : ANSI_NULL_DFLT_OFF와 반대. 둘 다 ON이 될수는 없다.

   4) ANSI_NULLS : NULL값과 =, <> 비교시 UNKNOWN(ON)이 된다. 아닐 경우 NULL비교가 가능하다. NULL비교에 있어서는 해당 설정과 무관하게 IS NULL, IS NOT NULL을 사용하는 것이 좋다.

   5) ANSI_PADDING : 문자열 저장 시, 후행 공백의 값을 저장한다(ON), 후행 공백 저장 여부에 영향을 받는 행은 varchar, nvarchar에 대해서 적용된다. 해당 설정 값이 ON인 경우 문자열 뒤의 공백을 저장하고, 아닐 경우 저장하지 않는다. char, nchar의 경우 해당 설정 값과는 무관하게 공백이 저장된다.

   6) ANSI_WARNINGS : SQL-92 표준 동작을 지정한다. ON일 경우 다음과 같이 동작한다.

    - 집계함수에 NULL값이 있으면 경고 메시지가 발생한다.

    - 0으로 나누기, 산술 오버플로우 발생 시, 명령문이 롤백되고 오류 메시지가 생성된다. OFF일경우는 NULL반환

    - 문자열의 길이가 컬럼 크기를 초과하는 경우에 오버플로우 오류로 작업에 실패

 

 (6) 트랜잭션

   1) IMPLICIT_TRANSACTIONS : 암시적 트랜잭션 설정. http://gomnezip.tistory.com/351 에 언급되어있음

   2) REMOTE_PROC_TRANSACTIONS : 로컬 트랜잭션 시작 시, 외부 저장 프로시저를 호출하면 T-SQL의 분산 트랜잭션을 시작한다. 이 설정 사용 대신 분산쿼리를 사용할 것. 이 옵션은 추후 제거될 예정.

 

 (7) 현재 세션에 설정된 옵션 보기

   1) DBCC USEROPTIONS문을 수행하거나 @@OPTIONS

 

9. PRINT사용

  - 문자열 메시지로 반환하는 구문. 최대 8000자까지 가능하다.

PRINT <출력할 문자열>

 

 

-출처 : [SQL Server advanced programming]