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
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 University’s Computer Technology and Application Program, he was awarded the Outstanding Teaching Award. Rosenzweig wrote and presented Oracle Forms Developer: The Complete Video Course, and coauthored Oracle 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
Introduction xvii
CHAPTER 1 PL/SQL Concepts 1
LAB 1.1 PL/SQL in Client/Server Architecture 2
1.1.1 Use PL/SQL Anonymous Blocks 8
1.1.2 Understand How PL/SQL Gets Executed 10
LAB 1.2 PL/SQL in SQL*Plus 12
1.2.1 Use Substitution Variables 16
1.2.2 Use the DBMS_OUTPUT.PUT_LINE Statement 17
Chapter 1 Try It Yourself 19
CHAPTER 2 General Programming Language
Fundamentals 21
LAB 2.1 PL/SQL Programming Fundamentals 22
2.1.1 Make Use of PL/SQL Language Components 23
2.1.2 Make Use of PL/SQL Variables 24
2.1.3 Handle PL/SQL Reserved Words 26
2.1.4 Make Use of Identifiers in PL/SQL 27
2.1.5 Make Use of Anchored Datatypes 28
2.1.6 Declare and Initialize Variables 31
2.1.7 Understand the Scope of a Block, Nested Blocks, and Labels 34
Chapter 2 Try It Yourself 37
CHAPTER 3 SQL in PL/SQL 39
LAB 3.1 Making Use of DML in PL/SQL 40
3.1.1 Use the Select INTO Syntax for Variable Initialization 41
3.1.2 Use DML in a PL/SQL Block 42
3.1.3 Make Use of a Sequence in a PL/SQL Block 44
LAB 3.2 Making Use of SAVEPOINT 45
3.2.1 Make Use of COMMIT, ROLLBACK, and SAVEPOINT in a PL/SQL Block 48
Chapter 3 Try It Yourself 51
CHAPTER 4 Conditional Control: IF Statements 53
LAB 4.1 IF Statements 54
4.1.1 Use the IF-THEN Statement 58
4.1.2 Use the IF-THEN-ELSE Statement 62
LAB 4.2 ELSIF Statements 65
4.2.1 Use the ELSIF Statement 69
LAB 4.3 Nested IF Statements 74
4.3.1 Use Nested IF Statements 76
Chapter 4 Try It Yourself 80
CHAPTER 5 Conditional Control: CASE Statements 81
LAB 5.1 CASE Statements 82
5.1.1 Use the CASE Statement 89
5.1.2 Use the Searched CASE Statement 91
LAB 5.2 CASE Expressions 96
5.2.1 Use the CASE Expression 100
LAB 5.3 NULLIF and COALESCE Functions 103
5.3.1 The NULLIF Function 107
5.3.2 Use the COALESCE Function 109
Chapter 5 Try It Yourself 112
CHAPTER 6 Iterative Control: Part I 113
LAB 6.1 Simple Loops 114
6.1.1 Use Simple Loops with EXIT Conditions 118
6.1.2 Use Simple Loops with EXIT WHEN Conditions 120
LAB 6.2 WHILE Loops 124
6.2.1 Use WHILE Loops 128
LAB 6.3 Numeric FOR Loops 132
6.3.1 Use Numeric FOR Loops with the IN Option 137
6.3.2 Use Numeric FOR Loops with the REVERSE Option 139
Chapter 6 Try It Yourself 142
CHAPTER 7 Iterative Control: Part II 143
LAB 7.1 The CONTINUE Statement 144
&...
About 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.
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
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:
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 ----------------------- ------------------ -----------... **
Description:
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
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 University’s Computer Technology and Application Program, he was awarded the Outstanding Teaching Award. Rosenzweig wrote and presented Oracle Forms Developer: The Complete Video Course, and coauthored Oracle 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
Introduction xvii
CHAPTER 1 PL/SQL Concepts 1
LAB 1.1 PL/SQL in Client/Server Architecture 2
1.1.1 Use PL/SQL Anonymous Blocks 8
1.1.2 Understand How PL/SQL Gets Executed 10
LAB 1.2 PL/SQL in SQL*Plus 12
1.2.1 Use Substitution Variables 16
1.2.2 Use the DBMS_OUTPUT.PUT_LINE Statement 17
Chapter 1 Try It Yourself 19
CHAPTER 2 General Programming Language
Fundamentals 21
LAB 2.1 PL/SQL Programming Fundamentals 22
2.1.1 Make Use of PL/SQL Language Components 23
2.1.2 Make Use of PL/SQL Variables 24
2.1.3 Handle PL/SQL Reserved Words 26
2.1.4 Make Use of Identifiers in PL/SQL 27
2.1.5 Make Use of Anchored Datatypes 28
2.1.6 Declare and Initialize Variables 31
2.1.7 Understand the Scope of a Block, Nested Blocks, and Labels 34
Chapter 2 Try It Yourself 37
CHAPTER 3 SQL in PL/SQL 39
LAB 3.1 Making Use of DML in PL/SQL 40
3.1.1 Use the Select INTO Syntax for Variable Initialization 41
3.1.2 Use DML in a PL/SQL Block 42
3.1.3 Make Use of a Sequence in a PL/SQL Block 44
LAB 3.2 Making Use of SAVEPOINT 45
3.2.1 Make Use of COMMIT, ROLLBACK, and SAVEPOINT in a PL/SQL Block 48
Chapter 3 Try It Yourself 51
CHAPTER 4 Conditional Control: IF Statements 53
LAB 4.1 IF Statements 54
4.1.1 Use the IF-THEN Statement 58
4.1.2 Use the IF-THEN-ELSE Statement 62
LAB 4.2 ELSIF Statements 65
4.2.1 Use the ELSIF Statement 69
LAB 4.3 Nested IF Statements 74
4.3.1 Use Nested IF Statements 76
Chapter 4 Try It Yourself 80
CHAPTER 5 Conditional Control: CASE Statements 81
LAB 5.1 CASE Statements 82
5.1.1 Use the CASE Statement 89
5.1.2 Use the Searched CASE Statement 91
LAB 5.2 CASE Expressions 96
5.2.1 Use the CASE Expression 100
LAB 5.3 NULLIF and COALESCE Functions 103
5.3.1 The NULLIF Function 107
5.3.2 Use the COALESCE Function 109
Chapter 5 Try It Yourself 112
CHAPTER 6 Iterative Control: Part I 113
LAB 6.1 Simple Loops 114
6.1.1 Use Simple Loops with EXIT Conditions 118
6.1.2 Use Simple Loops with EXIT WHEN Conditions 120
LAB 6.2 WHILE Loops 124
6.2.1 Use WHILE Loops 128
LAB 6.3 Numeric FOR Loops 132
6.3.1 Use Numeric FOR Loops with the IN Option 137
6.3.2 Use Numeric FOR Loops with the REVERSE Option 139
Chapter 6 Try It Yourself 142
CHAPTER 7 Iterative Control: Part II 143
LAB 7.1 The CONTINUE Statement 144
&...
About 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.
Excerpt. © Reprinted by permission. All rights reserved.
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
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
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
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
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.
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
This script causes an error when executed in Oracle 10g:
and it completes successfully when executed in Oracle 11g:
Consider another example that illustrates performance improvement when the PL/SQL expression is used to manipulate sequences:
For Example
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