Sumário Itens Encontrados: 467PART IOracle PL/SQL1 Oracle PL/SQL Development Overview 32 New Features 173 PL/SQL Basics 434 Language Fundamentals 1115 Control Structures 1536 Collections 2177 Error Management 261PART IIPL/SQL Programming8 Functions and Procedures 2939 Packages 34710 Large Objects 38511 Object Types 44912 Triggers 49113 Dynamic SQL 545PART IIIAppendixes and GlossaryA Oracle Database Primer 595B SQL Primer 695C SQL Built-in Functions 893D PL/SQL Built-in Packages and Types 965E Regular Expression Primer 999F Wrapping PL/SQL Code Primer 1019G PL/SQL Hierarchical Profiler Primer 1029H PL/SQL Reserved Words and Keywords 1045I Mastery Check Answers 1055Glossary 1085Index 1101PL/SQLâs History and Background 4Oracle Development Architecture 6The Database 7The PL/SQL Language 9The Oracle Processing Architecture 12Two-Tier Model 13N-Tier Model 13Summary 15Mastery Check 15New SQL Features 18Data Catalog DIRECTORY Qualifies a LIBRARY Object 19Define Tables with Valid-Time (VT) Support 19Enhanced Oracle Native LEFT OUTER JOIN Syntax 20Default Values for Columns Based on Sequences 20Default Values for Explicit Null Insertion 22Identity Columns 23Increased Size Limits of String and Raw Types 24Pass Results from SQL Statements to External Programs 24Native SQL Support for Query Row Limits and Offsets 26Oracle Database Driver for MySQL Applications 29SQL CROSS APPLY, OUTER APPLY, and LATERAL 29Bequeath CURRENT_USER Views 31New PL/SQL Features 32Caching of Invoker Rights Functions 32Ability to White List PL/SQL Program Unit Callers 32Native Client API Support for PL/SQL Types 34New PL/SQL Package UTL_CALL_STACK 34DBMS_UTILITY Adds EXPAND_SQL_TEXT Subprogram 34DBMS_SQL Adds a New Formal Schemato the PARSE Procedure 35PL/SQL Functions in SQL WITH Clause 35PL/SQL-Specific Data Types Allowed in SQL 37Implicit REF CURSOR Parameter Binding 40Supporting Scripts 40Summary 41Mastery Check 41Block Structure 44Execution Block 44Basic Block Structure 45Declaration Block 48Exception Block 49Behavior of Variables in Blocks 50Anonymous Blocks 50Nested Anonymous Blocks 55Local Named Blocks 57Stored Named Blocks 60Basic Scalar and Composite Data Types 63Scalar Data Types 63Attribute and Table Anchoring 65Composite Data Types 68Control Structures 81Conditional Structures 81Iterative Structures 83Exceptions 92User-Defined Exceptions 93Dynamic User-Defined Exceptions 94Bulk Operations 95Functions, Procedures, and Packages 97Functions 97Procedures 99Packages 100Transaction Scope 106Single Transaction Scope 106Multiple Transaction Scopes 107Database Triggers 108Summary 109Mastery Check 109Lexical Units 112Delimiters 112Identifiers 118Literals 119Comments 121Variables and Data Types 122Variable Data Types 123Scalar Data Types 126Large Objects (LOBs) 142Composite Data Types 144System Reference Cursors 147Summary 150Mastery Check 150Conditional Statements 154IF Statements 162CASE Statements 166Conditional Compilation Statements 169Iterative Statements 172Simple Loop Statements 172FOR Loop Statements 179WHILE Loop Statements 181Cursor Structures 185Implicit Cursors 185Explicit Cursors 190Bulk Statements 203BULK COLLECT INTO Statements 203FORALL Statements 208Supporting Scripts 213Summary 214Mastery Check 214Introduction to Collections 218Object Types: Varray and Table Collections 221Varray Collections 221Table Collections 225Associative Arrays 240Defining and Using Associative Arrays 241Oracle Collection API 247COUNT Method 250DELETE Method 251EXISTS Method 252EXTEND Method 253FIRST Method 254LAST Method 255LIMIT Method 255NEXT Method 256PRIOR Method 256TRIM Method 257Supporting Scripts 259Summary 259Mastery Check 259Exception Types and Scope 262Compilation Errors 263Runtime Errors 266Exception Management Built-in Functions 274User-Defined Exceptions 276Declaring User-Defined Exceptions 276Dynamic User-Defined Exceptions 278Exception Stack Functions 281Supporting Scripts 287Summary 287Mastery Check 288Function and Procedure Architecture 295Transaction Scope 302Calling Subroutines 303Positional Notation 304Named Notation 304Mixed Notation 304Exclusionary Notation 304SQL Call Notation 305Functions 306Function Model Choices 307Creation Options 308Pass-by-Value Functions 322Pass-by-Reference Functions 336Procedures 338Pass-by-Value Procedures 339Pass-by-Reference Procedures 342Supporting Scripts 345Summary 345Mastery Check 345Package Architecture 348Package Specification 354Prototype Features 355Serially Reusable Precompiler Directive 358Variables 359Types 361Components: Functions and Procedures 364Package Body 365Prototype Features 366Variables 368Types 371Components: Functions and Procedures 371Definer vs Invoker Rights Mechanics 375Managing Packages in the Database Catalog 378Finding, Validating, and Describing Packages 379Checking Dependencies 380Comparing Validation Methods: Timestamp vs Signature 381Summary 382Mastery Check 382Working with Internally Stored LOB Types 387LOB Assignments Under 32K 387LOB Assignments over 32K 389Reading Files into Internally Stored Columns 398Reading Local Files into CLOB or NCLOB Columns 399Reading Local Files into BLOB Columns 402Working with LOBs Through Web Pages 404Working with Binary Files (BFILEs) 413Creating and Using Virtual Directories 413Reading Canonical Path Names and Filenames 419Understanding the DBMS_LOB Package 427Package Constants 427Package Exceptions 428Opening and Closing Methods 429Manipulation Methods 430Introspection Methods 436BFILE Methods 440Temporary LOB Methods 441Security Link Methods 442Supporting Scripts 446The LONG to CLOB Script 446Manage LOBs from the File System 446Manage CLOB and BLOB LOBs Through the Web 446Manage BFILE LOBs Through the Web 446Summary 446Mastery Check 447Object Basics 453Declaring Objects Types 453Implementing Object Bodies 456White Listing Object Types 461Getters and Setters 463Static Member Methods 465Comparing Objects 467Inheritance and Polymorphism 475Declaring Subclasses 477Implementing Subclasses 478Type Evolution 481Implementing Object Type Collections 483Declaring Object Type Collections 483Supporting Scripts 487Summary 487Mastery Check 488Introduction to Triggers 492Database Trigger Architecture 495Data Definition Language Triggers 499Event Attribute Functions 501Building DDL Triggers 512Data Manipulation Language Triggers 515Statement-Level Triggers 516Row-Level Triggers 518Compound Triggers 527INSTEAD OF Triggers 532System and Database Event Triggers 536Trigger Restrictions 538Maximum Trigger Size 538SQL Statements 538LONG and LONG RAW Data Types 539Mutating Tables 539System Triggers 540Supporting Scripts 541Summary 541Mastery Check 541Dynamic SQL Architecture 547Native Dynamic SQL (NDS) 547Dynamic Statements 548Dynamic Statements with Inputs 550Dynamic Statements with Inputs and Outputs 554Dynamic Statements with an Unknown Number of Inputs 558DBMS_SQL Package 560Dynamic Statements 561Dynamic Statements with Input Variables 564Dynamic Statements with Variable Inputs and Fixed Outputs 566Dynamic Statements with Variable Inputs and Outputs 571DBMS_SQL Package Definition 576Supporting Scripts 591Summary 591Mastery Check 591Oracle Database Architecture 596Starting and Stopping theOracle Database 12c Server 603Unix or Linux Operations 604Microsoft Windows Operations 609Starting and Stopping the Oracle Listener 610Multiversion Concurrency Control 615Data Transactions 616DML Locking and Isolation Control 619Definer Rights and Invoker Rights 620Definer Rights 620Invoker Rights 621SQL Interactive and Batch Processing 622SQL*Plus Command-Line Interface 622Oracle SQL Developer Interface 644Database Administration 652Provisioning Users 652Using Database Constraints 661Security Hardening 670Data Governance 681SQL Tuning 684EXPLAIN PLAN Statement 685DBMS_XPLAN Package 686SQL Tracing 690Tracing Session Statements 691Convert Raw Trace Files to Readable Trace Files 693Summary 694Oracle SQL Data Types 699Data Definition Language (DDL) 703CREATE Statement 704ALTER Statement 773RENAME Statement 791DROP Statement 792TRUNCATE Statement 794COMMENT Statement 795Data Manipulation Language (DML) 795ACID Compliant Transactions 795INSERT Statement 799UPDATE Statement 815DELETE Statement 829MERGE Statement 834Transaction Control Language (TCL) 841Queries: SELECT Statements 843Queries that Return Columns or Results from Columns 845Queries that Aggregate 861Queries that Return Columns or Results Selectively 866Join Results 876Joins that Splice Together Rows 878Joins that Splice Collections 888Summary 891Character Functions 894ASCII Function 894ASCIISTR Function 895CHR Function 895CONCAT Function 896INITCAP Function 896INSTR Function 897LENGTH Function 897LOWER Function 898LPAD Function 898LTRIM Function 899REPLACE Function 899REVERSE Function 900RPAD Function 900RTRIM Function 901UPPER Function 901Data Type Conversion Functions 902CAST Function 902CONVERT Function 904TO_CHAR Function 905TO_CLOB Function 907TO_DATE Function 907TO_LOB Function 908TO_NCHAR Function 910TO_NCLOB Function 910TO_NUMBER Function 910Date-time Conversion Functions 911ADD_MONTHS Function 911CURRENT_DATE Function 911CURRENT_TIMESTAMP Function 912DBTIMEZONE Function 912EXTRACT Function 912FROM_TZ Function 913LAST_DAY Function 913LOCALTIMESTAMP Function 914MONTHS_BETWEEN Function 914NEW_TIME Function 915ROUND Function 916SYSDATE Function 916SYSTIMESTAMP Function 917TO_CHAR(date) Function 917TO_DSINTERVAL Function 918TO_TIMESTAMP Function 918TO_TIMESTAMP_TZ Function 919TO_YMINTERVAL Function 919TRUNC(date) Function 920TZ_OFFSET Function 920Collection Management Functions 921CARDINALITY Function 921COLLECT Function 921POWERMULTISET Function 925POWERMULTISET_BY_CARDINALITY Function 926SET Function 926Collection Set Operators 926CARDINALITY Operator 928EMPTY Operator 929MULTISET Operator 929MULTISET EXCEPT Operator 930MULTISET INTERSECT Operator 930MULTISET UNION Operator 931SET Operator 932SUBMULTISET OF Operator 933Number Functions 933CEIL Function 933FLOOR Function 934MOD Function 934POWER Function 936REMAINDER Function 937ROUND Function 938Error Reporting Functions 938SQLCODE Function 938SQLERRM Function 939Miscellaneous Functions 940BFILENAME Function 941COALESCE Function 943DECODE Function 944DUMP Function 945EMPTY_BLOB Function 945EMPTY_CLOB Function 948GREATEST Function 949LEAST Function 951NANVL Function 953NULLIF Function 953NVL Function 954SYS_CONTEXT Function 954TABLE Function 958TREAT Function 960USERENV Function 961VSIZE Function 963Summary 963Oracle Database 11g and 12c New Packages 966Examples of Package Use 974DBMS_APPLICATION_INFO Example 974DBMS_COMPARISON 979DBMS_CRYPTO 986DBMS_FGA 990Case Study: Query Tool 991Supporting Scripts 997Summary 997Regular Expression Introduction 1000Character Classes 1000Collation Classes 1003Metacharacters 1004Metasequences 1006Literals 1007Regular Expression Implementation 1007REGEXP_COUNT Function 1007REGEXP_INSTR Function 1011REGEXP_LIKE Function 1013REGEXP_REPLACE Function 1014REGEXP_SUBSTR Function 1015Supporting Scripts 1017Summary 1017Limitations of Wrapping PL/SQL 1020Limitations of the PL/SQL wrap Utility 1021Limitations of the DBMS_DDL.WRAP Function 1021Using the wrap Command-Line Utility 1021Using the DBMS_DDL Command-Line Utility 1021WRAP Function 1022CREATE_WRAPPED Procedure 1026Summary 1028Configuring the Schema 1030Collecting Profiler Data 1032Understanding Profiler Data 1035Reading the Raw Output 1035Defining the PL/SQL Profiler Tables 1037Querying the Analyzed Data 1039Using the plshprof Command-Line Utility 1040Supporting Scripts 1043Summary 1043Summary 1053Chapter 1 1056Chapter 2 1058Chapter 3 1060Chapter 4 1063Chapter 5 1065Chapter 6 1067Chapter 7 1069Chapter 8 1071Chapter 9 1074Chapter 10 1076Chapter 11 1078Chapter 12 1080Chapter 13 1082