ALTIBASE HDB Application Development Stored Procedures Manual Release 6.3.1 (November 30, 2017)
----------------------------------------------------------- ALTIBASE Application Development Stored Procedures Manual Release 6.3.1 Copyright c 2001~2016 ALTIBASE Corp. All Rights Reserved. 본문서의저작권은 알티베이스에있습니다. 이문서에대하여당사의동의없이무단으로복제또는전용할수없습니다. 알티베이스 152-790 서울시구로구구로동 182-13 대륭포스트타워 Ⅱ 10 층전화 : 02-2082-1114 팩스 : 02-2082-1099 고객서비스포털 : http://support.altibase.com homepage: http://www.altibase.com -----------------------------------------------------------
목차 서문 7 이매뉴얼에대하여 8 1. 저장프로시저 13 저장프로시저의개요 14 저장프로시저의구조 17 저장프로시저사용시주의사항 18 2. 저장프로시저 SQL 문 19 개요 20 CREATE PROCEDURE 23 ALTER PROCEDURE 29 DROP PROCEDURE 31 EXECUTE 32 CREATE FUNCTION 34 ALTER FUNCTION 38 DROP FUNCTION 39 3. 저장프로시저블록 41 저장프로시저블록 42 지역변수선언 45 SELECT INTO 52 RETURNING INTO 절 58 할당문 65 LABEL 68 PRINT 71 RETURN 74 목차 3
4. 흐름제어 77 개요 78 IF 79 CASE 84 LOOP 88 WHILE LOOP 90 FOR LOOP 92 EXIT 97 CONTINUE 100 GOTO 102 NULL 105 5. 커서 107 커서의개요 108 CURSOR 110 OPEN 113 FETCH 116 CLOSE 119 Cursor FOR LOOP 120 커서속성 123 6. 사용자정의타입 129 개요 130 사용자정의타입의정의 132 Associative Array 관련함수 135 RECORD 타입변수및 Associative Array변수의사용 139 REF CURSOR 142 7. 타입세트 147 개요 148 CREATE TYPESET 151 DROP TYPESET 154 8. 동적 SQL 157 동적 SQL의개요 158 EXECUTE IMMEDIATE 160 4 Stored Procedures Manual
OPEN FOR 163 9. 예외처리 165 개요 166 EXCEPTION 169 RAISE 170 RAISE_APPLICATION_ERROR 172 사용자정의예외 173 SQLCODE와 SQLERRM 176 Exception Handler 178 10. 저장패키지 183 개요 184 CREATE PACKAGE 187 CREATE PACKAGE BODY 189 ALTER PACKAGE 193 DROP PACKAGE 194 EXECUTE 195 11. 내장함수와저장프로시저 197 파일제어 198 DataPort 221 DBMS Stats 239 그외함수들 249 A. 부록 : 예제 253 저장프로시저예제 254 파일제어예제 262 찾아보기 265 목차 5
서문 서문 7
이매뉴얼에대하여 이매뉴얼은저장프로시저의개념및사용방법에대해설명한다. 대상사용자 이매뉴얼은다음과같은 ALTIBASE HDB 사용자를대상으로작성되었다. 데이터베이스관리자 성능관리자 데이터베이스사용자 응용프로그램개발자다음과같은배경지식을가지고이매뉴얼을읽는것이좋다. 컴퓨터, 운영체제및운영체제유틸리티운용에필요한기본지식 관계형데이터베이스사용경험또는데이터베이스개념에대한이해 컴퓨터프로그래밍경험 소프트웨어환경 이매뉴얼은데이터베이스서버로 ALTIBASE HDB 버전 6.3.1 을 사용한다는가정하에작성되었다. 이매뉴얼의구성 이매뉴얼은다음과같이구성되어있다. 제 1 장저장프로시저이장은저장프로시저의개념및구조, 사용시주의사항에대해설명한다. 제 2 장저장프로시저 SQL 문이장은저장프로시저 SQL 문에대한사용방법에대해설명한다. 제 3 장저장프로시저블록이장은저장프로시저블록의개념, 저장프로시저바디내에서선언하는지역변수및사용가능한문장에대해설명한다. 8 Stored Procedures Manual
제 4 장흐름제어이장은저장프로시저바디내에서절차적프로그램작성이가능하도록프로그램흐름을제어할수있는흐름제어문에대해설명한다. 제 5 장커서이장은저장프로시저내에서조회레코드건수가여러개인 SELECT 문을처리할수있도록커서를정의하고레코드를제어할수있는커서관련문들에대해설명한다. 제 6 장사용자정의타입이장은저장프로시저내에서사용자정의타입인 record 및 associative array 의정의및사용방법에대해설명한다. 제 7 장타입세트이장은사용자정의타입의집합인타입세트의정의및사용방법에대해설명한다. 제 8 장동적 SQL 이장은실행시간에사용자가원하는질의를만들어서실행하기위한동적 SQL 에대해설명한다. 제 9 장예외처리이장은저장프로시저실행중오류발생시저장프로시저내에서오류에대한예외처리가가능하도록하는예외처리관련문에대해설명한다. 제 10 장저장패키지이장은패키지를생성하고사용하는방법을설명한다. 제 11 장내장함수와저장프로시저 ALTIBASE HDB 는다양한종류의내장된저장프로시저와함수를제공한다. 저장프로시저내에서의파일제어함수와 DataPort 로통칭되는이전 (migration) 관련저장프로시저가그것이다. 이장은이들저장프로시저와함수를소개하고그사용법에대해설명한다. A. 부록이장은이매뉴얼의예제에서사용한스키마에대한설명과저장프로시저를이용한예제프로그램을설명한다. 문서화규칙 이절에서는이매뉴얼에서사용하는규칙에대해설명한다. 이 규칙을이해하면이매뉴얼과설명서세트의다른매뉴얼에서정보를 쉽게찾을수있다. 서문 9
여기서설명하는규칙은다음과같다. 구문다이어그램 샘플코드규칙 구문다이어그램 이매뉴얼에서는다음구성요소로구축된다이어그램을사용하여, 명령문의구문을설명한다. 구성요소 예약어 의미명령문이시작한다. 완전한명령문이아닌구문요소는화살표로시작한다. 명령문이다음라인에계속된다. 완전한명령문이아닌구문요소는이기호로종료한다. 명령문이이전라인으로부터계속된다. 완전한명령문이아닌구문요소는이기호로시작한다. ; 명령문이종료한다. SELECT 필수항목 선택적항목 NOT ADD 선택사항이있는필수항목. 한항목만제공해야한다. DROP 선택사항이있는선택적항목. ASC DESC ASC 선택적항목. 여러항목이허용된다. 각반복앞부분에 콤마가와야한다. DESC, 샘플코드규칙 코드예제는 SQL, Stored Procedure, isql, 또는다른명령라인 구문들을예를들어설명한다. 10 Stored Procedures Manual
아래테이블은코드예제에서사용된인쇄규칙에대해설명한다. 규칙 의미 예제 [ ] 선택항목을표시 VARCHAR [(size)] [[FIXED ] VARIABLE] { } 필수항목표시. 반드시하나이상을선택해야되는표시 { ENABLE DISABLE COMPILE } 선택또는필수항목표시의인자구분표시 { ENABLE DISABLE COMPILE } [ ENABLE DISABLE COMPILE ]... 그이전인자의반복표시예제코드들의생략되는것을표시 SQL> SELECT ename FROM employee; ENAME ------------------------ SWNO HJNO HSCHOI... 20 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 테이블같은샘플테이블에기반하여작성되었다. 이들 서문 11
테이블은 $ALTIBASE_HOME/sample/APRE/schema 디렉터리의 schema.sql 을사용하여생성할수있다. 샘플스키마에대한온전한 정보는 General Reference 를참고하기바란다. 관련자료 자세한정보를위하여다음문서목록을참조하기바란다. Installation Guide Getting Started Guide SQL Reference isql User s Manual Error Message Reference 온라인매뉴얼 Altibase 고객서비스포털 (http://support.altibase.com) 에서국문및 영문매뉴얼 (PDF, HTML) 을받을수있다. Altibase 는여러분의의견을환영합니다. 이매뉴얼에대한여러분의의견을보내주시기바랍니다. 사용자의의견은다음버전의매뉴얼을작성하는데많은도움이됩니다. 보내실때에는아래내용과함께고객서비스포털 (http://support.altibase.com/kr/) 로보내주시기바랍니다. 사용중인매뉴얼의이름과버전 매뉴얼에대한의견 사용자의성함, 주소, 전화번호이외에도 Altibase 기술지원설명서의오류와누락된부분및기타기술적인문제들에대해서이주소로보내주시면정성껏처리하겠습니다. 또한, 기술적인부분과관련하여즉각적인도움이필요한경우에도고객서비스포털을통해서비스를요청하시기바랍니다. 여러분의의견에항상감사드립니다. 12 Stored Procedures Manual
1. 저장프로시저 저장프로시저 13
저장프로시저의개요 저장프로시저 (Stored Procedure) 란 SQL 문들과흐름제어문, 할당문, 오류처리루틴등으로구성된데이터베이스객체 (object) 중의하나이다. 저장프로시저는생성될때컴파일되어바로실행가능한상태로데이터베이스에저장되며여러세션에서동시에하나의저장프로시저를실행하는것도가능하다 저장프로시저 (Stored Procedure) 라는용어는때때로저장프로시저와저장함수 (Stored Function) 을모두지칭하기도한다. 저장프로시저와저장함수는저장함수가실행시값을반환하는것외에는차이가없다. 저장프로시저와저장함수는각각 CREATE PROCEDURE 와 CREATE FUNCTION 구문을사용해서생성할수있다. 이구문에대한자세한설명은 2 장 저장프로시저 SQL 문 을참고하기바란다. 저장프로시저의종류 저장프로시저 저장프로시저는 SQL 구문이나다른저장프로시저내에서입력인자, 출력인자, 입출력인자를가지고실행할수있다. 저장프로시저호출시, 프로시저의바디부분에정의된절차에따라서 SQL 문을수행하게된다. 저장프로시저는반환값을가지지않지만, 출력인자와입출력인자들을통해프로시저를호출한클라이언트에게값을전달할수도있다. 이는반환값을갖지않기때문에 SQL 문의연산식 (expression) 내에서피연산자로사용될수없다. 저장함수 값을반환하는것만제외하면저장프로시저와동일하다. 저장프로시저와달리하나의반환값을가지므로 SQL 문의연산식 (expression) 내에서피연산자로사용할수있다. 타입세트 저장프로시저의사용자정의타입들을정의한집합이다. 이는주로저장프로시저끼리인자또는리턴값으로사용자정의타입을주고받을때사용한다. 자세한내용은 7 장 타입세트 에서다룬다. 14 Stored Procedures Manual
저장프로시저의특징 SQL 구문을이용한절차적프로그램 ALTIBASE HDB PSM (Persistent Stored Module) 은흐름제어문과예외처리문을제공하므로 SQL 문을사용해서절차적프로그래밍이가능하다. 성능 여러 SQL 문을순차적으로수행하는클라이언트프로그램의경우에는각 SQL 문수행시마다데이터베이스서버와통신을해야하므로통신비용이많이발생한다. 반면, 저장프로시저로작성된프로그램은프로시저호출시한번의통신만으로여러 SQL 문을수행할수있다. 따라서, 저장프로시저를사용하면통신부하의감소와함께데이터베이스서버와클라이언트응용프로그램간의데이터타입의차이로인해발생하는내부적인데이터타입변환의부하도줄일수있다. 모듈화 소스코드관리의용이성 업무절차를구현하는데필요한모든 SQL 작업을하나의저장 프로시저로묶어모듈화하여관리할수있다. 저장프로시저는데이터베이스서버에저장되는모듈이기때문에, 업무로직의변경시여러클라이언트에설치된프로그램들을모두수정할필요없이저장프로시저만변경하면되므로프로그램관리가용이하다. 공유와생산성 한사용자가생성한저장프로시저는데이터베이스에저장되므로접근권한이부여된다른사용자도해당저장프로시저를실행할수있어서로공유할수있을뿐만아니라, 한저장프로시저내에서다른저장프로시저의호출이가능하므로같은업무절차의재프로그래밍이필요없으므로생산성을높일수있다. 저장프로시저 15
SQL 과의통합성 저장프로시저내의흐름제어문의조건절은 SELECT 문의조건절을그대로사용할수있다. 즉, C/C++ 등의주언어의흐름제어문의조건절에서는사용할수없는 SQL 문스타일의기능을사용할수있다. 또한, 연산식에부질의 (subquery) 를사용하거나 SQL 문이지원하는시스템제공함수들을그대로사용할수있다는점등 SQL 문과밀착된프로그래밍이가능하다. 에러및예외처리 저장프로시저내에서 Exception Handler 를제공하므로 SQL 문 수행도중오류가발생했을때적절한대응조치를서버내에서바로 처리할수있다. 저장성 저장프로시저또한데이터베이스객체이기때문에사용자가 삭제하기전까지데이터베이스내에저장된다. 따라서업무절차 또한데이터베이스에저장하여보존시킬수있다. 16 Stored Procedures Manual
저장프로시저의구조 저장프로시저는블록으로구조화된언어로, 저장프로시저의바디는여러개의논리적인블록들로구성된다. 저장프로시저는크게헤더와바디로나뉘어진다. 저장프로시저의바디는하나의큰블록으로서선언부, 프로시저의실제바디, 예외처리부로구성된다. 바디는다시여러개의하위블록들을가질수있다. 저장프로시저헤더저장프로시저바디 ( 블록1) 저장프로시저구조를예를들어설명하면다음과같다. CREATE FUNCTION mycheck (id IN INTEGER,... ) RETURN INTEGER AS v_name CHAR(20); v_salary INTEGER; comm_missing EXCEPTION; CURSOR...... IF id > 10000 THEN... DECLARE......... END IF; RAISE comm_missing;... RETURN v_salary;... EXCEPTION... 블록 2 WHEN comm_missing THEN...... 블록 1 선언부 블록 1 바디 블록 1 예외처리부 블록 2 는블록 1 의하위블록으로블록 1 의구조와같이선언부, 바디, 예외처리부로구성될수도있다. 흐름제어문도명시적인시작과끝을알수있는하나의블록이다. 저장프로시저 17
저장프로시저사용시주의사항 트랜잭션관리 저장프로시저내에서사용가능한트랜잭션제어문은 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 된상태에서 COMMIT 을한번도하지않고 ROLLBACK 을실행하면커서가닫히므로주의해야한다. SELECT 문내에서호출되는저장함수의경우저장함수내에 INSERT, UPDATE, DELETE 문은사용할수없으며, 트랜잭션제어문도수행할수없다. INSERT, UPDATE, DELETE 문내에서호출되는저장함수내에서도트랜잭션제어문을수행할수없다. 관련메타테이블 저장프로시저관련메타테이블에대한자세한내용은 General Reference 의데이터딕셔너리부분을참조한다. 18 Stored Procedures Manual
2. 저장프로시저 SQL 문 저장프로시저 SQL 문 19
개요 저장프로시저 SQL 문 아래표는저장프로시저, 함수와타입세트를생성하고관리하는데 사용하는 DDL 문을보여준다. CREATE TYPESET 과 DROP TYPESET 구문에대한설명은 7 장 타입세트를참고하기바란다. 종류 관련문장 설명 생성 CREATE [OR REPLACE] PROCEDURE 문 새로운저장프로시저를생성하거나이미생성된저장프로시저의정의를변경하는문장이다. CREATE [OR REPLACE] FUNCTION 문 새로운저장함수를생성하거나이미생성된저장함수의정의를변경하는문장이다. CREATE [OR REPLACE] TYPESET 타입세트를생성또는변경하는문장이다. 문 변경 ALTER PROCEDURE 문 이구문은저장프로시저를재컴파일하여프로시저를유효한상태로변경하고자할때사용한다. ALTER FUNCTION 문 ALTER PROCEDURE문과동일하다. 삭제 DROP PROCEDURE 문 생성된저장프로시저를삭제하는문장이다. DROP FUNCTION 문 생성된저장함수를삭제하는문장이다. DROP TYPESET 문 생성된타입세트를삭제하는문장이다. 실행 EXECUTE 문 저장프로시저또는저장함수를실행하는문장이다. function_name SQL문내에서호출할때이름으로참조할수있다. 20 Stored Procedures Manual
데이터타입 저장프로시저에서는다음과같은데이터타입을지원한다. SQL 데이터타입 BOOLEAN 타입 FILE_TYPE 저장프로시저내에서만사용가능하며, 파일제어를위한타입이다. 자세한내용은 11 장의 파일제어 를참조한다. 사용자정의타입저장프로시저내에서만사용가능하며, RECORD 및 ASSOCIATIVE ARRAY 를지원한다. 자세한내용은 6 장 사용자정의타입 을참조한다. SQL 데이터타입 SQL 구문에서사용가능한데이터타입모두 PSM ( 저장프로시저, 저장함수 ) 에서사용할수있다. 각데이터타입에대한자세한 내용은 General Reference 의 " 데이터타입 " 장을참조한다. 아래표에나열한 SQL 데이터타입은 SQL 과 PSM 에서사용가능한 최대크기가다르다. 데이터타입 SQL에서최대크기 PSM에서최대크기 CHAR(M) 32000 65534 VARCHAR(M) 32000 65534 NCHAR(M) 16000 (UTF-16) 10666 (UTF-8) 32766 (UTF-16) 21843 (UTF-8) NVARCHAR(M) 16000 (UTF-16) 10666 (UTF-8) 32766 (UTF-16) 21843 (UTF-8) BLOB 2GB - 1 100MB LOB_OBJECT_BUFFER _SIZE 프로퍼티에의해결정 ( 기본값 : 32KB) CLOB 2GB - 1 100MB LOB_OBJECT_BUFFER _SIZE 프로퍼티에의해결정 ( 기본값 : 32KB) CHAR, VARCHAR, NCHAR, NVARCHAR 타입의크기를지정하지 않으면크기는 1 이다. 저장프로시저 SQL 문 21
BOOLEAN 타입 BOOLEAN 타입은저장프로시저또는저장함수에서만사용 가능하며, TRUE 또는 FALSE, NULL 값만가질수있다. BOOLEAN 변수는다음과같이선언할수있다. variable_name BOOLEAN; BOOLEAN 타입은 SQL 데이터타입중어느것과도호환되지 않으므로사용시아래와같은제약이있다. 테이블칼럼에 BOOLEAN 값을입력할수없다. 테이블칼럼의값을 BOOLEAN 변수에 fetch 할수없다. BOOLEAN 타입을반환하는저장함수나내장함수를 SQL 문에 사용할수없다. BOOLEAN 값은출력함수 (PRINT, PUT 등 ) 의인자로전달할수 없다. BOOLEAN 타입은아래와같이사용할수있다. done BOOLEAN;... done := TRUE; done := FALSE; done := NULL;... IF done = TRUE THEN... IF done = FALSE THEN... IF done THEN... IF done is NULL THEN... 22 Stored Procedures Manual
CREATE PROCEDURE 구문 기능 저장프로시저를새로생성하거나이미생성되어있는저장 프로시저를새로운저장프로시저로변경하는기능을수행한다. 저장프로시저 SQL 문 23
parameter_declaration 인자는생략할수있으며, 인자를명시할경우엔인자의명칭, 데이터타입및입출력구분을명시해야한다. 사용가능한입출력구분값은다음세가지중의하나이고생략시에 IN 이기본값이된다. 인자가 OUT 또는 INOUT 인경우에는 DEFAULT expression 은정의할수없다. IN: 프로시저호출시입력값으로주어지는입력인자 OUT: 프로시저실행후출력값을반환하는인자 INOUT: 입출력공용인자로프로시저호출시입력값을줄수있고, 실행후에출력값을반환할수있다저장프로시저가실행될때, IN 인자를사용해서프로시저에값을전달하고, 프로시저는 OUT 인자를사용해서호출한루틴에값을반환한다. IN 인자는저장프로시저내에서상수처럼동작하므로, 프로시저내에서할당문을사용해인자에값을대입할수없으며 SELECT 문의 INTO 절에도사용할수없다. 인자는기본값을가질수있다. 저장프로시저가호출될때기본값이정의된인자에값을넘겨주지않을경우, 기본값이사용된다. declaration_section 3 장의 지역변수선언 절참고 data_type 3 장의 지역변수선언 절참고 Exception Handler 9 장 Exception Handler 참고 CREATE PROCEDURE 문의실행 저장프로시저생성구문은텍스트편집기에서작성해서 isql 에붙여넣거나, isql 에서직접한라인씩입력할수도있다. 각 SQL 문, 저장프로시저제어문, 그리고블록 (END) 의끝에세미콜론 ( ; ) 을입력한다. 24 Stored Procedures Manual
isql 에서 CREATE PROCEDURE 문을실행할때는마지막 문의다음라인에반드시슬래시 ( / ) 를입력해야프로시저생성문이실행된다. 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); / isql> EXEC proc1 (2,2,2); Execute success. isql> SELECT * FROM t1; T1.I1 T1.I2 T1.I3 ---------------------------------------- 저장프로시저 SQL 문 25
26 Stored Procedures Manual 1 1 1 3 3 3 4 4 4 5 5 5 2 7 2 12 12 12 6 rows selected. 예제 2 ( 기본값이있는인자사용 ) 예제 3 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.I3 ---------------------------------------- 1 1 1 2 1 1 3 3 1 4 4 4 4 rows selected. CREATE OR REPLACE PROCEDURE proc1 (emp_id INTEGER, amount NUMBER(10,2)) AS UPDATE employees SET salary = salary + amount WHERE eno = emp_id; / isql> EXEC proc1(15, '250'); Execute success. isql> SELECT * FROM employees WHERE eno=15; ENO E_LASTNAME E_FIRSTNAME EMP_JOB -------------------------------------------------------- ---------------------- EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS -------------------------------------------------------- ---------------------------
예제 4 ( 출력, 입출력인자사용 ) 예제 5 15 Davenport Jason webmaster 0119556884 1003 1250 M 901212 H 1 row selected. 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; Execute success. isql> EXEC proc1(:t3, :t4); Execute success. isql> PRINT t3; NAME TYPE VALUE ----------------------------------------------- T3 INTEGER 5 CREATE OR REPLACE PROCEDURE proc1(p1 INTEGER, p2 IN OUT INTEGER, p3 OUT INTEGER) AS p2 := p1; p3 := p1 + 100; / 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 저장프로시저 SQL 문 27
예제 6 ( 입출력인자사용 ) 예제 7 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 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 CREATE OR REPLACE PROCEDURE proc1(p1 INTEGER, p2 IN OUT INTEGER, p3 OUT INTEGER) AS p2 := p1 + p2; p3 := p1 + 100; / 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 103 28 Stored Procedures Manual
ALTER PROCEDURE 구문 alter_procedure_statement ::= ALTER PROCEDURE procedure_name user_name. COMPILE ; 기능 저장프로시저생성이후에이프로시저내에서참조하는테이블, 시퀀스등의데이터베이스오브젝트혹은이저장프로시저가호출하는다른저장프로시저, 저장함수등의정의가변경되어서, 현재이저장프로시저의실행계획으로는이를실행할수없는경우에이저장프로시저는무효한 (invalid) 상태라고한다. 예를들면처음저장프로시저생성시존재하던인덱스가삭제된경우이전실행계획은인덱스를통해테이블에접근하도록계획되어있으므로이전의실행계획을이용해서테이블에접근할수없게된다. 무효한상태의프로시저가호출되면, ALTIBASE HDB 서버는바로자동으로이를재컴파일한다. 그러나런타임시에컴파일하는것은심각한성능이슈를불러올수있으므로, 프로시저가무효한상태가되었을때수동으로컴파일하는것이좋다. ALTER PROCEDURE 문은사용자가명시적으로저장프로시저를컴파일때사용된다. 예제 예제 1 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) 저장프로시저 SQL 문 29
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.I3 ----------------------------------------------- 1 seoul 20-JUN-2002 1 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.I3 ----------------------------------------------- 2 incheon 28-DEC-2010 1 row selected. 30 Stored Procedures Manual
DROP PROCEDURE 구문 drop_procedure_statement ::= DROP PROCEDURE procedure_name user_name. ; 기능 데이터베이스에서저장프로시저를삭제하는구문이다. 삭제하고자하는프로시저를다른저장프로시저에서참조하고있다하더라도, 그프로시저는삭제된다. 삭제되고없는저장프로시저나함수가호출될때 ALTIBASE HDB 는오류코드를반환한다. 예제 DROP PROCEDURE proc1; 저장프로시저 SQL 문 31
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 employees WHERE eno = eid; UPDATE employees SET salary = salary + amount WHERE eno = eid; / isql> SELECT * FROM employees WHERE eno = 15; ENO E_LASTNAME E_FIRSTNAME EMP_JOB 32 Stored Procedures Manual
-------------------------------------------------------- ---------------------- EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS -------------------------------------------------------- --------------------------- 15 Davenport Jason webmaster 0119556884 1003 501000 M 901212 H 1 row selected. isql> EXEC proc1(15, 333333); Execute success. isql> SELECT * FROM employees WHERE eno = 15; ENO E_LASTNAME E_FIRSTNAME EMP_JOB -------------------------------------------------------- ---------------------- EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS -------------------------------------------------------- --------------------------- 15 Davenport Jason webmaster 0119556884 1003 834333 M 901212 H 1 row selected. 저장프로시저 SQL 문 33
CREATE FUNCTION 구문 create_function::= CREATE OR FUNCTION REPLACE user_name. function_name ( parameter_declaration ), RETURN data_type AS DETERMINISTIC IS declaration_section statement END EXCEPTION exception_handler function_name parameter_declaration ::= parameter_name IN OUT data_type IN OUT DEFAULT := expression 기능 저장함수를새로생성하거나이미생성되어있는저장함수를 대체한다. 34 Stored Procedures Manual
parameter_declaration 저장프로시저와마찬가지로, 저장함수의인자도 IN, IN/OUT, OUT 인자로정의할수있다. 인자타입과주의사항에대한설명은 CREATE PROCEDURE 구문설명의 parameter_declaration 을참고하기바란다. RETURN data_type DETERMINISTIC 저장함수는저장프로시저와달리실행후하나의값을반환한다. 그러므로반드시반환데이터타입을명시해야한다. 동일한파라미터값으로함수를호출하면, 항상같은결과가반환됨을표시한다. DETERMINISTIC 으로선언한함수는 Check Constraint 와함수기반인덱스 (Function-Based Index) 에서사용할수있다. 이옵션을생략하면, non-deterministic 함수로선언된다. Declaration Section 3 장의 지역변수선언 절참고 Data Types 3 장의 지역변수선언 절참고 Exception Handler 9 장 Exception Handler 참고 CREATE FUNCTION 문의실행 저장함수생성구문의실행은저장프로시저구문과동일하다. CREATE PROCEDURE 절의 CREATE PROCEDURE 문의실행 을 참고하기바란다. 예제 CREATE TABLE t1( seq_no INTEGER, user_id VARCHAR(9), rate NUMBER, 저장프로시저 SQL 문 35
start_date DATE, end_date DATE); INSERT INTO t1 VALUES(0, '000000500', 200.50, '23-May- 2002', '23-Apr-2002'); INSERT INTO t1 VALUES(0, '000000501', 190, '23-Nov-2002', '23-Dec-2002'); INSERT INTO t1 VALUES(0, '000000523', 100, '12-Dec-2001', '12-Jan-2001'); INSERT INTO t1 VALUES(0, '000000532', 100, '11-Dec-2001', '11-Jan-2002'); INSERT INTO t1(seq_no, user_id, start_date, end_date) VALUES(0, '000000524', '30-Oct-2001', '30-Nov-2001'); INSERT INTO t1 VALUES(0, '000000524', 200.50, '30-Apr- 2002', '30-May-2002'); INSERT INTO t1 VALUES(0, '000000524', 200.50, '30-Apr- 2002', '30-May-2002'); INSERT INTO t1 VALUES(1, '000000524', 100, '30-Apr-2002', '30-May-2002'); INSERT INTO t1 VALUES(1, '000000524', 115.0, '19-Jan- 2002', '19-Mar-2002'); INSERT INTO t1 VALUES(0, '000000502', 120.0, '27-Jan- 2002', '27-Feb-2002'); INSERT INTO t1 VALUES(1, '000000504', 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 ----------------------- 0 000000500 200.5 2002/05/23 00:00:00 2002/04/23 00:00:00 0 000000501 190 2002/11/23 00:00:00 2002/12/23 00:00:00 0 000000523 100 2001/12/12 00:00:00 2001/01/12 00:00:00 0 000000532 100 2001/12/11 00:00:00 2002/01/11 00:00:00 0 000000524 2001/10/30 00:00:00 2001/11/30 00:00:00 0 000000524 200.5 2002/04/30 00:00:00 2002/05/30 00:00:00 0 000000524 200.5 2002/04/30 00:00:00 2002/05/30 00:00:00 1 000000524 100 2002/04/30 00:00:00 2002/05/30 00:00:00 1 000000524 115 2002/01/19 00:00:00 2002/03/19 00:00:00 0 000000502 120 2002/01/27 00:00:00 2002/02/27 00:00:00 1 000000504 150 2001/11/26 00:00:00 2001/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; 36 Stored Procedures Manual
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 주의사항 제약조건 (constraint) 또는함수기반인덱스 (Function-based Index) 가사용하는함수의경우, 함수의반환값이바뀌면안되기때문에함수를재정의하는것자체가불가능하다. 또한함수기반인덱스가기반하는함수내에서호출되는함수를변경하거나제거하면, 함수기반인덱스가생성되어있는테이블에대한 DML 이실패할수있으므로사용자의주의가필요하다. 저장프로시저 SQL 문 37
ALTER FUNCTION 구문 alter_function_statement ::= ALTER FUNCTION funtion_name user_name. COMPILE ; 기능 저장프로시저와마찬가지로, 저장함수생성후에함수내에서참조하는데이터베이스객체의정의가변경되어현재이저장함수의실행계획으로는더이상실행할수없는경우에이저장함수는무효한상태라고한다. ALTER FUNCTION 문은저장함수를명시적으로재컴파일하여유효한상태의실행계획을다시생성하기위해사용된다. 더자세한설명은 ALTER PROCECURE 절을참고한다. 예제 ALTER FUNCTION get_dept_name COMPILE; 38 Stored Procedures Manual
DROP FUNCTION 구문 drop_function_statement ::= DROP FUNCTION function_name user_name. ; 기능 저장함수를삭제하는구문이다. 삭제하고자하는저장함수를다른저장프로시저또는저자함수에서참조하고있다하더라도, 그저장함수는삭제된다. 이미삭제된저장함수를참조하고있던임의의저장프로시저또는저장함수가실행될때 ALTIBASE HDB 는오류를출력한다. 예제 DROP FUNCTION get_dept_name; 주의사항 제약조건 (constraints) 또는함수기반인덱스가참조하는함수는 삭제가불가능하다. 저장프로시저 SQL 문 39
3. 저장프로시저블록 저장프로시저와저장함수는한개이상의블록으로구성된다. 이 장에서는블록을사용해서저장프로시저내에절차화된프로그램을 작성하는방법을설명한다. 저장프로시저블록 41
저장프로시저블록 구문 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 42 Stored Procedures Manual
sql_statement ::= select_into_statement insert_statement delete_statement update_statement move_statement merge_statement commit_statement rollback_statement execute_imme_statement enqueue_statement dequeue_statement 블록은크게선언부 (Declare Section), 블록바디 (Block Body), 예외처리부 (Exception Handler Section) 의세부분으로나뉘어진다. DECLARE,, EXCEPTION 등의키워드뒤에는세미콜론을사용하지않지만 END 및기타모든프로시저내의명령문뒤에는세미콜론이있어야한다. 저장프로시저의코드에주석처리를할수있다. 단일행주석에는 -- 를문장앞에붙이고, 여러행을주석처리할경우는 /* 와 */ 사이에주석처리할문장이놓이도록작성한다. 이장에서는선언부와블록바디에사용할수있는구문중 SELECT INTO 문, 변수할당문, LABEL 문, PRINT 문, RETURN 문에대해서설명한다. 저장프로시저내에서사용가능한흐름제어문, 커서관련문, 예외처리에관련된내용은다음장에서순서대로설명하고있다. 그외 SQL 문들에대한자세한내용은 SQL Reference 을참조한다. 선언부 선언부는메인블록에서는 AS 와 키워드사이에위치하며, 하위블록에서는 DECLARE 와 키워드사이에위치한다. 해당블록내에서사용하는지역변수, 커서와사용자정의예외등을선언한다. 저장프로시저블록 43
이장에서는지역변수만을설명한다. 커서와예외처리는 5 장 커서와 9 장예외처리에서각각설명한다. 블록바디 과 END 사이의부분으로 SQL 문과흐름제어문을포함한다. 블록바디내에기술가능한 SQL 문과흐름제어문은다음과같다. DML 문 : SELECT/INSERT/DELETE/UPDATE/MOVE/MERGE/ENQUEUE/D EQUEUE 트랜잭션처리문 : 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 사이의부분으로저장프로시저또는함수실행 중에오류가발생했을때처리할루틴을기술한다. 44 Stored Procedures Manual
지역변수선언 구문 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 저장프로시저블록 45
기능 variable_name 변수의이름을명시할때사용된다. 변수의이름은하나의블록범위내에서유일해야한다. 칼럼과같은이름을가지는변수를 SQL 문장내에서사용할경우, 이것은칼럼명으로인식된다. 다음의예에서 eno 는칼럼명으로 인식되어 employees 테이블의모든레코드가삭제된다. DECLARE eno INTEGER := 100; DELETE FROM employees WHERE eno = eno; 다음과같은방법으로모호성을없앨수있다. <<del_block>> DECLARE eno INTEGER := 100; DELETE FROM employees WHERE eno = del_block.eno; 블록이름에관한설명은이장의 LABEL 절을참고한다. 46 Stored Procedures Manual
data_type 변수의데이터타입을명시한다. 저장프로시저내에서사용가능한데이터타입은아래와같다. SQL 문에서사용할수데이터타입 : 2 장의 " 데이터타입 " 참조 BOOLEAN 타입 : 2 장의 " 데이터타입 " 참조 %TYPE 속성을사용해서이미데이터타입이지정된칼럼이나변수와같은타입사용 %ROWTYPE 속성을사용해서여러개의칼럼이모인레코드타입을정의 사용자정의타입 : "6 장사용자정의타입 " 참조 %TYPE 과 %ROWTYPE 속성은테이블정의가변경될때마다저장프로시저내에서코드를변경해야하는번거로움을예방한다. 즉, 칼럼의데이터타입이변경될때, %TYPE 속성을사용해서정의된변수는자동으로변경된타입에맞추어진다. 이는데이터의독립성을실현하고유지보수비용을낮추는데기여한다. CONSTANT 이옵션은특정변수를값을할당할수없는상수로사용하고자하는경우에사용할수있다. 이렇게정의된변수는읽기전용변수로만사용할수있다. 다음과같이 max_val 을선언하면 max_val 에는임의의값을할당할수없고선언시할당한 100 의값을가지는상수처럼취급된다. max_val CONSTANT integer := 100; DEFAULT Cursor Declaration 다음과같이변수선언시초기값을설정할때사용된다. curr_val INTEGER DEFAULT 100; count_val INTEGER := 0; 5 장의 CURSOR 절참고 Exception Declaration 9 장의 EXCEPTION 절참고 저장프로시저블록 47
중첩블록및변수의범위 선언부에명시한변수들의영향력은자신이선언된 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; x 의범위... v_result¹ := 1;... /* start of sub-block (block2) */ DECLARE v_result² integer; y number; v_result¹ 값을참조할수있는영역 y 의범위... v_result² := 2;... /* end of block2 */... v_result¹ := 3;... /* end of block1 */ v_result² 값을참조할수있는영역 v_result¹ 값을참조할수있는영역 48 Stored Procedures Manual
제약조건 다음과같은기능은변수선언부에서지원하지않는기능들이다. 변수에 NOT NULL 속성을지정할수없다. 여러개의변수들을한꺼번에선언할수없다. 즉다음과같이선언할수없다. i, j, k INTEGER; 예제 %TYPE 사용 DECLARE my_title books.title%type; my_title 은 books 테이블의 title 칼럼과같은데이터타입을가지는 변수로정의된다. %ROWTYPE 사용 DECLARE dept_rec departments%rowtype dept_rec 은레코드타입의변수로서 departments 테이블또는 departments 라는이름의커서와동일한레코드타입을가지게된다. 예제 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 --------------------------- 1 1 1 row selected. 저장프로시저블록 49
--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.I3 ---------------------------------------- 1 1 1 3 3 3 1 1 1 3 rows selected. 예제 2 %ROWTYPE 속성을사용하는예제이다. CREATE TABLE emp( eno INTEGER, ename CHAR(10), emp_job CHAR(15), join_date DATE, salary NUMBER(10,2), 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', 30000000, BYTE'D001'); 50 Stored Procedures Manual
INSERT INTO emp VALUES (20, 'SWMYUNG', 'MANAGER', '01-Nov-1999', 50000000, 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 ----------------------------------------------- 10 DKLEE ENGINEER 2000/07/01 00:00:00 2005/01/27 16:26:26 30000000 D001 0 1 row selected. 저장프로시저블록 51
SELECT INTO 구문 select_list 와 rest_of_select_statement 는 SELECT 구문의문법과동일하므로 SQL Reference 을참고한다. 기능 저장프로시저에서 SELECT 문을사용할경우, SELECT 문은 INTO 절을포함해야한다. 저장프로시저또는함수내에서 INTO 절을가지는 SELECT 문은 52 Stored Procedures Manual
하나의레코드만검색할수있다. INTO 절이사용된 SELECT 구문이여러행을반환하거나한행도반환하지않으면오류가발생한다. SELECT 절의 select_list 와 INTO 절의상응하는 variable_name 은개수가동일해야하며호환가능한데이터타입이어야한다. %ROWTYPE 속성으로정의한변수를 INTO 절에사용하는경우에도 %ROWTYPE 변수내의칼럼의개수와 select_list 의칼럼의개수가동일해야하며, 상응하는칼럼의데이터타입은호환가능해야한다. 저장프로시저는표준예외사항이발생하는경우오류를발생시킨다. NO_DATA_FOUND 및 TOO_MANY_ROWS 등의예외사항을사용하여블록의예외처리부에서오류를처리할수있다. 예외처리에대한상세한설명은 9 장예외처리를참조한다. BULK COLLECT 절 한번에한레코드씩가져오는 INTO 절과달리, BULK COLLECT 절은 SELECT 문의수행결과를한꺼번에가져온다. INTO 뒤에는아래두종류의바인드변수를지정할수있다. array_record_name SELECT 문이반환하는레코드들을저장할 RECORD 타입의 associative array 변수를지정한다. array_variable_name SELECT 리스트의각칼럼에대해배열변수를지정한다. 각배열변수의데이터타입은 SELECT 리스트내에서대응하는칼럼의데이터타입과호환되어야하며, 배열변수의개수는 SELECT 리스트의칼럼개수와동일해야한다. BULK COLLECT 절을사용해서질의문의결과집합을한번에배열로가져오는것이 loop 문을사용해서결과행을한건씩가져오는것보다더효율적이다. 예제 예제 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; 저장프로시저블록 53
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.I3 ---------------------------------------- 1 1 1 3 3 3 1 1 1 3 rows selected. 예제 2 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); 54 Stored Procedures Manual
/ 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); 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.I3 ---------------------------------------- 100 100 100 10 0 0 1 1 1 2 2 2 3 3 3 3 4 4 4 4 4 4 5 5 5 6 6 6 7 7 7 8 8 7 9 9 8 8 8 8 10 10 14 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); CREATE OR REPLACE PROCEDURE proc1 저장프로시저블록 55
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.I3 ---------------------------------------- 1 1 1 2 2 2 2 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); / isql> exec proc1; Execute success isql> SELECT * FROM t3; T3.I1 -------------- 10000 1 row selected. 예제 5 56 Stored Procedures Manual 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 ----------------------------------------------- 7610011000001 2000/11/29 00:00:00 7001011001001 2000/11/29 00:00:00 2 rows selected. 예제 6 create table t1(i1 int,i2 int); insert into t1 values(1,1); insert into t1 values(2,2); insert into t1 values(3,3); CREATE OR REPLACE PROCEDURE proc1 AS type myvarchararr is table of varchar(10) index by integer; v2 myvarchararr; SELECT i2 BULK COLLECT INTO v2 FROM t1; FOR i IN v2.first().. v2.last() LOOP println('v2[' i ']=' v2[i]); END LOOP; / isql> EXEC proc1(); v2[1]=1 v2[2]=2 v2[3]=3 Execute success. 저장프로시저블록 57
RETURNING INTO 절 구문 returning_clause ::= RETURN RETURNING expr, into_clause bulk_collect_clause into_clause ::= INTO record_name variable_name, bulk_collect_clause ::= BULK COLLECT INTO array_record_name array_variable_name ; 기능 RETURNING INTO 절은그절이속해있는 DELETE, INSERT, 또는 UPDATE 문의실행으로영향을받은레코드값을저장할변수를 명시한다. 변수는개별적인변수이거나배열변수일수있다. expr 각 expr 는 DML 문에의해영향을받는칼럼의이름이거나칼럼 타입과호환되는데이터표현식이어야한다. into_clause INTO 절은변경된레코드의값들이각값에대응하는 58 Stored Procedures Manual
variable_name 변수로저장될것을지시한다. variable_name 각 variable_name 은조회된 expr 값을저장할 PSM 변수이다. RECORD 타입의변수를사용하는경우를제외하면, 변수의개수는 expr 리스트의 expr 개수와동일해야한다. PSM 변수의타입은대응하는 expr 의타입과호환되어야한다. record_name 구문이반환하는행을저장할 RECORD 타입변수의이름이다. bulk_collect_clause 한번에한레코드씩가져오는 INTO 절과달리, BULK COLLECT 절은구문이반환하는행들을한꺼번에가져온다. INTO 뒤에는아래두종류의바인드변수를지정할수있다. array_record_name RECORD 타입의 associative array 변수를지정한다. array_variable_name expr 리스트의각칼럼에대응하는배열변수를지정한다. 각배열변수의데이터타입은 expr 리스트내에서대응하는칼럼의데이터타입과호환되어야하며, 배열변수의개수는 expr 리스트의칼럼개수와동일해야한다. 예제 예제 1 isql> create table employees ( eno integer, ename varchar(20)); Create success. isql> create or replace procedure proc1 as x1 integer; x2 varchar(30); begin insert into employees values (1, 'jake') return eno, ename into x1, x2; println( 'x1=' x1 ', x2=' x2); end; / Create success. 저장프로시저블록 59
isql> exec proc1; x1=1, x2=jake Execute success. 예제 2 isql> create table employees ( eno integer, ename varchar(20)); Create success. isql> create or replace procedure proc1 as type myintarr is table of integer index by integer; type myvarchararr is table of varchar(30) index by integer; v1 myintarr; v2 myvarchararr; begin insert into employees values (1, 'jake') return eno, ename bulk collect into v1, v2; for i in v1.first().. v1.last() loop println( 'v1[' i ']=' v1[i] ); end loop; for i in v2.first().. v2.last() loop println( 'v2[' i ']=' v2[i] ); end loop; end; / Create success. isql> exec proc1; v1[1]=1 v2[1]=jake Execute success. 예제 3 60 Stored Procedures Manual isql> create table employees ( eno integer, ename varchar(20)); Create success. isql> create or replace procedure proc1 as type myrec is record( i1 integer, i2 varchar(30) ); type myrecarr is table of myrec index by integer; r1 myrecarr; s1 myrec; begin insert into employees values (1, 'jake') return eno, ename bulk collect into r1; for i in r1.first().. r1.last() loop s1 := r1[i]; println( 'r1[' i '].eno=' s1.i1 ', r1[' i '].ename=' s1.i2 ); end loop; end; /
Create success. isql> exec proc1; r1[1].eno=1, r1[1].ename=jake Execute success. 예제 4 create table employees ( eno integer, ename varchar(20)); insert into employees values (1, 'jake'); insert into employees values (2, 'nikita'); insert into employees values (3, 'dana'); isql> create or replace procedure proc1 as x1 integer; x2 varchar(30); begin delete from employees where eno = 1 return eno, ename into x1, x2; println( 'x1=' x1 ', x2=' x2); end; / Create success. isql> exec proc1; x1=1, x2=jake Execute success. 예제 5 create table employees ( eno integer, ename varchar(20)); insert into employees values (1, 'no1.jake'); insert into employees values (1, 'no2.jake'); insert into employees values (1, 'no3.jake'); isql> create or replace procedure proc1 as type myintarr is table of integer index by integer; type myvarchararr is table of varchar(30) index by integer; v1 myintarr; v2 myvarchararr; begin delete from employees where eno = 1 return eno, ename bulk collect into v1, v2; for i in v1.first().. v1.last() loop println( 'v1[' i ']=' v1[i] ); end loop; for i in v2.first().. v2.last() loop println( 'v2[' i ']=' v2[i] ); end loop; end; / Create success. 저장프로시저블록 61
isql> exec proc1; v1[1]=1 v1[2]=1 v1[3]=1 v2[1]=no1.jake v2[2]=no2.jake v2[3]=no3.jake Execute success. 예제 6 create table employees ( eno integer, ename varchar(20)); insert into employees values (1, 'no1.jake'); insert into employees values (1, 'no2.jake'); insert into employees values (1, 'no3.jake'); isql> create or replace procedure proc1 as type myrec is record( i1 integer, i2 varchar(30) ); type myrecarr is table of myrec index by integer; r1 myrecarr; s1 myrec; begin delete from employees where eno = 1 return eno, ename bulk collect into r1; for i in r1.first().. r1.last() loop s1 := r1[i]; println( 'r1[' i '].eno=' s1.i1 ', r1[' i '].ename=' s1.i2 ); end loop; end; / Create success. isql> exec proc1; r1[1].eno=1, r1[1].ename=no1.jake r1[2].eno=1, r1[2].ename=no2.jake r1[3].eno=1, r1[3].ename=no3.jake Execute success. 예제 7 62 Stored Procedures Manual create table employees ( eno integer, ename varchar(20)); insert into employees values (1, 'jake'); insert into employees values (2, 'nikita'); insert into employees values (3, 'dana'); isql> create or replace procedure proc1 as x1 integer; x2 varchar(30); begin update employees set ename = 'mikhaila' where eno = 1 return eno, ename into x1, x2; println( 'x1=' x1 ', x2=' x2); end;
/ Create success. isql> exec proc1; x1=1, x2=mikhaila Execute success. 예제 8 create table employees ( eno integer, ename varchar(20)); insert into employees values (1, 'no1.jake'); insert into employees values (1, 'no2.jake'); insert into employees values (1, 'no3.jake'); isql> create or replace procedure proc1 as type myintarr is table of integer index by integer; type myvarchararr is table of varchar(30) index by integer; v1 myintarr; v2 myvarchararr; begin update employees set eno = 5, ename = 'mikhaila' where eno = 1 return eno, ename bulk collect into v1, v2; for i in v1.first().. v1.last() loop println( 'v1[' i ']=' v1[i] ); end loop; for i in v2.first().. v2.last() loop println( 'v2[' i ']=' v2[i] ); end loop; end; / Create success. isql> exec proc1; v1[1]=5 v1[2]=5 v1[3]=5 v2[1]=mikhaila v2[2]=mikhaila v2[3]=mikhaila Execute success. 예제 9 create table employees ( eno integer, ename varchar(20)); insert into employees values (1, 'no1.jake'); insert into employees values (1, 'no2.jake'); insert into employees values (1, 'no3.jake'); isql> create or replace procedure proc1 as type myrec is record( i1 integer, i2 varchar(30) ); 저장프로시저블록 63
type myrecarr is table of myrec index by integer; r1 myrecarr; s1 myrec; begin update employees set eno = 5, ename = 'mikhaila' where eno = 1 return eno, ename bulk collect into r1; for i in r1.first().. r1.last() loop s1 := r1[i]; println( 'r1[' i '].eno=' s1.i1 ', r1[' i '].ename=' s1.i2 ); end loop; end; / Create success. isql> exec proc1; r1[1].eno=5, r1[1].ename=mikhaila r1[2].eno=5, r1[2].ename=mikhaila r1[3].eno=5, r1[3].ename=mikhaila Execute success. 64 Stored Procedures Manual
할당문 구문 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 으로참조할수있다. 저장프로시저블록 65
예제 예제 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.I3 ---------------------------------------- 5 6 7 6 7 8 7 8 9 8 9 10 9 10 11 10 11 12 6 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; / 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); / 66 Stored Procedures Manual
isql> EXEC proc1; Execute success. isql> SELECT * FROM t1; T1.I1 T1.I2 T1.I3 ---------------------------------------- 100 100 100 1 row selected. 저장프로시저블록 67
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 지정 <<LABEL2>> DECLARE V1 INTEGER;...(1) 68 Stored Procedures Manual
/ <<LABEL3>> DECLARE V1 INTEGER;...(2) LABEL1.V1 := 1; -- (1) 의 V1 참조 LABEL2.V1 := 2; LABEL3.V1 := 3; -- (1) 의 V1 참조 -- (2) 의 V1 참조 아래예제의경우 DECLARE 문위에 LABEL 을지정하지않아서 에러가나게된다. CREATE OR REPLACE PROCEDURE PROC1 AS V1 INTEGER; <<LABEL1>> V1 := 1; DECLARE V1 INTEGER; LABEL1.V1 := 1; --- ERROR. 3. 2 의경우와유사하게중첩된 LOOP 에서빠져나올때사용하는 경우반드시 LOOP 시작직전에 LABEL 을선언해야한다. LOOP 전에 LABEL 을여러개선언하는것은허용된다. CREATE OR REPLACE PROCEDURE PROC1 AS V1 INTEGER; V1 := 0; <<LABEL1>> <<LABEL2>> FOR I IN 1.. 10 LOOP V1 := V1 + 1; FOR I IN 1.. 10 LOOP V1 := V1 + 1; EXIT LABEL1 WHEN V1 = 30; END LOOP; END LOOP; / 아래예제는한 LABEL 이 LOOP 시작직전에선언되지않은 경우이다. 이 LABEL 을이용해서는중첩된 LOOP 를빠져나올수 없으므로, 저장프로시저컴파일시에러가발생하게된다. CREATE OR REPLACE PROCEDURE PROC1 AS V1 INTEGER; <<LABEL1>> V1 := 0; <<LABEL2>> 저장프로시저블록 69
70 Stored Procedures Manual FOR I IN 1.. 10 LOOP V1 := V1 + 1; FOR I IN 1.. 10 LOOP V1 := V1 + 1; EXIT LABEL1 WHEN V1 = 30; -- ERROR END LOOP; END LOOP; /
PRINT 구문 print_statement ::= PRINT PRINTLN ( string ) ; 기능 PRINT 구문은저장프로시저실행시에사용자가원하는텍스트를해당프로시저를호출한클라이언트에게출력한다. PRINT 구문은주로디버깅및테스트목적으로사용하도록 ALTIBASE HDB 가제공하는시스템프로시저이다. PRINTLN 은 PRINT 와동일하나출력메시지의마지막에개행문자 ( 윈도우는 \r\n, 유닉스는 \n ) 를붙여서출력한다. PRINT, PRINTLN 의소유자는 SYSTEM_ 이므로사용시아래처럼이를명시해도된다 : SYSTEM_.PRINTLN( Hello World ); 그러나이들에대한 PUBLIC 시노님이기본적으로생성되어있기때문에 SYSTEM_ 을굳이명시하지않아도된다. String 클라이언트로출력할문자열을기술한다. 사용자메시지출력시문자열과함께변수값등을출력하고싶은경우에는예제 2 와같이문자열연결연산자인 를사용해서하나의문자열로만들어서출력하면된다. 저장프로시저블록 71
예제 예제 1 CREATE OR REPLACE PROCEDURE proc1 AS v1 BIGINT; v1 := BIGINT'9223372036854775807'; system_.println ('1'); system_.println (v1); system_.println ('2'); / isql> EXEC proc1; 1 9223372036854775807 2 Execute success. 예제 2 CREATE OR REPLACE PROCEDURE proc1 AS eno_count INTEGER; SELECT COUNT(eno) INTO eno_count FROM employees; 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 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('--------------------------'); 72 Stored Procedures Manual
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 Procedure ----------------------------------------------- Execute success. 저장프로시저블록 73
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 RETURN p1 / 2; / 74 Stored Procedures Manual
isql> SELECT times_half(times_half(8)) FROM t1; TIMES_HALF(TIMES_HALF(8)) ---------------------------- 2 1 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 -------------- 100 100 100 3 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); 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) ------------------- 13 12 11 10 4 rows selected. 저장프로시저블록 75
4. 흐름제어 흐름제어 77
개요 이장은저장프로시저바디내에서의흐름제어방법을설명한다. 구문 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 문의조건으로사용될수없다. 단, 아래의조건은예외이다 : EXIST (subquery) NOT EXIST (subquery) 78 Stored Procedures Manual
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 문의조건과는다른조건을명시할수 있다. 흐름제어 79
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 employees; 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 := 5000; ELSIF e_job = 'MANAGER' THEN e_salary := 4500; ELSIF e_job = 'ENGINEER' THEN e_salary := 4300; ELSIF e_job = 'PROGRAMMER' THEN e_salary := 4100; ELSE e_salary := 4000; END IF; UPDATE employees SET salary = e_salary WHERE eno = emp_id; END IF; END LOOP; CLOSE c1; 80 Stored Procedures Manual
/ isql> SELECT eno, emp_job FROM employees WHERE salary IS NULL; ENO EMP_JOB -------------------------------- 1 CEO 8 manager 20 sales rep 3 rows selected. isql> EXEC proc1; Execute success. isql> SELECT eno, emp_job, salary FROM employees WHERE eno=1 OR eno=8 OR eno=20; ENO EMP_JOB SALARY --------------------------------------------- 1 CEO 5000 8 manager 4500 20 sales rep 4000 3 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)); / 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; 흐름제어 81
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.I3 ----------------------------------------------- 21-JUL-2001 2021 2000/07/01 00:00:00 21-OCT-01 10 1990/02/01 00:00:00 21-NOV-01 20 1990/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 > 25000 THEN bonus := 1000; ELSIF sum_qty > 15000 THEN bonus := 500; ELSE 82 Stored Procedures Manual
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 ------------------------------------ 12 17870 19 25350 20 13210 3 rows selected. isql> SELECT * FROM payroll; PAYROLL.ENO PAYROLL.BONUS ----------------------------- 12 500 19 1000 20 200 3 rows selected. 흐름제어 83
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: 조건식이참일때에특정한문장을수행하는방식 84 Stored Procedures Manual