Oracle stored procedures and Oracle Storage

An Oracle stored procedure consists of three parts: a procedure declaration, a procedure part, and a stored procedure exception.

Oracle stored procedures can have parameter-free stored procedures and parameter-stored procedures.

Procedure syntax of a non-parametric program

Create or replace procedure Noparpro

as;

Begin

;

Exception//Stored procedure exception

;

End

Examples of stored procedures with parameters

Create or Replace procedure Queryempname (Sfindno Emp.empno%type) as

Sname Emp.ename%type;

Sjob Emp.job%type;

Begin

....

exception

....

End

Three, with the parameter stored procedure contains the assignment method

Create or Replace procedure Runbyparmeters (isal in Emp.sal%type,

Sname out varchar,sjob in out varchar)

As icount number;

Begin

Select COUNT (*) into the icount from EMP where sal>isal and job=sjob;

If Icount=1 Then

....

Else

....

End If;

exception

When Too_many_rows Then

Dbms_output. Put_Line (' return value more than 1 rows ');

When others then

Dbms_output. Put_Line (' Mistakes in the runbyparmeters process! ');

End

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/