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. [
Source]