DB2 SQL存储过程语法_存储过程dynamic result sets 0-CSDN博客

权威指南……

　　创建SQL存储过程(CREATE PROCEDURE (SQL) statement )

　　语法格式如下：

>>-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-.     .-7 OLD SAVEPOINT LEVEL-.
>--+---------------------------+--*--+---------------------+---->
                          ’-7 NEW SAVEPOINT LEVEL-’
    .-LANGUAGE SQL-.     .-7 EXTERNAL ACTION----.
>--7 *--+--------------+--*--+--------------------+--*------------>
                  ’-7 NO EXTERNAL ACTION-’

>--+------------------------------+--3 *-------------------------->
’-3 PARAMETER CCSID--+-3 ASCII---+-’
’-3 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：存储过程的主体

　　例子1：产生一个SQL存储过程，返回员工的平均薪水. 返回所有员工超过平均薪水的数额，结果集包括name, position, and salary字段(参考数据库为db2的示例数据库sample)。

　　CREATE PROCEDURE MEDIAN_RESULT_SET (OUT medianSalary DOUBLE)
　　RESULT SETS 1
　　LANGUAGE SQL
　　BEGIN
　　DECLARE v_numRecords INT DEFAULT 1;
　　DECLARE v_counter INT DEFAULT 0;
　　DECLARE c1 CURSOR FOR
　　SELECT CAST(salary AS DOUBLE)
　　FROM staff
　　ORDER BY salary;
　　DECLARE c2 CURSOR WITH RETURN FOR
　　SELECT name, job, CAST(salary AS INTEGER)
　　FROM staff
　　WHERE salary > medianSalary
　　ORDER BY salary;
　　DECLARE EXIT HANDLER FOR NOT FOUND
　　SET medianSalary = 6666;
　　SET medianSalary = 0;
　　SELECT COUNT(*) INTO v_numRecords
　　FROM STAFF;
　　OPEN c1;
　　WHILE v_counter < (v_numRecords / 2 + 1)
　　DO
　　FETCH c1 INTO medianSalary;
　　SET v_counter = v_counter + 1;
　　END WHILE;
　　CLOSE c1;
　　OPEN c2;
　　END