5. T-SQL의 시스템 함수 시스템 제공 함수 시스템 제공 함수

Presentation on theme: "5. T-SQL의 시스템 함수 시스템 제공 함수 시스템 제공 함수"— Presentation transcript:

1 5. T-SQL의 시스템 함수 시스템 제공 함수 시스템 제공 함수
: SQL Server 시스템이 기본적으로 제공하는 함수. 시스템 제공 함수는 행 집합 함수, 집계 함수, 스칼라 함수로 구분된다. 행 집합 함수 : 행 집합(rowset) 함수는 테이블 참조 대신 사용할 수 있는 개체를 반환한다.

2 5. T-SQL의 시스템 함수 시스템 제공 함수 행 집합 함수의 종류

3 5. T-SQL의 시스템 함수 집계 함수 집계(aggregate) 함수: 값 집합에 대한 계산을 수행한 후 단일 값을
돌려준다. GROUP BY 절이 포함된 SELECT 문에서 많이 사용된다. 주요 집계 함수

4 5. T-SQL의 시스템 함수 스칼라 함수 스칼라(scalar) 함수: 단일 값에 대한 계산을 수행한 후 단일 값을 돌려준다.
날짜 및 시간 함수 : datetime 또는 smalldatetime 데이터 형식의 값에 대한 각종 조작을 수행하는 함수 : [표 8-14]의 구문 중에서 datepart로 표시된 매개변수에는 [표 8-15]와 같은 값을 사용할 수 있다(날짜 부분과 약어 모두 사용 가능하다).

5 5. T-SQL의 시스템 함수 스칼라 함수

6 5. T-SQL의 시스템 함수 스칼라 함수

7 5. T-SQL의 시스템 함수 스칼라 함수

8 5. T-SQL의 시스템 함수 스칼라 함수 오늘부터 60일 후의 날짜를 확인해보자. 예제 44
SELECT DATEADD(day, 60, GETDATE()) AS ’60일 후’;

9 5. T-SQL의 시스템 함수 스칼라 함수 오늘부터 2015년 크리스마스까지의 날수를 계산해보자. 예제 45
SELECT DATEDIFF(day, GETDATE(), '12/25/2015') AS '2015년 크리스마스까지 며칠‘;

10 5. T-SQL의 시스템 함수 수치 연산 함수 / 문자열 함수
수치 연산 함수: 수치 연산 함수는 각종 수학 계산을 하기 위한 함수로서, 다음과 같은 종류가 있다. ABS, ACOS, ASIN, ATAN, ATN2, CEILING, COS, COT, DEGREES, EXP, FLOOR, LOG, LOG10, PI, POWER, RADIANS, RAND, ROUND, SIGN, SIN, SQRT, SQUARE, TAN 문자열 함수: 문자열을 조작하는 함수로, 함수들 중에서 가장 많이 사용 된다.

11 5. T-SQL의 시스템 함수 문자열 함수

12 5. T-SQL의 시스템 함수 문자열 함수

13 5. T-SQL의 시스템 함수 문자열 함수 A와 a의 아스키 코드 값을 출력해보자. 예제 46
1 SELECT ASCII('ABC') AS 'ASCII(''ABC'')', 2 ASCII('abc') AS 'ASCII(''abc'')';

14 5. T-SQL의 시스템 함수 문자열 함수 아스키 코드 65와 97에 해당하는 문자를 출력해보자. 예제 47
1 SELECT CHAR(65) AS 'CHAR(65)', 2 CHAR(97) AS 'CHAR(97)';

15 5. T-SQL의 시스템 함수 문자열 함수 ‘ABCDEF’에서‘CDE’가 시작되는 위치를 출력해보자. - 주어진 문자열의 왼쪽 3문자와 오른쪽 3문자를 결합해보자. 예제 48 PRINT CHARINDEX('CDE', 'ABCDEF'); 예제 49 1 varchar(26); 2 = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'; 3 PRINT 3) + 3);

16 5. T-SQL의 시스템 함수 문자열 함수 뒷부분 공백이 문자열 길이로 계산되는지 확인해보자. 예제 50
PRINT LEN('A B ');

17 5. T-SQL의 시스템 함수 문자열 함수 주어진 문자열을 전부 소문자와 대문자로 변환한 것을 연결하여 출력해보자. 예제 51
1 varchar(26); 2 = 'AaBbCcDdEe'; 3 PRINT +

18 5. T-SQL의 시스템 함수 문자열 함수 LTRIM(RTRIM( ))을 통하여 좌우 양단의 공백을 제거해보자. 예제 52
PRINT '#' + LTRIM(RTRIM(' ABCDE ')) + '#’

19 5. T-SQL의 시스템 함수 문자열 함수 문자열 패턴의 위치를 확인해보자. 예제 53
1 varchar(26); 2 = 'CBA AAB AAC'; 3 PRINT 4 PRINT 5 PRINT

20 5. T-SQL의 시스템 함수 문자열 함수 모든‘AA’를‘ZZ’로 치환해보자. 알파벳 문자열을 반전시켜보자. 예제 54
PRINT REPLACE('AA1AA2AA3', 'AA', 'ZZ'); 예제 55 PRINT REVERSE('ABCDEFGHIJKLMNOPQRSTUVWXYZ');

21 5. T-SQL의 시스템 함수 문자열 함수 ‘ABC’를 3번 반복하여 문자열을 만들어보자. 키보드를 두드리지 않고 공백 50개를 만들어보자. 예제 56 PRINT REPLICATE('ABC', 3); 예제 57 PRINT '#' + SPACE(50) + '#‘;

22 5. T-SQL의 시스템 함수 문자열 함수 문자열 중에서 세 번째부터 두 개의 문자로 부분 문자열을 만들어보자. 예제 58
PRINT SUBSTRING('ABCDEFGHIJKLMNOPQRSTUVWXYZ', 3, 2);

