Transcript DBMS-08장(A)

08: 기초 T-SQL
08
Chapter
기초 T-SQL
학습 목표
SQL과 T-SQL을 비교하여 이해한다.
데이터 정의·제어·조작 언어로 구분된 명령문들을 이해한다.
T-SQL의 구문 요소를 이해한다.
프로그램을 작성하기 위한 흐름 제어 요소를 이해한다.
데이터베이스 구조를 만들고 프로그램을 작성하는 데 필요한 데이
터 형식을 이해한다.
데이터 형식, NULL/NOT NULL, IDENTITY 속성 등을 이해한다.
T-SQL의 각종 시스템 함수를 이해한다.
기본값, 유효성 검사, 기타 제약 조건들과 설정 방법을 이해한다.
공통 테이블 식, TOP 절, INSERT EXEC 문, 조인 및 하위
질의에 기초한 행 갱신 및 삭제 등을 숙지한다.
08
Chapter
기초 T-SQL
1. T-SQL 개요
2. T-SQL의 구문 요소
3. T-SQL의 흐름 제어
4. T-SQL의 데이터 형식
5. T-SQL의 시스템 함수
6. 무결성과 제약 조건
7. T-SQL 명령문
08
Chapter
1. T-SQL 개요
SQL과 T-SQL의 비교
 SQL과 T-SQL의 차이점
• T-SQL은 SQL의 도입 수준을 주로 구현하고, 그 밖에 SQL의 중간
수준 및 완전 수준의 일부 그리고 마이크로소프트의 독자적인 확장을
구현한 언어다.
• ANSI SQL-92는 도입 수준(entry level), 중간 수준(intermediate
level) 그리고 완전 수준(full level)으로 구분된다.
08
Chapter
1. T-SQL 개요
SQL과 T-SQL의 비교
 SQL과 T-SQL의 공통점
• SQL Server에서는 SQL과 T-SQL을 명확히 구분하지 않는다.
• 오라클에서는 SQL과 PL/SQL이 명확히 구분되어 명령어 형식과
이들을 처리하는 시스템도 서로 다르지만, SQL Server에서는 SQL과
T-SQL을 거의 동일하게 취급한다.
• SQL Server에서만 작업하는 경우에는 SQL과 T-SQL을 굳이 구분
할 필요가 없다. 그러나 다른 DBMS로 이전할 가능성이 있다면 되도록
SQL 위주로 스크립트를 작성하는 것이 좋으며, 이 경우에는T-SQL에
포함된 SQL을 구분해 낼 수 있어야 한다.
08
Chapter
1. T-SQL 개요
데이터 정의 언어(DDL) 문
 T-SQL(또는 SQL)을 기능에 따라 구분하면 데이터 정의 언어, 데이터
제어 언어, 데이터 조작 언어로 나눌 수 있다.
 데이터 정의 언어(DDL, Data Definition Language) 문
: 데이터베이스의 구조를 생성, 변경 또는 제거하는 명령문
 DDL 문의 구문
08
Chapter
1. T-SQL 개요
데이터 정의 언어(DDL) 문
• object_kind : DATABASE, DEFAULT, FUNCTION, INDEX, CEDURE,
RULE, SCHEMA, STATISTICS, TABLE, TRIGGER, VIEW가 올 수있다.
• object_name : 실제 개체의 이름이 온다.
• options : 각 명령문에 고유한 각종 옵션들이 뒤이어 온다.
•
DROP 문의 [ , ...n ]은 object_name이 여러 개 올 수 있음을 의미한다.
08
Chapter
1. T-SQL 개요
데이터 제어 언어(DCL), 데이터 조작 언어(DML) 문
 데이터 제어 언어 문 (DCL, Data Control Language)
: 데이터베이스의 특정 개체에 대한 액세스나 특정 명령문의 실행을 제
어하는 명령문
 구문
• GRANT 문: 권한을 부여하는 명령문이다.
• DENY 문: 권한을 구체적으로 빼앗는 명령문이다.
• REVOKE 문: 부여하거나 빼앗았던 권한을 중립 상태로 되돌려 놓는 명
령문이다.
08
Chapter
1. T-SQL 개요
데이터 조작 언어(DML) 문
 데이터 조작 언어(DML, Data Manipulation Language) 문
: 데이터를 추가, 갱신 또는 삭제하는 명령문
 대표적인 DML 문에는 SELECT, INSERT, UPDATE, DELETE가
