기초 T-SQL

SQL과 T-SQL을 비교하여 이해한다. 데이터 정의·제어·조작 언어로 구분된 명령문들을 이해한다 SQL과 T-SQL을 비교하여 이해한다. 데이터 정의·제어·조작 언어로 구분된 명령문들을 이해한다. T-SQL의 구문 요소를 이해한다. 프로그램을 작성하기 위한 흐름 제어 요소를 이해한다. 데이터베이스 구조를 만들고 프로그램을 작성하는 데 필요한 데이터 형식을 이해한다. 데이터 형식, NULL/NOT NULL, IDENTITY 속성 등을 이해한다. T-SQL의 각종 시스템 함수를 이해한다. 기본값, 유효성 검사, 기타 제약 조건들과 설정 방법을 이해한다. 공통 테이블 식, TOP 절, INSERT EXEC 문, 조인 및 하위 질의에 기초한 행 갱신 및 삭제 등을 숙지한다.

1. T-SQL 개요 2. T-SQL의 구문 요소 3. T-SQL의 흐름 제어 4. T-SQL의 데이터 형식 5 1. T-SQL 개요 2. T-SQL의 구문 요소 3. T-SQL의 흐름 제어 4. T-SQL의 데이터 형식 5. T-SQL의 시스템 함수 6. 무결성과 제약 조건 7. T-SQL 명령문

1. T-SQL 개요 SQL과 T-SQL의 비교 SQL과 T-SQL의 차이점 T-SQL은 SQL의 도입 수준을 주로 구현하고, 그 밖에 SQL의 중간 수준 및 완전 수준의 일부 그리고 마이크로소프트의 독자적인 확장을 구현한 언어다. ANSI SQL-92는 도입 수준(entry level), 중간 수준(intermediate level) 그리고 완전 수준(full level)으로 구분된다.

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을 구분해 낼 수 있어야 한다.

1. T-SQL 개요 데이터 정의 언어(DDL) 문 T-SQL(또는 SQL)을 기능에 따라 구분하면 데이터 정의 언어, 데이터 제어 언어, 데이터 조작 언어로 나눌 수 있다. 데이터 정의 언어(DDL, Data Definition Language) 문 : 데이터베이스의 구조를 생성, 변경 또는 제거하는 명령문 DDL 문의 구문

1. T-SQL 개요 데이터 정의 언어(DDL) 문 object_kind : DATABASE, DEFAULT, FUNCTION, INDEX, CEDURE, RULE, SCHEMA, STATISTICS, TABLE, TRIGGER, VIEW가 올 수있다. object_name : 실제 개체의 이름이 온다. options : 각 명령문에 고유한 각종 옵션들이 뒤이어 온다. DROP 문의 [ , ...n ]은 object_name이 여러 개 올 수 있음을 의미한다.

1. T-SQL 개요 데이터 제어 언어(DCL), 데이터 조작 언어(DML) 문 데이터 제어 언어 문 (DCL, Data Control Language) : 데이터베이스의 특정 개체에 대한 액세스나 특정 명령문의 실행을 제어하는 명령문 구문 GRANT 문: 권한을 부여하는 명령문이다. DENY 문: 권한을 구체적으로 빼앗는 명령문이다. REVOKE 문: 부여하거나 빼앗았던 권한을 중립 상태로 되돌려 놓는 명령문이다.

1. T-SQL 개요 데이터 조작 언어(DML) 문 데이터 조작 언어(DML, Data Manipulation Language) 문 : 데이터를 추가, 갱신 또는 삭제하는 명령문 대표적인 DML 문에는 SELECT, INSERT, UPDATE, DELETE가 있다.

