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)
Area := Len * Wid;
SQL> variable area1 number;
SQL> execute get_area (10, 50, :area1);
PL/SQL procedure successfully completed.
SQL> print area1
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
DELETE FROM employees
WHERE employees.employee_id = remove_emp.employee_id;
tot_emps := tot_emps - 1;
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]