Transact-SQL

1. Transact-SQL SQL과 Transact-SQL을 비교하여 이해한다. DDL, DCL, DML로 구분된 명령문을 이해한다. Transact-SQL의 기본 구문 요소와 기타 구문 요소를 이해한다. 프로그램을 작성하기 위한 흐름 제어 요소를 이해한다. Transact-SQL을 이용해 데이터베이스 프로그램을 작성할 수 있는 기본 지식을 갖춘다.

2. 1. SQL과 Transact-SQL • 2. Transact-SQL 기본 구문 요소 • 3.흐름 제어 • 4. Transact-SQL 기타 구문 요소*

3. SQL과 Transact-SQL >> • SQL과 Transact-SQL의 비교 • ■ SQL과 Transact-SQL의 차이점 • ● SQL: 1992년에 ISO에서 표준화된 ANSI SQL-92를 가리킴 • ● Transact-SQL: SQL의 도입 수준을 주로 구현하고, 그 밖에 SQL의 중간 수준 및 완전 수준의 일부, 그리고 마이크로소프트의 독자적인 확장을 구현한 언어

4. SQL과 Transact-SQL >> • SQL과 Transact-SQL의 비교 • ■ SQL과 Transact-SQL의 공통점 • ● SQL과 Transact-SQL을 명확히 구분하지 않음(오라클에서는 SQL과 PL/SQL이 명확히 구분되어, 명령어 형식과 이들을 처리하는 시스템도 서로 다르지만, SQL 서버에서는 SQL과 Transact-SQL이 거의 같이 취급됨) • ● SQL 서버에서만 작업하는 경우에는 SQL과 Transact-SQL을 굳이 구분할 필요가 없음 • ● 다른 DBMS로 이전할 가능성이 있는 경우에는 가능하면 SQL 위주로 스크립트를 작성하는 것이 좋으며, Transact-SQL에 포함된 SQL을 구분해 낼 수 있어야 함

5. SQL과 Transact-SQL >>데이터 정의 언어(DDL) 문 • ● Transact-SQL을 기능에 따라 구분 • ▸ 데이터 정의 언어(DDL) • ▸ 데이터 제어 언어(DCL) • ▸ 데이터 조작 언어(DML) • ● DDL 문은 데이터베이스의 구조를 생성, 변경 또는 제거하는 명령문 • ● 구문: • CREATE object_kind object_name options • ALTER object_kind object_name options • DROP object_kind object_name [ , ...n ]

6. SQL과 Transact-SQL >>데이터 제어 언어(DCL) 문 • ● 데이터 제어 언어(DCL, Data Control Language) 문은 데이터베이스의 특정 개체에 대한 액세스나 특정 명령문의 실행을 제어하는 명령문 • ● 개체 액세스 제어: 특정 테이블을 조회할 수 있는 권한을 특정 사용자에게 부여하거나 제거하는 것 • ● 명령문 실행 제어: CREATE DATABASE 등의 명령문을 실행할 수 있는 권한을 특정 사용자에게 부여하거나 제거하는 것 • ● DDL 문은 데이터베이스의 구조를 생성, 변경 또는 제거하는 명령문 • ● 구문: • GRANT options • DENY options • REVOKE options

7. SQL과 Transact-SQL >>데이터 조작 언어(DML) 문 • ● 데이터 조작 언어(DML, Data Manipulation Language) 문은 데이터를 추가, 갱신 또는 삭제하는 명령문 • ● 대표적인 DML 문에는 SELECT, INSERT, UPDATE, DELETE가 있음 • ● 명령문 실행 제어: CREATE DATABASE 등의 명령문을 실행할 수 있는 권한을 특정 사용자에게 부여하거나 제거하는 것

8. Transact-SQL 기본 구문 요소 >>식별자 • ● 식별자: 데이터베이스 내의 각종 개체의 이름 • ● 개체: 데이터베이스, 테이블, 열, 저장 프로시저 등 • ●표준 식별자와 구분 식별자로 구분됨 • ■ 표준(Standard) 식별자 • ● 표준적인 식별자

