Altibase Stored Procedure Manual

Transcription

1 ALTIBASE HDB Application Development Stored Procedures Manual Release (January 16, 2013)

2 ALTIBASE Application Development Stored Procedures Manual Release Copyright c 2001~2011 ALTIBASE Corp. All Rights Reserved. 본문서의저작권은 알티베이스에있습니다. 이문서에대하여당사의동의없이무단으로복제또는전용할수없습니다. 알티베이스 서울시구로구구로동 대륭포스트타워 Ⅱ 10 층전화 : 팩스 : 고객서비스포털 : homepage:

3 목차 서문 i 이매뉴얼에대하여 ii 1. 저장프로시저 1 저장프로시저의개요 2 저장프로시저의구조 5 저장프로시저사용시주의사항 7 2. 저장프로시저 SQL문 9 개요 10 CREATE PROCEDURE 12 ALTER PROCEDURE 20 DROP PROCEDURE 22 EXECUTE 23 CREATE FUNCTION 25 ALTER FUNCTION 29 DROP FUNCTION 저장프로시저블록 31 저장프로시저블록 32 지역변수선언 35 SELECT INTO 43 할당문 49 LABEL 52 PRINT 55 RETURN 흐름제어 61 개요 62 목차 I

4 IF 63 CASE 69 LOOP 73 WHILE LOOP 75 FOR LOOP 77 EXIT 83 CONTINUE 87 GOTO 89 NULL 커서 95 커서의개요 96 CURSOR 98 OPEN 101 FETCH 104 CLOSE 107 Cursor FOR LOOP 108 커서속성 사용자정의타입 117 개요 118 사용자정의타입의정의 120 Associative Array 관련함수 123 RECORD 타입변수및 Associative Array변수의사용 128 REF CURSOR 타입세트 139 개요 140 CREATE TYPESET 143 DROP TYPESET 동적 SQL 149 동적 SQL의개요 150 EXECUTE IMMEDIATE 153 OPEN FOR 156 II Stored Procedures Manual

5 9. 예외처리 159 개요 160 EXCEPTION 163 RAISE 164 RAISE_APPLICATION_ERROR 166 사용자정의예외 168 SQLCODE와 SQLERRM 171 Exception Handler 내장함수와저장프로시저 179 파일제어 180 DataPort 205 그외함수들 224 A. 예제 227 저장프로시저예제 228 파일제어예제 238 찾아보기 241 목차 III

6

7 서문 서문 i

8 이매뉴얼에대하여 이매뉴얼은저장프로시저의개념및사용방법에대해설명한다. 대상사용자 이매뉴얼은다음과같은알티베이스사용자를대상으로작성되었다. 데이터베이스관리자 성능관리자 데이터베이스사용자 응용프로그램개발자 기술지원부 다음과같은배경지식을가지고이매뉴얼을읽는것이좋다. 컴퓨터, 운영체제및운영체제유틸리티운용에필요한기본지식 관계형데이터베이스사용경험또는데이터베이스개념에대한이해 컴퓨터프로그래밍경험 데이터베이스서버관리, 운영체제관리또는네트워크관리경험 소프트웨어환경 이매뉴얼은데이터베이스서버로알티베이스버전 을사용한다는가정하에작성되었다. 이매뉴얼의구성 이매뉴얼은다음과같이구성되어있다. 제 1 장저장프로시저이장은저장프로시저의개념및구조, 사용시주의사항에대해설명한다. 제 2 장저장프로시저 SQL 문이장은저장프로시저 SQL 문에대한사용방법에대해설명한다. ii Stored Procedures Manual

9 제 3 장저장프로시저블록이장은저장프로시저블록의개념, 저장프로시저바디내에서선언하는지역변수및사용가능한문장에대해설명한다. 제 4 장흐름제어이장은저장프로시저바디내에서절차적프로그램작성이가능하도록프로그램흐름을제어할수있는흐름제어문에대해설명한다. 제 5 장커서이장은저장프로시저내에서조회레코드건수가여러개인 SELECT 문을처리할수있도록커서를정의하고레코드를제어할수있는커서관련문들에대해설명한다. 제 6 장사용자정의타입이장은저장프로시저내에서사용자정의타입인 record 및 associative array 의정의및사용방법에대해설명한다. 제 7 장타입세트이장은사용자정의타입의집합인타입세트의정의및사용방법에대해설명한다. 제 8 장동적 SQL 이장은실행시간에사용자가원하는질의를만들어서실행하기위한동적 SQL 에대해설명한다. 제 9 장예외처리이장은저장프로시저실행중오류발생시저장프로시저내에서오류에대한예외처리가가능하도록하는예외처리관련문에대해설명한다. 제 10 장파일제어이장은저장프로시저의운영체제텍스트파일에대한읽기및쓰기기능에대하여설명한다. A. 부록이장은이매뉴얼의예제에서사용한스키마에대한설명과저장프로시저를이용한예제프로그램을설명한다. 문서화규칙 이절에서는이매뉴얼에서사용하는규칙에대해설명한다. 이규칙을이해하면이매뉴얼과설명서세트의다른매뉴얼에서정보를쉽게찾을수있다. 여기서설명하는규칙은다음과같다. 구문다이어그램 샘플코드규칙 서문 iii

10 구문다이어그램 이매뉴얼에서는다음구성요소로구축된다이어그램을사용하여, 명령문의구문을설명한다. 구성요소 예약어 의미 명령문이시작한다. 완전한명령문이아닌구문요소는화살표로시작한다. 명령문이다음라인에계속된다. 완전한명령문이아닌구문요소는이기호로종료한다. 명령문이이전라인으로부터계속된다. 완전한명령문이아닌구문요소는이기호로시작한다. ; 명령문이종료한다. SELECT 필수항목 선택적항목 NOT ADD 선택사항이있는필수항목. 한항목만제공해야한다. DROP 선택사항이있는선택적항목. ASC DESC ASC 선택적항목. 여러항목이허용된다. 각반복앞부분에콤마가와야한다. DESC, 샘플코드규칙 코드예제는 SQL, Stored Procedure, isql, 또는다른명령라인구문들을예를들어설명한다. 아래테이블은코드예제에서사용된인쇄규칙에대해설명한다. 규칙의미예제 [ ] 선택항목을표시 VARCHAR [(size)] [[FIXED ] VARIABLE] iv Stored Procedures Manual

11 { } 필수항목표시. 반드시하나이상을선택해야되는표시 선택또는필수항목표시의인자구분표시... 그밖에기호 기울임꼴 소문자 대문자 그이전인자의반복표시 예제코드들의생략되는것을표시 위에서보여진기호이외에기호들 구문요소에서사용자가지정해야하는변수, 특수한값을제공해야만하는위치지정자 사용자가제공하는프로그램의요소들, 예를들어테이블이름, 칼럼이름, 파일이름등 시스템에서제공하는요소들또는구문에나타나는키워드 { ENABLE DISABLE COMPILE } { ENABLE DISABLE COMPILE } [ ENABLE DISABLE COMPILE ] SQL> SELECT ename FROM employee; ENAME SWNO HJNO HSCHOI rows selected. EXEC :p1 := 1; acc NUMBER(11,2); SELECT * FROM table_name; CONNECT userid/password; SELECT ename FROM employee; DESC SYSTEM_.SYS_INDICES_; 샘플스키마 이매뉴얼내의예제중의일부는 employees, departments 및 orders 테이블같은샘플테이블에기반하여작성되었다. 이들테이블은 $ALTIBASE_HOME/sample/APRE/schema 디렉터리의 schema.sql 을사용하여생성할수있다. 샘플스키마에대한온전한정보는 General Reference 를참고하기바란다. 관련자료 자세한정보를위하여다음문서목록을참조하기바란다. 서문 v