23 6. 무결성과 제약 조건 데이터 무결성 데이터 무결성(integrity, 無缺性): 데이터베이스에 저장된 데이터의
일관성과 정확성을 지키는 것을 말하며, 데이터가 무결성을 가지도록 하는 행위를 무결성 강화(enforcement)라고 한다.

24 6. 무결성과 제약 조건 데이터 무결성의 종류 도메인(Domain) 무결성 : 열의 값에 관련된 무결성으로, 이를 강화시키
는 방법에는 데이터 형식, NULL / NOT NULL, 기본값, 체크 / 규칙 등이 있다. 열 무결성이라고도 한다. 엔터티(Entity) 무결성 : 테이블의 모든 행들이 유일하게 식별되는 무결성 으로, 대부분의 경우 기본 키에 의해 강화된다. 테이블 무결성이라고도 한다. 참조(Referential) 무결성 : 서로 관계를 맺은 두 테이블 사이의 무결성 으로, 외래 키에 의해 강화된다. 자식 테이블의 외래 키는 반드시 부모 테이블의 기본 키 값으로 존재하는 값을 가져야 하며, 외래 키가 참조하는 기본 키 값은 변경 또는 삭제가 금지된다.

25 6. 무결성과 제약 조건 선언적 / 절차적 데이터 무결성
선언적(declarative) 데이터 무결성: DDL로 개체를 정의할 때 제약 조건, 개체 기본값, 규칙 등에 의해 선언적으로 강화되는 데이터 무결 성이다. 강화하기 쉬울 뿐만 아니라 실행할 때 더 효율적이기 때문에 절차적 데이터 무결성보다 선호된다. 선언적 참조 무결성(Declarative Referential Integrity)은 약어로 DRI라고 한다. 절차적(procedural) 데이터 무결성: 저장 프로시저, 트리거 등의 프로그램에 의해 절차적으로 강화되는 데이터 무결성을 말한다. 프로 그램 작성이 어렵고 실행할 때 비효율적이므로 선언적 데이터 무결성 보다 선호되지는 않지만, 강력하고 정교한 측면에서는 선언적 데이터 무결성을 훨씬 능가한다.

26 6. 무결성과 제약 조건 기본값 기본값: 행을 삽입하거나 갱신할 때 열의 값을 지정하지 않으면 미리
정의된 값을 자동으로 넣어주는 메커니즘이다. 제약 조건 기본값(DEFAULT): 제약 조건(constraint)으로 기본값을 정의하는 것이며, 기본값이 특정 열 하나에만 적용된다.

27 6. 무결성과 제약 조건 제약 조건 기본값 구문 CONSTRAINT constraint_name : 생략은 가능하지만, 생략 시 시스템이 복잡한 제약 조건 이름을 부여하므로 나중에 이 제약 조건을 관리하기가 더 어려워진다. constant_expression : 상수 값만 포함된 식으로서, 기본값으로 사용 될 상수, NULL, 시스템 함수 호출 또는 수치 연산식이 온다. 열 이름 이나 다른 데이터베이스 개체의 이름은 포함될 수 없다.

28 6. 무결성과 제약 조건 제약 조건 기본값 제약 조건 기본값을 활용해보자. 예제 59 1 USE Test1DB;
2 IF OBJECT_ID('test1', 'U') IS NOT NULL 3 DROP TABLE test1; 4 GO 5 CREATE TABLE test1 ( 6 sum int NOT NULL 7 DEFAULT 0 8 , reg_date char(6) NOT NULL 9 DEFAULT CONVERT(char(6), GETDATE(), 12) 10 ); 11 INSERT test1 (sum, reg_date) VALUES (DEFAULT, DEFAULT); 12 INSERT test1 DEFAULT VALUES; 13 SELECT * FROM test1;

29 6. 무결성과 제약 조건 제약 조건 기본값

30 6. 무결성과 제약 조건 개체 기본값 개체 기본값(defaults): 개체로서 기본값을 정의하는 것인데, 다음
구문과 같이 정의하고 적용하며, 해제한다. CREATE DEFAULT 문 : 개체 기본값을 정의한다. schema_name : 스키마 이름이고 default_name은 개체 기본값의 이름이다.

31 6. 무결성과 제약 조건 개체 기본값 constant_expression : 상수 값만 포함된 식으로서 열 이름이나 다른
데이터베이스 개체의 이름은 포함할 수 없다. 기본값으로 사용될 상수, NULL, 시스템 함수 호출 또는 수치 연산식이 온다. 정의된 개체 기본값은 sp_bindefault(‘d’가 하나인 것에 유의한다) 저장 프로시저를 이용하여 바인딩(binding, 일반적으로 프로그램 내의 변수를 실제 값으로 치환하는 것을 의미한다) 작업을 거쳐야 실제 열 또는 별칭 데이터 형식에 적용될 수 있다. object_name : 개체 기본값이 바인딩될 열 또는 별칭 데이터 형식의 이름이다.

32 6. 무결성과 제약 조건 개체 기본값 futureonly_flag : 개체 기본값을 별칭 데이터 형식에 바인딩하는 경우
에만 사용된다. futureonly_flag는 varchar(15)이며, 기본값은 NULL 이다. 이 값이 futureonly로 설정된 경우에는 해당 데이터 형식이 바인딩 된 기존의 열이 새 기본값을 상속하는 것을 방지한다. futureonly_flag가 NULL인 경우, 이 별칭 데이터 형식이 바인딩된 모든 열에 이 개체 기본값 이 소급 적용된다. 바인딩된 열 또는 별칭 데이터 형식으로부터 개체 기본값을 언바인딩 (unbinding)하려면 sp_unbindefault 저장 프로시저를 이용해야 한다. SQL Server 이후 버전에서는 개체 기본값 기능이 제거되므로 제약 조건 기본값(DEFAULT)을 사용할 것을 권장한다.

