BN.com Gift Guide

Oracle PL/SQL by Example (Prentice Hall PTR Oracle Ser.) / Edition 4

Paperback (Print)
Buy Used
Buy Used from BN.com
$41.46
(Save 40%)
Item is in good condition but packaging may have signs of shelf wear/aging or torn packaging.
Condition: Used – Good details
Used and New from Other Sellers
Used and New from Other Sellers
from $35.00
Usually ships in 1-2 business days
(Save 49%)
Other sellers (Paperback)
  • All (8) from $35.00   
  • New (6) from $54.43   
  • Used (2) from $35.00   

Overview

This integrated learning solution teaches all the Oracle PL/SQL skills you need, hands-on, through real-world labs, extensive examples, exercises, and projects! Completely updated for Oracle 11g, Oracle PL/SQL by Example , Fourth Edition covers all the fundamentals, from PL/SQL syntax and program control through packages and Oracle 11g’s significantly improved triggers.

One step at a time, you’ll walk through every key task, discovering the most important PL/SQL programming techniques on your own. Building on your hands-on learning, the authors share solutions that offer deeper insights and proven best practices. End-of-chapter projects bring together all the techniques you’ve learned, strengthening your understanding through real-world practice.

This book’s approach fully reflects the authors’ award-winning experience teaching PL/SQL programming to professionals at Columbia University. New database developers and DBAs can use its step-by-step instructions to get productive fast; experienced PL/SQL programmers can use this book as a practical solutions reference. Coverage includes

• Mastering basic PL/SQL concepts and general programming language fundamentals, and understanding SQL’s role in

PL/SQL

• Using conditional and iterative program control techniques, including the new CONTINUE and CONTINUE WHEN statements

• Efficiently handling errors and exceptions

• Working with cursors and triggers, including Oracle 11g’s powerful new compound triggers

• Using stored procedures, functions, and packages to write modular code that other programs can execute

• Working with collections, object-relational features, native dynamic SQL, bulk SQL, and other advanced PL/SQL capabilities

• Handy reference appendices: PL/SQL formatting guide, sample database schema, ANSI SQL standards reference, and

more

Read More Show Less

Product Details

  • ISBN-13: 9780137144228
  • Publisher: Prentice Hall
  • Publication date: 9/4/2008
  • Series: Prentice Hall Professional Oracle Series
  • Edition description: New Edition
  • Edition number: 4
  • Pages: 740
  • Sales rank: 400,430
  • Product dimensions: 7.00 (w) x 9.00 (h) x 1.60 (d)

Meet the Author

Benjamin Rosenzweig is a software development manager at Misys Treasury & Capital Markets, where he has worked since 2002. Prior to that he was a principal consultant for more than three years at Oracle Corporation in the Custom Development Department. His computer experience ranges from creating an electronic Tibetan—English dictionary in Kathmandu, Nepal, to supporting presentation centers at Goldman Sachs and managing a trading system at TIAA-CREF. Rosenzweig has been an instructor at the Columbia University Computer Technology and Application program in New York City since 1998. In 2002 he was awarded the Outstanding Teaching Award from the chair and director of the CTA program. He holds a B.A. from Reed College and a certificate in database development and design from Columbia University. His previous books with Prentice Hall are Oracle Forms Developer: The Complete Video Course (ISBN: 0-13-032124-9) and Oracle Web Application Programming for PL/SQL Developers (ISBN: 0-13-047731-1).

Elena Silvestrova Rakhimov has more than 15 years of experience in database development in a wide spectrum of enterprise and business environments, ranging from nonprofit organizations to Wall Street. She currently works at Alea Software, where she serves as Senior Developer and Team Lead. Her determination to stay hands-on notwithstanding, Rakhimov has managed to excel in the academic arena, having taught relational database programming at Columbia University’s highly esteemed Computer Technology and Applications program. She was educated in database analysis and design at Columbia University and in applied mathematics at Baku State University in Azerbaijan. She currently resides in Vancouver, Canada.

Read More Show Less

Read an Excerpt

Introduction Introduction PL/SQL New Features in Oracle 11g

Oracle 11g has introduced a number of new features and improvements for PL/SQL. This introduction briefly describes features not covered in this book and points you to specific chapters for features that are within scope of this book. The list of features described here is also available in the “What’s New in PL/SQL?” section of the PL/SQL Language Reference manual offered as part of Oracle help available online.

The new PL/SQL features and enhancements are as follows:

  • Enhancements to regular expression built-in SQL functions
  • SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE datatypes
  • CONTINUE statement
  • Sequences in PL/SQL expressions
  • Dynamic SQL enhancements
  • Named and mixed notation in PL/SQL subprogram invocations
  • Cross-session PL/SQL function result cache
  • More control over triggers
  • Compound triggers
  • Database resident connection pool
  • Automatic subprogram inlining
  • PL/Scope
  • PL/SQL hierarchical profiler
  • PL/SQL native compiler generates native code directly
Enhancements to Regular Expression Built-In SQL Functions

In this release Oracle has introduced a new regular expression built-in function, REGEXP_COUNT. It returns the number of times a specified search pattern appears in a source string.

For Example SELECT REGEXP_COUNT ('Oracle PL/SQL By Example Updated for Oracle 11g', 'ora', 1, 'i') FROM dual; REGEXP_COUNT('ORACLEPL/SQLBYEXAMPLEUPDATEDFORORACLE11G','ORA',1,'I') —————————————————————————————————— 2

