| |
| |
| Preface | |
| |
| |
| |
| Introduction | |
| |
| |
| |
| The Logical and Physical View of Tables | |
| |
| |
| |
| Database Types | |
| |
| |
| |
| NULLs | |
| |
| |
| |
| DDL and DML Statements | |
| |
| |
| |
| Column and Table Constraint Clauses | |
| |
| |
| |
| Sample Database | |
| |
| |
| |
| A Quick Tour of SQL*Plus | |
| |
| |
| |
| What SQL*Plus? | |
| |
| |
| |
| Invoking SQL*Plus | |
| |
| |
| |
| Connecting with SQL*Plus | |
| |
| |
| |
| SQL*Plus Editing/Basic Commands | |
| |
| |
| |
| SQL*Plus Scripts | |
| |
| |
| |
| Position-Dependent Parameters | |
| |
| |
| |
| Named Parameter Substitution | |
| |
| |
| |
| Running Script from a Host User | |
| |
| |
| |
| Select Statements | |
| |
| |
| |
| Qualifying Columns | |
| |
| |
| |
| Qualifying Rows | |
| |
| |
| |
| Joins | |
| |
| |
| |
| Table Joins with Nulls | |
| |
| |
| |
| Full Outer Join | |
| |
| |
| |
| SQL Tutorial | |
| |
| |
| |
| Tutorial Solutions | |
| |
| |
| |
| Declarative Constraints | |
| |
| |
| |
| Introduction | |
| |
| |
| |
| Primary Key | |
| |
| |
| |
| Primary Key Versus Unique | |
| |
| |
| |
| Concatenated Primary Key | |
| |
| |
| |
| Declaring an Additional Not Null Constraint | |
| |
| |
| |
| Error Messages | |
| |
| |
| |
| Naming Primary Keys | |
| |
| |
| |
| PL/SQL Coding with a Primary Key | |
| |
| |
| |
| SQL*Loader--Direct Load | |
| |
| |
| |
| Unique Constraints | |
| |
| |
| |
| Concatenated UNIQUE Constraints | |
| |
| |
| |
| PL/SQL Coding with a UNIQUE Constraint | |
| |
| |
| |
| Not Null Constraints | |
| |
| |
| |
| Error Messages | |
| |
| |
| |
| Naming Not Null Constraints | |
| |
| |
| |
| PL/SQL Coding with Not Null Constraints | |
| |
| |
| |
| CHECK Constraints | |
| |
| |
| |
| Multiple Constraints | |
| |
| |
| |
| Case Sensitivity | |
| |
| |
| |
| Multicolumn Constraints | |
| |
| |
| |
| Error Messages | |
| |
| |
| |
| Boolean Types as a Check Constraint | |
| |
| |
| |
| Complex Check Constraints | |
| |
| |
| |
| Foreign Key Constraints | |
| |
| |
| |
| NULLs in the Child Table | |
| |
| |
| |
| Delete Cascade | |
| |
| |
| |
| Syntax | |
| |
| |
| |
| Concatenated Primary Key Is a Foreign Key | |
| |
| |
| |
| Lookup Tables | |
| |
| |
| |
| Delete Cascade | |
| |
| |
| |
| Converting Check Constraints to Lookup Tables | |
| |
| |
| |
| Self-referential Integrity | |
| |
| |
| |
| Managing Constraints | |
| |
| |
| |
| Introduction | |
| |
| |
| |
| Declaring Constraints Disabled | |
| |
| |
| |
| Database Table Column Defaults | |
| |
| |
| |
| Multiconstraint Columns | |
| |
| |
| |
| The Alter Table Command | |
| |
| |
| |
| Altering Status | |
| |
| |
| |
| Altering Scale and Dimension | |
| |
| |
| |
| Altering Status with Cascade | |
| |
| |
| |
| Dropping Constraints | |
| |
| |
| |
| Relationships | |
| |
| |
| |
| One-to-One Relationship | |
| |
| |
| |
| One-to-Many Relationships | |
| |
| |
| |
| Many-to-Many Relationships | |
| |
| |
| |
| Analyzing Constraints through the Data Dictionary | |
| |
| |
| |
| Introduction | |
| |
| |
| |
| User Constraint Columns | |
| |
| |
| |
| Show Constraints | |
| |
| |
| |
| Show Columns | |
| |
| |
| |
| Show Positions | |
| |
| |
| |
| User Constraints | |
| |
| |
| |
| Show Table Constraints | |
| |
| |
| |
| Show Search Condition | |
| |
| |
| |
| Show Parent | |
| |
| |
| |
| Show Parent All | |
| |
| |
| |
| Show Child | |
| |
| |
| |
| Show Child All | |
| |
| |
| |
| Show Disabled Constraints | |
| |
| |
| |
| Case Study | |
| |
| |
| |
| Summary | |
| |
| |
| |
| PL/SQL Program Units | |
| |
| |
| |
| Introduction | |
| |
| |
| |
| Procedures | |
| |
| |
| |
| Functions | |
| |
| |
| |
| Packages | |
| |
| |
| |
| The Specification | |
| |
| |
| |
| Modularity | |
| |
| |
| |
| Information Hiding | |
| |
| |
| |
| Encapsulation of Types and Operations | |
| |
| |
| |
| Data Abstraction | |
| |
| |
| |
| Users of Abstract Objects | |
| |
| |
| |
| Stepwise Refinement | |
| |
| |
| |
| A Taxonomy of Packages | |
| |
| |
| |
| Using a Package | |
| |
| |
| |
| Motivation of Exceptions with Abstract Objects | |
| |
| |
| |
| Subprogram Parameter Modes | |
| |
| |
| |
| Out Mode Considerations | |
| |
| |
| |
| Mode Preservation | |
| |
| |
| |
| Default Assignment and In Out Mode | |
| |
| |
| |
| Formal Parameter and Positional Notation | |
| |
| |
| |
| Mixed Notation | |
| |
| |
| |
| When Named Notation Is Required | |
| |
| |
| |
| Subprogram Parameter Default Values | |
| |
| |
| |
| Overloading | |
| |
| |
| |
| Overloading Versus Adding Defaults | |
| |
| |
| |
| Package Initialization | |
| |
| |
| |
| The Development Environment | |
| |
| |
| |
| Introduction | |
| |
| |
| |
| Compilation Order | |
| |
| |
| |
| Interspecification Dependencies | |
| |
| |
| |
| General Order for Compile Scripts | |
| |
| |
| |
| Concurrent Development | |
| |
| |
| |
| Automatic Recompilation | |
| |
| |
| |
| Data Dictionary Views | |
| |
| |
| |
| USER_DEPENDENCIES | |
| |
| |
| |
| USER_OBJECTS | |
| |
| |
| |
| USER_SOURCE | |
| |
| |
| |
| DBMS_OUTPUT | |
| |
| |
| |
| Package Description | |
| |
| |
| |
| Developing Procedures in SQL*Plus | |
| |
| |
| |
| Creating Stored Procedures via SQL*Plus Editor | |
| |
| |
| |
| Editing and Executing from SQL*Plus | |
| |
| |
| |
| Creating Stored Procedures with a Host Editor | |
| |
| |
| |
| Compiling Procedures, Functions, and Packages | |
| |
| |
| |
| Syntax and Constructs | |
| |
| |
| |
| Comments | |
| |
| |
| |
| Assignment | |
| |
| |
| |
| Statements and Expressions | |
| |
| |
| |
| Boolean Expression | |
| |
| |
| |
| Logical Operators | |
| |
| |
| |
| String Concatenation | |
| |
| |
| |
| Arithmetric Expressions | |
| |
| |
| |
| Variables and Constants | |
| |
| |
| |
| IF Statements | |
| |
| |
| |
| LOOP | |
| |
| |
| |
| FOR LOOP | |
| |
| |
| |
| Cursor FOR LOOPs | |
| |
| |
| |
| WHILE LOOP | |
| |
| |
| |
| EXIT Statements | |
| |
| |
| |
| Embedded SQL | |
| |
| |
| |
| Introduction | |
| |
| |
| |
| Uses of Functions | |
| |
| |
| |
| User-Defined Functions | |
| |
| |
| |
| SCALAR Versus AGGREGATE Functions | |
| |
| |
| |
| PL/SQL Exceptions with GROUP Functions | |
| |
| |
| |
| Built-in Functions | |
| |
| |
| |
| Power-String Functions | |
| |
| |
| |
| SUBSTR | |
| |
| |
| |
| INSTR | |
| |
| |
| |
| RPAD, LPAD | |
| |
| |
| |
| RTRIM, LTRIM | |
| |
| |
| |
| DECODE | |
| |
| |
| |
| REPLACE | |
| |
| |
| |
| TRANSLATE | |
| |
| |
| |
| Miscellaneous String Functions | |
| |
| |
| |
| Numeric Functions | |
| |
| |
| |
| DATE Functions | |
| |
| |
| |
| SYSDATE | |
| |
| |
| |
| ADD_MONTHS | |
| |
| |
| |
| LAST_DAY | |
| |
| |
| |
| MONTHS_BETWEEN | |
| |
| |
| |
| NEW_TIME | |
| |
| |
| |
| NEXT_DAY | |
| |
| |
| |
| ROUND | |
| |
| |
| |
| Cursors | |
| |
| |
| |
| Explicit Cursors | |
| |
| |
| |
| Fetch Loop | |
| |
| |
| |
| Cursor Parameters | |
| |
| |
| |
| Implicit Cursors | |
| |
| |
| |
| Datatypes and Composite Structures | |
| |
| |
| |
| Introduction | |
| |
| |
| |
| Groups of Datatypes | |
| |
| |
| |
| Subtypes | |
| |
| |
| |
| Oracle Predefined Datatypes | |
| |
| |
| |
| Implicit Conversion | |
| |
| |
| |
| TO_TYPE Functions | |
| |
| |
| |
| Variable%TYPE | |
| |
| |
| |
| Column%TYPE | |
| |
| |
| |
| Database Table%ROWTYPE | |
| |
| |
| |
| Cursor Table%ROWTYPE | |
| |
| |
| |
| Database Table %ROWTYPE and Cursor %ROWTYPE | |
| |
| |
| |
| PL/SQL Tables | |
| |
| |
| |
| Exceptions When Addressing PL/SQL Tables | |
| |
| |
| |
| Assignment | |
| |
| |
| |
| Programming with Tables | |
| |
| |
| |
| PL/SQL Tables Release 2.3 | |
| |
| |
| |
| Tables of Composite Structures | |
| |
| |
| |
| Table Attributes | |
| |
| |
| |
| Examples | |
| |
| |
| |
| Records | |
| |
| |
| |
| Record Initialization with Literals and Variables | |
| |
| |
| |
| Record Initialization Using Global Data and Functions | |
| |
| |
| |
| Fetching into Records | |
| |
| |
| |
| Assignment | |
| |
| |
| |
| Record Types as Parameters | |
| |
| |
| |
| RECORD Versus %ROWTYPE | |
| |
| |
| |
| Error Handling and Exeptions | |
| |
| |
| |
| Introduction | |
| |
| |
| |
| Overview of PL/SQL Error Handling Options | |
| |
| |
| |
| Exceptions | |
| |
| |
| |
| Exception Categories | |
| |
| |
| |
| Exception Propogation | |
| |
| |
| |
| Exceptions Across Language Boundaries | |
| |
| |
| |
| Raise Application Error | |
| |
| |
| |
| Using Database Pipes | |
| |
| |
| |
| Summary | |
| |
| |
| |
| Interprocess Communication | |
| |
| |
| |
| DBMS_PIPE | |
| |
| |
| |
| Package Description | |
| |
| |
| |
| Runtime Errors | |
| |
| |
| |
| Pipe Examples | |
| |
| |
| |
| Pipe Buffer | |
| |
| |
| |
| DBMS_ALERT | |
| |
| |
| |
| Asynchronous Features | |
| |
| |
| |
| Communication | |
| |
| |
| |
| Polling Versus Blocking Wait | |
| |
| |
| |
| Distributed Process Control | |
| |
| |
| |
| Alerts with Triggers | |
| |
| |
| |
| Redundent Alerts Within a Process | |
| |
| |
| |
| Package Description | |
| |
| |
| |
| Examples | |
| |
| |
| |
| DBMS_LOCK | |
| |
| |
| |
| Introduction | |
| |
| |
| |
| Example | |
| |
| |
| |
| Dynamic SQL | |
| |
| |
| |
| Introduction | |
| |
| |
| |
| Package Description | |
| |
| |
| |
| Examples | |
| |
| |
| |
| Triggers | |
| |
| |
| |
| Introduction | |
| |
| |
| |
| When Triggers Fire | |
| |
| |
| |
| Twelve Triggers--When They Fire | |
| |
| |
| |
| Firing Within a DELETE Cascade | |
| |
| |
| |
| Trigger Names | |
| |
| |
| |
| Trigger Firing Specification Code | |
| |
| |
| |
| PL/SQL Keywords and Descriptions | |
| |
| |
| |
| The OF COLUMN_NAME Clause | |
| |
| |
| |
| The WHEN Clause Option | |
| |
| |
| |
| Trigger Types Summary | |
| |
| |
| |
| Trigger Body | |
| |
| |
| |
| Correlation Values | |
| |
| |
| |
| DBMS_STANDARD Functions | |
| |
| |
| |
| Transaction Control and Application Errors | |
| |
| |
| |
| Trigger Declarations | |
| |
| |
| |
| Configuration Control of Triggers | |
| |
| |
| |
| Redundant Firing Specifications | |
| |
| |
| |
| Dynamic Environments | |
| |
| |
| |
| File Management and Version Control | |
| |
| |
| |
| Administration of Triggers | |
| |
| |
| |
| Modularity in Triggers | |
| |
| |
| |
| Trigger Dependencies | |
| |
| |
| |
| Compiling Triggers | |
| |
| |
| |
| Testing with DBMS_OUTPUT | |
| |
| |
| |
| Data Dictionary Views | |
| |
| |
| |
| USER_TRIGGERS | |
| |
| |
| |
| USER_TRIGGER_COLS | |
| |
| |
| |
| Triggers as Procedural Constraints | |
| |
| |
| |
| PL/SQL Error Messages and Codes Package | |
| |
| |
| |
| Constraint Enforcement Package | |
| |
| |
| |
| Summary | |
| |
| |
| |
| A Trigger Methodology (Part 1) | |
| |
| |
| |
| Development Steps | |
| |
| |
| |
| Statement of Rules | |
| |
| |
| |
| Construct a Constraints Violation List | |
| |
| |
| |
| Trigger Functional Description | |
| |
| |
| |
| Error/Process Analysis | |
| |
| |
| |
| Encapsulate Functionality into a Constraints Package | |
| |
| |
| |
| Compute Constraints Package Complexity | |
| |
| |
| |
| Code the Trigger | |
| |
| |
| |
| Summary of Steps | |
| |
| |
| |
| Example 1 | |
| |
| |
| |
| Example 2 | |
| |
| |
| |
| Classes of Triggers | |
| |
| |
| |
| Example 3 | |
| |
| |
| |
| Mutating Tables | |
| |
| |
| |
| What Tables Mutate | |
| |
| |
| |
| When Do Tables Mutate | |
| |
| |
| |
| A Trigger Methodology (Part 2) | |
| |
| |
| |
| PL/SQL Temp Tables | |
| |
| |
| |
| PL/SQL Table Special Cases | |
| |
| |
| |
| Concatenated Keys | |
| |
| |
| |
| Old and New Attribute Requirements | |
| |
| |
| |
| Delete Cascade | |
| |
| |
| |
| Recursive Triggers | |
| |
| |
| |
| Case Study | |
| |
| |
| |
| Constraints Violation/Enforcement List | |
| |
| |
| |
| Trigger Functional Descriptions | |
| |
| |
| |
| Consolidated Trigger Functional Descriptions | |
| |
| |
| |
| PL/SQL Code | |
| |
| |
| |
| Stored Procedure Testing | |
| |
| |
| |
| Execution from SQL*Plus | |
| |
| |
| |
| Execution from the SQL*Plus Prompt | |
| |
| |
| |
| Execution from SQL*Plus Command Files | |
| |
| |
| |
| Execution from PL/SQL Blocks | |
| |
| |
| |
| Controlling Execution through Command Line Arguments | |
| |
| |
| |
| SQL*Plus Session Variables | |
| |
| |
| |
| SQL*Plus Example | |
| |
| |
| |
| PL/SQL Example | |
| |
| |
| |
| Implicit Conversion of Session Variables | |
| |
| |
| |
| Nonsupported Session Types | |
| |
| |
| |
| Restricting the Scope of Testing | |
| |
| |
| |
| Simulating Real Data | |
| |
| |
| |
| Test Environment | |
| |
| |
| |
| Runtime Errors | |
| |
| |
| |
| Identifying the Line of Execution | |
| |
| |
| |
| Locating Invisible Errors | |
| |
| |
| |
| Tracing with DBMS_OUTPUT | |
| |
| |
| |
| Summary | |
| |
| |
| Index | |
| |
| |
| About the Disk | |