33 6. 무결성과 제약 조건 개체 기본값 개체 기본값을 활용하는 예를 살펴보자. 예제 60 1 USE Test1DB;
2 IF OBJECT_ID('test1', 'U') IS NOT NULL 3 DROP TABLE test1; 4 GO 5 CREATE DEFAULT dfs_zero 6 AS 0; 7 GO 8 CREATE TABLE test1 ( 9 col_dfs int NOT NULL 10 ); 11 EXEC SP_BINDEFAULT 'dfs_zero', 'test1.col_dfs'; 12 INSERT test1 DEFAULT VALUES; 13 SELECT * FROM test1;

34 6. 무결성과 제약 조건 개체 기본값

35 6. 무결성과 제약 조건 유효성 검사 유효성 검사(validity check)
: 행을 삽입하거나 갱신할 때 열의 값이 정의된 규칙에 부합되는지를 검사하는 것이다. 여기에는 제약 조건 체크와 개체 규칙이 있다. 제약 조건 체크 : 체크(CHECK)는 제약 조건(constraint)으로 유효성 검사를 정의 한다.

36 6. 무결성과 제약 조건 제약 조건 체크 구문

37 6. 무결성과 제약 조건 제약 조건 체크 구문 설명 CONSTRAINT constraint_name : 생략은 가능하지만, 생략 시 시스템이 복잡한 제약 조건 이름을 부여하므로, 나중에 이 제약 조건을 관리하기가 더 어려워진다. 열 수준(column_check_constraint), 테이블 수준(table_check_ constraint) 체크 제약 조건을 정의할 수 있다. 일반적으로 한 열에 대 한 유효성 검사를 하려면 열 수준으로, 두 개 이상의 열들에 대한 유효성 검사(예: 두 열 값의 대소 비교 등)를 하려면 테이블 수준으로 정의한다. logical_expression : TRUE 또는 FALSE를 반환하는 논리식이다.

38 6. 무결성과 제약 조건 제약 조건 체크 열 수준의 제약 조건을 체크해보자. 예제 61 1 USE Test1DB;
2 IF OBJECT_ID('test1', 'U') IS NOT NULL 3 DROP TABLE test1; 4 GO 5 CREATE TABLE test1 ( 6 id int IDENTITY 7 , jumin char(14) NOT NULL 8 , zip char(7) NOT NULL 9 CHECK (zip LIKE '[0-9][0-9][0-9]-[0-9][0- 9][0-9]') 10 ); 11 INSERT test1 (jumin, zip) VALUES (' ', '112=119'); 12 INSERT test1 (jumin, zip) VALUES (' ', ' '); 13 SELECT * FROM test1;

39 6. 무결성과 제약 조건 제약 조건 체크

40 6. 무결성과 제약 조건 제약 조건 체크 테이블 수준의 제약 조건을 체크해보자. 예제 62 1 USE Test1DB;
2 IF OBJECT_ID('test2', 'U') IS NOT NULL 3 DROP TABLE test2; 4 GO 5 CREATE TABLE test2 ( 6 start_date char(20), 7 end_date char(20), 8 CONSTRAINT CHK_dates 9 CHECK (start_date <= end_date) 10 ); 11 INSERT test2 (start_date, end_date) VALUES (' ', ' '); 12 INSERT test2 (start_date, end_date) VALUES (' ', ' '); 13 SELECT * FROM test2;

41 6. 무결성과 제약 조건 제약 조건 체크

42 6. 무결성과 제약 조건 개체 규칙 규칙(rules)은 개체로 정의하는데, 다음 구문과 같이 정의하고 적용 및 해제한다.
CREATE RULE 문 : 규칙을 정의한다. schema_name : 스키마 이름이다. rule_name : 규칙의 이름이다.

43 6. 무결성과 제약 조건 개체 규칙 condition_expression : WHERE 절에서 사용할 수 있는 조건식이
올 수 있다. 단, 열 이름이나 다른 데이터베이스 개체의 이름을 포함할 수 없다. 조건식은 하나의 변수를 포함하며, 이 변수로 시작해야 한다. 이 변수는 최종적으로 바인딩되는 열에 매핑된다. 정의된 규칙은 sp_bindrule 저장 프로시저를 이용하여 바인딩 작업을 거쳐야 실제 열 또는 별칭 데이터 형식에 적용될 수 있다. object_name : 규칙이 바인딩될 열 또는 별칭 데이터 형식의 이름이다. futureonly_flag : 규칙을 별칭 데이터 형식에 바인딩하는 경우에만 사용된다.

44 6. 무결성과 제약 조건 개체 규칙 futureonly_flag는 varchar(15)이며, 기본값은 NULL이다. 이 값이
상속하는 것을 방지한다. futureonly_flag가 NULL인 경우, 바인딩되는 별칭 데이터 형식을 사용 하는 모든 열에 이 규칙이 소급 적용된다. 바인딩된 열 또는 별칭 데이터 형식으로부터 규칙을 언바인딩하려면 sp_unbinrule 저장 프로시저를 이용해야 한다.

45 6. 무결성과 제약 조건 개체 규칙 개체 규칙을 활용해보자. 예제 63 1 USE Test1DB; 2 GO
3 CREATE RULE rul_jumin 4 AS 5 @jumin LIKE 6 '[0-9][0-9][0-9][0-9][0-9][0-9]-[0-9][0- 9][0-9][0-9][0-9][0-9][0-9]'; 7 GO 8 EXEC sp_bindrule 'rul_jumin', 'test1.jumin' 9 INSERT test1 (jumin, zip) VALUES ('800101# ', ' '); 10 INSERT test1 (jumin, zip) VALUES (' ', ' '); 11 SELECT * FROM test1;

46 6. 무결성과 제약 조건 개체 규칙

