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 过程。
----------------------------------------------------------
《高性能SQL调优精要与案例解析》
blog1:http://www.cnblogs.com/lhdz_bj
blog2:http://blog.itpub.net/8484829
blog3:http://blog.csdn.net/tuning_optmization