4、DB2官方存储过程开发教程.doc

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 定义本地变量的名称。该名称不能与其他变量或参数名称相同，也不能与列名相同。 图 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 语句中声明多个相同数据类型的变量。数组数据类型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_increase = 15000; ELSEIF years_of_serv 20 THEN SET 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 PROCEDURE sal_increase_lim1 (empid CHAR(6)BEGIN DECLARE 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_serv FROM empl1 WHERE empno = empid; CASE WHEN years_of_serv 30 THEN SET v_incr_rate = 0.08; WHEN years_of_serv 20 THEN SET v_incr_rate = 0.07; WHEN years_of_serv 10 THEN SET v_incr_rate = 0.05; ELSE SET v_incr_rate = 0.04; END CASE; UPDATE empl1 SET salary = salary+salary*v_incr_rate WHERE empno = empid; END 回页首迭代语句SQL PL 支持一些重复执行某个逻辑的方法，包括简单的 LOOP、WHILE 循环、REPEAT 循环和 FOR 循环： LOOP 循环 - 简单的循环 o L1: LOOP o SQL statements; o 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 语句不同于其他的迭代语句，因为它用于迭代一个定义好的结果集中的行。为了演示这些循环技巧的使用，我们来编写一个过程，该过程从一个 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: BEGIN DECLARE v_at_end , v_counter INTEGER DEFAULT 0; DECLARE v_lastname VARCHAR(15); DECLARE v_birthd, v_hired DATE; DECLARE c1 CURSOR FOR SELECT lastname, hiredate, birthdate FROM employee WHERE WORKDEPT = deptin; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_at_end = 1; OPEN c1; FETCH_LOOP: LOOP FETCH c1 INTO v_lastname, v_hired, v_birthd; IF v_at_end 0 THEN - loop until last row of the cursor LEAVE FETCH_LOOP; END IF; SET v_counter = v_counter + 1; INSERT INTO REPORT_INFO_DEPT values(v_lastname, v_hired, v_birthd); END LOOP FETCH_LOOP; SET p_counter = v_counter; END Ll现在，我们使用 WHILE 循环语句来做同样的事情。清单 14. WHILE 循环的例子CREATE PROCEDURE DEPT_REPT (DEPTIN char(3), OUT p_counter INTEGER)Pl: BEGIN DECLARE v_at_end , v_counter INTEGER DEFAULT 0; DECLARE v_lastname VARCHAR(15); DECLARE v_birthd, v_hired DATE; DECLARE c1 CURSOR FOR SELECT lastname, hiredate, birthdate FROM employee WHERE WORKDEPT = deptin; DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_at_end = 1; OPEN c1; FETCH c1 INTO v_lastname, v_hired, v_birthd; WHILE (v_at_end = 0) DO INSERT INTO REPORT_INFO_DEPT values(v_lastname, v_hired, v_birthd); SET v_counter = v_counter + 1; FETCH c1 INTO v_lastname, v_hired, v_birthd; END WHILE; SET p_counter = v_counter; END P1REPEAT 循环非常类似于 WHILE 循环，只不过条件是在最后检查的（因此，它实际上是一个 UNTIL 循环）。现在，我们使用包含 FOR 循环语句的一个过程来填充 REPORT_INFO_DEPT 表。清单 15. FOR 循环的例子CREATE PROCEDURE DEPT_REPT1 (DEPTIN char(3), OUT p_counter INT)P1:BEGIN DECLARE v_counter INT DEFAULT 0; FOR dept_loop AS SELECT lastname, hiredate, birthdate FROM employee WHERE WORKDEPT = deptin DO INSERT INTO REPORT_INFO_DEPT values(dept_loop.lastname,dept_loop.hiredate,dept_loop.birthdate); SET v_counter = v_counter + 1; END FOR; SET p_counter = v_counter; END P1请注意，最后一个过程没有打开游标、从游标中取数据或关闭游标 所有这些都是由 FOR 循环语句隐式进行的。而且，可以引用循环中隐式地获取的值，使用循环名称限定列（例如 dept_loop.lastname） 而不必使用本地变量来存储这些值。异常处理机制DECLARE 有名称的条件SQL PL 允许为给定的 SQLSTATE 声明用户命名的条件，以用于之后的错误处理。条件名称在整个复合语句中必须是惟一的，并且只能在声明它的复合语句中引用它。清单 16 显示了声明一个有名称的条件的语法。清单 16. 声明一个有名称的条件的语法|-DECLARE-condition-name-CONDITION-FOR- .-VALUE-. .-SQLSTATE-+-+-. -+-+-string-constant-|下面是条件声明的例子：DECLARE FOREIGN_KEY_VIOLATION CONDITION FOR SQLSTATE 23503;DECLARE overflow CONDITION FOR SQLSTATE 22003;回页首DECLARE 条件处理程序如果发生一个错误，存储过程的行为是根据条件处理程序来决定的。在一个存储过程中，可以为一个普通的或有名称的条件和特定的 SQLSTATE 声明一个或多个条件处理程序。当一个 SQL 语句产生一个 SQLEXCEPTION 或 SQLWARNING（SQLCODE 0）时，控制被转移到为一个声明的处理程序中，以获取普通的异常或特定的 SQLSTATE 值。清单 17 显示了受支持的处理程序声明的语法。清单 17. 处理程序声明的语法|-DECLARE-+-CONTINUE-+-HANDLER-FOR- +-EXIT-+ -UNDO- -+-specific-condition-value-+-| SQL-procedure-statement |-|-general-condition-value- WHERE specific-condition-value.-,-.V .-VALUE-. |-+-SQLSTATE-+-+-string-constant-+-+-| -condition-name- 下面是演示它如何工作的一些例子。在下面的过程中，如果 UPDATE 语句失败，则控制被转移到 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_error1BEGIN DECLARE SQLCODE INT DEFAULT 0; DECLARE SQLSTATE CHAR(5) DEFAULT 00000; DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT SQLSTATE, SQLCODE INTO p_sqlstate_out, p_sqlcode_out FROM SYSIBM.SYSDUMMY1; UPDATE EMPLOYEE SET job = new_job WHERE empno = p_empno;END请注意，SQLCODE 和 SQLSTATE 应该被显式地声明为本地变量。清单 19 演示了一个场景，在此场景中，当一个给定的错误发生时，不是退出过程，而是继续某个动作。为了理解这个例子，请注意表 TAB1 被定义为（col1 int, col2 char(5)），在默认情况下，如果发生值溢出，DB2 不是截短一个值，而是产生 SQLSTATE 22001。清单 19. 具有 CONTINUE 处理程序的过程CREATE PROCEDURE proc1 (IN num int, IN new_status varchar(10) P1: BEGIN DECLARE SQLCODE INTEGER default 0; DECLARE SQLSTATE CHAR(5) default ; DECLARE v_trunc INTEGER default 0; DECLARE overflow CONDITION FOR SQLSTATE 22001; DECLARE CONTINUE HANDLER FOR overflow BEGIN INSERT 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 指示符将被设置为 2，新行将被插入到 TAB1 表中，插入时对 COL2 列使用了截短后的值，该过程最终成功完成。回页首强制发出异常 - SIGNAL SQLSTATESQL PL 支持发出一个错误或警告条件。这导致一个具有指定 SQLSTATE 的错误或警告被返回，同时返回的还有可选的消息文本。清单 20 显示了 SIGNAL 语句的语法。清单 20. SIGNAL 语句的语法-SIGNAL- .-VALUE-. -+-SQLSTATE-+-+-+-sqlstate-string-constant-+-+-| -variable-name- |-condition-name-+-+- 5 THEN 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 语句来调用。存储过程可以接受参数值，并根据输入数据更改业务逻辑的行为。然后，存储过程可以将输出值返回给调用者，或者将多个结果集返回给调用者。存储过程可以使用 SQL Procedure Language（SQL PL）或编程语言（比如 Java 或 C）来实现。存储过程可以从以下几个方面显著提高分布式应用程序（在远程系统上运行的应用程序）的性能： 减少网络传输量 减少应用程序开发人员编写代码的工作 为从分布式客户机调用远程存储过程提供一种简单的方式 图 1 显示了存储过程是有很用的：图 1. 通过使用存储过程减少网络传输量使用存储过程的优点存储过程有以下优点： 简化代码重用、代码标准化和代码维护： o 当一系列不同的应用程序都需要执行一项相似的任务时，通过编写一个执行这个任务的存储过程，让每个客户机应用程序调用该存储过程以执行任务，这样就可以很方便地实现这些应用程序。如果需要修改任务，那么只需修改受影响的存储过程。如果不使用存储过程的话，就需要修改每个应用程序。 对其他数据库对象的受控制的访问： o 没有权限访问特定的数据库对象或一个数据库上的操作（例如创建一个表），但是又想在那个对象上执行动作或执行一个操作的用户，可以通过调用他有权限运行的一个存储过程来实现他的目的。这意味着权限管理得以简化。 提高应用程序的性能： o 对于远程应用程序，需要逐条语句地通过网络传输每条发出的 SQL 语句。这可能导致大量的网络传输。通过将所有这些语句添加到一个存储过程中，客户机应用程序只需一次网络请求便可调用那个存储过程。这显然大大减少了所需的网络传输量，从而提高了应用程序的总体性能。 更有效的 SQL： o 由于存储过程存放在数据库服务器上，实际上它们本身就是数据库对象，它们可以比客户机应用程序更有效地执行，因为 SQL 请求被更高效地传输。使用嵌入式 SQL 操作的存储过程还拥有已经存储在包中的访问计划，因而可以提高执行每条语句的速度。如果存储过程在创建时使用了 NOT FENCED 子句，那么它与数据库管理器是在同一个进程空间中运行的，因此可以在共享内存中进行通信。 增强的功能： o 由于存储过程存放在数据库服务器上，因此很容易比应用程序获得更多可使用的内存和磁盘空间。而应用程序则可以访问安装在数据库服务器上的软件。 互操作性： o 不同的程序员可以用不同的编程语言实现不同的代码模块。为了帮助实现具有逻辑互操作性的代码重用，存储过程本身可以用不同的语言编写。用不同语言编写的客户机应用程序可以调用用不同语言编写的存储过程（例如，以 C+ 编写的应用程序可以调用以 Java 编写的存储过程）。而且?以不同语言编写的存储过程可以相互调用（例如，以 C 编写的存储过程可以调用以 Java 编写的存储过程）。客户机的操作系统和存储过程所在的服务器的操作系统也可以不同（例如，一个 Windows 客户机应用程序可以调用一个在 AIX 上运行的存储过程）。 存储过程的局限性 调用存储时应具有互操作性，以使客户机应用程序可以兼容用于编写存储过程的不同的编程语言。但是，用特定语言编写的外部存储过程只能在某些平台上运行。例如，CLR 存储过程只能在基于 Windows 的平台上运行。虽然可以在任何地方调用这种存储过程，但是，如果要将服务器迁移到不同的平台（例如 Solaris 或 AIX），那么就需要重新编写这种存储过程。对于用 C、C+ 或 COBOL 编写的存储过程也是如此，在新的服务器上，这些存储过程需要重新编译。而 SQL 过程则不存在这种问题。虽然 Java 存储过程也较为灵活，但是新的数据库服务器需要安装一个 Java 虚拟机（JVM）才能执行这种存储过程。 惟一用于调用一个存储过程的 SQL 语句是 CALL 语句。CALL 语句可以在应用程序、存储过程、用户定义函数或触发器中使用。 存储过程可以嵌套，一个存储过程可以调用另一个存储过程，后者又可以调用其他存储过程。在 DB2 v9.5 中，一次最多可以嵌套 64 个存储过程。 存储过程不能在不同的调用之间保存状态。 存储过程的输出参数不能直接被另一条 SQL 语句使用。调用接口需要先将输出参数赋给某个变量，然后才可以在另一条 SQL 语句中使用该变量。 存储过程不支持可滚动游标。 外部存储过程与 SQL 存储过程之间的不同点DB2 支持两种类型的存储过程。一种是外部存储过程，另一种是 SQL 过程。 外部存储过程在数据库之外的应用程序中用编程语言定义逻辑。这种类型的存储过程的可执行文件或库存在于数据库服务器所安装到的文件系统中。和 SQL 过程一样，外部存储过程也要注册到数据库，但是在注册过程中，需要指定存储过程可执行文件或库的位置。 DB2 支持用各种不同的编程语言编写的外部存储过程，包括 C、C+、COBOL、Java 和 .NET（也称 CLR 存储过程）。 以下是外部存储过程独有的特性： 外部存储过程允许访问非数据库接口，例如文件系统或应用程序。即使这些资源不是数据库系统的一部分，外部存储过程仍可以使用它们。例如，一个外部存储过程可以在一个 UNIX 数据库服务器上执行一个 shell 脚本，以执行一项特定的任务。 外部存储过程使用参数风格来确定用于该存储过程的编程语言如何使用输入、输出或输入/输出参数。有些参数风格允许在一个称作 dbinfo 的结构中使用对存储过程有用的传递元数据信息，例如数据库和存储过程属性信息。 外部存储过程可以被定义为 FENCED 或 NOT FENCED。这决定了存储过程应该与数据库管理器在同一个地址空间中运行（NOT FENCED），还是应该在它自己的进程中运行（FENCED）。被定义为 NOT FENCED 的存储过程可以执行得稍微快一点，因为不需要使用共享内存段通信；但是，这种存储过程的风险也更大。未隔离的存储过程如果遇到一个问题，则可能导致整个数据库服务器崩溃，因为它与 DB2 使用相同的地址空间。Java 存储过程必须被定义为 FENCED；不过，它们可以被定义为 THREADSAFE 或 NOT THREADSAFE。 下面是 SQL 过程独有的特性： SQL 过程只能使用一种称作 SQL Programming Language （SQL PL）的语言来编写。关于这种语言的更多信息，可以在本系列的前一篇教程中找到（参见 参考资料）。因此，外部存储过程与 SQL 过程主要的区别在于外部过程是使用一种特定的编程语言编写的，而 SQL 过程是只能使用 SQL 语句编写。 SQL 过程存放在真正的数据库中。外部存储过程依赖于文件系统中的一个外部库或可执行文件，而 SQL 过程是数据库的一部分。 构建 SQL 过程不需要编译器，也不需要对特定的编程语言有深入的理解。所以，SQL 过程的开发可能更快。 SQL 过程总是被定义为 NOT FENCED。这种类型的存储过程存在的风险更小，因为这种存储过程只能执行 SQL 操作，所以数据库服务器面临的风险是有限的。 SQL 过程的可移植性更好。由于它们不依赖于特定的编程语言，每个数据库服务器上也就不需要用于这种语言的编译器或解释器，所以必要时更容易在每个服务器上重新创建这些存储过程。 SQL 过程的结构SQL 过程的结构由 CREATE PROCEDURE 语句、参数和复合语句组成。下面的伪图显示了 SQL 过程的结构：清单 1. SQL 过程的结构CREATE PROCEDURE proc_name IN, OUT, INOUT parameters optional clauses SQL procedure body - compound statementCREATE PROCEDURE 语句定义存储在 DB2 系统目录（例如 SYSCAT.PROCEDURES）中的存储过程的特征和逻辑。 清单 2. CREATE PRO