Interacting with Oracle Database Server using PL/SQL Statements | Oracle PL/SQL Training | Uplatz

This video tutorial provides knowledge on how to interact with the Oracle DB server using PL/SQL statements. This is part of the Oracle PL/SQL https://training.uplatz.com/online-it-course.php?id=oracle-12c-pl-sql-35

Tutor-led Oracle PL/SQL Training:
https://training.uplatz.com/course.php?id=oracle-pl-sql-programming-452

PL/SQL includes procedural language elements like conditions and loops. It allows declaration of constants and variables, procedures and functions, types and variable of those types and triggers.

It can support Array and handle exceptions (runtime errors). You can create PL/SQL units like procedures, functions, packages, types and triggers, etc.

Interested in learning the complete course? Contact us:
Email us: [email protected]
View all our video courses: https://training.uplatz.com/online-it-courses.php
Website: https://training.uplatz.com


Oracle PL/SQL training syllabus:

Overview of PL/SQL
What is PL/SQL? What are the benefits?
PL/SQL Block Structure & Types of Blocks
Writing simple anonymous block
Using SQL*plus to run anonymous block
Creating Script and run it
Quiz
Declaring PL/SQL Variables
About Variables and the syntax
Defining Variables exercise
Guidelines for declaring & initializing variables
Delimiters in string literals (the q' Notation)
Types of variables and data types 1
Types of variables and data types 2
Data Types exercise
Define Variables Using %TYPE
Understating the SELECT statement in PL/SQL
Bind Variables
Quiz
Writing Executable Statements
Inside PL/SQL Block
Inside PL/SQL Block (exercise)
Nested Blocks
SQL & PL/SQL programming Guidelines
Quiz
Interacting with Oracle DB Server
SELECT statement & DML in PL/SQL
SELECT statement & DML in PL/SQL exercise
Understanding Implicit SQL cursor/ SQL Attributes
Implicit SQL cursor exercise
Writing Control Structure
IF Statement Part 1
IF Statement Part 2
Handling nulls in IF statement
CASE expression & CASE Statement
Basic Loop
While Loop
FOR Loop
Nested Loops
Nested Loops and Labels
Continue Statement
Working with Composite Data Types
PL/SQL Records: Programmer method part 1
PL/SQL Records: Programmer method part 2
PL/SQL Records: %ROWTYPE method
Nested PL/SQL Records
INDEX BY tables (Associative arrays)
INDEX BY tables Methods
INDEX BY tables of Records
Nested table
VARRAY
Using Explicit Cursors
Understanding Explicit Cursor
Explicit Cursor exercise
Explicit Cursor Attributes
FOR LOOP Cursor
Cursor with Parameters
FOR UPDATE Clause & CURRENT OF Clause

Creating Procedures
Understanding Procedures and subprograms
Understanding Procedures Syntax
Creating Procedure (IN parameter)
Creating Procedure (OUT parameter)
Creating Procedure (IN OUT parameter)
Comparing the Parameter Modes
Available notations for passing parameters
Using the default option for Parameters
Exception handling in multiple blocks
Using Boolean and PL/SQL records as parameters
Creating Functions
Understanding user-defined functions
Difference between procedures & functions
Creating a function example

Creating Packages
Understanding packages by nice example
More about packages
Creating package practice
Creating package specification without body
Guidelines for writing packages

Persistent State and cursor
using PL/SQL tables in packages
Using Oracle-supplied Packages in Application Development

Using Dynamic SQL
Execution flow of SQL
What is Dynamic SQL?
Execute immediate example 1 / delete any table
Execute immediate example 2 / DDL in PLSQL
Execute immediate example 3 / USING Clause
Execute immediate example 4 / INTO Clause
Execute immediate example 5 / single-row query
What is a REF Cursor?
Dynamic SQL with multi-row query
Execute immediate/ more examples
DBMS_SQL Package VS Execute immediately
Design Consideration for PL/SQL Code

Creating Triggers
Understanding Triggers
Tigger Types/ Trigger Event Types
Statement Level trigger VS Row Level (Part 1)
Statement Level trigger VS Row Level (Part 2)
Triggers common cases
Statement Level trigger Example 1
Statement Level trigger Example 2
Understanding the NEW & OLD qualifiers
Row-level trigger example
Row-level trigger (create Audit table)
Trigger Firing sequence
Compile/enable/ disable/ drop trigger
Using the triggers for populating a default value
Instead of triggers (on Views)
Creating Compound, DDL, and Event Database Triggers

Creating DDL triggers (Schema or Database)
Create a system event trigger
Final comments about the triggers
Using the PL/SQL Compiler
Introduction about PL/SQL compiler
Understanding (plsql_code_type) parameter
Understanding (plsql_optimize_level) parameter
Understanding Warnings in PL/SQL
Understanding (plsql_warnings) parameter
Using the package dbms_warning
Managing PL/SQL Code
Understanding conditional compilation
Understanding (dbms_db_version ) package
Understanding plsql_ccflags part 1
Understanding plsql_ccflags part 2
Understanding plsql_ccflags part 3
plsql_ccflags Real example
create_wrapped
The PL/SQL wrapper utility
Managing Dependencies

#DynamicSQL #DBMSSQL #PLSQL