Oracle PL/SQL
Oracle PL/SQL
A warm welcome to the Oracle PL/SQL course by Uplatz.
PL/SQL is a procedural extension of SQL, making it extremely simple to write procedural code that includes/embraces SQL as if it were a single language thus combining the power of both the database Structured Query Language (SQL) as well as the high-level Procedural Language (PL).
This comprehensive Oracle PL/SQL training course by Uplatz will teach you all concepts of Oracle PL/SQL programming in great depth. You will build skills on programming, productivity, performance, scalability, portability, and security of your Oracle database. With this training, you will learn how to build and execute PL/SQL programs in Oracle, build stored procedures, triggers, functions, etc., design and execute modular applications, automate the Oracle PL/SQL packages, tuning of Oracle database, and increase the efficiency of data movement - essential skills for any Oracle developer.
Key features of PL/SQL are:
PL/SQL is a procedural extension of SQL, making it extremely simple to write procedural code that includes SQL as if it were a single language. In comparison, most other programming languages require mapping data types, preparing statements and processing result sets, all of which require knowledge of specific APIs.
The data types in PL/SQL are a super-set of those in the database, so you rarely need to perform data type conversions when using PL/SQL. Ask your average Java or .NET programmer how they find handling date values coming from a database. They can only wish for the simplicity of PL/SQL.
When coding business logic in middle tier applications, a single business transaction may be made up of multiple interactions between the application server and the database. This adds a significant overhead associated with network traffic. In comparison, building all the business logic as PL/SQL in the database means client code needs only a single database call per transaction, reducing the network overhead significantly.
Oracle is a multi-platform database, making PL/SQL and incredibly portable language. If your business logic is located in the database, you are protecting yourself from operating system lock-in.
Programming languages go in and out of fashion continually. Over the last 35+ years Oracle databases have remained part of the enterprise landscape. Suggesting that any language is a safer bet than PL/SQL is rather naive. Placing your business logic in the database makes changing your client layer much simpler if you like to follow fashion.
Centralizing application logic enables a higher degree of security and productivity. The use of Application Program Interfaces (APIs) can abstract complex data structures and security implementations from client application developers, leaving them free to do what they do best.
Oracle PL-SQL course curriculum
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
Quiz
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
Handling Exceptions
Understanding Exceptions
Predefined Oracle Server Error Exception
Common mistakes when dealing with exceptions
Non-Predefined Oracle Server Error Exception
User-Defined Error Exception
Group Functions and exceptions
Many blocks and many exceptions
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 function example
No_data_found and functions
Advantages of user-defined functions in SQL Statements
Restrictions when calling functions from SQL part 1
Restrictions when calling functions from SQL part 2
Creating Packages
Understanding packages by nice example
More about packages
Creating package practice
Creating package specification without body
Guidelines for writing packages
The visibility of a package’s components
When & Why to recompile the package?
Working with Packages
Understanding Overloading Subprograms in PL/SQL
Overloading Subprograms practices (procedures)
Overloading Subprograms practices (functions)
Overloading Standard package
Forward Declaration
Persistent State of packages
Using PRAGMA SERIALLY_REUSABLE
Persistent State and cursor
using PL/SQL tables in packages
Using Oracle-supplied Packages in Application Development
About oracle-supplied Packages
DBMS_OUTPUT part 1
DBMS_OUTPUT part 2
UTL_FILE Package Part 1 / create directory
UTL_FILE Package Part 2 / get_line example 1
UTL_FILE Package Part 3 / get_line example 2
UTL_FILE Package Part 4 / put_line
UTL_FILE Package Part 5 / exceptions
Mail
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 REF Cursor?
Dynamic SQL with multi row query
Execute immediate/ more examples
DBMS_SQL Package VS Execute immediate
Design Consideration for PL/SQL Code
Standardizing Part 1
Standardizing Part 2
Definer’s Rights
Invoker’s Rights
Autonomous Transactions
Features Autonomous Transactions
The (IN) parameters always passed by reference
The (out/in out) parameters can be passed by value or reference
Passing by reference example (IN parameters)
Passing by value VS passing by reference using nocopy
When Does NOCOPY hint has no effects?
NOCOPY Performance Exercise
Using the PARALLEL_ENABLE hint
Using the RESULT_CACHE hint
Using the DETERMINISTIC hint
Understanding Bulk Binding
Bulk Binding Example
Bulk binding with save exception part 1
Bulk binding with save exception part 2
Understanding Bulk collect
Bulk collect another example
Bulk collect and cursors
Using returning in bulk collect
INDICES OF
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
Follows Statement in triggers
Understanding Compound triggers
Understanding Mutating Table
Solving Mutating Table issue using compound trigger
Using Compound trigger & array to solve Mutating
Mutating table & ON DELETE CASCADE
Database Triggers VS Stored procedures
Creating DDL triggers (Schema or Database)
Create 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
More examples for plsql_warnings
Using the package dbms_warning
Managing PL/SQL Code
Understanding conditional compilation
Understanding (dbms_db_version ) package
conditional compilation examples
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
Learn PL/SQL architecture, programming, fine-tuning in depth from basics to advanced level. Become Oracle Developer/DBA.
Url: View Details
What you will learn
- Learn end-to-end PL/SQL concepts, programming, and application
- Oracle PL/SQL architecture and building blocks
- Data Types, Variables, Literals
Rating: 3.15
Level: All Levels
Duration: 5 hours
Instructor: Uplatz Training
Courses By: 0-9 A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
About US
The display of third-party trademarks and trade names on this site does not necessarily indicate any affiliation or endorsement of hugecourses.com.
View Sitemap