47 6. 무결성과 제약 조건 기타 제약 조건 설정 기본 키, 유일성, 외래 키 등의 기타 제약 조건을 설정하는 방법과 연속
삭제 / 갱신의 설정 방법에 대해서 알아볼 것이다. 기본 키 제약 조건 설정 기본 키(primary key) 제약 조건의 특징 테이블 당 최대 하나의 기본 키 제약 조건만 설정할 수 있다. 기본 키의 값은 유일해야 하며, NULL을 허용하지 않는다. 기본 키 제약 조건에 의해 자동으로 인덱스가 만들어진다. 기본적으로 클러스터(clustered) 인덱스가 만들어지지만, 명시할 경우에는 비클 러스터(nonclustered) 인덱스로 만들 수도 있다.

48 6. 무결성과 제약 조건 기타 제약 조건 설정 기본 키 제약 조건은 하나 이상의 열을 기본 키로 설정하는 것으로
CREATE TABLE 문으로 설정할 수도 있으나, 일반적으로 다음과 같은 ALTER TABLE 문으로 많이 설정한다. database_name : 데이터베이스 이름이다. schema_name : 스키마 이름이다. table_name : 기본 키를 설정할 테이블 이름이다.

49 6. 무결성과 제약 조건 기타 제약 조건 설정 CONSTRAINT constraint_name : 생략은 가능하지만, 생략 시
시스템이 복잡한 제약 조건 이름을 부여하므로, 나중에 이 제약 조건을 관리하기가 더 어려워진다. CLUSTERED 또는 NONCLUSTERED : 클러스터 또는 비클러스터 인덱스를 만들도록 지정한다. 하나 이상의 column으로 기본 키를 구성하는 열 이름을 지정한다. 열이 두 개 이상이면 복합 기본 키가 만들어진다.

50 6. 무결성과 제약 조건 기타 제약 조건 설정 CREATE / ALTER TABLE 문으로 기본 키 제약 조건을 설정해보자.
예제 64 1 USE Test1DB; 2 IF OBJECT_ID('test1', 'U') IS NOT NULL 3 DROP TABLE test1; 4 GO 5 IF OBJECT_ID('test2', 'U') IS NOT NULL 6 DROP TABLE test2; 7 GO 8 CREATE TABLE test1 ( 9 pk1 int PRIMARY KEY, 10 uq1 int NULL 11 ); 12 CREATE TABLE test2 ( 13 pk2 int NOT NULL, 14 fk2 int NOT NULL 15 ); 16 GO 17 ALTER TABLE test2 18 ADD PRIMARY KEY NONCLUSTERED ( pk2, fk2 );

51 6. 무결성과 제약 조건 기타 제약 조건 설정

52 6. 무결성과 제약 조건 유일성 제약 조건 설정 유일성(unique) 제약 조건의 특징
한 테이블에 여러 개의 유일성 제약 조건을 설정할 수 있다. 적용되는 열의 값이 유일해야 하지만 널은 허용한다(기본 키는 널을 허용하지 않으므로 여기에 유일성 제약 조건을 적용할 수 없다). 그러나 널은 한 열에 하나만 허용되므로 널을 허용한다는 것이 별 의미는 없다. 유일성 제약 조건에 의해 자동으로 유일성 인덱스가 만들어진다. 주로 대체 키(alternate key, 기본 키로 지정되지 않은 후보 키)에 적용된다. 유일성 제약 조건은 외래 키 제약 조건에서 참조할 수 있다.

53 6. 무결성과 제약 조건 유일성 제약 조건 설정 유일성 제약 조건은 CREATE TABLE 문으로 설정할 수도 있으나,
일반적으로 다음과 같은 구문의 ALTER TABLE 문으로 많이 설정함 database_name : 데이터베이스 이름이다. schema_name : 스키마 이름이다. table_name : 유일성 제약 조건을 설정할 테이블 이름이다.

54 6. 무결성과 제약 조건 유일성 제약 조건 설정 ALTER TABLE 문으로 유일성 제약 조건을 설정해보자. - test1 테이블과 uq1 열은 [예제 64]에서 만들어 두었던 것이다. 예제 65 1 USE Test1DB; 2 ALTER TABLE test1 3 ADD CONSTRAINT unique_constr 4 UNIQUE ( uq1 );

55 6. 무결성과 제약 조건 외래 키 제약 조건 설정 외래 키(foreign key) 제약 조건의 특징
같거나 다른 테이블에 있는 기본 키 또는 유일성 제약 조건이 설정된 열 (들)에 대한 참조를 정의한다. 즉, 참조 무결성을 강화한다. 자식 테이블의 외래 키는 반드시 부모 테이블의 기본 키(또는 유일성 제약 조건 열)의 값으로 존재하는 값을 가져야 한다. 자식 테이블의 외래 키가 참조하는 부모 테이블의 기본 키(또는 유일성 제약 조건 열)의 값은 변경되거나 삭제될 수 없다. 이 값이 꼭 필요할 경우, 해당 기본 키(또는 유일성 제약 조건 열)를 참조하는 모든 자식 테이블의 행을 먼저 삭제해야 한다.

56 6. 무결성과 제약 조건 외래 키 제약 조건 설정 구문 외래 키 제약 조건은 자동으로 인덱스를 만들지 않는다. 그러나 외래
키는 조인할 때 자주 사용되므로, 인덱스를 설정할 것을 적극 권장한다. 임시 테이블에 대해서는 외래 키를 설정할 수 없다(무시된다). 구문

57 6. 무결성과 제약 조건 외래 키 제약 조건 설정 test2 테이블의 fk2 열이 test1 테이블의 pk1 열을 참조하도록 설정해보자. 예제 66 1 USE Test1DB; 2 ALTER TABLE test2 3 ADD CONSTRAINT fk2_constr 4 FOREIGN KEY ( fk2 ) REFERENCES test1 ( pk1 );

