advanced sql and pl/sql topics
Post on 07-Jan-2016
46 Views
Preview:
DESCRIPTION
TRANSCRIPT
A Guide to Oracle9i 1
Advanced SQL And PL/SQL Topics
Chapter 9
A Guide to Oracle9i 2
Lesson A Objectives
• Learn how to create and use indexes• Become familiar with PL/SQL stored program units• Learn how to create server-side stored program units
in SQL*Plus• Learn how to use Forms Builder to create stored
program units
A Guide to Oracle9i 3
Database Indexes
• Similar to an index in a book• Table with list of sorted data values and
corresponding physical location• Used to speed searches• Uses ROWID column to represent physical location• Primary key indexed automatically• Unlimited number allowed, but more indexes means
more processing time for action queries (insert, update, delete)
A Guide to Oracle9i 4
Creating an Index
• Create index after table data is loaded• CREATE INDEX index_name ON tablename
(index_fieldname);• Convention for naming index: tablename_fieldname.
A Guide to Oracle9i 5
Composite Index
• Contains multiple (up to 16) sorted columns• Used for queries with multiple search conditions• CREATE INDEX index_name ON
tablename(index_fieldname1, index_fieldname2, …);
A Guide to Oracle9i 6
Viewing Index Information
• Use data dictionary view USER_INDEXES
A Guide to Oracle9i 7
Dropping an Index
• If an index is no longer needed or does not improve performance, delete it
• DROP INDEX index_name;
A Guide to Oracle9i 8
Use an Index When
• Table contains a large number of records (a rule of thumb is that a large table contains over 100,000 records)
• The field contains a wide range of values• The field contains a large number of NULL values• Application queries frequently use the field in a
search condition or join condition• Most queries retrieve less than 2% to 4% of the table
rows
A Guide to Oracle9i 9
Do Not Use an Index When
• The table does not contain a large number of records• Applications do not use the proposed index field in a
query search condition• Most queries retrieve more than 2% to 4% of the
table records• Applications frequently insert or modify table data
A Guide to Oracle9i 10
Overview of PL/SQL Stored Program Units
• Self-contained group of program statements that can be used within a larger program.
• Easier to conceptualize, design, and debug • Save valuable programming time because
you can reuse them in multiple database applications
• Other PL/SQL programs can reference them
A Guide to Oracle9i 11
Overview of PL/SQL Stored Program Units
• Server-side program units — stored in the database as database objects and execute on the database server
• Client-side program units — stored in the file system of the client workstation and execute on the client workstation
A Guide to Oracle9i 12
Types of Program Units
A Guide to Oracle9i 13
Creating Stored Program Units• Procedure: a program unit that can receive multiple input parameters
and return multiple output values or return no output values• Function: a program unit that can receive multiple input parameters,
and always returns a single output value.
A Guide to Oracle9i 14
Parameter Declarations List
• Defines the parameters and declares their associated data types
• Enclosed in parentheses• Separated by commas
A Guide to Oracle9i 15
Parameter Declarations List
• Parameter mode describes how the program unit can change the parameter value:– IN - specifies a parameter that is passed to the program unit
as a read-only value that the program unit cannot change. – OUT - specifies a parameter that is a write-only value that
can appear only on the left side of an assignment statement in the program unit
– IN OUT - specifies a parameter that is passed to the program unit, and whose value can also be changed within the program unit
A Guide to Oracle9i 16
Creating a Stored Procedure in SQL*Plus
A Guide to Oracle9i 17
Debugging Stored Program Units in SQL*Plus
A Guide to Oracle9i 18
Debugging Stored Program Units in SQL*Plus
A Guide to Oracle9i 19
Calling a Stored Procedure
• From SQL*Plus command line:– EXECUTE procedure_name (parameter1_value,
parameter2_value, ...);
• From PL/SQL program:– Omit execute command
• Passing parameters (see Figure 9-13)
A Guide to Oracle9i 20
Creating a Stored Program Unit Function
A Guide to Oracle9i 21
Creating a Stored Program Unit Function
• Last command in function must be RETURN
A Guide to Oracle9i 22
Calling a Function
• variable_name := function_name(parameter1, parameter2, ...);
A Guide to Oracle9i 23
Using Forms Builder to Create Stored Procedures and Functions
• Create and test the program unit within a form• Save it as a stored program unit in your database
schema• Provides an enhanced development and debugging
environment:– Color-coded editor for entering and debugging program unit
commands– Displays compile error messages immediately– Use the Forms Debugger to step through program unit
commands and view how variable values change
A Guide to Oracle9i 24
Using Forms Builder to Create Stored Procedures and Functions
• Create the procedure or function as a form program unit
• Test and debug the form program unit by calling it from commands within a form trigger
• Save the form program unit as a stored program unit in the database
A Guide to Oracle9i 25
Lesson B Objectives
• Learn how to call stored procedures from other stored procedures and pass parameter values
• Create libraries• Create packages• Create database triggers
A Guide to Oracle9i 26
Calling Stored Program Units from Other Stored Program Units
• Decompose applications into logical units of work and then write individual program units for each logical unit
• Code is in a single location• Developers do not need to rewrite program units that
already exist• References procedures must be declared first
A Guide to Oracle9i 27
PL/SQL Libraries
• Operating system file that contains code for multiple related procedures and functions
• Attach a PL/SQL library to a form or report– Triggers within the form or report reference library’s procedures and
functions
• Store a PL/SQL library in the file system of the client workstation
• .pll extension - stands for “PL/SQL Library”
• Compile the library into a library executable file - .plx extension - stands for “PL/SQL Library Executable”
• Library places the commands for multiple related program units in a single location that developers can access and use
A Guide to Oracle9i 28
Creating a PL/SQL Library
• Use Forms Builder to create libraries• Add form program units and stored program units to the library.
A Guide to Oracle9i 29
Packages
• Another way to make PL/SQL program units available to multiple applications
• A code library that contains related program units and variables
• Stored in the database and executes on the database server
• Have more functionality than PL/SQL libraries:– Can create variables in packages
– Definitions for explicit cursors
– More convenient to use than PL/SQL libraries
– Available without explicitly attaching them to a form or report
A Guide to Oracle9i 30
Package Specification
• Also called package header• Declares package objects, including
variables, cursors, procedures, and functions,• Use to declare public variables:
– Remain in memory after the programs that declare and reference them terminate
– Declared in the DECLARE section of a package– Referenced same as private variables
A Guide to Oracle9i 31
Package Specification
A Guide to Oracle9i 32
Package Header
• Package_name identifies the package– Must adhere to the Oracle Naming Standard
• Declare the package objects in any order• Package can consist of just variable
declarations, or it can consist of just procedure or function declarations
A Guide to Oracle9i 33
Procedure and Function Declarations
• Declare a procedure:PROCEDURE procedure_name
(parameter1 parameter1_data_type,
parameter2 parameter2_data_type, ...);• Declare a function:
FUNCTION function_name
(parameter1 parameter1_data_type,
parameter2 parameter2_data_type, ...)
RETURN return_datatype;
A Guide to Oracle9i 34
Package Body
• Contains the implementation of declared procedures and functions• Specification comes before body• Optional: sometimes a package contains only variable or cursor
declarations, and no procedure or function declarations• See Figure 9-35 for general syntax
A Guide to Oracle9i 35
Package Body
• Package_name in the package body must be the same as package_name in the package specification
• Variables that you declare at the beginning of the package body are private to the package
• Each package program unit has its own declaration section and BEGIN and END statements
• Each program unit declared in the package body must have a matching program unit forward declaration in the package specification, with an identical parameter list
A Guide to Oracle9i 36
Creating a Package Header in SQL*Plus
A Guide to Oracle9i 37
Creating a Package Body in SQL*Plus
A Guide to Oracle9i 38
Using Package Objects
• Must preface the item with the package name:– package_name.item_name.
• To grant other users the privilege to execute a package:– GRANT EXECUTE ON package_name TO
username;
A Guide to Oracle9i 39
Creating a Package in Forms Builder
• Create a program unit of type Package Spec• Type the package specification in the PL/SQL editor• Create a program unit of type Package Body• Type package body in the PL/SQL editor• Compile package body and test using a form trigger• Save the package in the database for future use
A Guide to Oracle9i 40
Database Triggers
• Program units that execute in response to the database events of inserting, updating, or deleting a record
• Different from form triggers• Useful for maintaining integrity constraints and audit
information• Cannot accept input parameters• Executes only when its triggering event occurs
A Guide to Oracle9i 41
Trigger Properties
• Trigger timing:– Defines whether a trigger fires before or after the
SQL statement executes– Can have the values BEFORE or AFTER
• Trigger statement:– Defines the type of SQL statement that causes a
trigger to fire– Can be INSERT, UPDATE, or DELETE
A Guide to Oracle9i 42
Trigger Properties
• Trigger level:– Defines whether a trigger fires once for each triggering
statement or once for each row affected by the triggering statement
– Can have the values ROW or STATEMENT– Statement-level triggers fire once, either before or after the
SQL triggering statement executes.– Row-level triggers fire once for each row affected by the
triggering statement
• Use :OLD.fieldname to reference previous value• Use :NEW.fieldname to reference changed value
A Guide to Oracle9i 43
Creating Database Triggers
A Guide to Oracle9i 44
Database Trigger Header
• Trigger_name must follow Oracle Naming Standard• Join statement types using the OR operator to fire for
multiple statement types (INSERT OR UPDATE)• WHEN (condition) clause:
– Trigger will fire only for rows that satisfy a specific search condition
– WHEN OLD.grade IS NOT NULL;
A Guide to Oracle9i 45
Database Trigger Body
• Contains the commands that execute when the trigger fires
• PL/SQL code block that contains the usual declaration, body, and exception sections
• Cannot contain transaction control statements• Reference the NEW and OLD field values only in a
row-level trigger
A Guide to Oracle9i 46
Trigger Use – Audit Trail
A Guide to Oracle9i 47
Creating Audit Trigger in SQL*Plus
A Guide to Oracle9i 48
Creating a Database Triggerin Forms Builder
• Use the Database Trigger Dialog Box to specify trigger properties
• Type trigger body into Trigger Body entry field
A Guide to Oracle9i 49
Disabling and Dropping Triggers
• To remove a trigger:– DROP TRIGGER trigger_name;
• To disable/enable a trigger:– ALTER TRIGGER trigger_name [ENABLE |
DISABLE];
A Guide to Oracle9i 50
Viewing Trigger Information
A Guide to Oracle9i 51
Summary
• Database indexes store an ordered list of field values with corresponding ROWID
• Indexes are used to speed query performance• Stored program units are named PL/SQL blocks that
are saved• Procedures accept parameters and return 0,1, or
many values• Functions accept parameters and return exactly one
value
A Guide to Oracle9i 52
Summary
• PL/SQL Library is a client-side file containing procedures and functions
• PL/SQL Package is a collection of public variables, cursors, procedures and functions stored in the DBMS
• Database triggers are PL/SQL blocks that are run in response to table changes
• Database triggers are used to enforce integrity constraints and track changes
• Forms Builder may be used as an IDE to develop functions, procedures, libraries, packages and triggers
top related