있다.
08
Chapter
2. T-SQL의 구문 요소
식별자
 식별자(identifier): 데이터베이스 내 각종 개체(데이터베이스, 테이블,
열, 저장 프로시저 등)의 이름. 일반 식별자와 구분 식별자가 있다
• 일반 식별자: 표준적인 식별자로서, 다음과 같은 규칙이 있다.
1. 1∼128자의 문자로 구성된다. 단, 로컬 임시 테이블은 116자다.
2. 첫 문자는 다음 중 하나여야 한다.
- 유니코드 표준 3.2에서 정의된 문자, 밑줄(_), at 기호(@) 또는 숫자 기호(#)
3. 후속 문자는 다음을 포함할 수 있다.
- 유니코드 표준 3.2에서 정의된 문자
- 기본 라틴 또는 기타 국가 표준 스크립트의 실수
- at 기호(@), 달러 기호($), 숫자 기호 또는 밑줄
4. 식별자는 T-SQL 예약어가 아니어야 한다(대·소문자 모두).
5. 중간 공백이나 특수 문자, 보충 문자는 사용할 수 없다.
08
Chapter
2. T-SQL의 구문 요소
식별자
• 구분(delimited) 식별자: 일반 식별자의 규칙에 어긋나는 특별한 식별
자 를 사용하고자 할 때 식별자를 [ ] 또는“ ”로 둘러싸서 만든다
예제 1 구분 식별자를 활용해 보자.
1 CREATE DATABASE [1회용 DB];
2 DROP DATABASE [1회용 DB
08
Chapter
2. T-SQL의 구문 요소
식별자
예제 2 SET QUOTED_IDENTIFIER 옵션을 수정해보자.
SET QUOTED_IDENTIFIER 옵션을 OFF로 설정하면“ ”로 둘러싼 식별자에
서 오류가 발생한다. 구분 식별자를 사용할 때마다 매번 [ ] 또는“ ”로 둘러싸
야 하므로 번거롭고 오류가 발생할 가능성도 커지므로, 특별한 이유가 없다면
일반 식별자를 사용할 것을 권장한다.
1 SET QUOTED_IDENTIFIER OFF;
2 CREATE DATABASE "1회용 DB";
08
Chapter
2. T-SQL의 구문 요소
예약어
 예약어(reserved word): T-SQL에서 특별한 목적으로 사용하기
위해 예약해 놓은 키워드로, 일반 식별자로 사용하면 오류가 발생한다.
08
Chapter
예약어
2. T-SQL의 구문 요소
08
Chapter
예약어
2. T-SQL의 구문 요소
08
Chapter
예약어
2. T-SQL의 구문 요소
08
Chapter
2. T-SQL의 구문 요소
예약어
예제 3 예약어 ADD를 데이터베이스 이름으로 사용해보자.
오류가 발생한다. 쿼리 편집기에서 모든 키워드는 파란색으로 표시되는데, 이
는‘식별자로 사용하지 말라’는 경고 신호등으로 받아들이면 될 것이다.
CREATE DATABASE ADD;
08
Chapter
2. T-SQL의 구문 요소
변수
 변수(variable) 또는 지역변수
-‘@’로 시작되는 식별자
- 특정 데이터 형식의 값을 일시적으로 보관하는 용도로 사용
- 다른 일반적인 프로그래밍 언어에서처럼 T-SQL의 변수도 반드시 선언을
해야 하고, 사용하기 전에 값을 할당해야 한다.
 변수 선언과 값 할당에 사용되는 명령문들의 구문
08
Chapter
2. T-SQL의 구문 요소
변수
 구문 설명
• DECLARE 문 : 하나 이상의 변수를 선언한다.
• data_type : 데이터 형식으로, 4절에서 자세히 다룰 것이다.
• = value : 변수에 초기 값을 할당할 수 있다. 상수 또는 식을 할당할
수 있는데, 데이터 형식에 호환성이 있어야 한다.
• SET 문 : 변수에 값을 할당한다.
08
Chapter
2. T-SQL의 구문 요소
변수
예제 4 변수를 사용해보자.
@LastName이라는 변수를 선언하고, 여기에‘Gates’라는 값을 할당한 후에 이
를 SELECT 문의 WHERE 절에서 사용해보자.
1
2
3
4
5
6
USE AdventureWorksLT;
DECLARE @LastName nvarchar(50);
SET @LastName = 'Gates';
SELECT CustomerID, FirstName, LastName, Phone
FROM SalesLT.Customer
WHERE LastName = @LastName;
08
Chapter
2. T-SQL의 구문 요소
연산자
 연산자에는 산술 연산자, 대입 연산자, 비트 연산자, 비교 연산자, 논리
연산자, 문자열 연결 연산자, 단항 연산자 등이 있다.
 산술 연산자
• 산술 연산자에는 +, -, *, /, % 등이 있다.
• %는 모듈로(modulo) 연산자로, 나머지 계산을 한다.
예제 5
모듈로 연산자를 사용해보자.
PRINT 문은 연산식의 결과를 결과 집합이 아닌 메시지로 돌려준다.
PRINT 5 % 3;
08
Chapter
2. T-SQL의 구문 요소
연산자
 대입 연산자
대입 연산자에는 =가 있다. SET 문에서 변수에 값을 할당할 때 또는
SELECT 문에서 열 머리글을 정의할 때 사용된다.
 비트 연산자
비트 연산자에는 &(비트AND), |(비트OR), 그리고 ^(배타적 비트OR)
등이 있다.
예제 6
&(비트 AND) 연산자를 사용해보자.
PRINT 1 & 0;
08
Chapter
2. T-SQL의 구문 요소
연산자
예제 7
|(비트 OR) 연산자를 사용해보자.
PRINT 1 | 0;
예제 8
^(배타적 비트 OR) 연산자를 사용해보자.
PRINT 1 ^ 1;
08
Chapter
2. T-SQL의 구문 요소
연산자
 비교 연산자
비교 연산자에는 =(같다), ＞(크다), ＜(작다), ＞=(크거나 같다),
＜=(작거나 같다), ＜＞(같지 않다) 등이 있다.
 문자열 연결 연산자
문자열 연결 연산자에는 +가 있다. 문자열을 연결할 때 숫자 데이터
형식은 반드시 문자 데이터 형식으로 변환한 후 사용해야 한다.
08
Chapter
2. T-SQL의 구문 요소
연산자
예제 9 숫자 데이터 형식을 문자열 연결에 그대로 사용해보자.
- 오류가 발생한다.
PRINT ‘ABC’ + 1 + ‘DEF’;
08
Chapter
2. T-SQL의 구문 요소
연산자
예제 10 숫자 데이터 형식을 문자 데이터 형식으로 변환한 후 연결에
사용해보자.
CONVERT(char(1), 1) 함수 호출은 정수 1을 char(1) 데이터 형식으로 변환
하므로 성공한다.
PRINT ‘ABC’ + CONVERT(char(1), 1) + ‘DEF’;
08
Chapter
2. T-SQL의 구문 요소
연산자
 논리 연산자 : NOT, AND, OR 등이 있다.
 단항 연산자
• 단항 연산자에는 +(양수), -(음수), ~(비트NOT; 1의 보수) 등이 있다.
• +와 -는 숫자 형식의 식에, ~는 정수 형식의 식에만 적용할 수 있다.
예제 11 ~(비트 NOT; 1의 보수) 연산을 살펴보자.
000000002의 1의 보수인 111111112을 돌려준다.
PRINT ‘ABC’ + CONVERT(char(1), 1) + ‘DEF’;
08
Chapter
2. T-SQL의 구문 요소
연산자
 연산자 우선순위
• 연산자들 사이에는 우선순위(precedence)가 있어 서로 뒤섞여 있는
연산자들의 연산 순서를 결정해준다.
• 같은 우선순위를 가진 연산자들이 섞여 있을 때 연산 순서는 왼쪽부터
오른쪽 순으로 진행된다. 그리고 우선순위를 바꾸려면 괄호를 사용하면
된다. 괄호가 중첩될 경우에는 가장 안쪽의 괄호가 먼저 계산된다.
08
Chapter
연산자
2. T-SQL의 구문 요소
08
Chapter
2. T-SQL의 구문 요소
연산자
예제 12 중첩된 괄호를 사용해보자.
계산 순서는 (4 - 2) → (9 - 2) → 3 * 7 이 된다.
PRINT 3 * ( 9 – (4 – 2) );
08
Chapter
2. T-SQL의 구문 요소
주석
 주석(comment)
- 실행되지 않는 문자열로서, 다른 사람이나 본인이 나중에 소스를 봤을 때 쉽
게 이해할 수 있도록 소스 중간에 삽입한다.
- 주석에는 인라인 주석과 문 주석이 있다.
 인라인 주석
‘--’로 시작하여 그 줄 끝까지 이어지는 주석으로, 한 줄로 된 주석을
만들거나 명령문 뒤에 이어지는 주석을 만들 때, 그리고 명령문을 비활
성화할 때 사용한다.
08
Chapter
2. T-SQL의 구문 요소
주석
예제 13 명령문 뒤에 이어지는 주석을 만들어보자.
CREATE DATABASE ADD; --> 오류 발생함
예제 14 네 개의 명령문들의 실행을 막기 위해 비활성화시켜보자.
1
2
3
4
--SET QUOTED_IDENTIFIER ON;
--CREATE DATABASE "1회용 DB";
--DROP DATABASE "1회용 DB";
--SET QUOTED_IDENTIFIER OFF;
08
Chapter
2. T-SQL의 구문 요소
주석
 문 주석
‘/*’로 시작하고‘*/’로 끝나는 주석으로, 여러 줄로 된 주석을 만들거나 여러
개의 명령문들을 한꺼번에 비활성화할 때 사용한다.
예제 15 아래의 문 주석에서 각 줄 앞의‘**’는 가독성을 높이기 위한 것
인데, 주석 내에서 줄 바꿈이 자주 발생할 경우에는 생략하는 것이 좋
다.
1
2
3
4
5
6
7
/*
**
**
**
**
**
*/
[filename.sql]
제목:
설명:
버전:
개발 기간:
08
Chapter
2. T-SQL의 구문 요소
주석
예제 16 문 주석으로 여러 개의 명령문들을 한꺼번에 비활성화 시켜 보
자.
- 과거 버전에서는 이런 주석이 적용되지 않고 주석 내의 명령어가 실행되는 버
그가 종종 있었다. 만약 그럴 경우에는 Ctrl + K , Ctrl ＋ C 를 사용하여 인라
인 주석으로 바꿔야 한다.
1
2
3
4
5
6
/*
SET QUOTED_IDENTIFIER ON;
CREATE DATABASE "1회용 DB";
DROP DATABASE "1회용 DB";
SET QUOTED_IDENTIFIER OFF;
*/
08
Chapter
2. T-SQL의 구문 요소
동적 명령 실행
 동적 명령 실행
T-SQL 명령문(들)을 고정적으로 미리 작성해 두는 것이 아니라 실행하기 직
전에 동적으로 구성한 후 이를 실행하는 것이다.
사용자의 입력을 받은 후 명령문을 결정해야 하는 경우에 많이 사용된다.
 동적 명령 실행의 구문
08
Chapter
2. T-SQL의 구문 요소
동적 명령 실행
 구문 설명
• EXECUTE 또는 생략형 EXEC를 둘 다 쓸 수 있는데, 생략형을 더
많이 쓰는 편이다.
• ( )는 생략할 수 없다.
• 하나 이상의 문자 형식 변수 또는 문자열 상수를‘+’로 연결하여
명령문을 만든다.
• 중간에 숫자 형식이 나올 경우에는 CONVERT 함수 등을 사용하여
반드시 문자 형식으로 변환한 후 연결해야 한다.
• 연결할 때 중간에 공백을 삽입하는 것을 잊지 말아야 한다(예: 키워드
두 개가 중간에 공백 없이 붙을 경우에는 오류가 발생한다).
08
Chapter
2. T-SQL의 구문 요소
동적 명령 실행
예제 17 동적 명령을 구성하고 실행해보자.
1 DECLARE @column_list varchar(100), @table_name
varchar(20),
2
@where_phrase varchar(100), @orderby_phrase
varchar(100),
3
@sql varchar(400);
4 SET @column_list = 'CustomerID, FirstName,
LastName, Phone';
5 SET @table_name = 'SalesLT.Customer';
6 SET @where_phrase = 'CustomerID < 10';
7 SET @orderby_phrase = 'LastName';
8
9 SET @sql = 'USE AdventureWorksLT; SELECT ' +
@column_list;
10 SET @sql = @sql + ' FROM ' + @table_name;
11 IF @where_phrase IS NOT NULL
12
SET @sql = @sql + ' WHERE ' + @where_phrase;
13 IF @orderby_phrase IS NOT NULL
14
SET @sql = @sql + ' ORDER BY ' + @orderby_phrase;
15 EXEC( @sql );
08
Chapter
2. T-SQL의 구문 요소
동적 명령 실행
08
Chapter
2. T-SQL의 구문 요소
배치
 배치(batch)
: 한꺼번에 컴파일 및 실행되는 명령어들의 묶음으로‘일괄 처리’
라고도 한다. 쿼리 편집기 또는 sqlcmd 유틸리티에서는 특수한
명령어인GO를 사용하여 배치의 끝을 알린다.
 sqlcmd
: 쿼리 편집기의 텍스트 모드 버전으로 명령 프롬프트에서 실행하며,
과거 버전의 osql을 대체한다.
08
Chapter
2. T-SQL의 구문 요소
배치
 배치를 구성하는 예
08
Chapter
2. T-SQL의 구문 요소
배치
 배치-1에서 CREATE DATABASE 문, USE 문 그리고 CREATE
TABLE 문은 하나의 배치로 구성될 수 있다.
 만약 배치 내에 구문 오류가 있다면 배치 내의 명령문은 하나도 실행
되지 않고, 다음 배치로 제어가 넘어간다.
 배치-1의 경우, $test1이 구문 오류를 일으키므로 배치-1의 명령문
은 하나도 실행되지 않고 배치-2로 제어가 넘어간다.
 배치-2의 CREATE VIEW 문은 특별히 까다로운 DDL 문으로, 단독
으로 배치를 구성해야 한다.
 배치-3에서는 변수를 선언하고 초기화하고, 사용하는 명령문들을 반
드시 하나의 배치 안에 오도록 해야 한다.
08
Chapter
2. T-SQL의 구문 요소
스크립트
 스크립트
파일로 저장된 하나 이상의T-SQL 명령문이나 배치다. 스크립트의 확
장자는 일반적으로 .sql이다. 저장된 스크립트는 쿼리 편집기나
sqlcmd 등에 로드해서 재실행할 수 있으므로 편리하다.
예) 앞에서 검토했던 동적 명령 실행 예를 스크립트로 저장하고, sqlcmd
에 로드하여 실행할 수 있다. 여기서 -S 옵션은 서버 이름을 지정하는
것인데, [그림 8-3]과 같이 SSMSE SQL Server Management Studio
Express의 개체 탐색기에 표시되는 이름(예: WXP1AMD4 \SQLEXPRESS)
을 지정하면 된다. 그리고 -E 옵션은 트러스트된 연결로 설정하는 것이
고, -i 옵션은 입력 파일을 지정하는 것이다.
08
Chapter
스크립트
2. T-SQL의 구문 요소
08
Chapter
2. T-SQL의 구문 요소
스크립트
C:\> sqlcmd –S WXP1AMD4\SQLEXPRESS –E –I 08-17_
Dynamic.sql
08
Chapter
3. T-SQL의 흐름 제어
BEGIN…END 문
 하나 이상의 명령문들이 한꺼번에 실행되도록 묶어준다. C 계열 언어
(C, C++, 자바, C#)의 { } 블록과 같다고 생각하면 된다.
예제 18 BEGIN...END 문을 살펴보자.
1 IF (@@ERROR <> 0)
2
BEGIN
3
PRINT '오류 발생!';
4
RETURN;
5
END
08
Chapter
3. T-SQL의 흐름 제어
IF…ELSE 문
 조건에 따라 분기할 때 사용된다. BEGIN...END 문과 같이 사용되는
경우가 많다.
예제 19 ELSE 없는 IF 문을 살펴보자.
1 DECLARE @ERR smallint;
2 SET @ERR = 1;
3 IF @ERR <> 0
4
PRINT 'ERROR!';
08
Chapter
3. T-SQL의 흐름 제어
IF…ELSE 문
예제 20 IF...ELSE 문을 살펴보자.
- BEGIN...END 문이 없다면 무조건 RETURN 문이 실행되므로 주의한다.
1
2
3
4
5
6
7
DECLARE @ERR smallint;
SET @ERR = 1;
IF @ERR = 0
PRINT 'OK!';
ELSE
BEGIN
PRINT 'Error code: ' + CONVERT(varchar(10),
@ERR);
8
RETURN;
9
END
10 /* ... 다른 명령어들 ... */
08
Chapter
3. T-SQL의 흐름 제어
CASE 식
 단순 CASE 식: 독립적인 값을 비교할 때 많이 사용된다.
 구문
• input_expression : 보통 열 이름(SELECT 문 내에서)이나 변수가 온다.
• when_expression : input_expression과 비교하는 연산식으로,
일반적으로 문자열, 숫자 등의 상수가 온다.
• result_expression : when_expression이 input_expression
과 일치할 때 돌려주는 연산식이 온다.
• else_result_expression : when_expression이 모두 실패할 때
돌려주는 연산식이 온다.
08
Chapter
3. T-SQL의 흐름 제어
CASE 식
예제 21 Case 식을 사용해보자.
-‘Country =’는‘alias = column’형식으로 열 별칭을 지정하는 것이다. City 열의
값이‘Bothell’이면‘USA’를, ...,‘ Montreal’이면‘Canada’를, 그리고 다른 경우는
‘Unknown’을 열 값으로 돌려준다.
1 USE AdventureWorksLT;
2 SELECT City, Country =
3
CASE City
4
WHEN 'Bothell' THEN 'USA'
5
WHEN 'Dallas' THEN 'USA'
6
WHEN 'Phoenix' THEN 'USA'
7
WHEN 'Montreal' THEN 'Canada'
8
ELSE 'Unknown'
9
END
10
FROM SalesLT.Address
11
WHERE AddressID < 300;
08
Chapter
CASE 식
3. T-SQL의 흐름 제어
08
Chapter
3. T-SQL의 흐름 제어
CASE 식
 검색된 CASE 식: 값의 범위 등을 비교할 때 많이 사용된다.
 구문
• boolean_expression : 결과 값이 TRUE, FALSE 또는 NULL인
부울 식이 온다.
• result_expression : boolean_expression이 TRUE일 때 돌려주는 연산
식이 온다.
• else_result_expression : boolean_expression이 모두 실패할 때
돌려주는 연산식이 온다.
08
Chapter
3. T-SQL의 흐름 제어
CASE 식
예제 22 검색된 CASE 식을 사용해보자.
1 USE AdventureWorksLT;
2 SELECT ProductID, Name, ListPrice, PriceGrade =
3
CASE
4
WHEN ListPrice < 10 THEN 'Low'
5
WHEN ListPrice < 100 THEN 'Medium'
6
WHEN ListPrice >= 100 THEN 'High'
7
ELSE 'Illegal'
8
END
9
FROM SalesLT.Product
10
WHERE ProductID <= 710;
08
Chapter
CASE 식
3. T-SQL의 흐름 제어
08
Chapter
3. T-SQL의 흐름 제어
WHILE 루프
 WHILE 루프: 조건이 만족하는 한 반복해서 명령문 또는 문을 실행
하도록 한다.
 구문
• boolean_expression : TRUE 또는 FALSE를 반환하는 부울 식이다.
부울 식에 SELECT 문이 포함된 경우에는 SELECT 문을 괄호로 묶어
야 한다. boolean_expression은 C 계열 언어의 경우처럼 ( )로 둘러
싸지 않아도 된다.
• statement : 단일 명령문, statement_block은 명령문 블록을 가리킨
다. 일반적으로 명령문 블록을 많이 사용한다.
08
Chapter
3. T-SQL의 흐름 제어
WHILE 루프
• BREAK 문 : WHILE 루프 안에서 사용된다. 가장 안쪽의 WHILE 루프
를 무조건 벗어나게 한다.
• CONTINUE 문 : WHILE 루프 안에서 사용된다. 가장 안쪽의 WHILE
루프의 시작 부분(boolean_expression이 있는 곳)으로 제어를 이동
시킨다.
 WHILE 루프에 종료 처리가 누락되면 무한 루프에 빠지는 치명적인
문제가 발생하므로 주의해야 한다. 이를 방지하기 위해 일반적으로
카운터 변수를 운영한다.
08
Chapter
3. T-SQL의 흐름 제어
WHILE 루프
예제 23 카운터 변수를 운영하는 WHILE 루프를 사용해보자.
- @counter가 카운터 변수다.
1 DECLARE @counter int;
2 SET @counter = 1;
3 WHILE @counter <= 3
4
BEGIN
5
PRINT @counter;
6
SET @counter = @counter + 1;
7
END
08
Chapter
3. T-SQL의 흐름 제어
WHILE 루프
예제 24 BREAK와 CONTINUE 문을 사용해보자.
1 DECLARE @counter int;
2 SET @counter = 0;
3 WHILE @counter < 10
4
BEGIN
5
SET @counter = @counter + 1; --@counter 값 1 증가
6
7
IF @counter % 2 = 1
--홀수면
8
CONTINUE;
--루프 시작 부분으로 이동
9
ELSE IF @counter = 8
--8이면
10
BREAK;
--루프를 끝냄
11
ELSE
12
PRINT @counter;
--현재의 @counter 값
출력
13
END
08
Chapter
3. T-SQL의 흐름 제어
RETURN 문
 배치, 저장 프로시저, 사용자 정의 함수 등을 무조건 종료시키며, 호출
자에게 값을 반환할 수도 있다. 저장 프로시저의 경우 항상 int 형식의
값을 반환하며, 사용자 정의 함수의 경우에는 정의한 스칼라 값을 반환
할 수 있다.
예제 25 배치 내에 RETURN 문을 넣어보자.
1
2
3
4
5
DECLARE @ERR smallint;
SET @ERR = 1;
IF @ERR <> 0
RETURN;
PRINT '실행 불가능한 문임.';
08
Chapter
3. T-SQL의 흐름 제어
GOTO 문
 GOTO 문 : 지정한 레이블로 제어를 무조건 이동시키는 명령문 GOTO
를 사용하여 구현한 논리는 다른 흐름 제어문을 사용하여 구현될 수 있
으므로 GOTO 문은 가능한 한 사용하지 않는 것이 좋다.
 구 버전에서는 오류 처리를 위해 GOTO 문이 꼭 필요한 적이 있었
지만, 뒤이어 설명할 TRY...CATCH 문을 사용한다면 GOTO 문은
폐기해도 문제가 없을 것이다.
08
Chapter
3. T-SQL의 흐름 제어
TRY…CATCH 문
 TRY...CATCH는 TRY 블록과 CATCH 블록으로 구성된다. TRY
블록 내에 있는 TSQL 문에서 오류 조건을 감지하면 오류를 처리할
수 있는 CATCH 블록으로 제어가 넘어간다.
 TRY...CATCH의 구문
08
Chapter
3. T-SQL의 흐름 제어
TRY…CATCH 문
• sql_statement : 하나의 T-SQL 문이다.
• statement_block : 일괄처리 또는 BEGIN...END로 묶은 T-SQL
문 그룹이다.
• END CATCH 뒤에는‘;’이 올 수 있지만 END TRY 뒤에는 올 수
없다. 즉 BEGIN TRY부터 END CATCH 까지는 하나의 문으로 봐야 한
다.
• TRY 블록은 BEGIN TRY 문으로 시작해서 END TRY 문으로 끝난다.
• TRY 블록 바로 뒤에는 CATCH 블록이 와야 하는데, CATCH 블록은
BEGIN CATCH 문으로 시작해서 END CATCH 문으로 끝난다.
T-SQL에서 각 TRY 블록은 하나의 CATCH 블록과만 연결된다.
08
Chapter
3. T-SQL의 흐름 제어
TRY…CATCH 문
예제 26 TRY...CATCH를 사용해보자.
1
2
3
4
5
6
7
8
9
10
BEGIN TRY
-- 0으로 나누기 오류를 생성함
SELECT 1/0 AS '1/0 결과';
PRINT 'After Error'; --> 실행되지 않음
END TRY
BEGIN CATCH
PRINT 'Inside CATCH';
END CATCH;
PRINT 'Outside CATCH';
GO
08
Chapter
3. T-SQL의 흐름 제어
TRY…CATCH 문
 오류 함수: CATCH 블록에서는 다음과 같은 오류 함수를 사용하여
오류 정보를 포착할 수 있다.
• ERROR_NUMBER( ) : 오류 번호를 반환한다.
• ERROR_MESSAGE( ) : 오류 메시지의 전체 텍스트를 반환한다.
이 텍스트는 길이, 개체 이름 또는 시간과 같은 대체 가능한 매개 변수에
대해 제공된 값을 포함한다.
• ERROR_SEVERITY( ) : 오류 심각도를 반환한다.
• ERROR_STATE( ) : 오류 상태 번호를 반환한다.
• ERROR_LINE( ) : 오류를 발생시킨 루틴 내의 행 번호를 반환한다.
• ERROR_PROCEDURE( ) : 오류가 발생한 저장 프로시저, 사용자
정의 함수 또는 트리거의 이름을 반환한다.
08
Chapter
3. T-SQL의 흐름 제어
TRY…CATCH 문
예제 27 CATCH 블록에서 모든 오류 함수를 호출하고 SELECT 문으
로 확인해보자.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
BEGIN TRY
-- 0으로 나누기 오류를 생성함
SELECT 1/0 AS '1/0 결과';
PRINT 'After Error'; --> 실행되지 않음
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
08
Chapter
3. T-SQL의 흐름 제어
TRY…CATCH 문
08
Chapter
3. T-SQL의 흐름 제어
TRY…CATCH 문
 유의 사항
• 각각의 TRY...CATCH는 하나의 일괄 처리, 저장 프로시저 또는 트리거
내에 있어야 한다. 예를 들어, 서로 다른 일괄 처리에 넣을 수 없다. 즉
TRY 블록 및 연결된 CATCH 블록은 바로 이어져야 한다.
• TRY...CATCH 구문은 중첩할 수 있다. 즉 다른 TRY 및 CATCH 블록
내에 TRY...CATCH 구문을 넣을 수 있다. 중첩된 TRY 블록 내에서
오류가 발생하면 중첩된 TRY 블록과 연결된 CATCH 블록으로 프로
그램 제어가 넘어간다.
• CATCH 블록 내에서 발생하는 오류를 처리하려면 해당 CATCH 블록
안에 다시 TRY...CATCH 블록을 작성하면 된다.
08
Chapter
3. T-SQL의 흐름 제어
TRY…CATCH 문
• 데이터베이스 엔진에서 연결을 닫도록 하는 심각도 severity 20
이상의 오류는 TRY...CATCH 블록에서 처리되지 않는다. 그러나
연결이 닫히지만 않으면 TRY...CATCH 블록에서 심각도 20 이상의
오류도 처리된다.
• 심각도 10 이하의 오류는 경고 또는 정보 메시지로 간주되어 TRY...
CATCH 블록에서 처리되지 않는다.
08
Chapter
3. T-SQL의 흐름 제어
TRY…CATCH 문
예제 28 TRY 블록과 CATCH 블록 사이에 다른 일괄처리를 넣어보자.
- 5행처럼 GO를 넣어 다른 일괄 처리로 만들면 구문 오류가 발생한다.
1
2
3
4
5
6
7
8
BEGIN TRY
-- 0으로 나누기 오류를 생성함
SELECT 1/0 AS '1/0 결과';
END TRY
GO
BEGIN CATCH
PRINT 'Inside CATCH';
END CATCH;
08
Chapter
4. T-SQL의 데이터 형식
개요
 데이터 형식(data type): 열, 변수 등에 저장될 데이터의 종류, 크기
등을 지정하는 명세다.
 T-SQL의 데이터 형식은 크게 다음과 같이 구분할 수 있다.
• 문자 데이터 형식
• 숫자 데이터 형식
• 날짜 / 시각 데이터 형식
• 이진 데이터 형식
• BLOB 데이터 형식
• 기타 데이터 형식
08
Chapter
4. T-SQL의 데이터 형식
문자 데이터 형식
08
Chapter
4. T-SQL의 데이터 형식
문자 데이터 형식
예제 29 고정/가변길이 문자 형식(char(N), varchar(N))을 사용해보자.
- 주민등록번호와 사람 이름을 담을 변수를 선언하고 사용해보자. 사람 이름의
길이는 가변적이므로 @name의 데이터 형식은 varchar로 한다.
- char 및 varchar 데이터 형식에도 한글 데이터를 담을 수 있다.
1
2
3
4
DECLARE @jumin char(14), @name varchar(10);
SET @jumin = '030508-1234567';
SET @name = '홍길동';
PRINT @jumin + ', '+ @name;
08
Chapter
4. T-SQL의 데이터 형식
문자 데이터 형식
예제 30 큰 값 데이터 형식(varchar(MAX))을 사용해보자.
1 DECLARE @bigstring varchar(MAX) = '';
2 SET @bigstring = @bigstring + REPLICATE('A', 8000)
+ REPLICATE('B', 8000);
3 PRINT LEN(@bigstring);
08
Chapter
4. T-SQL의 데이터 형식
문자 데이터 형식
예제 31 시스템의 별칭 형식(sysname)을 사용해보자.
sysname 형식으로 변수를 선언하고, 현재 데이터베이스 내의 모든 개체 정보를
담고 있는 sysobjects로부터 뷰 이름을 하나 추출하여 이 변수에 할당해보자.
1
2
3
4
5
6
7
USE AdventureWorksLT;
DECLARE @tbl_name sysname;
SELECT @tbl_name = name
FROM sysobjects
WHERE name LIKE 'vGet%'
AND type = 'V';
PRINT @tbl_name;
08
Chapter
4. T-SQL의 데이터 형식
숫자 데이터 형식
08
Chapter
4. T-SQL의 데이터 형식
숫자 데이터 형식
08
Chapter
4. T-SQL의 데이터 형식
숫자 데이터 형식
08
Chapter
4. T-SQL의 데이터 형식
숫자 데이터 형식
예제 32 bit 형식 변수를 Boolean 형식처럼 사용해보자.
1
2
3
4
5
6
DECLARE @bool bit;
SET @bool = 1;
IF @bool = 1
PRINT 'TRUE';
ELSE
PRINT 'FALSE';
08
Chapter
4. T-SQL의 데이터 형식
숫자 데이터 형식
예제 33 각종 정수 데이터 형을 사용해보자.
- 도는 255개를 넘지 않으므로 tinyint 형식으로 충분하고, 시·군·면
은 32,767개를 넘지 않으므로 smallint 형식이 적절하며, 인구는 21
억을 넘지 않으므로 int 형식이 알맞고, 국민총생산은 21억을 넘으
므로 bigint 형식으로 해야 한다.
1 DECLARE @do_num tinyint, @sigunmyun_num smallint,
2
@population int, @gnp bigint
08
Chapter
4. T-SQL의 데이터 형식
날짜 / 시각 데이터 형식
08
Chapter
4. T-SQL의 데이터 형식
날짜 / 시각 데이터 형식
08
Chapter
4. T-SQL의 데이터 형식
날짜 / 시각 데이터 형식
 날짜 / 시각 데이터 형식은 각각 내부적으로 저장되는 고유한 형식이 있다.
 일반적으로 응용 프로그램에서는 임의적으로 포맷된 날짜 / 시각 표현
형식을 필요로 하는 경우가 많은데, 이를 위해 꼭 알아두어야 하는 것
이 CONVERT 함수다.
 CONVERT 함수의 구문
• 여기서 data_type은 변환할 목표가 되는 데이터 형식이고, expression은
변환할 식이며, style은 변환할 때 사용하는 스타일이다.
08
Chapter
4. T-SQL의 데이터 형식
날짜 / 시각 데이터 형식
 대표적인 날짜 / 시각 변환용 스타일
08
Chapter
4. T-SQL의 데이터 형식
날짜 / 시각 데이터 형식
 CAST 함수: CONVERT와 비슷한 함수. 그러나 CAST 함수는
CONVERT와는 달리 style을 지정할 수 없다는 단점이 있다.
 CAST 함수의 구문
예제 34
CONVERT를 사용해보자.
- GETDATE 함수로 얻은 현재 날짜 및 시각을 CONVERT 함수를
통해 두 개의 문자열 변수에 할당한 후, 결과 집합으로 출력해보자.
08
Chapter
4. T-SQL의 데이터 형식
날짜 / 시각 데이터 형식
1
2
3
4
DECLARE @date char(6), @hms char(8);
SET @date = CONVERT( char(6), GETDATE(), 12 );
SET @hms = CONVERT( char(8), GETDATE(), 8 );
SELECT @date AS '날짜', @hms AS '시각';
08
Chapter
4. T-SQL의 데이터 형식
이진 데이터 형식
 이진(binary) 데이터 : 이미지 파일이나 실행 프로그램과 같은, 텍스트
가 아닌 데이터를 말한다(예: GIF 그래픽 파일).
 binary가 고정 길이고, varbinary가 가변 길이인 것은 char와
varchar의 경우와 같다.
 binary와 varbinary 데이터 형식은 최대 8000바이트 저장 가능
 이보다 큰 데이터를 저장하고자 할 때는 varbinary(MAX)를 사용
 varbinary(MAX)는 varchar(MAX), nvarchar(MAX)와 같은 장점이
있으므로, image 데이터 형식 대신 사용할 것을 권장한다.
08
Chapter
4. T-SQL의 데이터 형식
이진 데이터 형식
08
Chapter
4. T-SQL의 데이터 형식
이진 데이터 형식
예제 35 이진 데이터를 16진수로 입력해보자.
- 아주 작은 데이터일 때만 가능하며, 실용성은 적다.
1 DECLARE @test_binary binary(4);
2 SET @test_binary = 0x41424344;
3 PRINT @test_binary;
08
Chapter
4. T-SQL의 데이터 형식
BLOB 데이터 형식
 BLOB(Binary Large Object): 대규모의 데이터를 의미한다.
T-SQL에서는 [표 8-10]과 같은 BLOB 데이터 형식을 지원한다
BLOB 데이터 형식은 세 가지가 있으며, 처리 방식이 모두 동일하다.
처리 방식은 행 외부 저장 방식과 행 내부 저장 방식으로 구분된다.
 text, ntext, image 형식 대신 varchar(MAX), nvarchar(MAX),
varbinary(MAX) 형식을 사용하기를 권장한다.
08
Chapter
4. T-SQL의 데이터 형식
BLOB 데이터 형식
08
Chapter
4. T-SQL의 데이터 형식
기타 데이터 형식
08
Chapter
4. T-SQL의 데이터 형식
별칭 데이터 형식
 별칭 데이터 형식: 지금까지 알아본 시스템 제공 데이터 형식을 이용
하여 사용자가 추가로 정의하는 데이터 형식이다.
 구문
• schema_name : 스키마 이름이다.
• type_name : 정의하려는 별칭 데이터 형식의 이름이다.
• base_type : 시스템 제공 데이터 형식의 이름이다. 자체 형식은
sysname이며, 다음 중 하나다.
08
Chapter
4. T-SQL의 데이터 형식
별칭 데이터 형식
• precision과 scale : decimal 또는 numeric 형식에서 전체 자릿수와
소수점 이하 자릿수를 표시하는 음이 아닌 정수이다.
• NULL 또는 NOT NULL : 해당 형식이 NULL 값을 보관할 수 있는지
여부를 지정한다. 이를 지정하지 않으면 기본값은 NULL이다.
08
Chapter
4. T-SQL의 데이터 형식
별칭 데이터 형식
예제 36
myname 형식을 정의해보자.
1 USE AdventureWorksLT;
2 CREATE TYPE myname
3
FROM varchar(10) NOT NULL;
08
Chapter
4. T-SQL의 데이터 형식
별칭 데이터 형식
예제 37 정의한 myname 형식을 확인해보자.
EXEC sp_help
08
Chapter
4. T-SQL의 데이터 형식
별칭 데이터 형식
예제 38
myname 형식으로 변수를 선언해보자.
1 DECLARE @var myname = '홍길동';
2 PRINT @var;
08
Chapter
4. T-SQL의 데이터 형식
별칭 데이터 형식
 구문
• schema_name : 스키마 이름이다.
• type_name : 별칭 데이터 형식의 이름이다. 별칭 데이터 형식이 테이
블 또는 다른 개체(CHECK 제약조건, 함수, 저장 프로시저, 트리거 등)
에 의해 참조되는 경우에는 삭제할 수없다. 해당 개체에서 데이터 형식을
바꾸거나 개체를 삭제한 후에 별칭 데이터 형식의 삭제가 가능하다.
08
Chapter
4. T-SQL의 데이터 형식
별칭 데이터 형식
예제 39 myname 형식을 삭제해보자.
- myname 형식은 데이터베이스 개체에 의해 참조되고 있지 않으므로
삭제가 가능하다.
1 USE AdventureWorksLT;
2 DROP TYPE myname;
08
Chapter
4. T-SQL의 데이터 형식
NULL과 NOT NULL
 열을 정의할 때 NULL을 지정하면 나중에 이 열에 데이터를 삽입 또는
갱신할 때 NULL을 허용하겠다는 것을 의미하며, 반대로 NOT NULL
은 NULL을 허용하지 않겠다는 것을 의미한다.
 NULL과 NOT NULL의 적용 지침
• 중요도가 높은 것에서 낮은 것 순으로 열의 순서를 재정렬
• 어느 선에서 위쪽은 모두 NOT NULL이, 아래쪽은 모두 NULL이 되도록
NULL 또는 NOT NULL을 적용하고, 이 상태는 항상 유지되도록 한다.
• bit 형식은 가급적 NOT NULL로 한다.
• 예외적인 적용이 필요할 경우에는 NULL 또는 NOT NULL을 수정한다
(예: 중요도는 높지만 처음에 삽입할 때는 무조건 NULL을 넣고, 트리거
에 의해 그 값이 자동으로 계산되어 수정되어야 하는 경우가 있다).
08
Chapter
4. T-SQL의 데이터 형식
NULL과 NOT NULL
 경험이 적을 때는 NOT NULL을 적용하는 경우가 많으나 NOT NULL
열은 삽입할 때 반드시 의미 있는 값을 부여해야 하므로, 행 자체를 삽
입할 수 없는 난감한 상황이 발생할 수도 있다.
 IDENTITY 속성은 테이블에 ID 열을 만든다. ID 열이란 행을 식별
할 수 있는 고유한 일련번호가 자동으로 부여되는 열을 가리키는데,
대부분 기본 키(primary key) 열을 ID 열로 많이 만든다.
 기본 키에 IDENTITY 속성을 부여하면 행을 삽입할 때마다 기본 키의
값이 기존의 값과 중복되지 않는지 조사할 필요가 없고, 자동으로 관리
되어 정확하고 편리하다.
 ID 열은 한 테이블에 최대 하나만 존재할 수 있다.
08
Chapter
4. T-SQL의 데이터 형식
IDENTITY 속성
 IDENTITY 속성은 정수 데이터 형식의 열에만 적용될 수 있다.
 구문
• 초기 값 : 첫째 행에 적용되는 일련번호의 값이다.
• 증분 값 : 이어서 부여되는 값들 사이의 차이다.
08
Chapter
4. T-SQL의 데이터 형식
IDENTITY 속성
예제 40
1
2
3
4
5
6
7
8
9
10
IDENTITY 속성을 사용해보자.
USE Test1DB;
GO
CREATE TABLE tbl1 (
tbl_id int IDENTITY(100, 10)
);
GO
INSERT tbl1 DEFAULT VALUES;
INSERT tbl1 DEFAULT VALUES;
INSERT tbl1 DEFAULT VALUES;
SELECT * FROM tbl1;
08
Chapter
4. T-SQL의 데이터 형식
IDENTITY 속성
예제 41 tbl_id 열 값이 110인 행을 삭제하고 한 행을 삽입해 보자.
1
2
3
4
5
6
7
USE Test1DB;
DELETE tbl1
WHERE tbl_id = 110;
INSERT tbl1 DEFAULT VALUES;
SELECT *
FROM tbl1
ORDER BY tbl_id;
08
Chapter
4. T-SQL의 데이터 형식
IDENTITY 속성
 IDENTITY 초기값을 확인하려면 IDENT_SEED 함수를, 증분값을 확인
하려면 IDENT_INCR 함수를 사용하면 되고, 방금 삽입된 IDENTITY
값을 확인하려면 @@IDENTITY 스칼라 함수를 호출하면 된다.
 SQL Server 6.5까지는‘@@’로 시작되는 전역 변수가 있었지만,
SQL Server 7.0부터는 이를 더 이상 전역 변수라고 하지 않고,
스칼라 함수라고 한다.
 @@IDENTITY 함수는 반드시 행을 삽입한 직후에 호출해야 한다.
왜냐하면 다른 테이블에서 IDENTITY 값이 삽입되면 @@IDENTITY
함수의 반환 값이 바뀌기 때문이다.
08
Chapter
4. T-SQL의 데이터 형식
IDENTITY 속성
예제 42 관련 함수들을 사용해보자.
1 USE Test1DB;
2 INSERT tbl1 DEFAULT VALUES;
3 SELECT IDENT_SEED('tbl1') AS SEED,
4
IDENT_INCR('tbl1') AS INCR,
5
@@IDENTITY AS '@@IDENTITY';
08
Chapter
4. T-SQL의 데이터 형식
IDENTITY 속성
 IDENTITY_INSERT 옵션 설정 구문
• IDENTITY_INSERT 옵션을 ON으로 설정하면 INSERT 문에서 ID 열
을 지정할 수 있을 뿐 아니라, 임의의 값을 삽입할 수도 있다. 그러나 기
존에 삽입된 IDENTITY 값을 갱신할 수는 없다. 갱신하려면 기존 행을
삭제하고, 다른 값을 삽입해야 한다(한 테이블에서 최대 하나뿐인 ID 열
을 지정할 때 IDENTITYCOL이라는 키워드를 사용할수 있다.).
 IDENTITY_INSERT 옵션을 ON으로 설정하는 것은 특별한 경우 외에는
권장하지 않으며, 작업을 완료한 직후에 OFF로 원상 복구해야 한다.
08
Chapter
4. T-SQL의 데이터 형식
IDENTITY 속성
예제 43 IDENTITY_INSERT 옵션을 설정해보자.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
USE Test1DB;
SET IDENTITY_INSERT tbl1 ON;
INSERT tbl1 (tbl_id)
VALUES (115);
GO
UPDATE tbl1
SET tbl_id = 125
WHERE IDENTITYCOL = 120;
GO
DELETE tbl1
WHERE IDENTITYCOL = 120;
INSERT tbl1 (tbl_id)
VALUES (125);
GO
SELECT *
FROM tbl1
ORDER BY tbl_id;
SET IDENTITY_INSERT tbl1 OFF
08
Chapter
4. T-SQL의 데이터 형식
IDENTITY 속성