Oracle+pl+sql from getting started to mastering. Dings. Tsinghua University Press. 2012

\ t 1th Introduction to PL/SQL development
1th Oracle 11g Database System
1.1 Introduction of relational database system
1.1.1 What is a relational data model
1.1.2 Database System Paradigm
1.1.3-relational database management system
1.1.4 using SQL statements to communicate with database management systems
1.2 Initial knowledge of Oracle 11g
1.2.1 Oracle 11g Introduction
1.2.2 Oracle 11g Architecture
1.2.3 How to create a database
1.2.4 Comparing Oracle database to SQL Server database
1.3 What is PL/SQL
1.3.1 PL/SQL is a language
1.3.2 PL/SQL for the execution environment
1.4 Building a PL/SQL development environment
1.4.1 using Sql*plus
1.4.2 using Oracle SQL Developer
1.4.3 PL/SQL Developer Development PL/SQL
.1.4.4 Quest Toad Development PL/SQL
1.4.5 using the Oracle document library
1.5 Summary

\ t 2nd basic concept of PL/SQL
2.1 Features
2.1.1 Structured programming
2.1.2 and SQL language integration
2.1.3 Object-oriented development
2.1.4 Modular Application Development
2.1.5 Improving Application Performance
2.2 Language Features
2.2.1 PL/SQL block structure
2.2.2 Variables and types
2.2.3 Program Control statements
2.2.4 processes, functions, and packages
2.2.5 Trigger
2.2.6 Structured Exception handling
2.2.7 Collections and records
2.2.8 Cursors
2.2.9 Dynamic SQL
2.3 Coding Style
2.3.1 PL/SQL lexical units
2.3.2 Indent
2.3.3 Identifier naming rules
2.3.4-Case Style
2.3.5 Formatting code with tools
2.4 Summary

\ t Chapter 3rd variables and types
3.1 Variables
Declaration of the 3.1.1 variable
Assignment of the 3.1.2 variable
3.1.3 Using%type
3.1.4 Using%rowtype
Scope and visibility of 3.1.5 variables
Definition of 3.1.6 Constants
3.2 Data types
3.2.1 character types
3.2.2 Number Types
3.2.3 Date and Time type
3.2.4 Boolean type
3.2.5 LOB Object Types
3.2.6 Reference types
3.2.7 Composite Type
3.2.8 User-defined subtype
3.2.9 Data type Conversions
3.3 Operators and expressions
3.3.1 Operator Type
Precedence of the 3.3.2 operator
3.3.3 Expression type
3.4 Summary

\ t 4th PL/SQL Control statements
4.1 Branch Control statements
4.1.1 If-then-else Statements
4.1.2 If-then-elsif Statements
4.1.3 Case Statement
4.1.4 Search Case Statements
4.2 Loop control Statements
4.2.1 Loop Loop
4.2.2 using Exit to exit the loop
4.2.3 using Exit-when to exit the loop
4.2.4 using continue to continue the loop
4.2.5 While-loop Cycle
4.2.6 For-loop Cycle
4.2.7 Circular Statement Usage recommendations
4.3 Sequential control statements
4.3.1 goto Statements and tags
4.3.2 NULL statement
4.4 Summary

\ t 2nd PL/SQL Development Basics
5th Chapter Management Data Sheet
5.1 Creating a Table
5.1.1 Data Definition Language DDL
5.1.2 CREATE TABLE statement
5.1.3 Creating tables in the designer
5.1.4 Creating a copy of a table
5.2 Creating constraints
5.2.1 Creating a PRIMARY KEY constraint
5.2.2 Creating a FOREIGN KEY constraint
5.2.3 Creating a Check Constraint
5.2.4 Viewing table constraints
5.3 Modifying a table
5.3.1 Modifying table columns
5.3.2 Modifying constraints
5.3.3 Removing data tables
5.3.4 modifying tables in the designer
5.4 Index
5.4.1 Index Introduction
5.4.2 Index principle
5.4.3 Creating an Index
5.4.4 Modifying an index
5.4.5 Deleting an index
5.5 Using Views
5.5.1 View Introduction
5.5.2 Creating a View
5.5.3 Modifying a view
5.5.4 Deleting a view
5.6 Summary

