Oracle 9i: Program with PL/SQL
Student handout
CALL US: 020 8795 1200
Exam: Introduction to Oracle9i: SQL
Duration: 10 days Flexible (5 days a week or 5 weekends)
Fees: £1,200 ( + VAT)
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.
This course counts towards the Hands-on course requirement for the Oracle 9i Database Administrator Certification. Only instructor-led in class or instructor-led online formats of this course will meet the Certification Hands-on Requirement. Self Study CD-Rom and Knowledge Center courses are excellent study and reference tools but DO NOT meet the Hands-on Requirement for certification.
Audience:
- Database Administrators
- System Analysts
- Data Analysts
- Application support Specialists
- Accountants
Prerequisites:
Required Prerequisites:
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:
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
- 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
CALL US FOR MORE DETAILS ON: 020 8795 1200
