PL/SQL Procedure

A procedure is a named PL/SQL block which is stored in the database and can be invoked by name.
An Oracle PL/SQL procedure has a header and a body.

The header consists of the keyword PROCEDURE, followed by the procedure name, followed by a list of parameters in parentheses. The body of the procedure consists of the PL/SQL code which implements the procedure.

Create procedure syntax:

CREATE [OR REPLACE] PROCEDURE procedure_name [ (parameter [,parameter]) ]

IS
    -- Declarative part (optional)

BEGIN -- Executable part (required)

-- SQL statements
-- PL/SQL statements

EXCEPTION -- Exception-handling part (optional)

-- Exception handlers for exceptions (errors) raised in executable part

END [procedure_name]; -- End of executable part (required)

Parameters are variables which are passed in to a procedure. They are used to pass data in to the procedure so that it can be used by the code inside the procedure. Parameters are declared in the procedure header, after the procedure name.

When calling a stored procedure with parameters, you need to specify whether each parameter is an IN, OUT, or IN OUT parameters.

A procedure can be executed by using the EXECUTE keyword, followed by the procedure name and a list of parameters to pass in to the procedure.

The procedure will then execute the code inside its body.

Create procedure example:

create or replace procedure get_lastname (p_dep_id number)
 is
  v_error varchar2(250);
begin
  for rec in (select * from employee where departmentid = p_dep_id)
  loop
   DBMS_OUTPUT.PUT_LINE('The name is: '||rec.lastname);
  end loop;
exception
  when others then
   v_error := 'Database error!';
   DBMS_OUTPUT.PUT_LINE(v_error);
end;

Execute procedure example:

begin
get_lastname(33);
end;

The output:

The name is: Jones
The name is: Steinberg

If the procedure has parameters, then the values of those parameters will be used by the procedure. Otherwise, the procedure will use the default values of the parameters.

The procedure can be replaced by using the REPLACE keyword. This will overwrite the existing procedure with the new procedure.

Drop procedure syntax:

DROP PROCEDURE procedure_name; 

Drop procedure example:

DROP PROCEDURE get_lastname;