Oracle Database 12c PL/SQL Programming
1st Edition
Published on 24. January 2014
1404 pages
978-0-07-181244-3 (ISBN)
Description
Master Oracle Database 12c PL/SQL Application Development Develop, debug, and administer robust database programs. Filled with detailed examples and expert strategies from an Oracle ACE, Oracle Database 12c PL/SQL Programming explains how to retrieve and process data, write PL/SQL statements, execute effective queries, incorporate PHP and Java, and work with dynamic SQL. Code testing, security, and object-oriented programming techniques are fully covered in this comprehensive Oracle Press guide. Explore new SQL and PL/SQL features in Oracle Database 12c Build control structures, cursors, and loop statements Work with collections, varrays, tables, and associative array collections Locate and repair errors and employ exception handlers Execute black box, white box, and integration tests Configure and manage stored packages and libraries Handle security with authentication and encryption Use LOBs to store text and multimedia content Write and implement PL/SQL and Java triggers Extend functionality using dynamic SQL statements Understand object types, nested tables, and unnesting queries
More details
Other editions
Additional editions
Michael McLaughlin
Oracle Database 12c PL/SQL Programming
Book
02/2014
Osborne/McGraw-Hill
€83.40
Shipment within 15-20 days
Content
- Cover
- Title Page
- Copyright Page
- About the Author
- About the Contributing Author
- About the Technical Editor
- Contents at a Glance
- Contents
- Acknowledgments
- Introduction
- Book Outline
- Part I: PL/SQL Fundamentals
- Part II: PL/SQL Programming
- Part III: Appendixes and Glossary
- Lexicon
- SQL Lexicon
- PL/SQL Stored Programs
- Other Conventions
- Data Model and Source Code to Download
- Part I: Oracle PL/SQL
- Chapter 1: Oracle PL/SQL Development Overview
- PL/SQL's History and Background
- Oracle Development Architecture
- The Database
- The PL/SQL Language
- The Oracle Processing Architecture
- Two-Tier Model
- N-Tier Model
- Summary
- Mastery Check
- Chapter 2: New Features
- New SQL Features
- Data Catalog DIRECTORY Qualifies a LIBRARY Object
- Define Tables with Valid-Time (VT) Support
- Table with Explicit VT Columns
- Table with Implicit VT Columns
- Enhanced Oracle Native LEFT OUTER JOIN Syntax
- Default Values for Columns Based on Sequences
- Default Values for Explicit Null Insertion
- Identity Columns
- Increased Size Limits of String and Raw Types
- Pass Results from SQL Statements to External Programs
- Native SQL Support for Query Row Limits and Offsets
- Oracle Database Driver for MySQL Applications
- SQL CROSS APPLY, OUTER APPLY, and LATERAL
- Bequeath CURRENT_USER Views
- New PL/SQL Features
- Caching of Invoker Rights Functions
- Ability to White List PL/SQL Program Unit Callers
- Native Client API Support for PL/SQL Types
- New PL/SQL Package UTL_CALL_STACK
- DBMS_UTILITY Adds EXPAND_SQL_TEXT Subprogram
- DBMS_SQL Adds a New Formal Schema to the PARSE Procedure
- PL/SQL Functions in SQL WITH Clause
- PL/SQL-Specific Data Types Allowed in SQL
- Implicit REF CURSOR Parameter Binding
- Supporting Scripts
- Summary
- Mastery Check
- Chapter 3: PL/SQL Basics
- Block Structure
- Execution Block
- Basic Block Structure
- Declaration Block
- Exception Block
- Behavior of Variables in Blocks
- Anonymous Blocks
- Nested Anonymous Blocks
- Local Named Blocks
- Stored Named Blocks
- Basic Scalar and Composite Data Types
- Scalar Data Types
- Strings
- Dates
- Numbers
- Attribute and Table Anchoring
- Composite Data Types
- SQL UDT
- PL/SQL Record Type
- SQL Collection
- PL/SQL Collection
- Control Structures
- Conditional Structures
- If, Elsif, and Else Statements
- CASE Statement
- Iterative Structures
- For Loop Statements
- While Loop Statements
- Simple Loop Statements
- Exceptions
- User-Defined Exceptions
- Dynamic User-Defined Exceptions
- Bulk Operations
- Functions, Procedures, and Packages
- Functions
- Procedures
- Packages
- Transaction Scope
- Single Transaction Scope
- Multiple Transaction Scopes
- Database Triggers
- Summary
- Mastery Check
- Chapter 4: Language Fundamentals
- Lexical Units
- Delimiters
- Identifiers
- Reserved Words and Keywords
- Predefined Identifiers
- Quoted Identifiers
- User-Defined Variables, Subroutines, and User-Defined Data Types
- Literals
- Character Literals
- String Literals
- Numeric Literals
- Boolean Literals
- Date and Time Literals
- Comments
- Variables and Data Types
- Variable Data Types
- Scalar Data Types
- Boolean
- Characters and Strings
- Dates, Times, and Intervals
- Unicode Characters and Strings
- Numbers
- Large Objects (LOBs)
- BFILE Data Type
- BLOB Data Type
- CLOB Data Type
- NCLOB Data Type
- Composite Data Types
- Records
- Objects
- Collections
- System Reference Cursors
- Summary
- Mastery Check
- Chapter 5: Control Structures
- Conditional Statements
- IF Statements
- If-then-else Statements
- If-then-elsif-then-else Statements
- CASE Statements
- Simple CASE Statements
- Searched CASE Statements
- Conditional Compilation Statements
- Iterative Statements
- Simple Loop Statements
- Static Simple Loops
- Dynamic Simple Loops
- Skipping Iterations
- FOR Loop Statements
- Range FOR Loop Statements
- WHILE Loop Statements
- Cursor Structures
- Implicit Cursors
- Single-Row Implicit Cursors
- Multiple-Row Implicit Cursors
- Cursor FOR Loop Statements
- Explicit Cursors
- Static Explicit Cursors
- Dynamic Explicit Cursors
- Subcursors
- Bulk Statements
- BULK COLLECT INTO Statements
- Parallel Collection Targets
- Record Collection Targets
- LIMIT-Constrained Collection Targets
- FORALL Statements
- INSERT Statement
- UPDATE Statement
- DELETE Statement
- %BULK_EXCEPTION Handling
- Supporting Scripts
- Summary
- Mastery Check
- Chapter 6: Collections
- Introduction to Collections
- Object Types: Varray and Table Collections
- Varray Collections
- Table Collections
- Scalar Table Collections
- Composite Table Collections
- Associative Arrays
- Defining and Using Associative Arrays
- Associative Arrays of Scalar Data Types
- Associative Arrays of Composite Data Types
- Oracle Collection API
- COUNT Method
- DELETE Method
- EXISTS Method
- EXTEND Method
- FIRST Method
- LAST Method
- LIMIT Method
- NEXT Method
- PRIOR Method
- TRIM Method
- Supporting Scripts
- Summary
- Mastery Check
- Chapter 7: Error Management
- Exception Types and Scope
- Compilation Errors
- Runtime Errors
- Execution and Exception Block Errors
- Declaration Blck Errors
- Exception Management Built-in Functions
- User-Defined Exceptions
- Declaring User-Defined Exceptions
- Dynamic User-Defined Exceptions
- Exception Stack Functions
- Supporting Scripts
- Summary
- Mastery Check
- Part II: PL/SQL Programming
- Chapter 8: Functions and Procedures
- Function and Procedure Architecture
- Transaction Scope
- Calling Subroutines
- Positional Notation
- Named Notation
- Mixed Notation
- Exclusionary Notation
- SQL Call Notation
- Functions
- Function Model Choices
- Creation Options
- DETERMINISTIC Clause
- PARALLEL_ENABLE Clause
- PIPELINED Clause
- Object Table Functions
- RESULT_CACHE Clause
- Pass-by-Value Functions
- System Reference Cursor Functions
- Deterministic Pass-by-Value Functions
- Nondeterministic Pass-by-Value Functions
- DML-Enabled Pass-by-Value Functions
- Recursive Functions
- Pass-by-Reference Functions
- Procedures
- Pass-by-Value Procedures
- Pass-by-Reference Procedures
- Supporting Scripts
- Summary
- Mastery Check
- Chapter 9: Packages
- Package Architecture
- Package Specification
- Prototype Features
- Serially Reusable Precompiler Directive
- Variables
- Types
- Components: Functions and Procedures
- Package Body
- Prototype Features
- Variables
- Types
- Components: Functions and Procedures
- Definer vs. Invoker Rights Mechanics
- Managing Packages in the Database Catalog
- Finding, Validating, and Describing Packages
- Checking Dependencies
- Comparing Validation Methods: Timestamp vs. Signature
- Summary
- Mastery Check
- Chapter 10: Large Objects
- Working with Internally Stored LOB Types
- LOB Assignments Under 32K
- Small LOB Assignments
- LOB Assignments over 32K
- LOB Construction for Assignments Greater Than 32,767 Bytes
- Reading Files into Internally Stored Columns
- Reading Local Files into CLOB or NCLOB Columns
- Reading Local Files into BLOB Columns
- Working with LOBs Through Web Pages
- Procedures to Upload CLOB, NCLOB, or BLOB Columns
- HTML and PHP Components for CLOB and BLOB Uploads
- HTML and PHP Components for Displaying CLOB and BLOB Columns
- Working with Binary Files (BFILEs)
- Creating and Using Virtual Directories
- Reading Canonical Path Names and Filenames
- Understanding the DBMS_LOB Package
- Package Constants
- BLOB_DEDUPLICATE_REGION Record Structure
- CLOB_DEDUPLICATE_REGION Record Structure
- Package Exceptions
- Opening and Closing Methods
- CLOSE Procedure
- ISOPEN Function
- OPEN Procedure
- Manipulation Methods
- APPEND Procedure
- CONVERTTOBLOB Procedure
- CONVERTTOCLOB Procedure
- COPY Procedure
- ERASE Procedure
- FRAGMENT_DELETE Procedure
- FRAGMENT_INSERT Procedure
- FRAGMENT_MOVE Procedure
- FRAGMENT_REPLACE Procedure
- ISSECUREFILE Function
- LOADBLOBFROMFILE Procedure
- LOADCLOBFROMFILE Procedure
- LOADFROMFILE Procedure
- SETOPTIONS Procedure
- TRIM Procedure
- WRITE Procedure
- WRITEAPPEND Procedure
- Introspection Methods
- COMPARE Function
- GETCHUNKSIZE Function
- GET_DEDUPLICATED_REGIONS Procedure
- GETLENGTH Function
- GETOPTIONS Function
- GET_STORAGE_LIMIT Function
- INSTR Function
- READ Procedure
- SUBSTR Function
- BFILE Methods
- FILECLOSE Procedure
- FILECLOSEALL Procedure
- FILEEXISTS Function
- FILEGETNAME Procedure
- FILEISOPEN Function
- FILEOPEN Procedure
- Temporary LOB Methods
- CREATETEMPORARY Procedure
- ISTEMPORARY Function
- FREETEMPORARY Procedure
- Security Link Methods
- COPY_DBFS_LINK Procedure
- COPY_FROM_DBFS_LINK Procedure
- DBFS_LINK_GENERATE_PATH Function
- GETCONTENTTYPE Function
- GET_DBFS_LINK Function
- GET_DBFS_LINK_STATE Procedure
- SETCONTENTTYPE Procedure
- SET_DBFS_LINK Procedure
- Supporting Scripts
- The LONG to CLOB Script
- Manage LOBs from the File System
- Manage CLOB and BLOB LOBs Through the Web
- Manage BFILE LOBs Through the Web
- Summary
- Mastery Check
- Chapter 11: Object Types
- Object Basics
- Declaring Objects Types
- Implementing Object Bodies
- White Listing Object Types
- Getters and Setters
- Static Member Methods
- Comparing Objects
- Comparing with the MAP Member Function
- Comparing with the ORDER Member Function
- Inheritance and Polymorphism
- Declaring Subclasses
- Implementing Subclasses
- Type Evolution
- Implementing Object Type Collections
- Declaring Object Type Collections
- Implementing Object Type Collections
- Supporting Scripts
- Summary
- Mastery Check
- Chapter 12: Triggers
- Introduction to Triggers
- Database Trigger Architecture
- Data Definition Language Triggers
- Event Attribute Functions
- ORA_CLIENT_IP_ADDRESS
- ORA_DATABASE_NAME
- ORA_DES_ENCRYPTED_PASSWORD
- ORA_DICT_OBJ_NAME
- ORA_DICT_OBJ_NAME_LIST
- ORA_DICT_OBJ_OWNER
- ORA_DICT_OBJ_OWNER_LIST
- ORA_DICT_OBJ_TYPE
- ORA_GRANTEE
- ORA_INSTANCE_NUM
- ORA_IS_ALTER_COLUMN
- ORA_IS_DROP_COLUMN
- ORA_IS_SERVERERROR
- ORA_LOGIN_USER
- ORA_PARTITION_POS
- ORA_PRIVILEGE_LIST
- ORA_REVOKEE
- ORA_SERVER_ERROR
- ORA_SERVER_ERROR_DEPTH
- ORA_SERVER_ERROR_MSG
- ORA_SERVER_ERROR_NUM_PARAMS
- ORA_SERVER_ERROR_PARAM
- ORA_SQL_TXT
- ORA_SYSEVENT
- ORA_WITH_GRANT_OPTION
- SPACE_ERROR_INFO
- Building DDL Triggers
- Data Manipulation Language Triggers
- Statement-Level Triggers
- Row-Level Triggers
- Compound Triggers
- INSTEAD OF Triggers
- System and Database Event Triggers
- Trigger Restrictions
- Maximum Trigger Size
- SQL Statements
- LONG and LONG RAW Data Types
- Mutating Tables
- System Triggers
- Supporting Scripts
- Summary
- Mastery Check
- Chapter 13: Dynamic SQL
- Dynamic SQL Architecture
- Native Dynamic SQL (NDS)
- Dynamic Statements
- Dynamic DDL Statements
- Dynamic DML Statements
- Dynamic Statements with Inputs
- Dynamic Statements with Inputs and Outputs
- Dynamic Statements with an Unknown Number of Inputs
- DBMS_SQL Package
- Dynamic Statements
- Dynamic DDL Statements
- Dynamic DML Statements
- Dynamic Statements with Input Variables
- Dynamic Statements with Variable Inputs and Fixed Outputs
- Row-by-Row Statement Processing
- Bulk Statement Processing
- Dynamic Statements with Variable Inputs and Outputs
- DBMS_SQL Package Definition
- DBMS_SQL Constants
- DBMS_SQL Data Types
- DBMS_SQL Functions and Procedures
- Supporting Scripts
- Summary
- Mastery Check
- Part III: Appendixes and Glossary
- Appendix A: Oracle Database Primer
- Oracle Database Architecture
- Starting and Stopping the Oracle Database 12c Server
- Unix or Linux Operations
- Microsoft Windows Operations
- Starting and Stopping the Oracle Listener
- Multiversion Concurrency Control
- Data Transactions
- DML Locking and Isolation Control
- Definer Rights and Invoker Rights
- Definer Rights
- Invoker Rights
- SQL Interactive and Batch Processing
- SQL*Plus Command-Line Interface
- Connecting to and Disconnecting from SQL*Plus
- Working in the SQL*Plus Environment
- Writing SQL Statements with SQL*Plus
- Saving SQL Statements with SQL*Plus
- Editing SQL Statements with SQL*Plus
- Rerunning SQL*Plus SQL Statements from the Buffer
- Aborting Entry of SQL Statements in SQL*Plus
- Calling and Running SQL*Plus Script Files
- Passing Parameters to SQL*Plus Script Files
- Calling PL/SQL Programs
- Writing SQL*Plus Log Files
- Oracle SQL Developer Interface
- Launching Oracle SQL Developer
- Configuring Oracle SQL Developer
- Using Oracle SQL Developer
- Database Administration
- Provisioning Users
- Creating a CDB Oracle User
- Creating a PDB Oracle User
- Granting Security Privileges
- Granting Oracle Privileges
- Revoking Privileges
- Using Database Constraints
- NOT NULL Constraints
- UNIQUE Constraints
- Primary Key Constraints
- Foreign Key Constraints
- CHECK Constraints
- Security Hardening
- Categories of Attackers
- Oracle Audit Vault and Database Firewall
- Password Hardening
- Listener Hardening
- Data Governance
- Overcoming Resistance to Data Governance
- Implementing Data Governance
- Developing a Culture of Data Governance
- SQL Tuning
- EXPLAIN PLAN Statement
- DBMS_XPLAN Package
- SQL Tracing
- Tracing Session Statements
- Enable Tracing in the Current Session
- Disable Tracing in the Current Session
- Enable Tracing in Another Session
- Convert Raw Trace Files to Readable Trace Files
- Summary
- Appendix B: SQL Primer
- Oracle SQL Data Types
- Data Definition Language (DDL)
- CREATE Statement
- Users
- Granting Oracle Privileges
- Revoking Privileges
- Tables
- Sequences and Identity Columns
- Nested Collection Types
- Constraints
- Indexes
- Views
- Functions, Procedures, and Packages
- Object Types
- External Tables
- Partitioned Tables
- Synonyms
- ALTER Statement
- Users
- Tables
- Indexes
- Object Types
- RENAME Statement
- DROP Statement
- DROP USER Statement
- DROP TABLESPACE Statement
- DROP TABLE Statement
- DROP INDEX Statement
- DROP VIEW Statement
- DROP FUNCTION Statement
- DROP PROCEDURE Statement
- DROP PACKAGE Statement
- DROP TYPE Statement
- TRUNCATE Statement
- COMMENT Statement
- Data Manipulation Language (DML)
- ACID Compliant Transactions
- ACID Compliant INSERT Statements
- ACID Compliant UPDATE Statements
- ACID Compliant DELETE Statements
- INSERT Statement
- Insert by Values
- Multiple-Table Insert Statements
- UPDATE Statement
- Generic Update by Values and Queries
- Oracle Update by Values and Queries
- Update by Correlated Queries
- DELETE Statement
- Delete by Value Matches
- Delete Nested Table Row Elements
- Delete by Correlated Queries
- MERGE Statement
- Transaction Control Language (TCL)
- Queries: SELECT Statements
- Queries that Return Columns or Results from Columns
- Subqueries
- Inline Views
- Hierarchical Queries
- Down the Tree
- Up the Tree
- Restricting the Depth of Search
- Leaf Node Up
- Queries that Aggregate
- Aggregate Columns Only
- Aggregate and Nonaggregate Columns
- Queries that Return Columns or Results Selectively
- Oracle Proprietary DECODE Statement
- ANSI SQL CASE Operator
- Selective Aggregation
- Join Results
- Joins that Splice Together Rows
- Cross Join
- Inner Join
- Outer Join
- Joins that Splice Collections
- UNION
- UNION ALL
- INTERSECT
- MINUS
- Summary
- Appendix C: SQL Built-in Functions
- Character Functions
- ASCII Function
- ASCIISTR Function
- CHR Function
- CONCAT Function
- INITCAP Function
- INSTR Function
- LENGTH Function
- LOWER Function
- LPAD Function
- LTRIM Function
- REPLACE Function
- REVERSE Function
- RPAD Function
- RTRIM Function
- UPPER Function
- Data Type Conversion Functions
- CAST Function
- CONVERT Function
- TO_CHAR Function
- Converting a String to a Character String
- Converting a Date to a Character String
- Converting a Number to a Character String
- TO_CLOB Function
- TO_DATE Function
- TO_LOB Function
- TO_NCHAR Function
- TO_NCLOB Function
- TO_NUMBER Function
- Date-time Conversion Functions
- ADD_MONTHS Function
- CURRENT_DATE Function
- CURRENT_TIMESTAMP Function
- DBTIMEZONE Function
- EXTRACT Function
- FROM_TZ Function
- LAST_DAY Function
- LOCALTIMESTAMP Function
- MONTHS_BETWEEN Function
- NEW_TIME Function
- ROUND Function
- ROUND(date) Function
- ROUND(number) Function
- SYSDATE Function
- SYSTIMESTAMP Function
- TO_CHAR(date) Function
- TO_DSINTERVAL Function
- TO_TIMESTAMP Function
- TO_TIMESTAMP_TZ Function
- TO_YMINTERVAL Function
- TRUNC(date) Function
- TZ_OFFSET Function
- Collection Management Functions
- CARDINALITY Function
- COLLECT Function
- COLLECT Function in an Object Table
- COLLECT Function with an Object Type Column
- POWERMULTISET Function
- POWERMULTISET_BY_CARDINALITY Function
- SET Function
- Collection Set Operators
- CARDINALITY Operator
- EMPTY Operator
- MULTISET Operator
- MULTISET EXCEPT Operator
- MULTISET INTERSECT Operator
- MULTISET UNION Operator
- SET Operator
- SUBMULTISET OF Operator
- Number Functions
- CEIL Function
- FLOOR Function
- MOD Function
- POWER Function
- REMAINDER Function
- ROUND Function
- Error Reporting Functions
- SQLCODE Function
- SQLERRM Function
- Miscellaneous Functions
- BFILENAME Function
- COALESCE Function
- DECODE Function
- DUMP Function
- EMPTY_BLOB Function
- EMPTY_CLOB Function
- GREATEST Function
- LEAST Function
- NANVL Function
- NULLIF Function
- NVL Function
- SYS_CONTEXT Function
- TABLE Function
- TREAT Function
- USERENV Function
- VSIZE Function
- Summary
- Appendix D: PL/SQL Built-in Packages and Types
- Oracle Database 11g and 12c New Packages
- Examples of Package Use
- DBMS_APPLICATION_INFO Example
- DBMS_COMPARISON
- DBMS_CRYPTO
- DBMS_FGA
- Case Study: Query Tool
- Supporting Scripts
- Summary
- Appendix E: Regular Expression Primer
- Regular Expression Introduction
- Character Classes
- Collation Classes
- Metacharacters
- Metasequences
- Literals
- Regular Expression Implementation
- REGEXP_COUNT Function
- Title Case Count
- Lowercase Count
- Case-Insensitive Count
- REGEXP_INSTR Function
- REGEXP_LIKE Function
- REGEXP_REPLACE Function
- REGEXP_SUBSTR Function
- Supporting Scripts
- Summary
- Appendix F: Wrapping PL/SQL Code Primer
- Limitations of Wrapping PL/SQL
- Limitations of the PL/SQL wrap Utility
- Limitations of the DBMS_DDL.WRAP Function
- Using the wrap Command-Line Utility
- Using the DBMS_DDL Command-Line Utility
- WRAP Function
- CREATE_WRAPPED Procedure
- Summary
- Appendix G: PL/SQL Hierarchical Profiler Primer
- Configuring the Schema
- Collecting Profiler Data
- Understanding Profiler Data
- Reading the Raw Output
- Defining the PL/SQL Profiler Tables
- Querying the Analyzed Data
- Using the plshprof Command-Line Utility
- Supporting Scripts
- Summary
- Appendix H: PL/SQL Reserved Words and Keywords
- Summary
- Appendix I: Mastery Check Answers
- Chapter 1
- Chapter 2
- Chapter 3
- Chapter 4
- Chapter 5
- Chapter 6
- Chapter 7
- Chapter 8
- Chapter 9
- Chapter 10
- Chapter 11
- Chapter 12
- Chapter 13
- Glossary
- Index
