[PDF] Oracle PL SQL by Example.pdf In the following exercises you

View - Download Oracle PL SQL by Example.pdf

Previous PDF

Next PDF

Oracle

PL/SQL

by Example

FOURTH EDITION

This page intentionally left blank

Oracle

PL/SQL

by Example

FOURTH EDITION

BENJAMIN ROSENZWEIG

ELENA SILVESTROVA RAKHIMOV

Upper Saddle River, NJ • Boston • Indianapolis • San Francisco • New York • Toronto • Montreal • London • Munich • Paris • Madrid • Cape Town • Sydney •

Tokyo • Singapore • Mexico City

Many of the designations used by manufacturers and sellers to distinguish their products are claimed as

trademarks. Where those designations appear in this book, and the publisher was aware of a trademark claim, the designations have been printed with initial capital letters or in all capitals. The authors and publisher have taken care in the preparation of this book, but make no expressed or implied warranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for incidental or consequential damages in connection with or arising out of the use of the information or programs contained herein. The publisher offers excellent discounts on this book when ordered in quantity for bulk purchases or

special sales, which may include electronic versions and/or custom covers and content particular to your

business, training goals, marketing focus, and branding interests. For more information, please contact:

U.S. Corporate and Government Sales

800-382-3419

corpsales@pearsontechgroup.com For sales outside the United States, please contact:

International Sales

international@pearson.com

Visit us on the Web: www.informit.com/ph

Library of Congress Cataloging-in-Publication Data

Rosenzweig, Benjamin.

Oracle PL/SQL by example / Benjamin Rosenzweig, Elena Silvestrova Rakhimov. p. cm. ISBN 0-13-714422-9 (pbk. : alk. paper) 1. PL/SQL (Computer program language) 2. Oracle (Computer file) 3. Relational databases. I. Rakhimov, Elena Silvestrova. II. Title.

QA76.73.P258R68 2008

005.75"6-dc22

2008022398

Copyright © 2009 Pearson Education, Inc. All rights reserved. Printed in the United States of America.

This publication is protected by copyright, and permission must be obtained from the publisher prior to

any prohibited reproduction, storage in a retrieval system, or transmission in any form or by any means,

electronic, mechanical, photocopying, recording, or likewise.

For information regarding permissions, write to:

Pearson Education, Inc.

Rights and Contracts Department

501 Boylston Street, Suite 900

Boston, MA 02116

Fax: (617) 671 3447

ISBN-13: 978-0-137-14422-8

ISBN-10: 0-137-14422-9

Text printed in the United States on recycled paper at Edwards Brothers in Ann Arbor, Michigan

First printing August 2008

Editor-in-Chief: Mark Taub

Acquisitions Editor: Trina MacDonald

Development Editor: Songlin Qiu

Managing Editor: Kristy Hart

Project Editor: Todd Taber

Copy Editor: Gayle Johnson

Indexer: Erika Millen

Proofreader: Debbie Williams

Technical Reviewers: Oleg Voskoboynikov,

Shahdad Moradi

Publishing Coordinator: Olivia Basegio

Cover Designer: Chuti Prasertsith

Composition: Nonie Ratcliff

To my parents, Rosie and Sandy Rosenzweig, for their love and support. -Benjamin Rosenzweig

To Sean. -Elena Silvestrova Rakhimov

This page intentionally left blank

Acknowledgments xiv

About the Authors xv

Introduction xvii

CHAPTER 1PL/SQL Concepts 1

LAB 1.1PL/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.2PL/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 2General Programming Language

Fundamentals 21

LAB 2.1PL/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 3SQL in PL/SQL 39

LAB 3.1Making 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.2Making 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

Contents

CHAPTER 4Conditional Control:IF Statements 53

LAB 4.1IF Statements 54

4.1.1 Use the IF-THEN Statement 58

4.1.2 Use the IF-THEN-ELSE Statement 62

LAB 4.2ELSIF Statements 65

4.2.1 Use the ELSIF Statement 69

LAB 4.3Nested IF Statements 74

4.3.1 Use Nested IF Statements 76

Chapter 4 Try It Yourself 80

CHAPTER 5Conditional Control:CASE Statements 81

LAB 5.1CASE Statements 82

5.1.1 Use the CASE Statement 89

5.1.2 Use the Searched CASE Statement 91

LAB 5.2CASE Expressions 96

5.2.1 Use the CASE Expression 100

LAB 5.3NULLIF 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 6Iterative Control:Part I 113

LAB 6.1Simple 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.2WHILE Loops 124

6.2.1 Use WHILE Loops 128

LAB 6.3Numeric 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 7Iterative Control:Part II 143

LAB 7.1The CONTINUE Statement 144

7.1.1 Use the CONTINUE Statement 146

7.1.2 Use the CONTINUE WHEN Condition 152

viiiContents

LAB 7.2Nested Loops 154

7.2.1 Use Nested Loops 157

Chapter 7 Try It Yourself 161

CHAPTER 8Error Handling and Built-in Exceptions 163

