Oracle Create Package

Oracle Create Package

To create a package uses the CREATE PACKAGE statement.
A package is an encapsulated collection of database objects like procedures, functions, variables, types, exceptions.
The objects are declared in the package specification.
The package body defines the queries for the cursors and the code for the subprograms.

Create Package syntax

CREATE OR REPLACE PACKAGE package_name AS
	-- declare functions
	-- declare procedures
END package_name; 
/

CREATE OR REPLACE PACKAGE BODY package_name AS
    -- functions body     
	-- procedures body  
END package_name; 
/

Create Package example

CREATE OR REPLACE PACKAGE pkg_customer AS
	function show_name(p_id number) return varchar2;
	procedure del_customer (p_id number);
END pkg_customer; 
/

Create Package Body example

CREATE OR REPLACE PACKAGE BODY pkg_customer AS
	function show_name(p_id number)
		return varchar2 
	is
		v_name varchar2(255);
	begin
		select name into v_name from customers where id=p_id;
		return v_name;
	end;
	
	procedure del_customer(p_id number)
	as
		v_name varchar2(255);
	begin
	  delete from customers where id=p_id;
	end;	
END pkg_customer; 
/