DB2创建存储过程

DB2中创建存储过程：

创建存储过程: CREATE OR REPLACE PROCEDURE/CREATE PROCEDURE 存储过程名(IN 输入变量名 输入变量类型 | OUT 输出变量名 输出变量类型)

存储过程名后面是存储过程属性列表，常用的有：LANGUAGE SQL、MODIFIES SQL DATA、RESULT SETS 1(返回结果集个数)

存储过程主体以begin开始，以end结束

调用存储过程：CALL 存储过程名(参数1，参数2，参数n)

刚开始接触存储过程，了解下存储过程的语法，存储过程主体的逻辑能用SQL写出来，存储过程就好创建了

CREATE PROCEDURE TDW.P_XXXX_DD(IN parameter1 data_type1,parameter2 data_type2...) --指定存储过程输入参数的个数、数据类型
SPECIFIC  TEST_CREATE_PROCEDURE --给存储过程指定别名
LANGUAGE SQL --指定用纯SQL编写存储过程
NOT DETERMINISTIC --表示存储过程是非动态的
EXTERNAL ACTION  --表示存储过程可执行一些不通过数据库管理器管而改变数据库状态的活动 ；存储过程的EXTERNAL ACTION or NO EXTERNAL ACTION 默认是EXTERNAL ACTION
MODIFIES SQL DATA  --表示存储过程可以执行任何SQL语句

BEGIN  --例如存储过程的主体是 对数据库中的数据按要求加工一下insert到另一张表中
delete from TDW.P_XXXX_DD where DT_DAY_ID between parameter1 and parameter2;
INSERT INTO TDW.P_XXXX_DD (DT_DAY_ID, ORG_COMPANY_ID, ORDER_QTY, ...)
SELECT c.DT_DAY_ID, b.ORG_COMPANY_ID, a.ORDER_QTY, ... FROM TDW.DW_XX_MX a 
	left join TDW.DW_XX_TEMP b on a.ID = b.ID 
	inner join 
	(select ORDER_CD, DT_DAY_ID , sum()... from TDW.DW_XX_ORDER where ORDER_QTY <> 0 group by ORDER_CD,DT_DAY_ID,...) c on a.XXA = c.XXC
	where c.DT_DAY_ID between parameter1 and parameter2
	group by c.DT_ORDERDAY_ID,b.ID,...;

END

存储过程语法参数说明：

• DETERMINISTIC or NOT DETERMINISTIC ：表示存储过程是动态或者非动态的。动态的返回的值是不确定的；非动态的存储过程每次执行返回的值是相同的。
• INHERIT SPECIAL REGISTERS ：表示继承专用寄存器。
• SPECIFIC specific_name 特定名称 别名，这个特定名称用于dorp存储过程，或者给存储过程添加注释用；但不能调用存储过程。如果不指定，则数据库会自动生成一个yymmddhhmmsshhn时间戳（例如SQL200318161629437）名字。
• EXTERNAL ACTION or NO EXTERNAL ACTION ：表示存储过程是否不通过数据库管理器管执行改变数据库状态的活动；默认是 EXTERNAL ACTION；如果指定为 NO EXTERNAL ACTION，则数据库会确定最最佳优化方案。
• DYNAMIC RESULT SETS INTEGER：指定存储过程返回结果的最大数量；存储过程中虽然没有return语句，但是却能返回结果集
• CONTAINS SQL： 指定存储过程中的SQL访问级别，这个表示存储过程可以执行中，既不可读取SQL数据，也不可修改SQL的数据。
• READS SQL DATA： 表示存储过程可以执行中，可读取SQL，但不可修改SQL的数据。
• MODIFIES SQL DATA： 表示存储过程可以执行任何SQL语句；可以对数据库中的数据进行增加、删除和修改。
• CALLED ON NULL INPUT：表示可以调用存储过程而不管任何的输入参数是否为 NULL，并且任何参数可以返回一个NULL或者非空值。检验参数是否为NULL是在过程中进行的。
• LANGUAGE SQL：指定程序的主体用的是SQL语言
• 存储过程变量的定义：变量使用前必须先定义，方法为：DECLARE 变量名 变量的数据类型 （default 默认值）

  DECLARE Var1 INTEGER DEFAULT 0;
  DECLARE Var2  char(8);

存储过程主体中常用的逻辑表达式：

1.if 表达式:

 if 条件1 then
      结果1
    elseif 条件2 then
      结果2
    else
      结果3
    end if;

2.case表达式：

    case 变量名 when
           变量值1 then
           . . .
    when
           变量值2 then
           . . .
    else
           . . .
    end case;

or

    case when
           变量名=变量值1 then
           . . .
    when
           变量名=变量值2 then
           . . .
    else
           . . .
    end case;

3.for 表达式:

    for 循环名 as
       游标名或select 表达式
    do
       sql表达式
    end for;

4.while表达式：

    while 条件表达式 do
           逻辑体；
     end while；

5.LOOP表达式：

   LOOP... END LOOP；

存储过程中关于游标：
定义游标：DECLARE 游标名 CURSOR FOR Select 语句；
打开游标： OPEN　游标名；
取值：FETCH　游标名　INTO 变量列表
注：游标的申明如果放在中间段，要用begin。。。end; 段分割标志分割开；

存储过程中建立临时表：
DECLARE GLOBAL TEMPORARY TABLE TABLE_NAME --指定临时表的名称.
AS (FULLSELECT) DEFINITION ONLY --指定临时表的定义
EXCLUDING IDENTITY COLUMN ATTRIBUTES --指定不是从源表定义中复制的恒等列.
ON COMMIT DELETE ROWS --指定如果沒有打开WITH GOLD光标,则刪除表的所有行
NOT LOGGED IN 临时表空间名 with replace； --指定不对表的改变进行记录，With replace选项会隐式的自动删除该临时表

DB2存储过程中的几个全局变量:
ROW_COUNT --影响行数
RETURN_STATUS --返回状态
SQLSTATE—SQL --返回错误代码 注：使用前必先定义 declare sqlstate char(5); declare state char(5);

DECLARE not_found CONDITION FOR SQLSTATE '53089'; --储存过程指定的宿主变量参数的个数不等于预期的参数个数
DECLARE not_found CONDITION FOR SQLSTATE '02000'; --没有找到满足SQL语句的行
DECLARE EXIT HANDLER FOR NOT FOUND SET  Salary = 6666;

抽取、提交存储过程：

db2 "get routine into 文件名 from procedure 存储过程名" --抽取存储过程
db2 "put routine from 文件名" --提交存储过程