12 Installation Guide Getting Started Guide SQL Reference isql User s Manual Error Message Reference 온라인매뉴얼 알티베이스고객서비스포털 ( 에서국문및영문매뉴얼 (PDF, HTML) 을받을수있다. 알티베이스는여러분의의견을환영합니다. 이매뉴얼에대한여러분의의견을보내주시기바랍니다. 사용자의의견은다음버전의매뉴얼을작성하는데많은도움이됩니다. 보내실때에는아래내용과함께고객서비스포털 ( 로보내주시기바랍니다. 사용중인매뉴얼의이름과버전 매뉴얼에대한의견 사용자의성함, 주소, 전화번호 이외에도알티베이스기술지원설명서의오류와누락된부분및기타기술적인문제들에대해서이주소로보내주시면정성껏처리하겠습니다. 기술적인부분과관련하여즉각적인도움이필요한경우에는기술지원센터로연락하시기바랍니다. 여러분의의견에항상감사드립니다. vi Stored Procedures Manual

13 1. 저장프로시저 저장프로시저 1

14 저장프로시저의개요 저장프로시저 (Stored Prodedure) 란 SQL 문들과흐름제어문, 할당문, 오류처리루틴등으로구성된데이터베이스객체 (object) 중의하나이다. 저장프로시저는생성될때컴파일되어바로실행가능한상태로데이터베이스에저장되며여러세션에서동시에하나의저장프로시저를실행하는것도가능하다 저장프로시저 (Stored Prodedure) 라는용어는때때로저장프로시저와저장함수 (Stored Function) 을모두지칭하기도한다. 저장프로시저와저장함수는저장함수가실행시값을반환하는것외에는차이가없다. 저장프로시저와저장함수는각각 CREATE PROCEDURE 와 CREATE FUNCTION 구문을사용해서생성할수있다. 이구문에대한자세한설명은 2 장 저장프로시저 SQL 문 을참고하기바란다. 저장프로시저의종류 저장프로시저 저장함수 타입세트 저장프로시저는 SQL 구문이나다른저장프로시저내에서입력인자, 출력인자, 입출력인자를가지고실행할수있다. 저장프로시저호출시, 프로시저의바디부분에정의된절차에따라서 SQL 문을수행하게된다. 저장프로시저는반환값을가지지않지만, 출력인자와입출력인자들을통해프로시저를호출한클라이언트에게값을전달할수도있다. 이는반환값을갖지않기때문에 SQL 문의연산식 (expression) 내에서피연산자로사용될수없다. 값을반환하는것만제외하면저장프로시저와동일하다. 저장프로시저와달리하나의반환값을가지므로 SQL 문의연산식 (expression) 내에서피연산자로사용할수있다. 저장프로시저의사용자정의타입들을정의한집합이다. 이는주로저장프로시저끼리인자또는리턴값으로사용자정의타입을주고받을때사용한다. 자세한내용은 7 장 타입세트 에서다룬다. 2 Stored Procedures Manual

15 저장프로시저의특징 SQL 구문을이용한절차적프로그램 성능 모듈화 소스코드관리의용이성 알티베이스 PSM (Persistent Stored Module) 은흐름제어문과예외처리문을제공하므로 SQL 문을사용해서절차적프로그래밍이가능하다. 여러 SQL 문을순차적으로수행하는클라이언트프로그램의경우에는각 SQL 문수행시마다데이터베이스서버와통신을해야하므로통신비용이많이발생한다. 반면, 저장프로시저로작성된프로그램은프로시저호출시한번의통신만으로여러 SQL 문을수행할수있다. 따라서, 저장프로시저를사용하면통신부하의감소와함께데이터베이스서버와클라이언트응용프로그램간의데이터타입의차이로인해발생하는내부적인데이터타입변환의부하도줄일수있다. 업무절차를구현하는데필요한모든 SQL 작업을하나의저장프로시저로묶어모듈화하여관리할수있다. 저장프로시저는데이터베이스서버에저장되는모듈이기때문에, 업무로직의변경시여러클라이언트에설치된프로그램들을모두수정할필요없이저장프로시저만변경하면되므로프로그램관리가용이하다. 공유와생산성 SQL 과의통합성 한사용자가생성한저장프로시저는데이터베이스에저장되므로접근권한이부여된다른사용자도해당저장프로시저를실행할수있어서로공유할수있을뿐만아니라, 한저장프로시저내에서다른저장프로시저의호출이가능하므로같은업무절차의재프로그래밍이필요없으므로생산성을높일수있다. 저장프로시저내의흐름제어문의조건절은 SELECT 문의조건절을 저장프로시저 3

16 그대로사용할수있다. 즉, C/C++ 등의주언어의흐름제어문의조건절에서는사용할수없는 SQL 문스타일의기능을사용할수있다. 또한, 연산식에부질의 (subquery) 를사용하거나 SQL 문이지원하는시스템제공함수들을그대로사용할수있다는점등 SQL 문과밀착된프로그래밍이가능하다. 에러및예외처리 저장프로시저내에서 Exception Handler 를제공하므로 SQL 문수행도중오류가발생했을때적절한대응조치를서버내에서바로처리할수있다. 저장성 저장프로시저또한데이터베이스객체이기때문에사용자가삭제하기전까지데이터베이스내에저장된다. 따라서업무절차또한데이터베이스에저장하여보존시킬수있다. 4 Stored Procedures Manual

17 저장프로시저의구조 저장프로시저는블록으로구조화된언어로, 저장프로시저의바디는여러개의논리적인블록들로구성된다. 저장프로시저는크게헤더와바디로나뉘어진다. 저장프로시저의바디는하나의큰블록으로서선언부, 프로시저의실제바디, 예외처리부로구성된다. 바디는다시여러개의하위블록들을가질수있다. 저장프로시저구조를예를들어설명하면다음과같다. 저장프로시저 헤더 CREATE FUNCTION mycheck (id IN INTEGER,... ) RETURN INTEGER AS v_name CHAR(20); v_salary INTEGER; comm_missing EXCEPTION; 블록 1 선언부 CURSOR IF id > THEN... DECLARE... 저장프로시저바디 ( 블록 1) 블록 2 블록 1 바디 END IF; RAISE comm_missing;... RETURN v_salary;... EXCEPTION... WHEN comm_missing THEN... 블록 1 예외처리부... 블록 2 는블록 1 의하위블록으로블록 1 의구조와같이선언부, 바디, 예외처리부로구성될수도있다. 흐름제어문도명시적인시작과끝을알수있는하나의블록이다. 저장프로시저 5

18 6 Stored Procedures Manual

19 저장프로시저사용시주의사항 트랜잭션관리 저장프로시저내에서사용가능한트랜잭션제어문은 COMMIT, ROLLBACK 문이다. 저장프로시저내에서사용한트랜잭션제어문은저장프로시저밖의작업에도영향을미칠수있다. 예를들어서 NON-AUTOCOMMIT 모드에서다음과같은작업을수행했다고가정하자. isql> INSERT INTO t1 values (1); isql> INSERT INTO t1 values (2); isql> EXECUTE proc1; proc1 이 INSERT INTO t1 values (3) 구문과 ROLLBACK 문을수행한다면프로시저내에서입력한 3 뿐만아니라프로시저외부의 isql 에서입력한 1 과 2 도 ROLLBACK 된다. 즉, 위의두 INSERT 문과 EXECUTE 문은하나의트랜잭션으로처리된다. 제약사항 커서가 OPEN 된상태에서는 COMMIT 또는 ROLLBACK 을실행할수없다. 즉, 커서를사용하는트랜잭션은커서의 OPEN, FETCH, CLOSE 로구성된전체커서블록을모두포함하고있어야한다. SELECT 문내에서호출되는저장함수의경우저장함수내에 INSERT, UPDATE, DELETE 문은사용할수없으며, 트랜잭션제어문도수행할수없다. INSERT, UPDATE, DELETE 문내에서호출되는저장함수내에서도트랜잭션제어문을수행할수없다. LOB 타입의변수는저장프로시저선언부에서선언할수없으며, LOB 타입칼럼또는다른변수와함께 %TYPE, %ROWTYPE 을이용해서도변수를선언할수없다. LOB 타입의변수는선언을할수없으므로커서에서값을받아올수없다. 따라서, LOB 타입의칼럼은커서문에서참조할수없다. 저장프로시저 7

20 관련메타테이블 저장프로시저관련메타테이블에대한자세한내용은 General Reference 의데이터딕셔너리부분을참조한다. 8 Stored Procedures Manual

21 2. 저장프로시저 SQL 문 저장프로시저 SQL 문 9

22 개요 저장프로시저 SQL 문 아래표는저장프로시저, 함수와타입세트를생성하고관리하는데사용하는 DDL 문을보여준다. CREATE TYPESET 과 DROP TYPESET 구문에대한설명은 7 장타입세트를참고하기바란다. 종류관련문장설명 생성 변경 삭제 CREATE [OR REPLACE] PROCEDURE 문 CREATE [OR REPLACE] FUNCTION 문 CREATE [OR REPLACE] TYPESET 문 ALTER PROCEDURE 문 ALTER FUNCTION 문 DROP PROCEDURE 문 DROP FUNCTION 문 DROP TYPESET 문 새로운저장프로시저를생성하거나이미생성된저장프로시저의정의를변경하는문장이다. 새로운저장함수를생성하거나이미생성된저장함수의정의를변경하는문장이다. 타입세트를생성또는변경하는문장이다. 이구문은저장프로시저를재컴파일하여프로시저의실행계획을최적화할때사용한다. 저장프로시저생성후에저장프로시저가참조하는객체의정의가변경되었다면, 그상황에서의저장프로시저의실행계획은최적화상태가아닐수있다. ALTER PROCEDURE 문과동일하다. 생성된저장프로시저를삭제하는문장이다. 생성된저장함수를삭제하는문장이다. 생성된타입세트를삭제하는문장이다. 실행 EXECUTE 문 저장프로시저또는저장 함수를실행하는문장이다. 10 Stored Procedures Manual

23 function_name SQL 문내에서호출할때이름으로참조할수있다. 데이터타입 저장프로시저에서는다음과같은데이터타입을지원한다. Primitive 타입 - 일반데이터타입 : SQL 구문에서사용가능한데이터타입으로자세한사항은 SQL Reference 에있는데이터타입부분을참조한다. - BOOLEAN 타입 : 저장프로시저내에서만사용가능하며, TRUE, FALSE 의값을가진다. FILE_TYPE 저장프로시저내에서만사용가능하며, 파일제어를위한 타입이다. 자세한내용은 9 장 파일제어 를참조한다. 사용자정의타입저장프로시저내에서만사용가능하며, RECORD 및 ASSOCIATIVE ARRAY 를지원한다. 자세한내용은 6 장 사용자정의타입 을참조한다. 저장프로시저 SQL 문 11

24 CREATE PROCEDURE 구문 create_procedure::= CREATE OR PROCEDURE REPLACE user_name. procedure_name AS ( parameter_declaration ) IS, declaration_section statement END EXCEPTION exception_handler procedure_name parameter_declaration ::= parameter_name IN OUT data_type IN OUT DEFAULT := expression 기능 저장프로시저를새로생성하거나이미생성되어있는저장프로시저를새로운저장프로시저로변경하는기능을수행한다. 12 Stored Procedures Manual

25 parameter_declaration declaration_section 인자는생략할수있으며, 인자를명시할경우엔인자의명칭, 데이터타입및입출력구분을명시해야한다. 사용가능한입출력구분값은다음세가지중의하나이고생략시에 IN 이기본값이된다. IN: 프로시저호출시입력값으로주어지는입력인자 OUT: 프로시저실행후출력값을반환하는인자 INOUT: 입출력공용인자로프로시저호출시입력값을줄수있고, 실행후에출력값을반환할수있다 저장프로시저가실행될때, IN 인자를사용해서프로시저에값을전달하고, 프로시저는 OUT 인자를사용해서호출한루틴에값을반환한다. IN 인자는저장프로시저내에서상수처럼동작하므로, 프로시저내에서할당문을사용해인자에값을대입할수없으며 SELECT 문의 INTO 절에도사용할수없다. 인자는기본값을가질수있다. 저장프로시저가호출될때기본값이정의된인자에값을넘겨주지않을경우, 기본값이사용된다. 3 장의 지역변수선언 절참고 data_type 3 장의 지역변수선언 절참고 Exception Handler 9 장 Exception Handler 참고 CREATE PROCEDURE 문의실행 저장프로시저생성구문은텍스트편집기에서작성해서 isql 에붙여넣거나, isql 에서직접한라인씩입력할수도있다. 각 SQL 문, 저장프로시저제어문, 그리고블록 (END) 의끝에세미콜론 ( ; ) 을입력한다. isql 에서 CREATE PROCEDURE 문을실행할때는마지막 문의다음라인에반드시슬래시 ( / ) 를입력해야프로시저생성문이 저장프로시저 SQL 문 13

26 실행된다. CREATE PROCEDURE 문실행시컴파일오류가발생하지않고블록이성공적으로컴파일되면 Create Success 메시지가출력된다. 저장프로시저바디부분에대해서는다음장에서부터각각블록, 흐름제어문, 커서, Exception Handler 부분으로구분해서설명한다. 예제 예제 1 (IN 인자사용 ) CREATE TABLE t1 (i1 INTEGER UNIQUE, i2 INTEGER, i3 INTEGER); INSERT INTO t1 VALUES (1,1,1); INSERT INTO t1 VALUES (2,2,2); INSERT INTO t1 VALUES (3,3,3); INSERT INTO t1 VALUES (4,4,4); INSERT INTO t1 VALUES (5,5,5); SELECT * FROM t1; CREATE OR REPLACE PROCEDURE proc1 (p1 IN INTEGER, p2 IN INTEGER, p3 IN INTEGER) AS v1 INTEGER; v2 t1.i2%type; v3 INTEGER; SELECT * INTO v1, v2, v3 FROM t1 WHERE i1 = p1 AND i2 = p2 AND i3 = p3; IF v1 = 1 AND v2 = 1 AND v3 = 1 THEN UPDATE t1 SET i2 = 7 WHERE i1 = v1; ELSIF v1 = 2 AND v2 = 2 AND v3 = 2 THEN UPDATE t1 SET i2 = 7 WHERE i1 = v1; ELSIF v1 = 3 AND v2 = 3 AND v3 = 3 THEN UPDATE t1 SET i2 = 7 WHERE i1 = v1; ELSIF v1 = 4 AND v2 = 4 AND v3 = 4 THEN UPDATE t1 SET i2 = 7 WHERE i1 = v1; ELSE DELETE FROM t1; END IF; INSERT INTO t1 VALUES (p1+10, p2+10, p3+10); 14 Stored Procedures Manual

27 / isql> EXEC proc1 (2,2,2); Execute success. isql> SELECT * FROM t1; T1.I1 T1.I2 T1.I rows selected. 예제 2 ( 기본값이있는인자사용 ) CREATE TABLE t1 (i1 INTEGER, i2 INTEGER, i3 INTEGER); CREATE OR REPLACE PROCEDURE proc1 (p1 IN INTEGER DEFAULT 1, p2 IN INTEGER DEFAULT 1, p3 IN INTEGER DEFAULT 1) AS INSERT INTO t1 VALUES (p1, p2, p3); / EXEC proc1; SELECT * FROM t1; EXEC proc1(2); SELECT * FROM t1; EXEC proc1(3,3); SELECT * FROM t1; EXEC proc1(4,4,4); isql> SELECT * FROM t1; T1.I1 T1.I2 T1.I 저장프로시저 SQL 문 15

28 4 rows selected. 예제 3 CREATE OR REPLACE PROCEDURE proc1 (emp_id INTEGER, amount NUMBER(10,2)) AS UPDATE employee SET salary = salary + amount WHERE eno = emp_id; / isql> EXEC proc1(15, '250000'); Execute success. isql> SELECT * FROM employee WHERE eno=15; ENO ENAME EMP_JOB EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS JHSEOUNG WEBMASTER M 1212 H 1 row selected. 예제 4 ( 출력, 입출력인자사용 ) CREATE TABLE t4(i1 INTEGER, i2 INTEGER); INSERT INTO t4 VALUES(1,1); INSERT INTO t4 VALUES(1,1); INSERT INTO t4 VALUES(1,1); INSERT INTO t4 VALUES(1,1); INSERT INTO t4 VALUES(1,1); CREATE OR REPLACE PROCEDURE proc1(a1 OUT INTEGER, a2 IN OUT INTEGER) AS SELECT COUNT(*) INTO a1 FROM t4 WHERE i2 = a2; / isql> VAR t3 INTEGER; isql> VAR t4 INTEGER; isql> EXEC :t4 := 1; 16 Stored Procedures Manual

29 Execute success. isql> EXEC proc1(:t3, :t4); Execute success. isql> PRINT t3; NAME TYPE VALUE T3 INTEGER 5 예제 5 CREATE OR REPLACE PROCEDURE proc1(p1 INTEGER, p2 IN OUT INTEGER, p3 OUT INTEGER) AS p2 := p1; p3 := p ; / isql> VAR v1 INTEGER; isql> VAR v2 INTEGER; isql> VAR v3 INTEGER; isql> EXEC :v1 := 3; Execute success. isql> EXEC proc1(:v1, :v2, :v3); Execute success. isql> PRINT VAR; [ HOST VARIABLE ] NAME TYPE VALUE V1 INTEGER 3 V2 INTEGER 3 V3 INTEGER 103 예제 6 ( 입출력인자사용 ) CREATE TABLE t3(i1 INTEGER); INSERT INTO t3 VALUES(1); INSERT INTO t3 VALUES(1); INSERT INTO t3 VALUES(1); CREATE OR REPLACE PROCEDURE proc1(a1 IN OUT INTEGER) AS 저장프로시저 SQL 문 17

30 SELECT COUNT(*) INTO a1 FROM t3 WHERE i1 = a1; / isql> VAR p1 INTEGER; isql> EXEC :p1 := 1; Execute success. isql> EXEC proc1(:p1); Execute success. isql> PRINT p1; NAME TYPE VALUE P1 INTEGER 3 예제 7 CREATE OR REPLACE PROCEDURE proc1(p1 INTEGER, p2 IN OUT INTEGER, p3 OUT INTEGER) AS p2 := p1 + p2; p3 := p ; / isql> VAR v1 INTEGER; isql> VAR v3 INTEGER; isql> EXEC :v1 := 3; Execute success. isql> EXEC :v2 := 5; Execute success. isql> EXEC proc1(:v1, :v2, :v3); Execute success. isql> PRINT VAR; [ HOST VARIABLE ] NAME TYPE VALUE V1 INTEGER 3 V2 INTEGER 8 V3 INTEGER Stored Procedures Manual

31 주의사항 CREATE PROCEDURE 구문에서는인자로 LOB 타입을사용할수없다. 저장프로시저 SQL 문 19

32 ALTER PROCEDURE 구문 alter_procedure_statement ::= ALTER PROCEDURE procedure_name user_name. COMPILE ; 기능 저장프로시저생성이후에이프로시저내에서참조하는테이블, 시퀀스등의데이터베이스오브젝트혹은이저장프로시저가호출하는다른저장프로시저, 저장함수등의정의가변경되어서, 현재이저장프로시저의실행계획으로는이를실행할수없는경우에이저장프로시저는무효한 (invalid) 상태라고한다. 이는프로시저내의 SQL 문을위한실행계획은프로시저생성시점에만들어졌고, 더이상최적화된상태도아니고실행할수도없기때문이다. 예를들면처음저장프로시저생성시존재하던인덱스가삭제된경우이전실행계획은인덱스를통해테이블에접근하도록계획되어있으므로이전의실행계획을이용해서테이블에접근할수없게된다. 무효한상태의프로시저가호출되면, 알티베이스서버는바로자동으로이를재컴파일한다. 그러나런타임시에컴파일하는것은심각한성능이슈를불러올수있으므로, 프로시저가무효한상태가되었을때수동으로컴파일하는것이좋다. ALTER PROCEDURE 문은사용자가명시적으로저장프로시저를컴파일때사용된다. 예제 예제 1 20 Stored Procedures Manual

33 CREATE TABLE t1 (i1 NUMBER, i2 VARCHAR(10), i3 DATE); CREATE OR REPLACE PROCEDURE proc1 (p1 IN NUMBER, p2 IN VARCHAR(10), p3 IN DATE) AS IF p1 > 0 then INSERT INTO t1 VALUES (p1, p2, p3); END IF; / isql> EXECUTE proc1 (1, 'seoul', '20-JUN-2002'); Execute success. isql> EXECUTE proc1 (-3, 'daegu', '21-APR-2002'); Execute success. isql> SELECT * FROM t1; T1.I1 T1.I2 T1.I seoul 20-JUN row selected. 예제 2 CREATE TABLE t1 (i1 NUMBER, i2 VARCHAR(10), i3 DATE DEFAULT SYSDATE); ALTER PROCEDURE proc1 COMPILE; isql> EXECUTE proc1 (2, 'incheon', SYSDATE); Execute success. isql> SELECT * FROM t1; T1.I1 T1.I2 T1.I incheon 28-DEC row selected. 저장프로시저 SQL 문 21

34 DROP PROCEDURE 구문 drop_procedure_statement ::= DROP PROCEDURE procedure_name user_name. ; 기능 데이터베이스에서저장프로시저를삭제하는구문이다. 삭제하고자하는프로시저를다른저장프로시저에서참조하고있다하더라도, 그프로시저는삭제된다. 삭제되고없는저장프로시저나함수가호출될때알티베이스는오류코드를반환한다. 예제 DROP PROCEDURE proc1; 22 Stored Procedures Manual

35 EXECUTE 구문 execute_procedure_statement ::= EXEC(UTE) procedure_name ; user_name. execute_function_statement ::= ( ) expression, EXEC(UTE) variable := function_name ; user_name. ( ) expression, 기능 저장프로시저또는저장함수를실행한다. 예제 CREATE OR REPLACE PROCEDURE proc1(eid INTEGER, amount NUMBER(10,2)) AS current_salary NUMBER(10,2); SELECT salary INTO current_salary FROM employee WHERE eno = eid; UPDATE employee SET salary = salary + amount 저장프로시저 SQL 문 23

36 WHERE eno = eid; / isql> SELECT * FROM employee WHERE eno = 15; ENO ENAME EMP_JOB EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS JHSEOUNG WEBMASTER M 1212 H 1 row selected. isql> EXEC proc1(15, ); Execute success. isql> SELECT * FROM employee WHERE eno = 15; ENO ENAME EMP_JOB EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS JHSEOUNG WEBMASTER M 1212 H 1 row selected. 24 Stored Procedures Manual

37 CREATE FUNCTION 구문 create_function::= CREATE FUNCTION function_name OR REPLACE user_name. ( parameter_declaration ) RETURN data_type AS IS, statement declaration_section END EXCEPTION exception_handler function_name parameter_declaration ::= parameter_name IN OUT data_type IN OUT DEFAULT := expression 기능 저장함수를새로생성하거나이미생성되어있는저장함수를대체한다. 저장프로시저 SQL 문 25

38 parameter_declaration 저장프로시저와마찬가지로, 저장함수의인자도 IN, IN/OUT, OUT 인자로정의할수있다. 인자타입과주의사항에대한설명은 CREATE PROCEDURE 구문설명의 parameter_declaration 을참고하기바란다. RETURN data_type 저장함수는저장프로시저와달리실행후하나의값을반환한다. 그러므로반드시반환데이터타입을명시해야한다. Declaration Section 3 장의 지역변수선언 절참고 Data Types Exception Handler CREATE FUNCTION 문의실행 3 장의 지역변수선언 절참고 9 장 Exception Handler 참고 저장함수생성구문의실행은저장프로시저구문과동일하다. CREATE PROCEDURE 절의 CREATE PROCEDURE 문의실행 을참고하기바란다. 예제 CREATE TABLE t1( seq_no INTEGER, user_id VARCHAR(9), rate NUMBER, start_date DATE, end_date DATE); INSERT INTO t1 VALUES(0, ' ', , '23-May-2002', '23-Apr-2002'); INSERT INTO t1 VALUES(0, ' ', 190, '23-Nov-2002', '23-Dec-2002'); INSERT INTO t1 VALUES(0, ' ', 100, '12-Dec-2001', '12-Jan-2001'); INSERT INTO t1 VALUES(0, ' ', 100, '11-Dec-2001', '11-Jan-2002'); 26 Stored Procedures Manual

39 INSERT INTO t1(seq_no, user_id, start_date, end_date) VALUES(0, ' ', '30-Oct-2001', '30-Nov-2001'); INSERT INTO t1 VALUES(0, ' ', , '30-Apr-2002', '30-May-2002'); INSERT INTO t1 VALUES(0, ' ', , '30-Apr-2002', '30-May-2002'); INSERT INTO t1 VALUES(1, ' ', 100, '30-Apr-2002', '30-May-2002'); INSERT INTO t1 VALUES(1, ' ', 115.0, '19-Jan-2002', '19-Mar-2002'); INSERT INTO t1 VALUES(0, ' ', 120.0, '27-Jan-2002', '27-Feb-2002'); INSERT INTO t1 VALUES(1, ' ', 150.0, '26-Nov-2001', '26-Dec-2001'); isql> SELECT * FROM t1; T1.SEQ_NO T1.USER_ID T1.RATE T1.START_DATE T1.END_DATE /05/23 00:00: /04/23 00:00: /11/23 00:00: /12/23 00:00: /12/12 00:00: /01/12 00:00: /12/11 00:00: /01/11 00:00: /10/30 00:00: /11/30 00:00: /04/30 00:00: /05/30 00:00: /04/30 00:00: /05/30 00:00: /04/30 00:00: /05/30 00:00: /01/19 00:00: /03/19 00:00: /01/27 00:00: /02/27 00:00: /11/26 00:00: /12/26 00:00:00 11 rows selected. CREATE OR REPLACE FUNCTION get_rate (p1 IN CHAR(30), p2 IN CHAR(30), p3 IN VARCHAR(9)) RETURN NUMBER AS v_rate NUMBER; 저장프로시저 SQL 문 27

40 SELECT NVL(SUM(rate), 0) INTO v_rate FROM (SELECT rate FROM t1 WHERE start_date = TO_DATE(p1) AND end_date = TO_DATE(p2) AND user_id = '000000' p3 AND seq_no = 0); RETURN v_rate; / isql> VAR res NUMBER; isql> EXECUTE :res := get_rate('30-apr-2002', '30-May-2002', '524'); Execute success. isql> PRINT res; NAME TYPE VALUE RES NUMBER 401 주의사항 CREATE PROCDURE 구문처럼, CREATE FUNCTION 구문에서도인자로 LOB 타입을사용할수없다. 또한반환값으로 LOB 타입을사용할수도없다. 28 Stored Procedures Manual

41 ALTER FUNCTION 구문 alter_function_statement ::= ALTER FUNCTION funtion_name user_name. COMPILE ; 기능 저장프로시저와마찬가지로, 저장함수생성후에함수내에서참조하는데이터베이스객체의정의가변경되어현재이저장함수의실행계획으로는더이상실행할수없는경우에이저장함수는무효한상태라고한다. ALTER FUNCTION 문은저장함수를명시적으로재컴파일하여유효한상태의실행계획을다시생성하기위해사용된다. 더자세한설명은 ALTER PROCECURE 절을참고한다. 예제 ALTER FUNCTION get_dept_name COMPILE; 저장프로시저 SQL 문 29

42 DROP FUNCTION 구문 drop_function_statement ::= DROP FUNCTION function_name user_name. ; 기능 저장함수를삭제하는구문이다. 삭제하고자하는저장함수를다른저장프로시저또는저자함수에서참조하고있다하더라도, 그저장함수는삭제된다. 이미삭제된저장함수를참조하고있던임의의저장프로시저또는저장함수가실행될때알티베이스는오류를출력한다. 예제 DROP FUNCTION get_dept_name; 30 Stored Procedures Manual

43 3. 저장프로시저블록 저장프로시저와저장함수는한개이상의블록으로구성된다. 이장에서는블록을사용해서저장프로시저내에절차화된프로그램을작성하는방법을설명한다. 저장프로시저블록 31

44 저장프로시저블록 구문 block ::= << label_name >> DECLARE declaration_section statement EXCEPTION exception_handler END label_name ; statement ::= << label_name >> print_statement sql_statement control_flow_statement open_cursor_statement open_for_statement fetch_statement close_cursor_statement assignment_statement raise_statement return_statement block 32 Stored Procedures Manual

45 sql_statement ::= select_into_statement insert_statement delete_statement update_statement commit_statement rollback_statement enqueue_statement execute_imme_statement 블록은크게선언부 (Declare Section), 블록바디 (Block Body), 예외처리부 (Exception Handler Section) 의세부분으로나뉘어진다. DECLARE,, EXCEPTION 등의키워드뒤에는세미콜론을사용하지않지만 END 및기타모든프로시저내의명령문뒤에는세미콜론이있어야한다. 저장프로시저의코드에주석처리를할수있다. 단일행주석에는 -- 를문장앞에붙이고, 여러행을주석처리할경우는 /* 와 */ 사이에주석처리할문장이놓이도록작성한다. 이장에서는선언부와블록바디에사용할수있는구문중 SELECT INTO 문, 변수할당문, LABEL 문, PRINT 문, RETURN 문에대해서설명한다. 저장프로시저내에서사용가능한흐름제어문, 커서관련문, 예외처리에관련된내용은다음장에서순서대로설명하고있다. 그외 SQL 문들에대한자세한내용은 SQL Reference 을참조한다. 선언부 선언부는메인블록에서는 AS 와 키워드사이에위치하며, 하위블록에서는 DECLARE 와 키워드사이에위치한다. 해당블록내에서사용하는지역변수, 커서와사용자정의예외등을선언한다. 저장프로시저블록 33

46 이장에서는지역변수만을설명한다. 커서와예외처리는 5 장커서와 9 장예외처리에서각각설명한다. 블록바디 과 END 사이의부분으로 SQL 문과흐름제어문을포함한다. 블록바디내에기술가능한 SQL 문과흐름제어문은다음과같다. DML 문 : SELECT/INSERT/DELETE/UPDATE 트랜잭션처리문 : COMMIT/ROLLBACK 흐름제어문 : IF, CASE, FOR, LOOP, WHILE, EXIT, CONTINUE, NULL 할당문 출력문 : PRINT, RETURN 커서관련문 : OPEN, FETCH, CLOSE, Cursor FOR LOOP 동적 SQL 문 : EXECUTE IMMEDIATE 예외처리구문 : RAISE, RAISE_APPLICATION_ERROR 저장프로시저의장점중하나는 SQL 문과달리블록을사용하여명령문을중첩할수있다는점이다. 명령문을사용할수있는위치에는블록을중첩할수있으므로, 명령문을블록으로만들어서중첩시키면된다. 예외처리부 EXCEPTION 과 END 사이의부분으로저장프로시저또는함수실행중에오류가발생했을때처리할루틴을기술한다. 34 Stored Procedures Manual

47 지역변수선언 구문 declaration_section ::= variable_declaration constant_declaration cursor_declaration exception_declaration variable_declaration ::= variable_name data_type DEFAULT := expression constant_declaration ::= DEFAULT constant_name CONSTANT data_type expression := data_type ::= sql_data_type type_attribute rowtype_attribute 저장프로시저블록 35

48 type_attreibute ::= table_name. column_name record_name % TYPE variable_name rowtype_attribute ::= cursor_name table_name % ROWTYPE 기능 variable_name 변수의이름을명시할때사용된다. 변수의이름은하나의블록범위내에서유일해야한다. 칼럼과같은이름을가지는변수를 SQL 문장내에서사용할경우, 이것은칼럼명으로인식된다. 다음의예에서 eno 는칼럼명으로인식되어 employee 테이블의모든레코드가삭제된다. DECLARE eno INTEGER := 100; DELETE FROM employee WHERE eno = eno; 다음과같은방법으로모호성을없앨수있다. 36 Stored Procedures Manual

49 <<del_block>> DECLARE eno INTEGER := 100; DELETE FROM employee WHERE eno = del_block.eno; 블록이름에관한설명은이장의 LABEL 절을참고한다. data_type 변수의데이터타입을명시한다. 저장프로시저내에서사용가능한데이터타입은아래와같다 : SQL 문에서사용할수데이터타입 BOOLEAN 타입 %TYPE 속성을사용해서이미데이터타입이지정된칼럼이나변수와같은타입사용 %ROWTYPE 속성을사용해서여러개의칼럼이모인레코드타입을정의 사용자정의타입 %TYPE 과 %ROWTYPE 속성은테이블정의가변경될때마다저장프로시저내에서코드를변경해야하는번거로움을예방한다. 즉, 칼럼의데이터타입이변경될때, %TYPE 속성을사용해서정의된변수는자동으로변경된타입에맞추어진다. 이는데이터의독립성을실현하고유지보수비용을낮추는데기여한다. CONSTANT 이옵션은특정변수를값을할당할수없는상수로사용하고자하는경우에사용할수있다. 이렇게정의된변수는읽기전용변수로만사용할수있다. 다음과같이 max_val 을선언하면 max_val 에는임의의값을할당할수없고선언시할당한 100 의값을가지는상수처럼취급된다. max_val CONSTANT integer := 100; DEFAULT 다음과같이변수선언시초기값을설정할때사용된다. curr_val INTEGER DEFAULT 100; count_val INTEGER := 0; Cursor Declaration 저장프로시저블록 37

50 5 장의 CURSOR 절참고 Exception Declaration 9 장의 EXCEPTION 절참고 중첩블록및변수의범위 선언부에명시한변수들의영향력은자신이선언된 BLOCK 문의 에서시작되고 END 에서종료된다. 만약 block2 가 block1 내에정의되어있고각각의블록내에같은이름을가지는 v_result 변수를선언하였다면, block2 의밖에서사용되는 v_result 는 block1 에정의된변수를참조하고 block2 내에서사용된 v_result 는 block2 에서선언한변수이다. 아래에 block2 ( 중첩블록 ) 에있는변수 y 는 block1 ( 외부블록 ) 에있는변수 x 를참조할수있지만, 변수 x 는변수 y 를참조할수없다. 중첩블록내에외부블록에있는변수 x 와동일한이름이주어지면그값은중첩블록내에서만유효하다. /* start of block1 */ DECLARE v_result¹ integer; x integer; Scope of x... v_result¹ := 1;... /* start of sub-block (block2) */ DECLARE v_result² integer; y number; Area can refer to v_result¹ Scope of y... v_result² := 2;... /* end of block2 */... v_result¹ := 3;... /* end of block1 */ Area can refer to v_result² Area can refer to v_result¹ 38 Stored Procedures Manual

51 제약조건 다음과같은기능은변수선언부에서지원하지않는기능들이다. 변수에 NOT NULL 속성을지정할수없다. 여러개의변수들을한꺼번에선언할수없다. 즉다음과같이선언할수없다. i, j, k INTEGER; 예제 %TYPE 사용 DECLARE my_title books.title%type; %ROWTYPE 사용 my_title 은 books 테이블의 title 칼럼과같은데이터타입을가지는변수로정의된다. DECLARE dept_rec department%rowtype dept_rec 은레코드타입의변수로서 department 테이블또는 department 라는이름의커서와동일한레코드타입을가지게된다. 예제 1 CONSTANT 와 %ROWTYPE 을사용해서변수를선언하는예제이다. CREATE TABLE t1(i1 INTEGER, i2 INTEGER); CREATE OR REPLACE PROCEDURE proc1 AS v1 constant INTEGER := 1; v2 constant t1.i1%type := 1; INSERT INTO t1 VALUES (v1, v2); / EXEC proc1; isql> SELECT * FROM t1; T1.I1 T1.I2 저장프로시저블록 39

52 row selected. --DROP TABLE t1; CREATE TABLE t1 (i1 INTEGER, i2 INTEGER, i3 INTEGER); INSERT INTO t1 VALUES(1,1,1); CREATE OR REPLACE PROCEDURE proc1 AS r1 t1%rowtype; INSERT INTO t1 VALUES(3,3,3); <<s>> DECLARE r1 t1%rowtype; SELECT i1, i2, i3 INTO s.r1.i1, s.r1.i2, s.r1.i3 FROM t1 WHERE i1 = 1; INSERT INTO t1 VALUES (s.r1.i1, s.r1.i2, s.r1.i3); / isql> EXEC proc1; Execute success. isql> SELECT * FROM t1; T1.I1 T1.I2 T1.I rows selected. 예제 2 %ROWTYPE 속성을사용하는예제이다. CREATE TABLE emp( eno INTEGER, ename CHAR(10), emp_job CHAR(15), join_date DATE, salary NUMBER(10,2), 40 Stored Procedures Manual

53 dno BYTE(2)); CREATE TABLE emp401( eno INTEGER, ename CHAR(10), emp_job CHAR(15), join_date DATE, leave_date DATE, salary NUMBER(10,2), dno BYTE(2), fund NUMBER(10,2) DEFAULT 0); INSERT INTO emp VALUES (10, 'DKLEE', 'ENGINEER', '01-Jul-2000', , BYTE'D001'); INSERT INTO emp VALUES (20, 'SWMYUNG', 'MANAGER', '01-Nov-1999', , BYTE'C002'); CREATE OR REPLACE PROCEDURE proc1(p1 INTEGER) AS DECLARE emp_rec emp%rowtype; SELECT * INTO emp_rec FROM emp WHERE eno = p1; INSERT INTO emp401(eno, ename, emp_job, join_date, leave_date, salary, dno) VALUES(emp_rec.eno, emp_rec.ename, emp_rec.emp_job, emp_rec.join_date, sysdate, emp_rec.salary, emp_rec.dno); / isql> EXEC proc1(10); Execute success. isql> SELECT * FROM emp401; EMP401.ENO EMP401.ENAME EMP401.EMP_JOB EMP401.JOIN_DATE EMP401.LEAVE_DATE EMP401.SALARY EMP401.DNO EMP401.FUND DKLEE ENGINEER 2000/07/01 00:00: /01/27 16:26: D001 0 저장프로시저블록 41

54 42 Stored Procedures Manual 1 row selected.

55 SELECT INTO 구문 select_into_statement ::= select_list INTO record_name variable_name ; FROM rest_of_select_statement ; select_list 와 rest_of_select_statement 는 SELECT 구문의문법과동일하므로 SQL Reference 을참고한다. 기능 저장프로시저에서 SELECT 문을사용할경우, SELECT 문은 INTO 절을포함해야한다. 저장프로시저또는함수내에서 INTO 절을가지는 SELECT 문은하나의레코드만검색할수있다. INTO 절이사용된 SELECT 구문이여러행을반환하거나한행도반환하지않으면오류가발생한다. SELECT 절의 select_list 와 INTO 절의상응하는 variable_name 은개수가동일해야하며호환가능한데이터타입이어야한다. %ROWTYPE 속성으로정의한변수를 INTO 절에사용하는경우에도 %ROWTYPE 변수내의칼럼의개수와 select_list 의칼럼의개수가동일해야하며, 상응하는칼럼의데이터타입은호환가능해야한다. 저장프로시저는표준예외사항이발생하는경우오류를발생시킨다. NO_DATA_FOUND 및 TOO_MANY_ROWS 등의예외 저장프로시저블록 43

56 사항을사용하여블록의예외처리부에서오류를처리할수있다. 예외처리에대한상세한설명은 9 장예외처리를참조한다. 예제 예제 1 CREATE TABLE t1(i1 INTEGER, i2 INTEGER, i3 INTEGER); INSERT INTO t1 VALUES(1,1,1); CREATE OR REPLACE PROCEDURE proc1 AS v1 INTEGER; r1 t1%rowtype; INSERT INTO t1 VALUES (3,3,3); <<s>> DECLARE v1 proc1.r1.i1%type; r1 t1%rowtype; SELECT i1,i2,i3 INTO s.r1.i1, s.r1.i2, s.r1.i3 FROM t1 WHERE i1 = 1; INSERT INTO t1 VALUES(s.r1.i1, s.r1.i2, s.r1.i3); / isql> EXEC proc1; Execute success. isql> SELECT * FROM t1; T1.I1 T1.I2 T1.I rows selected. 예제 2 44 Stored Procedures Manual

57 CREATE TABLE t1 (i1 INTEGER, i2 INTEGER, i3 INTEGER); INSERT INTO t1 VALUES(100, 100, 100); CREATE SEQUENCE seq1; CREATE SEQUENCE seq2; CREATE SEQUENCE seq3; CREATE OR REPLACE PROCEDURE proc1 AS <<seq1>> DECLARE nextval INTEGER; nextval := 10; INSERT INTO t1 VALUES (seq1.nextval,0,0); / CREATE OR REPLACE PROCEDURE proc2 AS INSERT INTO t1 VALUES (seq1.nextval, seq2.nextval, seq3.nextval); INSERT INTO t1 VALUES (seq1.nextval, seq2.nextval, seq3.nextval); INSERT INTO t1 VALUES (seq1.nextval, seq2.nextval, seq3.nextval); / CREATE OR REPLACE PROCEDURE proc3 AS v1 INTEGER; v2 INTEGER; v3 INTEGER; SELECT seq1.currval, seq2.nextval, seq3.nextval INTO v1, v2, v3 FROM t1 WHERE i1 = 100; INSERT INTO t1 VALUES (v1, v2, v3); SELECT seq1.currval, seq1.nextval, seq1.currval INTO v1, v2, v3 FROM t1 WHERE i1 = 100; INSERT INTO t1 VALUES (v1, v2, v3); 저장프로시저블록 45

58 SELECT seq1.currval, seq2.nextval, seq3.nextval INTO v1, v2, v3 FROM t1 WHERE i1 = 100; INSERT INTO t1 VALUES (v1, v2, v3); / EXEC proc1; SELECT * FROM t1; EXEC proc2; SELECT * FROM t1; EXEC proc3; SELECT * FROM t1; EXEC proc2; SELECT * FROM t1; EXEC proc3; isql> SELECT * FROM t1; T1.I1 T1.I2 T1.I rows selected. 예제 3 CREATE TABLE t1(i1 INTEGER, i2 INTEGER, i3 INTEGER); CREATE TABLE t2(i1 INTEGER, i2 INTEGER, i3 INTEGER); INSERT INTO t1 VALUES (1,1,1); INSERT INTO t1 VALUES (2,2,2); 46 Stored Procedures Manual

59 CREATE OR REPLACE PROCEDURE proc1 AS v1 INTEGER; r1 t1%rowtype; SELECT i1 INTO v1 FROM t1 WHERE i1 = 1; SELECT * INTO r1 FROM t1 WHERE i1 = 1; INSERT INTO t2 VALUES (v1, r1.i2, r1.i3); <<s>> DECLARE r1 t1%rowtype; SELECT i1, i2, i3 INTO s.r1.i1, s.r1.i2, s.r1.i3 FROM t1 WHERE i1 = 2; INSERT INTO t2 VALUES (s.r1.i1, s.r1.i2, s.r1.i3); / isql> EXEC proc1; Execute success. isql> SELECT * FROM t2; T2.I1 T2.I2 T2.I rows selected. 예제 4 CREATE TABLE t3(i1 INTEGER); CREATE OR REPLACE PROCEDURE proc1 AS max_qty orders.qty%type; SELECT MAX(qty) INTO max_qty FROM orders; INSERT INTO t3 VALUES(max_qty); / 저장프로시저블록 47

60 isql> exec proc1; Execute success isql> SELECT * FROM t3; T3.I row selected. 예제 5 CREATE TABLE delayed_processing( cno CHAR(14), order_date DATE); CREATE OR REPLACE PROCEDURE proc1 AS de_cno CHAR(14); de_order_date DATE; INSERT INTO delayed_processing SELECT cno, order_date INTO de_cno, de_order_date FROM orders WHERE processing = 'D'; / isql> EXEC proc1; Execute success. isql> SELECT * FROM delayed_processing; DELAYED_PROCESSING.CNO DELAYED_PROCESSING.ORDER_DATE /11/29 00:00: /11/29 00:00:00 2 rows selected. 48 Stored Procedures Manual

61 할당문 구문 assignment_statement ::= variable_name parameter_name := record_name variable_name. column_name expression ; SET parameter_name = record_name. column_name 기능 지역변수, OUT 또는 IN/OUT 형의인자에값을할당하고자할때사용하는할당문이다. 다음의두가지방법을사용해서변수또는인자에값을할당할수있다. := 연산자사용 variable_name := value; parameter_name := value; SET 키워드사용 SET variable_name = value; SET parameter_name := value; %ROWTYPE 속성을사용해서정의된 RECORD 타입변수의각칼럼값은 record_variable_name.field_name 으로참조할수있다. 저장프로시저블록 49

62 예제 예제 1 CREATE TABLE t1(i1 INTEGER, i2 INTEGER, i3 INTEGER); CREATE OR REPLACE PROCEDURE proc1 AS i INTEGER; i := 5; WHILE i <= 10 LOOP INSERT INTO t1 VALUES (i, i+1, i+2); i := i + 1; END LOOP; / isql> EXEC proc1; Execute success. isql> SELECT * FROM t1; T1.I1 T1.I2 T1.I rows selected. 예제 2 CREATE TABLE t1(i1 INTEGER, i2 INTEGER, i3 INTEGER); CREATE OR REPLACE FUNCTION plus20(p1 IN INTEGER) RETURN INTEGER AS v1 INTEGER; v1 := p1 + 20; RETURN v1; 50 Stored Procedures Manual

63 / CREATE OR REPLACE PROCEDURE proc1 AS v1 INTEGER; in_arg INTEGER; in_arg := 80; v1 := plus20(in_arg); INSERT INTO t1 VALUES (v1, v1, v1); / isql> EXEC proc1; Execute success. isql> SELECT * FROM t1; T1.I1 T1.I2 T1.I row selected. 저장프로시저블록 51

64 LABEL LABLE 문은저장프로시저내부의특정위치에명칭을지정하는데사용된다. LABEL 은블록내에다음과같이지정할수있다. << User_defined_label_name >> 기능 사용자가정의한 LABEL 명은다음 3 가지경우에사용된다. 같은이름의여러변수들의범위를제한하거나, 변수이름과칼럼이름이같아서발생하는모호성을없애기위한경우 중첩된 LOOP 에서빠져나오고싶은경우 GOTO 문장을사용하는경우 제약조건 1. 동일블록내에같은이름의 LABEL 이존재하면안된다. 아래예제의경우 LABEL1 이동일블록내에두번지정되어컴파일시에러가출력된다. CREATE OR REPLACE PROCEDURE PROC1 AS V1 INTEGER; << LABEL1>> V1 := 1; <<LABEL1>> V1 := V1 + 1; 2. 같은이름을가지는변수의범위를제한하기위하여사용하는경우, 반드시 DECLARE 문위에 LABEL 을선언해야한다. 단, LABEL 을여러개선언하는것은허용된다. 아래예제에서는 (1) 에서선언한변수 V1 이 2 번참조된다. CREATE OR REPLACE PROCEDURE PROC1 AS V1 INTEGER; <<LABEL1>> --- LABLE 지정 52 Stored Procedures Manual

65 / <<LABEL2>> DECLARE V1 INTEGER;...(1) <<LABEL3>> DECLARE V1 INTEGER;...(2) LABEL1.V1 := 1; LABEL2.V1 := 2; LABEL3.V1 := 3; -- (1) 의 V1 참조 -- (1) 의 V1 참조 -- (2) 의 V1 참조 아래예제의경우 DECLARE 문위에 LABEL 을지정하지않아서에러가나게된다. CREATE OR REPLACE PROCEDURE PROC1 AS V1 INTEGER; <<LABEL1>> V1 := 1; DECLARE V1 INTEGER; LABEL1.V1 := 1; --- ERROR 의경우와유사하게중첩된 LOOP 에서빠져나올때사용하는경우반드시 LOOP 시작직전에 LABEL 을선언해야한다. LOOP 전에 LABEL 을여러개선언하는것은허용된다. CREATE OR REPLACE PROCEDURE PROC1 AS V1 INTEGER; V1 := 0; <<LABEL1>> <<LABEL2>> FOR I IN LOOP V1 := V1 + 1; FOR I IN LOOP V1 := V1 + 1; EXIT LABEL1 WHEN V1 = 30; 저장프로시저블록 53

66 / END LOOP; END LOOP; 아래예제는한 LABEL 이 LOOP 시작직전에선언되지않은경우이다. 이 LABEL 을이용해서는중첩된 LOOP 를빠져나올수없으므로, 저장프로시저컴파일시에러가발생하게된다. CREATE OR REPLACE PROCEDURE PROC1 AS / V1 INTEGER; <<LABEL1>> V1 := 0; <<LABEL2>> FOR I IN LOOP V1 := V1 + 1; FOR I IN LOOP V1 := V1 + 1; EXIT LABEL1 WHEN V1 = 30; -- ERROR END LOOP; END LOOP; 54 Stored Procedures Manual

67 PRINT 구문 print_statement ::= PRINT PRINTLN ( string ) ; 기능 PRINT 구문은저장프로시저실행시에사용자가원하는텍스트를해당프로시저를호출한클라이언트에게출력한다. PRINT 구문은주로디버깅및테스트목적으로사용하도록알티베이스가제공하는시스템프로시저이다. PRINTLN 은 PRINT 와동일하나출력메시지의마지막에개행문자 ( 윈도우는 \r\n, 유닉스는 \n ) 를붙여서출력한다. PRINT, PRINTLN 의소유자는 SYSTEM_ 이므로사용시아래처럼이를명시해도된다 : SYSTEM_.PRINTLN( Hello World ); 그러나이들에대한 PUBLIC 시노님이기본적으로생성되어있기때문에 SYSTEM_ 을굳이명시하지않아도된다. String 클라이언트로출력할문자열을기술한다. 사용자메시지출력시문자열과함께변수값등을출력하고싶은경우에는예제 2 와같이문자열연결연산자인 를사용해서하나의문자열로만들어서출력하면된다. 저장프로시저블록 55

68 예제 예제 1 CREATE OR REPLACE PROCEDURE proc1 AS v1 BIGINT; v1 := BIGINT' '; system_.println ('1'); system_.println (v1); system_.println ('2'); / isql> EXEC proc1; Execute success. 예제 2 CREATE OR REPLACE PROCEDURE proc1 AS eno_count INTEGER; SELECT COUNT(eno) INTO eno_count FROM employee; println('the NUMBER of Employees: ' eno_count); / isql> EXEC proc1; The NUMBER of Employees: 20 Execute success. 예제 3 다음예제는쿼리결과를형식에맞춰출력하기위해 PRINT 와 PRINTLN 구문과함께 loop 를사용하는방법을보여준다. CREATE OR REPLACE PROCEDURE showprocedures AS CURSOR c1 IS 56 Stored Procedures Manual

69 SELECT SYSTEM_.sys_procedures_.proc_name, decode(system_.sys_procedures_.object_type, 0, 'Procedure',1,'Function') FROM system_.sys_procedures_ ; v1 CHAR(40); v2 CHAR(20); OPEN c1; SYSTEM_.PRINTLN(' '); SYSTEM_.PRINT('Proc_Name'); SYSTEM_.PRINTLN(' Procedure/Function'); SYSTEM_.PRINTLN(' '); LOOP FETCH C1 INTO v1, v2; EXIT WHEN C1%NOTFOUND; PRINT(' '); PRINT(v1); PRINTLN(v2); END LOOP; PRINTLN(' '); CLOSE c1; / isql> EXEC showprocedures; Proc_Name Procedure/Function PRINT PRINTLN.. Procedure Procedure SHOWPROCEDURES Execute success. Procedure 저장프로시저블록 57

70 RETURN 구문 return_statement ::= RETURN expression ( ) ; 기능 저장프로시저의수행을도중에중단하거나, 저장함수에서값을반환하고수행을중단하려하는경우에사용하는제어문이다. 저장프로시저는 RETURN 문에반환할값을지정하게되면에러가발생한다. 반면저장함수는항상값을반환해야하기때문에 RETURN 문에반환할값을명시하여야한다. expression 저장함수의경우에반환할값을기술한다. 반환값은연산식의형태로도기술할수있다. 예제 예제 1 CREATE TABLE t1(i1 INTEGER, i2 INTEGER, i3 INTEGER); INSERT INTO t1 VALUES(1,1,1); CREATE OR REPLACE FUNCTION times_half(p1 IN INTEGER) RETURN INTEGER AS 58 Stored Procedures Manual

71 RETURN p1 / 2; / isql> SELECT times_half(times_half(8)) FROM t1; TIMES_HALF(TIMES_HALF(8)) row selected. 예제 2 CREATE TABLE t1(i1 INTEGER, i2 INTEGER, i3 INTEGER); INSERT INTO t1 VALUES(1,1,1); INSERT INTO t1 VALUES(10,10,10); INSERT INTO t1 VALUES(100,100,100); CREATE OR REPLACE FUNCTION max_all_val RETURN INTEGER AS v1 INTEGER; SELECT MAX(i1) INTO v1 FROM t1; RETURN v1; / isql> SELECT max_all_val FROM t1; MAX_ALL_VAL rows selected. 예제 3 CREATE TABLE t4(i1 INTEGER, i2 INTEGER); INSERT INTO t4 VALUES(3, 0); INSERT INTO t4 VALUES(2, 0); INSERT INTO t4 VALUES(1, 0); INSERT INTO t4 VALUES(0, 0); 저장프로시저블록 59

72 CREATE OR REPLACE FUNCTION func_plus_10(p1 INTEGER) RETURN INTEGER AS RETURN p1+10; / isql> SELECT func_plus_10(i1) FROM t4; FUNC_PLUS_10(I1) rows selected. 60 Stored Procedures Manual

73 4. 흐름제어 흐름제어 61

74 개요 이장은저장프로시저바디내에서의흐름제어방법을설명한다. 구문 control_flow_statement ::= if_statement case_statement simple_loop_statement while_loop_statement for_loop_statement exit_statement continue_statement null_statement 저장프로시저에서사용할수있는흐름제어문은다음과같다. 조건분기문인 IF문과 CASE문 조건을만족할때반복수행하는 LOOP문, WHILE문과 FOR문 반복수행문의흐름을제어하는 EXIT문와 CONTINUE문 아무것도수행하지않음을명시적으로나타낼수있는 NULL문 특정위치로이동할수있는 GOTO문 제약사항 부질의 (subquery) 가포함된조건은 IF 문또는 CASE 문의조건으로사용될수없다. 62 Stored Procedures Manual

75 IF 구문 if_statement ::= IF condition THEN statement ELS(E)IF condition THEN statement ELSE statement END IF ; 기능 조건을만족하는경우와그렇지않은경우에따라처리흐름을분기하는조건분기문이다. IF 절은조건은검사해서 true 이면 THEN 절로제어를이동하고, false 이거나 NULL 이면 ELSE 절로제어를이동한다. condition 조건절에는 SQL 문의 WHERE 절에서사용가능한모든술어 (predicate) 들을사용할수있다. 지원하는술어들에대한상세내용은 SQL Reference 의 SELECT 구문을참조한다. ELS(E)IF ELS(E)IF 절의경우이전의 IF 문의조건과는다른조건을명시할수있다. 흐름제어 63

76 ELSIF 는한단어이며하나의 IF 문내에여러개의 ELS(E)IF 절을사용할수있다. ELSE 앞서 IF 와 ELS(E)IF 에서열거된모든조건을만족하지않는경우 ELSE 절의문장이수행된다. ELSE 절은명시하거나, 하나의 IF 문내에한번만기술가능하다. 중첩 IF 문 IF 문은다른 IF 문내에중첩되어기술할수있다. 즉, IF 문은다른 IF, ELS(E)IF 또는 ELSE 문의결과로수행되는일련의작업내에포함될수있다. 각 IF 문은 END IF 로종료해야한다. 예제 예제 1 CREATE OR REPLACE PROCEDURE proc1 AS CURSOR c1 IS SELECT eno, emp_job, salary FROM employee; emp_id employee.eno%type; e_job employee.emp_job%type; e_salary employee.salary%type; OPEN c1; LOOP FETCH c1 INTO emp_id, e_job, e_salary; EXIT WHEN c1%notfound; IF e_salary IS NULL THEN IF e_job = 'CEO' THEN e_salary := ; ELSIF e_job = 'MANAGER' THEN e_salary := ; ELSIF e_job = 'ENGINEER' THEN e_salary := ; ELSIF e_job = 'PROGRAMMER' THEN e_salary := ; ELSE e_salary := ; END IF; 64 Stored Procedures Manual

77 UPDATE employee SET salary = e_salary WHERE eno = emp_id; END IF; END LOOP; CLOSE c1; / isql> SELECT eno, emp_job FROM employee WHERE salary IS NULL; ENO EMP_JOB CEO 8 MANAGER 20 SALESMAN 3 rows selected. isql> EXEC proc1; Execute success. isql> SELECT eno, emp_job, salary FROM employee WHERE eno=1 OR eno=8 OR eno=20; ENO EMP_JOB SALARY CEO MANAGER SALESMAN rows selected. 예제 2 CREATE TABLE t1 (i1 VARCHAR(20), i2 NUMBER, i3 DATE); CREATE TABLE t2 (i1 VARCHAR(20), i2 NUMBER, i3 DATE); INSERT INTO t1 VALUES ('21-JUL-2001', 2, '01-JUL-2000'); INSERT INTO t2 VALUES (NULL,NULL,'01-FEB-1990'); INSERT INTO t2 VALUES (NULL,NULL,'02-FEB-1990'); CREATE OR REPLACE FUNCTION func2 (p1 IN DATE, p2 IN CHAR(30)) RETURN NUMBER AS RETURN (TO_NUMBER(TO_CHAR(p1, 'dd')) + TO_NUMBER(p2)); / 흐름제어 65

78 CREATE OR REPLACE FUNCTION func1 (p1 IN DATE, p2 IN DATE) RETURN DATE AS IF p1 >= p2 THEN RETURN add_months(p1, 3); ELSE RETURN add_months(p1, 4); END IF; / CREATE OR REPLACE PROCEDURE proc1 AS v1 VARCHAR(20); v2 NUMBER; v3 DATE; SELECT i1, func2(to_date(i1), TO_CHAR(i3, 'yyyy')), i3 INTO v1,v2,v3 FROM t1 WHERE i2 = 2; INSERT INTO t2 VALUES (v1,v2,v3); IF v2 not in (2001, 2002, 2003) AND v1 = '21-JUL-2001' THEN UPDATE t2 SET i1 = func1(v1, '17-JUL-2001'), i2 = nvl(i2, 10) WHERE i3 = '01-FEB-1990'; UPDATE t2 SET i1 = func1(v1, '27-JUL-2001'), i2 = nvl(i2, 10*2) WHERE i3 = '02-FEB-1990'; END IF; / isql> EXEC proc1; Execute success. isql> SELECT * FROM t2; T2.I1 T2.I2 T2.I JUL /07/01 00:00:00 66 Stored Procedures Manual

79 21-OCT /02/01 00:00:00 21-NOV /02/02 00:00:00 3 rows selected. 예제 3 CREATE TABLE payroll( eno INTEGER, bonus NUMBER(10, 2)); CREATE OR REPLACE PROCEDURE proc1 AS DECLARe CURSOR c1 IS SELECT DISTINCT(eno), SUM(qty) FROM orders GROUP BY eno; emp_id orders.eno%type; sum_qty orders.qty%type; bonus NUMBER(10, 2); OPEN c1; IF c1%isopen THEN LOOP FETCH c1 INTO emp_id, sum_qty; EXIT WHEN c1%notfound; IF sum_qty > THEN bonus := 1000; ELSIF sum_qty > THEN bonus := 500; ELSE bonus := 200; END IF; INSERT INTO payroll VALUES(emp_id, bonus); END LOOP; END IF; / isql> EXEC proc1; Execute success. isql> SELECT DISTINCT(eno), SUM(qty) sum FROM orders GROUP BY eno; ENO SUM 흐름제어 67

80 rows selected. isql> SELECT * FROM payroll; PAYROLL.ENO PAYROLL.BONUS rows selected. 68 Stored Procedures Manual

81 CASE 구문 case_statement_1 ::= CASE WHEN condition THEN statement ELSE statement END CASE ; case_statement_2::= CASE case_variable WHEN when_value THEN statement ELSE statement END CASE ; 기능 특정변수의값에따라서처리경로를결정하는조건분기문이다. IF 문과동일한기능이지만 CASE 문을사용하면프로그램의가독성을높일수있다. CASE 문은위의다이어그램에서보여주듯이다음두가지가있다. case_statement_1: 조건식이참일때에특정한문장을수행하는방식 흐름제어 69

82 case_statemen_2: 하나의변수가특정한값이되었을때에특정한문장을수행하는방식 단, 하나의 CASE 문에서이두가지방식을혼용할수없다. CASE 절을모두만족하지못하면 ELSE 절의문장을수행하게되며, ELSE 절이없는경우에는어떠한문장도수행하지않는다. condition 검사할조건을기술한다. SELECT 구문의 WHERE 절과같이술어문으로명시한다. case_variable 저장프로지저의처리를분기시키는기준이되는변수명을기술한다. when_value case_variable 변수와비교할실제상수값을기술한다. ELSE CASE 조건식이모두거짓이되었을때수행해야할처리구문을 ELSE 절에기술한다. ELSE 절은없어도상관없으며하나의 CASE 문에한번사용가능하다. 조건식이모두거짓이되었음에도 ELSE 절이없을경우 CASE 문은어떠한문장도수행하지않고지나간다. 예제 예제 1 CREATE OR REPLACE PROCEDURE proc1 AS CURSOR c1 IS SELECT eno, emp_job, salary FROM employee; emp_id employee.eno%type; e_job employee.emp_job%type; e_salary employee.salary%type; OPEN c1; LOOP 70 Stored Procedures Manual

83 FETCH c1 INTO emp_id, e_job, e_salary; EXIT WHEN c1%notfound; IF e_salary IS NULL THEN CASE WHEN e_job = 'CEO' THEN e_salary := ; WHEN e_job = 'MANAGER' THEN e_salary := ; WHEN e_job = 'ENGINEER' THEN e_salary := ; WHEN e_job = 'PROGRAMMER' THEN e_salary := ; ELSE e_salary := ; END CASE; UPDATE employee SET salary = e_salary WHERE eno = emp_id; END IF; END LOOP; CLOSE c1; / isql> EXEC proc1; Execute success. isql> SELECT eno, emp_job, salary FROM employee WHERE eno=1 OR eno=8 OR eno=20; ENO EMP_JOB SALARY CEO MANAGER SALESMAN rows selected. 예제 CREATE OR REPLACE PROCEDURE proc1 AS CURSOR c1 IS SELECT eno, emp_job, salary FROM employee; emp_id employee.eno%type; e_job employee.emp_job%type; e_salary employee.salary%type; OPEN c1; 흐름제어 71

84 LOOP FETCH c1 INTO emp_id, e_job, e_salary; EXIT WHEN c1%notfound; IF e_salary IS NULL THEN CASE e_job WHEN 'CEO' THEN e_salary := ; WHEN 'MANAGER' THEN e_salary := ; WHEN 'ENGINEER' THEN e_salary := ; WHEN 'PROGRAMMER' THEN e_salary := ; ELSE e_salary := ; END CASE; UPDATE employee SET salary = e_salary WHERE eno = emp_id; END IF; END LOOP; CLOSE c1; / isql> SELECT eno, emp_job FROM employee WHERE salary IS NULL; ENO EMP_JOB CEO 8 MANAGER 20 SALESMAN 3 rows selected. isql> EXEC proc1; Execute success. isql> SELECT eno, emp_job, salary FROM employee WHERE eno=1 OR eno=8 OR eno=20; ENO EMP_JOB SALARY CEO MANAGER SALESMAN rows selected. 72 Stored Procedures Manual

85 LOOP 구문 loop_statement:= << label_name >> LOOP statement END LOOP label_name ; 기능 LOOP 구문은조건을따로지정하지않고반복적으로구문 ( 들 ) 을수행하고자하는경우에사용된다. 그러나 LOOP 구문에서 EXIT 문을사용하지않게되면무한 LOOP 에빠져서시스템에문제를일으킬수있으므로주의한다. 예제 CREATE TABLE item(id INTEGER, counter NUMBER(2)); CREATE OR REPLACE PROCEDURE proc1 AS DECLARE v_id item.id%type := 501; v_counter NUMBER(2) := 1; LOOP INSERT INTO item VALUES(v_id, v_counter); 흐름제어 73

86 v_counter := v_counter + 1; EXIT WHEN v_counter > 10; END LOOP; / isql> EXEC proc1; Execute success. isql> SELECT * FROM item; ITEM.ID rows selected. ITEM.COUNTER 74 Stored Procedures Manual

87 WHILE LOOP 구문 while_loop_statement ::= << label_name >> WHILE condition LOOP statement END LOOP label_name ; 기능 조건이참인경우만 LOOP 을수행하고자할때사용하는반복문이다. 만약처음부터이조건이참이아니면, WHILE 문은한번도수행되지않는다. condition LOOP 을수행할지의여부를결정하는조건절을명시한다. 조건절에는 SQL 문의 WHERE 절에서사용가능한모든술어 (predicate) 들을사용할수있다. 예제 CREATE TABLE t1(i1 INTEGER, i2 INTEGER, i3 INTEGER); CREATE OR REPLACE PROCEDURE proc1 AS v1 INTEGER; v1 := 1; 흐름제어 75

88 WHILE v1 < 3 LOOP v1 := v1 + 1; INSERT INTO t1 VALUES (v1, v1, v1); IF v1 = 2 THEN CONTINUE; END IF; END LOOP; / isql> EXEC proc1; Execute success. isql> SELECT * FROM t1; T1.I1 T1.I2 T1.I rows selected. 76 Stored Procedures Manual

89 FOR LOOP 구문 for_loop_statement ::= << label_name >> FOR counter_name IN REVERSE lower_bound.. upper_bound STEP step_size LOOP statement END LOOP label_name ; 기능 FOR LOOP 문은일정횟수만큼반복해서구문 ( 들 ) 을수행하고자할때사용된다. 두개의점 (.. ) 을사용해서범위를지정할수있는데이는 FOR loop 에진입하기전에한번만체크된다. 범위의작은값과큰값을같은값으로지정하면, loop 내부는한번만실행된다. counter_name 일정한값만큼증가, 혹은감소시킬정수형변수를하나기술한다. 이변수는블록의선언부에선언할필요가없다. 또한이변수의범위는 LOOP 과 END LOOP 으로둘러싸여있는문장들에만한정된다. FOR LOOP 내에서이변수에새로운값을할당할수없다. REVERSE 흐름제어 77

90 REVERSE 모드를지정하면 counter_name 변수의값을 upper_bound 에서 lower_bound 까지감소시키면서 FOR 문을수행한다. lower_bound counter_name 변수가가질수있는값중가장작은값이다. 정수형또는정수형과호환성이있는표현식을지정하여야한다. 여기에는지역변수를사용할수도있으나, 이값은 FOR 문이맨처음으로실행될때에딱한번그값을계산하여저장해두고사용하므로, 추후해당지역변수를변경하여도 FOR 문의동작에는영향을미치지않는다. 여기에실수형을명시하면반올림된정수값으로변환된다. upper_bound counter_name 변수가가질수있는값중가장큰값이다. lower_bound 와마찬가지로정수형또는정수형과호환성이있는표현식을지정하여야한다. 정수형이아닌수를명시하면반올림된정수값으로변환된다. 만약 FOR 문이맨처음으로수행될때 upper_bound 가 lower_bound 보다작으면어떠한에러도내지않고단지 FOR 문을건너뛰고다음문장을수행한다. lower_bound 처럼, 여기에는지역변수를사용할수도있으나, 이값은 FOR 문이맨처음으로실행될때에딱한번그값을계산하여저장해두고사용하므로, 추후해당지역변수를변경하여도 FOR 문의동작에는영향을미치지않는다. step_size 증가또는감소시킬값을지정한다. 생략하면기본적으로 FOR 문은 counter_name 변수를 1 씩증, 혹은감소시킨다. 단, step size 는 1 보다작을수없다. 정수형이아닌수를명시한경우에는반올림된정수값으로변환된다. 예제 --###################### -- FOR LOOP --###################### 78 Stored Procedures Manual

91 예제 1 CREATE TABLE t6(i1 INTEGER, sum INTEGER); CREATE OR REPLACE PROCEDURE proc1 AS v1 INTEGER; sum INTEGER := 0; FOR i IN LOOP v1 := 2 * i - 1; sum := sum + v1; INSERT INTO t6 VALUES(v1, sum); END LOOP; / isql> EXEC proc1; Execute success. isql> SELECT * FROM t6; T6.I1 T6.SUM rows selected. 예제 2 CREATE OR REPLACE PROCEDURE proc1 AS eno_count INTEGER; SELECT COUNT(eno) INTO eno_count FROM employee; FOR i IN 1.. eno_count LOOP UPDATE employee SET salary = salary * 1.2 WHERE eno = i; END LOOP; / isql> SELECT eno, salary FROM employee WHERE eno in (11,12,13); ENO SALARY 흐름제어 79

92 rows selected. isql> EXEC proc1; Execute success. isql> SELECT eno, salary FROM employee WHERE eno IN (11,12,13); ENO SALARY rows selected. 예제 3 CREATE TABLE t1(i1 INTEGER, i2 INTEGER, i3 INTEGER); CREATE OR REPLACE PROCEDURE proc1 AS <<a>> INSERT INTO t1 VALUES (1,1,1); IF 1 = 1 THEN NULL; END IF; <<b>> FOR v1 IN LOOP <<c>> FOR v1 IN LOOP INSERT INTO t1 VALUES (b.v1, b.v1, c.v1); END LOOP; END LOOP; / isql> EXEC proc1; Execute success. isql> SELECT * FROM t1; T1.I1 T1.I2 T1.I Stored Procedures Manual

93 rows selected. --##################### -- reverse --##################### CREATE TABLE t6(i1 INTEGER, sum INTEGER); CREATE OR REPLACE PROCEDURE proc1 AS sum INTEGER := 0; FOR i IN reverse LOOP sum := sum + i; INSERT INTO t6 VALUES(i, sum); END LOOP; / isql> EXEC proc1; Execute success. isql> SELECT * FROM t6; T6.I1 T6.SUM rows selected. --##################### -- step 흐름제어 81

94 --##################### CREATE TABLE t6(i1 INTEGER, sum INTEGER); CREATE OR REPLACE PROCEDURE proc1 AS sum INTEGER := 0; FOR i IN STEP 2 LOOP sum := sum + i; INSERT INTO t6 VALUES(i, sum); END LOOP; / isql> EXEC proc1; Execute success. isql> SELECT * FROM t6; T6.I1 T6.SUM rows selected. 82 Stored Procedures Manual

95 EXIT 구문 exit_statement ::= EXIT label_name WHEN condition ; 기능 EXIT 문을감싸고있는가장가까운 LOOP 문을빠져나간다. 그러나 label_name 이명시적으로주어진경우에는 label_name 이정의된 LOOP 을빠져나간다. LOOP 문내부가아닌다른블록에서 EXIT 문을사용하면오류가발생한다. <<outer>> LOOP... LOOP... EXIT outer WHEN EXIT both LOOPs END LOOP;... END LOOP outer; EXIT WHEN count > 100; IF count > 100 THEN EXIT; END IF; EXIT 문은아래의 LOOP 문내에서사용가능하다. 흐름제어 83

96 LOOP WHILE LOOP FOR LOOP CURSOR FOR LOOP label_name EXIT 문을감싸고있는가장가까운 LOOP 가아닌그보다더바깥의 LOOP 으로빠져나가야하는경우, 해당 LOOP 의바로앞에 LABEL 을정의하고그이름을여기에명시한다. WHEN condition 특정조건이참인경우루프를빠져나갈수있도록 WHEN 절에조건식을지정할수있다. 조건절에는 SELECT 문의 WHERE 절에서사용가능한모든술어들을사용할수있다. EXIT 문을만나서 WHEN 절에명시된조건이참이면저장프로시저는가장가까운루프나지정된 LABEL 블록문을빠져나가서그다음문장을수행한다. EXIT WHEN 은간단한 IF 구문과유사하다. 다음의두문장들은동일한기능을한다. EXIT WHEN count > 100; IF count > 100 THEN EXIT; END IF; 예제 CREATE TABLE stock( gno BYTE(5) primary key, stock INTEGER, price numeric(10,2)); CREATE OR REPLACE PROCEDURE proc1 AS CURSOR c1 IS SELECT gno, stock, price FROM goods; rec1 c1%rowtype; OPEN c1; LOOP 84 Stored Procedures Manual

97 FETCH c1 INTO rec1; IF c1%found THEN IF rec1.stock > 0 AND rec1.stock < 1000 THEN INSERT INTO stock VALUES(rec1.gno, rec1.stock, rec1.price); END IF; ELSIF c1%notfound THEN EXIT; END IF; END LOOP; CLOSE c1; / isql> EXEC proc1; Execute success. isql> SELECT * FROM stock; STOCK.GNO STOCK.STOCK STOCK.PRICE A B D E E rows selected. --##################### -- EXIT WHEN --##################### CREATE OR REPLACE PROCEDURE proc1 AS CURSOR c1 IS SELECT gno, stock, price FROM goods; rec1 c1%rowtype; OPEN c1; IF c1%isopen THEN LOOP FETCH c1 INTO rec1; EXIT WHEN c1%notfound; IF rec1.stock > 0 AND rec1.stock < 1000 THEN INSERT INTO stock VALUES(rec1.gno, rec1.stock, rec1.price); END IF; END LOOP; END IF; 흐름제어 85

98 CLOSE c1; / isql> EXEC proc1; Execute success. isql> SELECT * FROM stock; STOCK.GNO STOCK.STOCK STOCK.PRICE A B D E E rows selected. 86 Stored Procedures Manual

99 CONTINUE 구문 continue_statement ::= CONTINUE ; 기능 현재 CONTINUE 문을감싸고있는 LOOP 에서 CONTINUE 문이후의문장들을전부무시하고 LOOP 의맨처음으로되돌아간다. CONTINUE 문을사용할수있는 LOOP 문은다음과같다. LOOP WHILE LOOP FOR LOOP CURSOR FOR LOOP LOOP 문내부가아닌다른곳에서 CONTINUE 문을사용하면오류가발생한다. 예제 CREATE TABLE t8(i1 INTEGER, mathpower INTEGER default 0); INSERT INTO t8(i1) VALUES(7); INSERT INTO t8(i1) VALUES(3); INSERT INTO t8(i1) VALUES(20); INSERT INTO t8(i1) VALUES(15); INSERT INTO t8(i1) VALUES(6); INSERT INTO t8(i1) VALUES(1); INSERT INTO t8(i1) VALUES(9); 흐름제어 87

100 CREATE OR REPLACE PROCEDURE proc1 AS DECLARE CURSOR c1 IS SELECT i1 FROM t8; rec c1%rowtype; OPEN c1; LOOP FETCH c1 INTO rec; EXIT WHEN c1%notfound; IF power(rec.i1, rec.i1) > THEN continue; ELSE UPDATE t8 SET mathpower = power(rec.i1, rec.i1) WHERE i1 = rec.i1; END IF; END LOOP; CLOSE c1; / isql> EXEC proc1; Execute success. isql> SELECT * FROM t8; T8.I1 T8.MATHPOWER rows selected. 88 Stored Procedures Manual

101 GOTO 구문 goto_statement ::= GOTO label_name ; 기능 지정된 LABEL 로제어를이동하는분기문이다. label_name 제어를이동할 LABEL 의이름이다. 제약사항 GOTO 는다음과같은제약사항을가진다. IF 나 CASE 블록내에서사용될때, THEN, ELS(E)IF, ELSE 또는 WHEN 문에해당하는블록안으로이동할수없다. 따라서아래예제의경우에러를출력한다. CREATE OR REPLACE PROCEDURE PROC1 AS V1 INTEGER; V1 := 1; IF V1 = 1 THEN ELSE END IF; GOTO LABEL1; <<LABEL1>> PRINTLN(V1); 흐름제어 89

102 / [ERR-3120F : Illegal GOTO statement. In PROC : GOTO LABEL1; ] ^ ^ 외부블록에서내부블록으로이동할수없다. 모든 /END 블록및 LOOP 문장에이제약조건이적용된다. CREATE OR REPLACE PROCEDURE PROC1 AS / V1 INTEGER; V1 := 1; DECLARE V2 INTEGER; <<LABEL1>> V2 := 1; GOTO LABEL1; [ERR-3120F : Illegal GOTO statement. In PROC : GOTO LABEL1; ] ^ ^ 예외처리부에서분기하는경우, 그예외처리부가속한블록으로는분기할수없다. 따라서다음의예제 1 은에러를반환한다. < 예제 1> CREATE OR REPLACE PROCEDURE PROC1 AS E1 EXCEPTION; RAISE E1; <<LABEL1>> PRINTLN('END'); EXCEPTION WHEN E1 THEN GOTO LABEL1; 90 Stored Procedures Manual

103 / [ERR-3120F : Illegal GOTO statement. In PROC : GOTO LABEL1; ] ^ ^ 그러나, 예외처리부로부터그상위블록으로의분기는가능하다. 예제 2 의경우 V1 이 5 가될때까지 EXCEPTION 을 4 번발생시키고정상으로종료한다. < 예제 2> CREATE OR REPLACE PROCEDURE PROC1 AS / E1 EXCEPTION; V1 INTEGER; V1 := 1; <<LABEL1>> V1 := V1 + 1; PRINTLN('BLOCK1'); PRINTLN('BLOCK2'); PRINTLN(V1); IF V1 = 5 THEN ELSE END IF; PRINTLN('goto label2 ' v1); GOTO LABEL2; RAISE E1; EXCEPTION <<LABEL2>> WHEN E1 THEN PRINTLN('goto label1 ' v1); GOTO LABEL1; PRINTLN('BLOCK1 AFTER BLOCK2'); isql> EXEC PROC1; BLOCK1 BLOCK2 2 흐름제어 91

104 goto label1 2 BLOCK1 BLOCK2 3 goto label1 3 BLOCK1 BLOCK2 4 goto label1 4 BLOCK1 BLOCK2 5 goto label2 5 BLOCK1 AFTER BLOCK2 Execute success. 92 Stored Procedures Manual

105 NULL 구문 null_statement ::= NULL ; 기능 NULL 문은흐름에영향을미치지않고아무것도수행하지않고다음으로넘어감을명시적으로나타내기위해서사용한다. 프로그램의가독성을높이기위해서사용한다. 예제 CREATE OR REPLACE PROCEDURE bonus (amount NUMBER(10,2)) AS CURSOR c1 IS SELECT eno, sum(qty) FROM orders group by eno; order_eno orders.eno%type; order_qty orders.qty%type; OPEN c1; LOOP FETCH c1 INTO order_eno, order_qty; EXIT WHEN c1%notfound; IF order_qty > THEN UPDATE employee SET salary = salary + amount WHERE eno = order_eno; ELSE NULL; END IF; END LOOP; 흐름제어 93

106 CLOSE c1; / isql> SELECT e.eno, salary, sum(qty) FROM employee e, orders o WHERE e.eno = o.eno group by e.eno, salary; ENO SALARY SUM(QTY) rows selected. isql> EXEC bonus(55550); Execute success. isql> SELECT eno, salary FROM employee WHERE eno = 19; ENO SALARY row selected. 저장프로시저내에서테이블의레코드를읽어오는방법에는 SELECT INTO 문을사용하는방법과커서를사용하는방법이있다. 94 Stored Procedures Manual

107 5. 커서 이장은커서를관리하고사용하는방법을설명한다. 커서 95

108 커서의개요 저장프로시저내에서테이블의레코드를읽어오는방법에는 SELECT INTO 문을사용하는방법과커서를사용하는방법이있다. SELECT INTO 문의경우결과레코드의수는반드시한개여야하고그이외의경우오류가발생한다. 따라서여러개의레코드를검색해야할경우커서를사용해야한다. 커서선언 커서는저장프로시저블록의선언부에커서명과함께수행할 SELECT 문을정의해야한다. 이는선언된후에, 아래 2 가지방법중의하나로관리될수있다. OPEN, FETCH, CLOSE 사용 Cursor FOR LOOP 사용 OPEN, FETCH 와 CLOSE 로커서관리하기 OPEN FETCH 커서는블록바디내에서 OPEN, FETCH, CLOSE 문을사용해서관리할수있다. OPEN 문은커서를초기화하는데사용된다. FETCH 문은레코드를반복적으로가져오는데사용된다. 마지막으로 CLOSE 문은커서를해제한다. 커서를사용하기위해서커서와관련된모든리소스들을초기화하는단계이다. 커서정의시에사용자파라미터를지정한경우 OPEN 구문에서파라미터의값을전달해야한다. 커서의 SELECT 문을만족하는결과집합으로부터한번에한레코드씩가져와서사용자변수에저장한다. 별도의변수에각칼럼을저장할수도있고, 전체레코드를 %ROWTYPE 을사용해서정의한 RECORD 타입변수에저장할수도있다. RECORD 타입변수에대한설명은 6 장사용자정의타입으참고하기바란다. 96 Stored Procedures Manual

109 CLOSE 사용이끝난커서의리소스를해제한다. OPEN 문을사용해서열어둔커서는해당프로시저가종료되기전에반드시 CLOSE 문을사용해서닫아야한다. Cursor FOR LOOP 로커서관리하기 커서의 OPEN, FETCH, CLOSE 단계를한번에수행하는 LOOP 문의하나이다. 결과레코드가존재하지않을때까지 LOOP 를반복수행한다. 커서에대해서명시적으로 OPEN 문이나 CLOSE 문을사용할필요가없는경우에편리한구문이다. 커서 97

110 CURSOR 구문 cursor_declaration ::= CURSOR cursor_name ( cursor_parameter_declaration ), IS select_statement ; cursor_parameter_declaration ::= parameter_name IN data_type DEFAULT := cursor_name data_type ::= sql_data_type type_attribute 기능 커서를정의한다. CURSOR 구문에서는커서명과커서가레코드를가져오는데사용할 SELECT 문을명시해야한다. cursor_name 98 Stored Procedures Manual

111 cursor_parameter_declaration OPEN, FETCH, CLOSE 및 Cursor FOR LOOP 에서참조할커서의이름을지정한다. 커서의 SELECT 문에인자가필요한경우, 프로시저의파라미터를지정하는문법과같은방법으로파라미터들을지정할수있다. 이인자들은다음과같은제약이있다. 커서인자는 SELECT 문안에서만사용할수있다 %ROWTYPE 은사용할수없다. 커서인자는 OUT 또는 IN/OUT 인자일수없다. 이파라미터들은커서 OPEN 이나커서 FOR 문으로부터값을받아서 SELECT 문을수행하게된다. DECLARE CURSOR c1 IS SELECT empno, ename, job, sal FROM emp WHERE sal > 2000; CURSOR c2 (low INTEGER DEFAULT 0, high INTEGER DEFAULT 99) IS SELECT...; data_type 3 장의 지역변수선언 절을참고한다. 예제 CREATE TABLE highsal (eno INTEGER, ename CHAR(20), salary NUMBER(10,2)); CREATE OR REPLACE PROCEDURE proc1 AS DECLARE CURSOR c1 IS SELECT eno, ename, salary FROM employee WHERE salary IS not NULL ORDER BY salary desc; emp_name CHAR(20); emp_no INTEGER; 커서 99

112 emp_sal NUMBER(10,2); OPEN c1; FOR i IN LOOP FETCH c1 INTO emp_no, emp_name, emp_sal; EXIT WHEN c1%notfound; INSERT INTO highsal VALUES(emp_no, emp_name, emp_sal); END LOOP; CLOSE c1; / isql> EXEC proc1; Execute success. isql> SELECT * FROM highsal; HIGHSAL.ENO HIGHSAL.ENAME HIGHSAL.SALARY YHBAE MSKIM SJKIM JHCHOI KCJUNG rows selected. 100 Stored Procedures Manual

113 OPEN 구문 open_cursor_statement ::= OPEN cursor_name ( cursor_parameter_name ) ;, 기능 이구문은커서를초기화하고, 쿼리를수행하여결과집합을결정하는데사용된다. 이후에 FETCH 문을사용해서데이터를가져올수있다. 이구문을실행하면알티베이스는커서사용에필요한모든리소스들을할당한다. 이미 OPEN 된커서를다시 OPEN 하려고시도하면 CURSOR_ALREADY_OPEN 에러가발생한다. cursor_name OPEN 하고자하는커서의이름을지정한다. 이커서는블록의선언부에선언되어있어야한다. cursor_parameter_name 커서에사용자파라미터가명시된경우전달할값을지정한다. 인자를가지는커서의경우, 아래와같이선언한다. DECLARE CURSOR c1(pname VARCHAR(40), pno INTEGER) IS SELECT empno, ename, job, sal FROM emp WHERE eame = pname; 커서 101

114 OPEN c1;... 인자가존재하는경우다음과같이커서 OPEN 시값을넘겨준다. OPEN c1(emp_name, 100); OPEN c1('mylee', 100); OPEN c1(emp_name, dept_no); 예제 예제 1 CREATE TABLE mgr (mgr_eno INTEGER, mgr_name CHAR(20), mgr_dno SMALLINT); CREATE OR REPLACE PROCEDURE proc1 AS DECLARE CURSOR emp_cur IS SELECT eno, ename, dno FROM employee WHERE emp_job = 'MANAGER'; emp_no employee.eno%type; emp_name employee.ename%type; emp_dno employee.dno%type; OPEN emp_cur; LOOP FETCH emp_cur INTO emp_no, emp_name, emp_dno; EXIT WHEN emp_cur%notfound; INSERT INTO mgr VALUES(emp_no, emp_name, emp_dno); END LOOP; CLOSE emp_cur; / isql> EXEC proc1; Execute success. 102 Stored Procedures Manual

115 isql> select * from mgr; MGR.MGR_ENO MGR.MGR_NAME HJMIN JDLEE JHCHOI rows selected. MGR.MGR_DNO 예제 2 CREATE TABLE t1(i1 INTEGER, i2 INTEGER, i3 INTEGER); CREATE TABLE t2(i1 INTEGER, i2 INTEGER, i3 INTEGER); INSERT INTO t1 VALUES(1,1,1); INSERT INTO t1 VALUES(2,2,2); INSERT INTO t1 VALUES(30,30,30); INSERT INTO t1 VALUES(50,50,50); CREATE OR REPLACE PROCEDURE proc1 AS CURSOR c1(k1 INTEGER, k2 INTEGER, k3 INTEGER) IS SELECT * FROM t1 WHERE i1 <= k1 AND i2 <= k2 AND i3 <= k3; FOR rec1 IN c1(2,2,2) LOOP INSERT INTO t2 VALUES (rec1.i1, rec1.i2, rec1.i3); END LOOP; / isql> SELECT * FROM t2; T2.I1 T2.I2 T2.I No rows selected. isql> EXEC proc1; Execute success. isql> SELECT * FROM t2; T2.I1 T2.I2 T2.I rows selected. 커서 103

116 FETCH 구문 fetch_statement ::= FETCH cursor_name INTO record_name variable_name, ; 기능 OPEN 된커서로부터하나의행을가져와서 INTO 절에명시된변수에그값을저장한다. 이때, 커서의 SELECT 문에명시한칼럼들에상응하는사용자변수를각각나열해서칼럼별로각각저장하거나, RECORD 타입변수명만명시해서커서로부터가져온한행을 RECORD 타입변수에저장되도록할수도있다. FETCH 문에 RECORD 타입변수를사용하는데는다음과같은제약이있다. 가져온한행을저장하는데오직한개의 RECORD 타입변수만사용할수있다. SELECT 문에서가져오는모든칼럼들을한 RECORD 타입변수에저장할수있어야한다. RECORD 타입변수는일반변수와섞어서사용할수없다. 만약 OPEN 되지않은커서로부터 FETCH 를하려고시도하면 INVALID_CURSOR 오류가발생한다. cursor_name 104 Stored Procedures Manual

117 FETCH 하고자하는커서의이름을지정한다. 이커서는블록의선언부에선언되어있어야한다. record_name 커서의 SELECT 문이반환하는레코드를받을 RECORD 타입변수명을지정한다. 이때사용되는 RECORD 타입변수는 SEELCT 구문의 select list 와칼럼의개수가같고타입이순서대로정확히일치해야한다. 따라서커서의 SELECT 문이한테이블의모든칼럼을가져오는경우, 그테이블에대해 %ROWTYPE 속성을사용해서선언한 RECORD 타입변수를주로사용하게된다. variable_name 값을저장할변수의이름이다. 이변수들의개수는커서의 SELECT 문에서명시한칼럼의개수와동일해야한다. 또한나열한변수의순서대로상응하는칼럼의타입과각각일치해야한다. LOOP FETCH c1 INTO my_name, my_empno, my_deptno; EXIT WHEN c1%notfound; END LOOP; 예제 CREATE TABLE emp_temp(eno INTEGER, ename CHAR(20)); CREATE OR REPLACE PROCEDURE proc1 AS DECLARE CURSOR c1 IS SELECT eno, ename FROM employee; emp_rec c1%rowtype; OPEN c1; LOOP FETCH c1 INTO emp_rec; EXIT WHEN c1%notfound; INSERT INTO emp_temp VALUES(emp_rec.eno, emp_rec.ename); END LOOP; CLOSE c1; 커서 105

118 / isql> EXEC proc1; Execute success. isql> SELECT * FROM emp_temp; EMP_TEMP.ENO EMP_TEMP.ENAME EJJUNG 2 HJNO 3 HSCHOI KMKIM 20 DIKIM 20 rows selected. 106 Stored Procedures Manual

119 CLOSE 구문 close_cursor_statement ::= CLOSE cursor_name ; 기능 열려있는커서를닫고해당커서에할당된리소스를해제한다. 커서가열려있지않았거나, 이미닫힌커서를닫으려고하면 INVALID_CURSOR 에러가발생한다. 사용자가커서를명시적으로 CLOSE 하지않아도커서가선언된블록을빠져나가게되면자동으로 CLOSE 되지만, 커서를 CLOSE 한다는것은커서와관련된모든리소스를시스템에반납한다는것을의미하므로, 사용이끝난커서는즉시 CLOSE 해주는것이바람직하다. cursor_name CLOSE 하고자하는커서의이름을명시한다. 예제 CLOSE c1; 커서 107

120 Cursor FOR LOOP 구문 cursor_for_loop_statement ::= << label_name >> FOR counter_name IN cursor_name ( cursor_parameter_name ), LOOP statement END LOOP label_name ; 기능 커서열기, 결과가져오기와커서닫기를자동으로처리하는기능을한다. Cursor FOR LOOP 은블록선언부에선언한커서를이용하여, 한번의 LOOP 를수행할때마다쿼리실행결과를한행씩반환한다. 이때하나의행은 RECORD 타입의변수에저장되고루프안에서이를자유롭게사용할수있다. label_ name EXIT 문이나 CONTINUE 문에서 LOOP 을지칭하는데필요한 LABEL 을명시할수있다. counter_name 108 Stored Procedures Manual

121 커서를이용하여 FETCH 된하나의행이저장될 RECORD 타입의변수명을지정한다. 이변수는블록의선언부에선언되어있지않아도 FETCH 된행이가지는칼럼의개수와타입에맞춰서자동으로생성된다. 이렇게생성된변수는 counter_name.column_name 형식으로참조할수있다. 이때 column_name 은 SELECT 구문의 select list 에존재하는칼럼의이름과동일하다. 따라서 select list 에연산식을사용한경우는해당연산식에별칭 (alias) 를지정하여참조가능하도록만들어야한다. cursor_name cursor_parameter_name 루프안에서사용하게될커서의이름를지정한다. 이커서는블록의선언부에선언되어있어야한다. OPEN 절의 cursor_parameter_name 을참조한다. 예제 CREATE TABLE emp_temp(eno INTEGER, ename CHAR(20)); CREATE OR REPLACE PROCEDURE proc1 AS DECLARE CURSOR c1 IS SELECT eno, ename FROM employee; FOR emp_rec IN c1 LOOP INSERT INTO emp_temp VALUES(emp_rec.eno, emp_rec.ename); END LOOP; / isql> EXEC proc1; Execute success. isql> SELECT * FROM emp_temp; EMP_TEMP.ENO EMP_TEMP.ENAME EJJUNG 커서 109

122 2 HJNO KMKIM 20 DIKIM 20 rows selected. 110 Stored Procedures Manual

123 커서속성 저장프로시저내에서커서와관련된수행도중커서의상태를파악하기위해서알티베이스가관리하고있는속성값들을참조할수있다. 구문 cursor_attribute ::= FOUND NOTFOUND cursor_name % ; ISOPEN ROWCOUNT 기능 특정커서의속성을참조할수있다. 정수형을반환하는 ROWCOUNT 커서속성을제외하고, 커서속성은커서의상태를나타내는 BOOLEAN 형의표현식이다. 커서의현재상태에따라각각의속성값은 TRUE 또는 FALSE 일수있다. 사용자는 DECLARE 구문에직접정의한커서의속성값뿐만아니라시스템에서정의한암시적커서의속성값도참조할수있다. 암시적커서는 DELETE, UPDATE, INSERT 와한건의레코드가반환되는 SELECT INTO 구문을위해선언된다. 이는가장최근에수행된 SQL 구문에해당하는커서의속성값을갖는다. %FOUND 커서에명시한 SELECT 문의조건을만족하는행이존재하는지여부를나타낸다. 단, 다음의경우에는조건을만족하는행의존재 커서 111

124 여부에상관없이 %FOUND 의값이무조건 FALSE 이다. 열지않은커서 FETCH 를한번도하지않은커서 이미닫은커서 암시적커서의경우는 DELETE, UPDATE, INSERT 문의수행결과로 1 건이상이영향을받거나, SELECT INTO 의반환되는레코드가 1 건이상일때, %FOUND 의값은 TRUE 가된다. 그러나 SELECT INTO 의반환되는레코드가 2 건이상일때에는 %FOUND 를검사하기전에 TOO_MANY_ROWS 예외가발생하므로, 이경우 %FOUND 커서속성이아닌예외로처리해야한다. %FOUND 속성의값은다음과같이참조할수있다. DELETE FROM emp; IF SQL%FOUND THEN... INSERT INTO emp VALUES (... ); END IF; -- delete succeeded %NOTFOUND 커서에명시한 SELECT 문의조건을만족하는행이존재하는지여부를나타낸다. 항상 %FOUND 와반대의값을가진다. 암시적커서의경우는 DELETE, UPDATE, INSERT 구문이성공적으로수행되었으나영향을받은레코드가하나도없거나, 또는 SELECT INTO 가반환하는레코드가하나도없을때, %NOTFOUND 의값이 TRUE 로된다. 그러나 SELECT INTO 가반환하는레코드가없을때는 %NOTFOUND 속성을검사하기전에 NO_DATA_FOUND 예외가발생하므로, %NOTFOUND 커서속성이아닌예외로처리해야한다. %NOTFOUND 속성의값은다음과같이참조할수있다. DELETE FROM emp; IF SQL%NOTFOUND THEN... END IF; %ISOPEN 커서가열렸는지여부를나타낸다. 커서를닫으면이값은 FALSE 가된다. %ISOPEN 의값은다음과같이참조할수있다. 112 Stored Procedures Manual

125 OPEN c1; -- CURSOR OPEN IF c1%isopen THEN... END IF; %ROWCOUNT 커서를사용하여현재몇개의행을 FETCH 하였는지를나타낸다. 주의할점은 %ROWCOUNT 는커서의 SELECT 문을만족하는행의수가아니라, FETCH 를성공할때마다 1 씩증가한다는것이다. FETCH 를한번도하지않은커서의경우 %ROWCOUNT 는 0 이다. 열기전의커서와닫은이후의커서에대해속성을참조하면 INVALID_CURSOR 에러를반환한다. DELETE FROM emp; IF SQL%ROWCOUNT > 10 THEN... END IF; 예제 예제 1 CREATE TABLE t1(i1 INTEGER, i2 INTEGER, i3 INTEGER); CREATE TABLE t3(i1 INTEGER); INSERT INTO t1 VALUES(2,2,2); CREATE OR REPLACE PROCEDURE proc1 AS v1 INTEGER; SELECT i1 INTO v1 FROM t1 WHERE i1 = 2; IF SQL%found THEN INSERT INTO t1 SELECT * FROM t1; v1 := SQL%ROWCOUNT; INSERT INTO t3 VALUES(v1); END IF; / 커서 113

126 isql> EXEC proc1; Execute success. isql> SELECT * FROM t3; T3.I row selected. 예제 2 CREATE TABLE t1(i1 INTEGER, i2 INTEGER, i3 INTEGER); CREATE TABLE t2(i1 INTEGER, i2 INTEGER, i3 INTEGER); CREATE TABLE t3(i1 INTEGER); INSERT INTO t1 VALUES(1,1,1); INSERT INTO t1 VALUES(1,1,1); INSERT INTO t1 VALUES(1,1,1); CREATE OR REPLACE PROCEDURE proc1 AS CURSOR c1 IS SELECT * FROM t1; v1 INTEGER; v2 INTEGER; v3 INTEGER; OPEN c1; IF c1%isopen THEN LOOP FETCH c1 INTO v1, v2, v3; IF c1%found THEN INSERT INTO t2 VALUES (v1, v2, v3); ELSIF c1%notfound THEN EXIT; END IF; END LOOP; END IF; v1 := c1%rowcount; INSERT INTO t3 VALUES (v1); CLOSE c1; / 114 Stored Procedures Manual

127 isql> EXEC proc1; Execute success. isql> SELECT * FROM t1; T1.I1 T1.I2 T1.I rows selected. isql> SELECT * FROM t2; T2.I1 T2.I2 T2.I rows selected. isql> SELECT * FROM t3; T3.I row selected. 예제 3 CREATE TABLE emp_temp(eno INTEGER, ename CHAR(20)); CREATE OR REPLACE PROCEDURE proc1 AS DECLARE CURSOR c1 IS SELECT eno, ename FROM employee; emp_rec c1%rowtype; OPEN c1; LOOP FETCH c1 INTO emp_rec; EXIT WHEN c1%rowcount > 10 OR c1%notfound; INSERT INTO emp_temp VALUES(emp_rec.eno, emp_rec.ename); END LOOP; CLOSE c1; 커서 115

128 / isql> EXEC proc1; Execute success. isql> SELECT * FROM emp_temp; EMP_TEMP.ENO EMP_TEMP.ENAME EJJUNG 2 HJNO 3 HSCHOI 4 KSKIM 5 SJKIM 6 HYCHOI 7 HJMIN 8 JDLEE 9 KMLEE 10 YHBAE 10 rows selected. 116 Stored Procedures Manual

129 6. 사용자정의타입 이장에서는저장프로시저와저장함수에서사용할수있는사용자정의타입에대해서설명하고있다. 사용자정의타입 117

130 개요 사용자정의타입인 RECORD 타입및 Associative Arrays 는데이터를논리적단위로구성하여처리하는기능을제공한다. 또한다른저장프로시저또는함수로부터호출되는저장프로시저또는함수의인자또는리턴타입으로사용이가능하다. 단, 클라이언트로는해당타입의값을전송할수없다. RECORD 타입 RECORD 타입은칼럼의집합으로이루어진사용자정의데이터타입이다. 이를이용하여각각의서로다른종류의데이터들을논리적단위로구성하여처리할수있다. 예를들어이름, 급여, 부서등의서로다른데이터들을 사원 이라는하나의데이터로처리하는경우하나의 RECORD 타입으로묶어처리할수있다. 블록안에정의된 RECORD 타입의범위는지역적이다. 즉, 그타입을정의한블록내에서만사용할수있다. RECORD 타입의정의는 6 장의 사용자정의타입의정의 절을참고한다. 정의하는방법을제외한다른사용방법은 %ROWTYPE 속성을사용해서선언하는 RECORD 타입변수와동일하다. Associative Arrays Associative Array 는해시테이블과유사하다. Associative Array 는키 - 값쌍의집합이다. 각키는유일한값이어야하고, 키에상응하는값에접근할때사용된다. 다음의문법으로접근할수있다. variable_name[index] 키 (index) 의데이터타입은 INTEGER 또는 VARCHAR 이어야한다. Associative Array 를이용하여데이터의개수와상관없이동일한타입의데이터들을하나의단위로묶어처리를할수있다. 예를들어사원들중사원번호 1~100 까지의사원데이터를처리하는경우 100 개의데이터를하나의 Associative Array 로처리할수있다. Associative Array 의선언방법은 6 장의 사용자정의타입의정의 절을참고한다. Associative Array 변수의배열요소접근을위해서는다음과같이 118 Stored Procedures Manual

131 대괄호 ( [ ] ) 를사용한다. 예 ) V1[ 1 ] := 1; REF CURSOR ( 커서변수 ) 커서변수는다중레코드를검색하는동적 SQL 문에서사용할수있는변수이다. 이러한커서변수는특정질의문에종속되지않기때문에커서보다훨씬유연하다. 저장프로시저또는함수간인자로전달이가능하고, 클라이언트로도전달할수있다. 커서와다른점은커서는항상선언시에명시한질의문만참조할수있지만, 커서변수는커서 OPEN 시에다른질의문을참조할수있다는점이다. 사용자정의타입 119

132 사용자정의타입의정의 구문 type_definition ::= associative_array_type_spec TYPE type_name IS record_type_spec ref_cursor_type_spec associative_array_type_spec ::= TABLE OF data_type INDEX BY integer_type varchar_type record_type_spec ::= RECORD ( column_name sql_data_type ), ref_cursor_type_spec ::= REF CURSOR 120 Stored Procedures Manual

133 type_name 사용자정의타입의이름을명시한다. associative_array_spec data_type 으로이루어진 Associative Array 타입을정의한다. 단, data_type 에다른 Associative Array 는올수없다. 즉 Associative Array 타입의 Associative Array 는생성할수없다. record_type_spec sql_data_type 으로이루어진 RECORD 타입을정의한다. sql_data_type 에는 SQL 구문에서사용가능한어떤데이터타입도올수있다. 단, Associative Array, RECORD 타입은여기에올수없다. ref_cursor_type_spec REF CURSOR 타입을정의한다. 예제 예제 1 이름 (VARCHAR(20)), 부서 (INTEGER), 봉급 (NUMBER(8)) 을 갖는이름이 employee 인 RECORD 타입을정의하라. DECLARE TYPE employee IS RECORD( name VARCHAR(20), dept INTEGER, salary NUMBER(8)); 예제 2 VARCHAR(20) 을구성요소로하고 INTEGER 타입을인덱스로하는이름이 namelist 인 Associative Array 를정의하라. DECLARE TYPE namelist IS TABLE OF VARCHAR(20) INDEX BY INTEGER; 사용자정의타입 121

134 예제 3 사용자정의 RECORD 타입인 employee 를구성요소로가지고 VARCHAR(10) 타입을인덱스로하는 employeelist 라는이름의 Associative Array 를정의하라. DECLARE TYPE employee IS RECORD( name VARCHAR(20), dept INTEGER, salary NUMBER(8)); TYPE employeelist IS TABLE OF employee INDEX BY VARCHAR(10)); 122 Stored Procedures Manual

