DB2 Procedure样例

鸦鸦

　　下面是两个DB2 Procedure的例子：
　　例子1：
　　CREATE PROCEDURE getPeople(IN piAge INTEGER)
DYNAMIC RESULT SETS 2
READS SQL DATA
LANGUAGE SQL
BEGIN
    DECLARE rs1 CURSOR WITH RETURN TO CLIENT FOR
        SELECT name, age FROM person
            WHERE age<piAge;
    DECLARE rs2 CURSOR WITH RETURN TO CALLER FOR
        SELECT NAME, age FROM person
            WHERE age>piAge;           
    OPEN rs1;
    OPEN rs2;
END  
　　例子2：
　　create procedure Dynamic_Cusor(IN SAL INTEGER)
DYNAMIC RESULT SETS 1
READS SQL DATA
LANGUAGE SQL
BEGIN
　　DECLARE stmt varchar(255);
   DECLARE st STATEMENT;
   DECLARE cur1 CURSOR WITH RETURN FOR st;
   SET stmt='select name,dept,job,salary from staff where salary>?';
   PREPARE st FROM stmt;
   OPEN cur1 USING SAL;
END