Oracle SQL

Oracle PL/SQL

Oracle Forms

Oracle Reports

Residential Courses

Prices

Location

Oracle PL/SQL: Basics

Computer based training course

Course ID: 61113

Course Overview

This course introduces the learner to the basics of PL/SQL. The course builds the necessary skills for using Oracle PL/SQL. It will help the learner acquire the expertise to design, develop, and implement Oracle Server applications by using Oracle Structured Query Language (SQL) and PL/SQL. The learner is guided step-by-step through the various aspects of PL/SQL programming. Numerous easy-to-understand examples ensure that the concepts are amply illustrated.

Learn To:

• 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 IF-THEN-ELSE and IF-THEN-ELSIF 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 user-defined exceptions
• Understand how exceptions are propagated.

Content Emphasis

Skills-Based

Audience

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

Total Learning Time

6 - 8 Hour(s)

Course Contents

Unit 1: PL/SQL: An Introduction

Duration: 0.5 - 2 Hour(s)

• 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

Duration: 0.5 - 1 Hour(s)

• 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

Duration: 1 Hour(s)

• 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

Duration: 1 Hour(s)

• 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

Duration: 1 Hour(s)

• 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

Duration: 1 Hour(s)

• 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

Duration: 1 Hour(s)

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

Anglesey has over 125 miles of coastline designated "areas of Natural Outstanding Beauty".

Tyn Y Felin is a mere five minutes away from the A55 North Wales Coast Expressway and is a perfectly situated coastal retreat on the serene and beautiful Isle of Anglesey.