135 Associative Array 관련함수 구문 associative_array_call_method ::= COUNT ( ), variable_name. index DELETE ( ) EXISTS ( index ) FIRST LAST NEXT PRIOR ( ) ( ) ( index ) ( index ) 기능 Associative array 변수의배열요소관리를위한여러가지함수를제공한다. 이함수들은 SQL 함수와달리 () 를생략할수없다. COUNT Associative array 의구성요소의개수를반환한다. DELETE DELETE() 는모든구성요소를제거하고제거된구성요소의개수를반환한다. DELETE(n) 은 n 에해당하는요소를제거하고제거된구성요소의개수를반환한다. 사용자정의타입 123

136 DELETE(m, n) 은범위 m 부터 n 에속하는모든구성요소를제거하고제거된구성요소의개수를반환한다. m 의값이 n 보다크면, 아무것도제거되지않는다. 값이같으면, 한요소만제거될것이다. EXISTS EXISTS(n) 은 n 에해당하는구성요소가존재하는지검사해서존재하면 boolean 값 TRUE 를그렇지않으면 FALSE 를반환한다. FIRST 정수값으로색인화된경우, 가장작은 index 번호를반환한다. 문자열로색인화된경우, 가장낮은키값을반환한다. 구성요소가하나도없으면 NULL 을반환한다. LAST 정수값으로색인화된경우, 가장큰 index 번호를반환한다. 문자열로색인화된경우, 가장높은키값을반환한다. 구성요소가하나도없으면 NULL 을반환한다. NEXT NEXT(n) 은 n 의다음 index 번호를반환한다. VARCHAR 키를갖는배열의경우, 키문자열들을바이너리값기준으로정렬하여바로다음키값을반환한다. 가장다음 index 가없으면 NULL 을반환한다. PRIOR PRIOR(n) 은 n 의이전 index 번호를반환한다. VARCHAR 키를갖는배열의경우, 키문자열들을바이너리값기준으로정렬하여바로앞의키값을반환한다. 이전 index 가없으면 NULL 을반환한다. 예제 예제 1 Associative array 변수 V1 의요소를삭제 CREATE OR REPLACE PROCEDURE PROC1( 124 Stored Procedures Manual

137 P1 IN VARCHAR(10), P2 IN VARCHAR(10) ) AS TYPE MY_ARR IS TABLE OF INTEGER INDEX BY VARCHAR(10); V1 MY_ARR; V2 INTEGER; V1['FSDGADS'] := 1; V1['AA'] := 2; V1['7G65'] := 3; V1['N887K'] := 4; V1['KU'] := 5; V1['34'] := 6; PRINTLN( 'V1 COUNT IS : ' V1.COUNT() ); V2 := V1.DELETE(P1, P2); PRINTLN( 'DELETED COUNT IS : ' V2); PRINTLN( 'V1 COUNT IS : ' V1.COUNT() ); / 실행결과 EXEC PROC1('005T34', 'BC35'); -- 이범위에포함되는인덱스를가지는요소는 V1['34'], V1['7G65'], V1['AA'] 으로총 3 개가지워져야한다. V1 COUNT IS : 6 DELETED COUNT IS : 3 V1 COUNT IS : 3 Execute success. 예제 2 Associative array 변수 V1 을오름차순과내림차순으로출력해라. CREATE OR REPLACE PROCEDURE PROC1 AS TYPE MY_ARR1 IS TABLE OF INTEGER INDEX BY INTEGER; V1 MY_ARR1; V1_IDX INTEGER; V1[435754] := 1; V1[95464] := 2; 사용자정의타입 125

