PL/SQL 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)

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
v_name := get_lastname(33);
end;

The output:

The name is: Jones
The name is: Steinberg

Drop procedure syntax:

DROP PROCEDURE procedure_name; 

Drop procedure example:

DROP PROCEDURE get_lastname;