Oracle exception + Stored Procedure + Function

-- First exception
Declare
V_num number;
Begin
V_num: = 10/0;
Exception
When zero_divide then -- capture specific exceptions
Dbms_output.put_line ('divisor cannot be 0 ');
When others then
Dbms_output.put_line ('error occurred '); -- catch all exceptions
End;
-- Application: Error Tracking (defect tracking)
Declare
V_trace varchar2 (200 );
V_num number;
Begin
V_trace: = 'the first business logic is in progress. Error code: 1001 ';
V_num: = 1234-435;
V_trace: = 'second business logic in progress, error no.: 8080 ';
V_num: = 56345-435;
V_trace: = 'third business logic in progress, error no.: 8080 ';
V_num: = 322-6786;
When others then
Dbms_output.put_line ('error occurred '); -- the user determines whether to prompt
-- Insert into log table (v_trace ......);

End;

-- The second type of exception: Define the exception number in advance and then capture (also a custom exception)
Declare
V_usermetadata t exception;
Pragma exception_init (v_usert T,-1476 );
V_num number;
Begin
V_num: = 10/0;
Exception
When v_user=t then
Dbms_output.put_line ('divisor cannot be 0 ');
End;
-- Third exception: custom exception
Declare
V_num number;
V_except exception;
Begin
V_num: = 20;
If v_num> 10 then
Raise v_except; -- an exception occurred when the system started.
End if;
Exception
When v_except then
Dbms_output.put_line ('error occurred ');
End;
-- Its application: can be used as an alert
Declare
V_num number;
E_wait t exception;
Begin
V_num: = 15;
If v_num> 10 and v_num <20 then
Raise e_except;
End if;
Exception
When e_except then
Dbms_output.put_line (to_char (sysdate, 'yyyy-mm-dd hh24: MI: ss') | ''| 'the current indicator of a device is: '| to_char (v_num ));
When others then
Dbms_output.put_line (sqlcode | '-' | sqlerrm );
End;

 

-- Stored Procedure
Declare
V_num number;
Begin
V_num: = 123;
End;

Create or replace procedure getname
Is
V_num number;
Begin
V_num: = 123;
End;
-- Call Method
-- Method 1: Command
SQL> execute getname;

PL/SQL procedure successfully completed

SQL>

-- Method 2: Program
Begin
Getname;
End;

Create or replace procedure getname1 (I _in_n_empno in number)
Is
V_name varchar2 (20 );
Begin
Select ename into v_name from EMP where empno = I _in_n_empno;
Dbms_output.put_line (v_name );
End;
-- Method 1
SQL> set serveroutput on
SQL> execute getname1 (7369 );

Smith

PL/SQL procedure successfully completed

SQL>

-- Method 2
Begin
Getname1 (0, 7369 );
End;

-- Out Parameter
Create or replace procedure getname2 (I _in_n_empno in number, I _out_c_name out varchar2)
Is
Begin
Select ename into I _out_c_name from EMP where empno = I _in_n_empno;
End;

-- Method 1
SQL> variable v_name varchar2 (20 );
SQL> execute getname2 (7369,: v_name );

PL/SQL procedure successfully completed
V_name
---------
Smith

SQL> select: v_name from dual;

: V_name
--------------------------------------------------------------------------------
Smith
V_name
---------
Smith

SQL>

-- Method 2
Declare
V_name varchar2 (20 );
Begin
Getname2 (7369, v_name );
Dbms_output.put_line (v_name );
End;

-- In out Parameter
Create or replace procedure getname3 (I _inout_c_name in out varchar2)
Is
Begin
Select ename into I _inout_c_name from EMP where empno = to_number (I _inout_c_name );
End;
-- Call Method
Declare
V_name varchar2 (20 );
Begin
V_name: = '000000 ';
Getname3 (v_name );
Dbms_output.put_line (v_name );
End;

-- Function
Declare
V_num number;
Begin
V_num: = 123;
End;

Create or replace function getfuncname
Return number
Is
V_num number;
Begin
V_num: = 123;
Return v_num;
End;

-- Method 1
SQL> select getfuncname from dual;

Getfuncname
-----------
123

SQL>
-- Method 2
Declare
V_num number;
Begin
-- Select getfuncname into v_num from dual;
V_num: = getfuncname ();
Dbms_output.put_line (v_num );
End;

Create or replace function getfuncname1 (I _in_n_empno number)
Return varchar2
Is
V_name varchar2 (20 );
Begin
Select ename into v_name from EMP where empno = I _in_n_empno;
Return v_name;
End;

-- Method 1
SQL> select getfuncname1 (7369) from dual;

Getfuncname1 (7369)
--------------------------------------------------------------------------------
Smith

SQL>
-- Method 2
Declare
V_name varchar2 (20 );
Begin
V_name: = getfuncname1 (7369 );
Dbms_output.put_line (v_name );
End;

 

Create or replace function getfuncname3 (I _in_n_empno in number, I _out_c_name out varchar2)
Return number
Is
Begin
Select ename into I _out_c_name from EMP where empno = I _in_n_empno;
Return 1;
End;
-- Call Method
Declare
V_name varchar2 (20 );
V_num number;
Begin
V_num: = getfuncname3 (7369, v_name );
Dbms_output.put_line (v_name | 'Return value: '| to_char (v_num ));
End;

 

Create or replace function getfuncname4 (I _inout_c_name in out varchar2)
Return number
Is
Begin
Select ename into I _inout_c_name from EMP where empno = to_number (I _inout_c_name );
Return 1;
End;

-- Call Method
Declare
V_name varchar2 (20 );
V_num number;
Begin
V_name: = '000000 ';
V_num: = getfuncname4 (v_name );
Dbms_output.put_line (v_name | 'Return value: '| to_char (v_num ));
End;