• Oracle Database: Program with PL/SQL 培訓
  Oracle Database: Program with PL/SQL 培訓
  培訓大綱：
  1. Introduction
  Course Objectives and Agenda
  Human Resources (HR) Schema
  PL/SQL Development Environments
  SQL Developer
  2. Introduction to PL/SQL
  PL/SQL
  Benefits of PL/SQL Subprograms
  Types of PL/SQL blocks
  Create a Simple Anonymous Block
  Generate Output from a PL/SQL Block
  3. Declare PL/SQL Identifiers
  Types of Identifiers in a PL/SQL Subprogram
  Use Declarative Section to Define Identifiers
  Use Variables to Store Data
  Scalar Data Types
  The %TYPE Attribute
  Bind Variables
  Sequences in PL/SQL Expressions
  4. Write Executable Statements
  Basic PL/SQL Block Syntax Guidelines
  Comment the Code
  Deployment of SQL Functions in PL/SQL
  Convert Data Types
  Nested Blocks
  Operators in PL/SQL
  5. Interaction with the Oracle Server
  Invoke SELECT Statements in PL/SQL
  Retrieve Data in PL/SQL
  SQL Cursor Concept
  Avoid Errors by using Naming Conventions when using Retrieval and DML Statements
  Data Manipulation in the Server using PL/SQL
  SQL Cursor concept
  Use SQL Cursor Attributes to Obtain Feedback on DML
  Save and Discard Transactions
  6. Control Structures
  Conditional Processing using IF Statements
  Conditional Processing using CASE Statements
  Simple Loop Statement
  While Loop Statement
  For Loop Statement
  Use the Continue Statement
  7. Composite Data Types
  Use PL/SQL Records
  The %ROWTYPE Attribute
  Insert and Update with PL/SQL Records
  INDEX BY Tables
  Examine INDEX BY Table Methods
  Use INDEX BY Table of Records
  8. Explicit Cursors
  Explicit Cursors
  Declare the Cursor
  Open the Cursor
  Fetch Data from the Cursor
  Close the Cursor
  Cursor FOR loop
  The %NOTFOUND and %ROWCOUNT Attributes
  FOR UPDATE Clause and WHERE CURRENT Clause
  9. Exception Handling
  Exceptions
  Handle Exceptions with PL/SQL
  Trap Predefined Oracle Server Errors
  Trap Non-Predefined Oracle Server Errors
  Trap User-Defined Exceptions
  Propagate Exceptions
  RAISE_APPLICATION_ERROR Procedure
  10. Stored Procedures
  Create a Modularized and Layered Subprogram Design
  Modularize Development With PL/SQL Blocks
  PL/SQL Execution Environment
  Benefits of using PL/SQL Subprograms
  Differences between Anonymous Blocks and Subprograms
  Create, Call, and Remove Stored Procedures
  Implement Procedures Parameters and Parameters Modes
  View Procedure Information
  11. Stored Functions and Debugging Subprograms
  Create, Call, and Remove a Stored Function
  Advantages of using Stored Functions
  Steps to create a stored function
  Invoke User-Defined Functions in SQL Statements
  Restrictions When Calling Functions
  Control Side Effects When Calling Functions
  View Functions Information
  Debug Functions and Procedures
  12. Packages
  Advantages of Packages
  Packages
  Components of a Package
  Develop a Package
  Enable Visibility of a Package''s Components
  Create the Package Specification and Body using the SQL CREATE Statement and SQL Developer
  Invoke the Package Constructs
  View the PL/SQL Source Code using the Data Dictionary
  13. Deploying Packages
  Overloading Subprograms in PL/SQL
  Use the STANDARD Package
  Use Forward Declarations to Solve Illegal Procedure Reference
  Implement Package Functions in SQL and Restrictions
  Persistent State of Packages
  Persistent State of a Package Cursor
  Control Side Effects of PL/SQL Subprograms
  Invoke PL/SQL Tables of Records in Packages
  14. Implement Oracle-Supplied Packages in Application Development
  Oracle-Supplied Packages
  Examples
  How the DBMS_OUTPUT Package Works
  Use the UTL_FILE Package to Interact with Operating System Files
  Invoke the UTL_MAIL Package
  Write UTL_MAIL Subprograms
  15. Dynamic SQL
  The Execution Flow of SQL
  Dynamic SQL
  Declare Cursor Variables
  Dynamically Executing a PL/SQL Block
  Configure Native Dynamic SQL to Compile PL/SQL Code
  Invoke DBMS_SQL Package
  Implement DBMS_SQL with a Parameterized DML Statement
  Dynamic SQL Functional Completeness
  16. Design Considerations for PL/SQL Code
  Standardize Constants and Exceptions
  Local Subprograms
  Write Autonomous Transactions
  Implement the NOCOPY Compiler Hint
  Invoke the PARALLEL_ENABLE Hint
  The Cross-Session PL/SQL Function Result Cache
  The DETERMINISTIC Clause with Functions
  Usage of Bulk Binding to Improve Performance
  17. Triggers
  Trigger Event Types and Body
  Business Application Scenarios for Implementing Triggers
  Create DML Triggers using the CREATE TRIGGER Statement and SQL Developer
  Trigger Event Types, Body, and Firing (Timing)
  Statement Level Triggers vs. Row Level Triggers
  Create Instead Of and Disabled Triggers
  How to Manage, Test, and Remove Triggers?
  18. Creating Compound, DDL, and Event Database Triggers
  Compound Triggers
  Timing-Point Sections of a Table Compound Trigger
  Compound Trigger Structure for Tables and Views
  Implement a Compound Trigger to Resolve the Mutating Table Error
  Comparison of Database Triggers to Stored Procedures
  Create Triggers on DDL Statements
  Create Database-Event and System-Events Triggers
  System Privileges Required to Manage Triggers
  19. PL/SQL Compiler
  Initialization Parameters for PL/SQL Compilation
  New PL/SQL Compile Time Warnings
  PL/SQL Compile Time Warnings for Subprograms
  Benefits of Compiler Warnings
  PL/SQL Compile Time Warning Messages Categories
  Setting the Warning Messages Levels: Using SQL Developer, PLSQL_WARNINGS Initialization Parameter, and the DBMS_WARNING Package Subprograms
  View Compiler Warnings: Using SQL Developer, SQL*Plus, or the Data Dictionary Views
  20. Manage PL/SQL Code
  Conditional Compilation
  Implement Selection Directives
  Invoke Predefined and User-Defined Inquiry Directives
  The PLSQL_CCFLAGS Parameter and the Inquiry Directive
  Conditional Compilation Error Directives to Raise User-Defined Errors
  The DBMS_DB_VERSION Package
  Write DBMS_PREPROCESSOR Procedures to Print or Retrieve Source Text
  Obfuscation and Wrapping PL/SQL Code
  21. Manage Dependencies
  Schema Object Dependencies
  Query Direct Object Dependencies using the USER_DEPENDENCIES View
  Query an Object''s Status
  Invalidation of Dependent Objects
  Display the Direct and Indirect Dependencies
  Fine-Grained Dependency Management in Oracle Database 11g
  Remote Dependencies
  Recompile a PL/SQL Program Unit
  ?
  ?
  ?
  ?
  ?
• 
  ?
• 
  ?
• 
  ?
  ?
  ?
  ?
• 
  ?
  ?

?
?

?

?

?

?

?
?

?
?

?

?

?

?
???

?

?
?

?

?