DB2 9.5 SQL Procedure Developer 认证考试 735 准备，第2部分

核心提示：现在，如果以部门 “51” 作为输入参数执行这个存储过程，DB2 9.5 SQL Procedure Developer 认证考试 735 准备，第2部分(7)，将得到整个部门的佣金：清单 15.部门 51 作为输入参数时的结果> call use_nested_cursor (51,?)

现在，如果以部门 “51” 作为输入参数执行这个存储过程，将得到整个部门的佣金：

清单 15.部门 51 作为输入参数时的结果

> call use_nested_cursor (51,?)
Value of output parameters
--------------------------
　Parameter Name: TOT_DEPT_COMM
　Parameter Value: 2333.40

通过全局变量共享数据

DB2支持会话全局变量。会话全局变量 与一个特定的会话相关联，它对于这个会话中的每个存储过程都是全局的，并包含一个对于这个会话是惟一的值。

下面的图展示了会话全局变量的语法：

清单 16. CREATE VARIABLE语法

CREATE VARIABLE var_name DATATYPE [DEAFULT value];

请注意，会话全局变量是在存储过程之外声明的，就像任何其他数据库对象一样。

下面的脚本演示了全局变量的使用：

清单 17. 全局变量的使用

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

测试和部署存储过程

DB2支持命令行处理器（CLP），它是一个部署（构建）和测试（执行）存储过程的界面。

构建存储过程的用户应该满足以下需求：

必须有执行 CREATE PROCEDURE语句的权限

必须有执行存储过程中所有SQL语句的权限

这个SQL过程中引用的所有数据库对象（例如表、视图、函数和其他过程）都应该存在数据库中。

必须成功地建立从CLP到数据库的连接（这可以通过使用以下db2命令来完成：db2 connect to sample user userid using password ）

SQL过程中的每个语句都需要一个语句终止符。默认的语句终止符是分号（;）。此外，为了能在一个脚本中创建SQL过程，并让 CLP 知道 SQL过程在哪里结束，还需要另一个终止字符。最常用的终止字符是 “at”（_cnnew1@）和惊叹号（!）字符。

接下来，我们编写一个脚本，其中包含一个简单的SQL过程，并以 “@” 终止字符结束：

清单 18. 以 @ 终止符结束的SQL过程

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 @

这个脚本被保存为文件 myscript.db2。为了从DB2 CLP构建存储过程number_of_orders，需要执行以下命令：

db2 -td@ -vf myscript.db2

这个CLP命令的一般语法如下：

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