The ultimate guide to Oracle's C PL (SQL) programming, a weekly book

This week's book is the Ultimate guide to Oracle's C PL (SQL) programming, published by the mechanical industry press, Sun Fingdong, Wang Yu, Guo Xiaohui.

Content Introduction:

The ultimate guide to Oracle 12c PL/SQL is the most systematic, comprehensive, practical and authoritative book in the field of PL/SQL, and is a standard reading in this field through a series of outstanding advantages that stand out in a large number of similar books.

PL/SQL itself involves the vast and complex knowledge points, beginners can not rely on their own ability to clarify the clue, the cost of learning is very high. This book on the knowledge point of the systematic carding, the complexity of the order, highlighting the key points, pointing to the core, step by step, as far as possible for learners to provide a "shortcut." Just this, the book is a distance from the same kind of book. This book is based on Oracle's latest version 12c and presents the latest PL/SQL technology, which is also better than the published book of its kind. The whole book is driven by a comprehensive human resources project, not only can the various theoretical knowledge points well into practice, but also allow the reader to experience the complete process of the project, "explaining more practice" is also an important feature of the books, to ensure that the reader can really learn. Of course, the most important thing is the content itself, the book first on the theoretical basis of PL/SQL is a comprehensive introduction, followed by a detailed explanation of PL/SQL development of all the functional modules, methods and techniques, and finally to its various advanced features also carried out in-depth discussion.

There are many books about PL/SQL in China, but like this book is based on the latest version of PL/SQL, with the actual project as the driver, systematic, comprehensive, in-depth explanation of PL/SQL should be very few. This book is intended for all beginners and working Oracle database engineers to provide the "ultimate reference" on PL/SQL.

The ultimate guide to Oracle 12c PL/SQL Programming has the following features:

Project-driven, focus on actual combat: The whole book with a human resource management system development as the main line, according to the actual process of project development, the actual need to introduce PL/SQL program design methods, techniques, techniques, case detailed, contains a large number of code and legends.

Comprehensive and pragmatic: The book introduces both PL/SQL basic knowledge and PL/I elementary development techniques for Beginners of PL/SQL, and also introduces PL/SQL advanced features for readers with some experience in development. The whole book emphasizes "explaining more practice", compresses the unnecessary theory introduction, focuses on the training of actual development technology, the core technology comes from actual application and Oracle Official document. Readers can also refer to this book as a reference manual.

In layman's terms, the structure is reasonable: the whole book according to the actual needs of PL/SQL Program Design organization, sub-basic, development, advanced three articles. Basic knowledge of PL/SQL program design, including basic grammar, control structure, cursor, exception handling, etc. the most practical development techniques for PL/SQL programs are introduced, including subroutines, packages, triggers, dynamic SQL, batch bindings, subroutine dependencies, etc. advanced article describes Oracle Object , LOB large objects, job scheduling management, performance optimization, and PL/SQL built-in packages, among other advanced features.

New, technologically advanced: The book integrates the latest features of Oracle 12c PL/SQL into the entire knowledge system, including functions executed with caller privileges for result caching, more PL/SQL unique data types to interact with, and using accessible by Phrases for access control, using the fetch first phrase to implement TOP-N queries, granting roles to PL/SQL packages and standalone subroutines, and so on.

Directory