138 V1[38] := 3; V1[57334] := 4; V1[138] := 5; V1[85462] := 6; PRINTLN( 'ASCENDING ORDER V1'); V1_IDX := V1.FIRST(); LOOP IF V1_IDX IS NULL THEN EXIT; ELSE PRINTLN( 'V1 IDX IS : ' V1_IDX ' VALUE IS : ' V1[V1_IDX] ); V1_IDX := V1.NEXT(V1_IDX); END IF; END LOOP; PRINTLN( 'DESCENDING ORDER V1'); V1_IDX := V1.LAST(); LOOP IF V1_IDX IS NULL THEN EXIT; ELSE PRINTLN( 'V1 IDX IS : ' V1_IDX ' VALUE IS : ' V1[V1_IDX] ); V1_IDX := V1.PRIOR(V1_IDX); END IF; END LOOP; / 실행결과 EXEC PROC1; ASCENDING ORDER V1 V1 IDX IS : 38 VALUE IS : 3 V1 IDX IS : 138 VALUE IS : 5 V1 IDX IS : VALUE IS : 4 V1 IDX IS : VALUE IS : Stored Procedures Manual

139 V1 IDX IS : VALUE IS : 2 V1 IDX IS : VALUE IS : 1 DESCENDING ORDER V1 V1 IDX IS : VALUE IS : 1 V1 IDX IS : VALUE IS : 2 V1 IDX IS : VALUE IS : 6 V1 IDX IS : VALUE IS : 4 V1 IDX IS : 138 VALUE IS : 5 V1 IDX IS : 38 VALUE IS : 3 Execute success. 사용자정의타입 127

