Develop the skills to effectively utilize Oracle’s programming language, PL/SQL

Print Outline in PDF format

Course Description:

This course is a lab intensive class where participants will gain a greater understanding and command of Oracle’s programming language PL/SQL. Focus will be on effectively utilizing programmer-defined packages and programmer-defined “built-in” functions to use in SQL statement processing.

You will cover ...

• How to work with triggers

• Mutating and Constraining Tables

• Module Overloading

• Cursor Attributes

• Recursion

• “Packaged” Variables

• User-defined records

Who Needs to Attend:

    Application developers, designers and database administrators interested in furthering their mastery of PL/SQL concepts and techniques.

Prerequisites:

    Oracle Foundation Classes (SQL Basics & SQL Plus and PL/SQL) or equivalent experience

Course Labs:

1. Triggers

2. Cursor attributes

3. PL/SQL tables

4. Cursor variables

5. UTL_File packages

6. DBMS_SQL packages

7. DBMs_Alert packages

8. DBMS_Pipe packages

9. PL/SQL wrappers

Course Content:

TRIGGERS

• Old and new

• For each row

• Update column list

• Multistatement triggers

MUTATING AND CONSTRAINING TABLES

• Tables

• Enable/Disable

“PACKAGED” VARIABLES

MODULE OVERLOADING

RECURSION

USER-DEFINED RECORDS

CURSOR ATTRIBUTES

• Explicit cursor attributes

• Implicit cursor attributes

PL/SQL TABLES

CURSOR VARIABLES

• Variables

• Open-for

• Fetch and close

• Taking advantage of the weak cursor variable

DBMS_OUTPUT PACKAGE

• DBMS_Output package (buffer I/O)

• Put_Line, Put and New_Line

UTL_FILE PACKAGE

• UTL_File package (FileI/O)

• Exceptions

DBMS_SQL PACKAGE

• DBMS_SQL package (Dynamic SQL)

DBMS_ALERT PACKAGE

• DBMS_Alert package (notification of database events)

DBMS_PIPE PACKAGE

• DBMS_Pipe package (communication between sessions)

DBMS_JOB PACKAGE

• DBMS_Job package (job scheduling)

• Terminating a job

• Viewing job queue information

PL/SQL WRAPPER

• PL/SQL Wrapper (source code encryption)

• Let’s wrap

LAYOUTS OF DEMO TABLES

PL/SQL CONTROL STRUCTURES

ORACLE FUNCTIONS