Sumário Itens Encontrados: 331Introduction 1Part I: Basic PL/SQL Concepts 7Chapter 1: PL/SQL and Your Database 9Chapter 2: The PL/SQL Environment 23Part II: Getting Started with PL/SQL 39Chapter 3: Laying the Groundwork: PL/SQL Fundamentals 41Chapter 4: Controlling Program Flow 85Chapter 5: Handling Exceptions 105Chapter 6: PL/SQL and SQL Working Together 127Part : Standards and Structures 163Chapter 7: Putting Your Code in the Right Place 165Chapter 8: Creating Naming Standards 187Chapter 9: Creating Coding Standards 201Part IV: PL/SQL Data Manipulations 219Chapter 10: Basic Datatypes 221Chapter 11: Advanced Datatypes 253Part V: Taking PL/SQL to the Next Level 289Chapter 12: Transaction Control 291Chapter 13: Dynamic SQL and PL/SQL 313Chapter 14: PL/SQL Best Practices 335Part VI: The Part of Tens 355Chapter 15: Ten PL/SQL Tips 357Chapter 16: Ten Common Mistakes to Avoid in PL/SQL 377Index 397About This Book 1Foolish Assumptions 2How This Book Is Organized 2Part I: Basic PL/SQL Concepts 2Part II: Getting Started with PL/SQL 3Part : Standards and Structures 3Part IV: PL/SQL Data Manipulations 3Part V: Taking PL/SQL to the Next Level 3Part VI: The Part of Tens 4Icons Used in This Book 4Where to Go from Here 4Chapter 1: PL/SQL and Your Database 9Knowing Just Enough about Relational Databases 9What makes a database ârelationalâ? 10Understanding basic database terminology 12Introducing database normalization 13What is a DBMS? 16The Scoop on SQL and PL/SQL 16The purpose of SQL and PL/SQL 17The difference between SQL and PL/SQL 18Whatâs new in Oracle SQL and PL/SQL? 18What Is PL/SQL Good For? 19Using database triggers 19Scripting with speed 20Keeping code server-side 20Programming for Oracle Developer 21Chapter 2: The PL/SQL Environment 23Setting Up an Oracle Environment 23Hardware and Software Requirements 25Accessing the Oracle Technology Network 26Installing the Database 27Working with Code 28SQL*Plus 28Oracle SQL Developer 30Third-party tools 30Establishing a Database Connection 31Checking the operating services 32Connecting with your username 32Unlocking (or locking) accounts 33Resetting passwords 33Setting up the server to communicate 34The Basics of PL/SQL Programs 34Writing a simple program 34Creating stored procedures 36Passing parameters to procedures 36Examining the Sample Data 37The Scott/Tiger schema 37The Human Resources (HR) and Order Entry (OE) schemas 38Part II: Getting Started with PL/SQL 39Chapter 3: Laying the Groundwork: PL/SQL Fundamentals 41PL/SQL As a Programming Language 41Anonymous PL/SQL Blocks 42Introducing the Lexical Set of Elements 43Identifiers 44Delimiters 44Literals 45Comments 45Working with Constants and Variables 46Declaring variables 46Assigning values to variables 48Literals as variable values 49Understanding the scope of variables 51Building Expressions with Operators 53Running Anonymous Blocks of Code 56Identifying common mistakes 56Spotting compilation errors 57Recognizing semicolon-related errors 57Creating Reusable PL/SQL Code 59Wrapping a task into a procedure 59Returning values with functions 61Parameters of subprograms 63Storing PL/SQL in the Database 67Database procedures and functions 68Packages 69Triggers 71Interpreting and fixing compilation errors 73x Oracle PL/SQL For Dummies Checking Out PL/SQL Extras 76Overloading calls 76Resolving calls to subprograms 78Recursion 80Compiler hints and directives 82Built-in packages 83Chapter 4: Controlling Program Flow 85Creating Condition Statements 85IF THEN statements 86IF ELSE statements 87CASE statements 89Comparing with NULL 91Handling conditions 95Looping the Loop 97Simple loops 97Nested loops 99WHILE loop 100FOR loop 102Chapter 5: Handling Exceptions 105Understanding Exception Basics 106Adding Exception Handlers to Your Code 107Understanding Different Exception Types 110Predefined Exceptions in PL/SQL Code 111Adding User-Defined Exceptions 114Assigning a code to a user-defined exception 115Including error messages in user-defined exceptions 116Propagation of Exceptions 118Seeing propagation of exceptions in action 118Handling exceptions without halting the program 122Avoiding exceptions raised in declaration part and exception handler 124Writing Exceptional Exceptions 126Cursors: What They Are and How to Use Them 128Returning more than one piece of information 129Looping through multiple records 132Placing cursors in nested loops 133Passing parameters to cursors 134Knowing Where to Declare Cursors 137Defining cursors in the header of the program unit 138Defining cursors in the local PL/SQL block 138Defining cursors in the package body 139Defining cursors in the package spec 140Being Explicitly Smart with Implicit Cursors 142Retrieving a single row: The basic syntax 142Handling exceptions in implicit cursors 143Returning an implicit cursor into a record 143Accessing Status Info by Using Cursor Variables 144Checking the status of explicit cursors 145Checking the status of implicit cursors 146Updating Records Fetched from Cursors 148Using a simple UPDATE statement 148Updating with logical operators 148Taking a Shortcut with CURSOR FOR Loops 150Comparing CURSOR FOR loops to cursors with the LOOP command 150When do CURSOR FOR loops simplify exception handling? 152When CURSOR FOR loops make your life harder 153Knowing what record is processing 155Referencing Functions in SQL 155Important facts to remember 157Getting good performance with functions 160Chapter 7: Putting Your Code in the Right Place 165Putting Code in the Database 165Managing code 166Packaging code in the database 166Placing triggers on tables 174Building INSTEAD OF trigger views 177Advantages of putting code in the database 182Disadvantages of putting code in the database 182Putting Code in the Application Server (Middle-Tier Approach) 183Advantages of the middle-tier approach 184Disadvantages of the middle-tier approach 184Placing code in the view layer 185Where Should You Place the Business Logic? 185Chapter 8: Creating Naming Standards 187Whatâs in a Naming Standard? 187Oracle database influences 188Java influences 188Modern application development tools and their influences 189Setting Naming Standards for Common Code Elements 189Basic objects 190Variables 191Program units: Procedures, packages, functions, triggers 193Parameters in code objects 194Exceptions 196User-defined datatypes 196Collections 197Filenames 198Making Sure Your Organization Follows Standards 199Chapter 9: Creating Coding Standards 201Why Standards Are Important 201Universal Truths 202Donât hard-code any constant value 202Donât make your program units too big or too small 205Put each data element on its own line 206Too many comments are much better thantoo few comments 207Avoid global variables 210Indent carefully 210Be careful with capitalization 211Use generic variable datatype declarations 211Limit line length 213Use explicit data conversion for dates 213Use synonyms 213Developing SQL Code Consistently 214Using a new line 214Using explicit column lists 214Prefixing (and suffixing) column names from multiple tables 215Giving columns aliases 216Using parentheses in complex mathematical and logical expressions 216Using white space intelligently 217Writing save exception handlers 217Packaging stored program units 217Part IV: PL/SQL Data Manipulations 219Chapter 10: Basic Datatypes 221Introducing the Main Datatype Groups 221Working with Numeric Datatypes 222Using the NUMBER datatype 222Boosting performance with BINARY_INTEGER 225Using BINARY_FLOAT and BINARY_DOUBLEfor complex calculations 226Handling numeric datatypes in built-in functions 228Keeping Track of Date and Time 229Selecting the info you want from DATE 229Using TIMESTAMP 234Using TIMESTAMP WITH TIME ZONE 235Storing elapsed time with INTERVAL 236Working with dates and built-in functions 237Storing Logical Values with BOOLEAN 241Processing Characters and Strings 242Limiting variable length with CHAR versus VARCHAR2 242Useful character built-in functions 244Extending your options with regular expressions 250Chapter 11: Advanced Datatypes 253Handling Large Objects in the Database 253Using internal large objects (CLOB, BLOB) 254Creating pointers with external large objects 254Working with Large Objects 255Populating BFILE 255Loading data to the CLOB by using BFILE 256Loading a page to a BLOB 257Performing basic string operations on CLOBs 258Keeping Code Consistent with User-Defined Subtypes 259Defining Your Own Datatypes 260Records 261Object types 267Grouping Sets of Data into Collections 271Using VARRAYs 272Nesting variable data sets in tables 275Associative arrays (index-by tables) 280Speeding Up Data Collection with Bulk Operations 283Using the BULK COLLECT command 284Adding a limit to BULK COLLECT 286Part V: Taking PL/SQL to the Next Level 289Chapter 12: Transaction Control 291Using Transactions to Maintain Data Consistency 292Committing or Rolling Back Changes to the Database 293Firing explicit and implicit commits 294Rolling back changes 294Knowing when to use implicit rollbacks 297Resolving deadlocks 298xiv Oracle PL/SQL For DummiesAutonomous Transactions 298Setting up the syntax for an autonomous transaction 299Handling auditing and security with autonomous transactions 300Autonomous transaction features 302Applying autonomous transactions to other real-world situations 308Chapter 13: Dynamic SQL and PL/SQL 313Taking Advantage of Dynamic SQL 313A Simple EXECUTE IMMEDIATE 314Building SQL on the Fly 316Improve performance with bind variables 317Return information using cursor variables 324Building DDL on the Fly 325Using Quoted Strings with Dynamic SQL 327Working with Advanced Datatypes 328Using BULK COLLECT with dynamic SQL 329Dynamic OPEN FOR 330Chapter 14: PL/SQL Best Practices 335Why Are Best Practices Important? 335Laying the Groundwork for Good Coding 336Understanding the big picture 336Communicating effectively 337Creating a code specification 337Writing Code with Best Practices in Mind 338Stub out your code 338Check the architecture as you go 339Prove code works with test cases 340Use code libraries 340Keep the code maintainable 341Donât forget about performance 341Compile as you go 341Debug the timesaving way 342Testing Your Code 343What are you testing? 345Creating a testing architecture 345Performance and load testing 345Tuning performance 346âGood enough is bestâ 348Coding the Agile Way 349Working together in Agile teams 349Programming in pairs 350Delivering code quickly 350Test first 351Keeping Up-to-Date with Oracle 352Conventional wisdom isnât always right 352Buy books 353Go to conferences 353Join your local Oracle user group 354Use online resources 354Part VI: The Part of Tens 355Chapter 15: Ten PL/SQL Tips 357Use SELECT INTO Correctly 357Donât Assume Column Order in DML Statements 360Use the NOCOPY Command 362Be Careful of Function Calls That Impact Performance 364Beware of Implicit Commits 366Use Only One RETURN Statement per Function 369Save Debugging Time with WHEN OTHERS 370Know When to Pass Parameters or Use Global Variables 372Use Implicit Cursors When Appropriate 373Remember That Dates Are Numbers 375Chapter 16: Ten Common Mistakes to Avoid in PL/SQL 377Catching an Exception with WHEN OTHERS THEN NULL 377Forgetting to Handle NULL Values 379Creating Unintended Boolean Expressions 380Forgetting to Close an Explicit Cursor 382Starting Endless Loops 384Reinventing the Wheel 386Converting Datatypes Implicitly 388Cutting and Pasting Code 391Ignoring Code Readability 393Assuming Code Doesnât Need Comments 395