EncartaLabs

PLSQL

( Duration: 5 Days )

PL/SQL is Oracle’s extension language for standard SQL.

In PLSQL training course, delegates familiar with SQL, learn to use PL/SQL to write sophisticated queries against an Oracle database.

By attending PLSQL workshop, delegates will:

  • Understand the PL/SQL Development Environment
  • Learn the basics of the PL/SQL language
  • Learn to declare and work with variables
  • Learn to use conditionals and loops in PL/SQL
  • Learn to handle and create user-defined exceptions
  • Learn to use SQL within PL/SQL
  • Learn about nested blocks and variable scope
  • Learn to create Subprograms, Stored Procedures and Functions
  • Learn to work with Packages, Triggers and Cursors
  • Learn to use Oracle Supplied Packages

  • Experience in Oracle SQL is required:

COURSE AGENDA

1

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 anonymous block structure
  • Subprogram blocks structure
2

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
3

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
4

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
5

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
6

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
7

Writing explicit cursors

  • Using a PL/SQL record variable
  • Distinguishing between implicit and explicit cursor
  • Writing a cursor FOR loop
8

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
9

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
10

Generating Procedures

  • Describe the uses of procedures
  • Create procedures
  • Create procedures with arguments
  • Invoke a procedure
  • Remove a procedure
11

Creating Functions

  • Describe the uses of functions
  • Create a function
  • Invoke a function
  • Remove a function
  • Differentiate between a procedure and a function
12

Managing Subprograms

  • Describe system and object privilege requirements
  • Query the relevant data dictionary views
  • Debug subprograms
13

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
14

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
15

Using Oracle-Supplied Packages

  • Overview of Oracle-supplied packages
  • View examples of some supplied packages
  • Writing dynamic SQL
16

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
17

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
18

Managing Dependencies

  • Overview of object dependencies
  • Manage PL/SQL objects for recompilation
19

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

Encarta Labs Advantage

  • One Stop Corporate Training Solution Providers for over 4,000 Modules on a variety of subjects
  • All courses are delivered by Industry Veterans
  • Get jumpstarted from newbie to production ready in a matter of few days
  • Trained more than 50,000 Corporate executives across the Globe
  • All our trainings are conducted in workshop mode with more focus on hands-on sessions

View our other course offerings by visiting http://encartalabs.com/course-catalogue-all.php

Contact us for delivering this course as a public/open-house workshop/online training for a group of 10+ candidates.

Top