Oracle存储过程记实（动态游标）

create or replace procedure P_TP_SQHDFJ
as
    str_sql varchar2(300);
    type ref_cursor_type is ref cursor; --定义一个动态（弱）游标
    sqhd_fj_list ref_cursor_type;--定义游标类型
    fj sqhd_fj%rowtype;--定义变量类型，sqhd_fj是表名
    rw_bh varchar2(50);
    varNum number;
begin
    --创建中间表（临时表）
    select count(*) into varNum from user_tables where table_name=upper('sqhd_fj_temp');

    if varNum > 0 then
        execute immediate 'drop table sqhd_fj_temp';
        str_sql := 'create table sqhd_fj_temp as select * from sqhd_fj@sy_wwsqhdfj where sfsc is null';
        execute immediate str_sql;
    elsif varNum < to_number(1) then --如果没有直接创建
        str_sql := 'create table sqhd_fj_temp as select * from sqhd_fj@sy_wwsqhdfj where sfsc is null';
        execute immediate str_sql;
    end if;

    --将临时表数据批量插入正式表
    str_sql := 'insert into sqhd_fj select id,blobvalue,filename,scsj from sqhd_fj_temp';
    execute immediate str_sql;
    
    --遍历动态游标修改互联网区数据库附件上传标志
    str_sql := 'select id,blobvalue,filename,scsj from sqhd_fj_temp';
    
    open sqhd_fj_list for str_sql;--打开游标
    loop
        fetch sqhd_fj_list into fj;--循环遍历游标给变量fj赋值
        exit when sqhd_fj_list%notfound;
        
        rw_bh := fj.id;
        update sqhd_fj@sy_wwsqhdfj set sfsc = 'Y' where id = rw_bh;
        update dqy_sqhd_sqsqb set fjtbbz = 'Y' where uuid = rw_bh;

    end loop;
    close sqhd_fj_list;--关闭游标

    --删除临时中间表
    str_sql := 'drop table sqhd_fj_temp';
    execute immediate str_sql;

    commit;

end;