Oracle Database 12c PL/SQL Programming by McLaughlin, Michael -- Read -- Imperial Library of Trantor

Index

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

← Prev
Back
Next →

← Prev
Back
Next →