PL/SQL Packages

The package consists of grouped objects as procedures, functions, variables, exceptions, cursors.

Each package consists of two parts:
- Specification of the package - Here we can declare public types, variables, constants and exceptions.
- The body of the package defines its own subroutine.


Exemple of PL/SQL Packages:


CREATE OR REPLACE PACKAGE users AS
   FUNCTION add_user( p_user_name VARCHAR2, p_user_email VARCHAR2)
   RETURN VARCHAR2;
END users;

CREATE OR REPLACE PACKAGE BODY users AS
   global_exception exception;
   FUNCTION add_user( p_user_name VARCHAR2, p_user_email VARCHAR2)
   RETURN VARCHAR2 IS
     v_output varchar2(4000):='Inserted';
   BEGIN
     IF p_user_name IS NULL THEN
       v_output:='p_user_name IS NULL';
       RAISE global_exception;
     END IF;
     INSERT INTO users(user_name, user_email)
     VALUES (p_user_name, p_user_email);
     RETURN v_output;
   EXCEPTION
     WHEN global_exception THEN
       RETURN v_output;
   END;
END users;