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 code.

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;