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 11grs"s significantly improved triggers. One step at a time, yours"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 yours"ve learned, strengthening your understanding through real-world practice. This bookrs"s approach fully reflects the authorsrs" 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 bull; Mastering basic PL/SQL concepts and general programming language fundamentals, and understanding SQLrs"s role in PL/SQL bull; Using conditional and iterative program control techniques, including the new CONTINUE and CONTINUE WHEN statements bull; Efficiently handling errors and exceptions bull; Working with cursors and triggers, including Oracle 11grs"s powerful new compound triggers bull; Using stored procedures, functions, and packages to write modular code that other programs can execute bull; Working with collections, object-relational features, native dynamic SQL, bulk SQL, and other advanced PL/SQL capabilities bull; Handy reference appendices: PL/SQL formatting guide, sample database schema, ANSI SQL standards reference, and more Benjamin Rosenzweig is a Software Development Manager at Misys. Previously he was a Principal Consultant at Oracle. His experience ranges from creating an electronic Tibetan-English Dictionary in Kathmandu, Nepal, to supporting presentation centers at Goldman Sachs and managing trading systems at TIAA-CREF. As an instructor at Columbia Universityrs"s Computer Technology and Application Program, he was awarded the Outstanding Teaching Award. Rosenzweig wrote and presentedOracle Forms Developer: The Complete Video Course, and coauthoredOracle Web Application Programming for PL/SQL Developers. Elena Silvestrova Rakhimov is Senior Developer and Team Lead at Alea Software. She has more than fifteen years of experience in database development in a wide spectrum of enterprise and business environments, ranging from non-profit organizations to Wall Street. She has taught database programming at Columbia University. Contents Acknowledgments xiv About the Authors xv

Author Biography

Read more

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.

Table of Contents

Read more

Acknowledgments
About the Authors
Introduction
PL/SQL Concepts
PL/SQL in Client-Server Architecture
Use PL/SQL Anonymous Blocks
Understand How PL/SQL Gets Executed
PL/SQL in SQL*Plus
Use Substitution Variables
Use the DBMS_OUTPUT.PUT_LINE Statement
Table of Contents provided by Publisher. All Rights Reserved.

Supplemental Materials

Read more

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

Excerpts

Read more

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 thatarewithin 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...'' 1indicates 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).1indicates the position of the source string where the search begins,2indicates the occurrence of the subexpression in the source string,0indicates 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 ControlPart 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 CLO