The REGEXP_COUNT function returns how many times the search pattern 'ora' appears in the source string 'Oracle PL/SQL...' 1 indicates the position of the source string where the search begins, and 'i' indicates case-insensitive matching.

The existing regular expression built-in functions, REGEXP_INSTR and REGEXP_SUBSTR, have a new parameter called SUBEXPR. This parameter represents a subexpression in a search pattern. Essentially it is a portion of a search pattern enclosed in parentheses that restricts pattern matching, as illustrated in the following example.

For Example SELECT REGEXP_INSTR ('Oracle PL/SQL By Example Updated for Oracle 11g', '((ora)(cle))', 1, 2, 0, 'i') FROM dual; REGEXP_INSTR('ORACLEPL/SQLBYEXAMPLEUPDATEDFORORACLE11G',...) —————————————————————————————— 38

The REGEXP_INSTR function returns the position of the first character in the source string 'Oracle PL/SQL...' corresponding to the second occurrence of the first subexpression 'ora' in the seach pattern (ora)(cle). 1 indicates the position of the source string where the search begins, 2 indicates the occurrence of the subexpression in the source string, 0 indicates that the position returned corresponds to the position of the first character where the match occurs, and 'i' indicates case-insensitive matching and REGEXP_SUBSTR.

SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE Datatypes

These datatypes are predefined subtypes of the PLS_INTEGER, BINARY_FLOAT, and BINARY_DOUBLE, respectively. As such, they have the same range as their respective base types. In addition, these subtypes have NOT NULL constraints.

These subtypes provide significant performance improvements over their respective base types when the PLSQL_CODE_TYPE parameter is set to NATIVE. This is because arithmetic operations for these subtypes are done directly in the hardware layer. Note that when PLSQL_CODE_TYPE is set to INTERPRETED (the default value), the performance gains are significantly smaller. This is illustrated by the following example.

For Example SET SERVEROUTPUT ON DECLARE v_pls_value1 PLS_INTEGER := 0; v_pls_value2 PLS_INTEGER := 1; v_simple_value1 SIMPLE_INTEGER := 0; v_simple_value2 SIMPLE_INTEGER := 1; — Following are used for elapsed time calculation — The time is calculated in 100th of a second v_start_time NUMBER; v_end_time NUMBER; BEGIN — Perform calculations with PLS_INTEGER v_start_time := DBMS_UTILITY.GET_TIME; FOR i in 1..50000000 LOOP v_pls_value1 := v_pls_value1 + v_pls_value2; END LOOP; v_end_time := DBMS_UTILITY.GET_TIME; DBMS_OUTPUT.PUT_LINE ('Elapsed time for PLS_INTEGER: ' (v_end_time - v_start_time)); — Perform the same calculations with SIMPLE_INTEGER v_start_time := DBMS_UTILITY.GET_TIME; FOR i in 1..50000000 LOOP v_simple_value1 := v_simple_value1 + v_simple_value2; END LOOP; v_end_time := DBMS_UTILITY.GET_TIME; DBMS_OUTPUT.PUT_LINE ('Elapsed time for SIMPLE_INTEGER: ' (v_end_time - v_start_time)); END;

This script compares the performance of the PLS_INTEGER datatype with its subtype SIMPLE_INTEGER via a numeric FOR loop. Note that for this run the PLSQL_CODE_TYPE parameter is set to its default value, INTERPRETED.

Elapsed time for PLS_INTEGER: 147 Elapsed time for SIMPLE_INTEGER: 115 PL/SQL procedure successfully completed. CONTINUE Statement

Similar to the EXIT statement, the CONTINUE statement controls loop iteration. Whereas the EXIT statement causes a loop to terminate and passes control of the execution outside the loop, the CONTINUE statement causes a loop to terminate its current iteration and passes control to the next iteration of the loop. The CONTINUE statement is covered in detail in Chapter 7, “Iterative Control—Part 2.”

Sequences in PL/SQL Expressions

Prior to Oracle 11g, the sequence pseudocolumns CURRVAL and NEXTVAL could be accessed in PL/SQL only through queries. Starting with Oracle 11g, these pseudocolumns can be accessed via expressions. This change not only improves PL/SQL source code, it also improves runtime performance and scalability.

For Example CREATE SEQUENCE test_seq START WITH 1 INCREMENT BY 1; Sequence created. SET SERVEROUTPUT ON DECLARE v_seq_value NUMBER; BEGIN v_seq_value := test_seq.NEXTVAL; DBMS_OUTPUT.PUT_LINE ('v_seq_value: 'v_seq_value); END;

This script causes an error when executed in Oracle 10g:

v_seq_value := test_seq.NEXTVAL;
• ERROR at line 4: ORA-06550: line 4, column 28: PLS-00357: Table,View Or Sequence reference 'TEST_SEQ.NEXTVAL' not allowed in this context ORA-06550: line 4, column 4: PL/SQL: Statement ignored

and it completes successfully when executed in Oracle 11g:

v_seq_value: 1 PL/SQL procedure successfully completed.

Consider another example that illustrates performance improvement when the PL/SQL expression is used to manipulate sequences:

