About Partners Contact Management Site Map   
  Oracle 11i Financials    
  Oracle 10g DBA    

Other Modules

    Introduction to Oracle SQL
    Oracle Forms Developer: Build Internet Applications

What you will learn:
This course introduces students to PL/SQL and helps them understand the benefits of this powerful programming language. In the class, students learn to create PL/SQL blocks of application code that can be shared by multiple forms, reports, and data management applications. Students learn to create procedures, functions, packages, and database triggers.

Students use iSQL*Plus to develop these program units. Students also learn to manage PL/SQL program units and database triggers, to manage dependencies, to manipulate large objects, and to use some of the Oracle-supplied packages.

Required Prerequisites:
        Working with iSQL*Plus
        Introduction to Oracle9i: SQL

Course Objectives:
        Create, execute, and maintain procedures, functions, packages, and triggers
        Manage dependencies
        Manipulate large objects
        Conditionally control code flow (loops, control structures, and explicit cursors)
        Describe Oracle-supplied packages
        Handle runtime errors
        Manage program constructs
        Describe the features and syntax of PL/SQL

Course Topics:
        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
        Using Coding Conventions

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
        Controlling Transactions in PL/SQL
        Determining the Outcome of SQL DML Statements

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

Writing Explicit Cursors
        Using a PL/SQL Record Variable
        Distinguishing Between the Implicit and Explicit Cursor
        Writing a Cursor FOR Loop

Advanced Explicit Cursor Concepts
        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
        Trapping Unanticipated Errors
        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

More Package Concepts
        Write packages that use the overloading feature of PL/SQL
        Avoid errors with mutually referential subprograms
        Initialize variables with a one-time-only procedure
        Specify the purity level of packaged functions
        Describe the persistent state of packaged variables, cursors, tables, and records
        Query the relevant data dictionary views

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 (notINSERT/UPDATE/DELETE)
        Create triggers by using alternative levels (not STATEMENT/ROW)
        Query the relevant data dictionary views

Managing Dependencies
        Overview of object dependencies
        Manage PL/SQL objects for recompilation

Manipulating Large Objects
        Compare and contrast LONG/RAW/LONG RAW with large objects (LOBs)
        Understand LOBs
        Manage binary large file objects (BFILEs)
        Use PL/SQL with an LOB
        Create a table with LOB columns
        Manipulate LOBs
        Use DBMS_LOB Oracle-supplied packages
        Create a temporary LOB

Suggested Next Course:
        Oracle9i Forms Developer: Build Internet Applications

GO TO TOP            
Comments, Suggestions, Requests: info@baseltd.com Cell:+8801713144220.
Copyright; 2006 @ BASE Limited.