Oracle stored procedures and functions, oracle stored procedures

Oracle stored procedures and functions, oracle stored procedures

1. in Oracle, stored procedures consist of three parts: the definition part, the execution part, and the exception handling part (that is, the exception)

Eg1: Enter the employee ID to query the employee's name and salary.

Create or repalce procedure mypro2 is

Declare -- definition part, defining variables and constants. variable definitions generally start with V _, and constant definitions generally start with C _.

V_ename varchar2 (20 );

V_sal number (7,2 );

Begin -- execution part

Select ename, sal into v_ename, v_sal from emp where empno = & no;

Dbms_output.put_line ('employee name: '| v_ename | 'salary:' | v_sal );

Exception -- exception Handling

When no_data_found then

Dbms_output.put_line ('the employee ID you entered does not exist ');

End;

Eg2: stored procedure with Parameters

-- Enter the employee name and new salary, and change the salary according to the name

Create or replace procedure mypro2 (pename varchar2, psal number) is

Begin

Update emp set sal = psal where ename = pename;

End;

Method of calling a stored procedure:

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

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

2. Functions

The function must return values.

The structure is as follows: Calculate the annual salary based on the employee name

Create or repalce functionMyfun1 (fename varchar2)ReturnNumberIsYearsal nunber;

Begin

Select sal * 12 + nvl (comm, 0) into yearsal from emp where ename = fename;

ReturnYearsal;

End;

Function call method:

SQL> var ys number;
SQL> call myf1 ('Scott ') into: ys;