如何运行和调试Oracle存储过程

1. 编写Oracle存储过程脚步如下：

CREATE OR REPLACE PROCEDURE P_AUTO_CREATE_PARTITION
（Result Out int,
LogErrDesc Out varchar2
）
is
type TypeTable is table of varchar2（20）;
CreatePartitionErr exception;
days TypeTable;
BEGIN
Result := 0;
SELECT DATETIME bulk collect into days FROM
（
SELECT TO_CHAR（TRUNC（SYSDATE-2,"dd"）,"YYYYMMDD"） AS DATETIME FROM DUAL
UNION
SELECT TO_CHAR（TRUNC（SYSDATE-1,"dd"）,"YYYYMMDD"） AS DATETIME FROM DUAL
UNION
SELECT TO_CHAR（TRUNC（SYSDATE,"dd"）,"YYYYMMDD"） AS DATETIME FROM DUAL
UNION
SELECT TO_CHAR（TRUNC（SYSDATE+1,"dd"）,"YYYYMMDD"） AS DATETIME FROM DUAL
UNION
SELECT TO_CHAR（TRUNC（SYSDATE+2,"dd"）,"YYYYMMDD"） AS DATETIME FROM DUAL
）;
for i in 1..days.count loop
if bruce_partiton.CreatePartitions（"T_CDT_1X_BASIC_Partion",days（i））<0 then raise CreatePartitionErr; end if;
end loop;
--名字写错了bruce_partiton，应该为bruce_partition
commit;
--异常处理
EXCEPTION
WHEN CreatePartitionErr THEN rollback; Result := -1; LogErrDesc := SQLERRM; commit; RETURN;
WHEN OTHERS THEN
rollback;
Result := -2;
LogErrDesc := "CDM_CTCALLTRACEINFO_PRC_NEW Fail！"||substr（dbms_utility.format_error_stack,1,200）;
commit;
RETURN;
END P_AUTO_CREATE_PARTITION;
/

2. 调试在PL/SQL中选择存储过程，右键TEST, ADD DEBUGINFO，

3. 运行，在Program Window中DBMS_OUTPUT 过多导致Oracle存储过程执行抛异常Oracle 设置归档与非归档模式相关资讯 Oracle教程

本文评论查看全部评论（0）