将定制的和重大的营业逻辑集成到 SQL 语句中
存储进程
什么是存储进程？
起初，存储进程是用于在数据库效劳器上处理和执行多个 SQL 命令的重大的 C 序次递次。树立这些序次递次是一个希罕异常重大的进程，须要对 C/C 编程和数据库接口言语，比如 DB2 Call Level Interface (CLI)有深切的相识。
随着数据库的演化，泛起了基于 SQL 的外地存储进程言语。DB2 利用一种称作 SQL PL 的言语，Oracle 利用 PL/SQL，SQL Server 和 Sybase 则利用 Transact-SQL (T-SQL)。这些言语各不相通，可是却遵照一个群众的环节字、控制语句和举动框架。SQL 标准中有一节就划定礼貌了存储进程言语所能蒙受的环节字和举动。每个 RDBMS 供应商以不同的要领完成该标准，所以应该明晰，在这些言语之间停止移植并非易事。
本节中谈到的年夜多数存储进程是用 DB2 的 SQL PL 编写的，由于这些存储进程更为无数，也更轻易阐释所触及的概念。也可以用许多不同范例的外部编程言语，包罗 C/C 、CLR、Java 言语和 OLE，来编写存储进程。
回页首
为什么利用存储进程？
您大约会出于以下原因须要在利用序次递次中利用存储进程：
- 增加网络传输： 由于存储进程只在网络上发送进程挪用和终极的了局，是以不用将一切的且则数据集都发送回客户机。关于客户机和效劳器的地舆地位相距极远的利用序次递次，利用存储进程可以年夜年夜提高性能。
- 提高性能： 存储进程是在效劳器上执行的，而不是在客户机上执行的。在年夜多数主流架构中，效劳器是希罕异常强年夜的板滞，处理 SQL 的速率比客户机快得多。
- 低落重大性：颠末进程将全部数据库利用序次递次代码放入模块单位中，可以将利用序次递次与数据库的交互零丁放到一个层中。这样更易于停止测试、调试和维护。
- 责任明白：将全部数据交互零丁放在存储进程中，便于 DBA 控制交互。DBA 是最相识系统的人，DBA 可以最快地对任何性能了局做出照应。
- 易于利用：利用存储进程编写处理数据的利用序次递次希罕异常重大，便于将营业逻辑转换成利用序次递次逻辑。让利用序次递次以特定的数据花样访问数据库，低落了对利用序次递次编程技术的要求。
回页首
何时应该以及何时不该该利用存储进程
前一节提到了利用存储进程的许多利益。可是，它们在利用上也有一些限制：
- 存储进程可以利用任何 DB2 系统数据范例。可是存储进程的前往值只能是整数。如果须要从存储进程中前往其他数据值，那么必需利用输出参数。
- 不克不及在 SQL 语句中挪用存储进程。
存储进程在封装年夜块相干逻辑方面最为有效。您应该将存储进程看作一个序次递次。如果一块代码在利用序次递次或其他进程中屡屡重复泛起，那么将它封装到它本身的存储进程中大约会很有效。可是，如果该逻辑只利用一次，那么无需将其封装为存储进程。
应该只管束止编写执行多个任务的年夜型存储进程，除非您可以明晰地定义该存储进程。您也大约会忍不住将全部数据库逻辑放入到一个存储进程中。可是，仅仅利用一个存储进程来前往一个表中的了局集或一行数据的做法不是很妥当。更好的做法是直接在利用序次递次中翻开一个游标，大约直接拔出一个行。
回页首
SQL 存储进程语法
SQL 存储进程的语法相通于树立函数的语法。清单 21 展现了树立 SQL 存储进程的简化语法图：
清单 21. SQL 存储进程的简化语法图
>>-CREATE PROCEDURE--procedure-name----------------------------->
>-- ---------------------------------------------------- --*---->
'-(-- ------------------------------------------ --)-'
| .-,------------------------------------. |
| 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-.
>--*-- ------------------- --*-- ---------------------- --*----->
'-DETERMINISTIC-----'
.-LANGUAGE SQL-. .-EXTERNAL ACTION----.
>--*-- -------------- --*-- -------------------- --*------------>
'-NO EXTERNAL ACTION-'
>--| SQL-procedure-body |-------------------------------------->|
SQL-procedure-body:
|--SQL-procedure-statement--------------------------------------|
CREATE PROCEDURE 语句的严重构成部门有：
PARAMETER TYPE: 有三种参数范例：IN用于输出参数。对这些参数的改观不会传回到挪用该进程的利用序次递次。OUT用于输出参数。对这些参数的改观会传回到挪用该进程的利用序次递次。INOUT用于输出和输出。对这些参数的改观以及它们的输出值会影响存储进程和挪用该进程的利用序次递次。
SPECIFIC: 为了在外部标识一个进程而指定的一个特命称号。如果没有指命称号，则 DB2 会授予一集系统生成的值。关于重载存储进程，这一点希罕有效。DYNAMIC RESULT SETS: 指定贯穿连接翻开并传回给挪用序次递次或利用序次递次的了局集的数目。DETERMINISTIC: 指定关于给定的参数值，该进程可否总是前往相通的了局。LANGUAGE: SQL 进程仅撑持 DB2 SQL PL 言语。
和用户定义函数一样，存储进程有三种访问数据库的形式，这些形式选择了存储进程怎样与数据库交互，以及利用什么范例的 SQL：
CONTAINS SQL: 标明存储进程可以执行既不读取也不修正 SQL 数据的 SQL 语句。READS SQL: 标明存储进程中可以包罗某些不修正 SQL 数据的 SQL 语句。MODIFIES SQL: 标明存储进程可以执行除了存储进程中不撑持的 SQL 语句之外的任何 SQL 语句。
在 DB2 Information Center（见 参考材料）中的 “SQL statements allowed in routines” 专题中，有一个表列出了一个 SQL 语句可否可以在具有指定 SQL 数据访问指示的例程中执行。
回页首
树立 SQL 存储进程
本教程不计议树立存储进程的一切语义。最严重的是相识怎样从命令行树立存储进程。屡屡，在命令行中可以这样执行包罗存储进程 DLL 的脚本：
db2 -td@ -v -f mysprocs.db2
在挪用包罗 SQL 语句的脚本时，有以下几种不同的选项：
t: 标明利用;显露一行的竣事。这样一来，在脚本中，一个 SQL 语句可以写成多行。v: 翻开 verbose 形式。在该形式下，脚本输出中会再次表现一切脚本命令。这样便于创造掉败的命令。当脚本文件较年夜时，如果没有翻开 verbose 形式，那么调试起来希罕异常花时候。f: 指定输出文件的称号。td: 指定利用跟在这个标记后面的字符（而不是一个分号）来界定命令的竣事。
清单 22 表现了一个执行重大运算的存储进程的例子，该存储进程带有一个输出参数，并将了局存储在输出参数中：
清单 22. 一个重大的 SQL 存储进程
CREATE PROCEDURE myProc1 (IN p_varOne INT,
OUT p_varTwo INT)
LANGUAGE SQL
BEGIN
DECLARE v_varUno INT DEFAULT 0; -- (1)
SET v_varUno = 1 p_varOne;
SET p_varTwo - v_varUno * p_varOne;
END
@ -- (2)
假定该脚本存放在一个名为 myprocs.db2 的文件中，那么可以在 DB2 Command Line Processor (CLP) 中利用以下命令执行该脚本：
db2 -t -v -f myprocs.db2
DB2 将在 (1) 处停上去，将其当作一个命令，并发生一个错误。为了制止这个了局，须要指定所利用的命令截至符 —— 在这里是 @ 字符。为了让下面的脚天性乐成地执行，须要利用以下命令：
db2 -td@ -v -f myprocs.db2
回页首
更多存储进程的例子
清单 23 和 清单 24 表现了 SQL 存储进程的其他例子。第一个例子也可以编写成函数，由于它只前往具有某种教导程度的雇员的人数。
清单 23. 一个重大的从游标中取数据的 SQL 存储进程
CREATE PROCEDURE empCount (IN p_edLevel SMALLINT, OUT p_edCount INT) LANGUAGE SQL BEGIN DECLARE c_cnt CURSOR FOR SELECT count(*) FROM employee WHERE edlevel = p_edLevel; OPEN c_cnt; FETCH FROM c_cnt INTO p_edCount; CLOSE c_cnt; END
清单 24 是一个更重大的进程，它扩展了清单 23 中的例子。它确定一个部门有几何雇员，如果部门人数少于 4 人，则给该部门中每个雇员增加薪水。
清单 24. 一个更重大的 SQL 存储进程
CREATE PROCEDURE increaseSize (IN p_size DECIMAL, OUT p_sqlstate CHAR(5))
LANGUAGE SQL
BEGIN
-- Declare SQLSTATE variable to hold error codes
DECLARE SQLSTATE CHAR(5);
-- Declare variables
DECLARE v_deptCount INT DEFAULT 0;
DECLARE v_dept CHAR(3) DEFAULT '';
-- Declare the cursor
DECLARE c_cnt CURSOR FOR
SELECT workdept, COUNT(*)
FROM employee
GROUP BY workdept;
-- Declare condition handler
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT sqlstate INTO p_sqlstate
FROM sysibm.sysdummy1;
-- Open the cursor
OPEN c_cnt;
-- Fetch from the cursor until it is empty
FETCH FROM c_cnt INTO v_dept, v_deptCount;
WHILE ( SQLSTATE = '00000' ) DO
-- Check if the department has four or less people
IF v_deptCount < 5 THEN
-- update the salary if they do
UPDATE employee
SET salary = salary * (1 p_size)
WHERE workdept = v_dept;
END IF;
FETCH FROM c_cnt INTO v_dept, v_deptCount;
END WHILE;
CLOSE c_cnt;
COMMIT;
END
下面是关于 清单 24 中的进程要属意的几点。第一点是它利用了异常和前提处理。在执行一条语句后，可以检查 SQLCODE 和 SQLSTATE 的值。为此，必需在进程的收尾处将它们声明为变量。每当泛起 SQL 错误（换句话说，每当语句前往负的 SQLCODE 时），就会激活前提处理序次递次。这个处理序次递次被定义为一个 EXIT 处理序次递次，这意味着一旦执行该处理序次递次的逻辑，进程即告截至。该处理序次递次将之前语句（即发生错误的语句）的 SQLCODE 值复制到关联的输出变量中，以便存储进程的挪用者须要时可以检查这个值。如果没有定义任何范例的 SQL 异常处理序次递次，那么碰到错误时存储进程会马上截至。还可以定义其他范例的处理序次递次，比如 CONTINUE 和 UNDO 处理序次递次，也可以定义定制的 SQLCODE 或 SQLSTATE 以便在须要时停止处理。
别的还须要属意的一点是在复合块（BEGIN...END）中变量、游标、前提处理序次递次等等的声明序次递次。这个序次递次很严重。如果没有按预期的序次递次声明各项，那么进程就不克不及编译。由于可以嵌套复合块，所以只需变量已在嵌套块中声明，就可以在一个进程靠后的地位声明该变量。记着，嵌套块中的项目的引用范围仅限于谁人块的范围内。比如，不克不及在外部块中引用一个外部复合块中声明的变量。
这个进程还演示了原子复合块的利用。之前的教程也提到，关于一个原子复合块，如果块中一个子语句掉败，那么整个块就被视为掉败，该块中对数据库的任何改观都将回滚。而关于非原子复合块，块中每个子语句自力执行，而不管之前的子语句可否乐成完成。进程末了的 COMMIT 语句提交复合块中做出的改观。ROLLBACK 语句将回滚复合块中的一切改观。
这个进程表现了 SQL PL 的一些更初级的方面，包罗利用错误和前提处理、游标、循环和变量。在这么短的教程中不大约粉饰覆盖 SQL PL 言语的方方面面。可是，我激烈推荐一本书，这个书的书名是 DB2 SQL PL: Essential Guide for DB2 UDB on Linux, UNIX, Windows, i5/OS, and z/OS, 2nd Edition （见 参考材料）。这本书是 SQL PL 的可靠指南，包罗了对该言语各构成部门的详细注释，并供应了一些有效的例子来演示其用法。
版权声明：
原创作品，容许转载，转载时请务必以超链接要领标明文章 原始来因 、作者信息和本声明。不然将追查法例责任。