DB2開發系列之二——SQL過程

1、SQL 過程的結構

1）SQL過程的結構

CREATE PROCEDURE proc_name
   IN, OUT, INOUT parameters
   optional clauses
   SQL procedure body - compound statement

2）示例

--not atomic：不回滾；

CREATE TABLE t1 (c1 INT, c2 CHAR(5))!

CREATE PROCEDURE my_proc1 ()

SPECIFIC not_atomic_example

P1: BEGIN NOT ATOMIC

INSERT INTO t1 VALUES(1, 'FIRST');--(1)

-- SIGNAL SQLSTATE TO INFORCE ERROR SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = 'INFORCE ERROR';

INSERT INTO t1 VALUES (2,'SECND'); --(2)

END P1

 

--atomic：回滾；
CREATE PROCEDURE my_proc2 ()
SPECIFIC atomic_example         
P1: BEGIN ATOMIC
   INSERT INTO t1 VALUES(3, 'THIRD');    --(1)
   -- SIGNAL SQLSTATE TO INFORCE ERROR
   SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = 'INFORCE ERROR';                           
   INSERT INTO t1 VALUES (4,'FOUR');      --(2)
END P1

2、複合 SQL 語句與變數的作用域

--示例

CREATE PROCEDURE VAR_SCOPE (  )              
L1:BEGIN
    DECLARE v_outer1 INT;
    DECLARE v_outer2 INT;
    L2:BEGIN
        DECLARE v_inner1 INT;
        DECLARE v_inner2 INT;
        SET v_outer1 = 100;   --(1) -- success
        SET v_inner1 = 200;
    END L2;
    SET v_outer2 = 300;
    SET v_inner2 = 400;    --(2)  -- fail
END L1

3、巢狀的儲存過程

1）呼叫巢狀過程

--示例

CREATE PROCEDURE NESTA (p1 int, p2 char(10), OUT p3 INT)
BEGIN
 SQL Statements
END
 

DECLARE v_v1 varchar(10);
DECLARE v_res INT default 0;

--- SQL statements and variable assignments

CALL nesta(10, v_v1, v_res);

2）獲取返回碼

--語法

 GET DIAGNOSTICS ret_code = DB2_RETURN_STATUS;

 --示例

CREATE PROCEDURE TEST1(out v1 int)
begin
  set v1 = 10;
  return;  
end

CREATE PROCEDURE TEST2(out v1 int)
begin
  set v1 = 5;
  return 2;  
end
 

CREATE PROCEDURE NEST_DIAGN (out ret_code1 int, out ret_code2 int  )
P1: BEGIN
 DECLARE val1 INT default 0;
 call test2(val1);
 GET DIAGNOSTICS ret_code1 = DB2_RETURN_STATUS;
 call test1(val1);
 GET DIAGNOSTICS ret_code2 = DB2_RETURN_STATUS; 
END P1
 
C:\Program Files\IBM\SQLLIB\BIN>db2 call nest_diagn(?,?)

  Value of output parameters
  --------------------------
  Parameter Name  : RET_CODE1
  Parameter Value : 2

  Parameter Name  : RET_CODE2
  Parameter Value : 0

  Return Status = 0

3、在儲存過程之間共享資料

--示例

CREATE PROCEDURE result_from_cursor (deptin int)
 DYNAMIC RESULT SETS 1
P1: BEGIN
 -- Declare cursor
 DECLARE cursor1 CURSOR WITH RETURN FOR
  SELECT a.name, a.job, COALESCE(a.comm,0), b.location
   FROM staff a, org b
   where a.dept = b.deptnumb
    AND  a.dept = deptin;

 OPEN cursor1;
END P1
 

CREATE PROCEDURE Use_nested_cursor (deptin int, OUT tot_dept_comm DEC(12,2))
  BEGIN
   
    DECLARE sqlcode int default 0;
    DECLARE v_comm DECIMAL(12,2) DEFAULT 0.0;
    DECLARE v_name, v_location varchar(20);
    DECLARE v_job char(6);
    
    DECLARE LOC1 RESULT_SET_LOCATOR VARYING;  
    
    SET tot_dept_comm = 0;  
    CALL result_from_cursor(deptin);

    ASSOCIATE RESULT SET LOCATOR( LOC1) WITH PROCEDURE result_from_cursor; 
    ALLOCATE C1 CURSOR FOR RESULT SET LOC1;

    FETCH FROM C1 INTO v_name,v_job,v_comm,v_location;
    WHILE sqlcode = 0 DO
            SET tot_dept_comm = tot_dept_comm + v_comm;            
      FETCH FROM C1 INTO v_name,v_job,v_comm,v_location;
    END WHILE;
 END
  

