1、DB2 9.5 SQL Procedure Developer第 1 部分: SQL Procedural Language 入门变量声明SQL 过程允许使用本地变量赋予和获取 SQL 值，以支持所有 SQL 逻辑。在 SQL 过程中，在代码中使用本地变量之前要先进行声明。清单 1 中的图演示了变量声明的语法：清单 1. 变量声明的语法.-,-. V | |-DECLARE-SQL-variable-name-+-.-DEFAULT NULL-. -+-data-type-+-+-+-| -DEFAULT-constant- | SQL-variable-name 定义本地变量的名称。该名称不
2、能与其他变量或参数名称相同，也不能与列名相同。 图 1 显示了受支持的 DB2 数据类型：图 1. DB2 数据类型DEFAULT 值 如果没有指定，在声明时将赋值为 NULL。下面是变量声明的一些例子： DECLARE v_salary DEC(9,2) DEFAULT 0.0; DECLARE v_status char(3) DEFAULT YES; DECLARE v_descrition VARCHAR(80); DECLARE v1, v2 INT DEFAULT 0; 请注意，从 DB2 version 9.5 开始才支持在一个 DECLARE 语句中声明多个相同数据类型的变量。
3、数组数据类型SQL 过程从 9.5 版开始支持数组类型的变量和参数。要定义一个数组类型的变量，需要先在数据库中创建该类型，然后在过程或应用程序中声明它。数组是临时的值，可以在存储过程和应用程序中操纵它，但是不能将它存储到表中。DB2 支持以下创建数组数据类型的语法：清单 2. 创建数组数据类型的语法-CREATE TYPEarray-type-name-AS-| data-type |-ARRAY-.-2147483647-. -+-+-DECLARE-cursor-name-CURSOR-FOR-+-select-statement-+- 30 THEN SET gl_sal_increas
4、e = 15000;ELSEIF years_of_serv 20 THENSET gl_sal_increase = 12000; ELSE SET gl_sal_increase = 10000;END IF; CASE 语句SQL PL 支持两种类型的 CASE 语句，以根据一个条件的状态实现逻辑的分支： simple CASE 语句用于根据一个字面值进入某个逻辑。 searched CASE 语句用于根据一个表达式的值进入某个逻辑。 清单 12 显示了使用 searched CASE 语句的一个存储过程的例子。清单 12. 使用 searched CASE 语句的存储过程CREATE
5、PROCEDURE sal_increase_lim1 (empid CHAR(6)BEGINDECLARE years_of_serv INT DEFAULT 0;DECLARE v_incr_rate DEC(9,2) DEFAULT 0.0;回页首SELECT YEAR(CURRENT DATE) - YEAR(hiredate)INTO years_of_servFROM empl1WHERE empno = empid;CASE WHEN years_of_serv 30 THEN SET v_incr_rate = 0.08;WHEN years_of_serv 20 THEN S
6、ET v_incr_rate = 0.07; WHEN years_of_serv 10 THEN SET v_incr_rate = 0.05;ELSESET v_incr_rate = 0.04; END CASE; UPDATE empl1 SET salary = salary+salary*v_incr_rateWHERE empno = empid; END 迭代语句SQL PL 支持一些重复执行某个逻辑的方法，包括简单的 LOOP、WHILE 循环、REPEAT 循环和 FOR 循环： LOOP 循环 - 简单的循环 o L1: LOOP o SQL statements; o
7、LEAVE L1; o END LOOP L1; WHILE 循环 - 进入前检查条件 o WHILE condition o DO o SQL statements 回页首o END WHILE; REPEAT 循环 - 退出前检查条件 o REPEAT o SQL statements; o UNTIL condition o END REPEAT; FOR 循环 - 结果集上的隐式循环 o FOR loop_name AS o SELECT FROM o DO o SQL statements; o END FOR; 请注意，FOR 语句不同于其他的迭代语句，因为它用于迭代一个定义好的结
8、果集中的行。为了演示这些循环技巧的使用，我们来编写一个过程，该过程从一个 EMPLOYEE 表中获取每个雇员的姓氏、工作年限和年龄，并将其插入到新表 REPORT_INFO_DEPT 中，这些信息分别被声明为 lname varchar(15)、hiredate date 和 birthdate date。请注意，使用一个简单的 SQL 语句也可以做同样的事情，但是在这个例子中我们使用 3 种不同的循环语句。清单 13. 简单的循环例子CREATE PROCEDURE LEAVE_LOOP (DEPTIN char(3), OUT p_counter INTEGER)Ll: BEGINDECL
9、ARE v_at_end , v_counter INTEGER DEFAULT 0;DECLARE v_lastname VARCHAR(15);DECLARE v_birthd, v_hired DATE;DECLARE c1 CURSORFOR SELECT lastname, hiredate, birthdate FROM employeeWHERE WORKDEPT = deptin;DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_at_end = 1;OPEN c1;FETCH_LOOP: LOOPFETCH c1 INTO v_last
10、name, v_hired, v_birthd;IF v_at_end .-VALUE-. .-SQLSTATE-+-+-. -+-+-string-constant-|下面是条件声明的例子：DECLARE FOREIGN_KEY_VIOLATION CONDITION FOR SQLSTATE 23503;DECLARE overflow CONDITION FOR SQLSTATE 22003;回页首DECLARE 条件处理程序如果发生一个错误，存储过程的行为是根据条件处理程序来决定的。在一个存储过程中，可以为一个普通的或有名称的条件和特定的 SQLSTATE 声明一个或多个条件处理程序。
11、当一个 SQL 语句产生一个 SQLEXCEPTION 或 SQLWARNING（SQLCODE +-EXIT-+ -UNDO- -+-specific-condition-value-+-| SQL-procedure-statement |-|-general-condition-value- WHERE specific-condition-value.-,-. V .-VALUE-. | |-+-SQLSTATE-+-+-string-constant-+-+-|-condition-name- 下面是演示它如何工作的一些例子。在下面的过程中，如果 UPDATE 语句失败，则控制被转移
12、到 EXIT 处理程序。结果，该过程被终止，但是它的输出参数包含 SQLCODE 和 SQLSTATE 的值。清单 18. 返回 SQLCODE 和 SQLSTATE 的处理程序的例子CREATE PROCEDURE simple_error (IN new_job CHAR(8), IN p_empno CHAR(6), OUT p_state_out CHAR(5),OUT p_code_out INT)SPECIFIC simple_error1BEGINDECLARE SQLCODE INT DEFAULT 0;DECLARE SQLSTATE CHAR(5) DEFAULT 0000
13、0;DECLARE EXIT HANDLER FOR SQLEXCEPTIONSELECT SQLSTATE, SQLCODE INTO p_sqlstate_out, p_sqlcode_outFROM SYSIBM.SYSDUMMY1;UPDATE EMPLOYEE SET job = new_job WHERE empno = p_empno;END请注意，SQLCODE 和 SQLSTATE 应该被显式地声明为本地变量。清单 19 演示了一个场景，在此场景中，当一个给定的错误发生时，不是退出过程，而是继续某个动作。为了理解这个例子，请注意表 TAB1 被定义为（col1 int, co
14、l2 char(5)），在默认情况下，如果发生值溢出，DB2 不是截短一个值，而是产生 SQLSTATE 22001。清单 19. 具有 CONTINUE 处理程序的过程CREATE PROCEDURE proc1 (IN num int, IN new_status varchar(10) P1: BEGINDECLARE SQLCODE INTEGER default 0;DECLARE SQLSTATE CHAR(5) default ;DECLARE v_trunc INTEGER default 0; DECLARE overflow CONDITION FOR SQLSTATE 2
15、2001;DECLARE CONTINUE HANDLER FOR overflow BEGININSERT INTO tab1 VALUES (num, substr (new_sataus,1,5);SET v_trunc = 2; END; INSERT INTO tab1 VALUES(num, new_status);RETURN v_trunc;END P1 如果以 Too many 作为输入参数 new_status 的值调用这个过程，那么在 INSERT 语句执行期间会产生 SQLSTATE 22001，控制被转移到 CONDITION HANDLER。结果，v_trunc 指
16、示符将被设置为 2，新行将被插入到 TAB1 表中，插入时对 COL2 列使用了截短后的值，该过程最终成功完成。强制发出异常 - SIGNAL SQLSTATESQL PL 支持发出一个错误或警告条件。这导致一个具有指定 SQLSTATE 的错误或警告被返回，同时返回的还有可选的消息文本。清单 20 显示了 SIGNAL 语句的语法。清单 20. SIGNAL 语句的语法-SIGNAL-.-VALUE-. -+-SQLSTATE-+-+-+-sqlstate-string-constant-+-+-| -variable-name- | -condition-name- -+-+- 5 THE
17、N SIGNAL SQLSTATE 72001 SET MESSAGE_TEXT = INPUT VALUE TOO LONG;END IF; INSERT INTO TAB1 VALUES (num, new_status); END P1第 2 部分: DB2 SQL 过程存储过程什么是存储过程？存储过程 （也称一个例程）是一个数据库对象，其中包含一系列直接在数据库服务器上执行的 SQL 语句和应用程序控制逻辑，可以制定业务逻辑。业务逻辑可以被封装在存储过程中，后者则可以被客户机应用程序、其他存储过程、用户定义函数或触发器通过一条 SQL 语句来调用。存储过程可以接受参数值，并根据输入数据
18、更改业务逻辑的行为。然后，存储过程可以将输出值返回给调用者，或者将多个结果集返回给调用者。存储过程可以使用 SQL Procedure Language（SQL PL）或编程语言（比如 Java 或 C）来实现。存储过程可以从以下几个方面显著提高分布式应用程序（在远程系统上运行的应用程序）的性能： 减少网络传输量 减少应用程序开发人员编写代码的工作 为从分布式客户机调用远程存储过程提供一种简单的方式 图 1 显示了存储过程是有很用的：图 1. 通过使用存储过程减少网络传输量使用存储过程的优点存储过程有以下优点： 简化代码重用、代码标准化和代码维护： o 当一系列不同的应用程序都需要执行一项相似
19、的任务时，通过编写一个执行这个任务的存储过程，让每个客户机应用程序调用该存储过程以执行任务，这样就可以很方便地实现这些应用程序。如果需要修改任务，那么只需修改受影响的存储过程。如果不使用存储过程的话，就需要修改每个应用程序。 对其他数据库对象的受控制的访问： o 没有权限访问特定的数据库对象或一个数据库上的操作（例如创建一个表），但是又想在那个对象上执行动作或执行一个操作的用户，可以通过调用他有权限运行的一个存储过程来实现他的目的。这意味着权限管理得以简化。 提高应用程序的性能： o 对于远程应用程序，需要逐条语句地通过网络传输每条发出的 SQL 语句。这可能导致大量的网络传输。通过将所有这些
20、语句添加到一个存储过程中，客户机应用程序只需一次网络请求便可调用那个存储过程。这显然大大减少了所需的网络传输量，从而提高了应用程序的总体性能。 更有效的 SQL： o 由于存储过程存放在数据库服务器上，实际上它们本身就是数据库对象，它们可以比客户机应用程序更有效地执行，因为 SQL 请求被更高效地传输。使用嵌入式 SQL 操作的存储过程还拥有已经存储在包中的访问计划，因而可以提高执行每条语句的速度。如果存储过程在创建时使用了 NOT FENCED 子句，那么它与数据库管理器是在同一个进程空间中运行的，因此可以在共享内存中进行通信。 增强的功能： o 由于存储过程存放在数据库服务器上，因此很容易
21、比应用程序获得更多可使用的内存和磁盘空间。而应用程序则可以访问安装在数据库服务器上的软件。 互操作性： o 不同的程序员可以用不同的编程语言实现不同的代码模块。为了帮助实现具有逻辑互操作性的代码重用，存储过程本身可以用不同的语言编写。用不同语言编写的客户机应用程序可以调用用不同语言编写的存储过程（例如，以 C+ 编写的应用程序可以调用以 Java 编写的存储过程）。而且?以不同语言编写的存储过程可以相互调用（例如，以 C 编写的存储过程可以调用以 Java 编写的存储过程）。客户机的操作系统和存储过程所在的服务器的操作系统也可以不同（例如，一个 Windows 客户机应用程序可以调用一个在 A
22、IX 上运行的存储过程）。 存储过程的局限性 调用存储时应具有互操作性，以使客户机应用程序可以兼容用于编写存储过程的不同的编程语言。但是，用特定语言编写的外部存储过程只能在某些平台上运行。例如，CLR 存储过程只能在基于 Windows 的平台上运行。虽然可以在任何地方调用这种存储过程，但是，如果要将服务器迁移到不同的平台（例如 Solaris 或 AIX），那么就需要重新编写这种存储过程。对于用 C、 C+ 或 COBOL 编写的存储过程也是如此，在新的服务器上，这些存储过程需要重新编译。而 SQL 过程则不存在这种问题。虽然 Java 存储过程也较为灵活，但是新的数据库服务器需要安装一个
23、Java 虚拟机（JVM）才能执行这种存储过程。 惟一用于调用一个存储过程的 SQL 语句是 CALL 语句。CALL 语句可以在应用程序、存储过程、用户定义函数或触发器中使用。 存储过程可以嵌套，一个存储过程可以调用另一个存储过程，后者又可以调用其他存储过程。在 DB2 v9.5 中，一次最多可以嵌套 64 个存储过程。 存储过程不能在不同的调用之间保存状态。 存储过程的输出参数不能直接被另一条 SQL 语句使用。调用接口需要先将输出参数赋给某个变量，然后才可以在另一条 SQL 语句中使用该变量。 存储过程不支持可滚动游标。 外部存储过程与 SQL 存储过程之间的不同点DB2 支持两种类型的
24、存储过程。一种是外部存储过程，另一种是 SQL 过程。 外部存储过程在数据库之外的应用程序中用编程语言定义逻辑。这种类型的存储过程的可执行文件或库存在于数据库服务器所安装到的文件系统中。和 SQL 过程一样，外部存储过程也要注册到数据库，但是在注册过程中，需要指定存储过程可执行文件或库的位置。 DB2 支持用各种不同的编程语言编写的外部存储过程，包括 C、C+、COBOL 、Java 和 .NET（也称 CLR 存储过程）。 以下是外部存储过程独有的特性： 外部存储过程允许访问非数据库接口，例如文件系统或应用程序。即使这些资源不是数据库系统的一部分，外部存储过程仍可以使用它们。例如，一个外部存
25、储过程可以在一个 UNIX 数据库服务器上执行一个 shell 脚本，以执行一项特定的任务。 外部存储过程使用参数风格来确定用于该存储过程的编程语言如何使用输入、输出或输入/输出参数。有些参数风格允许在一个称作 dbinfo 的结构中使用对存储过程有用的传递元数据信息，例如数据库和存储过程属性信息。 外部存储过程可以被定义为 FENCED 或 NOT FENCED。这决定了存储过程应该与数据库管理器在同一个地址空间中运行（NOT FENCED），还是应该在它自己的进程中运行（FENCED）。被定义为 NOT FENCED 的存储过程可以执行得稍微快一点，因为不需要使用共享内存段通信；但是，这种
26、存储过程的风险也更大。未隔离的存储过程如果遇到一个问题，则可能导致整个数据库服务器崩溃，因为它与 DB2 使用相同的地址空间。Java 存储过程必须被定义为 FENCED；不过，它们可以被定义为 THREADSAFE 或 NOT THREADSAFE。 下面是 SQL 过程独有的特性： SQL 过程只能使用一种称作 SQL Programming Language （SQL PL）的语言来编写。关于这种语言的更多信息，可以在本系列的前一篇教程中找到（参见 参考资料）。因此，外部存储过程与 SQL 过程主要的区别在于外部过程是使用一种特定的编程语言编写的，而 SQL 过程是只能使用 SQL 语句
27、编写。 SQL 过程存放在真正的数据库中。外部存储过程依赖于文件系统中的一个外部库或可执行文件，而 SQL 过程是数据库的一部分。 构建 SQL 过程不需要编译器，也不需要对特定的编程语言有深入的理解。所以，SQL 过程的开发可能更快。 SQL 过程总是被定义为 NOT FENCED。这种类型的存储过程存在的风险更小，因为这种存储过程只能执行 SQL 操作，所以数据库服务器面临的风险是有限的。 SQL 过程的可移植性更好。由于它们不依赖于特定的编程语言，每个数据库服务器上也就不需要用于这种语言的编译器或解释器，所以必要时更容易在每个服务器上重新创建这些存储过程。 SQL 过程的结构SQL 过程
28、的结构由 CREATE PROCEDURE 语句、参数和复合语句组成。下面的伪图显示了 SQL 过程的结构：清单 1. SQL 过程的结构CREATE PROCEDURE proc_nameIN, OUT, INOUT parametersoptional clausesSQL procedure body - compound statementCREATE PROCEDURE 语句定义存储在 DB2 系统目录（例如 SYSCAT.PROCEDURES）中的存储过程的特征和逻辑。 清单 2. CREATE PROCEDURE 命令语法CREATE PROCEDURE-procedure-na
29、me-+-+-?-(-+-+-)- | .-,-. | | V .-IN-. | -+-+-parameter-name-data-type-+- +-OUT-+ -INOUT- -+-+-?-SPECIFIC-specific-name- .-DYNAMIC RESULT SETS 0-. .-MODIFIES SQL DATA-. -+-+-?-+-+-DYNAMIC RESULT SETS-integer- +-CONTAINS SQL-+ -READS SQL DATA- .-NOT DETERMINISTIC-. .-CALLED ON NULL INPUT-. -?-+-+-?-+
30、-+-?-DETERMINISTIC- .-INHERIT SPECIAL REGISTERS-. .-OLD SAVEPOINT LEVEL-. -+-+-?-+-+-NEWSAVEPOINT LEVEL- .-LANGUAGE SQL-. .-EXTERNAL ACTION-. -?-+-+-?-+-+-?-NO EXTERNAL ACTION- -+-+-?-PARAMETER CCSID-+-ASCII-+- -UNICODE- -| SQL-procedure-body |-CALL-procedure-name-+-+-db2 call nest_diagn(?,?)Value o
31、f output parameters-Parameter Name : RET_CODE1Parameter Value : 2Parameter Name : RET_CODE2Parameter Value : 0Return Status = 0请注意，这里需要 DECLARE 一个变量，用于接受 DB2_RETURN_STATUS 的值。 在存储过程之间共享数据前面的例子展示了存储过程如何使用参数和 RETURN 语句来共享数据。现在，我们来看看 2 个（或更多）存储过程如何共享来自同一个游标的相同的结果集。 过程 result_from_cursor 从 STAFF 和 ORG 表
32、获取某个特定部门的每个工人的姓名、工作描述、佣金和位置： 清单 12. 返回结果集的过程的例子CREATE PROCEDURE result_from_cursor (deptin int)DYNAMIC RESULT SETS 1P1: BEGIN- Declare cursorDECLARE cursor1 CURSOR WITH RETURN FORSELECT a.name, a.job, COALESCE(m,0), b.locationFROM staff a, org bwhere a.dept = b.deptnumbAND a.dept = deptin; OPEN curs
33、or1;END P1例如，对于部门 51，可得到以下结果集： 清单 13. 部门 51 的结果集NAME JOB COMMISSION LOCATION - - - - Fraye Mgr 0.00 Dallas Williams Sales 637.65 Dallas Smith Sales 992.80 Dallas Lundquist Clerk 189.65 Dallas Wheeler Clerk 513.30 Dallas 现在，您想在另一个存储过程中使用来自这个存储过程的结果集（不是将它存储在临时或永久表中）。 DB2 允许一个外部存储过程使用来自一个内部存储过程的结果集。下面是
34、相应的步骤： 使用以下语法声明一个结果集定位符：o DECLARE rs_locator_var1 RESULT_SET_LOCATOR VARYING; 将这个结果集定位符与调用者过程相关联：o ASSOCIATE RESULT SET LOCATOR( rs_locator_var1) WITH PROCEDURE proc_called; 分配从调用过程指向结果集的游标：o ALLOCATE cursor1 CURSOR FOR RESULT SET rs_locator_var1; 下面的例子演示了所有这些方法： 清单 14. 使用来自嵌套过程的结果集 CREATE PROCEDURE
35、 Use_nested_cursor (deptin int, OUT tot_dept_comm DEC(12,2)BEGINDECLARE 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);ASSOCIAT
36、E 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 DOSET tot_dept_comm = tot_dept_comm + v_comm; FETCH FROM C1 INTO v_name,v_job,v_comm,v_location;END WHILE;END现在，如果以部门 “51” 作为输入参数执
37、行这个存储过程，将得到整个部门的佣金： 清单 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
38、_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_countFROM projectWHERE respemp = var_respemp;ENDCREATE PROCEDURE PROJECT_STATUS (IN p_respemp CHAR(6),OUT p_new_status CHAR(20)BEGINCALL project_count(p_respemp);IF global_var_count 2THENSET p_new_status = Maximum projects ;