\ t 6th query data Sheet
6.1 Simple Query
6.1.1 Querying table Data
6.1.2 Specifying query criteria
6.1.3 Sort
6.1.4 using Functions
6.1.5 Statistical functions
6.1.6 Grouping Statistics
6.1.7 HAVING clause
6.1.8 using the Dual table
6.1.9 rownum Pseudo-column
6.1.10 ROWID pseudo-column
6.2 Complex Queries
6.2.1 Multi-table connection query
6.2.2 using subqueries
6.2.3 table Collection Operations
6.2.4 Hierarchical queries
6.3 Summary

\ t 7th manipulate data Sheet
7.1 Inserting records
7.1.1 Data Manipulation Language DML
7.1.2 inserting a single line record
7.1.3 inserting default values and Null values
7.1.4 inserting multiple rows of data using subqueries
7.1.5 inserting multiple table data using insert
7.2 Update History
7.2.1 updating a single line record
7.2.2 Updating records using subqueries
7.2.3 Merging table rows with merge
7.3 Deleting Records
7.3.1 Delete a single line record
7.3.2 Deleting records using subqueries
7.3.3 using truncate to clear table data
7.4 Committing and rolling back records
7.4.1 Commit Changes
7.4.2 Rolling back changes
7.5 Using sequences
Introduction to the 7.5.1 sequence
7.5.2 Creating a data series
7.5.3 Nextval and Currval pseudo-columns
7.5.4 Using data series
7.5.5 Modifying a sequence
7.5.6 Deleting a sequence
7.6 Synonyms
7.6.1 Synonyms Introduction
7.6.2 Creating and using synonyms
7.7 Summary

\ t 8th chapter Records and collections
8.1 Record types
Introduction to 8.1.1 Record types
8.1.2 Defining record types
8.1.3 Record Type Assignment
8.1.4 Manipulating record types
8.1.5 using nested Records
8.2 Understanding Collection Types
Introduction to 8.2.1 Collection
8.2.2 Defining an Index Table
8.2.3 Manipulating Index tables
8.2.4 Defining nested Tables
8.2.5 manipulating nested tables
8.2.6 a nested table in a database
8.2.7 defining variable-length arrays
8.2.8 manipulating variable-length arrays
Variable-length arrays in the 8.2.9 database
8.2.10 Selecting a collection type
8.3 Using the collection method
8.3.1 using the Exists method
8.3.2 using the Count method
8.3.3 using the Limit method
8.3.4 first and Last methods
8.3.5 Prior and Next methods
8.3.6 Extend method
8.3.7 Trim method
8.3.8 Delete method
Exception Handling for 8.3.9 collections
8.3.10 using bulk Bindings
8.3.11 using bulk Collect
8.4 Summary

\ t 9th SQL built-in functions
9.1 Basic Functions
9.1.1 Character type function
9.1.2 Digital Functions
9.1.3 Date Time function
9.1.4 Type conversion function
9.1.5 grouping functions
9.1.6 other functions
9.2 Oracle Analytic functions
9.2.1 What is an analytic function
9.2.2 Basic Syntax
9.2.3 Analysis Function structure
9.2.4 Analysis Function List
9.3 Example of analysis function usage
9.3.1 Record Ranking
9.3.2 first-end record query
9.3.3 before and after the ranking query
9.3.4 level Query
9.3.5 Range Statistics Query
9.3.6 Adjacent Records comparison
9.3.7 Suppresses repetition
9.3.8 row and column conversion query
9.3.9 using analytic functions in PL/SQL
9.4 Summary

\ t 10th using cursors
10.1 Cursor BASIC structure
Introduction to 10.1.1 Cursors
10.1.2 Cursor Classification
10.1.3 Defining cursor Types
10.1.4 Opening Cursors
10.1.5 using Cursor Properties
10.1.6 Extracting cursor data
10.1.7 fetching cursor data in batches
10.1.7 closing Cursors
10.2 Manipulating cursor data
10.2.1 Loop Loop
10.2.2 While loop
10.2.3 cursor FOR loop
10.2.4 Modifying cursor data
10.3 Cursor Variables
Introduction to 10.3.1 Cursor variables
10.3.2 declaring cursor variable types
10.3.3 Defining a cursor variable
10.3.4 opening a cursor variable
10.3.5 Controlling cursor variables
10.3.7 Handling Cursor Variable exceptions
10.3.8 using cursor variables in packages
Limitations of 10.3.9 Cursor variables
10.4 Summary

