存储过程（2）----Oracle-优快云博客

本文链接：https://blog.youkuaiyun.com/xiao__miao/article/details/77273846

本文介绍了一个Oracle存储过程示例，用于批量更新表中的记录。该过程使用游标和FORALL语句，每次循环批量获取并更新最多1000条记录。同时展示了EXECUTE IMMEDIATE动态SQL执行的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

上一篇介绍的是mysql的存储过程的一些语法

如：concat在Oracle里是没有的，Oracle拼接字符是用‘||’

Oracle用游标是这样的

CREATE OR REPLACE PROCEDURE UPDATE_SNS_USERSCORE(table_name  in varchar2) as

    TYPE i_rowid IS TABLE OF ROWID;
    v_i_rowid i_rowid;
    type ref_cursor is ref cursor;
    c_rowid ref_cursor;

    v_errmsg varchar2(1024);
    BEGIN
    OPEN c_rowid FOR 'SELECT rowid FROM '||table_name ;
    LOOP
    fetch c_rowid bulk collect
    into v_i_rowid LIMIT 1000;
    FORALL i IN 1 .. v_i_rowid.COUNT EXECUTE IMMEDIATE
    'UPDATE '|| table_name||' SET
    SNS_SCORE=0,READ_SCORE=0,FIRST_RANK=1,SECOND_RANK=1,THIRD_RANK=1,LOGIN_FLAG=0,UPDATE_TIME=SYSDATE
		WHERE rowid = :1'
		USING v_i_rowid(i);
		COMMIT;
		exit when c_rowid%NOTFOUND;
		END LOOP;
		COMMIT;
		close c_rowid;

    exception
  when others then
    rollback;
    v_errmsg := 'sqlexception~~sqlcode:' || to_char(sqlcode) ||
                ' sqlstate:' || substr(sqlerrm, 1, 512);
    prc_iread_sys_writelog(2, 4, 'UPDATE_SNS_USERSCORE', v_errmsg, '');
    commit;
end UPDATE_SNS_USERSCORE;

type wmshuaiqi:定义类型变量

is ref cursor :相当于数据类型，不过是引用游标的数据类型

因为PL/SQL不允许存储过程或函数直接返回结果集，但可以返回类型变量，于是引用游标的类型变量作为输出参数或返回值就应运而生了。

fetch c_rowid bulk collect into v_i_rowid LIMIT 1000; -- 把查询到到结果集放到rowid里，限制是1000条

FORALL i in 1..v_i_rowid.COUNT 取值的时候只要 v_i_rowid(i) 就跟数组的原理是一样的

execute immediate的简单用法(oracle)

create or replace procedure proc_test(
--参数区域
)
is 
--变量区域
    --sql脚本
    v_sql varchar2(2000) :='';
    --记录学生数量
    v_num number;
begin
--执行区域


    -- execute immediate用法1：立刻执行sql语句
    v_sql := 'create or replace view myview as select id,name from student';
    execute immediate v_sql;
    
    --- execute immediate用法2：立刻执行sql语句，并赋值给某个变量
    v_sql := 'select count(1) from student';
    execute immediate v_sql into v_num;
    
    -- execute immediate用法3：带参数的sql
    v_sql:='select * from student t where t.name=:1 and t.age=:2'; 
    execute immediate v_sql using 'ZhangSan',23;
    
end proc_test;