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.
Audience: This course is appropriate for anyone needing to understand Oracle’s proprietary programming language. That would include business analysts, application developers and database administrators.
Course Duration: 5 days
Prerequisites:
Oracle12c SQL or equivalent experience is required.
Course Outline:
- PL/SQL Program Structure
- 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 One – PL/SQL Program Structure
- Lab One Solutions – PL/SQL Program Structure
- PL/SQL Flow Control
- Conditional Control
- Comparison Operators
- Logical Operators
- Truth Tables
- 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 Two – PL/SQL Flow Control
- Lab Two Solutions – PL/SQL Flow Control
- SQL Developer and PL/SQL
- SQL developer and PL/SQL
- Creating and Executing Scripts
- Lab Three – SQL Developer and PL/SQL
- Lab Three Solutions – SQL Developer and PL/SQL
- Select Into
- 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 Four – Select Into
- Lab Four Solutions – Select Into
- The PL/SQL Cursor
- 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 Five – The PL/SQL Cursor
- Lab Five Solutions – The PL/SQL Cursor
- Optimization
- Timing PL/SQL
- FOR UPDATE / WHERE CURRENT OF
- Lab Six – Optimization
- Lab Six Solutions – Optimization
- PL/SQL Exception Handling
- The Exception Section
- Oracle Named Exceptions
- Pragma EXCEPTION_INIT
- User Defined Exceptions
- The Scope 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 Seven – PL/SQL Exception Handling
- Lab Seven Solutions – PL/SQL Exception Handling
- Stored Procedures
- Procedures
- Benefits of Stored Procedures
- Database Security
- Performance
- Productivity
- Portability
- Parameters and Stored Procedures
- Parameter Notation
- Stored Object Creation
- Syntax for Creating a Procedure
- Compilation Errors
- Viewing Compiled Code
- Dropping a Procedure
- The Alter Command and Stored Procedures
- Lab Eight – Stored Procedures
- Lab Eight Solutions – Stored Procedures
- Creating Functions in PL/SQL
- 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
- Using SQL Developer with Stored Procedures
- Debugging
- Lab Nine Functions
- Lab Nine Solutions – Functions
- Packages
- 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
- Creating DML Triggers
- 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
- Advanced Concepts
- 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
- File Operations
- 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
- Communications
- DBMS_ALERT
- REGISTER Procedure
- REMOVE Procedure
- REMOVEALL Procedure
- SET_DEFAULTS Procedure
- SIGNAL Procedure
- WAITANY Procedure
- WAITONE Procedure
- Security
- DBMS_ALERT Example
- DBMS_PIPE
- CREATE_PIPE Function
- NEXT_ITEM_TYPE Function
- PACK_MESSAGE Procedure
- PURGE Procedure
- RECEIVE_MESSAGE Function
- RESET_BUFFER Procedure
- REMOVE_PIPE Function
- SEND_MESSAGE Function
- UNIQUE_SESSION_NAME Function
- UNPACK_MESSAGE Procedure
- DBMS_PIPE Example
- UTL_SMTP
- UTL_HTTP
- UTL_TCP
- UTL_MAIL
- DBMS_NETWORK_ACL_ADMIN
- Lab 14 – Communications
- Lab 14 Solutions – Communications
- DBMS_ALERT
- Security
- DBMS_RLS
- Implementation Tasks
- Our Example Scenario
- The Policy Package
- The Dynamic Predicate Package
- Creating the Context and the Trigger
- Using DBMS_RLS
- Exempting Policies
- DBMS_FGA
- Controlling Column Access with Virtual Columns
- Lab 15 – Security
- Lab 15 Solutions – Security
- DBMS_RLS
- Scheduling
- Oracle Scheduling
- DBMS_JOB
- PROCEDURE BROKEN
- PROCEDURE CHANGE
- PROCEDURE INSTANCE
- PROCEDURE INTERVAL
- PROCEDURE ISUBMIT
- FUNCTION IS_JOBQ
- PROCEDURE NEXT_DATE
- PROCEDURE REMOVE
- PROCEDURE RUN
- PROCEDURE SUBMIT
- PROCEDURE USER_EXPORT
- PROCEDURE USER_EXPORT
- PROCEDURE WHAT
- The NEXT_DATE Parameter
- The INTERVAL Parameter
- The WHAT Parameter
- DBMS_SCHEDULER
- Evaluating Calendaring Expressions
- DBMS_SCHEDULER Capabilities
- Lab 16 – Scheduling
- Lab 16 Solutions – Scheduling
- Miscellaneous Packages
- DBMS_OUTPUT
- DBMS_RANDOM
- STRING Function
- VALUE Function
- DBMS_STATS
- GATHER_SCHEMA_STATS
- GATHER_TABLE_STATS
- DBMS_WM
- DBMS_METADATA
- DBMS_REDEFINITION
- Other Notable Packages / Procedures
- Lab 17 – Miscellaneous Packages
- Lab 17 Solutions – Miscellaneous Packages
- Database Triggers
- Permissions Needed
- DDL Triggers
- SERVERERROR Triggers
- LOGON / LOGOFF Triggers
- STARTUP / SHUTDOWN Triggers
- SUSPEND Triggers
- Lab 18 – Database Triggers
- Lab 18 Solutions – Database Triggers
- Collections
- 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 19 – Collections
- Lab 19 Solutions – Collections
- Bulk Operations
- 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 20 – Bulk Operations
- Lab 20 Solutions – Bulk Operations
- Cursor Variable
- What Is a Cursor Variable?
- Cursor Variable Control
- OPEN-FOR
- Fetch
- Close
- Cursor Variable Examples
- ROWTYPE_MISMATCH
- Lab 21 – Cursor Variables
- Lab 21 Solutions – Cursor Variables
- Dynamic SQL
- Standard Dynamic SQL
- New DBMS_SQL Functionality
- Native Dynamic SQL (NDS)
- Execute Immediate
- OPEN FOR, FETCH and CLOSE
- Additional Rules for Native Dynamic SQL
- BULK EXECUTE IMMEDIATE
- SQL Injection Attacks
- SQL Statement Manipulation
- Additional Statement Insertion
- The USING Clause vs. Concatenation
- DBMS_ASSERT
- Switching Between NDS and DBMS_SQL
- Lab 22 – Dynamic SQL
- Lab 22 Solutions – Dynamic SQL
- Large Objects
- External vs. Internal Large Objects
- Initializing Internal Lob Locators
- The DBMS_LOB Package
- Inserting Internal Large Objects
- Loading Errors
- The SECUREFILE Option
- Lab 23 – Large Objects
- Lab 23 Solutions – Large Objects
- Object-Oriented Programming
- Oracle Objects
- Ordering Object Types
- Object Tables
- SQL for Object Tables
- Advanced Object Concepts
- Lab 24 – Object-Oriented Programming
- Lab 24 Solutions – Object-Oriented Programming
- Java and Other Languages
- Alternative Languages
- Loading Code
- Publishing Code
- Executing the Code
- Interfacing With C
- Enhanced Language Interface Features