140 RECORD 타입변수및 Associative Array 변수의사용 여기서는사용자정의타입을저장프로시저내에서사용할때의규칙및예제를다룬다. 사용자정의타입을파라미터및리턴값으로사용하는부분은 7 장타입세트를참고한다. 타입호환성 L_VALUE := R_VALUE; 위와같은할당문에사용될때사용자정의타입의타입호환성을살펴보면다음과같다. L_VALUE 의타입 R_VALUE 의타입호환성 RECORD 타입 RECORD 타입 같은이름을갖는동일한 레코드타입간에만 호환된다. 레코드타입의내부구조가같은다른레코드타입은서로호환되지않는다. RECORD 타입 %ROWTYPE 내부에정의된칼럼의 개수만동일하면호환된다. %ROWTYPE RECORD 타입 내부에정의된칼럼의 개수만동일하면호환된다. Associative Array Associative Array 동일한타입 ( 즉, 같은 이름의타입 ) 간에만 호환된다. 다음예제와같이사용자정의타입은그내부구조가동일하다하더라도할당문은실패하게된다. 예제 1 RECORD 타입변수의할당 CREATE OR REPLACE PROCEDURE PROC1 AS TYPE emp_rec_type1 IS RECORD ( name VARCHAR(20), job_id VARCHAR(10), salary NUMBER(8) ); 128 Stored Procedures Manual