Objective
Basic article
The 1th chapter PL/SQL overview
1.1 What is PL/SQL
1.2 PL/SQL benefits
1.3 PL/SQL features
1.4 PL/SQL operating mechanism
1.4.1 PL/SQL engine
1.4.2 PL/SQL unit and compilation parameters
1.5 PL/SQL 12.1 new Features
1.6 Introduction to the case database
1.6.1 Case database tables and sequences
1.6.2 Creating tables and sequences
1.6.3 inserting data into a table
1.7 Using Oracle Documentation
1.8 Summary
2nd. Use of PL/SQL development tools
2.1 SQL Plus
2.1.1 SQL Plus Introduction
2.1.2 SQL plus startup and exit
2.1.3 SQL Plus common commands
2.2 SQL Developer
2.2.1 SQL Developer Introduction
2.2.2 Creating a database connection
2.2.3 Browsing and managing database objects
2.2.4 Executing SQL statements
2.2.5 developing PL/SQL Programs
2.2.6 Performing and debugging PL/SQL programs
2.3 PL/SQL Developer
2.3.1 PL/SQL Developer Introduction
2.3.2 Connection Database
2.3.3 Writing and running PL/SQL programs
2.3.4 Test PL/SQL program
2.4 Summary
3rd PL/SQL Foundation
3.1 PL/SQL program structure
3.1.1 PL/SQL block structure
3.1.2 PL/SQL block classification
3.2 PL/SQL lexical units
3.2.1 Character Set
3.3.2 Identifier
3.2.3 Delimiter
3.2.4 literal value
3.2.5 Notes
3.3 PL/SQL data type
3.3.1 PL/SQL data type overview
3.3.2 character types
3.3.3 Number Types
3.3.4 Date/Time type
3.3.5 Boolean type
3.3.6 Composite Type
3.3.7 reference types
3.3.8 LOB Type
3.3.9%type Properties
3.3.10%rowtype Properties
3.4 PL/SQL variables and constants
3.4.1 declarations of constants and variables
Scope and visibility of 3.4.2 variables
3.5 Operators and expressions
3.5.1 operator
3.5.2-expression
3.6 Compiling instructions
3.7 Summary
4th PL/SQL Control statements
4.1 Item Selection Statements
4.1.1 If statement
4.1.2 Case Statement
4.2 Loop Statements
4.2.1 Simple Loop statement
4.2.2 While Loop statement
4.2.3 For Loop statement
4.2.4 Continue statements
4.3 Sequential control statements
4.3.1 Goto statement
4.3.2 NULL statement
4.4 Summary
The 5th Chapter data query
5.1 SQL Language Overview
5.1.1 SQL Language Introduction
5.1.2 Classification of SQL language
5.1.3 features of the SQL language
5.2 SELECT statement Basic syntax
5.3 Simple Query
5.3.1 Unconditional Query
5.3.2 Conditional Query
5.4 Query Sort
5.5 Group queries
5.5.1 Common aggregation functions
5.5.2 syntax for grouping queries
5.5.3 Single row Group query
5.5.4 Multi-column group queries
5.5.5 using the HAVING clause
5.5.6 using rollup and CUBE clauses
5.5.7 Merging group queries
5.6 Connection Query
5.6.1 Cartesian product Connection
5.6.2 Internal Connection
5.6.3 Natural Connection
5.6.4 connection using a using clause
5.6.5 External Connection
5.7 Sub-query
5.7.1 Sub-Query overview
5.7.2 single-row sub-query
5.7.3 Multiline single row subquery
5.7.4 single-row multi-column subquery
5.7.5 Multi-row multi-column subquery
5.7.6 related subqueries
5.7.7 using subqueries in the FROM clause
5.7.8 using subqueries in DDL statements
5.7.9 subqueries using the WITH clause
5.8 Level Query
5.8.1 The concept of hierarchy structure
5.8.2 syntax for hierarchical queries
5.8.3 the execution process of a hierarchical query
5.8.4 hierarchy Query related pseudo-columns
5.9 Integrated Query
5.9.1 UNION
5.9.2 UNION All
5.9.3 INTERSECT
5.9.4 minus
5.10 Analysis Query
5.10.1 Analysis Query Overview
5.10.2 parsing the query syntax
Application of 5.10.3 analysis function
5.11 Top-n and Paging query
5.11.1 Top-n and paging queries prior to Oracle 12c
5.11.2 Top-n and paging queries in Oracle 12c
5.12 Summary
The 6th chapter data manipulation and transaction control
6.1 Data Insertion
Introduction to 6.1.1 Data insertion
6.1.2 The syntax of the INSERT statement
6.1.3 inserting a single line record
6.1.4 Inserting data using subqueries
6.1.5 inserting data using the Direct-path method
6.1.6 Unconditional multi-table insertion
6.1.7 Conditional multiple table insertion
6.2 Data Modification
Syntax for the 6.2.1 UPDATE statement
6.2.2 Modifying Data using subqueries
6.3 Merge Statement
6.4 Data Deletion
6.4.1 The syntax of the DELETE statement
6.4.2 using subqueries to delete data
6.4.3 using truncate to clear data
6.5 Transaction control
6.5.1 Transaction Overview
6.5.2 Isolation level for Oracle transactions
6.5.3 setting a transaction using the SET TRANSACTION statement
6.5.4 Transaction Commit and Rollback
6.6 Lock
6.6.1 Classification of Locks in Oracle database
6.6.2 DML locks in an Oracle database
6.6.3 using the Lock table statement
6.7 Summary
7th chapter built-in SQL functions
7.1 Built-in SQL functions overview
7.2 Single-line function
7.2.1 Numeric functions
7.2.2 character functions
7.2.3 Date function
7.2.4 Conversion function
7.2.5 Null value function
7.2.6 other single-line functions
7.3 Aggregation functions
7.4 Analysis functions
7.5 Other functions
7.6 Summary
8th static SQL statements in PL/V
8.1 Overview of static SQL statements
8.1.1 Static SQL statement types
8.1.2 the SELECT statement in PL/SQL
DML statements in 8.1.3 PL/SQL
8.1.4 character matching in PL/SQL
8.2 an explicit cursor
8.2.1 Cursors Overview
8.2.2 declaring and defining an explicit cursor
8.2.3 Opening and closing an explicit cursor
8.2.4 retrieving an explicit cursor
8.2.5 properties of an explicit cursor
8.2.6 variables in an explicit cursor definition
8.2.7 parameterized Explicit cursors
8.2.8 for UPDATE cursors
8.3 Implicit cursors
8.3.1 Implicit cursor Introduction
8.3.2 properties of an implicit cursor
8.4 Cursor Variables
Introduction to 8.4.1 Cursor variables
8.4.2 declaring a cursor variable
8.4.3 Opening and closing cursor variables
8.4.4 Retrieving cursor variables
8.5 Cursor Expressions
8.6 Autonomous Affairs
8.6.1 Concept of autonomous affairs
8.6.2 Declaration of autonomous Procedures
8.6.3 Control of Autonomous affairs
8.6.4 Autonomous Trigger
8.7 Summary
9th Chapter Record
9.1 Overview of record types
9.2 Declaring record types
9.3 Record Type variables
9.3.1 declaring record type variables
9.3.2 using%rowtype to declare record type variables
9.3.3 Record type variable assignment
9.4 Application of record type variables
The application of 9.4.1 record type variable in SELECT statement
The application of 9.4.2 record type variable in INSERT statement
The application of 9.4.3 record type variable in UPDATE statement
Restrictions on the use of record type variables in 9.4.4 DML statements
9.5 Summary
10th Chapter Error Handling
10.1 Compilation warnings
10.2 Exception Handling Overview
10.2.1 Oracle error handling mechanism
10.2.2 advantages of using exception handling
10.3 Types of exceptions
10.3.1 Pre-defined exceptions
10.3.2 Internal Definition exception
10.3.3 User-defined exceptions
10.4 Throwing Exceptions
10.4.1 user-defined exception throws
10.4.2 the throw of a pre-defined exception
10.4.3 the throw of an internal definition exception
10.4.4 to re-throw the current exception
Use of the 10.4.5 Raise_application_error method
10.5 Catching and handling exceptions
10.5.1 Exception Handler
10.5.2 Others exception handler
10.6 Propagation of the anomaly
10.6.1 performing partial propagation of exceptions
10.6.2 declaration part and exception handling partial exception propagation
10.7 Exception Handling Policy
Basic principles of 10.7.1 exception handling
Range of 10.7.2 anomalies
10.7.3 to avoid unhandled exceptions
10.7.4 Exceptions and transactions
10.7.5 Error Location identification
10.8 Summary

