Sumário Itens Encontrados: 202CHAPTER 1 PL/SQL Concepts 1LAB 1.1 PL/SQL in Client/Server Architecture 21.1.1 Use PL/SQL Anonymous Blocks 81.1.2 Understand How PL/SQL Gets Executed 10LAB 1.2 PL/SQL in SQL*Plus 121.2.1 Use Substitution Variables 161.2.2 Use the DBMS_OUTPUT.PUT_LINE Statement 17Chapter 1 Try It Yourself 19CHAPTER 2 General Programming LanguageFundamentals 21LAB 2.1 PL/SQL Programming Fundamentals 222.1.1 Make Use of PL/SQL Language Components 232.1.2 Make Use of PL/SQL Variables 242.1.3 Handle PL/SQL Reserved Words 262.1.4 Make Use of Identifiers in PL/SQL 272.1.5 Make Use of Anchored Datatypes 282.1.6 Declare and Initialize Variables 312.1.7 Understand the Scope of a Block, Nested Blocks, and Labels 34Chapter 2 Try It Yourself 37CHAPTER 3 SQL in PL/SQL 39LAB 3.1 Making Use of DML in PL/SQL 403.1.1 Use the Select INTO Syntax for Variable Initialization 413.1.2 Use DML in a PL/SQL Block 423.1.3 Make Use of a Sequence in a PL/SQL Block 44LAB 3.2 Making Use of SAVEPOINT 453.2.1 Make Use of COMMIT, ROLLBACK, and SAVEPOINTin a PL/SQL Block 48Chapter 3 Try It Yourself 51CHAPTER 4 Conditional Control: IF Statements 53LAB 4.1 IF Statements 544.1.1 Use the IF-THEN Statement 584.1.2 Use the IF-THEN-ELSE Statement 62LAB 4.2 ELSIF Statements 654.2.1 Use the ELSIF Statement 69LAB 4.3 Nested IF Statements 744.3.1 Use Nested IF Statements 76Chapter 4 Try It Yourself 80CHAPTER 5 Conditional Control: CASE Statements 81LAB 5.1 CASE Statements 825.1.1 Use the CASE Statement 895.1.2 Use the Searched CASE Statement 91LAB 5.2 CASE Expressions 965.2.1 Use the CASE Expression 100LAB 5.3 NULLIF and COALESCE Functions 1035.3.1 The NULLIF Function 1075.3.2 Use the COALESCE Function 109Chapter 5 Try It Yourself 112CHAPTER 6 Iterative Control: Part I 113LAB 6.1 Simple Loops 1146.1.1 Use Simple Loops with EXIT Conditions 1186.1.2 Use Simple Loops with EXIT WHEN Conditions 120LAB 6.2 WHILE Loops 1246.2.1 Use WHILE Loops 128LAB 6.3 Numeric FOR Loops 1326.3.1 Use Numeric FOR Loops with the IN Option 1376.3.2 Use Numeric FOR Loops with the REVERSE Option 139Chapter 6 Try It Yourself 142CHAPTER 7 Iterative Control: Part II 143LAB 7.1 The CONTINUE Statement 1447.1.1 Use the CONTINUE Statement 1467.1.2 Use the CONTINUE WHEN Condition 152LAB 7.2 Nested Loops 1547.2.1 Use Nested Loops 157Chapter 7 Try It Yourself 161CHAPTER 8 Error Handling and Built-in Exceptions 163LAB 8.1 Handling Errors 1648.1.1 Understand the Importance of Error Handling 167LAB 8.2 Built-in Exceptions 1698.2.1 Use Built-in Exceptions 174Chapter 8 Try It Yourself 178CHAPTER 9 Exceptions 179LAB 9.1 Exception Scope 1809.1.1 Understand the Scope of an Exception 183LAB 9.2 User-Defined Exceptions 1889.2.1 Use User-Defined Exceptions 193LAB 9.3 Exception Propagation 1979.3.1 Understand How Exceptions Propagate 2039.3.2 Reraise Exceptions 206Chapter 9 Try It Yourself 209CHAPTER 10 Exceptions: Advanced Concepts 211LAB 10.1 RAISE_APPLICATION_ERROR 21210.1.1 Use RAISE_APPLICATION_ERROR 215LAB 10.2 EXCEPTION_INIT Pragma 21710.2.1 USE the EXCEPTION_INIT Pragma 219LAB 10.3 SQLCODE and SQLERRM 22210.3.1 Use SQLCODE and SQLERRM 225Chapter 10 Try It Yourself 227CHAPTER 11 Introduction to Cursors 229LAB 11.1 Cursor Manipulation 23011.1.1 Make Use of Record Types 23411.1.2 Process an Explicit Cursor 23511.1.3 Make Use of Cursor Attributes 24011.1.4 Put It All Together 242LAB 11.2 Using Cursor FOR Loops and Nested Cursors 24611.2.1 Use a Cursor FOR Loop 24711.2.2 Process Nested Cursors 247Chapter 11 Try It Yourself 252CHAPTER 12 Advanced Cursors 253LAB 12.1 Using Parameters with Cursors and Complex Nested Cursors 25412.1.1 Use Parameters in a Cursor 25512.1.2 Use Complex Nested Cursors 255LAB 12.2 FOR UPDATE and WHERE CURRENT Cursors 25812.2.1 For UPDATE and WHERE CURRENT Cursors 258CHAPTER 13 Triggers 263LAB 13.1 What Triggers Are 26413.1.1 Understand What a Trigger Is 27213.1.2 Use BEFORE and AFTER Triggers 274LAB 13.2 Types of Triggers 27713.2.1 Use Row and Statement Triggers 28313.2.2 Use INSTEAD OF Triggers 285Chaper 13 Try It Yourself 290CHAPTER 14 Compound Triggers 291LAB 14.1 Mutating Table Issues 29214.1.1 Understand Mutating Tables 296LAB 14.2 Compound Triggers 30014.2.1 Understand Compound Triggers 306Chapter 14 Try It Yourself 313CHAPTER 15 Collections 315LAB 15.1 PL/SQL Tables 31615.1.1 Use Associative Arrays 32615.1.2 Use Nested Tables 330LAB 15.2 Varrays 33415.2.1 Use Varrays 338LAB 15.3 Multilevel Collections 34215.3.1 Use Multilevel Collections 344Chapter 15 Try It Yourself 348CHAPTER 16 Records 349LAB 16.1 Record Types 35016.1.1 Use Table-Based and Cursor-Based Records 35816.1.2 Use User-Defined Records 362LAB 16.2 Nested Records 36716.2.1 Use Nested Records 369LAB 16.3 Collections of Records 37316.3.1 Use Collections of Records 374Chapter 16 Try It Yourself 378CHAPTER 17 Native Dynamic SQL 379LAB 17.1 EXECUTE IMMEDIATE Statements 38017.1.1 Use the EXECUTE IMMEDIATE Statement 387LAB 17.2 OPEN-FOR, FETCH, and CLOSE Statements 39217.2.1 Use OPEN-FOR, FETCH, and CLOSE Statements 395Chapter 17 Try It Yourself 401CHAPTER 18 Bulk SQL 403LAB 18.1 The FORALL Statement 40418.1.1 Use the FORALL Statement 413LAB 18.2 The BULK COLLECT Clause 42218.2.1 Use the BULK COLLECT Statement 428Chapter 18 Try It Yourself 437CHAPTER 19 Procedures 439LAB 19.1 Creating Procedures 44119.1.1 Create Procedures 44119.1.2 Query the Data Dictionary for Information on Procedures 443LAB 19.2 Passing Parameters into and out of Procedures 44419.2.1 Use IN and OUT Parameters with Procedures 445Chapter 19 Try It Yourself 447Part 1 447Part 2 447CHAPTER 20 Functions 449LAB 20.1 Creating and Using Functions 45020.1.1 Create Stored Functions 45120.1.2 Make Use of Functions 45220.1.3 Invoke Functions in SQL Statements 45320.1.4 Write Complex Functions 454Chapter 20 Try It Yourself 455CHAPTER 21 Packages 457LAB 21.1 The Benefits of Using Packages 45821.1.1 Create Package Specifications 46021.1.2 Create Package Bodies 46221.1.3 Call Stored Packages 46421.1.4 Create Private Objects 46521.1.5 Create Package Variables and Cursors 469LAB 21.2 Cursor Variables 47121.2.1 Make Use of Cursor Variables 475LAB 21.3 Extending the Package 48021.3.1 Extend the Package 480Chapter 21 Try It Yourself 493CHAPTER 22 Stored Code 495LAB 22.1 Gathering Information About Stored Code 49622.1.1 Get Stored Code Information from the Data Dictionary 49622.1.2 Enforce the Purity Level with the RESTRICT_REFERENCES Pragma 50022.1.3 Overload Modules 506Chapter 22 Try It Yourself 512CHAPTER 23 Object Types in Oracle 513LAB 23.1 Object Types 51423.1.1 Use Object Types 52223.1.2 Use Object Types with Collections 526LAB 23.2 Object Type Methods 53123.2.1 Use Object Type Methods 544Chapter 23 Try It Yourself 554CHAPTER 24 Oracle Supplied Packages 555LAB 24.1 Making Use of Oracle Supplied Packages to Profile PL/SQL, Access Files, and Schedule Jobs 55624.1.1 Access Files with UTL_FILE 56324.1.2 Schedule Jobs with DBMS_JOB 56324.1.3 Submit Jobs 564LAB 24.2 Making Use of Oracle-Supplied Packages to Generate an Explain Plan and Create HTML Pages 56824.2.1 Generate an Explain Plan with DBMS_XPLAN 572LAB 24.3 Creating Web Pages with the Oracle Web Toolkit 57824.3.1 Create an HTML Page with the Oracle Web Toolkit 594APPENDIX A PL/SQL Formatting Guide 597APPENDIX B Student Database Schema 601APPENDIX C ANSI SQL Standards 607APPENDIX D Answers to the Try It Yourself Sections 613INDEX 705