for example:
SQL> create table t1(a int primary key);Table created.SQL> insert into t1 values (1);1 row created.SQL> create or replace procedure test(a in number)
2 as
3 v_sqlcode number;
4 v_sqlerrm varchar2(200);
5 begin
6 insert into t1 values(a);
7 exception
8 when others then
9 v_sqlcode:=sqlcode;
10 v_sqlerrm:=sqlerrm;
11 dbms_output.put_line(v_sqlcode);
12 dbms_output.put_line(v_sqlerrm);
13 end;
14 /Procedure created.SQL> exec test(2)PL/SQL procedure successfully completed.SQL> exec test(2)
-1
ORA-00001: unique constraint (GOWIN.SYS_C005281) violatedPL/SQL procedure successfully completed.
SQL> create table t1(a int primary key);Table created.SQL> insert into t1 values (1);1 row created.SQL> create or replace procedure test(a in number)
2 as
3 v_sqlcode number;
4 v_sqlerrm varchar2(200);
5 begin
6 insert into t1 values(a);
7 exception
8 when others then
9 v_sqlcode:=sqlcode;
10 v_sqlerrm:=sqlerrm;
11 dbms_output.put_line(v_sqlcode);
12 dbms_output.put_line(v_sqlerrm);
13 end;
14 /Procedure created.SQL> exec test(2)PL/SQL procedure successfully completed.SQL> exec test(2)
-1
ORA-00001: unique constraint (GOWIN.SYS_C005281) violatedPL/SQL procedure successfully completed.
tempvar varchar2(20);
Begin
intErrorID := 0;
Select value Into tempvar From mytable Where Name='TRANSDATE';
If tempvar Is Not Null Then
Raise e_TransDate_Not_Found;
End If;
Exception
When e_TransDate_Not_Found Then
--DBMS_OUTPUT.PUT_LINE('TransDate Not Found');
--Rollback;
intErrorID := 1;
When others Then
intErrorID := 1;
End;
* 程序名:撤销支出结算表
* 程序功能:撤销一次性支出结算表,将锁定标志更新为'8'作废
* 输入:结算表编号
* 输出:返回值(=0成功,其它为失败)
* 出错信息
* 作者:Guocy
* 版本日期:2003-12-16
* 执行类型:用户手工执行
******************************************************************************/
CREATE OR REPLACE PROCEDURE SP_5010_CancelPayoutDecl(
pi_jsbbh IN VARCHAR2,
po_RetCode OUT NUMBER,
po_ErrMsg OUT VARCHAR2)
AS
/*--- 撤销支出结算表 Version:20031216 ----------------------------------- */
/*--- 私有局部变量声明 ---------------------------------------------------*/
n_count NUMBER;
BEGIN -- 初始化
po_RetCode := 0; -- 成功
po_ErrMsg := 'SUCCESS'; n_count := 0;
SELECT COUNT(*)
INTO n_count
FROM sti_ins_payout_decl
WHERE jsbbh = pi_jsbbh;
IF n_count = 0 THEN
po_RetCode := -1;
po_ErrMsg := '找不到此业务对应的支出结算表。';
RETURN;
ELSE
UPDATE sti_ins_payout_decl
SET sdbz_id = '8'
WHERE jsbbh = pi_jsbbh;
END IF;
EXCEPTION
WHEN OTHERS THEN
po_RetCode := -1;
po_ErrMsg := '撤销支出结算表异常错误,描述:'||SQLERRM;
END;
/