LAB 8.1Handling Errors 164

8.1.1 Understand the Importance of Error Handling 167

LAB 8.2Built-in Exceptions 169

8.2.1 Use Built-in Exceptions 174

Chapter 8 Try It Yourself 178

CHAPTER 9Exceptions 179

LAB 9.1Exception Scope 180

9.1.1 Understand the Scope of an Exception 183

LAB 9.2User-Defined Exceptions 188

9.2.1 Use User-Defined Exceptions 193

LAB 9.3Exception Propagation 197

9.3.1 Understand How Exceptions Propagate 203

9.3.2 Reraise Exceptions 206

Chapter 9 Try It Yourself 209

CHAPTER 10Exceptions:Advanced Concepts 211

LAB 10.1RAISE_APPLICATION_ERROR 212

10.1.1 Use RAISE_APPLICATION_ERROR 215

LAB 10.2EXCEPTION_INIT Pragma 217

10.2.1 USE the EXCEPTION_INIT Pragma 219

LAB 10.3SQLCODE and SQLERRM 222

10.3.1 Use SQLCODE and SQLERRM 225

Chapter 10 Try It Yourself 227

CHAPTER 11Introduction to Cursors 229

LAB 11.1Cursor Manipulation 230

11.1.1 Make Use of Record Types 234

11.1.2 Process an Explicit Cursor 235

11.1.3 Make Use of Cursor Attributes 240

11.1.4 Put It All Together 242

Contentsix

LAB 11.2Using Cursor FOR Loops and Nested Cursors 246

11.2.1 Use a Cursor FOR Loop 247

11.2.2 Process Nested Cursors 247

Chapter 11 Try It Yourself 252

CHAPTER 12Advanced Cursors 253

LAB 12.1Using Parameters with Cursors and Complex Nested Cursors 254

12.1.1 Use Parameters in a Cursor 255

12.1.2 Use Complex Nested Cursors 255

LAB 12.2FOR UPDATE and WHERE CURRENT Cursors 258

12.2.1 For UPDATE and WHERE CURRENT Cursors 258

CHAPTER 13Triggers 263

LAB 13.1What Triggers Are 264

13.1.1 Understand What a Trigger Is 272

13.1.2 Use BEFORE and AFTER Triggers 274

LAB 13.2Types of Triggers 277

13.2.1 Use Row and Statement Triggers 283

13.2.2 Use INSTEAD OF Triggers 285

Chaper 13 Try It Yourself 290

CHAPTER 14Compound Triggers 291

LAB 14.1Mutating Table Issues 292

14.1.1 Understand Mutating Tables 296

LAB 14.2Compound Triggers 300

14.2.1 Understand Compound Triggers 306

Chapter 14 Try It Yourself 313

CHAPTER 15Collections 315

LAB 15.1PL/SQL Tables 316

15.1.1 Use Associative Arrays 326

15.1.2 Use Nested Tables 330

LAB 15.2Varrays 334

15.2.1 Use Varrays 338

LAB 15.3Multilevel Collections 342

15.3.1 Use Multilevel Collections 344

Chapter 15 Try It Yourself 348

xContents

CHAPTER 16Records 349

LAB 16.1Record Types 350

16.1.1 Use Table-Based and Cursor-Based Records 358

16.1.2 Use User-Defined Records 362

LAB 16.2Nested Records 367

16.2.1 Use Nested Records 369

LAB 16.3Collections of Records 373

16.3.1 Use Collections of Records 374

Chapter 16 Try It Yourself 378

CHAPTER 17Native Dynamic SQL 379

LAB 17.1EXECUTE IMMEDIATE Statements 380

17.1.1 Use the EXECUTE IMMEDIATE Statement 387

LAB 17.2OPEN-FOR,FETCH,and CLOSE Statements 392

17.2.1 Use OPEN-FOR,FETCH,and CLOSE Statements 395

Chapter 17 Try It Yourself 401

CHAPTER 18Bulk SQL 403

LAB 18.1The FORALL Statement 404

18.1.1 Use the FORALL Statement 413

LAB 18.2The BULK COLLECT Clause 422

18.2.1 Use the BULK COLLECT Statement 428

Chapter 18 Try It Yourself 437

CHAPTER 19Procedures 439

LAB 19.1Creating Procedures 441

19.1.1 Create Procedures 441

19.1.2 Query the Data Dictionary for Information on Procedures 443

LAB 19.2Passing Parameters into and out of Procedures 444

19.2.1 Use IN and OUT Parameters with Procedures 445

Chapter 19 Try It Yourself 447

Part 1 447

Part 2 447

Contentsxi

CHAPTER 20Functions 449

LAB 20.1Creating and Using Functions 450

20.1.1 Create Stored Functions 451

20.1.2 Make Use of Functions 452

20.1.3 Invoke Functions in SQL Statements 453

20.1.4 Write Complex Functions 454

Chapter 20 Try It Yourself 455

CHAPTER 21Packages 457