\ t 11th transaction processing and locking
11.1 Introduction to Transaction processing
11.1.1 What is transaction processing
11.1.2 committing a transaction using commit
11.1.3 rolling back transactions using rollback
11.1.4 using savepoint Save point
11.1.5 setting transaction properties using SET transaction
11.2 Using Locks
11.2.1 Understanding Locking
11.2.2 Record Lock
11.2.3 table lock
11.2.4 Using the Lock table
11.3 Summary

\ t the 12th Chapter exception handling mechanism
12.1 Understanding Exception Handling
Introduction to 12.1.1 Exception handling
12.1.2 Exception Handling syntax
12.1.3 Pre-defined exceptions
12.2 Custom Exceptions
12.2.1 declaring exceptions
12.2.2 Scope Range
12.2.3 using Exception_init
12.2.4 using Raise_application_error
12.2.5 throws an exception
12.2.6 Handling Exceptions
12.2.7 using Sqlcode and SQLERRM
12.3 Exception passing
12.3.1 exception passing at execution time
12.3.2 exception passing when declaring
12.3.3 exceptions in the exception handler
12.3.4 re-throwing exceptions
12.3.5 Exception Handling Guidelines
12.4 Summary

\ t 3rd PL/SQL Advanced programming
13th. PL/SQL Sub-Program
13.1 Sub-Program structure
Introduction to 13.1.1 sub-procedures
Advantages of the 13.1.2 sub-program
13.1.3 creation Process
13.1.4 Creating a function
13.1.5 Return statement
13.1.6 Viewing and deleting subroutines
13.2 Sub-Program parameters
13.2.1-shaped participation arguments
13.2.2 parameter mode
Constraints of 13.2.3 form parameters
13.2.4 parameter Passing method
13.2.5 parameter Default value
13.2.6 using nocopy to compile hints
13.3 Sub-Program advanced technology
13.3.1 calling subroutines in SQL
13.3.2 Nesting of nested programs
Forward declaration of the 13.3.3 Sub-program
13.3.4 overloaded subroutines
13.3.5 Child Program self-service
13.3.6 Recursive calling subroutine
13.3.7 Understanding subroutine Dependencies
13.3.8 Sub-Program Rights Management
13.4 Summary

\ t 14th Package
14.1 Understand the PL/SQL Package
14.1.1 What is a package
14.1.2 Package Benefits
14.1.3 Defining Package Specification
14.1.4 Defining the Package
14.1.5 Calling Package Components
14.1.6 Compile and debug the package
14.1.7 View the package's source code
14.2 Package Advanced Technology
14.2.1 package Overloads
14.2.2 Package Initialization
14.2.3 Package Purity Level
14.2.4 Package permission settings
14.2.5 Use a cursor in a package
14.3 manage a package in the database
14.3.1 Viewing and deleting packages
14.3.2 checking package dependencies
14.4 Using the System Package
14.4.1 using Dbms_output package
14.4.2 using Dbms_ Pipe package
14.4.3 using Dbms_alter package
14.4.4 using Dbms_job package
14.5 Summary

\ t 15th trigger
15.1 Understanding trigger
15.1.1 Trigger Introduction
15.1.2 defining triggers
15.1.3 Trigger classifications
15.2 DML triggers
15.2.1 execution order of triggers
15.2.2 defining DML triggers
15.2.3 Debug triggers
15.2.4 using statement triggers
15.2.5 using the old and new verbs
15.2.6 using referencing clauses
15.2.7 using when clauses
15.2.8 using conditional verbs
15.2.9 to control the trigger order
15.2.10 Trigger Limit
15.2.11 Use an autonomous transaction
15.3 override trigger
15.3.1 override trigger
15.3.2 Define an alternate trigger
15.3.3 update with delete substitution trigger
15.3.4 nested table substitution trigger
15.4 System Event Trigger
15.4.1 Defining system Triggers
15.4.2 Trigger Event List
15.4.3 Trigger Property List
15.4.4 Property Function Use Example
15.4.5 defining SERVERERROR Triggers
15.4.6 Triggers transactions and constraints
15.5 the management of triggers
15.5.1 View Trigger source code
15.5.2 Delete and disable triggers
15.5.3 Management of names and permissions
15.6 summary

