DB2存储过程总结

cxs7225032

存储过程是一个能够封装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-----'

.-INHERIT SPECIAL REGISTERS-.     .-OLD SAVEPOINT LEVEL-.
>--+---------------------------+--*--+---------------------+---->
'-NEW SAVEPOINT LEVEL-'

.-LANGUAGE SQL-.     .-EXTERNAL ACTION----.
>--*--+--------------+--*--+--------------------+--*------------>
'-NO EXTERNAL ACTION-'

>--+------------------------------+--*-------------------------->
'-PARAMETER CCSID--+-ASCII---+-'
'-UNICODE-'

>--| SQL-procedure-body |--------------------------------------><

SQL-procedure-body:

|---SQL-procedure-statement-------------------------------------|


语法说明：
1、procedure-name: 存储过程的名字，在同一个数据库的同一模式下，不能存在存储过程名相同参数数目相同的存储过程，即使参数的类型不同也不行。
2、(IN | OUT | INOUT parameter-name data-type,...) ：传入参数
    IN：输入参数
OUT：输出参数
INOUT：作为输入输出参数
    parameter-name：参数名字，在此存储过程中唯一的标识符。
data-type:参数类型，可以接收SQL类型和创建的表。不支持LONG VARCHAR, LONG VARGRAPHIC, DATALINK, REFERENCE和用户自定义类型。
3、SPECIFIC specific-name：唯一的特定名称（别名），可以用存储过程名代替，这个特定名称用于dorp存储过程，或者给存储过程添加注视用，但不能调用存储过程。如果不指定，则数据库会自动生成一个yymmddhhmmsshhn时间戳的名字。推荐给出别名。
4、DYNAMIC RESULT SETS integer：指定存储过程返回结果的最大数量。存储过程中虽然没有return语句，但是却能返回结果集。
5、CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA： 指定存储过程中的SQL访问级别
    CONTAINS SQL： 表示存储过程可以执行中，既不可读取 SQL 数据，也不可修改 SQL 数据。
    READS SQL DATA： 表示存储过程可以执行中，可读取SQL，但不可修改 SQL 数据。
    MODIFIES SQL DATA： 表示存储过程可以执行任何 SQL 语句。可以对数据库中的数据进行增加、删除和修改。
6、DETERMINISTIC or NOT DETERMINISTIC：表示存储过程是动态或者非动态的。动态的返回的值是不确定的。非动态的存储过程每次执行返回的值是相同的。
7、CALLED ON NULL INPUT：表示可以调用存储过程而不管任何的输入参数是否为NULL，并且，任何的OUT或者INOUT参数可以返回一个NULL或者非空值。检验参数是否为NULL是在过程中进行的。
8、INHERIT SPECIAL REGISTERS：表示继承专用寄存器。
9、OLD SAVEPOINT LEVEL or NEW SAVEPOINT LEVEL：建立存储点。OLD SAVEPOINT LEVEL是默认的存储点。
10、LANGUAGE SQL：指定程序的主体用的是SQL语言。
11、EXTERNAL ACTION or NO EXTERNAL ACTION：表示存储过程是否执行一些改变理数据库状态的活动，而不通过数据库管理器管。默认是 EXTERNAL ACTION。如果指定为NO EXTERNAL ACTION ,则数据库会确定最最佳优化方案。
12、PARAMETER CCSID：指定所有输出字符串数据的编码,默认为UNICODE编码数据库为PARAMETER CCSID UNICODE ，其他的数据库默认为PARAMETER CCSID 3 ASCII。
13、SQL-procedure-body：存储过程的主体

.-NOT ATOMIC--.
>>-+---------+--BEGIN----+-------------+------------------------>
'-label:--'           '-ATOMIC------'

>-----+-----------------------------------------------+--------->
| .------------------------------------------.  |
| V                                          |  |
'-----+-| SQL-variable-declaration |-+---;---+--'
+-| condition-declaration |----+
'-| return-codes-declaration |-'

>--+----------------------------------+------------------------->
| .------------------------------. |
| V                              | |
'---| statement-declaration |--;-+-'

>-----+--------------------------------------+------------------->
|  .--------------------------------.  |
|  V                                |  |
'----DECLARE-CURSOR-statement--;----+--'

>-----+-------------------------------------+-------------------->
|  .-------------------------------.  |
|  V                               |  |
'----| handler-declaration |--;----+--'

.-------------------------------.
V                               |
>--------SQL-procedure-statement--;---+---END--+--------+------><
'-label--'


语法说明：
1.label:代表一个整体，同时也指定了一个变量可见范围
2.NOT ATOMIC，ATOMIC：是否把过程体当成一个整体，如果为整体（指定为ATOMIC），则表示当存储过程发生错误时会把所有执行的语句进行rollback，否则（指定为NOT ATOMIC，默认）不回滚
3.SQL-variable-declaration：变量声明

DECLARE variable-name data-type [DEFAULT value]

4.condition-declaration:条件声明

|---DECLARE--condition-name--CONDITION--FOR--------------------->
.-VALUE-.
.-SQLSTATE--+-------+---.
>----+-----------------------+---string-constant----------------|

5.return-codes-declaration:返回码的声明，表示存储过程执行的状态

DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INT DEFAULT 0;

6.DECLARE-CURSOR-statement:游标的声明，用于访问所获取的结果集信息

