008 데이터베이스오브젝트 U N I T 0 8 한데이터베이스에는테이블, 뷰, 인덱스, 트리거, 시퀀스등의다양한오브젝트들을생성할수있습니다. 버퍼풀과테이블스페이스를제외한여러가지데이터베이스오브젝트에대한소개와 CREATE 문, ALTER 문, DROP 문을이용한생성, 변경, 제거방법을합니다. UNIT 07 테이블스페이스 9
008 DB 9.7 운영자가이드 Administrator Edition 데이터베이스파티션그룹스키마스키마지정방법테이블 CREATE TABLE 문 ALTER TABLE 문데이터유형 NULL 값과 DEFAULT 값테이블스페이스지정고유키기본키외부키참조무결성점검제한조건 IDENTITY 컬럼 NOT LOGGED INITIALLY 옵션뷰 CREATE VIEW 문 MQT 인덱스 CREATE INDEX문 시퀀스트리거 CREATE TRIGGER AFTER 트리거 BEFORE 트리거 INSTEAD OF 트리거사용자정의유형사용자정의함수 CREATE FUNCTION 문 SQL 사용자정의함수저장프로시저 CREATE PROCEDURE 문 SQL/PL 저장프로시저 PL/SQL 저장프로시저 9 DB 9.7 운영자가이드
008 0 데이터베이스파티션그룹 DPF 를이용하여다중데이터베이스파티션을구성하면데이터베이스파티션의묶음인데이터베이스파티션그룹을정의할수있습니다. CREATE DATABASE PARTITION GROUP, DROP DATABASE PARTITION GROUP 문으로관리합니다. 데이터베이스를생성하면 개의데이터베이스파티션그룹이기본적으로생성됩니다. 파티션그룹 IBMCATGROUP 카탈로그테이블스페이스가생성되는파티션입니다. IBMDEFAULTTEMPGROUP 시스템임시테이블스페이스가생성되는파티션입니다. IBMDEFAULTGROUP 사용자테이블스페이스가생성되는기본파티션입니다. create database partition group 문의형식은다음과같습니다. Figure 080A CREATE DATABASE PARTITION GROUP 문 옵션 <DB 파티션그룹명 > 임의의고유한이름으로지정합니다. ON ALL DBPARTITIONNUMS 모든파티션에생성되도록합니다. ON DBPARTITIONNUMS 한개이상의지정한파티션에생성되도록합니다. < 파티션번호 > dbnodes.cfg 파일에정의된파티션번호입니다. SYSADM, SYSCTRL 권한이필요합니다. create database partition group 명령어에서한개이상의데이터베이스파티션번호를이용하여새로운데이터베이스파티션그룹을생성합니다. $ db "create database partition group <DB 파티션그룹명 > on dbpartitionnum (< 파티션번호 >, < 파티션번호 >)" IBMDEFAULTTEMPGROUP 은 list nodegroups 명령어로표시되지않습니다. 4 5 6 drop database partition group 명령어로데이터베이스파티션을제거합니다. $ db "drop database partition group <DB 파티션그룹명 >" $ db "drop nodegroup <DB 파티션그룹명 >" list database partition groups 명령어를이용하여정의된데이터베이스파티션그룹의정보를확인합니다. list nodegroups 명령어를사용해도됩니다. $ db list database partition groups show detail SYSCAT.NODEGROUPS 뷰에서연관된정보를확인합니다. $ db list database partition groups show detail 9 DB 9.7 운영자가이드
08 0 S E C T 0 스키마 데이터베이스오브젝트의이름은 < 스키마명 >.< 오브젝트명 > 과같이 -part 형식으로구성됩니다. 스키마는오브젝트의이름을수식하는수식자역할을합니다. CREATE SCHEMA, DROP SCHEMA 문으로관리합니다. 데이터베이스를생성하면다음과같이 4가지의스키마가기본적으로생성됩니다. 스키마 SYSIBM 시스템카탈로그테이블의스키마입니다. SYSCAT 시스템카탈로그뷰의스키마입니다. SYSSTAT 통계자료와관련된시스템카탈로그뷰의스키마입니다. SYSFUN 기본적으로제공되는사용자정의함수의스키마입니다. create schema 문의형식은다음과같습니다. 옵션 < 스키마명 > 임의의고유한이름으로지정합니다. AUTHORIZATION 스키마의소유자를지정합니다. Figure 080A CREATE SCHEMA 문 SYSADM, DBADM 권한이필요합니다. SYS로시작되는스키마명은사용하지않도록합니다. 새로생성된스키마를이용하려는사용자는스키마의소유자로부터 CREATEIN, ALTERIN, DROPIN 등의특권을부여받아야합니다. 제거하려는스키마명을가진오브젝트가존재하면, SQL0478N 오류코드가반환되고, 스키마는제거되지않습니다. 4 5 6 create schema 문을이용하여새로운스키마를생성합니다. $ db "create schema < 스키마명 >" create schema 문에서 AUTHORIZATION 옵션을이용하여스키마의소유자를지정할수있습니다. $ db "create schema < 스키마명 > AUTHORIZATION < 스키마의소유자명 >" drop schema 문을이용하여기존의스키마를제거합니다. 반드시 RESTICT 옵션을지정하도록합니다. $ db "drop schema < 스키마명 > restrict" list tables 명령어에서 FOR SCHEMA 옵션을이용하면동일한스키마를가지는테이블과뷰의목록을확인할수있습니다. $ db list tables for schema < 스키마명 > 7 생성된스키마에대한정보는 SYSCAT.SCHEMA 뷰를이용해서확인합니다. $ db "select * from syscat.schemata" UNIT 08 데이터베이스오브젝트 94
008 0 스키마지정방법 테이블등의데이터베이스오브젝트의이름을명시할때는 < 스키마명 >.< 오브젝트명 > 형식의 -part name 을사용하는것이원칙입니다. < 스키마명 > 을명시적으로지정하지않으면접속사용자명이스키마명입니다. SQL 문에서 < 스키마명 > 없이 < 테이블명 > 만지정하면, < 현재세션의로그온사용자명 > 이기 본 < 스키마명 > 으로인식됩니다. < 테이블명 > 은 < 사용자명 >.< 테이블명 > 으로인식됩니다. $ login < 사용자명 > $ db connect to < 데이터베이스명 > $ db "select * from < 테이블명 >" 데이터베이스에접속하는 connect 문에서 USER 와 USING 옵션을이용하면, < 현세션의 로그온사용자명 > 에관계없이 < 데이터베이스접속시에사용된사용자명 > 이기본 < 스키마명 > 으로인식됩니다. < 테이블명 > 은 < 사용자명 >.< 테이블명 > 으로인식됩니다. set current schema 문은데이터베이스에접속한상태에서실행할수있으며, 접속이해제되면 < 로그온사용자명 > 으로복원됩니다. $ login < 사용자명> $ db connect to < 데이터베이스명 > user < 사용자명> using < 암호명> $ db "select * from < 테이블명 >" CURRENT SCHEMA 특수레지스터리변수는스키마명을명시적으로지정하지않는경우에기본스키마로적용될값을저장하고있습니다. values 문으로현재값을확인할수있습니다. set current schema 문으로 CURRENT SCHEMA 특수레지스터리변수를변경하면, < 데이터베이스접속시사용된사용자명 > 보다우선적으로적용됩니다. < 테이블명 > 은 < 스키마명 >.< 테이블명 > 으로인식됩니다. $ login < 사용자명> $ db connect to < 데이터베이스명 > user < 사용자명> using < 암호명> $ db values(current schema) $ db set current schema < 스키마명> $ db values(current schema) $ db "select * from < 테이블명 >" 4 데이터베이스의오브젝트를지정할때는개별적인 SQL문에서 < 스키마명 > 을명시적으로지정하는것이권장됩니다. < 현재세션의로그온사용자명 >, < 데이터베이스접속시에사용된사용자명 >, <CURRENT SCHEMA 특수레지스터리변수의현재값 > 보다 SQL문에서명시적으로지정한 < 스키마명 > 이가장우선적으로적용됩니다. < 테이블명 > 은 < 스키마명>.< 테이블명 > 로인식됩니다. $ login < 사용자명> $ db connect to < 데이터베이스명 > user < 사용자명> using < 암호명> $ db set current schema < 스키마명> $ db "select * from < 스키마명>.< 테이블명 >" 95 DB 9.7 운영자가이드
08 0 S E C T 04 테이블 사용자의데이터는테이블에저장됩니다. 테이블의데이터는기본적으로한개의테이블스페이스에저장됩니다. CREATE TABLE, ALTER TABLE, DROP TABLE 문으로관리합니다. 테이블스페이스의페이지크기별제약사항은정보센터를참조합니다. 한테이블스페이스에는한개이상의테이블이저장됩니다. Database Manager Instance database Tablespace A Table Table Table Tablespace B Table 4 database Tablespace A Table Table Figure 0804A 테이블스페이스와테이블 테이블의한행의총길이는지정한테이블스페이스의페이지크기보다작아야합니다. create table 문을이용하여테이블을정의합니다. IMPLICIT_SCHEMA 특권이있으면, 존재하지않는 < 스키마명 > 을이용하여테이블을정의할수있습니다. $ db "create table < 스키마명 >.< 테이블명 > (< 컬럼정의 >)" alter table 문을이용하여컬럼추가, 고유키추가및제거, 기본키추가및제거, 외부키추 가및제거, 점검제한조건추가및제거등의변경작업이가능합니다. $ db "alter table < 스키마명 >.< 테이블명 > ADD < 제한조건 >" 4 drop table 문으로테이블을제거합니다. $ db "drop table < 스키마명 >.< 테이블명 >" 5 list tables 명령어에서테이블의목록을확인할수있습니다. $ db list tables $ db list tables for schema < 스키마명 > $ db list tables for system $ db list tables for all 6 테이블에대한정보는 SYSCAT.TABLES 뷰를이용해서확인합니다. $ db "select * from syscat.tables" 7 describe table 문으로테이블의컬럼에대한정보를확인합니다. $ db "describe table < 스키마명 >.< 테이블명 >" 8 dblook 명령어로테이블에대한 DDL 문을추출할수있습니다. $ dblook d <DB 명 > e z < 스키마명 > t < 테이블명 > -o < 출력파일명 > UNIT 08 데이터베이스오브젝트 96
008 05 CREATE TABLE 문 테이블을생성할때이용하는 SQL 문입니다. 컬럼, 고유키, 기본키, 외부키, 점검제한조건등을정의하고, 데이터와인덱스를저장할테이블스페이스를지정합니다. SYSADM, DBADM 권한또는데이터베이스에대한 CREATAB 특권이필요할수있습니다. create table 문의형식은다음과같습니다. 새로운스키마명을이용하여테이블명을지정하려면, 데이터베이스에대한 IMPLICIT_SCHEMA 특권이필요합니다. 기존의스키마명을이용하여테이블명을지정하려면, 스키마에대한 CREATIN 특권이필요합니다. IN, INDEX IN, LONG IN 옵션을사용하려면테이블스페이스에대한 USE 특권이필요합니다. 외부키를정의하려면, 부모테이블에대한 REFERENCES 특권이필요합니다. Figure 0804A CREATE TABLE 문 NOT LOGGED INITIALLY 특성은 ALTER TABLE 문으로활성화할때만적용됩니다. 옵션에대한은다음과같습니다. 옵션 < 테이블명 > 임의의고유한이름으로지정합니다. < 스키마명 > 을생략하면 CURRENT SCHEMA 특수레지스터리변수의값이기본스키마명으로사용됩니다. 기존의스키마명을사용하려면, < 컬럼명 > 테이블내에서고유한임의의이름으로지정합니다. < 데이터유형 > 컬럼의데이터유형을지정합니다. <NULL 허용여부 > NULL 값을허용하지않으려면 'NOT NULL' 로지정합니다. < 기본값 > WITH DEFAULT 옵션으로기본값을지정합니다. IN <TS 명 > 테이블의데이터가저장될테이블스페이스명을지정합니다. INDEX IN <TS 명 > 인덱스데이터가저장될테이블스페이스명을지정합니다. LONG IN <TS 명 > LONG 데이터가저장될테이블스페이스명을지정합니다. NOT LOGGED INITIALLY 트랜잭션에서해당테이블에대한변경사항을로그에기록하지않게합니다. 97 DB 9.7 운영자가이드
08 0 S E C T 06 ALTER TABLE 문 테이블의특성을변경할떄사용하는 SQL문입니다. 컬럼추가, 고유키추가및제거, 기본키추가및제거, 외부키추가및제거, 점검제한조건추가및제거등의변경작업이가능합니다. SYSADM, DBADM 권한또는테이블에대한 CONTROL, ALTER 특권이필요할수있습니다. alter table 문의형식은다음과같습니다. 기존의스키마명을이용하여테이블을변경하려면, 스키마에대한 ALTERIN 특권이필요합니다. 외부키를정의하려면, 부모테이블에대한 REFERENCES 특권이필요합니다. Figure 0805A ALTER TABLE 문 옵션에대한은다음과같습니다. 옵션 < 테이블명 > 변경할테이블명을지정합니다. ADD < 컬럼정의 > 컬럼을추가합니다. ADD < 고유키제한조건 > 고유키를추가합니다. ADD < 기본키제한조건 > 기본키를추가합니다. ADD < 외부키제한조건 > 외부키를추가합니다. ADD < 점검제한조건 > 점검제한조건을추가합니다. ALTER < 컬럼명 > VARCHAR 유형에서컬럼의길이를증가시킬수있습니다. DROP < 제한조건명 > 지정된 < 제한조건 > 을제거합니다. ACTIVATE NOT LOGGED INITIALLY NOT LOGGED 모드로전환합니다. 트랜잭션이종료될때까지는해당테이블에대한변경작업이로깅되지않습니다. UNIT 08 데이터베이스오브젝트 98
008 07 데이터유형 기본적으로지원되는컬럼의데이터유형은다음과같습니다. CREATE DISTINCT TYPE 문으로사용자가새로운데이터유형을추가로생성하여사용할수도있습니다. 기본적으로지원되는데이터의유형은다음과같습니다. Data Types Numeric Integer DECIMAL Floating SMALLINT INTEGER BIGINT DECIMAL REAL DOUBLE String Character String Single Byte Double Byte CHAR VARCHAR LONG VARCHAR CLOB GRAPHIC VARGRAPHIC LONG VARGRAPHIC DBCLOB Binary String BLOB VARCHAR FOR BIT DATA Datetime DATE TIME TIMESTAMP Datalink Figure 0807A 데이터유형 VARCHAR의최대길이는페이지크기에따라다릅니다. LOB 유형의최대길이는 G입니다. 대표적인데이터유형에대한은다음과같습니다. 구분유형저장 BYTE 수최대범위 숫자 SMALLINT -,768 ~ +-,767 INT 4 -,47,48,648 ~ +,47,48,647 BIGINT 8-9,,7,06,854,775,808 ~ +9,,7,06,854,775,807 DEC(p,s) (p+s)/+ 자리 DOUBLE 8 -.79769E+08 ~ +.79769E+08 문자 CHAR(n) n 54 바이트 VARCHAR( n) n + 4 67 바이트 (K 페이지인경우 ) 날짜 DATE 0 000-0-0 ~ 9999-- TIME 8 00:00:00 ~ 4:00:00 TIMESTAMP 6 000-0-0-00.00.00.000000 ~ 9999---4.00.00.000000 99 DB 9.7 운영자가이드
08 0 S E C T 08 NULL 값과 DEFAULT 값 컬럼에 NULL 값과 DEFAULT 값을허용하게할수있습니다. DEFUALT 속성을가지지않는컬럼은반드시명시적으로값을지정해야합니다. CREATE TABLE 문에서 NOT NULL 옵션과 WITH DEFULAT 옵션을이용합니다. NULL 값은 0 또는공백 (blank) 또는 empty string 이아닙니다. empty string은길이가 0인값을의미하며, 공백문자와는다릅니다. NULL 값은알려지지않은값을의미합니다. 테이블을정의할때, 컬럼에 NULL 값을허용하지않으려면 CREATE TABLE 문에서 NOT NULL 옵션을이용합니다. $ db "create table < 테이블명 > ( < 컬럼명 > < 데이터유형명 > NOT NULL,.)" CREATE TABLE 문에서 WITH DEFAULT 옵션만지정하면시스템기본값이제공됩니다. $ db "create table < 테이블명 > ( < 컬럼명 > < 데이터유형명 > WITH DEFAULT)" 유형 기본값 숫자 0 0 문자 EMPTY STRING 길이가 0 인문자 DATE CURRENT DATE 현재시스템날짜 TIME CURRENT TIME 현재시스템시간 TIMESTAMP CURRENT TIMESTAMP 현재시스템시간소인 날짜유형의표현식은데이터베이스의코드페이지에따라달라집니다. 코드페이지가 970(ko_KR) 인경우에 DATE 유형은 'yyyy-mm-dd', TIME 유형은 'hh:mm:ss' 가되고, TIMESTAMP 유형은 'yyyy-mmdd-hh.mm.ss.uuuuuu' 으로표현됩니다. CREATE TABLE 문에서 WITH DEFAULT < 기본값 > 옵션을지정하면사용자가지정한값이 기본값으로사용됩니다. $ db "create table < 테이블명 > ( < 컬럼명 > < 데이터유형명 > WITH DEFAULT < 기본값 >)" 유형기본값예 숫자 WITH DEFAULT < 숫자 > WITH DEFAULT 0 문자 WITH DEFAULT '< 문자열 >' WITH DEFAULT 'xx' DATE WITH DEFAULT '< 날짜 >' WITH DEFAULT '006-04-7' TIME WITH DEFAULT '< 시간 >' WITH DEFAULT '4::0' TIMESTAMP WITH DEFAULT '< 시간소인 >' WITH DEFAULT '006-04-7-4..0.69400' CREATE TABLE staff ( id SMALLINT NOT NULL WITH DEFAULT 0, name VARCHAR(9), dept SMALLINT NOT NULL, job CHAR(5), years SMALLINT, salary DECIMAL(7, ), comm DECIMAL(7, ) WITH DEFAULT ); 사용자가제공하는기본값인 0 이사용됩니다. 시스템이기본값인 0.00 이사용됩니다. Figure 0808A 기본값지정 UNIT 08 데이터베이스오브젝트 00
008 09 테이블스페이스지정 CREATE TABLE 문에서 IN 키워드를이용하여테이블스페이스를지정할수있습니다. INDEX IN, LONG IN 키워드로테이블, 인덱스, LONG 데이터를개별적인 DMS 테이블스페이스에저장할수있습니다. 지정한테이블스페이스는변경될수없습니다. 해당테이블의행의총길이를수용할수있는적합한기본테이블스페이스가없다면, SQL086N 오류가반환됩니다. 특정한테이블스페이스를지정하려면, 해당테이블스페이스에대한 USE 특권이있어야합니다. 사용자가정의한테이블스페이스중에서해당테이블의행의총길이를수용할수있는페이지크기를가진첫번째 REGULAR 유형의테이블스페이스가기본사용자테이블스페이스로사용됩니다. CREATE TABLE 문에서 IN 옵션을지정하지않으면, 테이블은기본사용자테이블스페이스에 저장됩니다. $ db "create table < 스키마명 >.< 테이블명 > (< 컬럼정의 >) " CREATE TABLE 문에서 IN 옵션으로테이블이저장될테이블스페이스를지정합니다. 테이블 의모든데이터와인덱스데이터는동일한테이블스페이스에저장됩니다. $ db "create table < 스키마명 >.< 테이블명 > (< 컬럼정의 >) IN < 테이블스페이스명 >" CREATE TABLE 문에서 INDEX IN 키워드를이용하여인덱스를위한데이터를별도의테이 블스페이스에저장할수있습니다. IN 옴션과 INDEX IN 옵션에서지정한테이블스페이스는 DMS 방식의 REGULAR 유형이어야합니다. INDEX IN 옵션만지정할수는없습니다. 사용자가정의한테이블스페이스가없다면, USERSPACE이기본사용자테이블스페이스입니다. 한테이블이여러테이블스페이스에저장되었다면, 테이블스페이스는함께 drop 되어야합니다. 4 5 $ db "create table < 스키마명 >.< 테이블명 > (< 컬럼정의 >) IN < 테이블스페이스명 > INDEX IN < 테이블스페이스명 > " CREATE TABLE 문에서 LONG IN 키워드를이용하여 LONG 데이터를별도의테이블스페 이스에저장할수있습니다. IN 옴션에서지정한테이블스페이스는 DMS 방식의 REGULAR 유형이고, LONG IN 옵션에서지정한테이블스페이스는 DMS 방식의 LARGE 유형이어야 합니다. LONG IN 옵션만지정할수는없습니다. $ db "create table < 스키마명 >.< 테이블명 > (< 컬럼정의 >) IN < 테이블스페이스명 > LONG IN < 테이블스페이스명 > " CREATE TABLE 문에서 IN, INDEX IN, LONG 옵션을모두사용하여테이블데이터, 인덱 스데이터, LONG 데이터를별도의 DMS 테이블스페이스를저장할수있습니다. $ db "create table < 스키마명 >.< 테이블명 > (< 컬럼정의 >) IN < 테이블스페이스명 > INDEX IN < 테이블스페이스명 > LONG IN < 테이블스페이스명 > " 테이블의데이터는 ts0, 인덱스의데이터는 ts0, LONG 데이터는 ts0 에분리하여자장합니다. CREATE TABLE kes.dept ( id smallint not null IN INDEX IN LONG IN, name varchar(0) not null, man smallint, budget int, CONSTRAINT dept_pk0 PRIMARY KEY (id)) ts0 ts0 ts0; ts0, ts0, ts0 는 DMS 유형의테이블스페이스입니다. Figure 0809A 테이블스페이스지정 0 DB 9.7 운영자가이드
08 0 S E C T 0 고유키 고유키는한개이상의컬럼들로구성되어테이블의각행을고유하게구별하는값입니다. 한테이블에한개이상의고유키를지정할수있습니다. 고유키를정의하면, 해당컬럼들로구성된고유인덱스가자동으로생성됩니다. 고유키제한조건을정의하는구문은다음과같습니다. 고유키를구성하는각컬럼은 NOT NULL 속성을지정해야합니다. Figure 080A 고유키제한조건절 고유키에대응하는인덱스가이미존재하면, SQL0000W 라는경고메시지가반환되지만, 무시해도됩니다. < 제한조건명 > 은데이터베이스내에서고유해야합니다. create table 문에서 CONSTRAINT ~ UNIQUE 라는옵션으로지정합니다. < 제한조건명 > 과동일한이름을가진고유인덱스가자동으로생성됩니다. 고유키는한테이블에여러개정의할수있습니다. CONSTRAINT 옵션을지정하지않으면, 제한조건명과인덱스의이름은 'SQLyymmddhhmmssxxx' 형식으로엔진이부여합니다. $ db "create table < 스키마명 >.< 테이블명 > (, < 고유키제한조건절 >, )" < 제한조건명 > 을명시하지않으면, 엔진이생성한이름으로관리하게됩니다. ALTER 또는 DROP문으로관리하려면, 사용자가제한조건명을명시하는것이편리합니다. 4 alter table 문을이용하여고유키를추가할수있습니다. $ db "alter table < 스키마명 >.< 테이블명 > ADD < 고유키제한조건절 >" alter table 문을이용하여고유키를제거할수있습니다. $ db "alter table < 스키마명 >.< 테이블명 > DROP CONSTRAINT < 제한조건명 >" CREATE TABLE kes.empl ( id smallint not null, name varchar(0) not null, sex char(), mydept smallint, salary smallint, email varchar(0) not null, hiredate date, CONSTRAINT empl_uk0 UNIQUE(email) ); email 컬럼은 NOT NULL 로지정합니다. email 컬럼이고유키가됩니다. 제한조건명은 empl_uk0 입니다. 자동으로생성되는고유인덱스의이름도 empl_uk0 입니다. ALTER TABLE kes.empl DROP CONSTRAINT empl_uk0 ; ALTER TABLE kes.empl ADD CONSTRAINT empl_uk0 UNIQUE (email) ; Figure 080B 고유키생성 UNIT 08 데이터베이스오브젝트 0
008 기본키 기본키는고유키와동일한특성을갖지만, 한테이블에한개만지정할수있습니다. 기본키는한개이상의컬럼들로구성될수있으며, 해당컬럼들로구성된고유인덱스가자동으로생성됩니다. 고유키와기본키는기능적으로동일합니다. 단, IMPORT 명령어의 INSERT_UPDATE 옵션을사용할때에는반드시기본키가필요합니다. 기본키제한조건을정의하는구문은다음과같습니다. 기본키를구성하는각컬럼은 NOT NULL 속성을지정해야합니다. Figure 08A 기본키제한조건절 고유키에대응하는인덱스가이미존재하면, SQL0000W 라는경고메시지가반환되지만, 무시해도됩니다. create table 문에서 CONSTRAINT ~ PRIMARY KEY 라는옵션으로지정합니다. < 제한조건명 > 과동일한이름을가진고유인덱스가자동으로생성됩니다. 기본키는한테이블에한개만정의할수있습니다. CONSTRAINT 옵션을지정하지않으면, 제한조건명과인덱스의이름은 'SQLyymmddhhmmssxxx' 형식으로엔진이부여합니다. < 제한조건명 > 은데이터베이스내에서고유해야합니다. < 제한조건명 > 을명시하지않으면, 엔진이생성한이름으로관리하게됩니다. ALTER 또는 DROP문으로관리하려면, 사용자가제한조건명을명시하는것이편리합니다. 4 $ db "create table < 스키마명 >.< 테이블명 > (, < 기본키제한조건절 >, )" alter table 문을이용하여기본키를추가할수있습니다. $ db "alter table < 스키마명 >.< 테이블명 > ADD < 기본키제한조건절 >" alter table 문을이용하여기본키를제거할수있습니다. $ db "alter table < 스키마명 >.< 테이블명 > DROP CONSTRAINT < 제한조건명 >" CREATE TABLE kes.empl ( id smallint not null, name varchar(0) not null, sex char(), mydept smallint, salary smallint, email varchar(0) not null, hiredate date, CONSTRAINT empl_pk0 PRIMARY KEY (id) ); 제한조건명은 empl_pk0 입니다. 자동으로생성되는고유인덱스의이름도 empl_pk0 입니다. id 컬럼은 NOT NULL 로지정합니다. id 컬럼이기본키가됩니다. ALTER TABLE kes.empl DROP CONSTRAINT empl_pk0 ; ALTER TABLE kes.empl ADD CONSTRAINT empl_pk0 PRIMARY KEY(id) ; Figure 08B 기본키생성 0 DB 9.7 운영자가이드
08 0 S E C T 외부키 외부키는부모테이블의고유키또는기본키를참조하는키입니다. 참조하는고유키또는기본키와호환되는컬럼들로구성되어야하며, 각컬럼은 NULL 값을허용합니다. 외부키제한조건을정의하는구문은다음과같습니다. Figure 08A 외부키제한조건절 < 제한조건명 > 은데이터베이스내에서고유해야합니다. create table 문에서 CONSTRAINT ~ FOREIGN KEY 라는옵션으로지정합니다. 외부키는한테이블에여러개정의할수있습니다. CONSTRAINT 옵션을지정하지않으면, 제한조건명은 'SQLyymmddhhmmssxxx' 형식으로엔진이부여합니다. $ db "create table < 스키마명 >.< 테이블명 > (, < 외부키제한조건절 >, )" < 제한조건명 > 을명시하지않으면, 엔진이생성한이름으로관리하게됩니다. ALTER 또는 DROP문으로관리하려면, 사용자가제한조건명을명시하는것이편리합니다. 4 alter table 문을이용하여외부키를추가할수있습니다. $ db "alter table < 스키마명 >.< 테이블명 > ADD < 외부키제한조건절 >" alter table 문을이용하여외부키를제거할수있습니다. $ db "alter table < 스키마명 >.< 테이블명 > DROP CONSTRAINT < 제한조건명 >" 외부키는 SQL문에서부모테이블의기본키또는고유키와 JOIN 하게되므로, 외부키에대한인덱스를생성하는것이좋습니다. CREATE TABLE kes.empl ( id smallint not null, name varchar(0) not null ); mydept 컬럼은 NULL 값을허용할수있습니다., sex char(), mydept smallint, salary smallint, email varchar(0) not null, hiredate date, CONSTRAINT empl_fk0 FOREIGN KEY(mydept) REFERENCES kes.dept 부모테이블의이름은 kes.dept 입니다. 제한조건명은 empl_fk0 입니다. ALTER TABLE kes.empl DROP CONSTRAINT empl_fk0 ; mydept 컬럼이외부키가됩니다. ALTER TABLE kes.empl ADD CONSTRAINT empl_fk0 FOREIGN KEY(mydept) REFERENCES kes.dept ; Figure 08B 외부키생성 UNIT 08 데이터베이스오브젝트 04
008 참조무결성 두테이블이고유키와외부키로연결되어외부키를가진테이블에데이터를추가, 변경하는경우에데이터의참조무결성이유지됩니다. 고유키를가진테이블에데이터를변경, 제거하는경우에는 UPDATE 규칙과 DELETE 규칙이적용됩니다. 외부키제한조건에서 UPDATE 규칙과 DELETE 규칙을정의하는옵션은다음과같습니다. Figure 08A UPDATE 규칙과 DELETE 규칙옵션 부모테이블에 INSERT문을실행할때, 점검규칙은필요하지않습니다. 외부키는자신의테이블에있는고유키를참조할수도있습니다. 동일한부모테이블의고유키를여러자손테이블의외부키가참조할수있습니다. 한개의 DELETE 문또는 UPDATE 문을실행할때, 두개이상의 RI가존재하는경우는흔하지않으므로, 보통은 NO ACTION과 RESTRICT 를동일하게생각하면됩니다. 외부키를가진자손테이블에 INSERT 문으로데이터를추가할때, 제공된외부키가부모테이 블의고유키에존재하는값인지점검합니다. 존재하지않는값인경우에는 SQL050N 오류코 드가반환되고, INSERT 문은실패합니다. 자손테이블에외부키에입력된데이터는부모테이 블의고유키에존재하는값이므로항상참조가가능합니다. 이러한기능을 ' 참조무결성 (RI, Refrential Integrity)' 라고합니다. 고유키를가진부모테이블에서 UPDATE 문을실행할때는다음과같이 가지의 UPDATE 규칙을적용받게할수있습니다. CREATE TABLE 문에서외부키를정의할때 ON UPDATE 옵션을이용하여지정합니다. UPDATE 규칙 NO ACTION RESTICT 다른 RI 관계의 UPDATE 규칙을먼저적용하고, 자신의 UPDATE 규칙을적용합니다. 변경되는행의고유키값을참조하고있는자손테이블의행이존재하면, SQL05N 오류코드가반환되고, UPDATE 문이실패합니다. 기본값으로사용됩니다. 다른 RI 관계의 UPDATE 규칙보다자신의 UPDATE 규칙을먼저적용하는점을제외하고, NO ACTION 과동일합니다. 4 고유키를가진부모테이블에서 DELETE 문을실행할때는다음과같이 4 가지의 DELETE 규 칙을적용받게할수있습니다. CREATE TABLE 문에서외부키를정의할때 ON DELETE 옵 션을이용하여지정합니다. DELETE 규칙 NO ACTION RESTICT CASCADE SET NULL 다른 RI 관계의 DELETE 규칙을먼저적용하고, 자신의 DELETE 규칙을적용합니다. 삭제되는행의고유키값을참조하고있는자손테이블의행이존재하면, SQL05N 오류코드가반환되고, DELETE 문이실패합니다. 기본값으로사용됩니다. 다른 RI 관계의 DELETE 규칙보다자신의 DELETE 규칙을먼저적용하는점을제외하고, NO ACTION 과동일합니다. 삭제되는행의고유키값을참조하고있는자손테이블의행을함께삭제합니다. 삭제되는행의고유키값을참조하고있는자손테이블의행의외부키의값을 NULL 값으로변경합니다. 자손테이블의외부키컬럼이 NULL 을허용하는컬럼일때지정할수있습니다. 05 DB 9.7 운영자가이드
08 0 S E C T 4 점검제한조건 테이블의컬럼에입력되는값을제한하는조건입니다. INSERT와 UPDATE 문을실행하면자동으로점검이실행되어조건에맞지않는값인경우에는오류를반환합니다. 점검제한조건을정의하는구문은다음과같습니다. Figure 084A 점검제한조건절 < 제한조건명 > 은데이터베이스내에서고유해야합니다. < 제한조건명 > 을명시하지않으면, 엔진이생성한이름으로관리하게됩니다. ALTER 또는 DROP문으로관리하려면, 사용자가제한조건명을명시하는것이편리합니다. 점검제한조건을표현하는방법은 SQL 문의 WHERE 조건절의표현식과동일합니다. 4 create table 문에서 CONSTRAINT ~ CHECK 라는옵션으로지정합니다. 점검제한조 건은한테이블에여러개정의할수있습니다. CONSTRAINT 옵션을지정하지않으면, 제한 조건명은 'SQLyymmddhhmmssxxx' 형식으로엔진이부여합니다. $ db "create table < 스키마명 >.< 테이블명 > (, < 점검제한조건절 >, )" alter table 문을이용하여점검제한조건을추가할수있습니다. $ db "alter table < 스키마명 >.< 테이블명 > ADD < 점검제한조건절 >" alter table 문을이용하여점검제한조건을제거할수있습니다. $ db "alter table < 스키마명 >.< 테이블명 > DROP CONSTRAINT < 제한조건명 >" CREATE TABLE kes.empl ( id smallint not null, name varchar(0) not null, sex char() sex 컬럼은 NULL값, mydept smallint 을허용할수있습니다., salary smallint, email varchar(0) not null, hiredate date, CONSTRAINT empl_cc0 CHECK (sex in ('M','F')) ); 제한조건명은 empl_cc0 입니다. sex 컬럼에는 'M' 또는 'F' 만허용됩니다. ALTER TABLE kes.empl DROP CONSTRAINT empl_cc0 ; ALTER TABLE kes.empl ADD CONSTRAINT empl_cc0 CHECK (sex = 'M' or sex ='F') ; 표현식은 SQL 문의 WHERE 절과동일합니다. Figure 084B 외부키생성 UNIT 08 데이터베이스오브젝트 06
008 5 Identity 컬럼 INSERT 문이실행되면자동으로그값이증가되는컬럼입니다. 컬럼의데이터유형은숫자유형으로한테이블에한개만정의할수있습니다. CREATE TABLE 문에서 GENERATED 라는옵션으로지정할수있습니다. create table 문에서 GENERATED 라는옵션으로지정합니다. Figure 085A INDENTITY 컬럼정의 데이터베이스가비활성화되면, 사용되지않고캐쉬에남아있던자동생성값은유실됩니다. 주요한옵션은다음과같습니다. 옵션 GENERATED ALWAYS GENERATED BY DEFAULT 엔진에의해자동으로생성된값만허용됩니다. 사용자가명시적으로값을지정하여입력할수없습니다. 사용자가값을지정하지않는경우에만엔진이자동으로값을생성합니다. 값의고유성을보장할수없습니다. START WITH 양수또는음수의시작값을지정합니다. INCREMENT BY 양수또는음수의증가값을지정합니다. MAXVALUE 양수또는음수의최대값을지정합니다. CYCLE 최대값또는최소값에도달하면최소값또는최대값을생성합니다. CACHE 지정된개수의생성값을미리캐쉬에보관하여성능에유리합니다. CREATE TABLE inventory ( partno INT GENERATED BY DEFAULT AS IDENTITY (START WITH 00 INCREMENT BY ), description CHAR(0), PRIMARY KEY(partno) ); INSERT INTO inventory VALUES (DEFAULT,'A'); INSERT INTO inventory (description) VALUES ('B'); INSERT INTO inventory VALUES (0,'C'); INSERT INTO inventory VALUES (00,'D'); partno 컬럼을자동생성합니다. 성공 : (00,A) 성공 : (0,B) 실패 : 기본키중복성공 : (00,D) Figure 085B GENEARTED BY DEFAULT 로생성된 IDENTITY 컬럼 07 DB 9.7 운영자가이드
08 0 S E C T 6 NOT LOGGED INITIALLY 옵션 필요시에 ALTER TABLE 문을이용하여 NOT LOGGED 모드를활성화시키면, 데이터베이스로깅없이 SQL문을실행할수있으므로대량의데이터를입력하는경우에유리합니다. UOW가실패하면, 테이블은재생성되어야합니다. NOT LOGGED 모드로테이블에입력된데이터는롤포워드복구시에 rollforward db 명령어로복구될수없습니다. create table 문에서 NOT LOGGED INITIALLY 라는옵션으로지정해야합니다. $ db "create table < 테이블명 > (< 컬럼정의 >) NOT LOOGED INITIALLY" 'Not Logged Initially ' not enabled Log Buffer Bufferpool 'Not Logged Initially ' is enabled Bufferpool New Row Old Row log With Logging New Row table Flush to log when mincomit reached commit successful Insert Dirty pages written by cleaners to disk when softmax or chngpgs_thresh reached New Row table No Logging Flush to disk when commit successful Figure 086A NOT LOGGED 모드와 LOGGED 모드 create table 문을실행하고 commit 하면 'Not Logged Initially' 상태가해제됩니다. ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE 옵션을이용하면테이블의기존데이터를로깅하지않고삭제합니다. NOT LOGGED INITIALLY 옵션은기본적으로비활성화상태이므로테이블의변경내역은데이터베이스로그파일에기록됩니다. alter table 문으로 NOT LOGGED INITIALLY 옵션을활성화시키면, COMMIT 또는 ROLLBACK 문이실행될때까지해당테이블에대한데이터베이스로깅이중지됩니다. $ vi < 입력파일명 > ALTER TABLE < 테이블명 > ACTIVATE NOT LOGGED INITIALLY; INSERT INTO... SELECT FROM... ; COMMIT; $ db +c svtf < 입력파일명 > 4 NOT LOGGED 옵션은 UOW가성공적으로종료되면자동으로비활성화되고, 테이블에대한데이터베이스로깅은다시시작됩니다. UOW가실패하면해당테이블은재생성해야합니다. $ db drop table < 테이블명 > $ dblook d < 데이터베이스명 > -z < 스키마명 > -t < 테이블명 > -e o < 출력파일명 > db svtf < 출력파일명 > UNIT 08 데이터베이스오브젝트 08
008 7 뷰 사용자가원본테이블의특정행과특정컬럼들만액세스할수있도록하려면뷰를생성합니다. 한개이상의원본테이블의데이터를조인하여뷰를생성하는것도가능합니다. 기본테이블에대해최소한 SELECT 특권이있어야합니다.. 원본테이블이제거되면뷰는작동불능상태가되어서액세스를할수없습니다. 원본테이블이다시생성되어도작동불능상태의뷰는액세스할수없으므로, 재생성이필요합니다. create view 문으로생성하며, SELECT 문으로액세스가허용되는데이터를제한합니다. $ db "create view kes.t_v as select * from kes.t where c > 00 " WITH CHECK OPTION 을이용하면, 뷰의정의에맞지않는데이터를추가, 삭제, 변경할수 없습니다. INSERT 또는 UPDATE 를실행할때뷰의정의에맞는지를확인합니다. CREATE VIEW kes.empl_v AS SELECT * FROM kes.empl WHERE salary >= 00 WITH CHECK OPTION; salary 컬럼의값이 00 이상인데이터만액세스를허용합니다. INSERT INTO kes.empl_v VALUES (,'KES','F',,00,'kes@kr.ibm.com','000-0-0'); INSERT INTO kes.empl_v VALUES (,'XXX','F',,00,'xxx@kr.ibm.com','006-0-0'); salary 컬럼의값으로 00 을지정하였으므로 INSERT 문은실패합니다. Figure 087A WITH CHECK OPTION 옵션이있는뷰 VIEW 를통한액세스가가능합니다. $ db "insert into kes.t_v values (,00)" $ db "select * from kes.t_v" 4 drop view 문을이용하여제거합니다. $ db drop view kes.t_v 5 list tables 명령어에서뷰의목록을확인할수있습니다. $ db list tables $ db list tables for schema < 스키마명 > $ db list tables for system $ db list tables for all 6 뷰에대한정보는 SYSCAT.VIEWS, SYSCAT.VIEWDEP, SYSCAT.TABLES 뷰를이용해서확인합니다. SYSCAT.TABLES 뷰에서 TYPE 컬럼의값이 'V' 입니다. $ db "select * from syscat.tables" 7 dblook 명령어를이용하여뷰에대한 DDL 을추출합니다. $ dblook d <DB 명 > e z < 스키마명 > v < 뷰명 > -o < 출력파일명 > 09 DB 9.7 운영자가이드
08 0 S E C T 8 CREATE VIEW 문 뷰룰생성하는 SQL 문입니다. SELECT 문을이용하여뷰를통하여액세스할수있는데이터의범위를결정하며, WITH CHECK OPTION 옵션으로뷰의정의에맞지않는데이터에대한액세스를허용하지않게합니다. SYSADM, DBADM 권한또는테이블에대한 SEELCT 특권이필요할수있습니다. create view 문의형식은다음과같습니다. 새로운스키마명을이용하여테이블명을지정하려면, 데이터베이스에대한 IMPLICIT_SCHEMA 특권이필요합니다. 기존의스키마명을이용하여테이블명을지정하려면, 스키마에대한 CREATIN 특권이필요합니다. Figure 088A CREATE VIEW 문 옵션에대한은다음과같습니다. 모드 < 뷰명 > 임의의고유한이름으로지정합니다. < 컬럼명 > 지정하지않으면베이스테이블의컬럼명또는 SELECT 문의결과컬럼명이사용됩니다. WITH < 공통테이블표현식 > SELECT 문에서사용될공통테이블을정의합니다. AS <select 문 > 뷰의내용이되는 SELECT 문을지정합니다. WITH CASCADE CHECK OPTION WITH LOCAL CHECK OPTION 뷰의정의에맞지않는데이터를처리하지않습니다. 해당뷰를이용한다른뷰를생성했를때, 이특성을전달합니다. 뷰의정의에맞지않는데이터를처리하지않습니다. 해당뷰를이용한다른뷰를생성했를때, 이특성을전달하지않습니다. UNIT 08 데이터베이스오브젝트 0
008 9 MQT 집계함수를이용한 summary table 을미리정의해두면, 집계함수를이용한복잡한쿼리의실행시간을단축시킬수있습니다. CREATE TABLE 문으로생성하고, REFRESH TABLE 문으로관리합니다. SUMMARY 테이블이라고도합니다. immediate 옵션을사용해도최초에한번은 refresh table 명령어를실행해야합니다. Meterialized Query Table 의약자입니다. create table 문에서 REFRESH 옵션으로 생성합니다. < 컬럼정의 > refresh deferred 옵션을사용하면, refresh table 문을실행하기전까지는완벽한데이터일관성을보장하지않습니다. Figure 089A CREATE TABLE 문의 MQT 생성옵션 사용하기전에 REFRESH TABLE 문을이용하여최초의결과집합을생성해야합니다. Figure 089B REFRESH TABLE 문 4 SELECT문에서직접 MQT를이용할수있습니다. $ db "select * from <MQT명 >" 베이스테이블을대상으로하는 SELECT문에서명시한조건문이 MQT의정의부분과일치하면, MQT를이용한 SELECT문으로자동으로변환됩니다. REFRESH TABLE staff_summary; SELECT 문이자동으로 MQT 를사용할수있도록변환됩니다. AVG 는 SUM/COUNT 로변형이가능합니다. Figure 089C MQT 를이용하도록변환되는쿼리 5 drop table 문을이용하여제거합니다. $ db drop table <MQT 명 > DB 9.7 운영자가이드
08 0 S E C T 0 인덱스 효율적인데이터액세스를위해서한테이블에한개이상의인덱스를생성할수있습니다. CREATE INDEX 문과 DROP INDEX 문으로관리합니다. 고유인덱스는 NULL 값을허용하며, NULL 값을가진행은한개만허용됩니다. 테이블에기본키또는고유키를정의하면해당컬럼에대한자동으로 UNIQUE 인덱스가생성됩니다. CLUSTER 옵션을가진인덱스는한테이블에한개만가능하므로, 가장중요한인덱스를 CLUSTER 인덱스로정의합니다. INCLUDE 옵션은 UNIQUE 인덱스에서만사용가능합니다. create index 문으로컬럼명과컬럼별정렬순서를지정합니다. 기본적으로인덱스는중복된 값을허용하므로중복된행을허용하지않는인덱스를생성하려면 UNIQUE 옵션을이용합니다. $ db "create index < 스키마명 >.< 인덱스명 > on < 테이블명 > (< 컬럼명 >)" $ db "create UNIQUE index < 인덱스명 > on < 테이블명 > (< 컬럼명 >)" CLUSTER 옵션을이용하면, 해당인덱스의정렬순서를기준으로테이블의데이터가물리적으 로배치되므로효율적인액세스가가능합니다. $ db "create unique index < 인덱스명 > on < 테이블명 >(< 컬럼명 >) CLUSTER" Figure 080A High Cluster Ratio Index Table 인덱스의클러스터비율 Table Low Cluster Ratio Index INCLUDE 옵션으로추가된컬럼들은인덱스의데이터페이지에 RID 와함께저장되어, 인덱 스전용액세스를가능하게합니다. 반드시 UNIQUE 옵션을함께지정해야합니다. 4 $ db "create UNIQUE index < 인덱스명 > on < 테이블명 > (< 컬럼명 >) INCLUDE (< 컬럼명 >, < 컬럼명 >, )" ALLOW REVERSE SCANS 옵션으로생성된인덱스는양방향액세스를허용합니다. 5 $ db "create unique index < 인덱스명 > on < 테이블명 > (< 컬럼명 >) cluster ALLOW REVERS SCANS" drop index 문으로제거하며, 테이블이제거되면자동으로제거됩니다. $ db "drop index < 스키마명 >.< 인덱스명 >" 6 인덱스에대한정보는 SYSCAT.INDEXES 뷰또는 describe indexes 명령어를이용하여확인합니다. $ db "select * from syscat.indexes" $ db describe indexes for table < 스키마명 >.< 테이블명 > show detail 7 dblook 명령어를이용하여테이블과함께인덱스에대한 DDL 을추출합니다. $ dblook d <DB 명 > e z < 스키마명 > v < 테이블명 > -o < 출력파일명 > UNIT 08 데이터베이스오브젝트
008 CREATE INDEX 문 테이블에인덱스를생성하는 SQL문입니다. UNIQUE, CLUSTER, INCLUDE, ALLOW REVERSE SCANS 등의옵션을가진인덱스를생성할수있습니다. SYSADM, DBADM 권한또는테이블에대한 CONTROL, INDEX 특권이필요할수있습니다. create index 문의형식은다음과같습니다. 새로운스키마명을이용하여인덱스명을지정하려면, 데이터베이스에대한 IMPLICIT_SCHEMA 특권이필요합니다. 기존의스키마명을이용하여인덱스명을지정하려면, 스키마에대한 CREATIN 특권이필요합니다. Figure 08A CREATE INDEX 문 INCLUDE 옵션에서지정한컬럼은인덱스를구성하는컬럼이아니므로, 조건식에서이컬럼을이용하여검색을요청할때, index scan 이선택되지않습니다. 옵션에대한은다음과같습니다. 모드 UNIQUE 고유인덱스로생성합니다. < 인덱스명 > 임의의고유한이름으로지정합니다. ON < 테이블명 > 인덱스가생성될테이블명을지정합니다. < 컬럼명 > 인덱스를구성하는컬럼명을지정합니다. 한개이상인경우에는,( 컴마 ) 로구분합니다. ASC / DESC INCLUDE (< 컬럼명 >) 컬럼별정렬순서를오름차순 (ASC) 또는내림차순 (DESC) 로지정합니다. 기본값은 ASC 입니다. 인덱스의리프페이지에 RID 와함께저장되는컬럼을지정합니다. 한개이상인경우에는,( 컴마 ) 로구분합니다. CLUSTER 클러스터인덱스로지정합니다. PCTFREE MINPCTUSED 인덱스의각페이지의여유공간비율로백분율로지정합니다. 기본값은 0 입니다. 인덱스의리프페이지의최소사용공간비율로백분율로지정합니다. 50 이하로지정하는것이성능에유리합니다. ALLOW REVERSE SCANS 역방향액세스를허용합니다. DB 9.7 운영자가이드
08 0 S E C T 시퀀스 데이터베이스치원에서제공되는자동생성일련번호를시퀀스라고합니다. CREATE SEQUENCE 문, ALTER SEQUENCE 문, DROP SEQUENCE 문으로관리합니다. 테이블의 identity 컬럼과기능상동일합니다. identity 컬럼은해당테이블에서만유효하며, 시퀀스는데이터베이스수준에서운영됩니다. create sequence 문의형식은다음과같습니다. 데이터베이스가비활성화되면, 사용되지않고캐쉬에남아있던자동생성값은유실됩니다. Figure 08A 주요한옵션은다음과같습니다. 옵션 < 시퀀스명 > 임의의고유한이름으로지정합니다. AS < 데이터유형 > CREATE SEQUENCE 문 SAMLLINT, INT, BIGINT, DECIMAL 중에서원하는데이터유형을선택합니다. START WITH 양수또는음수의시작값을지정합니다. INCREMENT BY 양수또는음수의증가값을지정합니다. MINVALUE 양수또는음수의최소값을지정합니다. MAXVALUE 양수또는음수의최대값을지정합니다. CYCLE 최대값또는최소값에도달하면최소값또는최대값을생성합니다. CACHE 지정된개수의생성값을미리캐쉬에보관하여성능에유리합니다. drop sequence dept_seq restrict; create sequence dept_seq start with 500 increment by cache 0 no cycle no maxvalue; select prevval for dept_seq from sysibm.sysdummy; -------- 50 nextval 을호출할때마다증가합니다. 최근의 nextval 호출로반환된값이 prevval 입니다. insert into dept values (nextval for dept_seq, Sales ); insert into dept values (nextval for dept_seq, Marketting ); select * from dept; C C ------------------------------ 500 Sales 50 Marketting Figure 08B PREVVAL 과 NEXTVAL UNIT 08 데이터베이스오브젝트 4
008 트리거 특정테이블에 INSERT, UPDATE, DELETE 문이실행될때자동으로실행되는일련의작업들을정의합니다. 트리거의이벤트유형은 BEFORE, AFTER, INSTEAD OF가있으며, CREATE TRIGGER 문과 DROP TRIGGER 문으로관리합니다. 트리거가구현하는비즈니스규칙은데이터베이스오브젝트에대한일련의변경작업또는예외처리로직입니다. 트리거는특정조건을만족하는경우에만 SQL문의요청을허용하기위한용도로사용되기도합니다. 한데이터베이스의특정테이블에대한변경작업이다른테이블에영향을미칠수있습니다. 응용프로그램의로직에서이러한비즈니스규칙을구현하면, 비즈니스규칙이변경될때마다응용프로그램의로직을수정해야합니다. 트리거는특정테이블또는뷰에대한변경작업이요청될때마다자동으로실행되어야하는일련의작업들을데이터베이스수준에서정의합니다. 비즈니스규칙이변경되되어도데이터베이스에존재하는트리거의정의만변경하면되므로, 모든응용프로그램은추가적인로직의변경없이새로운비즈니스규칙을적용할수있습니다. Business Rules: INSERT, UPDATE, DELETE 데이터베이스 사용자프로그램 ENFORCEMENT APPLICATION DATABASE Figure 08A 비즈니스규칙 트리거에정의된비즈니스로직을실행하는시점에의해 가지유형으로분류됩니다. 유형 AFTER 트리거 BEFORE 트리거 특정테이블에대해요청된 INSERT, UPDATE, DELETE 문을먼저실행하고, 정의된일련의작업들을실행합니다. 정의된일련의작업들을먼저실행하고, 특정테이블에대해요청된 INSERT, UPDATE, DELETE 문을실행합니다. 트리거는발생시키는 SQL 문의유형에의해 가지유형으로분류됩니다. 유형 INSERT 트리거특정테이블에대해 INSERT 문이요청된경우에실행됩니다. UPDATE 트리거 특정테이블에대해 UPDATE 문이요청된경우에실행됩니다. 특정컬럼을지정할수도있습니다. DELETE 트리거특정테이블에대해 DELETE 문이요청된경우에실행됩니다. 4 트리거의기준이되는대상은 가지로분류됩니다. 유형 테이블트리거 뷰트리거 INSTEAD OF 트리거 특정테이블에 INSERT, UPDATE, DELETE 문이요청될때요청된 SQL 문과정의된일련의작업들을실행합니다. 특정뷰에 INSERT, UPDATE, DELETE 문을요청하면, 요청된 SQL 문대신에정의된일련의작업들을실행합니다. 5 DB 9.7 운영자가이드
08 0 S E C T 4 CREATE TRIGGER 문 트리거를생성하는 SQL문입니다. 단일 SQL문또는 SQL/PL을이용하여트리거가실행할로직을구현합니다. INSERT, UPDATE, DELETE 트리거는개별적으로생성하고, 트리거의정의에변경이있을때는재생성합니다. SYSADM, DBADM 권한또는테이블에대한 CONTROL 특권이필요할수있습니다 create trigger 문의형식은다음과같습니다. IMPLICIT_SCHEMA 데이터베이스특권, 스키마에대한 ALTERIN, CREATEIN 특권이필요합니다. BEFORE, AFTER 트리거를정의할때는테이블에대한 ALTER 특권이필요합니다. INSTEAD OF 트리거를정의할때는뷰에대한 CONTROL 특권이필요합니다. TRANSITION 변수또는테이블을사용할때는테이블에대한 SELECT 특권이필요합니다. Figure 084A CREATE TRIGGER 문 < 트리거본문 > transition 행또는테이블은트리거의비즈니스로직에서이용할수있습니다. DELETE, UPDATE 트리거에서사용됩니다. UPDATE 옵션은모든컬럼의변경에적용됩니다. OF 옵션은지정한컬럼의 UPDATE 시에만적용됩니다. 트리거본문에정의되는비즈니스로직은기본적으로단일 SQL문입니다. 여러개의 SQL문과논리적인로직을구현하려면 BEGIN~END 블록을이용하여SQL/PL을이용합니다. 옵션에대한은다음과같습니다. 모드 < 트리거명 > 임의의고유한이름으로지정합니다. NO CASCADE BEFORE BEFORE 트리거를생성합니다. AFTER AFTER 트리거를생성합니다. INSTEAD OF INSTEAD OF 트리거를생성합니다. INSERT INSERT 트리거를생성합니다. DELETE DELETE 트리거를생성합니다. UPDATE OF < 컬럼명 > UPDATE 트리거를생성합니다. REFERENCING transition 테이블또는행의이름을지정합니다. OLD AS <id> OLD_TABLE AS <id> NEW AS <id> NEW_TABLE AS <id> 요청된 SQL 문이실행되기전의행또는테이블의값을보관하고있는 transition 행또는테이블 id 입니다. 요청된 SQL 문이실행된후의행또는테이블의값을보관하고있는 transition 행또는테이블 id 입니다. FOR EACH ROW 조건에맞는모든행에개별적으로트리거가적용됩니다. FOR EACH STATEMENT 조건에맞는행의개수에상관없이한번만실행됩니다. WHEN < 조건식 > < 조건식 > 을만족하는경우에만본문의로직을실행합니다. < 트리거본문 > SQL/PL 을이용하여로직을작성합니다. UNIT 08 데이터베이스오브젝트 6
008 5 BEFORE 트리거 특정테이블에대한 INSERT, UPDATE, DELETE 문을실행하기전에먼저정의된일련의작업들을실행합니다. CREATE TRIGGER 문에서 NO CASCADE BEFORE 옵션을사용하여생성합니다. INSERT, UPDATE, DELETE 문에대한트리거를한개이상생성할수있습니다. create trigger 문에서 NO CASCADE BEFORE 옵션으로생성합니다. CREATE TABLE kes.class ( number smallint not null, name varchar(0), length smallint ); CREATE TABLE kes.test ( id int not null, number smallint, test_date date, start_time time, seat smallint, score smallint ); INSERT INTO kes.class VALUES (,'DB',60); CREATE TRIGGER pre9 NO CASCADE BEFORE INSERT ON kes.test REFERENCING NEW AS N FOR EACH ROW 실패 : MODE DBSQL SQL048N 응용프로그램이진단텍스트 "09:00 이전에는죄석을예 WHEN (N.START_TIME < '09:00:00') 약할수없습니다!" 과 ( 와 ) 함께오류를표시했습니다. SQLSTATE=7000 SIGNAL SQLSTATE '7000' ('09:00 이전에는죄석을예약할수없습니다!'); INSERT INTO kes.test VALUES (,,'006-04-9','08:0:00',,NULL); CREATE TRIGGER aft5 NO CASCADE BEFORE INSERT ON kes.test REFERENCING NEW AS N FOR EACH ROW MODE DBSQL WHEN (N.START_TIME + (SELECT SMALLINT(LENGTH) FROM test 실패 : SQL048N 응용프로그램이진단텍스트 "6:00 이후에는좌석을예약할수없습니다!" 과 ( 와 ) 함께오류를표시했습니다. SQLSTATE=70004 WHERE NUMBER = N.NUMBER) MINUTES > '7:00:00') SIGNAL SQLSTATE '70004' ('6:00 이후에는좌석을예약할수없습니다!'); INSERT INTO kes.test VALUES (,,'006-04-9','6:0:00',,NULL); INSERT INTO kes.test VALUES (,,'006-04-9','0:00:00',,NULL); SELECT FROM id, number, start_time kes.test; Figure 085A BEFORE 트리거 7 DB 9.7 운영자가이드
08 0 S E C T 6 AFTER 트리거 특정테이블에대해요청된 INSERT, UPDATE, DELETE 문을먼저실행하고, 정의된비즈니스로직을실행합니다. 최대 6 레벨까지다른트리거를연쇄적으로수행할수있습니다. CREATE TRIGGER 문에서 AFTER 옵션을사용하여생성합니다. INSERT, UPDATE, DELETE 문에대한트리거를한개이상생성할수있습니다. AFTER 트리거를생성하는예는다음과같습니다. CREATE TABLE kes.empl ( id smallint not null, name varchar(0) not null, sex char(), mydept smallint, salary smallint, email varchar(0) not null, hiredate date ); INSERT INTO kes.empl VALUES (,'KES','F',,00,'kes@kr.ibm.com','99-0-0'), (,'KHY','F',,50,'khy@kr.ibm.com','99-0-7'), (,'JHS','F',,00,'jhs@kr.ibm.com','997-0-0'), (4,'JJY','M',,80,'jjy@kr.ibm.com','998-07-'); CREATE TABLE kes.log ( id smallint, osalary smallint, nsalary smallint, ts timestamp); CREATE TRIGGER kes.empl_trig0 AFTER UPDATE OF salary ON kes.empl REFERENCING OLD AS o NEW AS n FOR EACH ROW MODE DBSQL WHEN ( n.salary > o.salary *. ) INSERT INTO kes.log VALUES(o.id,o.salary,n.salary,current timestamp ); UPDATE kes.empl SET salary = salary + 0 WHERE hiredate < '000-0-0' ; SELECT id, salary, hiredate FROM kes.empl ORDER BY id ; SELECT id, osalary, nsalary FROM kes.log; 변경된 salary 값이 0% 이상인경우에만 kes.log 에추가합니다. Figure 086A AFTER 트리거 UNIT 08 데이터베이스오브젝트 8
008 7 INSTEAD OF 트리거 뷰를대상으로정의된트리거로테이블에대한변경 SQL문을뷰를통해서관리하거나, 뷰에직접허용되지않는변경 SQL문을트리거로직으로대신실행할때도사용됩니다. CREATE TRIGGER 문에서 INSTEAD OF 옵션을사용하여생성합니다. 특정뷰에대한 INSERT, UPDATE, DELETE 문을처리하는트리거는한개씩만생성할수있습니다. FOR EACH STATEMENT 옵션은사용할수없습니다. create trigger 문에서 INSTEAD OF 옵션으로생성합니다. CREATE TABLE kes.empl ( id smallint not null, name varchar(0) not null, sex char(), mydept smallint, salary smallint, email varchar(0) not null, hiredate date ); CREATE VIEW kes.empl_v AS SELECT id, name, sex FROM kes.empl; CREATE TRIGGER kes.empl_v_insert INSTEAD OF INSERT ON kes.empl_v REFERENCING NEW AS N FOR EACH ROW MODE DBSQL INSERT INTO kes.empl VALUES ( n.id, n.name, CASE RTRIM(LTRIM(UPPER(n.sex))) WHEN 'M' THEN 'M' WHEN 'F' THEN 'F' ELSE NULL END, NULL, NULL, LOWER(n.name) '@kr.ibm.com', CURRENT DATE days); INSERT INTO kes.empl_v VALUES (,'KES',''); INSERT INTO kes.empl_v VALUES (,'KHY','f'); INSERT INTO kes.empl_v VALUES (,'JHS','F'); INSERT INTO kes.empl_v VALUES (4,'JJY','m'); INSERT INTO kes.empl_v VALUES (5,'XXX','X'); SELECT id, substr(name,,) name, sex. email FROM kes.empl_v ORDER BY id; sex 컬럼의값이 'M', 'm', 'F', 'f' 인경우에대문자로변환하여입력합니다. '' 값인경우에는 NULL 값으로입력합니다. email 컬럼과 hiredate 컬럼에는 name 컬럼의값과시스템날짜를이용한값이입력됩니다. Figure 087A INSTEAD OF 트리거 9 DB 9.7 운영자가이드
08 0 S E C T 8 사용자정의데이터유형 사용자가추가적으로정의할수있는데이터유형입니다. 일반적으로도량형과관련된데이터를저장할때이용합니다. CREATE DISTINCT TYPE 문과 DROP DISTINCT TYPE 문으로관리합니다. 단위가서로다른의미를가지는두개의값을단순히값으로만비교하는것은잘못된결과를만 들게됩니다. 어? 난지금 90 이라구요. 뭐가문제죠? 속도위반입니다. 60 을넘으셨군요. 이거받으세요.--;; SPEED 00 LIMIT SPEED 65 LIMIT CANADA USA Figure 088A UDT 의필요성 with comparisos 옵션은새로생성한 UDT 형으로형변환을할수있는형변환 (CAST) 함수를기본적으로제공합니다. 단위가다른두값을직접비교하는것을방지하기위해사용자가 create distinct type 문으로새로운데이터유형을생성할수있습니다. 유형이다른두데이터는서로직접비교될수없습니다. CREATE DISTINCT TYPE dollar AS INTEGER WITH COMPARISONS ; CREATE DISTINCT TYPE won AS INTEGER WITH COMPARISONS ; CREATE TABLE kes.person VALUES ( f_name VARCHAR (0), money_d dollar NOT NULL, money_w won NOT NULL ) ; INSERT INTO kes.person VALUES (KES', 80, 70),('JHS',80,00); SELECT f_name FROM kes.person WHERE money_d < money_w; money_d 와 money_w 컬럼을다른유형이므로직접비교가불가능합니다. money_w 컬럼을 dollar 유형으로형변환하였으므로비교가가능합니다. SELECT f_name FROM kes.person WHERE money_d < dollar(money_w) OR won(money_d) < money_w; money_d 컬럼을 won 유형으로형변환하였으므로비교가가능합니다. Figure 088B 다른데이터유형간의비교실패 UNIT 08 데이터베이스오브젝트 0
008 9 사용자정의함수 사용자가직접구현하여엔진에추가적으로정의한함수입니다. 사용자정의함수는기존의내장 SQL 함수와동일한방법으로 SQL문에서사용됩니다. CREATE FUNCTION문과 DROP FUNCTION 문으로관리합니다. DB 에서사용하는함수는생성주체에따라 가지유형으로구분됩니다. 유형 빌트인엔진에의해기본적으로제공되는함수입니다. 내장함수라고합니다. 사용자정의사용자가새롭게작성하여엔진에추가한함수입니다. 적용되는대상과반환하는값의유형에따라함수는 4 가지유형으로구분됩니다. 기존의함수에서 UDT 유형으로정의된컬럼을사용될수없습니다. 기존의함수를소스함수로하는새로운 UDF가필요합니다. Java 언어를이용하면, JAR 형태로 UDF를관리할수있습니다. 사용자정의함수를작성한언어에따라라이브러리를생성하는방법이다릅니다. SQL/PL로작성하는경우에는별도의생성과정없이 create function 문으로생성합니다. 4 5 유형 스칼라조건에맞는모든행에대해서각각함수를실행하여결과를반환합니다. 문자함수, 수학함수, 날짜함수등이있습니다. 컬럼 조건에맞는행들을지정된값에의해그룹으로분류하고, 각그룹별로함수를적용시킨결과값을반환합니다. 집계함수가해당됩니다. 행조건에맞는결과행을 Row 형태로반환하는함수입니다. 테이블 조건에맞는결과집합을 table 구조로반환합니다. SELECT 문의 FROM 절에서사용되며, 일반테이블과동일한방법으로액세스할수있습니다. 스냅샷함수가해당됩니다. 사용자정의함수의유형은작성하는언어와반환하는결과의유형에따라 7 가지로분류됩니다. 유형 외부스칼라 외부테이블 C, Java 등의프로그래밍언어로작성된라이브러리를이용하며, 스칼라값을반환합니다. C, Java 등의프로그래밍언어로작성된라이브러리를이용하며, 테이블을반환합니다. OLE DB 외부테이블 OLE DB 공급자로부터데이터를액세스하기위한함수입니다. 소스함수 기존의내장, 외부, SQL, 소스함수를이용하여작성된함수입니다. SQL 스칼라 SQL/PL 로작성되며, 스칼라값을반환합니다. SQL 행 SQL/PL 로작성되며, 행을반환합니다. SQL 테이블 SQL/PL 로작성되며, 테이블을반환합니다. 사용자정의함수는 SQL/PL, C, Java, OLE 등을이용하여생성합니다. 사용자정의함수에대한정보는 SYSCAT.ROUTINES 뷰를이용해서확인합니다. $ db "select * from syscat.routines" 6 dblook 명령어로 SQL 사용자정의함수에대한 DDL 문을추출할수있습니다. $ dblook d <DB 명 > e -o < 출력파일명 > DB 9.7 운영자가이드
08 0 S E C T 0 CREATE FUNCTION 문 SQL/PL을이용하여작성한사용자정의함수의라이브러리를생성하고시그너처를등록하는 SQL문입니다. C, Java, OLE 등으로적성된사용자정의함수는시그너처만등록되며, 개별적인방법으로함수의라이브러리를생성해야합니다. SYSADM, DBADM 권한또는테이블에대한 CONTROL, SELECT 특권이필요할수있습니다. create function 문의형식은다음과같습니다. 새로운스키마명을이용하여인덱스명을지정하려면, 데이터베이스에대한 IMPLICIT_SCHEMA 특권이필요합니다. 기존의스키마명을이용하여인덱스명을지정하려면, 스키마에대한 CREATIN 특권이필요합니다. 함수의시그너처가다르면동일한이름으로함수를생성할수있습니다. 동일한이름으로함수를오버로딩할때는 SPECIFIC 옵션으로고유한함수명을지정하는것이권장됩니다. 사용자정의함수의 <SPECIFIC명 > 은 < 함수명 > 과동일하게지정할수있습니다. 옵션을지정하지않으면, SQLyymmddhhmmssxxx 형식으로엔진이자동생성합니다. SPECIFIC 옵션은 drop 문또는다른함수에서참조되는이름으로만사용됩니다. SQL문에서실제로함수를사용할때는 < 스키마명 >.< 함수명 > 을사용해야합니다. Figure 080A SQL 사용자정의함수를위한 CREATE FUNCTION 문옵션에대한은다음과같습니다. 모드 < 함수명 > 함수의이름을지정합니다. < 인수이름 > < 데이터유형 > 입력인수의이름과데이터유형을지정합니다. RETURNS < 데이터유형 > 반환되는값의데이터유형입니다. SPECIFIC < 고유함수명 > 임의의고유한이름으로지정합니다. LANGUAGE SQL SQL/PL을이용하여로직을작성합니다. DETERMINISTIC 동일한입력인수에대해서항상동일한값을반환합니다. CONTAINS SQL 데이터의조회, 변경을위한 SQL문을포함하지않습니다. READS SQL DATA 데이터를변경하는 SQL문을포함할수없습니다. MODIFIES SQL DATA 지원되는모든 SQL문을사용합니다. CALLED ON NULL INPUT 입력인수의값이 NULL인경우에도호출됩니다. <SQL 함수본문 > 단일 SQL문또는 SQL/PL 블록을이용하여구현합니다. UNIT 08 데이터베이스오브젝트
008 SQL 사용자정의함수 단일 SQL문또는 SQL/PL을이용하여작성한사용자정의함수를 SQL 사용자정의함수라고합니다. CREATE FUNCTION 문으로함수의라이브러리가생성되고, 함수의시그너처도등록됩니다. 내장함수와동일한방법으로 SQL문에서사용합니다. 단일한 SQL문으로표현되지않는로직을구현하려면, SQL/PL 의 BEGIN ~ END 블록을이용합니다. SQL/PL에서 ; ( 세미콜론 ) 은문장의구분자로사용됩니다. create function 문의끝을구별할때는 @ 또는! 등의문자를사용합니다. SQL 사용자정의함수를위한로직을작성하여임의의 < 파일명 > 으로저장합니다. $ cat < 파일명 > CREATE FUNCTION todate (x varchar(8)) RETURNS date SPECIFIC TODATE0 LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC RETURN date(substr(x,,4) '-' SUBSTR(X,5,) '-' SUBSTR(X,7,)) @ CREATE FUNCTION tan (X DOUBLE) RETURNS DOUBLE LANGUAGE SQL CONTAINS SQL NO EXTERNAL ACTION DETERMINISTIC RETURN SIN(X)/COS(X) @ CREATE FUNCTION kes.percent(number int, rate int) RETURNS decimal(6,) F: BEGIN ATOMIC RETURN (number * rate) / 00; END@ SQL 사용자정의함수는엔진에내장된특별한컴파일러를이용하여라이브러리를작성합니다. 별도의 C 컴파일러는필요하지않습니다. UDF를참조하려면 EXECUTE 권한이필요합니다. UDF명은대소문자를구분하지않습니다. create function 문을이용하여 SQL 사용자정의함수를생성하고, 등록합니다. $ db connect to < 데이터베이스명 > $ db -td@ -svf < 파일명 > SQL 문에서기존의내장함수와동일한방법으로사용자정의함수를참조합니다. $ db x " values(todate('00400'))" 004-0-0 $ db -x "SELECT id, salary, kes.percent(salary,5) FROM kes.empl" 0 6.00 80 4.00 0 6.00 4 0 5.00 DB 9.7 운영자가이드
08 0 S E C T 저장프로시저 서버에저장된프로그램로직입니다. 클라이언트에서 CALL 문으로서버의저장프로시저를호출하면, 서버에서로직이실행되어결과만클라이언트로반환됩니다. CREATE PROCEDURE 문과 DROP PROCEDURE 문으로관리합니다. 저장프로시저는클라이언트머신의응용프로그램에서실행해야하는로직을서버의데이터베이 스에저장하여서버머신에서직접실행함으로써클라언트와서버간의데이터전송량을줄이고, 성능을향상시킵니다. Figure 08A 저장프로시저의장점공용로직을저장프로시저로만들어서사용하면관리가용이합니다. 클라이언트응용프로그램의개별적인코딩으로인한오류와소스를반복적으로작성해야하는부담을줄일수있습니다. 프로시저의로직이변경되면, 서버의저장프로시저만재생성하면됩니다. 저장프로시저는실행시에서버의자원을사용하여실행됩니다. 일반적으로클라이언트머신보 다서버머신의사양이좋으므로, 동일한로직을실행할때실행시간이단축될수있습니다. 4 서버머신의 OS 에의존적인로직의구현이가능합니다. 클라이언트가 Windows 이고, 서버 가 UNIX 인경우에 UNIX 에서만지원되는기능을프로시저의로직에포함시킬수있습니다. PL/SQL은 DB 9.7이후지원되는프로시저언어입니다. 저장프로시저를작성한언어에따라라이브러리를생성하는방법이다릅니다. SQL/PL 또는 PL/SQL로작성하는경우에는별도의생성과정없이 create proceudre 문으로생성합니다. 5 6 7 8 저장프로시저의유형은작성하는언어에의해 가지로분류됩니다. 유형 외부소스 저장프로시저는 SQL/PL, PL/SQL, ESQL, C, Java 등의언어를이용하여생성합니다. 저장프로시저에대한정보는 SYSCAT.ROUTINES 뷰를이용해서확인합니다. $ db "select * from syscat.routines" dblook 명령어로 SQL 저장프로시저에대한 DDL 문을추출할수있습니다. $ dblook d <DB 명 > e -o < 출력파일명 > ESQL, C, Java 등의프로그래밍언어로작성된라이브러리를이용하며, 스칼라값또는결과집합을반환합니다. SQL SQL/PL 또는 PL/SQL 로작성되며, 스칼라값또는결과집합을반환합니다. UNIT 08 데이터베이스오브젝트 4
008 CREATE PROCEDURE 문 SQL/PL 저장프로시저의라이브러리를생성하고시그너처를등록하는 SQL문입니다. ESQL, C, Java 등으로적성된저장프로시저는시그너처만등록되며, 개별적인방법으로저장프로시저의라이브러리를생성해야합니다. SYSADM, DBADM 권한또는데이터베이스에대한 BINDADD 특권이필요할수있습니다. create procedure 문의형식은다음과같습니다. Figure 08A SQL/PL 저장프로시저를위한 CREATE PROCEDURE 문 사용자정의함수의 <SPECIFIC명 > 은 < 함수명 > 과동일하게지정할수있습니다. 옵션을지정하지않으면, SQLyymmddhhmmssxxx 형식으로엔진이자동생성합니다. 옵션에대한은다음과같습니다. 모드 < 프로시저명 > 프로시저의이름을지정합니다. < 인수입출력유형 > 인수의유형은 IN, OUT, INOUT 으로지정합니다. < 인수이름 > 인수의이름을지정합니다. < 데이터유형 > 인수의데이터유형을지정합니다. SPECIFIC 임의의고유한이름으로지정합니다. DYNAMIC RESULT SETS 반환할결과집합의개수를지정합니다. CONTAINS SQL 데이터의조회, 변경을위한 SQL 문을포함하지않습니다. READS SQL DATA 데이터를변경하는 SQL 문을포함할수없습니다. MODIFIES SQL DATA 지원되는모든 SQL 문을사용합니다. DETERMINISTIC 동일한입력인수에대해서항상동일한값을반환합니다. CALLED ON NULL INPUT 입력인수의값이 NULL 인경우에도호출됩니다. LANGUAGE SQL SQL/PL 로작성한 SP 입니다. NO EXTERNAL ACTION 데이터베이스시스템이관리하지않는외부오브젝트에대한상태를변경시키는로직의포함여부를지정합니다. <SQL 함수본문 > SQL/PL 블록을이용하여구현합니다. 5 DB 9.7 운영자가이드
08 0 S E C T 4 SQL/PL 프로시저 SQL/PL 언어를이용하여저장프로시저를생성합니다. CALL 문을이용하여호출합니다. SQL/PL에서 ; ( 세미콜론 ) 은문장의구분자로사용됩니다. create function 문의끝을구별할때는 @ 또는! 등의문자를사용합니다. SQL 저장프로시저를위한로직을작성하여임의의 < 파일명 > 으로저장합니다. $ cat < 파일명 > CREATE PROCEDURE myproc (IN deptnumber CHAR(), OUT mediansalary DOUBLE) LANGUAGE SQL BEGIN DECLARE SQLCODE INTEGER; DECLARE SQLSTATE CHAR(5); DECLARE v_numrecords INT DEFAULT ; DECLARE v_counter INT DEFAULT 0; DECLARE c CURSOR FOR SELECT CAST(salary AS DOUBLE) FROM employee WHERE workdept = deptnumber ORDER BY salary; DECLARE EXIT HANDLER FOR NOT FOUND SET mediansalary = 6666; SET mediansalary = 0; SELECT COUNT(*) INTO v_numrecords FROM employee WHERE workdept = deptnumber; OPEN c; WHILE v_counter < (v_numrecords / + ) DO FETCH c INTO mediansalary; SET v_counter = v_counter + ; END WHILE; CLOSE c; END @ create procedure 문을이용하여 SQL 저장프로시저를생성하고, 등록합니다. $ db connect to < 데이터베이스명 > $ db -td@ -svf < 파일명 > SP를호출하려면 EXECUTE 권한이필요합니다. SP명은대소문자를구분하지않습니다. CLP에서 SP를호출하려면, OUT 유형의인수값에는출력용변수명대신에?( 물음표 ) 를이용합니다. CALL 문을이용하여저장프로시저를호출합니다. $ db "call myproc('a00',?)" Value of output parameters -------------------------- Parameter Name : MEDIANSALARY Parameter Value : +4.65000000000000E+004 Return Status = 0 UNIT 08 데이터베이스오브젝트 6
008 5 PL/SQL 프로시저 PL/SQL 언어를이용하여저장프로시저를생성합니다. CALL 문을이용하여호출합니다. 9.7 이후로는 PL/SQL을이용하여저장프로시저를생성할수있습니다. PL/SQL 을이용하여저장프로시저를생성하고자하는경우에는 Registry 변수에 compatibility vector 를 800 로설정한후, 데이터베이스를생성합니다. $ dbset compatibility Vector = 800 create procedure 문을이용하여 SQL 저장프로시저를개발합니다. $ cat script.db set sqlcompat plsql/ 작성된구문이 PL/SQL 구문임을알려주는기능 CREATE OR REPLACE FUNCTION emp_comp ( p_sal NUMBER, p_comm NUMBER ) RETURN NUMBER IS BEGIN RETURN (p_sal + NVL(p_comm, 0)) * 4; END emp_comp / CREATE OR REPLACE PROCEDURE update_comp(p_name IN VARCHAR) AS BEGIN UPDATE emp SET tot_comp = emp_comp(salary, comm) WHERE name = p_name; END update_comp / 작성된스크립트에서구분자가 / 또는 ; 인경우에는 - td/-vf로생성합니다. DBCLP 창에서컴파일및생성합니다. $ db td/ -vf script.db 4 CLP 창에서프로시저를호출할때에는 CALL 을사용합니다. $ db call update_comp( Curly ) 7 DB 9.7 운영자가이드
06 0 S E C T Memo UNIT 08 데이터베이스오브젝트 8