Development articles
11th. PL/SQL Sub-Program
Overview of 11.1 PL/SQL sub-programs
The concept and classification of 11.1.1 PL/SQL Sub-Program
Advantages of the 11.1.2 PL/SQL Sub-Program
11.2 Independent subroutines
11.2.1 Sub-Program structure
11.2.2 Creating a stored procedure
11.2.3 Creating a function
11.3 Calling Subroutines
11.3.1 Calling stored Procedures
11.3.2 calling a function in a PL/SQL program
11.3.3 calling a function in an SQL statement
11.4 Sub-Program parameters
11.4.1 form parameters and actual parameters
How the 11.4.2 parameter is passed
11.4.3 parameter mode
11.4.4 nocopy Keywords
Constraints of the 11.4.5 parameter
11.4.6 in mode parameter default value
11.4.7 Non-parametric subroutines
The correspondence relation of 11.4.8 real participation formal parameters
11.5 Nested Programs
11.5.1 the concept of nested subqueries
11.5.2 creation of nested subforms
11.5.3 forward declaration of nested subqueries
11.5.4 Overloading of nested subforms
11.6 management of the independent sub-program
11.7 Summary
12th PL/SQL Package
12.1 Package Overview
The concept of 12.1.1 package
Classification of 12.1.2 Bags
12.1.3 Advantages of using packages
12.2 Creating a Package
12.2.1 Package Specification
12.2.2 Bag Body
12.3 Calling Package
12.4 Reload Package
12.5 Instantiating and initializing packages
12.6 Package Purity Level
12.7 Status of the package
12.7.1 variable Persistence
12.7.2 cursor Persistence
12.7.3 serially_reusable Bag
12.8 Package Authoring Guide
12.9 Standard Pack
12.10 Package Management
12.11 Summary
13th Zhang Ziyi (Program dependency and Rights management
13.1 Object Dependencies Overview
13.1.1 The concept of object dependencies
13.1.2 Object Dependency Query
13.2 Local dependencies for subroutines
13.2.1 dependencies of the sub-program
Dependency relationships of 13.2.2 packages
13.3 Remote dependencies for subroutines
13.3.1 the establishment of remote dependency relationship
Identification of 13.3.2 remote dependency invalidation
13.4 Sub-Program Permissions overview
13.4.1 Required permissions for subroutine calls
13.4.2 the permissions required by the child program definition
13.5 caller and Definition permissions
13.6 Summary
14th Chapter Trigger
14.1 Introduction to Triggers
The concept of 14.1.1 triggers
The role of 14.1.2 triggers
Types of 14.1.3 triggers
Structure of the 14.1.4 trigger
14.2 simple DML triggers
14.2.1 Simple DML triggers overview
14.2.2 statement-level simple DML trigger
14.2.3 row-level simple DML triggers
14.2.4 execution order of simple DML triggers
14.3 INSTEAD of DML triggers
14.3.1 INSTEAD of DML triggers overview
14.3.2 creating instead of DML triggers
14.3.3 Inst-ead of DML triggers based on view nested table columns
14.4 Composite DML Triggers
14.4.1 composite DML triggers overview
14.4.2 creating a composite DML trigger
14.4.3 Composite DML Trigger Application
14.5 referential integrity guaranteed with DML triggers
14.6 system triggers
14.6.1 response events for system triggers
14.6.2 Creating a system trigger
14.6.3 event Property function
14.7 Mutation Table Trigger
Limitations of 14.7.1 mutation table triggers
14.7.2 Mutation Table trigger application
14.8 Trigger Design Guide
14.9 Trigger Management
14.9.1 limits defined by triggers
Disabling and activating the 14.9.2 trigger
Modification, recompilation, and deletion of 14.9.3 triggers
Information query of 14.9.4 trigger
14.10 Summary
15th Chapter Set
15.1 Collection Overview
15.2 Union Array
15.2.1 Union Array Overview
15.2.2 Defining Union arrays
15.2.3 Operation Union Array
15.3 Nested tables
15.3.1 Nested Tables Overview
15.3.2 Defining nested Tables
15.3.3 Initializing nested tables
15.3.4 manipulating nested tables
15.4 variable Array
15.4.1 Variable Array overview
15.4.2 definition and initialization of variable arrays
15.4.3 manipulating variable arrays
15.5 method of collecting and closing
15.6 Comparison of Set-type variables
15.6.1 comparison of set type variables to NULL
15.6.2 Nested Table variables comparison
15.6.3 Nested table variable multi-set condition comparison
15.7 Independent collection Types
Application of 15.7.1 nested table in database
Application of 15.7.2 variable array in database
15.8 collection types that are defined in a package
15.9 Multi-dimensional collections
15.10 Summary
16th Chapter Dynamic SQL statements
16.1 Overview of Dynamic SQL statements
16.1.1 The concept of dynamic SQL statements
16.1.2 Dynamic SQL statements compared to static SQL statements
16.2 Local Dynamic SQL
16.2.1 EXECUTE Immediate Statement
16.2.2 Dynamic DDL Operations
16.2.3 Dynamic DCL operation
16.2.4 Dynamic DML Operations
16.2.5 Dynamic Single-row query operation
16.2.6 Dynamic Multi-row query operation
16.2.7 calling subroutines in dynamic SQL statements
16.2.8 placeholder for duplicate names in dynamic SQL statements
16.3 Dbms_sql Bag
16.3.1 Dbms_sql Package Overview
Introduction to Methods of 16.3.2 Dbms_sql package
16.3.3 Dbms_sql Operation Flow
16.3.4 using the Dbms_sql package to execute dynamic DDL statements
16.3.5 dynamic DML statements using the Dbms_sql package
16.3.6 using the Dbms_sql package to execute Dynamic query statements
16.3.7 using Dbms_sql package to execute anonymous blocks
16.3.8 Other common dbms_sql methods applied
16.4 Summary
17th Chapter Batch Binding
17.1 Batch Bindings Overview
17.2 forall Statements
17.2.1 syntax for ForAll statements
17.2.2 forall statements compared to For loop statements
17.2.3 Bulk DML Operations
17.2.4 using a sparse collection of forall statements
Exception handling for 17.2.5 forall statements
17.2.6 gets the number of records affected by the ForAll statement
17.3 BULK Collect Phrases
17.3.1 SELECT BULK Collect statement
17.3.2 FETCH BULK Collect statement
17.3.3 returning BULK collect phrase
17.4 Dynamic Batch Binding
17.4.1 Dynamic Bulk DML operations
17.4.2 Dynamic Batch query operation
17.5 Summary

Advanced article
18th Chapter Object
18.1 Oracle Objects Overview
18.1.1 Oracle Object Introduction
18.1.2 Benefits of using Oracle objects
18.1.3 Object Characteristics of Oracle Database
18.2 Object type definition
18.2.1 Object Type Specification
18.2.2 Object Type Body
18.3 Object Type Methods
18.3.1 Member method
18.3.2 static method
18.3.3 Map and order method
18.3.4 Construction Method
18.4 Inheritance of object types
18.4.1 Object Type Inheritance Overview
18.4.2 final and not final object types and methods
18.4.3 creating sub-object types
18.4.4 method redefinition and overloading
18.4.5 not instantiable object types and methods
18.5 manipulating objects in the database
18.5.1 Creating an Object table
18.5.2 Action Object Table
18.5.3 Manipulating Column objects
18.5.4 Object reference
18.6 Object views
18.6.1 Object View Overview
18.6.2 creating an Object view
18.6.3 nesting objects in an object view
18.6.4 Action Object View
18.7 using object types in PL/SQL
Declaration and initialization of a 18.7.1 object
18.7.2 manipulating objects in PL/SQL
18.8 Object-related functions and operators
18.9 management of object types
18.9.1 Modifying Object types
18.9.2 Deleting object types
18.10 Summary
The 19th chapter of the Big object
19.1 Overview of Large objects
19.1.1 why large objects are used
Comparison of 19.1.2 lob with long
19.1.3 LOB Classification
19.1.4 LOB storage Structures and locators
19.1.5 LOB operation Technology and interface
19.2 manipulating LOB objects with SQL
19.2.1 Creating a database table with LOB columns
19.2.2 using SQL to manipulate internal LOB
19.2.3 using SQL to manipulate external lobs
19.3 Dbms_lob Bag
19.3.1 Dbms_lob Package Introduction
Introduction of 19.3.2 Dbms_lob Package Neutron Program
19.4 manipulating LOB objects with PL/SQL
19.4.1 using Dbms_lob package to manipulate CLOB objects
19.4.2 using the Dbms_lob package to manipulate BLOB objects
19.4.3 using Dbms_lob package to manipulate bfile objects
19.5 Summary
20th. Oracle Task Scheduling
20.1 Oracle Scheduler Overview
20.1.1 Concept of Oracle Scheduler
20.1.2 Scheduling objects
20.2 Job Object Management
20.2.1 Creating a Job Object
20.2.2 Modifying a Job Object
20.2.3 running the Job Object
20.2.4 Stop Job Object
20.2.5 Deleting Job Objects
20.2.6 Disabling Job Objects
20.2.7 activating a Job Object
20.3 Program Object Management
20.4 Scheduling Object Management
20.5 Summary
21st Chapter PL/SQL performance optimization
Introduction to 21.1 PL/SQL Performance optimizations
21.2 Sub-Program inline
21.3 SQL statement Optimization
21.3.1 SQL Statement Optimization overview
21.3.2 SQL statement Execution procedure
21.3.3 to host memory for common programs
21.3.4 Efficient Use of indexes
21.3.5 uses the appropriate multi-table connection technology
21.3.6 SQL statement Usage tips
21.4 Minimizing CPU Load
21.4.1 function calls in an optimized query
21.4.2 Optimizing subroutine Calls
21.4.3 optimization Cycle
21.4.4 optimizing the PL/SQL code for calculations
21.4.5 using SQL character functions
21.4.6 batch SQL and batch bindings
21.5 use of pipe functions
21.5.1 Table Functions Overview
21.5.2 Creating a pipe table function
21.5.3 a pipeline table function as a conversion function
21.5.4 retrieving the returned result set of a pipeline table function
21.5.5 passing a cursor expression to a pipe table function
21.6 Performance Optimization Tool
21.6.1 using the Profiler API tool
21.6.2 using the Trace API tool
21.6.3 using the PL/SQL Hierarchical Profiler Tool
21.7 Summary
22nd. Common Oracle Built-in packages
22.1 Introduction to Oracle Built-in packages
22.2 Dbms_alert Bag
22.3 Dbms_ddl Bag
22.4 Dbms_flashback Bag
22.5 Dbms_metadata Bag
22.5.1 Dbms_metadata Package Introduction
22.5.2 dbms_metadata Packet Neutron program
22.6 DBMS_LOGMNR Bag
22.7 Dbms_logmnr_d Bag
22.8 Dbms_pipe Bag
22.8.1 Oracle Pipeline Communications Overview
22.8.2 dbms_pipe Packet Neutron program
22.9 Dbms_random Bag
22.10 Dbms_rowid Bag
22.11 Dbms_transaction Bag
22.12 Utl_file Bag
22.13 Utl_tcp Bag
22.14 Summary

Get method

If you are tempted, how do you get this book?
1. If you are not at this time, please go to the subscription number (xuanhun521), reply "Weekly book", find the corresponding article, leave a message below, talk about your database, Oracle's understanding, understanding, sentiment or interesting stories and ideas
2. Let your friends help you to praise the message.
3. If your sentiment or story is wonderful, you can submit it to us.
Selection criteria:
1. The highest number of likes, or message very wonderful message, will get a book
2. If you have a contribution related to the content of the book, if adopted, in the subscription number issued, this book priority reward to you .
3. At the same time, many people contribute, return to the 1th article.
Scan issued two-dimensional code, follow the subscription number, reply to the "weekly book", to understand the latest running situation, the recent book small activities, the method of obtaining books in the article.
Every week, there's always a book you like.

Xuan Soul Studio-Bridge to build communication with knowledge

The ultimate guide to Oracle's C PL (SQL) programming, a weekly book