Oracle stored procedures and functions

1. In Oracle, the stored procedure consists of three parts: the definition section, the execution section, and the Exception handling section (that is, the exception)

EG1: Enter the employee number to inquire about the employee's name and salary

Create or Repalce procedure Mypro2 is

Declare--defining parts, defining variables and constants, etc., variable definitions usually begin with v_, and constant definitions usually begin with C_

V_ename varchar2 (20);

V_sal number (7,2);

begin--Executive Section

Select Ename,sal to V_ename,v_sal from EMP where empno=&no;

Dbms_output.put_line (' Employee's name is: ' | | v_ename| | ' Wages are ' | | V_sal);

exception--Exception Handling Section

When No_data_found Then

Dbms_output.put_line (' The employee number you entered does not exist ');

End

EG2: Stored procedure with parameters

--Enter employee's name and new salary, change salary according to name

Create or Replace procedure Mypro2 (pename varchar2,psal number) is

Begin

Update emp set sal=psal where Ename=pename;

End

Methods for calling stored procedures:

A:exec Mypro1 () or call Mypro1 ();

B:exec Mypro1 (' Scott ', 1200) or call Mypro1 (' Scott ', 1200);

2. Functions

function must have a return value of

The structure is as follows: annual salary According to employee's name

Create or Repalce function myfun1 (fename varchar2) return number is yearsal nunber;

Begin

Select SAL*12+NVL (comm,0) to yearsal from EMP where ename=fename;

return yearsal;

End

Methods for calling Functions:

sql> var ys number;
Sql> call Myf1 (' SCOTT ') Into:ys;

Oracle stored procedures and functions