第一种情况,先分析无参存储:
create or replace procedure p_test is
V_CURSOR SYS_REFCURSOR;--游标
V_ID VARCHAR(20); --id
V_NAME VARCHAR(50);--名称
V_ERROR varchar2(2000);--错误信息
begin
OPEN V_CURSOR FOR
--遍历t_test表
SELECT t.id, t.name
FROM t_test t;
loop
FETCH V_CURSOR
INTO V_ID, V_NAME;
EXIT WHEN V_CURSOR%NOTFOUND;
--插入日志表
insert into t_test_log
(id, name,createtime)
values
(To_Char(sysdate, 'yyyymmdd') ||
lpad(round(dbms_random.value(1, 9999999999)), 10, 0),
V_NAME,
sysdate);
commit;
end loop;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
V_ERROR := 'p_test 出错,' || SQLCODE || ':' ||
substr(SQLERRM, 1, 200);
INSERT INTO debug_table VALUES (V_ERROR, SYSDATE);
COMMIT;
end p_test;