58 6. 무결성과 제약 조건 외래 키 제약 조건 설정 FOREIGN KEY 절을 생략한 외래 키 제약 조건을 설정해보자.
예제 67 1 USE Test1DB; 2 IF OBJECT_ID('test2', 'U') IS NOT NULL 3 DROP TABLE test2; 4 IF OBJECT_ID('test1', 'U') IS NOT NULL 5 DROP TABLE test1; 6 GO 7 CREATE TABLE test1 ( 8 pk1 int PRIMARY KEY, 9 fk1 int NOT NULL REFERENCES test1 ( pk1 ) 11 ); 12 EXEC sp_help test1;

59 6. 무결성과 제약 조건 외래 키 제약 조건 설정

60 6. 무결성과 제약 조건 연속 삭제 / 갱신 설정 자식 테이블의 외래 키가 참조하는 부모 테이블의 기본 키 값은 변경
하거나 삭제할 수 없고, 이것이 꼭 필요할 경우에는 기본 키를 참조 하는 모든 자식 테이블의 행을 먼저 삭제해야 한다. 부모 테이블의 기본 키 값을 자주 변경하거나 삭제해야 하는 경우가 있다면, 매우 불편할 뿐만 아니라 많은 시간을 허비하게 된다. 이때 필요한 것이 연속 삭제 / 갱신(cascade delete / update)이다. 연속 삭제 / 갱신의 필요성을 나타내주는 개념도

61 6. 무결성과 제약 조건 외래 키 제약 조건 설정 ALTER TABLE 문으로 연속 삭제 / 갱신을 설정하는 구문
database_name : 데이터베이스 이름이다. schema_name : 스키마 이름이다. table_name : 테이블 이름이다. ON DELETE CASCADE : 연속 삭제를 설정한다. ON UPDATE CASCADE : 연속 갱신을 설정한다.

62 6. 무결성과 제약 조건 외래 키 제약 조건 설정 일반적으로 연속 갱신보다 연속 삭제를 더 많이 설정한다(둘 다 설정
하는 경우도 있다). 복잡한 트리나 망 형태의 관계가 이루어질 때처럼 부모-자식 테이블 간의 관계가 복잡해지면 ON DELETE CASCADE 또는 ON UPDATE CASCADE로 설정하는 연속 삭제 / 갱신이 오작동되는 사례가 많다. 이때는 좀 더 정교한 트리거(trigger)에 의해 연속 삭제 / 갱신을 설정 할 필요가 있다.

63 6. 무결성과 제약 조건 외래 키 제약 조건 설정 [그림 8-7]의 개념을 구현해보자. 예제 68 1 USE Test1DB;
2 IF OBJECT_ID('test1', 'U') IS NOT NULL 3 DROP TABLE test1; 4 IF OBJECT_ID('test2', 'U') IS NOT NULL 5 DROP TABLE test2; 6 GO 7 CREATE TABLE test1 ( 8 pk1 int IDENTITY PRIMARY KEY 9 ); 10 CREATE TABLE test2 ( 11 pk2 int IDENTITY PRIMARY KEY, 12 fk2 int FOREIGN KEY (fk2)

64 6. 무결성과 제약 조건 외래 키 제약 조건 설정 14 REFERENCES test1( pk1 )
ON DELETE CASCADE ON UPDATE CASCADE 17 ); 18 GO 19 INSERT test1 DEFAULT VALUES; 20 INSERT test2 (fk2) VALUES (1); 21 INSERT test2 (fk2) VALUES (1); 22 SELECT * FROM test1; 23 SELECT * FROM test2; 24 GO 25 DELETE test1 WHERE pk1 = 1; 26 SELECT * FROM test1; 27 SELECT * FROM test2;

65 6. 무결성과 제약 조건 외래 키 제약 조건 설정

66 6. 무결성과 제약 조건 기타 데이터 무결성과 강화 방법의 나머지 부분들을 살펴보자. 제약 조건 비활성화 및 활성화
설정해 놓은 제약 조건을 일시적으로 적용하고 싶지 않을 때 제약 조건을 삭제했다가 다시 만드는 일은 비효율적이다. 이때는 제약 조건을 비활 성화시켰다가 필요할 때 다시 활성화시키는 것이 좋다. 제약 조건을 비활성화 또는 활성화시키는 구문

67 6. 무결성과 제약 조건 기타 체크 제약 조건을 비활성화시켜보자. 예제 69 1 USE Test1DB;
2 IF OBJECT_ID('test2', 'U') IS NOT NULL 3 DROP TABLE test2; 4 IF OBJECT_ID('test1', 'U') IS NOT NULL 5 DROP TABLE test1; 6 GO 7 CREATE TABLE test1 ( 8 start_date char(20), 9 end_date char(20), 10 CONSTRAINT CHK_dates CHECK (start_date <= end_date) 12 ); 13 GO 14 ALTER TABLE test1 15 NOCHECK

68 6. 무결성과 제약 조건 기타 16 CONSTRAINT CHK_dates; 17 EXEC sp_help test1; 18 GO
19 INSERT test1 (start_date, end_date) VALUES (' ', ' '); 20 SELECT * FROM test1;

69 6. 무결성과 제약 조건 기타 체크 제약 조건을 활성화시켜보자. 예제 70 1 USE Test1DB;
2 ALTER TABLE test1 3 CHECK 4 CONSTRAINT CHK_dates; 5 EXEC sp_help test1;

