Advanced sql and pl sql topics
This presentation is the property of its rightful owner.
Sponsored Links
1 / 52

Advanced SQL And PL/SQL Topics PowerPoint PPT Presentation


  • 57 Views
  • Uploaded on
  • Presentation posted in: General

Advanced SQL And PL/SQL Topics. Chapter 9. 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.

Download Presentation

Advanced SQL And PL/SQL Topics

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Advanced sql and pl sql topics

Advanced SQL And PL/SQL Topics

Chapter 9

A Guide to Oracle9i


Lesson a objectives

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


Database indexes

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


Creating an index

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


Composite index

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


Viewing index information

Viewing Index Information

  • Use data dictionary view USER_INDEXES

A Guide to Oracle9i


Dropping an index

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


Use an index when

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


Do not use an index when

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


Overview of pl sql stored program units

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


Overview of pl sql stored program units1

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


Types of program units

Types of Program Units

A Guide to Oracle9i


Creating stored program units

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


Parameter declarations list

Parameter Declarations List

  • Defines the parameters and declares their associated data types

  • Enclosed in parentheses

  • Separated by commas

A Guide to Oracle9i


Parameter declarations list1

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


Creating a stored procedure in sql plus

Creating a Stored Procedure in SQL*Plus

A Guide to Oracle9i


Debugging stored program units in sql plus

Debugging Stored Program Units in SQL*Plus

A Guide to Oracle9i


Debugging stored program units in sql plus1

Debugging Stored Program Units in SQL*Plus

A Guide to Oracle9i


Calling a stored procedure

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


Creating a stored program unit function

Creating a Stored Program Unit Function

A Guide to Oracle9i


Creating a stored program unit function1

Creating a Stored Program Unit Function

  • Last command in function must be RETURN

A Guide to Oracle9i


Calling a function

Calling a Function

  • variable_name := function_name(parameter1, parameter2, ...);

A Guide to Oracle9i


Using forms builder to create stored procedures and functions

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


Using forms builder to create stored procedures and functions1

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


Lesson b objectives

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


Calling stored program units from other stored program units

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


Pl sql libraries

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


Creating a pl sql library

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


Packages

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


Package specification

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


Package specification1

Package Specification

A Guide to Oracle9i


Package header

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


Procedure and function declarations

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


Package body

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


Package body1

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


Creating a package header in sql plus

Creating a Package Header in SQL*Plus

A Guide to Oracle9i


Creating a package body in sql plus

Creating a Package Body in SQL*Plus

A Guide to Oracle9i


Using package objects

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


Creating a package in forms builder

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


Database triggers

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


Trigger properties

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


Trigger properties1

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 triggersfire once, either before or after the SQL triggering statement executes.

    • Row-level triggersfire 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


Creating database triggers

Creating Database Triggers

A Guide to Oracle9i


Database trigger header

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


Database trigger body

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


Trigger use audit trail

Trigger Use – Audit Trail

A Guide to Oracle9i


Creating audit trigger in sql plus

Creating Audit Trigger in SQL*Plus

A Guide to Oracle9i


Creating a database trigger in forms builder

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


Disabling and dropping triggers

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


Viewing trigger information

Viewing Trigger Information

A Guide to Oracle9i


Summary

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


Summary1

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

A Guide to Oracle9i


  • Login