141 TYPE emp_rec_type2 IS RECORD ( name VARCHAR(20), job_id VARCHAR(10), salary NUMBER(8) ); v_emp1 emp_rec_type1; v_emp2 emp_rec_type2; v_emp1.name := 'smith'; v_emp1.job_id := 'RND1069'; v_emp1.salary := ' '; v_emp2 := v_emp1; -- 실패. 두변수가같은내부구조이지만각각다른타입이므로할당은실패한다. 그러나아래처럼타입이일치하는요소간의할당은가능하다. v_emp2.name := v_emp1.name; RECORD 타입변수예제 예제 1 사원의이름, 급여, 부서를저장하는 RECORD 타입의변수를생성한다. CREATE OR REPLACE PROCEDURE PROC1 AS TYPE emp_rec_type IS RECORD ( v_emp name emp_rec_type; VARCHAR(20), job_id VARCHAR(10), salary NUMBER(8) ); v_emp.name := 'smith'; v_emp.job_id := 'RND1069'; v_emp.salary := ' '; PRINTLN('NAME : ' v_emp.name ' ' 'JOB ID : ' v_emp.job_id ' ' 'SALARY : ' v_emp.salary ); 사용자정의타입 129

142 / ASSOCIATIVE ARRAY 타입예제 예제 1 사원번호가 1 에서 100 사이에속한사원의이름을출력한다. CREATE OR REPLACE PROCEDURE PROC1 AS TYPE emp_array_type IS TABLE OF VARCHAR(20) INDEX BY INTEGER; v_emp emp_array_type; FOR I IN LOOP SELECT name INTO v_emp[i] FROM employees WHERE emp_id = I; END LOOP; FOR I IN v_emp.first().. v_emp.last() LOOP PRINTLN( v_emp[i] ); END LOOP; / 예제 2 사원번호가 1 에서 100 사이에속한사원의이름, 급여, 부서를출력한다. CREATE OR REPLACE PROCEDURE PROC1 AS TYPE emp_rec_type IS RECORD ( name VARCHAR(20), job_id VARCHAR(10), salary NUMBER(8) ); TYPE emp_array_type IS TABLE OF emp_rec_type INDEX BY INTEGER; v_emp emp_array_type; 130 Stored Procedures Manual

