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