iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - PL/SQL 

 

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

 

Lesson 15

￿A person reveals his character by nothing so clearly as the joke he resents.￿ Georg Christoph Lichtenberg (1742 - 1799)

 

Read first then play the video:

   PLS-VIDEO -Creating PL/SQL Package

  

Section 4: Package

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.

 

￿Without friends no one would choose to live, though he had all other goods.￿ Aristotle (384 BC - 322 BC), Nichomachean Ethics

 

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?