Oracle9i PL/SQL: Basics

Audience

The intended audience for this course are Application Developers Database Administrators System Analysts and Technical Support Professionals. Required prerequisites are Oracle SQL and SQL*Plus and Oracle Basic PL/SQL Suggested prerequisites are Oracle9i SQL and SQL*Plus: Basic SELECT Statements (61130) Oracle9i SQL and SQL*Plus: Data Retrieval Techniques (61131) and Oracle9i SQL and SQL*Plus: DML and DDL (61132).

Objective

• Identify the features of PL/SQL.
• Identify the benefits of PL/SQL.
• Select the actions by which a PL/SQL engine processes a block of code.
• Identify the functions of PL/SQL variables.
• Identify the guidelines for declaring PL/SQL variables.
• Match the PL/SQL datatypes with their descriptions.
• Retrieve data from a table by using the SELECT statement.
• Identify the conditions in which various SELECT exceptions are raised.
• Write the code to add rows to a table by using the INSERT statement.
• Write PL/SQL code using the IFTHENELSE statement.
• Write PL/SQL code using the IFTHENELSIF statement.
• Write PL/SQL code using the CASE expression.
• Declare a PL/SQL record by using the RECORD datatype.
• Reference a PL/SQL record by using the dot notation.
• Declare PL/SQL records with %ROWTYPE.
• Identify the declaration methods of cursors both implicit and explicit.
• Sequence the steps for controlling explicit cursors.
• Write the code to declare a cursor by using the CURSOR statement.
• Match the types of exceptions with their properties.
• Match some common predefined Oracle server exceptions with their descriptions.
• Identify the guidelines to trap exceptions.

Topics Include

Unit 1: PL/SQL: An Introduction

• Identify the features of PL/SQL.
• Identify the benefits of PL/SQL.
• Select the actions by which a PL/SQL engine processes a block of code.
• Identify the features of the sections of a basic PL/SQL block.
• Match the PL/SQL program constructs with their features.
• Identify the syntax rules to be applied in creating a PL/SQL block.

Unit 2: Developing a Simple PL/SQL Block

• Identify the functions of PL/SQL variables.
• Identify the guidelines for declaring PL/SQL variables.
• Match the PL/SQL datatypes with their descriptions.
• Match scalar datatypes with their descriptions.
• Declare a scalar variable in a PL/SQL block.
• Declare a scalar variable with the %TYPE attribute.
• Write the code to assign a value a to a variable by using the assignment operator.
• Manipulate data in PL/SQL variables by using operators.
• Manipulate data in PL/SQL variables by using SQL functions.
• Convert the datatype of a variable by using a data conversion function.
• Identify the features of variable scoping in nested PL/SQL blocks.
• Declare a bind variable in the iSQL*Plus environment.
• Identify the case conventions for writing PL/SQL code.
• Match the identifiers with their naming conventions.

Unit 3: Accessing the Database Using PL/SQL

• Retrieve data from a table by using the SELECT statement.
• Identify the conditions in which various SELECT exceptions are raised.
• Write the code to add rows to a table by using the INSERT statement.
• Write the code to modify the existing data in a table by using the UPDATE statement.
• Write the code to delete data from a table by using the DELETE statement.
• Identify the features of a SQL cursor.
• Match the SQL cursor attributes with their descriptions.
• Write the code to confirm the current transaction by using the COMMIT command.
• Write the code to discard the changes made to the table by using the ROLLBACK command.
• Write the code to control the transaction at the intermediate point by using the SAVEPOINT command.
• Merge data from one table with that of another table by using the MERGE statement.

Unit 4: Controlling Flow in PL/SQL Blocks

• Write PL/SQL code using the IF-THEN-ELSE statement.
• Write PL/SQL code using the IF-THEN-ELSIF statement.
• Match a condition that uses logical operators with its result.
• Write the code for a basic loop to insert records into a table by using the LOOP keyword.
• Write the code to execute a set of statements repeatedly by using the FOR LOOP keyword.
• Write the code to execute a set of statements repeatedly by using the WHILE LOOP.
• Identify the features of a nested loop.
• Write PL/SQL code using the CASE expression.

Unit 5: Composite Datatypes

• Declare a PL/SQL record by using the RECORD datatype.
• Reference a PL/SQL record by using the dot notation.
• Declare PL/SQL records with %ROWTYPE.
• Declare an INDEX BY table by using the TABLE datatype.
• Reference an INDEX BY table by using a primary_key_value.
• Match the INDEX BY table methods with their descriptions.
• Identify the syntax to reference a table of records.
• Match the LOB datatypes with their descriptions.
• Identify the features of LOB variables in PL/SQL.

Unit 6: Explicit Cursors

• Identify the declaration methods of cursors both implicit and explicit.
• Sequence the steps for controlling explicit cursors.
• Write the code to declare a cursor by using the CURSOR statement.
• Write the code to open a cursor by using the OPEN statement.
• Retrieve rows from a cursor by using the FETCH statement.
• Close a cursor by using the CLOSE statement.
• Check the status of a cursor by using the %ISOPEN attribute.
• Check the status of a cursor by using the %FOUND attribute.
• Check the status of a cursor by using the %NOTFOUND attribute.
• Write the code to fetch a specified number of rows from a cursor by using the %ROWCOUNT attribute.
• Write the code to process the rows of the active set conventionally by fetching values into a PL/SQL record.
• Write the code to process rows in an explicit cursor using cursor FOR loops.
• Pass parameters to a cursor when a cursor is opened by using the cursor_name parameter.
• Lock the records by using the FOR UPDATE clause.
• Write the code to update the latest fetched row by using the WHERE CURRENT OF clause.

Unit 7: Handling Exceptions

• Match the types of exceptions with their properties.
• Match some common predefined Oracle server exceptions with their descriptions.
• Identify the guidelines to trap exceptions.
• Complete the code to trap predefined exceptions.
• Complete the code to trap nonpredefined exceptions.
• Complete the code to trap user-defined exceptions.
• Match the functions for identifying the associated error message or error code with their descriptions.
• Match each calling environment with the error-handling method.
• Raise user-defined error codes and messages by using the RAISE_APPLICATION_ERROR procedure.

Duration

8

Minimum Requirements

The CDROM version of this course requires:

• At least a 486DX 33Mhz CPU.
• Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
• At least 8MB RAM.
• At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).
• At least a double speed CDROM drive.
• An MPC compliant sound card with attached speakers or headphones is recommended (Currently only the CDROM version supports audio).

• At least a 486DX 33Mhz CPU.
• Microsoft Windows 3.1 or higher and a Microsoft compatible mouse.
• At least 8MB RAM and 22MB available hard disk space or file server space.
• At least VGA graphics capability with a minimum 512K video RAM (1MB video RAM recommended).

Media