Oracle Stored Procedure exception capture

Oracle stored procedure exception capture learning, execution, and presentation process:

Stored procedures:

    CREATE OR REPLACE PROCEDUREsp_test_2 (param1inch int,--Input ParametersParam2inch int, Out_return outvarchar2 --return Results    )       is         --Global VariablesValint; Errorexception exception; --declaring exceptionsErrorCode Number;--Exception CodeErrorMsgvarchar2( +);--Exception InformationFlagvarchar2(Ten); beginflag:= 'true'; Out_return:= 'flag=' ||Flag|| ', errorcode=' ||ErrorCode|| ', errormsg=' ||errormsg; Val:=param1/param2; --/*  Exception whenErrorexception ThenErrorCode:=SQLCODE; ErrorMsg:=SUBSTR (SQLERRM,1, $); Flag:= 'false'; Out_return:= 'flag=' ||Flag|| ', errorcode=' ||ErrorCode|| ', errormsg=' ||errormsg;  whenOthers ThenErrorCode:=SQLCODE; ErrorMsg:=SUBSTR (SQLERRM,1, $); Flag:= 'false'; Out_return:= 'flag=' ||Flag|| ', errorcode=' ||ErrorCode|| ', errormsg=' ||errormsg; --dbms_output.put_line (ErrorCode | | ', ' | |   ERRORMSG);          --*/      Endsp_test_2;

To demonstrate a stored procedure:

    DECLAREOut_returnvarchar2( +); Valint;--Global VariablesErrorexception exception;--declaring exceptionsErrorCode Number;--Exception EncodingErrorMsgvarchar2( +);--Exception InformationFlagvarchar2(Ten); beginflag:= 'true'; Out_return:= 'flag=' ||Flag|| ', errorcode=' ||ErrorCode|| ', errormsg=' ||errormsg; Val:= 1/0; Exception--exception snapping, do not put the required code behind the exception catch, there is an exception to execute the exception code under all code, no exception will not be executed              whenErrorexception ThenErrorCode:=SQLCODE; ErrorMsg:=SUBSTR (SQLERRM,1, $); Flag:= 'false'; Out_return:= 'flag=' ||Flag|| ', errorcode=' ||ErrorCode|| ', errormsg=' ||errormsg;  whenOthers ThenErrorCode:=SQLCODE; ErrorMsg:=SUBSTR (SQLERRM,1, $); Flag:= 'false'; Out_return:= 'flag=' ||Flag|| ', errorcode=' ||ErrorCode|| ', errormsg=' ||errormsg;           Dbms_output.put_line (Out_return); End;

Execute stored procedure in Sqlplus:

    DECLARE       varchar2 (+);             begin            sp_test_2 (1,0, out_return);            -- Print results      End;  

Execute the Stored procedure 1 divided by 0 results:

Oracle Stored Procedure exception capture