everythingOracle.bizhat.com

 

'Everything you wanted
to know about Oracle'

Training References Syntax Tell A Friend Contact Us

 

 

PL/SQL

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22
<< Previous

Chapter # 15

Next >>


 

Create a Packages

 

Hands-On introduction

In this Hands-On, you create a package by copy and pasting an existing PL/SQL stored procedure. Then save it in the local library or the database server.

A package is a database construct that allows users to collect many program units into one database object. They consist of two components: a package specification and a package body.

 

Create a Package Specification

Introduction

A package should have a PL/SQL package specification and a PL/SQL package body. A �PL/SQL package specification� contains all your PL/SQL functions header, procedures header, type, variables, etc. A �PL/SQL package body� contains a complete PL/SQL stored procedures or functions. All the declared PL/SQL functions, procedures, variables, etc in a package specification are called public procedures and functions. They can be accessible to the users who have privilege to execute them. In the PL/SQL package specification, all the functions and procedures must have a PL/SQL procedure in its PL/SQL package body. It is not necessary that all the PL/SQL procedures in a PL/SQL package body have a specification entry in its PL/SQL package specification. Those PL/SQL procedures that have not have any specification entry in the PL/SQL package specification called private PL/SQL procedures.

Select "Program Units" and then click on the �Create� icon to create a package that contains all of department's functions and procedures.

 

Name the package (package department) and select package specification. Then click "OK"

Write or �copy and paste� all functions� and procedures� header for public use.

Open the add_dept procedure and copy it header specification into the package specification. Do not forget to add the ";"

 

Repeat this process for any other stored procedures, if needed.

(Procedure Builder)

PACKAGE pkg_dept

IS

-- No variables

-- This is the add_dept specification�

PROCEDURE add_dept

(p_dept_rec IN dept%ROWTYPE,

p_status OUT VARCHAR2);

-- This is the remove_dept specification�

PROCEDURE remove_dept

(p_deptno IN dept.depno%TYPE,

p_status OUT VARCHAR2);

-- Add more and more�

END pkg_dept;

/

Add more headers if needed. These two programs are going to be enough to make the point.

 

Compile a package specification

Compile the package specification. No strike means the compilation was successful.

Create a package body

Create a package body. Name the package body the same as its package specification name. Select �Package Body.� Then click �OK.�

Copy and paste the entire stored procedures into the package body. Repeat the copy process for other stored procedures if needed.

(Procedure Builder)

PACKAGE BODY pkg_dept

IS

-- Add department procedure�

PROCEDURE add_dept

(p_deptno IN dept.deptno%TYPE,

p_dname IN dept.dname%TYPE,

p_loc IN dept.loc%TYPE,

p_status OUT VARCHAR2)

IS

-- No variable

BEGIN

/* This program add dept. record. */

INSERT INTO dept

VALUES (p_deptno, p_dname, p_loc);

--- Save record.

COMMIT;

-- Added successfully if the get to this line.

p_status := �OK�;

EXCEPTION

-- Check for an Unique or Primary Key

WHEN dup_val_on_index THEN

p_status := �DUPLICATE RECORD�;

-- Check for invalid input data

WHEN invalid_number THEN

p_status := �INVALID INPUT DATA�;

-- Check for any other problems

WHEN others THEN

p_status := �CHECK THIS WE HAVE UNKNOWN PROBLEM.�;

END add_dept;

-- Remove department procedure�

PROCEDURE remove_dept

(p_deptno IN dept.deptno%TYPE,

p_status OUT VARCHAR2)

IS

-- Delete a record

DELETE FROM dept

WHERE deptno = p_deptno;

-- Save the transaction.

COMMIT;

-- Check the status.

p_status := �OK�;

EXCEPTION

WHEN no_data_found THEN

p_status := �NO DATA FOUND.�;

WHEN others THEN

p_status := �Other Problems.�;

END remove_dept;

-- And more internal procedures.

END pkg_dept;

/

Compile a package body

Compile the package body. No strike means: successfully compiled.

Make a syntax error and compile again. Read the error messages. Then correct the error and compile it again.

 

Then close the window.

Run and test a procedure or function in a package

Query the department table.

PL/SQL> SELECT * FROM dept;

 

Write a PL/SQL procedure block to use the remove procedure to delete the department number 40.

PL/SQL> DECLARE

v_status VARCHAR2(40);

BEGIN

pkg_dept.remove_dept(40, v_status);

TEXT_IO.PUT_LINE(v_status);

END;

Then output the status parameter to see that the transaction was successfully deleted.

�OK� means: it was successfully deleted.

Query the department table again.

PL/SQL> SELECT * FROM dept;

Record was deleted.

Save a package specification and body into the database server

Store the package into the database server.

First store the "package specification" then store the "package body" unit.

 

Questions:

Q: What is the PL/SQL package?

Q: What are the components of a PL/SQL package?

Q: What is a package body in the PL/SQL language?

Q: What is a package specification in the PL/SQL language?

Q: Where do you save the package body and its package specification?

Q: Can you store a PL/SQL package in a client environment?

Q: How do you create a package specification and body?

Q: What are the dependencies between a package body and its package specification?

Q: Write a PL/SQL package to have all your created PL/SQL functions and procedures?

Q: What is a public PL/SQL procedure or function in a PL/SQL package?

Q: What is a private PL/SQL procedure or function in a PL/SQL package?

Q: What are the differences between a public or private PL/SQL procedure?

Q: How do you run a PL/SQL procedure or function in a PL/SQL package?

     Reviews and Templates for FrontPage
     

Copyright � everythingOracle.bizhat.com 2006 All Rights Reserved.