For Example SET SERVEROUTPUT ON DECLARE v_seq_value NUMBER; — Following are used for elapsed time calculation v_start_time NUMBER; v_end_time NUMBER; BEGIN — Retrieve sequence via SELECT INTO statement v_start_time := DBMS_UTILITY.GET_TIME; FOR i in 1..10000 LOOP SELECT test_seq.NEXTVAL INTO v_seq_value FROM dual; END LOOP; v_end_time := DBMS_UTILITY.GET_TIME; DBMS_OUTPUT.PUT_LINE ('Elapsed time to retrieve sequence via SELECT INTO: ' (v_end_time-v_start_time)); — Retrieve sequence via PL/SQL expression v_start_time := DBMS_UTILITY.GET_TIME; FOR i in 1..10000 LOOP v_seq_value := test_seq.NEXTVAL; END LOOP; v_end_time := DBMS_UTILITY.GET_TIME; DBMS_OUTPUT.PUT_LINE ('Elapsed time to retrieve sequence via PL/SQL expression: ' (v_end_time-v_start_time)); END; Elapsed time to retrieve sequence via SELECT INTO: 52 Elapsed time to retrieve sequence via PL/SQL expression: 43 PL/SQL procedure successfully completed. Dynamic SQL Enhancements

In this version, Oracle has introduced a number of enhancements to the native dynamic SQL and DBMS_SQL package.

Native dynamic SQL enables you to generate dynamic SQL statements larger than 32K. In other words, it supports the CLOB datatype. Native dynamic SQL is covered in detail in Chapter 17, “Native Dynamic SQL.”

The DBMS_SQL package now supports all datatypes that native dynamic SQL supports. This includes the CLOB datatype. In addition, two new functions, DBMS_SQL.TO_REFCURSOR and DBMS_SQL.TO_CURSOR_NUMBER, enable you to switch between the native dynamic SQL and DBMS_SQL package.

Named and Mixed Notation in PL/SQL Subprogram Invocations

Prior to Oracle 11g, a SQL statement invoking a function had to specify the parameters in positional notation. In this release, mixed and named notations are allowed as well. Examples of positional, named, and mixed notations can be found in Chapter 21, “Packages,” and Chapter 23, “Object Types in Oracle.”

Consider the following example:

