課程大綱： ORACLE PL/SQL Fundamentals培訓
Introduction
Aims and Objectives
Course Schedule
Introductions
Pre-requisites
Responsibilities
SQL Tools
Objectives
SQL Developer
SQL Developer - Connection
Viewing Table Information
Using SQL, SQL Developer - Query
SQL*Plus Login
Direct Connection
Using SQL*Plus
Ending the Session
SQL*Plus Commands
SQL*Plus Environment
SQL*Plus Prompt
Finding Information about Tables
Getting Help
Using SQL Files
iSQL*Plus, Entity Models
The ORDERS Tables
The FILM Tables
Course Tables Handout
SQL Statement Syntax
SQL*Plus Commands
What is PL/SQL?
What is PL/SQL?
Why Use PL/SQL?
Block Structure
Displaying a Message
Sample Code
Setting SERVEROUTPUT
Update Example, Style Guide
Variables
Variables
Datatypes
Setting Variables
Constants
Local and Global Variables
%Type Variables
Substitution Variables
Comments with &
Verify Option
&& Variables
Define and Undefine
SELECT Statement
SELECT Statement
Populating Variables
%Rowtype Variables
CHR Function
Self Study
PL/SQL Records
Example Declarations
Conditional Statement
IF Statement
SELECT Statement
Self Study
Case Statement
Trapping Errors
Exception
Internal Errors
Error Code and Message
Using No Data Found
User Exceptions
Raise Application Error
Trapping Non-defined Errors
Using PRAGMA EXCEPTION_INIT
Commit and Rollback
Self Study
Nested Blocks
Workshop
Iteration - Looping
Loop Statement
While Statement
For Statement
Goto Statement and Labels
Cursors
Cursors
Cursor Attributes
Explicit Cursors
Explicit Cursor Example
Declaring the Cursor
Declaring the Variable
Open, Fetching the First Row
Fetching the Next Row
Exit When %Notfound
Close
For Loop I
For Loop II
Update Example
FOR UPDATE
FOR UPDATE OF
WHERE CURRENT OF
Commit with Cursors
Validation Example I
Validation Example II
Cursor Parameters,
Workshop
Workshop Solution
Procedures, Functions and Packages
Create Statement
Parameters
Procedure Body
Showing Errors
Describe a Procedure
Calling Procedures
Calling Procedures in SQL*Plus
Using Output Parameters
Calling with Output Parameters
Creating Functions
Example Function
Showing Errors
Describe a Function
Calling Functions
Calling Functions in SQL*Plus
Modular Programming
Example Procedure
Calling Functions
Calling Functions In An IF Statement
Creating Packages
Package Example
Reasons for Packages
Public and Private Sub-programs
Showing Errors
Describe a Package
Calling Packages in SQL*Plus
Calling Packages From Sub-Programs
Dropping a Sub-Program
Finding Sub-programs
Creating a Debug Package
Calling the Debug Package
Positional and Named Notation
Parameter Default Values
Recompiling Procedures and Functions
Workshop
Triggers
Creating Triggers
Statement Triggers
Row Level Triggers
WHEN Restriction
Selective Triggers - IF
Showing Errors
Commit in Triggers
Restrictions
Mutating Triggers
Finding Triggers
Dropping a Trigger
Generating an Auto-number
Disabling Triggers
Enabling Triggers
Trigger Names
Sample Data
ORDER Tables
FILM Tables
EMPLOYEE Tables
Dynamic SQL
SQL in PL/SQL
Binding
Dynamic SQL
Native Dynamic SQL
DDL and DML
DBMS_SQL Package
Dynamic SQL - SELECT
Dynamic SQL - SELECT Procedure
Using Files
Using Text Files
UTL_FILE Package
Write/Append Example
Read Example
Trigger Example
DBMS_ALERT Packages
DBMS_JOB Package
COLLECTIONS
%Type Variables
Record Variables
Collection Types
Index-By Tables
Setting Values
Nonexistent Elements
Nested Tables
Nested Table Initialisation
Using the Constructor
Adding to a Nested Table
Varrays
Varray Initialization
Adding Elements to a Varray
Multilevel Collections
Bulk Bind
Bulk Bind Example
Transactional Issues
BULK COLLECT Clause
RETURNING INTO
Ref Cursors
Cursor Variables
Defining REF CURSOR Types
Declaring Cursor Variables
Constrained and Unconstrained
Using Cursor Variables
Cursor Variable Examples
加入高級會員獲得助教答疑
