Oracle Database Stored Procedures Example

Stored procedure: A program that changes the state of a database object and can contain one or more behaviors, often by processing a record of one table and then putting it in another table.

1. Basic grammar:

CREATE [OR REPLACE] PROCEDURE procedure_name
[(Parameter_name [In | Out | In out] type[,....])]
{is | As}
[Local declarations]
BEGIN
Executable statements;
[EXCEPTION
exception_statements;
End procedure_name;

Example:

--/
CREATE OR REPLACE PROCEDURE proc_1 (num number, name VARCHAR2)
Is
BEGIN
INSERT INTO EMP (empno, ename) VALUES (num, name);
End;
/

Call:

--/
BEGIN
Proc_1 (1002, ' 1002 ');
End;
/

2, in and Out parameters:
The in parameter represents the input parameters of the stored procedure. The out parameter represents the output parameter of the stored procedure, and the output parameter changes the value of the parameter, passing it to a variable of the caller.
Example:

--/
CREATE OR REPLACE PROCEDURE proc_2 (num in number, v_emp out Emp%rowtype)
Is
BEGIN
SELECT * into V_emp from emp where empno = num;
End;
/

Call:

--/
DECLARE
var Emp%rowtype;
BEGIN
proc_2 (1000, VAR);
Dbms_output.put_line (Var.ename);
End;
/

3, at the same time with the in and out parameters:
The in out mode is a combination of in and out mode, also known as an input/output parameter.
When the procedure is invoked, the value of the actual parameter is passed to the procedure, and the formal parameter can be read out and written. When the procedure call ends, the control returns to the calling environment, and the contents of the formal argument are given the actual value.
Example:

--/
CREATE OR REPLACE PROCEDURE proc_3 (x in number, y into out number)
Is
V_TMP number;
BEGIN
V_tmp: = x + y;
Y: = v_tmp;
End;
/

Call:

--/
DECLARE
NUM1 number: = 1;
NUM2 number: = 2;
BEGIN
Proc_3 (NUM1, num2);
Dbms_output.put_line (NUM2);
End;
/

4,
The specific processing in the stored procedure relies on the basic syntax of PL SQL to implement.

Stored procedure Example: a salary of 10% for a specified employee on the basis of the original salary

/*
A salary of 10% for a specified employee on the basis of the original salary and for printing the salary before and after the salary
*/
sql> Create or Replace procedure raisesalary (Empid in number)
As
Psal emp.sal%type;--Save employee's current salary
Begin
--Check the employee's salary
Select Sal into Psal from EMP where empno=empid;
--Raise wages for the employee
Update emp Set sal = sal*1.1 where empno=empid;
--print salary before and after the raise
Dbms_output.put_line (' Employee number: ' | | empid | | ' Before the pay rise
' || Psal | | ' After the pay rise ' | | psal*1.1);
End
1/

Procedure created
--Stored procedure call
--Method One
Sql> set Serveroutput on
sql> exec raisesalary (7369);

Employee Number: 7369 Before salary rise
800 Salary Rise 880

Method Two
Set Serveroutput on
Begin
Raisesalary (7369);
End
/

Pl/sql procedure successfully completed


Stored functions
A function is a named stored program that takes parameters and returns a computed value. Functions and procedures have a similar structure, but you must have a return clause that returns the value of the function. Function description to specify the function name, the type of the result value, and the parameter type, and so on.

To establish the syntax for a stored function:

CREATE [OR REPLACE] Function name (argument list)
return function value type
As
Plsql subroutine body;


Example: Querying the yearly income of a worker.
Sql>/**/
/*
Check the total income of a worker
*/
Create or Replace function queryempsalary (empid in number)
return number
As
Psal number; --Define variables save employee's salary
Pcomm number; --Define variables to save employee bonuses
Begin
Select Sal,comm into Psal,pcomm from emp where empno = Empid;
Return PSAL*12+NVL (pcomm,0);
End
/

Function created

Call to the L function

Sql> Declare
V_sal number;
Begin
V_sal:=queryempsalary (7934);
Dbms_output.put_line (' salary is: ' | | | v_sal);
End
/

Salary is:15600

Pl/sql procedure successfully completed

Sql> begin
Dbms_output.put_line (' salary is: ' | | queryempsalary (7934));
End
/

Salary is:15600

Pl/sql procedure successfully completed