DB2 9 根底（730 检验）认证指南，第 4 部分: 措置奖励 DB2 数据(5)

cheng029

　　











　　SQL 历程和用户定义的函数

　　竖立和挪用 SQL 历程

　　SQL 历程 是历程体用 SQL 编写的历程。历程体搜罗 SQL 历程的逻辑。它可以搜罗变量声明、条件措置奖励、流控制语句和 DML。可以在复合语句（compound statement） 中指定多个 SQL 语句，复合语句将几个语句组分解一个可实施块。

　　当告成地挪用 CREATE PROCEDURE (SQL) 语句时，就会竖立一个 SQL 历程，这会在运用效力器上定义 SQL 历程。SQL 历程是一种定义角力计较宏年夜的查询或任务的简便法子，可以在需求时挪用它们。

　　竖立 SQL 历程的一种简便法子是在号令行措置奖励顺序（CLP）脚本中编写 CREATE PROCEDURE (SQL) 语句。比如，假设将下面的语句放在一个称为 createSQLproc.db2 的文件中，就可以实施这个文件来竖立 SQL 历程：

• 跟尾 SAMPLE 数据库。
  
• 收回以下号令：
  db2 -td@ -vf createSQLproc.db2
  

db2 号令指定 -td 选项符号，这让号令行措置奖励顺序应用 @ 作为语句住手字符（因为在历程体内已经应用分号作为语句住手字符）；-v 选项符号让号令行措置奖励顺序将号令文本回显到标准输入；-f 选项符号让号令行措置奖励顺序从指定的文件（而不是标准输入）读取号令输入。

　　
CREATE PROCEDURE sales_status
(IN quota INTEGER, OUT sql_state CHAR(5))
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE SQLSTATE CHAR(5);
DECLARE rs CURSOR WITH RETURN FOR
SELECT sales_person, SUM(sales) AS total_sales
FROM sales
GROUP BY sales_person
HAVING SUM(sales) > quota;
OPEN rs;
SET sql_state = SQLSTATE;
END @




　　这个历程称为 SALES_STATUS，它担当一个输入参数 quota 并前往输入参数 sql_state。历程体中只要一个 SELECT 语句，它返回销售总量跨越指定额度的贩卖职员的姓名和贩卖总量。

　　年夜少数 SQL 历程担当至多一个输入参数。在我们的示例中，输入参数搜罗一个值（quota），这个值用在历程体搜罗的 SELECT 语句中。

　　许多 SQL 历程前往至多一个输入参数。我们的示例搜罗一个输入参数（sql_state），这个参数用来讲演 SQL 历程是告成还是失败。DB2 前往一个 SQLSTATE 值来呼应条件，可以作为 SQL 语句的效果。因为前往的 SQLCODE 或 SQLSTATE 值属于历程体中收回的最后一个 SQL 语句，并且访问这些值会窜改这些变量的后续值（因为访问它们需求应用 SQL 语句），所以应该将 SQLCODE 或 SQLSTATE 值赋值给一个局部定义的变量并经由历程它前往（比如我们示例中的 sql_state 变量）。

　　SQL 历程的参数列表可以指定零个或更多的参数，每个参数可所以三品种型之一：

• IN 参数将一个输入值通报给 SQL 历程；在历程体内不克不及删改这个值。
  
• OUT 参数从 SQL 历程前往一个输入值。
  
• INOUT 参数将一个输入值通报给 SQL 历程并从 SQL 历程前往一个输入值。
  

　　

　　SQL 历程可以前往零个或更多的效果集。在我们的示例中，SALES_STATUS 历程前往一个效果集。前往效果集的法子是：

• 在 DYNAMIC RESULT SETS 子句中声明 SQL 历程前往的效果集数量。
  
• 在历程体中为前往的每个效果集声明一个游标（应用 WITH RETURN FOR 子句）。游标（cursor） 是一个定名的控制布局，运用顺序应用它指向有序行汇集的特定行。游标用来从行汇集检索行。
  
• 翻开前往的每个效果集的游标。
  
• 当 SQL 历程前往时，让游标翻开着。
  

　　

　　变量必须在 SQL 历程体的开头住手声明。要声明 一个变量，应该分派一个专注的标识符并指定变量的 SQL 数据典范榜样，还可以可选地分派一个初始值。

　　我们的 SQL 历程示例中的 SET 子句是一个流控制 子句。在 SQL 历程体中可以应用以下的流控制语句、结谈判子句来住手有条件措置奖励：

• CASE 布局依据对一个或多个条件的打定选择一个实施途径。
  
• FOR 布局关于表中的每一行实施一个代码块。
  
• GET DIAGNOSTICS 语句将关于前一个 SQL 语句的信息前往到一个 SQL 变量中。
  
• GOTO 语句将控制转移到一个有标签的块（一个或多个语句的块，由一个专注的 SQL 称号和冒号来标识）。
  
• IF 布局依据对条件的打定选择一个实施途径。ELSEIF 和 ELSE 子句容许实施分支，或指定在其他条件分歧意时实施的默许操纵。
  
• ITERATE 子句将流控制通报到一个有标签的循环的开头。
  