LAB 21.1The Benefits of Using Packages 458

21.1.1 Create Package Specifications 460

21.1.2 Create Package Bodies 462

21.1.3 Call Stored Packages 464

21.1.4 Create Private Objects 465

21.1.5 Create Package Variables and Cursors 469

LAB 21.2Cursor Variables 471

21.2.1 Make Use of Cursor Variables 475

LAB 21.3Extending the Package 480

21.3.1 Extend the Package 480

Chapter 21 Try It Yourself 493

CHAPTER 22Stored Code 495

LAB 22.1Gathering Information About Stored Code 496

22.1.1 Get Stored Code Information from the Data Dictionary 496

22.1.2 Enforce the Purity Level with the RESTRICT_REFERENCES

Pragma 500

22.1.3 Overload Modules 506

Chapter 22 Try It Yourself 512

CHAPTER 23Object Types in Oracle 513

LAB 23.1Object Types 514

23.1.1 Use Object Types 522

23.1.2 Use Object Types with Collections 526

LAB 23.2Object Type Methods 531

23.2.1 Use Object Type Methods 544

Chapter 23 Try It Yourself 554

xiiContents

CHAPTER 24Oracle Supplied Packages 555

LAB 24.1Making Use of Oracle Supplied Packages to Profile PL/SQL,Access Files, and Schedule Jobs 556

24.1.1 Access Files with UTL_FILE 563

24.1.2 Schedule Jobs with DBMS_JOB 563

24.1.3 Submit Jobs 564

LAB 24.2Making Use of Oracle-Supplied Packages to Generate an Explain

Plan and Create HTML Pages 568

24.2.1 Generate an Explain Plan with DBMS_XPLAN 572

LAB 24.3Creating Web Pages with the Oracle Web Toolkit 578

24.3.1 Create an HTML Page with the Oracle Web Toolkit 594

APPENDIX APL/SQL Formatting Guide 597

APPENDIX BStudent Database Schema 601

APPENDIX CANSI SQL Standards 607

APPENDIX DAnswers to the Try It Yourself Sections 613

INDEX705

1) Visit www.informit.com/title/0137144229 to learn how to register this product and gain

access to additional content.

2) To register this product and gain access to bonus content, go to www.informit.com/register to

sign in and enter the ISBN. After you register the product, a link to the additional content will be listed on your Account page, under Registered Products.

Contentsxiii

ACKNOWLEDGMENTS

Benjamin Rosenzweig:I would like to thank my coauthor, Elena Silvestrova Rakhimov, for being a wonderful and knowledgeable colleague to work with. I would also like to thank Douglas Scherer for giving me the opportunity to work on this book, as well as for providing constant support and assistance through the entire writing process. I am indebted to the team at Prentice Hall, which includes Trina MacDonald, Songlin Qiu, Todd Taber, Shahdad Moradi, and Oleg Voskoboynikov. Their contributions, suggestions, and edits helped improve our original manuscript and make the book what it is today. Finally, I would like to thank my many friends and family, especially Edward Clarin and Edward Knopping, for helping me through the long process of putting the whole book together, which included many late nights and weekends. Elena Silvestrova Rakhimov: My contribution to this book reflects the help and advice of many people. I am particularly indebted to my coauthor, Benjamin Rosenzweig, for making this project a rewarding and enjoyable experience. Special thanks to Trina MacDonald, Songlin Qiu, Todd Taber, and many others at Prentice Hall who diligently worked to bring this book to market. Thanks to Shahdad Moradi and Oleg Voskoboynikov for their valuable comments and sugges- tions. Most importantly, to my family, whose excitement, enthusiasm, inspiration, and support encouraged me to work hard to the very end, and were exceeded only by their love.

ABOUT THE AUTHORS

Benjamin Rosenzweigis 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 data- base 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 Rakhimovhas more than 15 years of experience in database development in a wide spectrum of enterprise and business environments, ranging from nonprofit organiza- tions 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.

This page intentionally left blank

INTRODUCTION

PL/SQL New Features in Oracle 11g

Oracle 11g has introduced a number of new features and improvements for PL/SQL. This intro- duction briefly describes features not covered in this book and points you to specific chapters

for features that arewithin 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. xviiiIntroduction 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 opera- tions 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;Introduction

xix xxIntroduction 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 expres- sion is used to manipulate sequences:

FOR EXAMPLE

quotesdbs_dbs17.pdfusesText_23

[PDF] oracle sql syntax

[PDF] oracle sql tuning pocket reference pdf

[PDF] oracle support chat

[PDF] oracle support number australia

[PDF] oracle support number canada

[PDF] oracle support number for sr

[PDF] oracle support number singapore

[PDF] oracle support number to escalate sr

[PDF] oracle support number uk

[PDF] oracle support romania

[PDF] oracle tuning: the definitive reference third edition pdf download

[PDF] oracle tutorial for beginners with examples pdf

[PDF] oral dextrose for cats

[PDF] oral mediation cefr

[PDF] orange and rockland bulk storage rfp