日期:2014-05-16  浏览次数:20494 次

第一次使用游标

create or replace procedure proc_test
as
strs varchar2(32);
begin
select requestid into strs from eweaver.uf_worktable where requestdate=to_char(sysdate,'yyyy-mm-dd')
dbms_output.put_line(strs);
end proc_test;

call proc_test();

 先定义一个cursor:

create table t_temp(requestid varchar2(32));

create or replace Procedure  P_MYPROC
IS
rid VARCHAR2(32):='';
rdate varchar2(20):='';
 --定义
 CURSOR MYCUR IS
       select requestid,requestdate from eweaver.uf_worktable
BEGIN
     --打开
     OPEN MYCUR;
     --遍历
     LOOP
         FETCH MYCUR
         INTO rid,rdate;
         --无数据时退出
         EXIT WHEN MYCUR%NOTFOUND;
          --DO SOMETHING HERE;
         update t_temp set requestid=rdate where requestid=rid;
         commit;
     END LOOP;

END P_MYPROC;

call p_myproc();


select * from t_temp;