70 7. T-SQL 명령문 공통 테이블 식 공통 테이블 식(CTE, Common Table Expression): 임시로 이름
이 부여되는 결과 집합이다. CTE는 단순 쿼리에서 파생되고, 하나의 SELECT, INSERT, UPDATE 또는 DELETE 문 실행 범위 내에서 정의되며, SELECT 문의 일부로 CREATE VIEW 문 내에서도 사용할 수 있다. 공통 테이블 식은 개체로 저장되지 않고 쿼리 지속 시간 동안만 존재한다는 점에서 파생 테이블과 비슷하지만, 자신에 대한 참조가 가능하며(이를 재귀 공통 테이블 식이라 한다) 동일 쿼리에서 여러 번 참조될 수 있다는 점 에서 파생 테이블과는 다르다.

71 7. T-SQL 명령문 공통 테이블 식 구문 expression_name : 공통 테이블 식의 식별자다. expression_name 은 같은 WITH<common_table_expression> 절에서 정의된 다른 공통 테이블 식의 이름과는 달라야 하지만 기본 테이블 또는 뷰의 이름과 같을 수는 있다(쿼리에서 expression_name에 대한 모든 참조는 기본 개체가 아니라 공통 테이블 식을 사용한다).

72 7. T-SQL 명령문 공통 테이블 식 column_name : 공통 테이블 식에서 지정하는 열 이름이다. 단일 CTE
정의 내에서는 이름이 중복되어서는 안 된다. 그리고 지정한 열 이름의 개수는 CTE_query_definition의 결과 집합에 있는 열 수와 반드시 일치 해야 한다. 쿼리 정의에서 모든 결과 열에 대한 고유한 이름을 제공한 경우 에만 열 이름 목록을 생략할 수 있다. CTE_query_definition : 공통 테이블 식을 채울 결과 집합을 위한 SELECT 문을 지정한다. CTE_query_definition의 SELECT 문은 CTE가 또 다른 CTE를 정의하지는 못한다는 점을 제외하고는 뷰를 만들 때와 동일한 요구 사항을 만족해야 한다.

73 7. T-SQL 명령문 공통 테이블 식 CTE_query_definition : 두 개 이상 정의되어 있을 경우에는 UNION
ALL, UNION, EXCEPT 또는 INTERSECT 등의 집합 연산자 중 하나를 사용하여 쿼리 정의를 결합해야 한다. 공통 테이블 식을 사용하면 하위 쿼리의 중복적인 사용을 방지할 수 있다.

74 7. T-SQL 명령문 공통 테이블 식 총 비용이 평균값보다 크고 운임이 총 비용의 5%를 초과하는 행을 조회해보자.
예제 71 1 USE AdventureWorksLT; 2 SELECT SalesOrderID, TotalDue, Freight 3 FROM SalesLT.SalesOrderHeader 4 WHERE TotalDue > ( SELECT AVG(TotalDue) FROM SalesLT.SalesOrderHeader WHERE YEAR(OrderDate) = '2004') 8 AND Freight > ( SELECT AVG(TotalDue) FROM SalesLT.SalesOrderHeader WHERE YEAR(OrderDate) = '2004') * 0.05;

75 7. T-SQL 명령문 공통 테이블 식

76 7. T-SQL 명령문 공통 테이블 식 공통 테이블 식을 사용하여 [예제 8-71]을 바꿔보자. - 7행에 있는‘ )’뒤에‘;’을 붙이면 구문 오류가 발생하므로 주의해야 한다. 예제 72 1 USE AdventureWorksLT; 2 WITH price_calc(year2004) 3 AS ( 4 SELECT AVG(TotalDue) 5 FROM SalesLT.SalesOrderHeader 6 WHERE YEAR(OrderDate) = '2004' 7 ) 8 SELECT SalesOrderID, TotalDue, Freight 9 FROM SalesLT.SalesOrderHeader 10 WHERE TotalDue > (SELECT year2004 FROM price_calc) AND Freight > (SELECT year2004 FROM price_ calc) * 0.05;

77 7. T-SQL 명령문 공통 테이블 식

78 7. T-SQL 명령문 공통 테이블 식 임시 테이블이 생성된 경우를 살펴보자. 예제 73
1 USE AdventureWorksLT; 2 SELECT p.ProductCategoryID, s.Name,AVG(p.ListPrice) AS Avg_ListPrice 3 INTO #Temp1 4 FROM SalesLT.Product p 5 JOIN SalesLT.ProductCategory s ON s.ProductCategoryID = p.ProductCategoryID 7 GROUP BY p.ProductCategoryID, s.Name; 8 9 SELECT ProductCategoryID, Name, Avg_ListPrice AS MaxAvg_ListPrice 10 FROM #Temp1 11 WHERE Avg_ListPrice = (SELECT MAX(Avg_ListPrice) FROM #Temp1); 12 13 DROP TABLE #Temp1;

79 7. T-SQL 명령문 공통 테이블 식

80 7. T-SQL 명령문 공통 테이블 식 [예제 73]에서는 범주별 정가 평균값을 구하고, 이 값들 중 최댓값을
구하기 위해 두 개의 SELECT 문으로 나누고 이 문들 사이의 매개 체로 임시 테이블을 사용했다. 그러나 임시 테이블은 인덱스를 사용할 수 없어 성능이 저하되는 등 여러 가지 문제가 발생하므로 되도록 사용 하지 않는 편이 좋다. [예제 74]는 임시 테이블보다 훨씬 더 효율적인 공통 테이블 식을 사용 하도록 바꾼 것이다.

81 7. T-SQL 명령문 공통 테이블 식 공통 테이블 식 사용으로 임시 테이블을 제거해보자. 예제 74
1 USE AdventureWorksLT; 2 WITH ProdList (ProductCategoryID, Name, Avg_ ListPrice) AS 3 ( 4 SELECT p.ProductCategoryID, s.Name, AVG(p.ListPrice) AS Avg_ListPrice 5 FROM SalesLT.Product p JOIN SalesLT.ProductCategory s ON s.ProductCategoryID = p.ProductCategoryID 8 GROUP BY p.ProductCategoryID, s.Name 9 ) 10 SELECT ProductCategoryID, Name, Avg_ListPrice AS MaxAvg_ListPrice 11 FROM ProdList 12 WHERE Avg_ListPrice = (SELECT MAX(Avg_ListPrice) FROM ProdList);

