 Oracle 9i PL/SQL |
| Course duration(in hrs): 28 |
| Course Objectives: |
» Use iSQL*Plus to format reports
» Use basic iSQL*Plus commands
» Retrieve, insert, update, and delete data
» Describe relational and object relational database concepts
» Create and maintain database objects
» Control transactions
» Control data and user access
|
| Course Topics |
| INTRODUCTION |
» Describing PL/SQL
» Describing the Use of PL/SQL for the Developer and the Database Administrator
» Explaining the Benefits of PL/SQL
» PL/SQL program constructs
» PL/SQL anonymous block structure
» Subprogram block structure
» Course objectives and overview
|
| DECLARING VARIABLES |
» Recognizing the Basic PL/SQL Block and Its Sections
» Describing the Significance of Variables in PL/SQL
» Distinguishing Between PL/SQL and Non-PL/SQL Variables
» Declaring Variables and Constants Executing a PL/SQL Block
|
| WRITING EXECUTABLE STATEMENTS |
» Recognizing the Significance of the Executable Section
» Writing Statements Within the Executable Section
» Describing the Rules of Nested Blocks
» Executing and Testing a PL/SQL Block
|
| INTERACTING WITH THE ORACLE SERVER |
» Writing a Successful SELECT Statement in PL/SQL
» Declaring the Data type and Size of a PL/SQL Variable Dynamically
» Writing Data Manipulation Language (DML) Statements in PL/SQL
|
| WRITING CONTROL STRUCTURES |
» Identifying the Uses and Types of Control Structures
» Constructing an IF Statement
» Constructing and Identifying Different Loop Statements
» Controlling Block Flow Using Nested Loops and Labels
» Using Logic Tables
|
| WORKING WITH COMPOSITE DATA TYPES |
» Creating User-Defined PL/SQL Records
» Creating a PL/SQL Table
» Creating a PL/SQL Table of Records
» Differentiating Among Records, Tables, and Tables of Records
» Use iSQL*Plus to format reports
» Use basic iSQL*Plus commands
» Retrieve, insert, update, and delete data
» Describe relational and object relational database concepts
» Create and maintain database objects
» Control transactions
» Control data and user access
|
| WRITING EXPLICIT CURSORS |
» Using a PL/SQL Record Variable
» Distinguishing Between the Implicit and Explicit Cursor
» Writing a Cursor that Uses Parameters
» Determining When a FOR UPDATE Clause in a Cursor Is Required
» Using a PL/SQL Table Variable
» Using a PL/SQL Table of Records
|
| HANDLING EXCEPTIONS |
» Defining PL/SQL Exceptions
» Recognizing Unhandled Exceptions
» Listing and Using Different Types of PL/SQL Exception Handlers
» Describing the Effect of Exception Propagation in Nested Blocks
» Customizing PL/SQL Exception Messages
|
| CREATING PROCEDURES |
» Describe the uses of procedures
» Create procedures
» Create procedures with arguments
» Invoke a procedure
» Remove a procedure
|
| CREATING FUNCTIONS |
» Describe the uses of functions
» Create a function
» Invoke a function
» Remove a function
» Differentiate between a procedure and a function
|
| MANAGING SUBPROGRAMS |
» Describe system privilege requirements
» Describe object privilege requirements
» Query the relevant data dictionary views
» Debug subprograms
|
| CREATING PACKAGES |
» Describe packages and list their possible components
» Create packages that include public and private subprograms, as well as global and
local variables
» Invoke objects in a package
» Remove packages
» Write packages that use the overloading feature of PL/SQL
|
| USING ORACLE-SUPPLIED PACKAGES |
» Overview of Oracle-supplied packages
» View examples of some supplied packages
» Write dynamic SQL
|
| CREATING DATABASE TRIGGERS |
» Describe different types of triggers
» Describe database triggers and their use
» Create database triggers
» Describe database trigger firing rules
» Drop database triggers
|
| MORE TRIGGER CONCEPTS |
» Create triggers that fire when certain database actions occur
» List some of the limitations of database triggers
» Determine when to use database triggers or Oracle Server features
» Create triggers by using alternative events (not INSERT/UPDATE/DELETE)
» Create triggers by using alternative levels (not STATEMENT/ROW)
» Query the relevant data dictionary views
|