Duration
28 hours (usually 4 days including breaks)
Requirements
- Basic skills in any programming language, object-oriented structural or
- The free use of the basics of SQL and knowledge of Oracle database environment (preferably Oracle 11g completion of training – Native SQL for Programmers – Workshops)
Overview
For who
Workshops are dedicated to developers, end users and administrators, who until now have had no contact with the language PL / SQL, and the need to exploit its huge potential in working with a database, automate processes, and in building applications
Exams and Certificates
The plan covers the training material required to pass the exam 1Z0-144 Oracle Database 11g Program with PL / SQL and obtain the title of Oracle PL / SQL Developer Certified Associate
Purpose of training
The workshop aims to familiarize participants with the programming language PL / SQL, its capabilities and limitations. This workshop will include a full understanding of the mechanisms involved in the programming language PL / SQL needed to implement the logic of the free applications, automation of data processing and database management.
The content of the training
- Introduction to PL / SQL architecture solutions based on this language, the organization of the working environment
- Create scripts and stored program units that operate on data
Notes
The workshops are based on the software version 11g XE
Course Outline
Introduction to PL / SQL
- Runtime Environment
- Construction and types of PL / SQL blocks
- Declaring and using variables
- Control statements, decisions, loops
SQL statements in PL / SQL
- DML commands
- DDL and dynamic SQL
- TCL commands and transactional
- SELECT
Procedures and Functions
- Create and delete
- Parameterization
- Passing parameters by value and reference, nocopy
Handling errors and exceptions
Create and use cursors
- Records
- static cursors
- Parameterizing cursors
- Cursor FOR UPDATE
Associative arrays
Packages
- The structure of the package: the specification and body
- Section Initialization and global variables, memory management, an instance of the package
- Encapsulation, overloading programs, pre-declaration procedures
Triggers
- DML triggers
- The triggers Drives (INSTEAD OF)
- The triggers system
- New features in Oracle 11g triggers
Examples of application packages built
- Writing to a file through UTL_FILE
- Sending e-mails
Management code and compiler
- Encryption code (dynamic obfuscation, wrap)
- conditional Compilation
- The relationships between objects
- Warnings
Duration
21 hours (usually 3 days including breaks)
Requirements
The course is only suitable for those who have some knowledge of SQL.
Previous experience with an interactive computer system is desirable but not essential.
Overview
This 3 day course gives an introduction to ORACLE PL/SQL, an application development environment that enables the writing of stored procedures, functions and triggers using both SQL and PL/SQL commands.
The course takes the format of a workshop, with a mix of lecture, working examples and practical exercises. Although the content may be customised, at least 2 days are needed to cover the core elements.
Full course notes are provided along with sample database files, example SQL files and free software tools for use in accessing an ORACLE database.
Course Outline
Introduction
- Aims and Objectives
- Course Schedule
- Introductions
- Pre-requisites
- Responsibilities
SQL Tools
- Objectives
- SQL Developer
- SQL Developer – Connection
- Viewing Table Information
- Using SQL, SQL Developer – Query
- SQL*Plus Login
- Direct Connection
- Using SQL*Plus
- Ending the Session
- SQL*Plus Commands
- SQL*Plus Environment
- SQL*Plus Prompt
- Finding Information about Tables
- Getting Help
- Using SQL Files
- iSQL*Plus, Entity Models
- The ORDERS Tables
- The FILM Tables
- Course Tables Handout
- SQL Statement Syntax
- SQL*Plus Commands
What is PL/SQL?
- What is PL/SQL?
- Why Use PL/SQL?
- Block Structure
- Displaying a Message
- Sample Code
- Setting SERVEROUTPUT
- Update Example, Style Guide
Variables
- Variables
- Datatypes
- Setting Variables
- Constants
- Local and Global Variables
- %Type Variables
- Substitution Variables
- Comments with &
- Verify Option
- && Variables
- Define and Undefine
SELECT Statement
- SELECT Statement
- Populating Variables
- %Rowtype Variables
- CHR Function
- Self Study
- PL/SQL Records
- Example Declarations
Conditional Statement
- IF Statement
- SELECT Statement
- Self Study
- Case Statement
Trapping Errors
- Exception
- Internal Errors
- Error Code and Message
- Using No Data Found
- User Exceptions
- Raise Application Error
- Trapping Non-defined Errors
- Using PRAGMA EXCEPTION_INIT
- Commit and Rollback
- Self Study
- Nested Blocks
- Workshop
Iteration – Looping
- Loop Statement
- While Statement
- For Statement
- Goto Statement and Labels
Cursors
- Cursors
- Cursor Attributes
- Explicit Cursors
- Explicit Cursor Example
- Declaring the Cursor
- Declaring the Variable
- Open, Fetching the First Row
- Fetching the Next Row
- Exit When %Notfound
- Close
- For Loop I
- For Loop II
- Update Example
- FOR UPDATE
- FOR UPDATE OF
- WHERE CURRENT OF
- Commit with Cursors
- Validation Example I
- Validation Example II
- Cursor Parameters,
- Workshop
- Workshop Solution
Procedures, Functions and Packages
- Create Statement
- Parameters
- Procedure Body
- Showing Errors
- Describe a Procedure
- Calling Procedures
- Calling Procedures in SQL*Plus
- Using Output Parameters
- Calling with Output Parameters
- Creating Functions
- Example Function
- Showing Errors
- Describe a Function
- Calling Functions
- Calling Functions in SQL*Plus
- Modular Programming
- Example Procedure
- Calling Functions
- Calling Functions In An IF Statement
- Creating Packages
- Package Example
- Reasons for Packages
- Public and Private Sub-programs
- Showing Errors
- Describe a Package
- Calling Packages in SQL*Plus
- Calling Packages From Sub-Programs
- Dropping a Sub-Program
- Finding Sub-programs
- Creating a Debug Package
- Calling the Debug Package
- Positional and Named Notation
- Parameter Default Values
- Recompiling Procedures and Functions
- Workshop
Triggers
- Creating Triggers
- Statement Triggers
- Row Level Triggers
- WHEN Restriction
- Selective Triggers – IF
- Showing Errors
- Commit in Triggers
- Restrictions
- Mutating Triggers
- Finding Triggers
- Dropping a Trigger
- Generating an Auto-number
- Disabling Triggers
- Enabling Triggers
- Trigger Names
Sample Data
- ORDER Tables
- FILM Tables
- EMPLOYEE Tables
Dynamic SQL
- SQL in PL/SQL
- Binding
- Dynamic SQL
- Native Dynamic SQL
- DDL and DML
- DBMS_SQL Package
- Dynamic SQL – SELECT
- Dynamic SQL – SELECT Procedure
Using Files
- Using Text Files
- UTL_FILE Package
- Write/Append Example
- Read Example
- Trigger Example
- DBMS_ALERT Packages
- DBMS_JOB Package
COLLECTIONS
- %Type Variables
- Record Variables
- Collection Types
- Index-By Tables
- Setting Values
- Nonexistent Elements
- Nested Tables
- Nested Table Initialisation
- Using the Constructor
- Adding to a Nested Table
- Varrays
- Varray Initialization
- Adding Elements to a Varray
- Multilevel Collections
- Bulk Bind
- Bulk Bind Example
- Transactional Issues
- BULK COLLECT Clause
- RETURNING INTO
Ref Cursors
- Cursor Variables
- Defining REF CURSOR Types
- Declaring Cursor Variables
- Constrained and Unconstrained
- Using Cursor Variables
- Cursor Variable Examples
