PL/SQL Procedures

Index - PL/SQL Tutorial

Introduction to PL/SQL
Chap 1 : Block structure and Advantages of PL/SQL
Chap 2 : PL/SQL Variables and Constants
Chap 3 : PL/SQL Records
Chap 4 : PL/SQL IF ELSE statement / Conditional statements
Chap 5 : PL/SQL FOR and WHILE LOOP / Iterative Statements
Chap 6 : Cursors in PL/SQL
Chap 7 : PL/SQL Procedures
Chap 8 : PL/SQL Functions
Chap 9 : Parameters-Procedure and Function in PL/SQL
Chap 10 : What are PL/SQL Triggers
Chap 11 : Exception Handling in PL/SQL

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;