2. T-SQL의 구문 요소 식별자 식별자(identifier): 데이터베이스 내 각종 개체(데이터베이스, 테이블, 열, 저장 프로시저 등)의 이름. 일반 식별자와 구분 식별자가 있다 일반 식별자: 표준적인 식별자로서, 다음과 같은 규칙이 있다. 1∼128자의 문자로 구성된다. 단, 로컬 임시 테이블은 116자다. 첫 문자는 다음 중 하나여야 한다. - 유니코드 표준 3.2에서 정의된 문자, 밑줄(_), at 기호(@) 또는 숫자 기호(#) 3. 후속 문자는 다음을 포함할 수 있다. - 유니코드 표준 3.2에서 정의된 문자 - 기본 라틴 또는 기타 국가 표준 스크립트의 실수 - at 기호(@), 달러 기호($), 숫자 기호 또는 밑줄 4. 식별자는 T-SQL 예약어가 아니어야 한다(대·소문자 모두). 5. 중간 공백이나 특수 문자, 보충 문자는 사용할 수 없다.

2. T-SQL의 구문 요소 식별자 구분(delimited) 식별자: 일반 식별자의 규칙에 어긋나는 특별한 식별자 를 사용하고자 할 때 식별자를 [ ] 또는“ ”로 둘러싸서 만든다 구분 식별자를 활용해 보자. 예제 1 1 CREATE DATABASE [1회용 DB]; 2 DROP DATABASE [1회용 DB

2. T-SQL의 구문 요소 식별자 SET QUOTED_IDENTIFIER 옵션을 수정해보자. 예제 2 SET QUOTED_IDENTIFIER 옵션을 OFF로 설정하면“ ”로 둘러싼 식별자에서 오류가 발생한다. 구분 식별자를 사용할 때마다 매번 [ ] 또는“ ”로 둘러싸야 하므로 번거롭고 오류가 발생할 가능성도 커지므로, 특별한 이유가 없다면 일반 식별자를 사용할 것을 권장한다. 예제 2 2 CREATE DATABASE "1회용 DB"; 1 SET QUOTED_IDENTIFIER OFF;

2. T-SQL의 구문 요소 예약어 예약어(reserved word): T-SQL에서 특별한 목적으로 사용하기 위해 예약해 놓은 키워드로, 일반 식별자로 사용하면 오류가 발생한다.

2. T-SQL의 구문 요소 예약어

2. T-SQL의 구문 요소 예약어

2. T-SQL의 구문 요소 예약어

2. T-SQL의 구문 요소 예약어 예약어 ADD를 데이터베이스 이름으로 사용해보자. 오류가 발생한다. 쿼리 편집기에서 모든 키워드는 파란색으로 표시되는데, 이는‘식별자로 사용하지 말라’는 경고 신호등으로 받아들이면 될 것이다. 예제 3 CREATE DATABASE ADD;

2. T-SQL의 구문 요소 변수 변수(variable) 또는 지역변수 변수 선언과 값 할당에 사용되는 명령문들의 구문 -‘@’로 시작되는 식별자 - 특정 데이터 형식의 값을 일시적으로 보관하는 용도로 사용 - 다른 일반적인 프로그래밍 언어에서처럼 T-SQL의 변수도 반드시 선언을 해야 하고, 사용하기 전에 값을 할당해야 한다. 변수 선언과 값 할당에 사용되는 명령문들의 구문

2. T-SQL의 구문 요소 변수 구문 설명 DECLARE 문 : 하나 이상의 변수를 선언한다. data_type : 데이터 형식으로, 4절에서 자세히 다룰 것이다. = value : 변수에 초기 값을 할당할 수 있다. 상수 또는 식을 할당할 수 있는데, 데이터 형식에 호환성이 있어야 한다. SET 문 : 변수에 값을 할당한다.

2. T-SQL의 구문 요소 변수 변수를 사용해보자. @LastName이라는 변수를 선언하고, 여기에‘Gates’라는 값을 할당한 후에 이를 SELECT 문의 WHERE 절에서 사용해보자. 예제 4 1 USE AdventureWorksLT; 2 DECLARE @LastName nvarchar(50); 3 SET @LastName = 'Gates'; 4 SELECT CustomerID, FirstName, LastName, Phone 5 FROM SalesLT.Customer 6 WHERE LastName = @LastName;

2. T-SQL의 구문 요소 연산자 모듈로 연산자를 사용해보자. 연산자에는 산술 연산자, 대입 연산자, 비트 연산자, 비교 연산자, 논리 연산자, 문자열 연결 연산자, 단항 연산자 등이 있다. 산술 연산자 산술 연산자에는 +, -, *, /, % 등이 있다. %는 모듈로(modulo) 연산자로, 나머지 계산을 한다. 모듈로 연산자를 사용해보자. PRINT 문은 연산식의 결과를 결과 집합이 아닌 메시지로 돌려준다. 예제 5 PRINT 5 % 3;

2. T-SQL의 구문 요소 연산자 대입 연산자 비트 연산자 예제 6 대입 연산자에는 =가 있다. SET 문에서 변수에 값을 할당할 때 또는 SELECT 문에서 열 머리글을 정의할 때 사용된다. 비트 연산자 비트 연산자에는 &(비트AND), |(비트OR), 그리고 ^(배타적 비트OR) 등이 있다. &(비트 AND) 연산자를 사용해보자. 예제 6 PRINT 1 & 0;

2. T-SQL의 구문 요소 연산자 |(비트 OR) 연산자를 사용해보자. ^(배타적 비트 OR) 연산자를 사용해보자. 예제 7 PRINT 1 | 0; 예제 8 PRINT 1 ^ 1;

2. T-SQL의 구문 요소 연산자 비교 연산자 문자열 연결 연산자 비교 연산자에는 =(같다), ＞(크다), ＜(작다), ＞=(크거나 같다), ＜=(작거나 같다), ＜＞(같지 않다) 등이 있다. 문자열 연결 연산자 문자열 연결 연산자에는 +가 있다. 문자열을 연결할 때 숫자 데이터 형식은 반드시 문자 데이터 형식으로 변환한 후 사용해야 한다.

2. T-SQL의 구문 요소 연산자 숫자 데이터 형식을 문자열 연결에 그대로 사용해보자. - 오류가 발생한다. 예제 9 PRINT ‘ABC’ + 1 + ‘DEF’;

2. T-SQL의 구문 요소 연산자 숫자 데이터 형식을 문자 데이터 형식으로 변환한 후 연결에 사용해보자. CONVERT(char(1), 1) 함수 호출은 정수 1을 char(1) 데이터 형식으로 변환하므로 성공한다. 예제 10 PRINT ‘ABC’ + CONVERT(char(1), 1) + ‘DEF’;

2. T-SQL의 구문 요소 연산자 논리 연산자 : NOT, AND, OR 등이 있다. 단항 연산자 +와 -는 숫자 형식의 식에, ~는 정수 형식의 식에만 적용할 수 있다. ~(비트 NOT; 1의 보수) 연산을 살펴보자. 000000002의 1의 보수인 111111112을 돌려준다. 예제 11 PRINT ‘ABC’ + CONVERT(char(1), 1) + ‘DEF’;

2. T-SQL의 구문 요소 연산자 연산자 우선순위 연산자들 사이에는 우선순위(precedence)가 있어 서로 뒤섞여 있는 연산자들의 연산 순서를 결정해준다. 같은 우선순위를 가진 연산자들이 섞여 있을 때 연산 순서는 왼쪽부터 오른쪽 순으로 진행된다. 그리고 우선순위를 바꾸려면 괄호를 사용하면 된다. 괄호가 중첩될 경우에는 가장 안쪽의 괄호가 먼저 계산된다.

2. T-SQL의 구문 요소 연산자

2. T-SQL의 구문 요소 연산자 중첩된 괄호를 사용해보자. 계산 순서는 (4 - 2) → (9 - 2) → 3 * 7 이 된다. 예제 12 PRINT 3 * ( 9 – (4 – 2) );

2. T-SQL의 구문 요소 주석 주석(comment) 인라인 주석 - 실행되지 않는 문자열로서, 다른 사람이나 본인이 나중에 소스를 봤을 때 쉽게 이해할 수 있도록 소스 중간에 삽입한다. - 주석에는 인라인 주석과 문 주석이 있다. 인라인 주석 ‘--’로 시작하여 그 줄 끝까지 이어지는 주석으로, 한 줄로 된 주석을 만들거나 명령문 뒤에 이어지는 주석을 만들 때, 그리고 명령문을 비활 성화할 때 사용한다.

2. T-SQL의 구문 요소 주석 명령문 뒤에 이어지는 주석을 만들어보자. 예제 13 네 개의 명령문들의 실행을 막기 위해 비활성화시켜보자. 1 --SET QUOTED_IDENTIFIER ON; 2 --CREATE DATABASE "1회용 DB"; 3 --DROP DATABASE "1회용 DB"; 4 --SET QUOTED_IDENTIFIER OFF; 예제 13 CREATE DATABASE ADD; --> 오류 발생함 예제 14

2. T-SQL의 구문 요소 주석 문 주석 ‘/*’로 시작하고‘*/’로 끝나는 주석으로, 여러 줄로 된 주석을 만들거나 여러 개의 명령문들을 한꺼번에 비활성화할 때 사용한다. 아래의 문 주석에서 각 줄 앞의‘**’는 가독성을 높이기 위한 것인데, 주석 내에서 줄 바꿈이 자주 발생할 경우에는 생략하는 것이 좋다. 1 /* [filename.sql] 2 ** 제목: 3 ** 설명: 4 ** 5 ** 버전: 6 ** 개발 기간: 7 */ 예제 15

2. T-SQL의 구문 요소 주석 문 주석으로 여러 개의 명령문들을 한꺼번에 비활성화 시켜 보 자. 예제 16 - 과거 버전에서는 이런 주석이 적용되지 않고 주석 내의 명령어가 실행되는 버그가 종종 있었다. 만약 그럴 경우에는 Ctrl + K , Ctrl ＋ C 를 사용하여 인라인 주석으로 바꿔야 한다. 1 /* 2 SET QUOTED_IDENTIFIER ON; 3 CREATE DATABASE "1회용 DB"; 4 DROP DATABASE "1회용 DB"; 5 SET QUOTED_IDENTIFIER OFF; 6 */ 예제 16

2. T-SQL의 구문 요소 동적 명령 실행 동적 명령 실행 사용자의 입력을 받은 후 명령문을 결정해야 하는 경우에 많이 사용된다. 동적 명령 실행의 구문

2. T-SQL의 구문 요소 동적 명령 실행 구문 설명 EXECUTE 또는 생략형 EXEC를 둘 다 쓸 수 있는데, 생략형을 더 많이 쓰는 편이다. ( )는 생략할 수 없다. 하나 이상의 문자 형식 변수 또는 문자열 상수를‘+’로 연결하여 명령문을 만든다. 중간에 숫자 형식이 나올 경우에는 CONVERT 함수 등을 사용하여 반드시 문자 형식으로 변환한 후 연결해야 한다. 연결할 때 중간에 공백을 삽입하는 것을 잊지 말아야 한다(예: 키워드 두 개가 중간에 공백 없이 붙을 경우에는 오류가 발생한다).

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 );

2. T-SQL의 구문 요소 동적 명령 실행

2. T-SQL의 구문 요소 배치 배치(batch) : 한꺼번에 컴파일 및 실행되는 명령어들의 묶음으로‘일괄 처리’ 라고도 한다. 쿼리 편집기 또는 sqlcmd 유틸리티에서는 특수한 명령어인GO를 사용하여 배치의 끝을 알린다. sqlcmd : 쿼리 편집기의 텍스트 모드 버전으로 명령 프롬프트에서 실행하며, 과거 버전의 osql을 대체한다.

2. T-SQL의 구문 요소 배치 배치를 구성하는 예

2. T-SQL의 구문 요소 배치 배치-1에서 CREATE DATABASE 문, USE 문 그리고 CREATE TABLE 문은 하나의 배치로 구성될 수 있다. 만약 배치 내에 구문 오류가 있다면 배치 내의 명령문은 하나도 실행 되지 않고, 다음 배치로 제어가 넘어간다. 배치-1의 경우, $test1이 구문 오류를 일으키므로 배치-1의 명령문 은 하나도 실행되지 않고 배치-2로 제어가 넘어간다. 배치-2의 CREATE VIEW 문은 특별히 까다로운 DDL 문으로, 단독 으로 배치를 구성해야 한다. 배치-3에서는 변수를 선언하고 초기화하고, 사용하는 명령문들을 반드시 하나의 배치 안에 오도록 해야 한다.

2. T-SQL의 구문 요소 스크립트 스크립트 파일로 저장된 하나 이상의T-SQL 명령문이나 배치다. 스크립트의 확장자는 일반적으로 .sql이다. 저장된 스크립트는 쿼리 편집기나 sqlcmd 등에 로드해서 재실행할 수 있으므로 편리하다. 예) 앞에서 검토했던 동적 명령 실행 예를 스크립트로 저장하고, sqlcmd 에 로드하여 실행할 수 있다. 여기서 -S 옵션은 서버 이름을 지정하는 것인데, [그림 8-3]과 같이 SSMSE SQL Server Management Studio Express의 개체 탐색기에 표시되는 이름(예: WXP1AMD4 \SQLEXPRESS) 을 지정하면 된다. 그리고 -E 옵션은 트러스트된 연결로 설정하는 것이 고, -i 옵션은 입력 파일을 지정하는 것이다.

