存储过程cursor

 

   前面我们已经讨论了如何声明存储过程的返回结果集。这里我们讨论一下结果集返回类型的问题。结果集的返回类型有两种：调用者(CALLER) 和客户应用(CLIENT)。首先我们看一下声明这两种游标的例子：


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   



   代码中rs1游标的DECLAER语句中包含WITH RETURN TO CLIENT子句，表示结果集返回给客户应用(CLIENT)。rs2游标的DECLARE语句中包含WITH RETURN TO CALLER子句，表示结果集返回给调用者(CALLER)。

   游标返回给调用者(CALLER)表示由存储过程的调用者接收结果集，而不考虑调用者是否是另一个存储过程，还是客户应用。图（1）中存储过程PROZ如果声明为WITH RETURN TO CALLER，那么结果集会返回给存储过程PROY，Client Application是不会得到PROZ返回的结果集的。


   游标返回给客户应用（CLIENT）表示由发出最初 CALL 语句的客户应用接收结果集，即使结果集由嵌套层次中的 15 层深的嵌套存储过程发出也是如此。图1中存储过程 PROZ 如果声明为 WITH RETURN TO CLIENT，那么结果集会返回给 Client Application。返回给客户应用（CLIENT）的游标声明是我们经常使用的，也是默认的结果集类型。

    在声明返回类型时，我们要认真考虑一下，我们需要把结果集返回给谁，以免丢失返回集，导致程序错误。

例一：
CREATE PROCEDURE "ARS"."TEST_CURSOR"
(
   OUT "O_MSG" VARCHAR(500)
)
  SPECIFIC "ARS"."TEST_CURSOR"
  LANGUAGE SQL
  NOT DETERMINISTIC
  CALLED ON NULL INPUT
  EXTERNAL ACTION
  OLD SAVEPOINT LEVEL
  MODIFIES SQL DATA
  INHERIT SPECIAL REGISTERS
  begin
    declare i int default 1;

for v_cur as select * from test_cursor
do
      update ars.test_cursor a set  score=v_cur.score+1 where a.score=v_cur.score ;
 
end for;

set o_msg='执行成功';

end;



例二：
CREATE PROCEDURE "ARS"."TEST_CURSOR1"
(
   OUT "O_MSG" VARCHAR(500)
)
  SPECIFIC "ARS"."TEST_CURSOR"
  LANGUAGE SQL
  NOT DETERMINISTIC
  CALLED ON NULL INPUT
  EXTERNAL ACTION
  OLD SAVEPOINT LEVEL
  MODIFIES SQL DATA
  DYNAMIC RESULT SETS 2
  INHERIT SPECIAL REGISTERS
  begin

declare v_cur cursor  with return to caller for
    select * from ars.test_cursor;
open v_cur;
set o_msg='执行成功';
end;


例三：