82 7. T-SQL 명령문 공통 테이블 식

83 7. T-SQL 명령문 공통 테이블 식 공통 테이블 식은 이 밖에도 자기 자신을 호출하도록 재귀적으로 구성
할 수도 있다. 이렇게 하면 사용자 정의 함수를 사용하는 것보다 더 간단하고 효율적으로 재귀적 처리를 수행할 수 있다. 재귀적 처리의 응용 분야는 트리 구조 검색, 조직 관리(예: 관리자와 부하 직원 관계), 반복적 계산(예: 팩토리얼 계산) 등 매우 다양하다.

84 7. T-SQL 명령문 TOP 절 TOP 절은 결과 집합에서 처음의 일부 행만 돌려주도록 제약하는 것
[그림 8-10]은 TOP 절의 개념도다. ListPrice 열 값이 오름차순 으로 정렬된 왼쪽의 결과 집합에 대해서 처음 3개의 행만 돌려주도록 TOP 3으로 제약한 것이다.

85 7. T-SQL 명령문 TOP 절 TOP 절의 구문 TOP 절은 SELECT, INSERT, UPDATE, MERGE 및 DELETE 문에서 사용할 수 있다. expression은 반환할 행의 수를 지정하는 숫자식이다. PERCENT가 지정된 경우 expression은 암시적으로 float 값으로 변환되며, 그 밖의 경우에는 bigint로 변환된다. INSERT, UPDATE, MERGE 및 DELETE 문에서는 expression 바깥의 괄호를 반드시 사용해야 한다. SELECT 문에서는 이전 버전과 호환성을 위해 괄호를 생략할 수 있지만, 권장되지는 않는다.

86 7. T-SQL 명령문 TOP 절 ORDER BY 절을 같이 사용하지 않으면 행에 일정한 순서가 없어서
TOP 절이 무의미해진다. 따라서 TOP 절을 사용할 때는 대부분의 경우 ORDER BY 절을 같이 사용한다. ORDER BY 절에서 열(들)을 오름차순 또는 내림차순으로 정렬하도록 함에 따라 작은 값 또는 큰 값을 가진 행들 이 반환된다. 즉‘TOP’이 항상 큰 것을 의미하는 것은 아니다. WITH TIES는 ORDER BY 절이 있는 SELECT 문에서만 지정할 수 있다.

87 7. T-SQL 명령문 TOP 절 WITH TIES는 TOP 절이 돌려주는 마지막 행과 같은 값을 가지는
반환한다. 같은 값을 가지는 행 수가 얼마나 있느냐에 따라 반환되는 행 수가 달라진다. PERCENT를 지정하면 처음 expression%(예: 1.4%)에 해당되는 행들만 반환된다. 만약 expression%에 해당되는 행의 수가 실수(예: 6.16행)로 계산되면 다음으로 큰 정수 개로 반환된다(예: 7행).

88 7. T-SQL 명령문 TOP 절 [그림 8-10] 개념도의 TOP 3을 구현해보자. 예제 75
1 USE AdventureWorksLT; 2 SELECT TOP 3 ProductID, Name, ListPrice 3 FROM SalesLT.Product 4 ORDER BY ListPrice;

89 7. T-SQL 명령문 TOP 절 TOP WITH TIES를 실행해보자. 예제 76 1 USE AdventureWorksLT;
2 SELECT TOP 3 WITH TIES ProductID, Name, ListPrice 3 FROM SalesLT.Product 4 ORDER BY ListPrice;

90 7. T-SQL 명령문 TOP 절 TOP PERCENT를 실행해보자. - 2~3행에서 SalesLT.Customer 테이블의 전체 행 수(440개)를 파악한다. 4행에서 TOP 1.4 PERCENT로 지정했다. 이는 6.16으로 계산되지만 실제로는 다음으로 큰 정수인 7로 변환되어 적용된다. 따라서 결과 집합은 7개 행이다. 예제 77 1 USE AdventureWorksLT; 2 SELECT COUNT(*) AS '전체 행 수' 3 FROM SalesLT.Customer; 4 SELECT TOP 1.4 PERCENT CustomerID, FirstName 5 FROM SalesLT.Customer 6 ORDER BY CustomerID;

91 7. T-SQL 명령문 TOP 절

92 7. T-SQL 명령문 TOP 절 UPDATE 문에서 TOP 절을 사용해보자. - 4행에서‘TOP (5)’를 지정하여 5개 행만 업데이트시킨다. UPDATE 문의 TOP 절에서는 SELECT 문의 ORDER BY 절처럼 명시적으로 정렬 순서를 지정할 수는 없지만, 실행 결과를 보면 기본 키인 orders_id 값에 따라 오름차순으로 정렬되어 상위 5개 행만 적용된 것을 알 수 있다. 예제 78 1 USE Test1DB; 2 SELECT * FROM orders ORDER BY orders_id; 3 GO 4 UPDATE TOP (5) orders 5 SET qty = qty * 2; 6 SELECT * FROM orders ORDER BY orders_id;

93 7. T-SQL 명령문 TOP 절

94 7. T-SQL 명령문 INSERT EXEC 문 INSERT EXEC 문: 결과 집합을 돌려주는 저장 프로시저를 실행한
결과를 받아 기존의 테이블에 누적 삽입하는 것으로, 5장에서 배웠던 INSERT SELECT 문과 비슷한 개념이다. INSERT EXEC 문의 구문 existing_table은 데이터베이스 내에 존재하는 기존의 테이블 이름 이어야 한다. stored_proc_name은 결과 집합을 돌려주는 저장 프로시저의 이름이다. parameter는 저장 프로시저 stored_proc_name의 매개변수이다.

