PL/SQL Procedures

PL/SQL Procedures

There are two types of PL/SQL blocks: named block and an anonymous block.
There are two types of named blocks: Functions and Procedures

A stored procedure is a PL/SQL block that accepts zero or more parameters as input (IN), output (OUT), or both (INOUT). PL/SQL Procedures do not return a value; instead the INOUT parameter or OUT parameter may be used to pass a value from the procedure. Procedures cannot be used in SQL statements; they are invoked using the EXECUTE command or called inside a PL/SQL block.

Syntax of PL/SQL Procedure:

PROCEDURE name [(parameter[, parameter, …])] IS
[local declarations]
BEGIN
executable statements
[EXCEPTION
exception handlers]
END [name];

Example 1 of PL/SQL Procedure

create or replace procedure get_area
(Len in number, Wid in number, Area out number)
as
begin
Area := Len * Wid;
end;

SQL> variable area1 number;
SQL> execute get_area (10, 50, :area1);
PL/SQL procedure successfully completed.
SQL> print area1
AREA1
---------
500

Example 2 of PL/SQL Procedure

The following statement creates the procedure remove_emp in the schema hr. The PL/SQL is shown in italics:

CREATE PROCEDURE remove_emp (employee_id NUMBER) AS
tot_emps NUMBER;
BEGIN
DELETE FROM employees
WHERE employees.employee_id = remove_emp.employee_id;
tot_emps := tot_emps - 1;
END;
/


The remove_emp procedure removes a specified employee. When you call the procedure, you must specify the employee_id of the employee to be removed.

The procedure uses a DELETE statement to remove from the employees table the row of employee_id. [Via]