如何运行和调试Oracle存储过程
如何运行和调试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中
评论暂时关闭