1. 首页
  2. IT资讯

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 FilesIBMSQLLIBBIN>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 过程。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8484829/viewspace-2116705/,如需转载,请注明出处,否则将追究法律责任。

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/185040.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code