PLSQL

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 ananymous block structure.

Subprogram block structure.

• 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
• 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 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.

Generating 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 and 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 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.
• Writing 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.

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 Courses