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