• LEAVE 子句使顺序控制离开一个循环或代码块。
  
• LOOP 子句几回再三实施一个代码块，直到 LEAVE、ITERATE 或 GOTO 语句使控制离开循环。
  
• REPEAT 子句重复实施一个代码块，直到指定的搜索条件早往真为止。
  
• RETURN 子句将控制从 SQL 历程前往给挪用者。
  
• SET 子句将一个值赋值给一个输入变量或 SQL 变量。
  
• WHILE 在指定的条件为真时重复实施一个代码块。
  

　　

　　要想告成地竖立 SQL 历程，必须在数据库效力器上装置 DB2 Application Development Client。（关于 Application Development Client 的更多信息见 本系列中的第一个教程。）以前需求用 C 编译器来竖立 SQL 历程，这种依赖性在 DB2 Universal Database Version 8 中已经消除了。以前依赖于 C 编译器的完备绝对操纵今朝由驻留在捏造机中的 DB2 天生的字节码实施。关于这个改革的更多信息见 参考质料。

　　应用 SQL CALL 语句从 DB2 号令行挪用 SQL 历程。被挪用的历程必须在系统编目中住手定义。用任何支撑的言语编写的客户机运用顺序都可以挪用 SQL 历程。为了挪用 SQL 历程 SALES_STATUS，实施以下步调：

• 跟尾 SAMPLE 数据库。
  
• 收回以下语句：
  db2 CALL sales_status (25, ?)
  
  
  因为圆括号关于基于 UNIX 的系统上的号令 shell 有非凡意义，所以在这些系统上必须在它们后面加上反斜线（\）字符，或许用双引号包围它们：
  db2 "CALL sales_status (25, ?)"
  
  
  假设以交互输入体式格局应用号令行措置奖励顺序（CLP）（由 db2 => 输入提示透露表现），那么不用搜罗双引号。
  

　　

　　在这个示例中，值 25 作为输入参数 quota 通报给 SQL 历程，并应用问号（?）作为输入参数 sql_state 的占位符。这个历程返回销售总量跨越指定额度（25）的每个贩卖职员的姓名和贩卖总量。下面是这个语句前往的输入示例：
SQL_STATE: 00000
SALES_PERSON
TOTAL_SALES
GOUNOT
50
LEE
91
"SALES_STATUS" RETURN_STATUS: "0"













　　竖立和应用 SQL 用户定义函数

　　可以竖立用户定义函数来扩展内置的 DB2 函数。比如，竖立打定宏年夜的算术表达式或操纵字符串的函数，然后在 SQL 语句中像看待任何现有的内置函数一样援用这些函数。

　　假定需求一个前往圆的面积的函数，这个函数的输入参数是圆的半径。内置的 DB2 函数中没有如许的函数，然则可以竖立一个用户定义的 SQL 标量函数 来实施这个任务，可以在 SQL 语句中支撑标量函数的任那儿所援用这个函数。
CREATE function ca (r DOUBLE)
RETURNS DOUBLE
LANGUAGE SQL
CONTAINS SQL
NO EXTERNAL ACTION
DETERMINISTIC
RETURN 3.14159 * (r * r);


NO EXTERNAL ACTION 子句指出这个函数不会对数据库管理顺序非论理的对象的情况有任何影响。DETERMINISTIC 关头字指出这个函数关于给定的参数值老是前往沟通的效果。在查询优化期间会应用这个信息。实施这个函数的简便法子是在一个查询中援用它。鄙人面的示例中，针对 SYSIBM.SYSDUMMY1 编目视图（其中只要一行）实施这个查询（可以选择肆意的查询目的）：
db2 SELECT ca(96.8) AS area FROM sysibm.sysdummy1
AREA
------------------------
2.94374522816000E 004
1 record(s) selected.




　　还可以竖立用户定义的表函数，它担当零个或更多的输入参数并以表的体式格局前往数据。表函数只能用在 SQL 语句的 FROM 子句中。

　　假定需求一个前往拥有特定任务的完备绝对职员的姓名和职员号的函数，函数的参数是这个任务的头衔。下面是实施这个任务的表函数示例：
CREATE FUNCTION jobemployees (job VARCHAR(8))
RETURNS TABLE (
empno CHAR(6),
firstname VARCHAR(12),
lastname VARCHAR(15)
)
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
SELECT empno, firstnme, lastname
FROM employee
WHERE employee.job = jobemployees.job;


以下查询在 FROM 子句中援用这个新的表函数，并通报任务头衔 ‘CLERK’ 作为函数的参数。语法要求用关头字 AS 引入一个相关称号：
db2 SELECT * FROM TABLE(jobemployees('CLERK')) AS clerk
EMPNO  FIRSTNAME    LASTNAME
------ ------------ ---------------
000120 SEAN         O'CONNELL
000230 JAMES        JEFFERSON
000240 SALVATORE    MARINO
000250 DANIEL       SMITH
000260 SYBIL        JOHNSON
000270 MARIA        PEREZ
6 record(s) selected.




版权声明：
原创作品，容许转载，转载时请务必以超链接体式格局标明文章 原始出处 、作者信息和本声明。否则将清查执法责任。