95 7. T-SQL 명령문 INSERT EXEC 문 INSERT EXEC 문을 사용해보자. 예제 79 1 USE Test1DB;
2 IF OBJECT_ID ('order_names', 'U') IS NOT NULL 3 DROP TABLE order_names; 4 GO 5 CREATE TABLE order_names ( 6 orders_id int PRIMARY KEY, 7 buyer_name varchar(20) NOT NULL, 8 product_name varchar(20) NOT NULL, 9 qty int NOT NULL 10 ); 11 GO 12 IF OBJECT_ID ('dbo.usp_GetOrderNames', 'P') IS NOT NULL DROP PROCEDURE usp_GetOrderNames; 14 GO

96 7. T-SQL 명령문 INSERT EXEC 문 15 CREATE PROCEDURE dbo.usp_GetOrderNames
16 AS 17 SET NOCOUNT ON; 18 SELECT o.orders_id, b.buyer_name, p.product_name, o.qty FROM orders AS o INNER JOIN buyer AS b ON o.buyer_id = b.buyer_id INNER JOIN product AS p ON o.product_id = p.product_id ORDER BY o.orders_id; 25 GO 26 INSERT INTO order_names 27 EXEC usp_GetOrderNames; 28 29 SELECT * FROM order_names;

97 7. T-SQL 명령문 INSERT EXEC 문

98 7. T-SQL 명령문 조인에 기초한 행 갱신 조인에 기초한 행 갱신의 개념도

99 7. T-SQL 명령문 조인에 기초한 행 갱신 SalesLT.SalesOrderHeader와 SalesLT.Address 테이블들을 조인하여 조인 테이블을 만들고, 이 조인 테이블의 SalesOrderID를 가진 SalesLT.SalesOrderHeader 테이블의 행들에 대해 UPDATE 문을 실행한다. 조인에 기초한 행 갱신과 관련된 구문

100 7. T-SQL 명령문 조인에 기초한 행 갱신 [그림 8-11]의 개념도를 구현해보자. - 10~12행의 조인 절을 4~6행에 포함하여 만든 SELECT 문을 2~6 행에서 실행하여 갱신 전의 조인 결과를 확인하면 행 수는 2행이다 (Comment 열 값에 유의할 것). - 7~12행에서 조인에 기초한 행 갱신을 실행하고, 13~17행에서 다시 조인 절을 포함한 SELECT 문을 실행해 보면 Comment 열 값이 바뀐 것을 알 수 있다. 예제 80

101 7. T-SQL 명령문 조인에 기초한 행 갱신 1 USE AdventureWorksLT;
2 SELECT s.SalesOrderID, a.City, s.Comment 3 FROM SalesLT.SalesOrderHeader s 4 INNER JOIN SalesLT.Address a ON s.ShipToAddressID = a.AddressID AND a.City = 'London'; 7 UPDATE SalesLT.SalesOrderHeader 8 SET Comment = 'To London' 9 FROM SalesLT.SalesOrderHeader s INNER JOIN SalesLT.Address a ON s.ShipToAddressID = a.AddressID AND a.City = 'London'; 13 SELECT s.SalesOrderID, a.City, s.Comment 14 FROM SalesLT.SalesOrderHeader s INNER JOIN SalesLT.Address a ON s.ShipToAddressID = a.AddressID AND a.City = 'London';

102 7. T-SQL 명령문 조인에 기초한 행 갱신

103 7. T-SQL 명령문 하위 질의에 기초한 행 갱신 FROM 절에서 하위 질의를 사용하여 행을 갱신해보자. 예제 81
1 USE AdventureWorksLT; 2 SELECT TOP 5 CustomerID, LastName 3 FROM SalesLT.Customer 4 ORDER BY LastName; 5 UPDATE SalesLT.Customer 6 SET LastName = SalesLT.Customer.LastName + '*' 7 FROM ( 8 SELECT TOP 2 CustomerID, LastName FROM SalesLT.Customer ORDER BY LastName 11 ) AS Customer2 12 WHERE SalesLT.Customer.CustomerID = Customer2.CustomerID; 13 SELECT TOP 5 CustomerID, LastName 14 FROM SalesLT.Customer 15 ORDER BY LastName;

104 7. T-SQL 명령문 하위 질의에 기초한 행 갱신

105 7. T-SQL 명령문 조인 및 하위 질의에 기초한 행 삭제
행 갱신의 경우와 비슷하게, 행 삭제도 조인 또는 하위 질의에 기초 하여 수행할 수 있다. 행 삭제 구문 첫 번째 FROM 절은 행을 삭제할 테이블(또는 뷰)을 지정하는 것이고, 두 번째 FROM 절은 조인 조건을 지정하는 것이다. table_source 또는 search_condition에서 하위 질의를 사용할 수 있다.

106 7. T-SQL 명령문 조인 및 하위 질의에 기초한 행 삭제 WHERE 절에서 하위 질의를 사용하여 행을 삭제해보자.
예제 82 1 USE AdventureWorksLT; 2 SELECT DISTINCT p.ProductID, p.Name AS Product, c.Name AS ProductCategory 3 FROM SalesLT.Product p 4 JOIN SalesLT.ProductCategory c ON p.ProductCategoryID = c.ProductCategoryID AND c.Name = 'Panniers'; 7 DELETE FROM SalesLT.Product 8 WHERE ProductCategoryID IN ( 9 SELECT ProductCategoryID FROM SalesLT.ProductCategory WHERE Name = 'Panniers'); 12 SELECT DISTINCT p.ProductID, p.Name AS Product, 13 FROM SalesLT.Product p JOIN SalesLT.ProductCategory c ON p.ProductCategoryID = c.ProductCategoryID AND c.Name = 'Panniers';

107 7. T-SQL 명령문 조인 및 하위 질의에 기초한 행 삭제