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.

  1. Specify the same package name here that was used in your package specification command
  2. Each public sub-program is defined here. Parameters must match the package specifications. Separate the sub-programs using the END statement followed by a semicolon. In addition to the public sub-programs, additional private sub-programs, cursors, variables, and exceptions can be defined here.
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
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