143 FOR I IN LOOP SELECT name, job_id, salary INTO v_emp[i] FROM employees WHERE emp_id = I; END LOOP; FOR I IN v_emp.first().. v_emp.last() LOOP PRINTLN( v_emp[i].name v_emp[i].job_id v_emp[i].salary ); END LOOP; / 사용자정의타입 131

144 REF CURSOR 저장프로시저는동적 SQL 의실행으로얻은결과집합 (Result set) 을커서변수 (REF CURSOR) 를통해서클라이언트에게전달할수있다. OPEN FOR 문으로커서변수를열고질의를실행한후, 커서를 OUT 인자를통해서클라이언트로전달하면클라이언트는결과집합에접근할수있게된다. 여러개의커서를전달하면, 클라이언트에서는다중결과집합에접근이가능하다. 커서변수를열기위한 OPEN FOR 구문을제외하고, 기존의커서관련구문을그대로이용할수있다. 커서변수는저장프로시저의 OUT 또는 IN/OUT 인자로만전달할수있고, 저장함수에서 RETURN 문으로는반환할수없다. 저장프로시저에서 OPEN 된상태로커서변수가클라이언트로전달되어야클라이언트에서결과집합을 FETCH 할수있다. 즉, 커서상태가 CLOSE 상태로전달될경우결과집합을사용할수없다. 저장프로시저에서 UPDATE, INSERT 문을실행할경우, 영향받은레코드수 (Affected Row Count) 는반환하지않는다. 클라이언트에서커서변수로결과집합을받는방법은클라이언트의형태에따라달라진다. 커서변수를이용하여결과집합을클라이언트로전달하는것은 ODBC 와 JDBC 에서만가능하다. Embedded SQL (Altibase Precompiler (APRE)) 에서는커서변수를통해프로시저의결과집합을받을수없다. 예제 REF CURSOR를이용한저장프로시저를생성한다. 1. 테이블 emp 와 staff 을생성하고값을입력한다. create table emp (eno integer, ename char(20), dno integer); create table staff (name char(20), dept char(20), job char(20), salary integer); insert into emp values (10, 'dulgi papa', 100); insert into emp values (20, 'kunhan', 200); insert into emp values (30, 'okasa', 300); insert into staff values ('dulgi papa', '100', 'papa', 100); insert into staff values ('shincha', '200', 'engineer', 200); 132 Stored Procedures Manual

