RelationalDBDesignRelationalDBDesign





Creating Packages   «Prev  Next»
Lesson 5Making the package body
ObjectiveBuild the package body

Making the Package Body in Oracle

The package body is created separately from the package specification, although the two objects are closely associated. When the package body is generated, each procedure and function listed in the package specification must be accurately represented. A procedure defined in the package specification must have the same name and the same parameter list in the package body.
Look at the following MouseOver for a description of the package body syntax. 

CREATE [OR REPLACE] PACKAGE BODY packagename {IS | AS} 
{PROCEDURE | FUNCTION} subprogram_name [(parameter_specs)] IS 
BEGIN
  ... PL/SQL commands here ...
END subprogram_name;
{PROCEDURE | FUNCTION} subprogram_name [(parameter_specs)] IS 
BEGIN 
  ...PL/SQL commands here ...
END subprogram_name;
  ...
END [packagename];

Example Package Body Syntax
An example of a package body is shown in the graphic below.

Package body example
Package body example 
CREATE OR REPLACE PACKAGE BODY GET_MONTHEND_DATA IS
PROCEDURE CALC_PROFIT
  (I_YEAR IN NUMBER, I_MONTH IN NUMBER, 0_PROFIT OUT NUMBER) IS
BEGIN
  SELECT SUM(TOTAL_SALE_AMOUNT) INTO O_PROFIT FROM
	CUSTOMER_SALE
	WHERE TO_CHAR(SALES_DATE, 'MMYYYY')= LPAD(I_MONTH || I_YEAR, 6 , 0);
END CALC_PROFIT
The next lesson shows you how to execute a procedure contained within a package.

Making Package Body - Exercise

Click the exercise link below to create your own package.
Making Package Body - Exercise

Create Package - Exercise

Click the link below to do the same exercise, but run the commands at home on your own database.
Create Package - Exercise