2. T-SQL의 구문 요소 스크립트

2. T-SQL의 구문 요소 스크립트 C:\> sqlcmd –S WXP1AMD4\SQLEXPRESS –E –I 08-17_ Dynamic.sql

3. T-SQL의 흐름 제어 BEGIN…END 문 하나 이상의 명령문들이 한꺼번에 실행되도록 묶어준다. C 계열 언어 (C, C++, 자바, C#)의 { } 블록과 같다고 생각하면 된다. BEGIN...END 문을 살펴보자. 예제 18 1 IF (@@ERROR <> 0) 2 BEGIN 3 PRINT '오류 발생!'; 4 RETURN; 5 END

3. T-SQL의 흐름 제어 IF…ELSE 문 조건에 따라 분기할 때 사용된다. BEGIN...END 문과 같이 사용되는 경우가 많다. ELSE 없는 IF 문을 살펴보자. 예제 19 1 DECLARE @ERR smallint; 2 SET @ERR = 1; 3 IF @ERR <> 0 4 PRINT 'ERROR!';

3. T-SQL의 흐름 제어 IF…ELSE 문 IF...ELSE 문을 살펴보자. - BEGIN...END 문이 없다면 무조건 RETURN 문이 실행되므로 주의한다. 예제 20 1 DECLARE @ERR smallint; 2 SET @ERR = 1; 3 IF @ERR = 0 4 PRINT 'OK!'; 5 ELSE 6 BEGIN 7 PRINT 'Error code: ' + CONVERT(varchar(10), @ERR); 8 RETURN; 9 END 10 /* ... 다른 명령어들 ... */

3. T-SQL의 흐름 제어 CASE 식 단순 CASE 식: 독립적인 값을 비교할 때 많이 사용된다. 구문 input_expression : 보통 열 이름(SELECT 문 내에서)이나 변수가 온다. when_expression : input_expression과 비교하는 연산식으로, 일반적으로 문자열, 숫자 등의 상수가 온다. result_expression : when_expression이 input_expression 과 일치할 때 돌려주는 연산식이 온다. else_result_expression : when_expression이 모두 실패할 때 돌려주는 연산식이 온다.

3. T-SQL의 흐름 제어 CASE 식 Case 식을 사용해보자. -‘Country =’는‘alias = column’형식으로 열 별칭을 지정하는 것이다. City 열의 값이‘Bothell’이면‘USA’를, ...,‘ Montreal’이면‘Canada’를, 그리고 다른 경우는‘Unknown’을 열 값으로 돌려준다. 예제 21 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;

3. T-SQL의 흐름 제어 CASE 식

3. T-SQL의 흐름 제어 CASE 식 검색된 CASE 식: 값의 범위 등을 비교할 때 많이 사용된다. 구문 boolean_expression : 결과 값이 TRUE, FALSE 또는 NULL인 부울 식이 온다. result_expression : boolean_expression이 TRUE일 때 돌려주는 연산식이 온다. else_result_expression : boolean_expression이 모두 실패할 때 돌려주는 연산식이 온다.

3. T-SQL의 흐름 제어 CASE 식 검색된 CASE 식을 사용해보자. 예제 22 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;

3. T-SQL의 흐름 제어 CASE 식

3. T-SQL의 흐름 제어 WHILE 루프 WHILE 루프: 조건이 만족하는 한 반복해서 명령문 또는 문을 실행 하도록 한다. 구문 boolean_expression : TRUE 또는 FALSE를 반환하는 부울 식이다. 부울 식에 SELECT 문이 포함된 경우에는 SELECT 문을 괄호로 묶어 야 한다. boolean_expression은 C 계열 언어의 경우처럼 ( )로 둘러 싸지 않아도 된다. statement : 단일 명령문, statement_block은 명령문 블록을 가리킨 다. 일반적으로 명령문 블록을 많이 사용한다.

3. T-SQL의 흐름 제어 WHILE 루프 WHILE 루프에 종료 처리가 누락되면 무한 루프에 빠지는 치명적인 BREAK 문 : WHILE 루프 안에서 사용된다. 가장 안쪽의 WHILE 루프 를 무조건 벗어나게 한다. CONTINUE 문 : WHILE 루프 안에서 사용된다. 가장 안쪽의 WHILE 루프의 시작 부분(boolean_expression이 있는 곳)으로 제어를 이동 시킨다. WHILE 루프에 종료 처리가 누락되면 무한 루프에 빠지는 치명적인 문제가 발생하므로 주의해야 한다. 이를 방지하기 위해 일반적으로 카운터 변수를 운영한다.

3. T-SQL의 흐름 제어 WHILE 루프 카운터 변수를 운영하는 WHILE 루프를 사용해보자. - @counter가 카운터 변수다. 예제 23 1 DECLARE @counter int; 2 SET @counter = 1; 3 WHILE @counter <= 3 4 BEGIN 5 PRINT @counter; 6 SET @counter = @counter + 1; 7 END

3. T-SQL의 흐름 제어 WHILE 루프 BREAK와 CONTINUE 문을 사용해보자. 예제 24 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

3. T-SQL의 흐름 제어 RETURN 문 배치, 저장 프로시저, 사용자 정의 함수 등을 무조건 종료시키며, 호출 자에게 값을 반환할 수도 있다. 저장 프로시저의 경우 항상 int 형식의 값을 반환하며, 사용자 정의 함수의 경우에는 정의한 스칼라 값을 반환 할 수 있다. 배치 내에 RETURN 문을 넣어보자. 예제 25 1 DECLARE @ERR smallint; 2 SET @ERR = 1; 3 IF @ERR <> 0 4 RETURN; 5 PRINT '실행 불가능한 문임.';

3. T-SQL의 흐름 제어 GOTO 문 GOTO 문 : 지정한 레이블로 제어를 무조건 이동시키는 명령문 GOTO를 사용하여 구현한 논리는 다른 흐름 제어문을 사용하여 구현될 수 있으므로 GOTO 문은 가능한 한 사용하지 않는 것이 좋다. 구 버전에서는 오류 처리를 위해 GOTO 문이 꼭 필요한 적이 있었 지만, 뒤이어 설명할 TRY...CATCH 문을 사용한다면 GOTO 문은 폐기해도 문제가 없을 것이다.

3. T-SQL의 흐름 제어 TRY…CATCH 문 TRY...CATCH는 TRY 블록과 CATCH 블록으로 구성된다. TRY

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 블록과만 연결된다.

3. T-SQL의 흐름 제어 TRY…CATCH 문 TRY...CATCH를 사용해보자. 예제 26 1 BEGIN TRY 2 -- 0으로 나누기 오류를 생성함 3 SELECT 1/0 AS '1/0 결과'; 4 PRINT 'After Error'; --> 실행되지 않음 5 END TRY 6 BEGIN CATCH 7 PRINT 'Inside CATCH'; 8 END CATCH; 9 PRINT 'Outside CATCH'; 10 GO

3. T-SQL의 흐름 제어 TRY…CATCH 문 오류 함수: CATCH 블록에서는 다음과 같은 오류 함수를 사용하여 오류 정보를 포착할 수 있다. ERROR_NUMBER( ) : 오류 번호를 반환한다. ERROR_MESSAGE( ) : 오류 메시지의 전체 텍스트를 반환한다. 이 텍스트는 길이, 개체 이름 또는 시간과 같은 대체 가능한 매개 변수에 대해 제공된 값을 포함한다. ERROR_SEVERITY( ) : 오류 심각도를 반환한다. ERROR_STATE( ) : 오류 상태 번호를 반환한다. ERROR_LINE( ) : 오류를 발생시킨 루틴 내의 행 번호를 반환한다. ERROR_PROCEDURE( ) : 오류가 발생한 저장 프로시저, 사용자 정의 함수 또는 트리거의 이름을 반환한다.

3. T-SQL의 흐름 제어 TRY…CATCH 문 CATCH 블록에서 모든 오류 함수를 호출하고 SELECT 문으로 확인해보자. 예제 27 1 BEGIN TRY 2 -- 0으로 나누기 오류를 생성함 3 SELECT 1/0 AS '1/0 결과'; 4 PRINT 'After Error'; --> 실행되지 않음 5 END TRY 6 BEGIN CATCH 7 SELECT 8 ERROR_NUMBER() AS ErrorNumber 9 ,ERROR_SEVERITY() AS ErrorSeverity 10 ,ERROR_STATE() AS ErrorState 11 ,ERROR_PROCEDURE() AS ErrorProcedure 12 ,ERROR_LINE() AS ErrorLine 13 ,ERROR_MESSAGE() AS ErrorMessage; 14 END CATCH; 15 GO

3. T-SQL의 흐름 제어 TRY…CATCH 문

3. T-SQL의 흐름 제어 TRY…CATCH 문 유의 사항 내에 있어야 한다. 예를 들어, 서로 다른 일괄 처리에 넣을 수 없다. 즉 TRY 블록 및 연결된 CATCH 블록은 바로 이어져야 한다. TRY...CATCH 구문은 중첩할 수 있다. 즉 다른 TRY 및 CATCH 블록 내에 TRY...CATCH 구문을 넣을 수 있다. 중첩된 TRY 블록 내에서 오류가 발생하면 중첩된 TRY 블록과 연결된 CATCH 블록으로 프로 그램 제어가 넘어간다. CATCH 블록 내에서 발생하는 오류를 처리하려면 해당 CATCH 블록 안에 다시 TRY...CATCH 블록을 작성하면 된다.

3. T-SQL의 흐름 제어 TRY…CATCH 문 데이터베이스 엔진에서 연결을 닫도록 하는 심각도 severity 20 오류도 처리된다. 심각도 10 이하의 오류는 경고 또는 정보 메시지로 간주되어 TRY... CATCH 블록에서 처리되지 않는다.

3. T-SQL의 흐름 제어 TRY…CATCH 문 TRY 블록과 CATCH 블록 사이에 다른 일괄처리를 넣어보자. - 5행처럼 GO를 넣어 다른 일괄 처리로 만들면 구문 오류가 발생한다. 예제 28 1 BEGIN TRY 2 -- 0으로 나누기 오류를 생성함 3 SELECT 1/0 AS '1/0 결과'; 4 END TRY 5 GO 6 BEGIN CATCH 7 PRINT 'Inside CATCH'; 8 END CATCH;

4. T-SQL의 데이터 형식 개요 데이터 형식(data type): 열, 변수 등에 저장될 데이터의 종류, 크기 등을 지정하는 명세다. T-SQL의 데이터 형식은 크게 다음과 같이 구분할 수 있다. 문자 데이터 형식 숫자 데이터 형식 날짜 / 시각 데이터 형식 이진 데이터 형식 BLOB 데이터 형식 기타 데이터 형식

4. T-SQL의 데이터 형식 문자 데이터 형식

4. T-SQL의 데이터 형식 문자 데이터 형식 고정/가변길이 문자 형식(char(N), varchar(N))을 사용해보자. - 주민등록번호와 사람 이름을 담을 변수를 선언하고 사용해보자. 사람 이름의 길이는 가변적이므로 @name의 데이터 형식은 varchar로 한다. - char 및 varchar 데이터 형식에도 한글 데이터를 담을 수 있다. 예제 29 1 DECLARE @jumin char(14), @name varchar(10); 2 SET @jumin = '030508-1234567'; 3 SET @name = '홍길동'; 4 PRINT @jumin + ', '+ @name;

4. T-SQL의 데이터 형식 문자 데이터 형식 큰 값 데이터 형식(varchar(MAX))을 사용해보자. 예제 30 1 DECLARE @bigstring varchar(MAX) = ''; 2 SET @bigstring = @bigstring + REPLICATE('A', 8000) + REPLICATE('B', 8000); 3 PRINT LEN(@bigstring);

4. T-SQL의 데이터 형식 문자 데이터 형식 시스템의 별칭 형식(sysname)을 사용해보자. sysname 형식으로 변수를 선언하고, 현재 데이터베이스 내의 모든 개체 정보를 담고 있는 sysobjects로부터 뷰 이름을 하나 추출하여 이 변수에 할당해보자. 예제 31 1 USE AdventureWorksLT; 2 DECLARE @tbl_name sysname; 3 SELECT @tbl_name = name 4 FROM sysobjects 5 WHERE name LIKE 'vGet%' 6 AND type = 'V'; 7 PRINT @tbl_name;

4. T-SQL의 데이터 형식 숫자 데이터 형식

4. T-SQL의 데이터 형식 숫자 데이터 형식

4. T-SQL의 데이터 형식 숫자 데이터 형식

4. T-SQL의 데이터 형식 숫자 데이터 형식 bit 형식 변수를 Boolean 형식처럼 사용해보자. 예제 32 1 DECLARE @bool bit; 2 SET @bool = 1; 3 IF @bool = 1 4 PRINT 'TRUE'; 5 ELSE 6 PRINT 'FALSE';

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 예제 33

4. T-SQL의 데이터 형식 날짜 / 시각 데이터 형식

4. T-SQL의 데이터 형식 날짜 / 시각 데이터 형식

4. T-SQL의 데이터 형식 날짜 / 시각 데이터 형식 날짜 / 시각 데이터 형식은 각각 내부적으로 저장되는 고유한 형식이 있다. 일반적으로 응용 프로그램에서는 임의적으로 포맷된 날짜 / 시각 표현 형식을 필요로 하는 경우가 많은데, 이를 위해 꼭 알아두어야 하는 것 이 CONVERT 함수다. CONVERT 함수의 구문 여기서 data_type은 변환할 목표가 되는 데이터 형식이고, expression은 변환할 식이며, style은 변환할 때 사용하는 스타일이다.

4. T-SQL의 데이터 형식 날짜 / 시각 데이터 형식 대표적인 날짜 / 시각 변환용 스타일

4. T-SQL의 데이터 형식 날짜 / 시각 데이터 형식 CAST 함수: CONVERT와 비슷한 함수. 그러나 CAST 함수는 CONVERT와는 달리 style을 지정할 수 없다는 단점이 있다. CAST 함수의 구문 CONVERT를 사용해보자. - GETDATE 함수로 얻은 현재 날짜 및 시각을 CONVERT 함수를 통해 두 개의 문자열 변수에 할당한 후, 결과 집합으로 출력해보자. 예제 34

4. T-SQL의 데이터 형식 날짜 / 시각 데이터 형식 1 DECLARE @date char(6), @hms char(8); 2 SET @date = CONVERT( char(6), GETDATE(), 12 ); 3 SET @hms = CONVERT( char(8), GETDATE(), 8 ); 4 SELECT @date AS '날짜', @hms AS '시각';

4. T-SQL의 데이터 형식 이진 데이터 형식 이진(binary) 데이터 : 이미지 파일이나 실행 프로그램과 같은, 텍스트가 아닌 데이터를 말한다(예: GIF 그래픽 파일). binary가 고정 길이고, varbinary가 가변 길이인 것은 char와 varchar의 경우와 같다. binary와 varbinary 데이터 형식은 최대 8000바이트 저장 가능 이보다 큰 데이터를 저장하고자 할 때는 varbinary(MAX)를 사용 varbinary(MAX)는 varchar(MAX), nvarchar(MAX)와 같은 장점이 있으므로, image 데이터 형식 대신 사용할 것을 권장한다.

4. T-SQL의 데이터 형식 이진 데이터 형식

4. T-SQL의 데이터 형식 이진 데이터 형식 이진 데이터를 16진수로 입력해보자. - 아주 작은 데이터일 때만 가능하며, 실용성은 적다. 예제 35 1 DECLARE @test_binary binary(4); 2 SET @test_binary = 0x41424344; 3 PRINT @test_binary;

4. T-SQL의 데이터 형식 BLOB 데이터 형식 BLOB(Binary Large Object): 대규모의 데이터를 의미한다. T-SQL에서는 [표 8-10]과 같은 BLOB 데이터 형식을 지원한다 BLOB 데이터 형식은 세 가지가 있으며, 처리 방식이 모두 동일하다. 처리 방식은 행 외부 저장 방식과 행 내부 저장 방식으로 구분된다. text, ntext, image 형식 대신 varchar(MAX), nvarchar(MAX), varbinary(MAX) 형식을 사용하기를 권장한다.

4. T-SQL의 데이터 형식 BLOB 데이터 형식

4. T-SQL의 데이터 형식 기타 데이터 형식

4. T-SQL의 데이터 형식 별칭 데이터 형식 별칭 데이터 형식: 지금까지 알아본 시스템 제공 데이터 형식을 이용 하여 사용자가 추가로 정의하는 데이터 형식이다. 구문 schema_name : 스키마 이름이다. type_name : 정의하려는 별칭 데이터 형식의 이름이다. base_type : 시스템 제공 데이터 형식의 이름이다. 자체 형식은 sysname이며, 다음 중 하나다.

4. T-SQL의 데이터 형식 별칭 데이터 형식 precision과 scale : decimal 또는 numeric 형식에서 전체 자릿수와 소수점 이하 자릿수를 표시하는 음이 아닌 정수이다. NULL 또는 NOT NULL : 해당 형식이 NULL 값을 보관할 수 있는지 여부를 지정한다. 이를 지정하지 않으면 기본값은 NULL이다.

4. T-SQL의 데이터 형식 별칭 데이터 형식 myname 형식을 정의해보자. 예제 36 1 USE AdventureWorksLT; 2 CREATE TYPE myname 3 FROM varchar(10) NOT NULL;

4. T-SQL의 데이터 형식 별칭 데이터 형식 정의한 myname 형식을 확인해보자. 예제 37 EXEC sp_help

4. T-SQL의 데이터 형식 별칭 데이터 형식 myname 형식으로 변수를 선언해보자. 예제 38 1 DECLARE @var myname = '홍길동'; 2 PRINT @var;

4. T-SQL의 데이터 형식 별칭 데이터 형식 구문 schema_name : 스키마 이름이다. type_name : 별칭 데이터 형식의 이름이다. 별칭 데이터 형식이 테이 블 또는 다른 개체(CHECK 제약조건, 함수, 저장 프로시저, 트리거 등) 에 의해 참조되는 경우에는 삭제할 수없다. 해당 개체에서 데이터 형식을 바꾸거나 개체를 삭제한 후에 별칭 데이터 형식의 삭제가 가능하다.

4. T-SQL의 데이터 형식 별칭 데이터 형식 myname 형식을 삭제해보자. - myname 형식은 데이터베이스 개체에 의해 참조되고 있지 않으므로 삭제가 가능하다. 예제 39 1 USE AdventureWorksLT; 2 DROP TYPE myname;

4. T-SQL의 데이터 형식 NULL과 NOT NULL 중요도가 높은 것에서 낮은 것 순으로 열의 순서를 재정렬 어느 선에서 위쪽은 모두 NOT NULL이, 아래쪽은 모두 NULL이 되도록 NULL 또는 NOT NULL을 적용하고, 이 상태는 항상 유지되도록 한다. bit 형식은 가급적 NOT NULL로 한다. 예외적인 적용이 필요할 경우에는 NULL 또는 NOT NULL을 수정한다 (예: 중요도는 높지만 처음에 삽입할 때는 무조건 NULL을 넣고, 트리거 에 의해 그 값이 자동으로 계산되어 수정되어야 하는 경우가 있다).

4. T-SQL의 데이터 형식 NULL과 NOT NULL 경험이 적을 때는 NOT NULL을 적용하는 경우가 많으나 NOT NULL 열은 삽입할 때 반드시 의미 있는 값을 부여해야 하므로, 행 자체를 삽입할 수 없는 난감한 상황이 발생할 수도 있다. IDENTITY 속성은 테이블에 ID 열을 만든다. ID 열이란 행을 식별 할 수 있는 고유한 일련번호가 자동으로 부여되는 열을 가리키는데, 대부분 기본 키(primary key) 열을 ID 열로 많이 만든다. 기본 키에 IDENTITY 속성을 부여하면 행을 삽입할 때마다 기본 키의 값이 기존의 값과 중복되지 않는지 조사할 필요가 없고, 자동으로 관리 되어 정확하고 편리하다. ID 열은 한 테이블에 최대 하나만 존재할 수 있다.

4. T-SQL의 데이터 형식 IDENTITY 속성 IDENTITY 속성은 정수 데이터 형식의 열에만 적용될 수 있다. 구문 초기 값 : 첫째 행에 적용되는 일련번호의 값이다. 증분 값 : 이어서 부여되는 값들 사이의 차이다.

4. T-SQL의 데이터 형식 IDENTITY 속성 IDENTITY 속성을 사용해보자. 예제 40 1 USE Test1DB; 2 GO 3 CREATE TABLE tbl1 ( 4 tbl_id int IDENTITY(100, 10) 5 ); 6 GO 7 INSERT tbl1 DEFAULT VALUES; 8 INSERT tbl1 DEFAULT VALUES; 9 INSERT tbl1 DEFAULT VALUES; 10 SELECT * FROM tbl1;

4. T-SQL의 데이터 형식 IDENTITY 속성 tbl_id 열 값이 110인 행을 삭제하고 한 행을 삽입해 보자. 예제 41 1 USE Test1DB; 2 DELETE tbl1 3 WHERE tbl_id = 110; 4 INSERT tbl1 DEFAULT VALUES; 5 SELECT * 6 FROM tbl1 7 ORDER BY tbl_id;

4. T-SQL의 데이터 형식 IDENTITY 속성 IDENTITY 초기값을 확인하려면 IDENT_SEED 함수를, 증분값을 확인 하려면 IDENT_INCR 함수를 사용하면 되고, 방금 삽입된 IDENTITY 값을 확인하려면 @@IDENTITY 스칼라 함수를 호출하면 된다. SQL Server 6.5까지는‘@@’로 시작되는 전역 변수가 있었지만, SQL Server 7.0부터는 이를 더 이상 전역 변수라고 하지 않고, 스칼라 함수라고 한다. @@IDENTITY 함수는 반드시 행을 삽입한 직후에 호출해야 한다. 왜냐하면 다른 테이블에서 IDENTITY 값이 삽입되면 @@IDENTITY 함수의 반환 값이 바뀌기 때문이다.

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';

4. T-SQL의 데이터 형식 IDENTITY 속성 IDENTITY_INSERT 옵션 설정 구문 IDENTITY_INSERT 옵션을 ON으로 설정하면 INSERT 문에서 ID 열 을 지정할 수 있을 뿐 아니라, 임의의 값을 삽입할 수도 있다. 그러나 기 존에 삽입된 IDENTITY 값을 갱신할 수는 없다. 갱신하려면 기존 행을 삭제하고, 다른 값을 삽입해야 한다(한 테이블에서 최대 하나뿐인 ID 열 을 지정할 때 IDENTITYCOL이라는 키워드를 사용할수 있다.). IDENTITY_INSERT 옵션을 ON으로 설정하는 것은 특별한 경우 외에는 권장하지 않으며, 작업을 완료한 직후에 OFF로 원상 복구해야 한다.

4. T-SQL의 데이터 형식 IDENTITY 속성 IDENTITY_INSERT 옵션을 설정해보자. 예제 43 1 USE Test1DB; 2 SET IDENTITY_INSERT tbl1 ON; 3 4 INSERT tbl1 (tbl_id) 5 VALUES (115); 6 GO 7 UPDATE tbl1 8 SET tbl_id = 125 9 WHERE IDENTITYCOL = 120; 10 GO 11 DELETE tbl1 12 WHERE IDENTITYCOL = 120; 13 INSERT tbl1 (tbl_id) 14 VALUES (125); 15 GO 16 SELECT * 17 FROM tbl1 18 ORDER BY tbl_id; 19 20 SET IDENTITY_INSERT tbl1 OFF

4. T-SQL의 데이터 형식 IDENTITY 속성