145 insert into staff values ('ji hyung', '300', '', 0); 2. REF CUR 인사용자정의타입 MY_CUR 를정의하고, 이를포함하는타입세트 MY_TYPE 를정의한다. create typeset MY_TYPE as end; / type MY_CUR is REF CURSOR; 3. MY_CUR 타입의 OUT 인자 P1 과 P2, INTEGER 타입의 IN 인자 SAL 을가지는저장프로시저 PROC1 을생성한다. create or replace procedure PROC1 (P1 out MY_TYPE.MY_CUR, P2 out MY_TYPE.MY_CUR, SAL in INTEGER) as sql_stmt VARCHAR2(200); begin sql_stmt := 'SELECT name,dept,job FROM staff WHERE salary >?'; OPEN P1 FOR 'SELECT eno, ename, dno FROM emp'; OPEN P2 FOR sql_stmt USING SAL; end; / 4. 데이터베이스서버에연결한후, 프로시저 PROC1 을실행한다. SQLRETURN execute_proc() { SQLCHAR errmsg[msg_len]; char sql[1000]; SQLHSTMT stmt = SQL_NULL_HSTMT; int sal; int sal_len; int eno; int eno_len; int dno; int dno_len; SQLCHAR ename[ename_len+1]; SQLCHAR name[name_len+1]; SQLCHAR dept[dept_len+1]; SQLCHAR job[job_len+1]; int job_ind; SQLRETURN rc = SQL_SUCCESS; 사용자정의타입 133

146 if (SQL_ERROR == SQLAllocStmt(dbc, &stmt)) { printf("sqlallocstmt error!!\n"); return SQL_ERROR; } /* 실행할 SQL 문을준비 */ sprintf(sql, "EXEC proc1(?)"); if ( SQLPrepare(stmt,(SQLCHAR *)sql,sql_nts) == SQL_ERROR ) { printf("error: prepare stmt\n"); } else { printf("success: prepare stmt\n"); } /* 변수 sal 에 100 을할당 */ sal = 100; /* SQL 문장에매개변수 (sal) 를연결시킴 */ if ( SQLBindParameter( stmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0, &sal, 0, NULL) == SQL_ERROR ) { printf("error: Bind Parameter\n"); } else { printf("success: 1 Bind Parameter\n"); } /* SQL 문장실행, 프로시져 PROC1 을실행해서 'SELECT eno, ename, dno FROM emp' 의결과값은 P1 에 'SELECT name,dept,job FROM staff WHERE salary >?'(USING SAL) 의결과값은 P2 에가져온다 */ if (SQL_ERROR == SQLExecute(stmt)) 134 Stored Procedures Manual

147 { } printf("error: Execute Procedure\n"); /* 'SELECT eno, ename, dno FROM emp' 의결과값을변수 (eno, ename, dno) 에저장 */ if (SQL_ERROR == SQLBindCol(stmt, 1, SQL_C_SLONG, &eno, 0, (long *)&eno_len)) { printf("error: Bind 1 Column\n"); } if (SQL_ERROR == SQLBindCol(stmt, 2, SQL_C_CHAR, ename, sizeof(ename), NULL)) { printf("error: Bind 2 Column\n"); } if (SQL_ERROR == SQLBindCol(stmt, 3, SQL_C_SLONG, &dno, 0, (long *)&dno_len)) { printf("error: Bind 3 Column\n"); } /* P1 에결과값이있는동안결과값을받아화면에출력 */ while (SQL_SUCCESS == rc) { rc = SQLFetch(stmt); if (SQL_SUCCESS == rc) { printf("result Set 1 : %d,%s,%d\n",eno, ename, dno); } else { if (SQL_NO_DATA == rc) { break; } else { printf("error: SQLFetch [%d]\n", rc); execute_err(dbc, stmt, sql); break; } } } 사용자정의타입 135

148 /* 다음결과 (P2) 로이동 */ rc = SQLMoreResults(stmt); if (SQL_ERROR == rc) { printf("error: SQLMoreResults\n"); } else { /* 'SELECT name,dept,job FROM staff WHERE salary >?'(USING SAL) 의 결과값을변수 (name, dept, job) 에저장 */ if (SQL_ERROR == SQLBindCol(stmt, 1, SQL_C_CHAR, name, sizeof(name), NULL)) { printf("error: Bind 1 Column\n"); } if (SQL_ERROR == SQLBindCol(stmt, 2, SQL_C_CHAR, dept, sizeof(dept), NULL)) { printf("error: Bind 2 Column\n"); } if (SQL_ERROR == SQLBindCol(stmt, 3, SQL_C_CHAR, job, sizeof(job), (long *)&job_ind)) { printf("error: Bind 3 Column\n"); } /* P2 에결과값이있는동안결과값을받아화면에출력 */ while (SQL_SUCCESS == rc) { rc = SQLFetch(stmt); if (SQL_SUCCESS == rc) { if( job_ind == -1 ) printf("result Set 2 : %s,%s,null\n",name, dept); else printf("result Set 2 : %s,%s,%s\n",name, dept, job); } else { if (SQL_NO_DATA == rc) { 136 Stored Procedures Manual

149 break; } else { printf("error: SQLFetch [%d]\n", rc); execute_err(dbc, stmt, sql); break; } } } } if (SQL_ERROR == SQLFreeStmt( stmt, SQL_DROP )) { printf("sql free stmt error\n"); } } 사용자정의타입 137

150

151 7. 타입세트 이장에서는타입세트를정의하고사용하는방법에대해설명한다. 타입세트 139

152 개요 타입세트 (Type Set) 는저장프로시저에서사용하는사용자정의타입들을한곳에모아서관리하도록해주는데이터베이스객체 (Object) 이다. 특징 사용자정의타입의공유 사용자정의타입들을한곳에서관리하므로각각의저장프로시저내에서동일한구조의사용자정의타입을중복해서선언하지않아도된다. 사용자정의타입을인자또는리턴값으로사용 동일한타입세트에속해있는타입은인자또는리턴값으로프로시저간전달이가능하다. 단, 클라이언트로는전송할수없다. 데이터타입들을논리적단위로통합관리 타입세트는저장프로시저와저장함수에서사용하는타입들을데이터의논리적단위로통합관리하기용이하게해준다. 결과집합전달 저장프로시저내부에서실행된 SQL 문의결과집합을 REF CURSOR 타입을사용하여클라이언트로전달할수있다. 구조 아래그림과샘플코드에서처럼, 타입세트를사용하면여러저장프로시저에서사용하는사용자정의타입을공유하고관리하는것이가능하며, 데이터이동이용이하다. 140 Stored Procedures Manual

153 TYPESET 1 PROCEDURE 1, 2, FUNCTION 3 에서사용하는데이터타입들의집합 PROCDURE 1 PROCDURE 2 FUNCTION 3 TYPESET 1 사용자정의타입 emp_rec_type 과 emp_arr_type 을 typeset_1 안에정의한다. CREATE TYPESET typeset_1 AS TYPE emp_rec_type IS RECORD ( name VARCHAR(20), job_id VARCHAR(10), salary NUMBER(8) ); TYPE emp_arr_type IS TABLE OF emp_rec_type INDEX BY INTEGER; / PROCEDURE 1 저장프로시저 procedure_1 에서 emp_arr_type 을 OUT 인자로하는 procedure_2 를호출한다. CREATE PROCEDURE procedure_1 AS V1 typeset_1.emp_arr_type; procedure_2( V1 ); PRINTLN(V1[1].name); PRINTLN(V1[1].job_id); PRINTLN(V1[1].salary); / PROCEDURE 2 타입세트 141

154 저장프로시저 procedure_2 의 OUT 인자에 function_3 의반환값을할당한다. CREATE PROCEDURE procedure_2 ( P1 OUT typeset_1.emp_arr_type ) AS V1 typeset_1.emp_rec_type; V1 := function_3(); P1[1] := V1; / FUNCTION 3 typeset_1.emp_rec_type 타입의값을반환한다. CREATE FUNCTION function_3 RETURN typeset_1.emp_rec_type AS V1 typeset_1.emp_rec_type; V1.name := 'Smith'; V1.job_id := 1010; V1.salary := 200; RETURN V1; / 142 Stored Procedures Manual

155 CREATE TYPESET 구문 create_typeset ::= CREATE TYPESET typeset_name OR REPLACE user_name. AS IS type_declaration END 전제조건 SYS 사용자또는 CREATE PROCEDURE, CREATE ANY PROCEDURE 시스템권한을가진사용자만실행가능하다. 설명 저장프로시저에서사용할사용자정의타입을포함하는타입세트를정의한다. 타입세트내에서정의한타입은프로시저의 INPUT / OUTPUT 인자로사용할때용이하다. user_name 생성될타입세트의소유자이름을명시한다. 생략하면알티베이스는현재세션에연결된사용자의스키마에타입세트를생성한다 typeset_name 타입세트의이름이다. type_declaration 타입세트 143

156 6 장사용자정의타입에서 사용자정의타입의정의 절을참고한다. 예제 예제 1 my_typeset 이란이름의타입세트를생성한다. CREATE TYPESET my_typeset AS TYPE emp_rec_type IS RECORD( name VARCHAR(20), id INTEGER ); TYPE emp_arr_type IS TABLE OF emp_rec_type INDEX BY INTEGER; / 예제 2 my_typeset 을이용하는프로시저 my_proc1 을생성한다. CREATE PROCEDURE my_proc1 AS V1 my_typeset.emp_rec_type; V2 my_typeset.emp_arr_type; V1.name := 'jejeong'; V1.id := 10761; V2[1] := V1; V1.name := 'ehkim'; V1.id := 11385; V2[2] := V1; V1.name := 'mslee'; V1.id := 13693; V2[3] := V1; PRINTLN('NAME : ' V2[1].name ' ID : ' V2[1].id ); PRINTLN('NAME : ' V2[2].name ' ID : ' V2[2].id ); PRINTLN('NAME : ' V2[3].name ' ID : ' V2[3].id ); 144 Stored Procedures Manual

157 / 결과 isql> exec my_proc1; NAME : jejeong ID : NAME : ehkim ID : NAME : mslee ID : Execute success. 타입세트 145

158 DROP TYPESET 구문 drop_typeset ::= DROP TYPESET typeset_name ; user_name. 전제조건 SYS 사용자이거나객체의생성자또는 DROP ANY PROCEDURE 시스템권한을가진사용자만실행가능하다. 설명 명시된타입세트를제거한다. 제거된타입세트를사용하던저장프로시저는유효하지않은 (Invalid) 상태가된다. user_name 제거될타입세트의소유자이름을명시한다. 생략하면알티베이스는제거될타입세트가현재세션에연결된사용자의스키마에속한것으로간주한다. typeset_name 타입세트의이름이다. 예제 146 Stored Procedures Manual

159 my_typeset 이란이름의타입세트를삭제한다. DROP TYPESET my_typeset 타입세트 147

160

161 8. 동적 SQL 이장에서는저장프로시저와저장함수에서동적 SQL 을사용하는방법을설명한다. 동적 SQL 149

162 동적 SQL 의개요 동적 SQL(Dynamic SQL) 은실행시간에사용자가원하는질의를만들어서실행하는것이다. 일반적인저장프로시저의 SQL 실행방법은정적인 (Static) 방법으로, 저장프로시저가컴파일될때이미 SQL 문의실행계획이생성된다. 저장프로시저를실행할때결정되는 SQL 구문은동적 SQL 을사용해야한다. 동적 SQL 의실행 아래다이어그램은정적 SQL 과동적 SQL 이포함된저장프로시저를생성하고실행할때알티베이스내부에서이를수행하는과정을비교하고있다. 150 Stored Procedures Manual

163 [ 그림 8-1] 정적 SQL 과동적 SQL 의실행과정비교 [ 그림 8-1] 의왼쪽저장프로시저는 DELETE FROM T1 문을정적으로처리한것이고, 오른쪽저장프로시저는동일한 DELETE 문을 EXECUTE IMMEDIATE 를사용하여동적으로처리한것이다. 전자는프로시저생성 ( 컴파일 ) 시에이미 DELETE 문에대한실행계획이만들어지고, 실행시에 DELETE 문을수행 (EXECUTE) 한다. 하지만, 후자는프로시저생성 ( 컴파일 ) 시에는실행계획을만들지않고프로시저를실행할때 DELETE 문에대해실행계획을생성하고수행한다. 수행시에실행계획을생성하고수행하기때문에, 수행시에 SQL 문장이다른문장으로바뀌어도된다. 특징 동적 SQL 151