Oracle 12c Release 2 PL/SQL (Three Days)
Course: OR12R2P3
Duration: 3 Days
Level: I
On our website at:
http://www.verhoef-training.com/courses/OR12R2P3.html
Course Summary
This course provides a complete, hands-on, comprehensive introduction to PL/SQL including the use of both SQL Developer and SQL*Plus. This coverage is appropriate for both users of Oracle12c and Oracle11g.
Topics Covered In This Course
CHAPTER 1 -- PL/SQL PROGRAM STRUCTURE
C*******************************************************
PL/SQL VS. SQL
PL/SQL ENGINES AVAILABLE
ANONYMOUS PL/SQL BLOCK STRUCTURE
OBJECT NAMING RULES
VARIABLE DECLARATIONS
AVAILABLE DATATYPES
- Scalar Datatypes
- Using Extended Datatypes
- Object Types
EXECUTABLE STATEMENTS
EXPRESSIONS
BLOCK LABELING
VARIABLE SCOPING RULES
COMMENTS IN PROGRAMS AND SCRIPTS
BASIC CODING STANDARDS
LAB 1: PL/SQL PROGRAM STRUCTURE
LAB 1 SOLUTIONS: PL/SQL PROGRAM STRUCTURE
C**********************************************
CHAPTER 2 -- PL/SQL FLOW CONTROL
C**********************************************
CONDITIONAL CONTROL
COMPARISON OPERATORS
LOGICAL OPERATORS
REPETITION CONTROL
- The Simple Loop
- WHILE Loop
- FOR Loop
- CONTINUE Statements In Loops
- Step Loops
THE GOTO STATEMENT
CASE EXPRESSIONS / STATEMENTS
BIND VARIABLES
SUBSTITUTION VARIABLES
LAB 2: PL/SQL FLOW CONTROL
LAB 2 SOLUTIONS: PL/SQL FLOW CONTROL
C*******************************************************
CHAPTER 3 -- SQL DEVELOPER AND PL/SQL
C*******************************************************
SQL DEVELOPER AND PL/SQL
CREATING AND EXECUTING SCRIPTS
LAB 3: SQL DEVELOPER AND PL/SQL
LAB 3 SOLUTIONS: SQL DEVELOPER AND PL/SQL
C***********************************
CHAPTER 4 -- SELECT INTO
C***********************************
SELECTING SINGLE ROWS OF DATA
ANCHORING VARIABLES TO DATATYPES
DML IN PL/SQL
RETURNING ? INTO
SEQUENCES IN PL/SQL
TRANSACTION CONTROL IN PL/SQL
AUTONOMOUS TRANSACTIONS
LAB 4: SELECT INTO
LAB 4 SOLUTIONS: SELECT INTO
C******************************************
CHAPTER 5 -- THE PL/SQL CURSOR
C*******************************************
DECLARING EXPLICIT CURSORS
OPENING AND CLOSING EXPLICIT CURSORS
USING EXPLICIT CURSORS TO RETRIEVE VALUES
EXPLICIT CURSOR ATTRIBUTES
USING A LOOP WITH AN EXPLICIT CURSOR
USING %ROWTYPE WITH CURSORS
THE CURSOR FOR LOOP
DBMS_OUTPUT
LAB 5: THE PL/SQL CURSOR
LAB 5 SOLUTIONS: THE PL/SQL CURSOR
C***********************************
CHAPTER 6 -- OPTIMIZATION
C***********************************
TIMING PL/SQL
FOR UPDATE / WHERE CURRENT OF
LAB 6: OPTIMIZATION
LAB 6 SOLUTIONS: OPTIMIZATION
C*******************************************************
CHAPTER 7 -- PL/SQL EXCEPTION HANDLING
C*******************************************************
THE EXCEPTION SECTION
ORACLE NAMED EXCEPTIONS
PRAGMA EXCEPTION_INIT
USER DEFINED EXCEPTIONS
- The Scope Of User-Defined Exceptions
RAISING NAMED EXCEPTIONS
EXCEPTION PROPAGATION
RAISING AN EXCEPTION AGAIN
LIFE AFTER AN EXCEPTION
WHEN OTHERS
TAKING YOUR BALL AND GOING HOME
DBMS_ERRLOG
LAB 7: PL/SQL EXCEPTION HANDLING
LAB 7 SOLUTIONS: PL/SQL EXCEPTION HANDLING
C**********************************************
CHAPTER 8 -- STORED PROCEDURES
C**********************************************
PROCEDURES
BENEFITS OF STORED PROCEDURES
- Database Security
- Performance
- Productivity
- Portability
PARAMETERS AND STORED PROCEDURES
STORED OBJECT CREATION
- Syntax For Creating A Procedure
COMPILATION ERRORS
VIEWING COMPILED CODE
DROPPING A PROCEDURE
THE ALTER COMMAND AND STORED PROCEDURES
LAB 8: STORED PROCEDURES
LAB 8 SOLUTIONS: STORED PROCEDURES
C**********************************************************
CHAPTER 9 -- CREATING FUNCTIONS IN PL/SQL
C**********************************************************
FUNCTIONS
PURITY LEVELS
USING WHITE LISTS
OPTIMIZATIONS
PARALLEL_ENABLE
DETERMINISTIC FUNCTIONS
PL/SQL RESULT CACHE
NOCOPY
DBMS_OUTPUT IN FUNCTIONS
USING THE WITH CLAUSE FOR FUNCTIONS
PRAGMA UDF
PRAGMA INLINE
THE IMPACT OF DATA-BOUND COLLATION
USING SQL DEVELOPER WITH STORED PROCEDURES
DEBUGGING
LAB 9: FUNCTIONS
LAB 9 SOLUTIONS: FUNCTIONS
C********************************
CHAPTER 10 -- PACKAGES
C********************************
CREATING PACKAGES
PACKAGE BENEFITS
- Security
- Persistent State
- I/O Efficiency
A SIMPLE PACKAGE
OVERLOADING
BODILESS PACKAGES
SOURCE CODE ENCRYPTION
CREATING PACKAGES FROM PROCEDURES AND FUNCTIONS
LAB 10: PACKAGES
LAB 10 SOLUTIONS: PACKAGES
C***************************************************
CHAPTER 11 -- CREATING DML TRIGGERS
C***************************************************
DML TRIGGERS
DML TRIGGER STRUCTURE
CONDITIONAL TRIGGERING PREDICATES
TRIGGERS FOR BUSINESS RULES ENFORCEMENT
MUTATING AND CONSTRAINING TABLES
COMPOUND TRIGGERS
CONTROLLING FIRING ORDER
DDL FOR TRIGGERS
VIEWING TRIGGER SOURCE
INSTEAD OF TRIGGERS
LAB 11: DML TRIGGERS
LAB 11 SOLUTIONS: DML TRIGGERS
C***********************************************
CHAPTER 12 -- ADVANCED CONCEPTS
C***********************************************
EMBEDDED PROCEDURES
THE OPTIMIZING COMPILER
PL/SQL COMPILER WARNINGS
COMPILING FOR DEBUGGING
CONDITIONAL COMPILATION / INQUIRY DIRECTIVES
- Error Directives
- Inquiry Directives
- Using Static Constants
DBMS_DB_VERSION
NATIVE COMPILATION
- Recompiling All Database Objects
LAB 12: ADVANCED CONCEPTS
LAB 12 SOLUTIONS: ADVANCED CONCEPTS
C*****************************************
CHAPTER 13 -- FILE OPERATIONS
C*****************************************
MOVING FILES BETWEEN DATABASES
DIRECTORY ACCESS
FILE MANIPULATION
- FCLOSE Procedure
- FCLOSE_ALL Procedure
- FCOPY Procedure
- FFLUSH Procedure
- FGETATTR Procedure
- FGETPOS Function
- FOPEN Function
- FREMOVE Procedure
- FRENAME Procedure
- FSEEK Procedure
- GET_LINE Procedure
- GET_RAW Procedure
- IS_OPEN Function
- NEW_LINE Procedure
- PUT Procedure
- PUT_LINE Procedure
- PUTF Procedure
- PUT_RAW Procedure
LAB 13: FILE OPERATIONS
LAB 13 SOLUTIONS: FILE OPERATIONS
C************************************
CHAPTER 14 -- COLLECTIONS
C************************************
DEFINING RECORDS
COLLECTIONS
- Associative Arrays
- Nested Tables
- VARRAYs / VARYING ARRAYs
- Assignments
- Comparing Collections
COLLECTION METHODS
- EXISTS
- FIRST
- LAST
- COUNT
- LIMIT
- PRIOR
- NEXT
- DELETE
- TRIM
SET THEORY AND NESTED TABLES
LAB 14: COLLECTIONS
LAB 14 SOLUTIONS: COLLECTIONS
C******************************************
CHAPTER 15 -- BULK OPERATIONS
C******************************************
BULK BINDING
FORALL
SQL%BULK_ROWCOUNT
SAVE EXCEPTIONS / SQL%BULK_EXCEPTIONS
BULK COLLECT
- The LIMIT Clause
- FORALL And The INDICES OF Clause
- FORALL And VALUES OF
PIPELINED TABLE FUNCTIONS
MULTIDIMENSIONAL COLLECTIONS
LAB 15: BULK OPERATIONS
LAB 15: SOLUTIONS: BULK OPERATIONS
Who Should Take This Course
This course is appropriate for anyone needing to understand Oracle?s proprietary programming language. That would include end users, business analysts, application developers and database administrators.
Recommended Prerequisites
Oracle12c SQL or equivalent experience is required.
Related Courses
Code |
Course Title |
Duration |
Level |
|
OR12R2S5 |
Oracle12c Release 2 SQL (Five Days) |
5 Days |
I |
Details |
OR12R2D5 |
Oracle12c Release 2 DBA I (Five Days) |
5 Days |
I |
Details |
OR12R2D3 |
Oracle12c Release 2 DBA I (Three Days) |
3 Days |
I |
Details |
OR12R2SQ |
Oracle12c Release 2 SQL (Three Days) |
3 Days |
I |
Details |
Every student attending a Verhoef Training class will receive
a certificate good for $100 toward their next public class taken
within a year.
You can also buy "Verhoef Vouchers" to get a discounted rate for a
single student in any of our public or web-based classes.
Contact your account manager or our sales office for details.