html5-img
1 / 24

C O N T E N T S

C O N T E N T S. SP 기본 구조 SP 예제 코드 주석문 SP 이름과 매개 변수 선언 SPECIFIC 옵션 RESULT SETS 옵션 LANGUAGE 옵션 COMPOUND SQL 블록 지역 변수 선언 및 초기화 반환용 변수 선언 및 초기화 CONDITION 선언 CURSOR 선언 HANDLER 선언 NOT ATOMIC 옵션. ATOMIC 옵션 COMMIT, ROLLBACK, SAVEPOINT 문 변수 선언 , 초기화 , 값 할당

camden
Télécharger la présentation

C O N T E N T S

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. C O N T E N T S • SP 기본 구조 • SP 예제 코드 • 주석문 • SP 이름과 매개 변수 선언 • SPECIFIC 옵션 • RESULT SETS 옵션 • LANGUAGE 옵션 • COMPOUND SQL 블록 • 지역 변수 선언 및 초기화 • 반환용 변수 선언 및 초기화 • CONDITION 선언 • CURSOR 선언 • HANDLER 선언 • NOT ATOMIC 옵션 • ATOMIC 옵션 • COMMIT, ROLLBACK, SAVEPOINT 문 • 변수 선언, 초기화, 값 할당 • SQLCODE, SQLSTATE, 반환용 변수 선언 • 오류 CONDITION 선언 • CURSOR 선언 • 오류 HANDLER 처리 로직 정의 • LOGIC FLOW CONTROL 문 • DYNAMIC SQL 문 • GET DIAGNOSTICS 문 • SIGNAL 문 • TEMPORARY TABLE 정의 • SECURITY 제어

  2. SP 기본 구조 ----------------------------------- -- 주석문 ----------------------------------- -- 프로그램ID : INST01.SP01 -- 업 무 명 : 계약정보관리 ----------------------------------- -- SP 및 매개 변수 선언 ----------------------------------- CREATE PROCEDURE inst01.sp01 ( IN p_deptno CHAR(3), OUT p_message VARCHAR(100) ) ----------------------------------- -- SPECIFIC NAME 설정 ----------------------------------- SPECIFIC DELETE_DEPT ----------------------------------- -- RESULT SET 개수 설정 ----------------------------------- DYNAMIC RESULT SETS 1 ----------------------------------- -- SP 작성 언어 설정 ----------------------------------- LANGUAGE SQL ----------------------------------- -- 본문 시작 ----------------------------------- BEGIN--첫번재 BEGIN ----------------------------------- -- 로컬 변수 선언 및 초기화 ----------------------------------- DECLARE v_num_rows INT DEFAULT 0; ----------------------------------- -- 반환용 변수 선언 및 초기화 ----------------------------------- DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; DECLARE SQLCODE INT DEFAULT 0; DECLARE v_ret_value INT DEFAULT 0; ----------------------------------- -- CONDITION 선언 ------------------------------------------------------ DECLARE c_EMP_child_rows_exist CONDITION FOR SQLSTATE '99001'; ----------------------------------- -- CURSOR 선언 ----------------------------------- DECLARE C1 CURSOR FOR SELECT문; ----------------------------------- -- HANDLER 선언 ----------------------------------- DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SET p_message = 'Unknown error, SQLSTATE: "' || SQLSTATE || '", SQLCODE=' || CHAR(SQLCODE); SET v_ret_value = -1; END; ----------------------------------- -- 로직 구현 (SQL구성 및 실행) ----------------------------------- -- Logic 처리 END--첫번째 END ----------------------------------- -- 명령 종료 문자 ----------------------------------- @

  3. SP 예제 코드 -- 이것은 SP 샘플 코드입니다. CREATE PROCEDURE inst01.sp01 ( IN p_deptno CHAR(3), OUT p_message VARCHAR(100) ) SPECIFIC delete_dept DYNAMIC RESULT SET 1 LANGUAGE SQL BEGIN -- SQLCODE, SQLSTATE, 반환용 변수 선언 DECLARE SQLCODE INT DEFAULT 0; DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; DECLARE v_return INT DEFAULT 0; -- MAIN 시작 body:BEGIN -- 지역 변수 선언 DECLARE v_num_rows INT DEFAULT 0; -- CONDITION 선언 DECLARE c_EMP_child_rows_exist CONDITION FOR SQLSTATE '99001'; -- CURSOR 선언 DECLARE c1 CURSOR FOR SELECT * FROM department WHERE deptno = p_deptno; -- 시스템 핸들러 선언 DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SET p_message = ‘오류 : SQLSTATE: ‘ || SQLSTATE ||’,’|| CHAR(SQLCODE); SET v_ret_value = -1; END; -- 사용자 정의 핸들러 선언 DECLARE EXIT HANDLER FOR c_EMP_child_rows_exist BEGIN SET p_message = ‘child EMPLOYEE rows exist.'; SET v_ret_value = -1; END; -- Child table: EMPLOYEE SELECT COUNT(1) INTO v_num_rows FROM employee WHERE workdept = p_deptno; -- EMPLOYEE 에 데이터 존재하는 경우, SIGNAL 발생 IF v_num_rows <> 0 THEN SIGNAL c_EMP_child_rows_exist; END IF; -- DEPARTMENT 데이터 삭제 DELETE FROM department WHERE deptno = p_deptno; -- 삭제된 ROW_COUNT 검사 GET DIAGNOSTICS v_num_rows = ROW_COUNT; -- 반환 메시지 지정 IF v_num_rows = 0 THEN BEGIN SET v_ret_value = 1; SET p_message = ‘데이터가 없습니다 : ‘ || p_deptno; END; ELSE SET p_message = p_deptno || ‘가 삭제되었습니다.’ ; END IF; END body; OPEN c1; RETURN v_ret_value; END

  4. 주석문 여러 행에 걸친 주석문은 /* -- */ 를 이용하며, 한 행에 해당하는 주석문은 –- 를 이용하여 지정해도 됩니다. /********************************************************************************************************************************* ** 프로그램ID : inst01_sp01 ** 업무명 : Sample Stored Procedure ** 프로그램명 : inst01_sp01.db2 ** 최초 작성일 : 2004-06-08 ** 최종 작성일 : 2004-06-08 ** 최종 수정일 : 2004-06-08 ** 개발자 : Kim Eun Sook ** 수정자 : Kim Eun Sook ** 입력값 : p_deptno (부서 코드) ** 출력값 : p_message (오류 메시지) ** 참고사항 : 없음 ** 변경이력 : 없음 ** 실행예제 : call inst01,sp01(‘E01’,?) ********************************************************************************************************************************/ CREATE PROCEDURE inst01.sp01 ( IN p_deptno CHAR(3), OUT p_message VARCHAR(100) ) SPECIFIC delete_dept DYNAMIC RESULT SET 1 LANGUAGE SQL BEGIN -- SQLCODE, SQLSTATE, 반환용 변수 선언 DECLARE SQLCODE INT DEFAULT 0; -- SQLCODE 변수 DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; -- SQLSTATE 변수 DECLARE v_return INT DEFAULT 0; -- SP 실행 상태 변수 body:BEGIN -- 지역 변수 선언 DECLARE v_num_rows INT DEFAULT 0;

  5. SP 이름과 매개 변수 선언 SP 이름을 지정할 때는 스키마명을 명시하도록 합니다. CREATE PROCEDURE TEST.SP01 (IN P_EMPID INTEGER, IN P_PERCENTINCR DECIMAL(4,2), OUT P_UPDATED_SALARY INT ) 스키마명이 생략된 경우 기본 스키마명은 레지스터리 변수인 “CURRENT SCHEMA” 에 의해 결정됩니다. CURRENT SCHEMA 가 지정되어 있지 않은 경우에는 현재 접속한 사용자의 ID 를 기본값으로 제공합니다. 사용자명이 INST01 이라면 다음의 경우에 INST01.SP01 로 해석됩니다. CREATE PROCEDURE SP01 (IN P_EMPID INTEGER, IN P_PERCENTINCR DECIMAL(4,2), OUT P_UPDATED_SALARY INT ) 매개 변수는 모드, 변수 이름, 데이터 유형 등의 세 가지 부분으로 구성됩니다. 매개 변수의 모드에는 IN, OUT, INOUT의 세 가지가 있습니다. CREATE PROCEDURE TEST.SP01 (IN P_EMPID INTEGER, IN P_PERCENTINCR DECIMAL(4,2), OUT P_UPDATED_SALARY INT ) 매개 변수가 없는 경우에는 () 만 입력하면 됩니다. CREATE PROCEDURE TEST.SP02 ()

  6. SPECIFIC 옵션 동일한 SP 이름을 가지고 있으나, 매개 변수의 개수가 다른 SP를 여러 개 생성할 수 있습니다. 이를 “PROCEDURE OVERLOADING”이라 합니다. 이 경우에는 SPECIFIC절을 지정하며, 이 이름을 이용하여 SP를 DROP 합니다. 지정되는 이름은 한 데이터베이스내에서 고유해야 합니다. • 매개 변수가 3개인 SP • CREATE PROCEDURE sum(IN p_a INTEGER,IN p_b INTEGER,OUT p_s INTEGER) • SPECIFIC sum_ab • LANGUAGE SQL • BEGIN • SET p_s = p_a + p_b; • END • 매개 변수가 4개인 SP • CREATE PROCEDURE sum(IN p_a INTEGER,IN p_b INTEGER,IN p_c INTEGER,OUT p_s INTEGER) • SPECIFIC sum_abc • LANGUAGE SQL • BEGIN • SET p_s = p_a + p_b + p_c; • END CLP 를 이용하는 경우 다음과 같이 호출합니다. CLP 를 이용하는 경우 다음과 같이 SPECIFIC NAME 을 이용하여 DROP 합니다. CALL sum(100,200,?) CALL sum(100,200,300,?) DROP PROCEDURE sum(INTEGER,INTEGER,INTEGER) DROP PROCEDURE sum_abc

  7. RESULT SETS 옵션 • 다음과 같은 방법으로 한 개 이상의 Result Set을 반환할 수 있습니다. SP, CLP, java 등을 이용하여 반환된 결과 집합을 처리할 수 있습니다. • CREATE PROCEDURE문장 내에 DYNAMIC RESULT SETS 절을 기술합니다. • WITH RETURN절과 함께 커서를 선언합니다. • 커서를 오픈한 채로 SP를 종료합니다. CREATE PROCEDURE read_emp_multi() SPECIFIC read_emp_multi DYNAMIC RESULT SETS 2 LANGUAGE SQL re: BEGIN DECLARE v_comm DEC(9,2) DEFALUT 0.0; DECLARE c_salary CURSOR WITH RETURN FOR SELECT salary FROM employee; DECLARE c_bonus CURSOR WITH RETURN FOR SELECT bonus FROM employee; DECLARE c_comm CURSOR SELECT comm FROM employee; OPEN c_comm; FETCH c_comm INTO v_comm; WHILE ( SQLSTATE = '00000' ) DO SET p_total = p_total + v_comm; FETCH c_comm INTO v_comm; END WHILE; OPEN c_salary; OPEN c_bonus; RETURN p_total; END re CREATE PROCEDURE receive_multi (IN p_dept CHAR(3), OUT p_names VARCHAR(100), OUT p_total DECIMAL(9,2) ) SPECIFIC receive_multi LANGUAGE SQL rm: BEGIN DECLARE v_fname VARCHAR(12) DEFAULT ''; DECLARE v_lname VARCHAR(15) DEFAULT ''; DECLARE v_salary DECIMAL(9,2) DEFAULT 0.0; DECLARE v_rs1, v_rs2, v_rs3 RESULT_SET_LOCATOR VARYING; DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; CALL emp_multi(p_dept); ASSOCIATE RESULT SET LOCATOR (v_rs1, v_rs2) WITH PROCEDURE emp_multi; ALLOCATE v_rsCur1 CURSOR FOR RESULT SET v_rs1; ALLOCATE v_rsCur2 CURSOR FOR RESULT SET v_rs2; SET p_names = 'The employees are:'; WHILE (SQLSTATE = '00000') DO SET p_names = p_names || v_fname || ' ' || v_lname || ' '; FETCH FROM v_rsCur1 INTO v_fname; FETCH FROM v_rsCur2 INTO v_lname; END WHILE; SET p_total = 0; WHILE ( SQLSTATE = '00000' ) DO SET p_total = p_total + v_salary; FETCH FROM v_rsCur3 INTO v_salary; END WHILE; END rm

  8. LANGUAGE 옵션 • SP 를 작성하는 방법은 여러 가지가 있습니다. • SQL/PL • C, COBOL, FOTRAN, … • JAVA • OLE SQL/PL 을 이용하여 SP를 개발하는 경우에는 LANGUAGE SQL 이라고 명시합니다. CREATE PROCEDURE inst01.sp01 ( IN p_deptno CHAR(3), OUT p_message VARCHAR(100) ) SPECIFIC delete_dept DYNAMIC RESULT SET 1 LANGUAGE SQL BEGIN -- SQLCODE, SQLSTATE, 반환용 변수 선언 DECLARE SQLCODE INT DEFAULT 0; DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; DECLARE v_return INT DEFAULT 0; -- MAIN 시작 body:BEGIN -- 지역 변수 선언 DECLARE v_num_rows INT DEFAULT 0; -- CONDITION 선언 DECLARE c_EMP_child_rows_exist CONDITION FOR SQLSTATE '99001'; -- CURSOR 선언 DECLARE c1 CURSOR FOR SELECT * FROM department WHERE deptno = p_deptno;

  9. COMPOUND SQL 블럭 SP의 본문은 한 개 이상의 COMPOUND SQL 블럭으로 구성됩니다. 각 블록의 형식은 다음과 같습니다. 연관된 SQL 문들은 한 블럭안에 코딩합니다. 블록 내부에 변수, CURSOR, CONDITION, HANDLER, Flow Control 등을 코딩할 수 있습니다. 한 블록 내에서의 코딩 순서는 아래와 같습니다. 한 개 이상의 Compound SQL 블럭을 중첩하여 정의할 수 있습니다. 외부 블록에서는 내부 블럭에 선언된 변수를 사용할 수 없지만, 내부 블록에서는 외부 블럭에 선언된 변수를 사용할 수 있습니다. -- 외부 블록 시작 BEGIN -- 외부 블록 변수 DECLARE v_outer1 INT; DECLARE v_outer2 INT; DECLARE v_outer3 INT; -- 내부 블록 시작 BEGIN -- 내부 블럭 변수 DECLARE v_inner1 INT; DECLARE v_inner2 INT; DECLARE v_inner3 INT; -- 변수 값 할당 SET v_outer1 = 100; -- 외부 블록 변수 참조 SET v_inner1 = 200; -- 내부 블록 종료 END; -- 변수 값 할당 SET v_outer2 = 300; SET v_inner2 = 400; -- 내부 블록 변수 참조 (오류 발생) -- 외부 블록 종료 END -- 외부 블록 레이블명: BEGIN 변수 선언 컨디션 선언 반환용 변수 선언 커서 선언 핸들러 선언 SQL문 및 각종 로직 구현 문장 -- 내부 블록 1 레이블명: BEGIN 변수 선언 컨디션 선언 반환용 변수 선언 커서 선언 핸들러 선언 SQL문 및 각종 로직 구현 문장 END 레이블명 -- 내부 블록 2 레이블명: BEGIN 변수 선언 컨디션 선언 반환용 변수 선언 커서 선언 핸들러 선언 SQL문 및 각종 로직 구현 문장 END 레이블명 END 레이블명

  10. NOT ATOMIC 옵션 Compound 블록에 ATOMIC 옵션을 기술하지 않으면 기본적으로 NOT AUTOMIC 모드로 정의됩니다. NOT AUTOMIC 절은 선택적으로 사용할 수 있으나, SQL 코드의 모호성을 피하기 위해 명시적으로 사용하는 것이 권장됩니다. NOT ATOMIC 옵션을 지정하면, 블록 내의 모든 SQL문은 독립적으로 간주되므로, 오류가 발생한 SQL문은 무시됩니다. COMMIT / ROLLBACK 을 이용하여 제어할 수 있습니다. CREATE PROCEDURE not_atomic_proc () SPECIFIC not_atomic_proc LANGUAGE SQL BEGIN NOT ATOMIC DECLARE v_job VARCHAR(8); INSERT INTO atomic_test(proc, res) VALUES ('Not_Atomic_Proc','Before error test'); COMMIT; SIGNAL SQLSTATE '70000'; INSERT INTO atomic_test(proc, res) VALUES ('Not_Atomic_Proc','After error test'); END 첫 번째 insert 문이 commit 된 후, 오류가 발생하였으므로 atomic_test 테이블에는 한 건의 데이터가 이미 입력되었습니다. $ db2 “delete from atomic_test” $ db2 "select * from atomic_test" PROC RES -------------------- -------------------- 0 레코드가 선택됨. $ db2 "call not_atomic_proc()“ SQL0438N 응용프로그램이 진단 텍스트 ""과(와) 함께 오류를 표시했습니다. SQLSTATE=70000 $ db2 "select * from atomic_test" PROC RES -------------------- -------------------- Not_Atomic_Proc Before error test 1 레코드가 선택됨. CREATE TABLE ATOMIC_TEST ( PROC VARCHAR(20), RES VARCHAR(20) )

  11. ATOMIC 옵션 ATOMIC 옵션을 사용하면 Compound 블록 내의 문장들이 단일 문장으로 처리됩니다. 만약 블록 내의 임의의 문장을 실행하다가 오류가 발생하면 블록 내에서 이미 실행된 모든 문장들이 롤백 처리됩니다. ATOMIC 옵션을 가진 Compound 블록 내에서는 COMMIT, ROLLBACK, SAVEPOINTS 문장과 중첩된 ATOMIC Compound 블럭을 기술 할 수 없습니다. CREATE PROCEDURE atomic_proc () SPECIFIC atomic_proc LANGUAGE SQL BEGIN ATOMIC DECLARE v_job VARCHAR(8); INSERT INTO atomic_test(proc, res) VALUES ('Atomic_Proc','Before error test'); SIGNAL SQLSTATE '70000'; INSERT INTO atomic_test(proc, res) VALUES ('Atomic_Proc','After error test'); END 두 개의 insert 문이 모두 rollback 되므로 atomic_test 테이블에는 데이터가 한 건도 없습니다. $ db2 “delete from atomic_test” $ db2 "select * from atomic_test" PROC RES -------------------- -------------------- 0 레코드가 선택됨. $ db2 "call atomic_proc()“ SQL0438N 응용프로그램이 진단 텍스트 ""과(와) 함께 오류를 표시했습니다. SQLSTATE=70000 $ db2 "select * from atomic_test" PROC RES -------------------- -------------------- 0 레코드가 선택됨

  12. COMMIT, ROLLBACK, SAVEPOINT 문 ROLLBACK문은 실행 중에 오류가 발생하면 한 UOW 단위로 그 실행을 취소하게 합니다. ROLLBACK TO SAVEPOINT 문을 이용하면 한 UOW 내에서 SAVEPOINT가 지정된 시점부터 ROLLBACK 이 요청된 시간 사이의 변경 부분만 실행을 취소할 수 있습니다. ROLLBACK 과 COMMIT 문은 ATOMIC Compound 블럭내에서는 사용할 수 없습니다. SAVEPOINT문, ROLLBACK TO SAVEPOINT문, RELESE SAVEPOINT 문 등을 이용합니다 CREATE PROCEDURE bonus_incr () SPECIFIC bonus_incr LANGUAGE SQL bi: BEGIN DECLARE v_dept, v_actdept CHAR(3); DECLARE v_bonus DECIMAL(9,2); DECLARE v_deptbonus DECIMAL(9,2); DECLARE v_newbonus DECIMAL(9,2); DECLARE v_empno CHAR(6); DECLARE v_atend SMALLINT DEFAULT 0; DECLARE c_sales CURSOR WITH HOLD FOR SELECT workdept, bonus, empno FROM employee ORDER BY workdept; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_atend=1; DECLARE EXIT HANDLER FOR SQLEXCEPTION SET v_atend=1; OPEN c_sales; FETCH c_sales INTO v_dept, v_bonus, v_empno; nextdept:BEGIN IF v_atend = 0 THEN SAVEPOINT svpt_bonus_incr ON ROLLBACK RETAIN CURSORS; SET v_actdept = v_dept; SET v_deptbonus = 0; WHILE ( v_actdept = v_dept ) AND ( v_atend = 0 ) DO SET v_newbonus = v_bonus * 1.1; UPDATE employee SET bonus = v_newbonus WHERE empno = v_empno; SET v_deptbonus = v_deptbonus + v_newbonus; FETCH c_sales INTO v_dept, v_bonus, v_empno; END WHILE; IF v_deptbonus <= 3000.00 THEN COMMIT; ELSE ROLLBACK TO SAVEPOINT svpt_bonus_incr; RELEASE SAVEPOINT svpt_bonus_incr; END IF; GOTO nextdept; END IF; END nextdept; END bi

  13. 변수 선언, 초기화, 값 할당 • DECLARE 문을 사용하여 변수를 정의할 수 있습니다. 각 변수의 정의는 변수 이름, 데이터 유형 및 기본값으로 구성됩니다. 변수는 반드시 BEGIN~END사이의 블록에서 첫 부분에 선언되어야 합니다. 변수 이름은 SP에 사용된 테이블의 컬럼명과 구별되는 것이 좋습니다. 다음과 같이 접두어를 구별하여 매개 변수인지 일반 변수인지를 구별하는 것도 좋습니다. • v_ : 일반 변수 접두어 • p_ : 매개 변수 접두어 변수가 선언되면 NULL로 초기화 되며, DEFAULT절을 이용하여 특정 값으로 초기화할 수 있습니다. CREATE PROCEDURE proc_with_variables (IN p_empno CHAR(4), OUT p_msg VARCHAR(100)) SPECIFIC proc_with_vars LANGUAGE SQL BEGIN DECLARE v_rcount INTEGER; DECLARE v_name CHAR(10) DEFAULT ‘’; DECLARE v_adate,v_another DATE; DECLARE v_total INTEGER DEFAULT 0; -- Default값을 이용 SET v_total = v_total + 1; -- SET을 이용 SET v_name = ‘KIM’; SELECT MAX(EMPNO) INTO v_max FROM PLAYERS; -- Select를 이용 VALUES CURRENT DATE INTO v_adate; -- VALUES를 이용 VALUES CURRENT DATE, CURRENT DATE INTO v_adate,v_another; -- 레지스터를 이용 DELETE FROM EMPLOYEE WHERE EMPNO = ‘000100’; GET DIAGNOSTICS rcount = ROW_COUNT; -- GET DIAGNOSTICS을 이용

  14. SQLCODE, SQLSTATE, 반환용 변수 선언 SQLCODE와 SQLSTATE는 SQL문이 실행될 때마다 변경되는 값입니다. SQL문의 실행 결과의 성공 여부를 확인하기 위해 SQLCODE, SQLSTATE를 이용하려면 그 값을 저장할 로컬 변수를 선언합니다. SQLSTATE : ISO/ANSI SQL92 표준의 5자리 문자를 반환합니다. 00000, 00 : Successful completion 01 : Warning 02 : not found condition SQLCODE : Database Product별 CODE 를 반환합니다. 0 : Executed successfully + : Successful completion but warning message - : Error Occurred RETURN문장을 만나면 SP는 종료됩니다. OUT 모드로 선언된 매개 변수를 이용하여 값을 반환하려면 RETURN 문이 실행되기 전에 출력용 변수에 값을 할당해야 합니다. OUT 모드의 변수가 없어도 RETURN 문을 이용하여 기본적으로 한 개의 값을 반환할 수 있습니다. 명시적인 RETURN 문을 사용하지 않고, SP가 정상적으로 종료되면 0 이 반환됩니다. RETURN 문과 함께 반환될 변수를 선언합니다. CREATE PROCEDURE simple_error (IN p_empno CHAR(6), OUT p_msg VARCHAR(100)) SPECIFIC simple_error LANGUAGE SQL se: BEGIN DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; DECLARE SQLCODE INT DEFAULT 0; UPDATE employee SET midinit = ‘xx’ WHERE empno = p_empno; IF (SQLCODE <> 0) THEN SET p_msg = “update 오류”; END IF; SET p_msg = “update 성공”; END se CREATE PROCEDURE return_test ( IN p_empno CHAR(6), IN p_emplastname VARCHAR(15) ) SPECIFIC return_test LANGUAGE SQL rt: BEGIN DECLARE v_lastname VARCHAR(15); DECLARE v_ret INTEGER DEFAULT 1; SELECT lastname INTO v_lastname FROM EMPLOYEE WHERE empno = p_empno; IF v_lastname = p_emplastname THEN SET v_ret = 2; END IF; RETURN v_ret ; END rt

  15. 오류 CONDITION 선언 오류 처리를 위해 SP에서 사용할 수 있는 미리 정의된 시스템 Condition은 다음의 세 가지가 있습니다. • SQLEXCEPTION : SQLSTATE의 첫번째 두 자리가 00,01,02가 아닌 경우 • SQLWARNING : SQLSTATE의 첫번째 두 자리가 01 또는 SQLCODE가 양수인 경우( +100 제외) • NOT FOUND : SQLSTATE의 첫번째 두 자리가 02 또는 SQLCODE가 +100인 경우 DECLARE ~ CONDITION 문을 이용하여 사용자가 특정 SQLSTATE 에 대한 Condition을 정의할 수도 있습니다. • 시스템 사용 SQLSTATE : ‘0’ ~ ‘6’ or ‘A’ ~ ‘H’ 로 시작하는 Class code와 subclass code • 사용자 정의 SQLSTATE : ‘7’ ~ ‘9’ or ‘I’ ~ ‘Z’ 로 시작하는 Class code와 subclass code 시스템에 정의 Condition 또는 사용자 정의 Condition은 DECLARE~HANDLER문, SIGNAL문 등과 함께 사용되어 오류 발생시 처리 내역을 지정합니다, CREATE PROCEDURE insert_update_department () SPECIFIC ins_upd_dept LANGUAGE SQL iud: BEGIN DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; DECLARE SQLCODE INT DEFAULT 0; DECLARE v_duplicate INT DEFAULT 0; DECLARE v_num_rows INT DEFAULT 0; DECLARE c_duplicate CONDITION FOR SQLSTATE '23505'; DECLARE c_too_many_rows CONDITION FOR SQLSTATE '99001'; DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT SQLSTATE,SQLCODE INTO p_sqlstate_out,p_sqlcode_out FROM sysibm.sysdummy1; DECLARE CONTINUE HANDLER FOR c_duplicate SET v_duplicate = 1;

  16. CURSOR 선언 한 건 이상의 데이터를 조회한 결과를 보관하기 위해 커서를 선언합니다. COMMIT 이후에도 커서의 포인터를 유지하려면 WITH HOLD 옵션을 이용하면 됩니다. POSITIONED UPDATE와 DELETE 를 지원합니다. CURSOR 선언문에 CURRENT DATE, CURRENT TIME, CURRENT TIMESTAMP 레지스터를 포함한 경우, 모든 FETCH문에 동일한 값을 반환합니다. 레레지스터의 값은 OPEN CURSOR 에서 결정됩니다. CREATE PROCEDURE total_raise ( IN p_min DEC(4,2), IN p_max DEC(4,2), OUT p_total DEC(9,2) ) SPECIFIC total_raise LANGUAGE SQL tr: BEGIN DECLARE v_salary DEC(9,2); DECLARE v_bonus DEC(9,2); DECLARE v_comm DEC(9,2); DECLARE v_raise DEC(4,2); DECLARE v_job VARCHAR(15) DEFAULT 'PRES'; DECLARE SQLSTATE CHAR(5); DECLARE c_emp CURSOR FOR SELECT salary, bonus, comm FROM employee WHERE job != v_job; OPEN c_emp; SET p_total = 0; FETCH FROM c_emp INTO v_salary, v_bonus, v_comm; WHILE ( SQLSTATE = '00000' ) DO SET v_raise = p_min; IF ( v_comm < 2000 ) THEN SET v_raise = v_raise + 0.03; ELSEIF ( v_comm < 3000 ) THEN SET v_raise = v_raise + 0.02; ELSE SET v_raise = v_raise + 0.01; END IF; IF ( v_raise > p_max ) THEN SET v_raise = p_max; END IF; SET p_total = p_total + v_salary * v_raise; FETCH FROM c_emp INTO v_salary, v_bonus, v_comm; END WHILE; CLOSE c_emp; END tr 하나 이상의 행을 반환하는 경우에는 반드시 커서를 이용해야 합니다. 다음의 문장은 오류를 발생시킵니다. 첫 번째 한 건만 원하는 경우에는 커서를 정의하지 말고, FETCH FIRST 옵션을 이용하여 SELECT 하는 것이 좋습니다. SET v_c1 = (SELECT c1 FROM t1); -- 한 건 이상인 경우 오류 발생. SET v_c1 = (SELECT c1 FROM t1 FETCH FIRST 1 ROW ONLY); -- 한 건만 FETCH 하는 경우 효율적.

  17. 오류 HANDLER 처리 로직 정의 SP 내부에 SQL문을 실행하면서 발생되는 예외 사항에 대한 처리를 명시하는 방법입니다. 시스템에 미리 정의된 Condition 및 사용자가 정의한 Condition에 대해 Handler 유형을 지정하고, 상황에 대한 처리 방법을 정의할 수 있습니다. Handler 로직을 다중 문장으로 처리하는 경우에는 BEGIN ~ END 로 표시되는 Compound 블록을 사용합니다. Handler의 유형으로는 다음의 세 가지가 있습니다. • EXIT : Handler 내부의 SQL문을 실행하고, Compound 블럭의 끝부분을 실행합니다. • CONTINUE : Handler 내부의 SQL문을 실행하고, Exception이 발생한 다음 문장부터 실행을 계속합니다. • UNDO : Handler 내부의 SQL문을 실행하고, Compound 블럭내에서 실행된 모든 문장을 롤백한 후, Compound 블럭의 끝부분을 실행합니다. CREATE PROCEDURE simple_error (IN p_midinit CHAR, IN p_empno CHAR(6), OUT p_sqlstate_out CHAR(5), OUT p_sqlcode_out INT ) SPECIFIC simple_error LANGUAGE SQL se: BEGIN DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; DECLARE SQLCODE INT DEFAULT 0; DECLARE v_duplicate INT DEFAULT 0; DECLARE c_duplicate CONDITION FOR SQLSTATE '23505'; DECLARE c_too_many_rows CONDITION FOR SQLSTATE '99001'; DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT SQLSTATE,SQLCODE INTO p_sqlstate_out,p_sqlcode_out FROM sysibm.sysdummy1; DECLARE CONTINUE HANDLER FOR c_duplicate SET v_duplicate = 1; VALUES (SQLSTATE, SQLCODE) INTO p_sqlstate_out, p_sqlcode_out; UPDATE employee SET midinit = p_midinit WHERE empno = p_empno; END se

  18. Logic Flow Control 문 SP 본문의 로직을 구현하기 위해 사용되는 IF, CASE, GO TO, RETURN, WHILE, FOR, LOOP, ITERATE, LEAVE 문의 기본적인 사용 예는 다음과 같습니다. -- LOOP 문 L1: LOOP SELECT 1 INTO v_tmp FROM employee WHERE empno = v_current_id; SET v_salary = p_salary * 2 IF (SQLCODE <> 0) THEN LEAVE L1; END IF; END LOOP L1; -- FOR 문 SET fullname=‘’; FOR vl AS SELECT firstnme, midinit, lastname FROM employee DO SET fullname = lastname || ',' || firstnme ||' ' || midinit; INSERT INTO tnames VALUE (fullname); END FOR -- ITERATE / LEAVE 문 ins_loop: LOOP FETCH c1 INTO v_dept, v_deptname, v_admdept; IF at_end = 1 THEN LEAVE ins_loop; ELSEIF v_dept = 'D11' THEN ITERATE ins_loop; END IF; INSERT INTO department VALUES ('NEW', v_deptname, v_admdept); END LOOP ins_loop; -- RETURN 문 SELECT manager INTO v_manager FROM org WHERE empno = p_empno; IF v_manager = p_manager THEN RETURN 1; ELSE RETURN -1; END IF; -- IF 문 IF rating = 1 THEN SET salary = salary * 1.10; ELSEIF rating = 2 THEN SET salary = salary * 1.05; ELSE SET salary = salary * 1.03; END IF; -- CASE 문 CASE rating WHEN 1 THEN SET SALARY = SALARY *1.10; WHEN 2 THEN SET SALARY = SALARY *1.05; ELSE SET SALARY = SALARY *1.03; END CASE; -- GO TO 문 IF v_service > (CURRENT DATE - 1 year) THEN GOTO exit; END IF; SET v_new_salary = v_new_salary + 10; exit: SET p_adjusted_salary = v_new_salary; -- WHILE 문 WHILE (v_current <= p_end) DO SET v_temp = v_temp + v_current; SET v_current = v_current + 1; END WHILE;

  19. DYNAMIC SQL문 EXECUTE IMMEDIATE, PREPARE ~ EXECUTE 등의 동적 SQL문을 사용할 수 있습니다. 매개 변수 표시 문자인 ? 를 이용하여 PREPPARE와 EXECUTE를 분리시키면 동일한 SQL문을 다시 PREPARE 하지 않으므로 성능을 향상시킬 수 있습니다. 동적 SQL에 대한 보안 및 인증 관계는 실행시에 평가됩니다. 동적 SQL을 사용하기 위해 적절한 권한이 필요합니다. CREATE PROCEDURE change_mgr_bonus (IN p_bonus_increase DECIMAL, OUT p_num_changes INT ) SPECIFIC change_mgr_bonus LANGUAGE SQL cmb: BEGIN DECLARE v_dynSQL VARCHAR(200); DECLARE v_new_bonus DECIMAL; DECLARE v_no_data SMALLINT DEFAULT 0; DECLARE v_mgrno CHAR(6); DECLARE v_bonus DECIMAL; DECLARE v_stmt1 STATEMENT; DECLARE c_managers CURSOR FOR SELECT e.empno, e.bonus FROM EMPLOYEE e, DEPARTMENT d WHERE e.empno=d.mgrno; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_no_data=1; SET v_dynSQL = 'UPDATE EMPLOYEE SET BONUS=? WHERE EMPNO=?'; SET p_num_changes=0; PREPARE v_stmt1 FROM v_dynSQL; OPEN c_managers; FETCH c_managers INTO v_mgrno, v_bonus; WHILE (v_no_data=0) DO SET p_num_changes = p_num_changes + 1; SET v_new_bonus = v_bonus + p_bonus_increase; EXECUTE v_stmt1 USING v_new_bonus, v_mgrno; FETCH c_managers INTO v_mgrno, v_bonus; END WHILE; CLOSE c_managers; END cmb

  20. GET DIAGNOSTICS 문 GET DIAGNOSTICS 문은 실행한 SQL 문장과 연관된 정보를 수집하는데 사용합니다. ROW_COUNT 변수는 SELECT,INSERT,UPDATE,DELETE 등의 실행 후 관련된 레코드 건수를 반환하며, MESSAGE_TEXT는 오류 메시지를 반환합니다. RETURN_STATUS 를 이용하면 SP를 실행한 후의 반환 코드를 확인할 수 있습니다. CREATE PROCEDURE get_diag (IN p_empno CHAR(6)) SPECIFIC get_diag LANGUAGE SQL gd: BEGIN DECLARE v_rows INT DEFAULT -1; DELETE FROM employee WHERE empno like p_empno || '%'; GET DIAGNOSTICS v_rows = ROW_COUNT; RETURN v_rows; END gd CREATE PROCEDURE TESTIT (IN p_empno VARCHAR(6)) LANGUAGE SQL BEGIN DECLARE v_retval INTEGER DEFAULT 0; DECLARE v_note VARCHAR(20); CALL get_diag (p_empno); GET DIAGNOSTICS v_retval = RETURN_STATUS; SET v_note = EMPNO || ':[' || CHAR(v_retval) || ']'; INSERT INTO NOTE VALUES (v_note); END CREATE PROCEDURE simple_error_message ( IN p_midinit CHAR(10), IN p_empno CHAR(6), OUT p_error_message VARCHAR(300) ) SPECIFIC simple_error_msg LANGUAGE SQL sem: BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING GET DIAGNOSTICS EXCEPTION 1 p_error_message = MESSAGE_TEXT; SET p_error_message = ''; UPDATE employee SET midinit = p_midinit WHERE empno = p_empno; END sem SQL0727N An error occurred during implicit system action type "1". Information returned for the error includes SQLCODE "-204", SQLSTATE "42704" and message tokens "DB2ADMIN.EMPLOYEE". SQLSTATE=56098

  21. SIGNAL 문 시스템이 제공하는 SQL 오류 이외에 프로그램에서 사용자가 정의한 오류 Condition에 대한 Handler를 실행해야 하는 경우가 있습니다. SIGNAL 문을 이용하여 사용자가 정의한 Condition 또는 SQLSTATE에 해당하는 처리 내역을 지정할 수 있습니다. -- Initialize output parms VALUES (SQLSTATE, SQLCODE) INTO p_sqlstate_out,p_sqlcode_out; -- See how many rows are already in the DEPARTMENT table SELECT COUNT(1) INTO v_num_rows FROM department; -- Signal an error if more than 10 rows exist IF v_num_rows > 10 THEN SIGNAL c_too_many_rows SET MESSAGE_TEXT = 'DEPARTMENT 에 데이터가 너무 많습니다.'; END IF; -- Try insert, if duplicate, then update INSERT INTO department ( deptno,deptname,mgrno,admrdept,location ) VALUES ( p_deptno,p_deptname,p_mgrno,p_admrdept,p_location); IF v_duplicate = 1 THEN -- only update if non-null value provided as input parameter UPDATE department SET deptname = coalesce(p_deptname, deptname) ,mgrno = coalesce(p_mgrno, mgrno) ,admrdept = coalesce(p_admrdept, admrdept) ,location = coalesce(p_location, location) WHERE deptno = p_deptno; END IF; END iud CREATE PROCEDURE insert_update_department ( IN p_deptno CHAR(3), IN p_deptname VARCHAR(29), IN p_mgrno CHAR(6), IN p_admrdept CHAR(3), IN p_location CHAR(16), OUT p_sqlstate_out CHAR(5), OUT p_sqlcode_out INT ) SPECIFIC ins_upd_dept LANGUAGE SQL iud: BEGIN -- Declare variables DECLARE SQLSTATE CHAR(5) DEFAULT '00000'; DECLARE SQLCODE INT DEFAULT 0; DECLARE v_duplicate INT DEFAULT 0; DECLARE v_num_rows INT DEFAULT 0; -- Declare condition DECLARE c_duplicate CONDITION FOR SQLSTATE '23505'; DECLARE c_too_many_rows CONDITION FOR SQLSTATE '99001'; -- Declare handlers DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT SQLSTATE,SQLCODE INTO p_sqlstate_out,p_sqlcode_out FROM sysibm.sysdummy1; DECLARE CONTINUE HANDLER FOR c_duplicate SET v_duplicate = 1;

  22. TEMPORARY TABLE Temporary Table은 한 세션 동안에만 존재하는 임시 테이블입니다. Connection이 종료되면 선언된 임시 테이블은 자동으로 제거됩니다. Temporary Table을 선언하기 위해서는 User Temporary Tablespace 가 필요합니다. Temporary Table은 특정 Session 에서만 액세스할 수 있습니다. 동일한 이름을 가진 Temporary Table을 여러 세션에서 동시에 독립적으로 액세스합니다. 테이블의 스키마명은 세션이나 사용자에 관계없이 “SESSION”을 사용합니다. CREATE PROCEDURE temp_table_insert (IN parm1 INTEGER, IN parm2 INTEGER ) LANGUAGE SQL BEGIN DECLARE v_column1 INTEGER DEFAULT 0; DECLARE v_column2 INTEGER DEFAULT 0; IF (1 = 0) THEN DECLARE GLOBAL TEMPORARY TABLE ttt(column1 INT, column2 INT) NOT LOGGED; END IF; INSERT INTO session.ttt(column1, column2) VALUES ( parm1+3, parm2+3); INSERT INTO session.ttt(column1, column2) VALUES ( parm1+4, parm2+4); BEGIN DECLARE cursor1 CURSOR FOR SELECT * FROM session.ttt; OPEN cur1 ; FETCH FROM cursor1 INTO v_column1, v_column2; WHILE (result_set_end = 0) DO INSERT INTO note99 VALUES ( v_column1, v_column2 ); FETCH FROM cursor1 INTO v_column1, v_column2; END WHILE; CLOSE cursor1; END; END CREATE USER TEMPORARY TABLESPACE usertempspace MANAGED BY SYSTEM USING (‘/sqlsp/usertempspace') • CREATE TABLE NOTE99 ( F1 int, F2 int) • CALL temp_table_insert (10,20) • SELECT * FROM note99 • F1 F2 • ----- ----- • 23 • 14 24

  23. SECURITY 제어 특정 SP에 대한 제거 및 실행 권한은 기본적으로 SP를 생성한 사용자, DBADM, SYSADM 에게 있습니다. 다른 사용자가 해당 SP를 실행하게 하려면 그 ROUTINE 에 대한 EXECUTE 권한을 사용자 또는 그룹별로 부여해야 합니다. $ db2 connect to smaple user inst01 using inst01 $ db2 "call inst01.sp01('000340')“ 리턴 상태 = 1 $ db2 connect to smaple user user01 using user01 $ db2 "call inst01.sp01('000340')" SQL0551N "USER01"에는 오브젝트 "INST01.SP01"에서 조작 "EXECUTE"을(를) 수행할 수 있는 특권이 없습니다. SQLSTATE=42501 $ db2 "select grantor, grantee, executeauth from syscat.routineauth where specificname = 'SP01'" GRANTOR GRANTEE EXECUTEAUTH ---------------------------------------------------------------------------- SYSIBM YURIMAMA G $ db2 connect to smaple user inst01 using inst01 $ db2 grant execute on procedure inst01.sp01 to user01 $ db2 "select grantor, grantee, executeauth from syscat.routineauth where specificname = 'SP01'" GRANTOR GRANTEE EXECUTEAUTH ---------------------------------------------------------------------------- SYSIBM INST01 G INST01 USER01 Y $ db2 connect to smaple user user01 using user01 $ db2 "call inst01.sp01('000340')" 리턴 상태 = 1

More Related