Home Page Icon
Home Page
Table of Contents for
Title
Close
Title
by Donald J. Bales
Beginning Oracle PL/SQL, Second Edition
Cover
Title
Copyright
Dedication
Contents at a Glance
About IOUG Press
Contents
About the Author
About the Technical Reviewer
Acknowledgments
Foreword
Introduction (The Big Picture)
Chapter 1: Relational SQL
Tables
An Entity-Relationship Diagram
Data Definition Language (DDL)
It’s Your Turn to Create a Table
Indexes
DDL Again
It’s Your Turn to Create an Index
Constraints
Column Constraints
Table Constraints
It’s Your Turn to Create a Constraint
Triggers
Views
Insert
Insert . . .Values
It’s Your Turn to Insert with Values
Insert . . . Select
It’s Your Turn to Insert with Select
Update
Fix a Mistake with Update
It’s Your Turn to Update
Update and Subqueries
Delete
A Change in Order
It’s Your Turn to Delete
Select
Joins
It’s Your Turn to Select
Your Working Example
Your Example Narrative
Your Example ERD
Create a Code Table
It’s Your Turn to Create Code Tables
Create a Content Table
It’s Your Turn to Create Content Tables
Create an Intersection Table
It’s Your Turn to Create Intersection Tables
Summary
Chapter 2: Blocks, Functions, and Procedures
Blocks
Anonymous Blocks
Exceptions
Common Exceptions
Catching an Exception
Functions
Create a Function
It’s Your Turn to Create a Function
Procedures
Create a Procedure
It’s Your Turn to Create a Procedure
Nested Blocks
An Example of Nesting Blocks
Rules for Nesting
Packages
Create a Package Specification
It’s Your Turn to Create a Package Specification
Create a Package Body
It’s Your Turn to Create a Package Body
Benefits of Using Packages
Summary
Chapter 3: Types, Variables, and Scope
PL/SQL Data Types
Variables
Variable Naming
Variable Declarations
Variable Anchors
Variable Assignments
NULL Value
It’s Your Turn to Declare Variables
Scope
Scope Rules
It’s Your Turn to Scope Things Out
Types
Table Types
Record Types
Multidimensional Arrays
Parameters
Parameter Naming
Parameter Declarations
Parameter Scope
It’s Your Turn to Declare Parameters
Summary
Chapter 4: Single Row Processing
Inserts
Catching a DUP_VAL_ON_INDEX Exception
Using PL/SQL to Predetect a Duplicate
IF I Don’t Tell You Now, When ELSE Can I?
Using SQL to Predetect a Duplicate
It’s Your Turn to Insert
Updates
Updating a Row
Using SQL to Perform Complex Updates
Deletes
Selects
No Data Found
Too Many Rows
It’s Your Turn to Select
Summary
Chapter 5: Multirow Processing
Cursors
Cursor Declarations
Fetching Rows from a Cursor Manually
Cursor Records
A Singleton Cursor
It’s Your Turn to Fetch Manually
Fetching Rows from a Cursor Automatically
It’s Your Turn to Fetch Automatically
Bulk Collect
Bulk Collect with a Cursor
Bulk Collect with a Select Statement
It’s Your Turn to Bulk Collect
FORALL
Summary
Chapter 6: Object-Relational SQL
Information Modeling
Object Orientation
Key Tenets
Reuse
Service Orientation
A Roadmap to Reality
Step 1: Table Packages
Step 2: User-Defined Types
Step 3: Object Views
Step 4: Object Tables
A Change in Order
Object Types
Create a User-Defined Type Specification
Create a User-Defined Type Implementation
It’s Your Turn to Create a User-Defined Type
Object Views
Create an Object View
It’s Your Turn to Create an Object View
Object Tables
Create an Object Table
It’s Your Turn to Create an Object Table
Impedance Mismatch?
Nested Types and Collections
It’s Your Turn to Prove There’s No Impedance Mismatch
Summary
Chapter 7: Troubleshooting
Prevention
Anchors
Data Type Prefixes
Explicit Conversions
Preparation
Blocking
Bread Crumbs
After the Fact
Success Messages
Failure Messages
It’s Your Turn to Use put_line( )
As It Happens
A DEBUG Table
A DEBUGGER Package
It’s Your Turn to Use Debug Logging
One Step at a Time
Debugging with Oracle SQL Developer
Debugging Anonymous PL/SQL
Debugging with TOAD for Oracle
Debugging with Visual Studio
It’s Your Turn to Use a Debugger
Profiling PL/SQL
Profiler’s Tables
Profiler’s Methods
Profiling Reports
It’s Your Turn to Profile
Profiling SQL
Explain Plan
Physics vs. Explain Plan
It’s Your Turn to Use Explain Plan
TKPROF
Summary
Chapter 8: Testing
SQL Test Patterns
PL/SQL Test Patterns
A Testing Tool
A TEST Table
A TEST Package Specification
A TEST Package Body
It’s Your Turn to Create a Testing Tool
Testing
Testing a Code Table Package
It’s Your Turn to Test a Code Table Package
Testing a Content Table Package
It’s Your Turn to Test a Content Table Package
Testing an Intersection Table Package
It’s Your Turn to Test an Intersection Table Package
Testing a Type
It’s Your Turn to Test a Type
Automating Testing
Automate Test Processing
It’s Your Turn to Automate Test Processing
Summary
Chapter 9: Documenting
Indestructible Documentation
SQL*Plus Documentation Tools
GUI Development Environment Tools
Rules for Documentation Comments
Documentation on Demand
A Text-Based Documentation Formatting Tool
Accessing Documentation on Demand
It’s Your Turn to Access Documentation on Demand
Distributable Documentation
An HTML-Based Documentation Formatting Tool
Generating Distributable Documentation
It’s Your Turn to Generate Distributable Documentation
Documentation Distribution
Summary
Chapter 10: Examples
Polymorphic Commands
Parametric SQL
Code Table Methods
Content Table Methods
Intersection Table Methods
Hierarchical Table Methods
The Black Box
Table Methods
Universal Methods
Divide and Conquer
Data Migration
On-Demand Data Processing
Polling Data Processing
Interfacing
Reporting
Summary
Appendix A: How to Download, Install, and Use Oracle
How to Download Oracle Database Software
How to Install Oracle Database Software
How to Use SQL*Plus
How to Download This Book’s Source Code
How to Create a New Username
How to Write a SQL*Plus Script
How to Execute a SQL*Plus Script
How to Describe Your Tables and Stored Procedures
Index
General
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Prev
Previous Chapter
Cover
Next
Next Chapter
Copyright
Beginning Oracle PL/SQL
Second Edition
Donald J. Bales
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset