PLSQL Packages

1
PL/SQL Packages Advanced Data Structures

• CIS 410

2
Program Unit Dependencies

• Object dependencies
• Program units are dependent on the database
  objects they reference (tables, views, sequences,
  )
• Procedure dependencies
• Program units are dependent on other program
  units they call

3
Direct and Indirect Dependencies

• Direct dependency
• Object or program is directly called or
  referenced
• Indirect dependency
• Object or program is called or referenced by a
  subprogram

4
Direct and Indirect Dependencies
CUST_ORDER
Direct Dependency
CREATE_ NEW_ ORDER
ORDER_ID_ SEQUENCE
Indirect Dependency
CREATE_ NEW_ ORDER_LINE
ORDER_ID_ SEQUENCE
ORDER_LINE
5
Invalidation

• If an object or program on which a program has a
  dependency is changed, the program is
  invalidated, and must be recompiled

6
Packages

• Server-side code library
• Can contain
• Global variable declarations
• Cursors
• Procedures
• Functions

7
Differences Between Packages and Libraries

• Libraries have to be explicitly attached to
  applications, while packages are always available
  to be called by applications
• Libraries always execute on client
• Packages always execute on server

8
Package Components

• Specification
• Used to declare all public variables, cursors,
  procedures, functions
• Body
• Contains underlying code for procedures and
  functions
• Rationale
• Specification is visible to users, gives details
  on how to use
• Body is not visible, users dont care about
  details

9
Creating a Package Specification in SQLPlus
CREATE OR REPLACE PACKAGE package_name IS
--public variables variable_name datatype
--program units PROCEDURE procedure_name
(parameter_list) FUNCTION function_name
(parameter_list) END
10
Creating a Package Specification in SQLPlus
11
Creating a Package Body in SQLPlus
CREATE OR REPLACE PACKAGE BODY package_name IS
private variable declarations program unit
blocks END
12
Creating a Package Body in SQLPlus
13
Calling a Program Unit That Is In a Package

• Preface the program unit name with the package
  name
• PACKAGE_NAME.program_unit_name(parameter_list)
• Example
• DBMS_OUTPUT.PUT_LINE(Hello World)

14
Overloading Program Units in Packages

• Overloading
• Multiple program units have the same name, but
  accept different input parameters
• Allows user to use the same command to perform an
  action but pass different parameter values

15
Package Specification With Overloaded Procedures
16
PL/SQL Tables

• Data structure that contains multiple data items
  that are the same data type
• Each table item has a key and a value
• Key values do not need to be sequential
• Used to create a lookup table that is stored in
  memory to improve processing speed

17
Simple table example
declare type meal_table is table of varchar2(20)
index by binary_integer v_meal_table
meal_table v_loop_count integer 1
v_text_line varchar2 (50) begin
v_meal_table(1) 'Breakfast' v_meal_table(2)
'Lunch' v_meal_table(3) 'Dinner'
v_meal_table(4) 'Snack' for v_loop_count in 1
.. 4 loop dbms_output.put_line(v_meal_table(v_lo
op_count)) end loop end
OUTPUT Breakfast Lunch Dinner Snack
18
PL/SQL Table of Records

• PL/SQL table that can store multiple values that
  are referenced by a key
• Usually used to store database records that need
  to be processed by a PL/SQL program
• Improves performance by limiting number of
  database retrievals

19
Table of Records Example
OUTPUT 101 FLG 102 PHX 103 MPS 104 PHX 15 PHX 17
PHX 31 PHX 33 PHX 35 PHX 329 LAX 40 PHX 694
LAX 434 LAX 400 LAX 600 PHX 604 PHX 606 PHX 60
PHX 202 SFO 691 SFO 518 SFO 1260 SFO

• declare
• type
• flight_table is table of FLIGHTrowtype
• index by binary_integer
• v_flt_table flight_table
• v_loop_count integer 1
• v_text_line varchar2 (50)
• cursor v_flight_cursor is
• select from flight
• v_flight_rec_no integer 1
• begin
• open v_flight_cursor
• loop
• fetch v_flight_cursor into
  v_flt_table(v_flight_rec_no)
• exit when v_flight_cursornotfound
• v_flight_rec_no v_flight_rec_no 1
• end loop
• for v_loop_count in 1 .. v_flight_rec_no - 1
• loop