oracle存储过程常见异常,Oracle_存储过程exception异常处理大全及实例经典最终

一、一个实例

Commit;

create or replace procedure PROC_SUNHONGBO_MONCHNL(tim varchar2) is

timest varchar(8);

timday varchar(33);

sqlsql varchar(6666);

no_result EXCEPTION;

begin

Commit;

select to_char(sysdate,'yyyymmddHH24miss') tima into timday from dual;

select to_char(add_months(sysdate,-1),'yyyymm') mon into timest from dual;

select tim mon into timest from dual;

execute immediate 'delete report.wo_sunhongbo_chnl where mon = '||tim;

Commit;

/*1、添加所需要的列real_charge + time ，如果有就抛出异常后继续执行**********/ BEGIN

execute immediate 'alter table shiyw11.t_my add real_charge'||tim||' varchar2(66)'; EXCEPTION

WHEN no_result THEN

DBMS_OUTPUT.PUT_LINE('你的数据更新语句失败了!');

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('你的数据更新语句!已经存在该列1：'||tim);

END;

execute immediate 'update shiyw11.t_my set real_charge'||tim||' = null';

Commit;

Commit;

dbms_output.put_line('timest:'||timest||';tim:'||tim||';timest:'||sqlsql);

Commit;

end PROC_SUNHONGBO_MONCHNL;

/

Commit;