PL/SQL Creating stored Procedures

1. Stored Procedure creation format:

create [or Replace] procedure procedure_name (parameter)
As/is
Declarations section
Begin
Executive Section
[exception
Exception handling Section]
End

Note: (1). The brackets are optional, which can or may not be in a stored procedure.

(2). If the stored procedure does not have parameters, do not use parentheses.

2. Call the stored procedure:

Format: Execute/exec procedure_name

An example invocation:

Begin

procedure_name (parameter);

End

　　

3. Learn to create a stored procedure:

(1). Create a non-parametric stored procedure
CREATE PROCEDURE Proc_test
Is
Begin
Dbms_output.put_line (' My first stored procedure ');
End

Call:

Begin
Proc_test;
End

(2). Create a stored procedure to calculate the square of a number.

Create or Replace procedure proc_test01 (a in number,b out number)
Is
Begin
B:=a*a;
End

Perform:

Declare
b number;
Begin
PROC_TEST01 (5,B);
Dbms_output.put_line (' 5 squared is: ' | | b);
End

(3). Create a stored procedure that requires you to enter a specified employee number to increase the employee's salary by 20% and to have exception handling;

Create or Replace procedure Proc_add_sal (Eno Employee.empno%type)
As
V_sal Employee.sal%type;
Sal_is_null exception;
Begin
Select Sal to V_sal from employee where Empno=eno;
If v_sal is null
Then
Raise Sal_is_null;
Else
Update employee set sal=sal*1.2 where Empno=eno;
End If;
exception
When Sal_is_null
Then
Dbms_output.put_line (' Employee wages do not exist ');
When No_data_found

Then
Dbms_output.put_line (' Employee number does not exist ');
When others

Then
Dbms_output.put_line (' other anomalies ');
End
　　
Begin
Proc_add_sal (eno=>9999);
End

4. Specify the permissions to execute the stored procedure to other users:

Grant execute on procedure_name to User/public;

5. Use Stored Procedure considerations:

(1). must contain declarations and executable parts;

(2). The call stored procedure has three parameters specified by location, specified by name, mixed designation;

(3). There are three types of parameters: In,out,in out;

(4). DDL cannot be used;

(5). Variable type with reference type;

(6). Stored procedures are best to declare exception handling.