Oracle Stored Procedure Syntax

A stored procedure consists of three parts: a declaration, an execution section, and an exception.
can have parameter-free programs and stored procedures with parameters.
Non-parametric program syntax
1 Create or replace procedure Noparpro
2 as;
3 begin
4;
5 exception
6;
7 End;
8

Instance of a stored procedure with parameters
1 Create or Replace procedure Queryempname (Sfindno Emp.empno%type) as
2 sname Emp.ename%type;
3 Sjob Emp.job%type;
4 begin
5...
7 exception
....
End;
15

With parameter stored procedure with assignment method
1 Create or Replace procedure Runbyparmeters (isal in Emp.sal%type,
Sname out varchar,sjob in out varchar)
2 as icount number;
3 begin
4 Select COUNT (*) into the icount from EMP where sal>isal and job=sjob;
5 if Icount=1 Then
6...
9 Else
10...
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 (' An error occurred during the runbyparmeters process. ');
End;
19

Procedure Call
Way One
1 declare
2 Realsal Emp.sal%type;
3 realname varchar (40);
4 realjob varchar (40);
5 begin
6 realsal:=1100;
7 realname:= ';
8 realjob:= ' clerk ';
9 runbyparmeters (Realsal,realname,realjob); --Must be in order
Ten dbms_output.   Put_Line (realname| | ' '|| Realjob);
one end;
12

Mode two
1 declare
2 Realsal Emp.sal%type;
3 realname varchar (40);
4 realjob varchar (40);
5 begin
6 realsal:=1100;
7 realname:= ';
8 realjob:= ' clerk ';
9 runbyparmeters (Sname=>realname,isal=>realsal,sjob=>realjob); --The specified value corresponds to variable order
Ten dbms_output.   Put_Line (realname| | ' '|| Realjob);
one end;
12