Sunday, March 04, 2012

PL/SQL Procedures



PL/SQL Stored Procedures

Stored procedures are called blocks that allow grouping and organization of SQL commands and PL/SQL. Both source code and the executable are stored in the database. By storing it in the database, the code is situated in accessible and centralized location. Because the executable code is located in the database, invoke stored procedures is more efficient.

We can pass parameters to procedures in three ways.
1) IN-parameters
2) OUT-parameters
3) IN OUT-parameters

A procedure may or may not return any value.

General Syntax to create a procedure is:

CREATE [OR REPLACE] PROCEDURE proc_name [list of parameters]
IS
Declaration section
BEGIN
Execution section
EXCEPTION
Exception section
END;


Example Stored Procedure:

CREATE OR REPLACE PROCEDURE raise_salary
(p_id IN employees.employee_id%Type,
p_percent IN NUMBER)
IS
BEGIN
UPDATE employees
SET salary= salary*(1+p_percent/100)
WHERE employee_id = p_id;
END raise_salary;
Begin
raise_salary(200,10);
End;

No comments:

Post a Comment