Search for Training

PLSQL

Course Overview


PL/SQL is Oracle's extension language for standard SQL. In this PL/SQL training class, students who already know SQL, learn to use PL/SQL to write sophisticated queries against an Oracle database.

Course Content


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

Customer Reviews


Thanks to Xpertised and the tutor who walked me through all the topics with Practical exposure which is helping me in my current project.
-Waseem

Course was quite helpful in terms of understanding of concepts and practicality. Its really a very friendly environment to learn. The timing were mutually chosen, as we both are working professional. I am quite satisfied with the course.
-Tanmoy

...more
Share:

For Batch Details
Call us at: +91 7259222234

Not sure? Consult Our Experts

What is the sum of 4 + 3? (security question)

Looking for a Training for

Myself

My Team/Organization

I agree to be contacted over mail or phone

or
Call us at: +91 7259222234

Subscribe to our weekly newsletter