9. Transact-SQL 기본 구문 요소 >>식별자 • ● 식별자 생성 규칙 • ▸ 1～128자의 글자로 구성됨 • ▸ 공백은 포함할 수 없으며, 예약어만으로 식별자를 만들 수 없음 • ▸ 첫째 글자는 유니 코드 표준 2.0에서 정의된 문자여야 함 • ▸ 첫째(및 둘째) 글자가 다음과 같은 식별자는 특별한 용도로 사용됨 • - '@'로 시작되는 식별자는 지역 변수(local variable) 또는 인자(parameter)를 나타냄 • - '#'으로 시작되는 식별자는 지역 임시 테이블 또는 프로시저를 나타냄 • - '##'으로 시작되는 식별자는 전역 임시 테이블 또는 프로시저를 나타냄 • - '#' 또는 '##'으로 시작되는 임시 개체의 이름은 '#' 또는 '##'을 포함하여 29자를 넘지 않도록 해야 함(내부적으로 이 식별자에 숫자 접미사가 붙기 때문) • ▸ 첫째 이후의 글자로는 유니 코드 표준 2.0에서 정의된 문자, 기호(_, @, # 또는 $) 또는 숫자를 사용할 수 있음

10. Transact-SQL 기본 구문 요소 >>식별자 • ● 지역 임시 개체와 전역 임시 개체 • ▸ 공통점: 이름이 '#'으로 시작되는 지역 임시 개체와 '##'으로 시작되는 전역 임시 개체는 항상 tempdb에 만들어짐 • ▸ 차이점: 지역 임시 개체는 이것을 만든 사용자만 액세스할 수 있고, 이 사용자가 연결을 끊을 때 자동으로 삭제됨. 전역 임시 개체는 모든 사용자가 액세스할 수 있고, 마지막 사용자가 연결을 끊을 때 자동으로 삭제됨 • ■ 구분(Delimited) 식별자 • ● 표준 식별자의 규칙에 어긋나는 특별한 식별자를 사용하고자 할 때, 식별자를 대괄호([ ]) 또는 큰따옴표(“”)로 둘러싸서 만듦 • ● [예 1] 식별자의 첫째 글자가 숫자이고 중간에 공백이 있으므로, [ ]로 둘러싼 구분 식별자로 만들어야 한다. • CREATE DATABASE [1회용 DB] • DROP DATABASE [1회용 DB]

11. Transact-SQL 기본 구문 요소 >>식별자 ●[예 2] SET QUOTED_IDENTIFIER ON 옵션 설정이 되지 않아서 오류가 발생함 CREATE DATABASE "1회용 DB" ● [예 3] SET QUOTED_IDENTIFIER ON 옵션 설정을 했기 때문에 정상적으로 처리됨 SET QUOTED_IDENTIFIER ON CREATE DATABASE "1회용 DB" DROP DATABASE "1회용 DB" SET QUOTED_IDENTIFIER OFF ● 구분 식별자는 사용할 때마다 매번 [ ] 또는 “”로 둘러싸 주어야 하므로 번거롭고 오류가 발생할 가능성도 커지므로, 특별한 이유가 없다면 사용하지 말 것을 권장함(표준 식별자를 사용할 것)

12. Transact-SQL 기본 구문 요소 >>예약어 • ● 예약어(reserved word): Transact-SQL에서 특별한 목적으로 사용하기 위해 예약해 놓은 키워드로서, 식별자 등에 이것을 사용하면 오류가 발생함 • ●Transact-SQL의 예약어의 종류 • 책 [표 6-1] 참고 • ●[예 1] 예약어 ADD를 데이터베이스 이름으로 사용할 경우: 오류가 발생함 • CREATE DATABASE ADD • ●[예 2] 미래에 사용할 키워드 ABSOLUTE를 데이터베이스 이름으로 사용할 경우: 오류는 발생하지 않으나, 권장하지 않음 • CREATE DATABASE ABSOLUTE • DROP DATABASE ABSOLUTE • ●쿼리 분석기에서 키워드가 파란색으로 표시됨→식별자로 사용하지 말라는 경고 신호등

13. Transact-SQL 기본 구문 요소 >>변수 • ● (지역) 변수(variable): ‘@’로 시작되는 식별자로, 특정 데이터 형의 값을 일시적으로 보관하는 용도로 사용함 • ● 반드시 선언을 해야 하고, 사용하기 전에 값을 할당해야 함 • ● 구문: • DECLARE @variable_name data_type [, ...n] • SET @variable_name = expression • ▸ DECLARE 문으로 하나 이상의 변수를 선언함 • ▸ data_type은 데이터 형임(“8장 1절 데이터 형”에서 자세히 배움) • ▸ SET 문으로 변수에 값을 할당함

14. Transact-SQL 기본 구문 요소 >>연산자 • ■ 산술 연산자 • ● +, -, *, /, % • ● %: 모듈로(modulo) 연산자로, 나머지 계산을 함 • ● [예 1] 모듈로 연산: PRINT 문은 연산식의 결과를 (결과 집합이 아닌) 메시지로 돌려줌 • PRINT 5 % 3 • ■ 할당 연산자 • ●= • ● SET 문에서 변수에 값을 할당할 때나 SELECT 문에서 열 머리글을 정의할 때 사용

15. Transact-SQL 기본 구문 요소 >>연산자 • ■ 비트 단위 연산자 • ● &(비트 단위 AND), |(비트 단위 OR), ^(비트 단위 XOR) • ● [예 1] &(비트 단위 AND) 연산 • PRINT 1 & 0 • ● [예 2] |(비트 단위 OR) 연산 • PRINT 1 | 0 • ● [예 3] ^(비트 단위 XOR) 연산 • PRINT 1 ^ 1 • ■ 비교 연산자 • ●=(같다), >(크다), <(작다), >=(크거나 같다), <=(작거나 같다), <>(같지 않다) • ● SET 문에서 변수에 값을 할당할 때나 SELECT 문에서 열 머리글을 정의할 때 사용

16. Transact-SQL 기본 구문 요소 >>연산자 • ■ 논리 연산자 • ● NOT, AND, OR • ■ 문자열 연결 연산자 • ●+ • ● 주의: 문자열을 연결할 때 숫자 데이터 형은 반드시 문자 데이터 형으로 변환한 후 연결에 사용해야 한다. • ■ 단항 연산자 • ● +(양수), -(음수), ~(비트 단위 NOT; 1의 보수) • ● +, -는 숫자 형의 식에, ~는 정수 형의 식에만 적용할 수 있음

17. 우선순위 종류 연산자 1 그룹화 ( ) 2 단항 +, -, ~ 3 산술 곱셈 *, /, % 4 문자열 연결, 산술 덧셈 +(문자열 연결), +, - 5 비교 =, >, <, >=, <=, < > 6 비트 단위 ^, &, | 7 논리 NOT NOT 8 논리 AND AND 9 논리 OR, 기타 OR, BETWEEN, IN, LIKE, ALL, ANY, SOME 10 할당 = • Transact-SQL 기본 구문 요소 >>연산자 • ■ 연산자 우선순위 ● 같은 우선순위를 가진 연산자들이 섞여 있을 때 연산 순서는 왼쪽부터 오른쪽 순임 ● 가장 편리하고 확실한 연산 순서 결정은 ( )를 사용하는 것

18. Transact-SQL 기본 구문 요소 >>코멘트 • ● 실행되지 않는 문자열로서, 다른 사람이나 본인이 나중에 소스를 봤을 때 쉽게 이해할 수 있도록 하기 위해 소스 코드 중에 삽입함 • ● 코멘트에는 인라인 코멘트와 블록 코멘트가 있다. • ■ 문자열 연결 연산자 • ● “--”로 시작하여 그 줄 끝까지 이어지는 코멘트 • ● 한 줄로 된 코멘트를 만들거나, 명령문 뒤에 이어지는 코멘트를 만들 때, 그리고 명령문을 비활성화할 때 사용함 • ●[예 1] 명령문 뒤에 이어지는 코멘트를 만들 때: “--”에 이어 “>”등을 추가하면 코멘트가 더 눈에 잘 띔. 특별한 경우에는(예: 일부 GO 명령어) 인라인 코멘트가 명령어와 같은 줄에 오면 오류가 발생하는 버그가 있으며, 이 경우에는 인라인 코멘트를 다른 줄로 옮겨야 함. • CREATE DATABASE ADD --> 오류 발생함

19. 데이터베이스 조회 명령문>>행 제약 조건 Transact-SQL 기본 구문 요소 >>코멘트 ● [예 2] 명령문을 비활성화할 때: 네 개의 명령문들의 실행을 막기 위해 비활성화함. 쿼리 분석기에서는 네 줄을 선택한 후, Ctrl + Shift + C 키를 누르면 네 줄을 한꺼번에 인라인 코멘트로 만들 수 있음(코멘트를 해제할 때는 네 줄을 선택한 후, Ctrl + Shift + R). -- SET QUOTED_IDENTIFIER ON -- CREATE DATABASE "1회용 DB" -- DROP DATABASE "1회용 DB" -- SET QUOTED_IDENTIFIER OFF

20. Transact-SQL 기본 구문 요소 >>코멘트 ■ 블록 코멘트 ● “/*”로 시작하고 “*/”로 끝나는 코멘트 ● 여러 줄로 된 코멘트를 만들거나, 여러 개의 명령문들을 한꺼번에 비활성화할 때 사용함 ●[예 1] 블록 코멘트를 사용한 스크립트 파일 설명: 각 줄 앞의 “**”는 판독성을 높이기 위한 것인데(코멘트와는 무관함), 코멘트 내에서 줄 바꿈이 자주 발생할 경우에는 생략하는 것이 좋음 /* [filename.sql] ** 제목: ** 설명: ** ** 버전: ** 개발 기간: ** 개발자: */

21. 흐름 제어 ■ BEGIN...END 블록 ● 하나 이상의 명령문들이 한꺼번에 실행되도록 묶어줌 ● C 계열 언어(C, C++, 자바, C#)의 { } 블록과 같다고 생각하면 된다. ■ IF...ELSE 블록 ● 조건에 따라 분기할 때 사용 ● BEGIN...END 블록을 같이 사용하는 경우가 많음 ●[예 1] ELSE 없는 IF 블록 DECLARE @ERR smallint SET @ERR = 1 IF @ERR <> 0 PRINT 'ERROR!'

22. 흐름 제어 ■ CASE 함수 ● CASE 함수는 여러 개의 조건식을 평가하여 일치하는 것의 값을 돌려줌 ● C 계열 언어의 switch 문과 비슷하지만, 명령을 실행하는 대신 항상 값을 돌려주기 때문에 함수라고 부름 ● 연산식을 사용할 수 있는 곳이라면 어디든지 편리하게 적용할 수 있음 ● 단순 CASE 함수와 검색된 CASE 함수로 구분됨 단순 CASE 함수 ▸ 독립적인 값을 비교할 때 많이 사용함 ▸ 구문: CASE input_expression WHEN when_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END

23. 흐름 제어 ● 검색된 CASE 함수 ▸ 값의 범위 등을 비교할 때 많이 사용함 ▸ 구문: CASE WHEN boolean_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END

24. 흐름 제어 ■ WHILE 루프 ● 조건을 만족할 때까지 반복해서 명령문 또는 블록을 실행하도록 해 줌 ● 구문: WHILE boolean_expression { statement | statement_block } ●주의: WHILE 루프에 종료 처리가 누락되면 무한 루프에 빠지는 치명적인 문제가 발생함. 이를 방지하기 위해 카운터 변수를 운영하는 것이 일반적임 ■ BREAK와 CONTINUE 문 ●WHILE 루프 안에서 사용 ●BREAK 문은 가장 안쪽의 WHILE 루프를 무조건 벗어나게 함 ●CONTINUE 문은 가장 안쪽의 WHILE 루프의 시작 부분(boolean_expression이 있는 곳)으로 제어를 이동시킴

25. 흐름 제어 ■ WHILE 루프 ● 조건을 만족할 때까지 반복해서 명령문 또는 블록을 실행하도록 해 줌 ● 구문: WHILE boolean_expression { statement | statement_block } ■ RETURN 문 ● 배치(4절에서 설명함), 저장 프로시저, 사용자 정의 함수 등을 무조건 종료시키며, 호출자에게 값을 반환할 수도 있음 ● 저장 프로시저의 경우, 항상 int 형의 값을 반환하며, 사용자 정의 함수의 경우에는 정의한 스칼라 값을 반환할 수 있음

26. 흐름 제어 ■ GOTO 문 ● 지정한 라벨로 제어를 무조건 이동시키는 명령문 ● 좋지 않다고 알려져 있으므로, 오류 처리 등, 꼭 필요한 경우를 제외하고는 가급적 사용하지 않는 것이 좋음

27. Transact-SQL 기타 구문 요소>>시스템 제공 함수 ● 시스템 제공 함수: SQL 서버 시스템이 기본적으로 제공하는 함수 ● 행 집합 함수, 집계 함수, 스칼라 함수로 구분함

28. 함수 이름 설명 CONTAINSTABLE 전체 텍스트 검색에서 키워드가 일치하는 키워드를 검색 FREETEXTTABLE 전체 텍스트 검색에서 뜻이 일치하는 키워드를 검색 OPENDATASOURCE 연결된 서버 이름을 사용하지 않고 네 부분으로 된 개체 이름의 일부로 임의 연결 정보를 제공 OPENQUERY 연결된 서버에 대해 쿼리를 실행 OPENROWSET 원격 데이터에 연결하고 액세스하는 일회성의 임시 방법으로, 연결된 서버에서 테이블을 액세스하는 방법의 대체 방법 OPENXML XML 문서에 대한 행 집합 뷰를 제공 Transact-SQL 기타 구문 요소>>행 집합 함수 ● 테이블 참조 대신 사용할 수 있는 개체를 반환함 ● 주요 행 집합 함수

29. 함수 이름 설명 AVG 그룹에서 값의 평균을 돌려줌 COUNT 그룹에 포함된 항목 개수를 돌려줌 GROUPING 행이 CUBE 또는 ROLLUP 연산자를 통해 추가될 때 추가 열의 출력이 1이 되도록 함 MAX 그룹에서 값의 최대 값을 돌려줌 MIN 그룹에서 값의 최소 값을 돌려줌 SUM 그룹에서 값의 합계를 돌려줌 Transact-SQL 기타 구문 요소>>집계 함수 ● 값 집합에 대한 계산을 수행한 후 단일 값을 돌려줌 ● GROUP BY 절이 포함된 SELECT 문에서 많이 사용함 ● 주요 집계 함수

30. 날짜 부분 약어 Year yy, yyyy quarter qq, q Month mm, m dayofyear dy, y Day dd, d Week wk, ww Hour hh minute mi, n second ss, s millisecond ms Transact-SQL 기타 구문 요소>>스칼라 함수 ● 스칼라(scalar) 함수: 단일 값에 대한 계산을 수행한 후, 단일 값을 돌려준다. ■ 날짜 및 시간 함수 ● datetime 또는 smalldatetime 데이터 형의 값에 대한 각종 조작을 수행하는 함수 ●datepart 인자 값(날짜 부분과 약어 둘 다 사용 가능함)

31. Transact-SQL 기타 구문 요소>>스칼라 함수 ■ 수치 연산 함수 ● 각종 수학 계산을 하기 위한 함수 ●ABS, ACOS, ASIN, ATAN, ATN2, CEILING, COS, COT, DEGREES, EXP, FLOOR, LOG, LOG10, PI, POWER, RADIANS, RAND, ROUND, SIGN, SIN, SQRT, SQUARE, TAN ■ 수치 연산 함수 ● 문자열을 조작하는 함수로서, 함수들 중에서 가장 많이 사용함 책 [표 6-7] 참고

32. Transact-SQL 기타 구문 요소>>동적 명령 실행 ● 동적 명령 실행: Transact-SQL 명령문(들)을 고정적으로 미리 작성해 두는 것이 아니라, 실행하기 직전에 동적으로 구성한 후 이를 실행하는 것 ● 사용자의 입력을 받은 후 명령문을 결정해야 하는 경우에 많이 사용함 ● 구문: EXEC[UTE] ( { @string_variable | [ N ] 'tsql_string' } [ + ...n ] ) ▸ EXECUTE 또는 생략형 EXEC를 둘 다 쓸 수 있는데, 생략형을 더 많이 쓰는 편임 ▸ ( )는 생략할 수 없음 ▸ 하나 이상의 문자형 변수 또는 문자열 상수를 ‘+’로 연결하여 명령문을 만듬 ▸ 중간에 숫자 형이 나올 경우에는 CONVERT 함수 등을 사용하여 반드시 문자형으로 변환한 후 연결해야 함 ▸ 연결할 때 중간에 공백을 삽입하는 것을 잊지 말아야 함

33. Transact-SQL 기타 구문 요소>>배치 ● 배치(Batch, 또는 “일괄 처리”): 한꺼번에 컴파일 및 실행되는 명령어들의 묶음 ● 쿼리 분석기 또는 osql(쿼리 분석기의 텍스트 모드 버전으로, 명령 프롬프트에서 실행함) 유틸리티에서는 특수한 명령어인 GO를 사용하여 배치의 끝을 알림 ● 배치를 구성하는 예

34. Transact-SQL 기타 구문 요소>>배치 ●불필요하게 배치를 나눌 필요는 없음(INSERT 문이나 SELECT 문은 아무리 많아도 하나의 배치에 넣을 수 있음) ● DDL 문이 개입되면 불가피하게 배치를 구분해야 하는 경우가 많음

35. Transact-SQL 기타 구문 요소>>스크립트 ●스크립트: 파일로 저장된 하나 이상의 Transact-SQL 명령문이나 배치 ● 파일의 확장자는 보통 .sql임 ● 저장된 스크립트는 쿼리 분석기나 osql 등에 로드해서 재실행할 수 있으므로 편리함