테스트 환경: Toad for DB2, DB2 Server Express-C
다음의 코드는 Toad for DB2에서 작성한 예이며 개발자에 코딩된 부분은 청색으로 표시했고, 나머지 부분은 개발자의 설정에 의해 Toad for DB2가 대신 작성해 준 것이므로 실제 개발자가 직접 작성할 부분은 실제 Query 뿐이다.
DB2를 설치할 때 디폴트로 생성되는 SAMPLE 데이터베이스의 employee 테이블을 대상으로 질의를 작성한 것이다.
Stored Procedure 내에서는 화면에 출력할 수 없기 때문에 디버그 문자열을 직접 출력해 보기 위해서는 다른 디버그용 테이블에 저장하여 확인하는 방법을 사용했다. 디버그 문자열을 저장하기 위해 debugtxt 라는 테이블을 생성하고 사용하는 것으로 하였다. 즉, 아래의 Stored Procedure를 실행한 후에 debugtxt 테이블을 확인해 보면 Stored Procedure가 리턴한 문자열을 확인할 수 있다.
Toad for DB2에서 Stored Procedure 실행
아래의 Stored Procedure 를 작성한 후에 'Edit SQL' 윈도우에 아래와 같이 명령하면 실행결과를 바로 확인할 수 있을 것이다.
좀더 편리한 방법은 Procedures 탭을 눌러서 나오는 프로시저 이름 리스트에서 마우스 우측을 눌러서 'Execute Procedures' 를 선택하면 된다.
CALL ADMINISTRATOR.PROC_TEST();
select * from DEBUGTXT;
PROC_TEST 프로시저의 내용
SET SCHEMA = 'ADMINISTRATOR';
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","DB2ADMIN";
CREATE PROCEDURE "ADMINISTRATOR"."PROC_TEST" ( )
SPECIFIC "SQL110211185311800"
LANGUAGE SQL
NOT DETERMINISTIC
COMMIT ON RETURN NO
EXTERNAL ACTION
MODIFIES SQL DATA
CALLED ON NULL INPUT
INHERIT SPECIAL REGISTERS
OLD SAVEPOINT LEVEL
BEGIN
DECLARE f_name VARCHAR(12);
select firstnme into f_name from ADMINISTRATOR.employee where empno='000010';
insert into debugtxt values (f_name);
commit;
END;
SET SCHEMA = 'SYSIBM';
GRANT EXECUTE ON PROCEDURE "ADMINISTRATOR"."PROC_TEST"( ) TO USER "DB2ADMIN" WITH GRANT OPTION;
SET SCHEMA = 'ADMINISTRATOR';
IN 파라미터를 사용한 Stored Procedure의 예
Toad for DB2 4.6 을 사용하여 작성했기 때문에 아래의 굵은 글씨에 해당하는 부분만 개발자가 입력하면 된다
아래와 같이 작성된 프로시저는 'Edit SQL' 창에 call SP_IN_PARAM(2); 와 같이 입력하여 실행하면 프로시저가 실행되고 그 결과 debugtxt 테이블에 저장되도록 했기 때문에 select * from debugtxt 명령으로 확인할 수 있다.
SET SCHEMA = 'DB2ADMIN';
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","DB2ADMIN";
CREATE PROCEDURE "DB2ADMIN"."SP_IN_PARAM" ( IN "PNUM" INTEGER )
SPECIFIC "SQL110216134349800"
LANGUAGE SQL
NOT DETERMINISTIC
COMMIT ON RETURN NO
EXTERNAL ACTION
MODIFIES SQL DATA
CALLED ON NULL INPUT
INHERIT SPECIAL REGISTERS
OLD SAVEPOINT LEVEL
BEGIN
DECLARE na VARCHAR(20);
SELECT name INTO na FROM sample_tb WHERE num=PNUM;
INSERT INTO debugtxt VALUES (na);
commit;
END;
SET SCHEMA = 'SYSIBM';
GRANT EXECUTE ON PROCEDURE "DB2ADMIN"."SP_IN_PARAM"( INTEGER ) TO USER "DB2ADMIN" WITH GRANT OPTION;
SET SCHEMA = 'DB2ADMIN';
Toad for DB2의 'Edit SQL' 창에서 call SP_IN_PARAM(2); 와 같이 입력하고 실행해서 프로시저를 실행할 수 있지만 더 편리한 방법은 Procedures 탭을 누르면 프로시저가 나열되어 있을 때 특정 프로시저의 이름 위에서 마우스 우측을 누르고 'Execute Procedures...' 를 선택하면 파라미터를 입력하는 창이 뜨고 파라미터를 입력하면 프로시저를 실행할 수 있다.
OUT 파라미터를 이용한 프로시저의 예
이 프로시저를 Toad for DB2상에서 실행할 때는 앞의 방법과 동일하게 'Edit SQL' 창에 'call SP_OUT_PARAM(?);' 을 입력하여 실행하는 방법과 'Procedures' 탭을 눌러 나열된 프로시저의 이름 위에서 마우스 우측을 눌러 'Execute Procedurss...' 를 선택하는 방법이 있다. 실행명령문의 '?' 표는 출력 파라미터를 의미하므로 실제 값을 전달해서는 안된다.
SET SCHEMA = 'DB2ADMIN';
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","DB2ADMIN";
CREATE PROCEDURE "DB2ADMIN"."SP_OUT_PARAM" ( OUT "NA" VARCHAR(20) )
SPECIFIC "SQL110216140902100"
LANGUAGE SQL
NOT DETERMINISTIC
COMMIT ON RETURN NO
EXTERNAL ACTION
MODIFIES SQL DATA
CALLED ON NULL INPUT
INHERIT SPECIAL REGISTERS
OLD SAVEPOINT LEVEL
BEGIN
select name into NA from sample_tb where num=4;
insert into debugtxt values(NA);
commit;
END;
SET SCHEMA = 'SYSIBM';
GRANT EXECUTE ON PROCEDURE "DB2ADMIN"."SP_OUT_PARAM"( VARCHAR(20) ) TO USER "DB2ADMIN" WITH GRANT OPTION;
SET SCHEMA = 'DB2ADMIN';
IN, OUT 파라미터를 동시에 사용하는 프로시저의 예
IN, OUT 파라미터를 동시에 가진 프로시저를 호출할 때는 'Edit SQL' 창에서 다음과 같이 입력하고 실행하면 된다.
call SP_IN_OUT_PARAM(5, ?); --> OUT 파라미터를 '?' 으로 표현하면 된다. 이름 위에서 마우스 우측을 눌러 실행하는 것은 위와 동일하며, OUT 파라미터를 입력하는 창에서는 '?' 입력하면 된다.
SET SCHEMA = 'DB2ADMIN';
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","DB2ADMIN";
CREATE PROCEDURE "DB2ADMIN"."SP_IN_OUT_PARAM" (
IN "PNUM" INTEGER,
OUT "PNAME" VARCHAR(20) )
SPECIFIC "SQL110216142721600"
LANGUAGE SQL
NOT DETERMINISTIC
COMMIT ON RETURN NO
EXTERNAL ACTION
MODIFIES SQL DATA
CALLED ON NULL INPUT
INHERIT SPECIAL REGISTERS
OLD SAVEPOINT LEVEL
BEGIN
SELECT name INTO PNAME FROM sample_tb WHERE num=PNUM;
INSERT INTO debugtxt VALUES( PNAME );
COMMIT;
END;
SET SCHEMA = 'SYSIBM';
GRANT EXECUTE ON PROCEDURE "DB2ADMIN"."SP_IN_OUT_PARAM"( INTEGER, VARCHAR(20) ) TO USER "DB2ADMIN" WITH GRANT OPTION;
SET SCHEMA = 'DB2ADMIN';
CURSOR를 사용하는 프로시저의 예
커서에 포함된 가장 위쪽 행을 가져오는 예 (클라이언트에게 리턴되지 않는 커서 예제)
SET SCHEMA = 'DB2ADMIN';
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","DB2ADMIN";
CREATE PROCEDURE "DB2ADMIN"."SP_CURSOR_DEMO" ( )
SPECIFIC "SQL110216153400000"
LANGUAGE SQL
NOT DETERMINISTIC
COMMIT ON RETURN NO
EXTERNAL ACTION
MODIFIES SQL DATA
CALLED ON NULL INPUT
INHERIT SPECIAL REGISTERS
OLD SAVEPOINT LEVEL
BEGIN
DECLARE VNAME varchar(20);
DECLARE c1 CURSOR WITH HOLD FOR
SELECT name FROM db2admin.sample_tb;
OPEN c1;
FETCH c1 INTO VNAME; -- 커서의 맨 위 레코드를 가져온다
INSERT INTO debugtxt values(VNAME);
commit;
END;
SET SCHEMA = 'SYSIBM';
GRANT EXECUTE ON PROCEDURE "DB2ADMIN"."SP_CURSOR_DEMO"( ) TO USER "DB2ADMIN" WITH GRANT OPTION;
SET SCHEMA = 'DB2ADMIN';
클라이언트에게 리턴되는 커서 예제
아래의 프로시저를 호출하면 일반 SQL문장을 실행한 경우처럼 'Edit SQL' 창에 바로 출력된다.
SET SCHEMA = 'DB2ADMIN';
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","DB2ADMIN";
CREATE PROCEDURE "DB2ADMIN"."SP_CURSOR_RETURN_TO_CLIENT" ( )
SPECIFIC "SQL110216162709300"
LANGUAGE SQL
NOT DETERMINISTIC
COMMIT ON RETURN NO
EXTERNAL ACTION
MODIFIES SQL DATA
CALLED ON NULL INPUT
INHERIT SPECIAL REGISTERS
OLD SAVEPOINT LEVEL
BEGIN
DECLARE c1 CURSOR WITH RETURN TO CLIENT FOR
select * from sample_tb;
OPEN c1;
END;
SET SCHEMA = 'SYSIBM';
GRANT EXECUTE ON PROCEDURE "DB2ADMIN"."SP_CURSOR_RETURN_TO_CLIENT"( ) TO USER "DB2ADMIN" WITH GRANT OPTION;
SET SCHEMA = 'DB2ADMIN';
CURSOR와 WHILE 문장을 사용하는 프로시저 예
SET SCHEMA = 'DB2ADMIN';
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","DB2ADMIN";
CREATE PROCEDURE "DB2ADMIN"."SP_CURSOR_DEMO" ( )
SPECIFIC "SQL110216153400000"
LANGUAGE SQL
NOT DETERMINISTIC
COMMIT ON RETURN NO
EXTERNAL ACTION
MODIFIES SQL DATA
CALLED ON NULL INPUT
INHERIT SPECIAL REGISTERS
OLD SAVEPOINT LEVEL
BEGIN
DECLARE VNAME varchar(20);
DECLARE c1 CURSOR WITH HOLD FOR
SELECT name from db2admin.sample_tb;
OPEN c1;
FETCH c1 INTO VNAME;
INSERT INTO debugtxt VALUES(VNAME);
commit;
END;
SET SCHEMA = 'SYSIBM';
GRANT EXECUTE ON PROCEDURE "DB2ADMIN"."SP_CURSOR_DEMO"( ) TO USER "DB2ADMIN" WITH GRANT OPTION;
SET SCHEMA = 'DB2ADMIN';