>>-DECLARE--cursor-name--CURSOR----+------------+--------------->
'-WITH HOLD--'
>-----+------------------------------+-------------------------->
|              .-TO CALLER--.  |
'-WITH RETURN--+------------+--'
'-TO CLIENT--'
>----FOR--+-select-statement-+---------------------------------><
'-statement-name---'

7.handler-declaration:处理器的声明，用于指示当存储过程发生异常时而所执行的动作

>>---DECLARE----+-CONTINUE-+---HANDLER--FOR---------------------->
+-EXIT-----+
'-UNDO-----'
.-,-----------------------------------.
V               .-VALUE-.             |
>---------+-SQLSTATE--+-------+--string--+--+------------------->
+-condition-name---------------+
+-SQLEXCEPTION-----------------+
+-SQLWARNING-------------------+
'-NOT FOUND--------------------'
>----SQL-procedure-statement------------------------------------|


[variable assgin statement]
[other statement]
[logic statement]

1.variable assign statement:变量赋值语句
常见的变量赋值语句有：SET，VALUES INTO，SELECT INTO,FETCH语句

SET variable-name = value
SET variable-name = (SELECT statement)


VALUES value INTO variable-name


SELECT {table-row,...} INTO {variable-name,..} FROM <SELECT statement>


>>-FETCH--+-------+---cursor-name------------------------------->
'-FROM--'
.-,----------------.
V                  |
>------INTO-----variable-name----+-----------------------------><

2.logic statement:逻辑语句
常见的逻辑语句有：IF，CASE，FOR,WHILE,ITERATE,LEAVE,LOOP,REPEAT,GOTO语句

>>-IF--search-condition-THEN----------------------------------->
.------------------------------.
V                              |
>--------SQL-procedure-statement-;---+------------------------->
.-------------------------------------------------------------.
V                                                             |
>----+------------------------------------------------------------+-+>
|                                .-------------------------. |
|                                V                         | |
'-ELSEIF-search-condition-THEN--SQL-procedure-statement-;--+-'
>-----+------------------------------------------+--END IF-----><
|        .------------------------------.  |
|        V                              |  |
'-ELSE-----SQL-procedure-statement-;---+---'


>>-CASE----+-| searched-case-statement-when-clause |-+---------->
'-| simple-case-statement-when-clause |---'
>----END CASE--------------------------------------------------><
simple-case-statement-when-clause
|---expression-------------------------------------------------->
.-------------------------------------------------------------.
|                          .-------------------------------.  |
V                          V                               |  |
>--------WHEN--expression--THEN-----SQL-procedure-statement--;---+--+>
>-----+------------------------------------------+--------------|
|        .------------------------------.  |
|        V                              |  |
'-ELSE-----SQL-procedure-statement--;---+--'
searched-case-statement-when-clause
.-------------------------------------------------------------.
|                           .------------------------------.  |
V                           V                              |  |
>--WHEN--search-condition--THEN---SQL-procedure-statement--;---+--+->
>----+------------------------------------------+---------------|
|        .------------------------------.  |
|        V                              |  |
'-ELSE-----SQL-procedure-statement--;---+--'


>>-+--------+--FOR--for-loop-name--AS--------------------------->
'-label:-'
>--+----------------------------------------------+------------->
|                                              |
'-cursor-name--CURSOR--+-----------+--FOR------'
'-WITH HOLD-'
.----------------------------.
V                            |
>--select-statement--DO----SQL-procedure-statement--;-+--------->
>--END FOR--+-------+------------------------------------------><
'-label-'


>>-+---------+--WHILE--search-condition--DO--------------------->
'-:label--'
.-------------------------------.
V                               |
>--------SQL-procedure-statement--;---+--END WHILE-------------->
>-----+--------+-----------------------------------------------><
'-label--'


.-------------------------------.
V                               |
>>-+---------+--REPEAT-------SQL-procedure-statement--;---+----->
'-label:--'
>----UNTIL--search-condition---END REPEAT----+--------+--------><
'-label--'


.-------------------------------.
V                               |
>>-+---------+--LOOP-------SQL-procedure-statement--;---+------->
'-label:--'
>----END LOOP----+--------+------------------------------------><
'-label--'


>>-GOTO--label-------------------------------------------------><
>>-ITERATE--label----------------------------------------------><
>>-LEAVE--label------------------------------------------------><

其他语句：RETURN,OPEN,CLOSE,CALL

>>-RETURN--+------------+--------------------------------------><
'-expression-'

1.说明：RETURN是立即结果存储过程而返回一个值

>>--OPEN--cursor-name------------------------------------------><

1.说明：一般要使用游标之前，先declare，再open，这样游标才能fetch,最后close

>>--CLOSE--cursor-name-----------------------------------------><

1.说明：关闭游标

>>--CALL--procedure-name(parameter list)-----------------------><

1.说明：调用其它的存储过程，一般IN类型的参数用户传值，OUT类型的参数用"?"代替，如
  创建一个求两整数和的存储过程：
   CAREATE PROCEDURE SUM(IN p_one INT, IN p_two INT, OUT p_result INT)
   ......
  调用这个存储过程：
   CALL SUM(2,4,?)或CALL <user-schema>.SUM(2,4,?)
删除一个已创建的存储过程，语法：

DROP SPECIFIC PROCEDURE specific-name
DROP PROCEDURE procedure-name(parameter list)

example:
  DROP SPECIFIC PROCEDURE SUM_ab;(假设上面SUM存储过程的SPECIFIC为SUM_ab)
  DROP PROCEDURE SUM(INT,INT,INT)