Oracle Stored Procedure

I,Stored Procedure:

A)
Stored Procedures and functions are alsoPL/SQLBlock, which is stored in the dataPL/SQLBlock. However, stored procedures are stored and stored in the database in a named manner;

B)
Advantages of stored procedures:

I.Stored Procedures and functions are stored in the database as named database objects.

II.
Stored Procedures and functions can be ensured by the database. To use stored procedures and functions, you must have the authorization of the owner of the stored procedure, the stored procedure or callback function can be executed only when the authorized oil pot or the bucket is created.

III.
Information about stored procedures and functions is written into the database dictionary,1Therefore, stored procedures can be seen as a public module.

IV.
Similar to other advanced languages and functions. Parameters can be passed to stored procedures or functions.

C)
Stored Procedures and functions need to be compiled to eliminate syntax errors. They can only be called after compilation.

II,Create a stored procedure

A)
To create a stored procedure, you must haveCreate procedureOrCreate any procedure. This permission can be granted by the system administrator.

B)
Create [or replace] ProcedureStored Procedure name[(Parameters[In | Out | in out]Data Type…)] {As | is}

Begin

Executable part

[ExceptionError Handling]

End [Process name];

C)
Keywords

I. or replace indicates that if the stored procedure already exists, it is overwritten with a new stored procedure, which is usually used to reconstruct the stored procedure.

II.
the parameter section defines multiple parameters. ( if no parameter exists, you can omit ) . There are three types of parameters: in , out and
In out . If the parameter format is not specified, the default value is in .

III.
KeywordsAsIt can also be writtenIs, Followed by the description of the process. Local variables of the process can be defined here.

III,Call Stored Procedure

A)
Method

I.Execute
Mode name.Stored Procedure name[(Parameters…)];

II.
Begin

Mode name.Stored Procedure name[(Parameters…)]

End;

B)
The passed parameters must be consistent with the defined parameter type, number, and order.(If the parameter defines the default value,The parameter can be omitted during the call.)The parameter can be a variable. Constant or expression.

C)
For example, create a stored procedure that displays the total number of employees and execute the stored procedure.

Create or replace procedure emp_count --Create a stored procedure

Is

V_total number (10 );--Declare variables, but cannot be written hereDeclareTo define

Beging

Select count (*) into v_total from EMP;

Dbms_output.put_line ('The number of employees is: '| V_total );

End;

--Execute the Stored Procedure

Execute emp_count

D)
For example, compile a stored procedure for displaying employee information.Emp_list,And ReferenceEmp_countStored procedure.

Create or replace procedure emp_list --Create a stored procedure

Is

Cursor C is

Select empo, ename, Sal from EMP;

Begin

For v_emp in C Loop

Dbms_output.put_line (v_emp.empno | ',' | v_emp.ename | ',' | v_emp.sal );

End loop;

Emp_count;

End;

--Execute the Stored Procedure

Execute emp_list;