\ t 16th dynamic SQL Statement
16.1 Understanding dynamic SQL Statements
16.1.1 Dynamic SQL Basics
16.1.2 Dynamic SQL use time
16.1.3 local dynamic SQL
16.2 using Execute Immediate
16.2.1 execute immediate Syntax
16.2.2 Execute SQL statement and PL/SQL statement block
16.2.3 use bound variable
16.2.4 use returnning into clause
16.2.5 execute a single line query
16.2.6 Specify parameter Mode
16.3 Multiline query statement
16.3.1 use open-for statement
16.3.2 use a fetch statement
16.3.3 Close cursor Variable
16.4 Using bulk Binding
16.4.1 Bulk Execute immediate syntax
16.4.2 using bulk FETCH statements
16.4.3 using bulk forall statements
16.5 usage recommendations for dynamic SQL
16.5.1 using binding variables to improve performance
16.5.2 using duplicate placeholders
16.5.3 use caller Permissions
16.5.4 pass the null parameter
16.5.5 Dynamic SQL exception handling
16.6 Summary

\ t 4th PL/SQL Advanced programming
The 17th chapter object-oriented programming
17.1 Object Basics
17.1.1 Introduction to Object orientation
17.1.2 What is an object type
17.1.3 the composition of objects in PL/SQL
17.2 Defining Object Types
17.2.1 Defining Object Types
17.2.2 Defining Object Bodies
17.2.3 Defining properties
17.2.4 Definition Method
17.2.5 using the Self keyword
17.2.6 Defining constructors
17.2.7 defining map and order methods
17.2.8 using Object types
17.2.9 using nested object types
17.2.10 Object Inheritance
17.2.11 Method Overloading
17.3 Management Object Table
17.3.1 Defining Object Tables
17.3.2 Insert Object Table
17.3.3 Retrieving object tables
17.3.4 Update Object Table
17.3.5 Deleting an Object table
17.3.6 Creating Object Columns
17.3.7 using object views
17.4 Managing Object Types
17.4.1 Viewing object types
17.4.2 Modifying Object types
17.5 Summary

\ t the 18th Chapter PL/SQL Performance optimization recommendations
18.1 Understanding PL/SQL Program performance
18.1.1 common causes of impact performance
18.1.2 using Dbms_profiler Package
18.1.3 using Dbms_trace Package
18.2 PL/SQL performance optimization Tips
18.2.1 Understanding Query Execution plan
18.2.2 table order for join queries
18.2.3 Specifying WHERE Condition order
18.2.4 Avoid using * symbols
18.2.5 using the Decode function
18.2.6 use where rather than having
18.2.7 using union instead of or
18.2.8 using exists rather than in
18.2.9 avoid inefficient PL/SQL Process Control statements
18.2.10 avoiding implicit types of conversions
18.3 Summary

\ t 5th PL/SQL case
The 19th chapter of enterprise IC chip due to the calculation program
19.1 System Design
19.1.1 Program Requirements Introduction
19.1.2 data Table ER diagram
19.1.3 System Overall Process
19.1.4 example Environment build-up
19.2 System Encoding implementation
19.2.1 Creating Package Specifications
19.2.2 Initializing data
19.2.3 Obtaining IC Demand
19.2.4 IC material Check function
19.2.5 get the number of goods that have been walked
19.2.6 Get Sales order Quantity
19.2.7 Calculate Enterprise IC Demand
19.2.8 prepare the data for the next calculation
19.2.9 Define calling Main program
19.3 Debugging and deploying applications
19.3.1 compiling the application
19.3.2 Debugging Applications
19.3.3 Viewing program Results
19.3.4 deploying to a production server
19.4 Summary

\ t 20th PL/SQL Mail Sender Program
20.1 System Design
20.1.1 Program Requirements Introduction
20.1.2 using Utl_ SMTP to send e-mail
20.1.3 System Overall Process
20.1.4 example Environment build-up
20.2 System Encoding Implementation
20.2.1 recognize MIME types
20.2.2 implementing MIME-type mail delivery
20.2.3 Defining Package Specifications
20.2.4 Message initialization function Xm_init
20.2.5 send and close the connection Xm_close
20.2.6 Sending HTML messages
20.2.7 Sending mail attachments
20.2.8 Send Excel Attachment content
20.2.9 writing to the workbook Wb_header
20.2.10 Writing Worksheets Xm_worksheet
20.2.11 Writing table Rows Xm_ws_row
20.2.12 Writing worksheet footer information
20.2.13 Execute SQL statement to the worksheet
20.3 Compiling and deploying applications
20.3.1 Compiling and debugging applications
20.3.2 Verifying test results
20.3.3 deploying to a production server
20.4 Summary

Oracle+pl+sql from getting started to mastering. Dings. Tsinghua University Press. 2012