Oracle PL/SQL: Basics

Audience

Application Developers Database Administrators System Analysts and Technical Support Professionals. Prior to taking this course students should have knowledge equivalent to the computer based training (CBT) courses entitled: Oracle SQL and SQL*Plus: Basic SELECT Statements (course 61110) Oracle SQL and SQL*Plus: DML and DDL (61112) and Oracle 8i.

Objective

• Identify the features and benefits of PL/SQL and understand the PL/SQL program constructs.
• Identify the PL/SQL datatypes
• Identify how to declare PL/SQL variables and identify the PL/SQL programming conventions.
• Retrieve and manipulate data from the database by using PL/SQL commands
• Identify features and attributes of SQL cursors and manage transactions.
• Use the IFTHENELSE and IFTHENELSIF conditional constructs and the Basic loop FOR LOOP and WHILE LOOP looping constructs to control flow in PL/SQL code.
• Declare and reference PL/SQL records and PL/SQL tables
• Identify the types and features of LOB datatypes.
• Understand cursors how to operate explicit cursors work with explicit cursor attributes
• Use the advanced functionality of explicit cursors.
• Understand the basic concepts of exceptions trap predefined nonpredefined and userdefined exceptions
• Understand how exceptions are propagated.

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 the scalar datatypes with their descriptions.
• Declare a scalar variable.
• Declare a scalar variable with the %TYPE attribute.
• Write the code to assign a value a to a variable by using the assignment operator.
• Use operators with the PL/SQL variables.
• 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.
• Create a bind variable in the SQL*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..INTO 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.

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.

Unit 5: Composite Datatypes

• Declare a PL/SQL record by using the RECORD datatype.
• Reference a PL/SQL record.
• Declare PL/SQL records with %ROWTYPE.
• Declare a PL/SQL table by using the TABLE datatype.
• Reference a PL/SQL table.
• Match the methods used to operate on PL/SQL tables 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 its error-handling method.
• Use the RAISE_APPLICATION_ERROR procedure to raise user-defined error codes and messages.

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

CDROM