For Example CREATE OR REPLACE FUNCTION test_function (in_val1 IN NUMBER, in_val2 IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN (in_val1' - 'in_val2); END; Function created. SELECT test_function(1, 'Positional Notation') col1, test_function(in_val1 => 2, in_val2 => 'Named Notation') col2, test_function(3, in_val2 => 'Mixed Notation') col3 FROM dual; COL1 COL2 COL3 ———————————- ————————— ————————— 1 - Positional Notation 2 - Named Notation 3 - Mixed Notation

Note that mixed notation has a restriction: positional notation may not follow named notation. This is illustrated by the following SELECT:

SELECT test_function(1, 'Positional Notation') col1, test_function(in_val1 => 2, in_val2 => 'Named Notation') col2, test_function(in_val1 => 3, 'Mixed Notation') col3 FROM dual; test_function(in_val1 => 3, 'Mixed Notation') col3
• ERROR at line 4: ORA-06553: PLS-312: a positional parameter association may not follow a named association
Cross-Session PL/SQL Function Result Cache

A result-cached function is a function whose parameter values and result are stored in the cache. This means that when such a function is invoked with the same parameter values, its result is retrieved from the cache instead of being computed again. This caching mechanism is known as single-session caching because each session requires its own copy of the cache where function parameters and its results are stored.

Starting with Oracle 11, the caching mechanism for result-cached functions has been expanded to cross-session caching. In other words, the parameter values and results of the result-cached function are now stored in the shared global area (SGA) and are available to any session. Note that when an application is converted from single-session caching to cross-session caching, it requires more SGA but considerably less total system memory.

Consider the following example, which illustrates how a result-cached function may be created:

For Example — Package specification CREATE OR REPLACE PACKAGE test_pkg AS — User-defined record type TYPE zip_record IS RECORD (zip VARCHAR2(5), city VARCHAR2(25), state VARCHAR2(2)); — Result-cached function FUNCTION get_zip_info (in_zip NUMBER) RETURN zip_record RESULT_CACHE; END test_pkg; / — Package body CREATE OR REPLACE PACKAGE BODY test_pkg AS — Result-cached function FUNCTION get_zip_info (in_zip NUMBER) RETURN zip_record RESULT_CACHE RELIES_ON (ZIPCODE) IS rec zip_record; BEGIN SELECT zip, city, state INTO rec FROM zipcode WHERE zip = in_zip; RETURN rec; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN null; END get_zip_info; END test_pkg; /

Note the use of the RESULT_CACHE and RELIES_ON clauses. RESULT_CACHE specifies that the function is a result-cached function, and RELIES_ON specifies any tables and/or views that the function results depend on.

More Control over Triggers

Starting with Oracle 11g, the CREATE OR REPLACE TRIGGER clause may include ENABLE, DISABLE, and FOLLOWS options. The ENABLE and DISABLE options allow you to create a trigger in the enabled or disabled state, respectively. The FOLLOWS option allows you to specify the order in which triggers fire. Note that the FOLLOWS option applies to triggers that are defined on the same table and fire at the same timing point. Triggers are covered in detail in Chapter 13, “Triggers.”

Compound Triggers

A compound trigger is a new type of trigger that allows you to combine different types of triggers into one trigger. Specifically, you can combine the following:

  • A statement trigger that fires before the firing statement
  • A row trigger that fires before each row that the firing statement affects
  • A row trigger that fires after each row that the firing statement affects
  • A statement trigger that fires after the firing statement

This means that a single trigger may fire at different times when a transaction occurs. Compound triggers are covered in detail in Chapter 14, “Compound Triggers.”

Database Resident Connection Pool

Database Resident Connection Pool (DRCP) provides a connection pool that is shared by various middle-tier processes. The new package, DBMS_CONNECTION_POOL, enables database administrators to start and stop DRCP and configure its parameters.

Automatic Subprogram Inlining

The PL/SQL compiler translates PL/SQL code into machine code. Starting with Oracle 10g, the PL/SQL compiler can use the performance optimizer when compiling PL/SQL code. The performance optimizer enables the PL/SQL compiler to rearrange PL/SQL code to enhance performance. The optimization level used by the PL/SQL compiler is controlled by the PLSQL_OPTIMIZE_LEVEL parameter. Its values range from 0 to 2, where 2 is the default value. This means that the PL/SQL compiler performs optimization by default.

Starting with Oracle 11g, the PL/SQL compiler can perform subprogram inlining. Subprogram inlining substitutes a subprogram invocation with an actual copy of the called subprogram. This is achieved by specifying PRAGMA INLINE or setting the PLSQL_OPTIMIZE_LEVEL parameter to a new value, 3. When PLSQL_OPTIMIZE_LEVEL is set to 3, the PL/SQL compiler performs automatic subprogram inlining where appropriate. However, in some instances, the PL/SQL compiler may choose not to perform subprogram inlining because it believes it is undesirable.

The use of PRAGMA INLINE is illustrated in the following example. Note that in this example, PLSQL_OPTIMIZE_LEVEL has been set to its default value, 2.

For Example SET SERVEROUTPUT ON DECLARE v_num PLS_INTEGER := 1; v_result PLS_INTEGER; — Following are used for elapsed time calculation v_start_time NUMBER; v_end_time NUMBER; — Define function to test PRAGMA INLINE FUNCTION test_inline_pragma (in_num1 IN PLS_INTEGER, in_num2 IN PLS_INTEGER) RETURN PLS_INTEGER IS BEGIN RETURN (in_num1 + in_num2); END test_inline_pragma; BEGIN — Test function with INLINE PRAGMA enabled v_start_time := DBMS_UTILITY.GET_TIME; FOR i in 1..10000000 LOOP PRAGMA INLINE (test_inline_pragma, 'YES'); v_result := test_inline_pragma (1, i); END LOOP; v_end_time := DBMS_UTILITY.GET_TIME; DBMS_OUTPUT.PUT_LINE ('Elapsed time when PRAGMA INLINE enabled: ' (v_end_time-v_start_time)); — Test function with PRAGMA INLINE disabled v_start_time := DBMS_UTILITY.GET_TIME; FOR i in 1..10000000 LOOP PRAGMA INLINE (test_inline_pragma, 'NO'); v_result := test_inline_pragma (1, i); END LOOP; v_end_time := DBMS_UTILITY.GET_TIME; DBMS_OUTPUT.PUT_LINE ('Elapsed time when INLINE PRAGMA disabled: ' (v_end_time-v_start_time)); END; Elapsed time when PRAGMA INLINE enabled: 59 Elapsed time when PRAGMA INLINE disabled: 220 PL/SQL procedure successfully completed.

Note that PRAGMA INLINE affects every call to the specified subprogram when PRAGMA INLINE is placed immediately before one of the following:

  • Assignment
  • Call
  • Conditional
  • CASE
  • CONTINUE-WHEN
  • EXECUTE IMMEDIATE
  • EXIT-WHEN
  • LOOP
  • RETURN
PL/Scope

PL/Scope gathers and organizes data about user-defined identifiers used in PL/SQL code. This tool is used primarily in interactive development environments such as SQL Developer or Jdeveloper rather than directly in PL/SQL.

PL/SQL Hierarchical Profiler

PL/SQL hierarchical profiler enables you to profile PL/SQL applications. In other words, it gathers statistical information about the application such as execution times for SQL and PL/SQL, the number of calls to a particular subprogram made by the application, and the amount of time spent in the subprogram itself.

The hierarchical profiler is implemented via the Oracle-supplied package DBMS_HPROF, which is covered in Chapter 24, “Oracle Supplied Packages.”

PL/SQL Native Compiler Generates Native Code Directly

In this version of Oracle, the PL/SQL native compiler can generate native code directly. Previously, PL/SQL code was translated into C code, which then was translated by the C compiler into the native code. In some cases, this improves performance significantly. The PL/SQL compiler type is controlled via the PLSQL_CODE_TYPE parameter, which can be set to either INTERPRETED (the default value) or NATIVE.

© Copyright Pearson Education. All rights reserved.

Read More Show Less

Table of Contents

Acknowledgments

About the Authors

Introduction

Chapter 1 PL/SQL Concepts

Chapter 2 General Programming Language Fundamentals

Chapter 3 SQL in PL/SQL

Chapter 4 Conditional Control: IF Statements

Chapter 5 Conditional Control: CASE Statements

Chapter 6 Iterative Control: Part 1

Chapter 7 Iterative Control: Part 2

Chapter 8 Error Handling and Built-In Exceptions

Chapter 9 Exceptions

Chapter 10 Exceptions: Advanced Concepts

Chapter 11 Introduction to Cursors

Chapter 12 Advanced Cursors

Chapter 13 Triggers

Chapter 14 Compound Triggers

Chapter 15 Collections

Chapter 16 Records

Chapter 17 Native Dynamic SQL

Chapter 18 Bulk SQL

Chapter 19 Procedures

Chapter 20 Functions

Chapter 21 Packages

Chapter 22 Stored Code

Chapter 23 Object Types in Oracle

Chapter 24 Oracle Supplied Packages

Appendix A PL/SQL Formatting Guide

Appendix B Student Database Schema

Appendix C ANSI SQL Standards

Appendix D Answers to Try it Yourself Sections

Read More Show Less

Preface

Introduction

PL/SQL New Features in Oracle 11g

Oracle 11g has introduced a number of new features and improvements for PL/SQL. This introduction briefly describes features not covered in this book and points you to specific chapters for features that are within scope of this book. The list of features described here is also available in the “What’s New in PL/SQL?” section of the PL/SQL Language Reference manual offered as part of Oracle help available online.

The new PL/SQL features and enhancements are as follows:

  • Enhancements to regular expression built-in SQL functions
  • SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE datatypes
  • CONTINUE statement
  • Sequences in PL/SQL expressions
  • Dynamic SQL enhancements
  • Named and mixed notation in PL/SQL subprogram invocations
  • Cross-session PL/SQL function result cache
  • More control over triggers
  • Compound triggers
  • Database resident connection pool
  • Automatic subprogram inlining
  • PL/Scope
  • PL/SQL hierarchical profiler
  • PL/SQL native compiler generates native code directly

Enhancements to Regular Expression Built-In SQL Functions

In this release Oracle has introduced a new regular expression built-in function, REGEXP_COUNT. It returns the number of times a specified search pattern appears in a source string.

For Example

SELECT REGEXP_COUNT ('Oracle PL/SQL By Example Updated for Oracle 11g', 'ora', 1, 'i') FROM dual; REGEXP_COUNT('ORACLEPL/SQLBYEXAMPLEUPDATEDFORORACLE11G','ORA',1,'I') -------------------------------------------------------------------- 2

The REGEXP_COUNT function returns how many times the search pattern 'ora' appears in the source string 'Oracle PL/SQL...' 1 indicates the position of the source string where the search begins, and 'i' indicates case-insensitive matching.

The existing regular expression built-in functions, REGEXP_INSTR and REGEXP_SUBSTR, have a new parameter called SUBEXPR. This parameter represents a subexpression in a search pattern. Essentially it is a portion of a search pattern enclosed in parentheses that restricts pattern matching, as illustrated in the following example.

For Example

SELECT REGEXP_INSTR ('Oracle PL/SQL By Example Updated for Oracle 11g', '((ora)(cle))', 1, 2, 0, 'i') FROM dual; REGEXP_INSTR('ORACLEPL/SQLBYEXAMPLEUPDATEDFORORACLE11G',...) ------------------------------------------------------------ 38

The REGEXP_INSTR function returns the position of the first character in the source string 'Oracle PL/SQL...' corresponding to the second occurrence of the first subexpression 'ora' in the seach pattern (ora)(cle). 1 indicates the position of the source string where the search begins, 2 indicates the occurrence of the subexpression in the source string, 0 indicates that the position returned corresponds to the position of the first character where the match occurs, and 'i' indicates case-insensitive matching and REGEXP_SUBSTR.

SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE Datatypes

These datatypes are predefined subtypes of the PLS_INTEGER, BINARY_FLOAT, and BINARY_DOUBLE, respectively. As such, they have the same range as their respective base types. In addition, these subtypes have NOT NULL constraints.

These subtypes provide significant performance improvements over their respective base types when the PLSQL_CODE_TYPE parameter is set to NATIVE. This is because arithmetic operations for these subtypes are done directly in the hardware layer. Note that when PLSQL_CODE_TYPE is set to INTERPRETED (the default value), the performance gains are significantly smaller. This is illustrated by the following example.

For Example

SET SERVEROUTPUT ON DECLARE v_pls_value1 PLS_INTEGER := 0; v_pls_value2 PLS_INTEGER := 1; v_simple_value1 SIMPLE_INTEGER := 0; v_simple_value2 SIMPLE_INTEGER := 1; -- Following are used for elapsed time calculation -- The time is calculated in 100th of a second v_start_time NUMBER; v_end_time NUMBER; BEGIN -- Perform calculations with PLS_INTEGER v_start_time := DBMS_UTILITY.GET_TIME; FOR i in 1..50000000 LOOP v_pls_value1 := v_pls_value1 + v_pls_value2; END LOOP; v_end_time := DBMS_UTILITY.GET_TIME; DBMS_OUTPUT.PUT_LINE ('Elapsed time for PLS_INTEGER: ' (v_end_time - v_start_time)); -- Perform the same calculations with SIMPLE_INTEGER v_start_time := DBMS_UTILITY.GET_TIME; FOR i in 1..50000000 LOOP v_simple_value1 := v_simple_value1 + v_simple_value2; END LOOP; v_end_time := DBMS_UTILITY.GET_TIME; DBMS_OUTPUT.PUT_LINE ('Elapsed time for SIMPLE_INTEGER: ' (v_end_time - v_start_time)); END;

This script compares the performance of the PLS_INTEGER datatype with its subtype SIMPLE_INTEGER via a numeric FOR loop. Note that for this run the PLSQL_CODE_TYPE parameter is set to its default value, INTERPRETED.

Elapsed time for PLS_INTEGER: 147 Elapsed time for SIMPLE_INTEGER: 115 PL/SQL procedure successfully completed.

CONTINUE Statement

Similar to the EXIT statement, the CONTINUE statement controls loop iteration. Whereas the EXIT statement causes a loop to terminate and passes control of the execution outside the loop, the CONTINUE statement causes a loop to terminate its current iteration and passes control to the next iteration of the loop. The CONTINUE statement is covered in detail in Chapter 7, “Iterative Control—Part 2.”

Sequences in PL/SQL Expressions

Prior to Oracle 11g, the sequence pseudocolumns CURRVAL and NEXTVAL could be accessed in PL/SQL only through queries. Starting with Oracle 11g, these pseudocolumns can be accessed via expressions. This change not only improves PL/SQL source code, it also improves runtime performance and scalability.

For Example

CREATE SEQUENCE test_seq START WITH 1 INCREMENT BY 1; Sequence created. SET SERVEROUTPUT ON DECLARE v_seq_value NUMBER; BEGIN v_seq_value := test_seq.NEXTVAL; DBMS_OUTPUT.PUT_LINE ('v_seq_value: 'v_seq_value); END;

This script causes an error when executed in Oracle 10g:

v_seq_value := test_seq.NEXTVAL;
• ERROR at line 4: ORA-06550: line 4, column 28: PLS-00357: Table,View Or Sequence reference 'TEST_SEQ.NEXTVAL' not allowed in this context ORA-06550: line 4, column 4: PL/SQL: Statement ignored

and it completes successfully when executed in Oracle 11g:

v_seq_value: 1 PL/SQL procedure successfully completed.

Consider another example that illustrates performance improvement when the PL/SQL expression is used to manipulate sequences:

For Example

SET SERVEROUTPUT ON DECLARE v_seq_value NUMBER; -- Following are used for elapsed time calculation v_start_time NUMBER; v_end_time NUMBER; BEGIN -- Retrieve sequence via SELECT INTO statement v_start_time := DBMS_UTILITY.GET_TIME; FOR i in 1..10000 LOOP SELECT test_seq.NEXTVAL INTO v_seq_value FROM dual; END LOOP; v_end_time := DBMS_UTILITY.GET_TIME; DBMS_OUTPUT.PUT_LINE ('Elapsed time to retrieve sequence via SELECT INTO: ' (v_end_time-v_start_time)); -- Retrieve sequence via PL/SQL expression v_start_time := DBMS_UTILITY.GET_TIME; FOR i in 1..10000 LOOP v_seq_value := test_seq.NEXTVAL; END LOOP; v_end_time := DBMS_UTILITY.GET_TIME; DBMS_OUTPUT.PUT_LINE ('Elapsed time to retrieve sequence via PL/SQL expression: ' (v_end_time-v_start_time)); END; Elapsed time to retrieve sequence via SELECT INTO: 52 Elapsed time to retrieve sequence via PL/SQL expression: 43 PL/SQL procedure successfully completed.

Dynamic SQL Enhancements

In this version, Oracle has introduced a number of enhancements to the native dynamic SQL and DBMS_SQL package.

Native dynamic SQL enables you to generate dynamic SQL statements larger than 32K. In other words, it supports the CLOB datatype. Native dynamic SQL is covered in detail in Chapter 17, “Native Dynamic SQL.”

The DBMS_SQL package now supports all datatypes that native dynamic SQL supports. This includes the CLOB datatype. In addition, two new functions, DBMS_SQL.TO_REFCURSOR and DBMS_SQL.TO_CURSOR_NUMBER, enable you to switch between the native dynamic SQL and DBMS_SQL package.

Named and Mixed Notation in PL/SQL Subprogram Invocations

Prior to Oracle 11g, a SQL statement invoking a function had to specify the parameters in positional notation. In this release, mixed and named notations are allowed as well. Examples of positional, named, and mixed notations can be found in Chapter 21, “Packages,” and Chapter 23, “Object Types in Oracle.”

Consider the following example:

For Example

CREATE OR REPLACE FUNCTION test_function (in_val1 IN NUMBER, in_val2 IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN (in_val1' - 'in_val2); END; Function created. SELECT test_function(1, 'Positional Notation') col1, test_function(in_val1 => 2, in_val2 => 'Named Notation') col2, test_function(3, in_val2 => 'Mixed Notation') col3 FROM dual; COL1 COL2 COL3 ----------------------- ------------------ ------------------ 1 - Positional Notation 2 - Named Notation 3 - Mixed Notation

Note that mixed notation has a restriction: positional notation may not follow named notation. This is illustrated by the following SELECT:

SELECT test_function(1, 'Positional Notation') col1, test_function(in_val1 => 2, in_val2 => 'Named Notation') col2, test_function(in_val1 => 3, 'Mixed Notation') col3 FROM dual; test_function(in_val1 => 3, 'Mixed Notation') col3
• ERROR at line 4: ORA-06553: PLS-312: a positional parameter association may not follow a named association

Cross-Session PL/SQL Function Result Cache

A result-cached function is a function whose parameter values and result are stored in the cache. This means that when such a function is invoked with the same parameter values, its result is retrieved from the cache instead of being computed again. This caching mechanism is known as single-session caching because each session requires its own copy of the cache where function parameters and its results are stored.

Starting with Oracle 11, the caching mechanism for result-cached functions has been expanded to cross-session caching. In other words, the parameter values and results of the result-cached function are now stored in the shared global area (SGA) and are available to any session. Note that when an application is converted from single-session caching to cross-session caching, it requires more SGA but considerably less total system memory.

Consider the following example, which illustrates how a result-cached function may be created:

For Example

-- Package specification CREATE OR REPLACE PACKAGE test_pkg AS -- User-defined record type TYPE zip_record IS RECORD (zip VARCHAR2(5), city VARCHAR2(25), state VARCHAR2(2)); -- Result-cached function FUNCTION get_zip_info (in_zip NUMBER) RETURN zip_record RESULT_CACHE; END test_pkg; / -- Package body CREATE OR REPLACE PACKAGE BODY test_pkg AS -- Result-cached function FUNCTION get_zip_info (in_zip NUMBER) RETURN zip_record RESULT_CACHE RELIES_ON (ZIPCODE) IS rec zip_record; BEGIN SELECT zip, city, state INTO rec FROM zipcode WHERE zip = in_zip; RETURN rec; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN null; END get_zip_info; END test_pkg; /

Note the use of the RESULT_CACHE and RELIES_ON clauses. RESULT_CACHE specifies that the function is a result-cached function, and RELIES_ON specifies any tables and/or views that the function results depend on.

More Control over Triggers

Starting with Oracle 11g, the CREATE OR REPLACE TRIGGER clause may include ENABLE, DISABLE, and FOLLOWS options. The ENABLE and DISABLE options allow you to create a trigger in the enabled or disabled state, respectively. The FOLLOWS option allows you to specify the order in which triggers fire. Note that the FOLLOWS option applies to triggers that are defined on the same table and fire at the same timing point. Triggers are covered in detail in Chapter 13, “Triggers.”

Compound Triggers

A compound trigger is a new type of trigger that allows you to combine different types of triggers into one trigger. Specifically, you can combine the following:

  • A statement trigger that fires before the firing statement
  • A row trigger that fires before each row that the firing statement affects
  • A row trigger that fires after each row that the firing statement affects
  • A statement trigger that fires after the firing statement

This means that a single trigger may fire at different times when a transaction occurs. Compound triggers are covered in detail in Chapter 14, “Compound Triggers.”

Database Resident Connection Pool

Database Resident Connection Pool (DRCP) provides a connection pool that is shared by various middle-tier processes. The new package, DBMS_CONNECTION_POOL, enables database administrators to start and stop DRCP and configure its parameters.

Automatic Subprogram Inlining

The PL/SQL compiler translates PL/SQL code into machine code. Starting with Oracle 10g, the PL/SQL compiler can use the performance optimizer when compiling PL/SQL code. The performance optimizer enables the PL/SQL compiler to rearrange PL/SQL code to enhance performance. The optimization level used by the PL/SQL compiler is controlled by the PLSQL_OPTIMIZE_LEVEL parameter. Its values range from 0 to 2, where 2 is the default value. This means that the PL/SQL compiler performs optimization by default.

Starting with Oracle 11g, the PL/SQL compiler can perform subprogram inlining. Subprogram inlining substitutes a subprogram invocation with an actual copy of the called subprogram. This is achieved by specifying PRAGMA INLINE or setting the PLSQL_OPTIMIZE_LEVEL parameter to a new value, 3. When PLSQL_OPTIMIZE_LEVEL is set to 3, the PL/SQL compiler performs automatic subprogram inlining where appropriate. However, in some instances, the PL/SQL compiler may choose not to perform subprogram inlining because it believes it is undesirable.

The use of PRAGMA INLINE is illustrated in the following example. Note that in this example, PLSQL_OPTIMIZE_LEVEL has been set to its default value, 2.

For Example

SET SERVEROUTPUT ON DECLARE v_num PLS_INTEGER := 1; v_result PLS_INTEGER; -- Following are used for elapsed time calculation v_start_time NUMBER; v_end_time NUMBER; -- Define function to test PRAGMA INLINE FUNCTION test_inline_pragma (in_num1 IN PLS_INTEGER, in_num2 IN PLS_INTEGER) RETURN PLS_INTEGER IS BEGIN RETURN (in_num1 + in_num2); END test_inline_pragma; BEGIN -- Test function with INLINE PRAGMA enabled v_start_time := DBMS_UTILITY.GET_TIME; FOR i in 1..10000000 LOOP PRAGMA INLINE (test_inline_pragma, 'YES'); v_result := test_inline_pragma (1, i); END LOOP; v_end_time := DBMS_UTILITY.GET_TIME; DBMS_OUTPUT.PUT_LINE ('Elapsed time when PRAGMA INLINE enabled: ' (v_end_time-v_start_time)); -- Test function with PRAGMA INLINE disabled v_start_time := DBMS_UTILITY.GET_TIME; FOR i in 1..10000000 LOOP PRAGMA INLINE (test_inline_pragma, 'NO'); v_result := test_inline_pragma (1, i); END LOOP; v_end_time := DBMS_UTILITY.GET_TIME; DBMS_OUTPUT.PUT_LINE ('Elapsed time when INLINE PRAGMA disabled: ' (v_end_time-v_start_time)); END; Elapsed time when PRAGMA INLINE enabled: 59 Elapsed time when PRAGMA INLINE disabled: 220 PL/SQL procedure successfully completed.

Note that PRAGMA INLINE affects every call to the specified subprogram when PRAGMA INLINE is placed immediately before one of the following:

  • Assignment
  • Call
  • Conditional
  • CASE
  • CONTINUE-WHEN
  • EXECUTE IMMEDIATE
  • EXIT-WHEN
  • LOOP
  • RETURN

PL/Scope

PL/Scope gathers and organizes data about user-defined identifiers used in PL/SQL code. This tool is used primarily in interactive development environments such as SQL Developer or Jdeveloper rather than directly in PL/SQL.

PL/SQL Hierarchical Profiler

PL/SQL hierarchical profiler enables you to profile PL/SQL applications. In other words, it gathers statistical information about the application such as execution times for SQL and PL/SQL, the number of calls to a particular subprogram made by the application, and the amount of time spent in the subprogram itself.

The hierarchical profiler is implemented via the Oracle-supplied package DBMS_HPROF, which is covered in Chapter 24, “Oracle Supplied Packages.”

PL/SQL Native Compiler Generates Native Code Directly

In this version of Oracle, the PL/SQL native compiler can generate native code directly. Previously, PL/SQL code was translated into C code, which then was translated by the C compiler into the native code. In some cases, this improves performance significantly. The PL/SQL compiler type is controlled via the PLSQL_CODE_TYPE parameter, which can be set to either INTERPRETED (the default value) or NATIVE.

© Copyright Pearson Education. All rights reserved.

Read More Show Less

Introduction

Preface

Oracle PL/SQL by Example, 3rd edition, presents the Oracle PL/SQL programming language in a unique and highly effective format. It challenges you to learn Oracle PL/SQL by using it rather than by simply reading about it. Just as a grammar workbook would teach you about nouns and verbs by first showing you examples and then asking you to write sentences, Oracle PL/SQL by Example teaches you about cursors, loops, procedures, triggers, and so on by first showing you examples and then asking you to create these objects yourself.

Who This Book Is For

This book is intended for anyone who needs a quick but detailed introduction to programming with Oracle's PL/SQL language. The ideal readers are those with some relational database experience, with some Oracle experience, specifically with SQL and SQL*Plus, but with little or no experience with PL/SQL or with most other programming languages.

The content of this book is based on the material that is taught in an Introduction to PL/SQL class at Columbia University's Computer Technology and Applications (CTA) program in New York City. The student body is rather diverse, in that there are some students who have years of experience with information technology (IT) and programming, but no experience with Oracle PL/SQL, and then there are those with absolutely no experience in IT or programming. The content of the book, like the class, is balanced to meet the needs of both extremes. The exercises in this book can be used as lab and homework assignments to accompany the lectures in such a PL/SQL course.

How This Book Is Organized

The intent of this workbook is to teach you about Oracle PL/SQL bypresenting you with a series of challenges followed by detailed solutions to those challenges. The basic structure of each chapter is as follows:

Chapter
- Lab
- - Exercises
- - Exercise Answers (with detailed discussion)
- - Self-Review Questions
- Lab...

Test Your Thinking Questions

Each chapter contains interactive labs that introduce topics about Oracle PL/SQL. The topics are discussed briefly and then explored though exercises, which are the heart of each lab.

Each exercise consists of a series of steps that you will follow to perform a specific task, along with questions that are designed to help you discover the important things about PL/SQL programming on your own. The answers to these questions are given at the end of the Exercises, along with more in-depth discussion of the concepts explored.

The exercises are not meant to be closed-book quizzes to test your knowledge. On the contrary, they are intended to act as your guide and walk you through a task. You are encouraged to flip back and forth from the exercise question section to the exercise answer section so that, if need be, you can read the answers and discussions as you go along.

At the end of each lab is a series of multiple-choice self-review questions. These are meant to be closed-book quizzes to test how well you understood the lab material. The answers to these questions appear in Appendix A. Finally, at the end of each chapter you will find a Test Your Thinking section, which consists of a series of projects designed to solidify all of the skills you have learned in the chapter. If you have successfully completed all of the labs in the chapter, you should be able to tackle these projects with few problems. You will find guidance and/or solutions to these in Appendix D and at the companion Web site.

Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously
Sort by: Showing all of 3 Customer Reviews
  • Anonymous

    Posted September 29, 2003

    You can access a PL/SQL executable with this book

    Very much a task oriented book, suitable for a classroom or self paced study. It is aimed at someone who wants or needs explicit lessons and exercises. Thoughtfully, the authors also furnished answers to the exercises. Sometimes with other computing books, I have wondered why their authors omit questions. Perhaps the nature of the topic makes it awkward to provide problem sets. But not for Oracle's PL/SQL language. So this book is chock-a-block with them. To students, retention is greatly enhanced by doing, and the book will take you a long way into facility with PL/SQL if you can discipline yourself to go through it methodically and not peek at the answers till you have given the problems a fair go. As far as the language itself, the book's presentation reminds me of a text on fortran or C, and unlike a text on java or C#. Firstly, the latter two are object-oriented, while PL/SQL, fortran and C are procedural. So if you have already learnt fortran or C and you read this book, step back and defocus a little from the specific syntax. Stylistically, the book has the flavour of many texts in those languages. Reinforced by PL/SQL not having a GUI. The I/O is character based. A slight retro feel. [Though PL/SQL should NOT have a GUI. It is optimised as a query language.] One thing to note is that the book explains using examples and exercises that are at the subroutine or procedural level, as mentioned above. Bite sized. What it lacks are higher level examples that necessitate decomposing a problem into several procedures. But this may be unfair. Those are harder, more abstract issues, whereas this book is meant for someone who still needs to learn the syntax in the first place. It does suggest the utility of such a book, as a logical successor to this. A final note relates to the problem sets. There is a difficulty with learning PL/SQL compared to, say, C or java. With C you can download an open source compiler. With java, you can get a free compiler from Sun or IBM. With SQL, there is the open source MySQL. Not so with PL/SQL. I don't think Oracle offers a free version. So if you are studying on your own, this is a severe quandary. How do you get a PL/SQL executable? Otherwise, the attraction of this book, or any other on PL/SQL, for that matter, is greatly restricted to classrooms or companies that already have that, and not to individuals. Well, the authors and the publisher offer access, at the publisher's website. This may be the strongest merit of the book.

    Was this review helpful? Yes  No   Report this review
  • Anonymous

    Posted July 16, 2009

    No text was provided for this review.

  • Anonymous

    Posted November 25, 2008

    No text was provided for this review.

Sort by: Showing all of 3 Customer Reviews

If you find inappropriate content, please report it to Barnes & Noble
Why is this product inappropriate?
Comments (optional)