> call use_nested_cursor (51,?)

Value of output parameters 
-------------------------- 
  Parameter Name: TOT_DEPT_COMM 
  Parameter Value: 2333.40
 

4、透過全域性變數共享資料

--示例

CREATE VARIABLE var_name DATATYPE [DEAFULT value];

CREATE VARIABLE global_var_count INTEGER default 0;

CREATE PROCEDURE project_count (IN var_respemp CHAR(6))   
BEGIN 
  SELECT COUNT(*)
    INTO global_var_count
    FROM   project
    WHERE  respemp = var_respemp;
END

CREATE PROCEDURE PROJECT_STATUS (IN p_respemp CHAR(6),OUT p_new_status CHAR(20))
BEGIN
 CALL project_count(p_respemp);
 IF  global_var_count > 2
 THEN
      SET p_new_status = 'Maximum projects' ;    
 ELSE
      SET  p_new_status  = 'Available';
 END IF;
END

5、測試和部署儲存過程

--指令碼myscript.db2內容

CREATE PROCEDURE NUMBER_OF_ORDERS ( in_status varchar(10), in_date DATE,
                                   out num_of_order int)
 
------------------------------------------------------------------------
-- SQL Procedure 
------------------------------------------------------------------------
P1: BEGIN
 
 declare v_number INTEGER DEFAULT 0;
 
  SELECT count(poid)
  INTO v_number
    FROM PURCHASEORDER
    where ucase(status) = ucase(in_status)
      and orderdate < in_date;
    
    SET  num_of_order = v_number;   

END P1 @
 

--部署過程

 db2 -td@ -vf myscript.db2

 --語法

db2 -td <terminating-character> -vf <CLP-script-name>

 --呼叫

db2 call number_of_orders('Shipped',current date, ?)

5、 保證 SQL 過程的安全 

1）授權

       i)要建立一個 SQL 過程，執行該任務的 userid 需要有資料庫上的 BINDADD 授權以及資料庫上的 IMPLICIT_SCHEMA 授權（如果儲存過程的模式還不存在）或者模式上的 CREATE_IN 授權（如果儲存過程的模式已經存在）。他們還需要執行在儲存過程主體中定義的 SQL 所需的所有許可權。

      ii)要呼叫一個 SQL 過程，執行該任務的 userid 需要有這個儲存過程上的 EXECUTE 許可權。

      iii)建立 SQL 過程的 userid 自動獲得 EXECUTE 許可權和 GRANT EXECUTE 許可權。此外，擁有 DBADM 或 SYSADM 許可權的使用者也可以建立或呼叫一個 SQL 過程。通常建議由資料庫管理員（DBA）建立儲存過程，而由需要它的應用程式開發人員來呼叫它。

2)SQL 過程中的 SQL 訪問級別

      i)NO SQL：儲存過程中不能有 SQL 語句；

      ii)CONTAINS SQL：儲存過程中不能有可以修改或讀資料的 SQL 語句 ；

      iii)READS SQL：儲存過程中不能有可以修改資料的 SQL 語句；

      iv)MODIFIES SQL：儲存過程中的 SQL 語句既可以修改資料，也可以讀資料；

6、加密 SQL 過程

1）GET ROUTINE 是一個 DB2 命令，它從資料庫中提取一個 SQL 過程，並將它轉換成一個 SAR（SQL Archive）檔案，後者可以傳送給客戶。GET ROUTINE 命令上的HIDE BODY 子句確保 SQL 過程的主體不被提取，從而加密儲存過程。

2）PUT ROUTINE 是一個 DB2 命令，它根據透過 GET ROUTINE 提取的 SAR 檔案，在資料庫中建立 SQL 過程。