PL/SQL Tutorial - Master PL/SQL Programming Quickly and Easily

If you’re a developer or a database administrator looking for a way to master PL/SQL programming language, these PL/SQL tutorial series are for you.

These PL/SQL tutorial series contain the information that every developer and database administrator should have to efficiently use PL/SQL in their daily tasks.

As you go through the entire tutorial series, you will acquire a lot of new knowledge about PL/SQL programming. These PL/SQL tutorial series present the best practices in PL/SQL programming language. The best part is that each tutorial contains a lot of examples and helpful scripts.

Section 1. Getting started with PL/SQL

What is PL/SQL – introduce you to PL/SQL programming language and its architecture.
Anonymous Block – explain PL/SQL anonymous blocks and show you how to execute an anonymous block in SQL*Plus and Oracle SQL Developer tools.
Data Types – give you a brief overview of PL/SQL data types including number, Boolean, character, and datetime.
Variables – introduce you to PL/SQL variables and show you how to manipulate variables in programs efficiently.
Comments – use single-line or multi-line comments to document your code to make it more readable and maintainable.
Constants – learn how to declare constants that hold values that remain unchanged throughout the execution of the program.

Section 2. Conditional control

IF statements – introduce you to various IF statement to either execute or skip a sequence of statements based on a condition.
CASE statements – learn how to choose one sequence of statements out of many possible sequences to execute.
GOTO – explain the GOTO statement and show how to use it to transfer control to a labeled block or statement.
NULL statement – show you how to use the NULL statement to make the code more clear.

Section 3. Iterative processing with loops

Basic LOOP statement – show you how to use the basic LOOP statement to execute a sequence of code multiple times.
Numeric FOR LOOP statement – learn how to execute a sequence of statements a fixed number of times.
WHILE loop – execute a sequence of statements as long as a specified condition is TRUE.
CONTINUE – use the CONTINUE statement to skip the current iteration of the loop and immediately continue the next iteration.

Section 4. Select Into

SELECT INTO – learn how to fetch a single row from a table into variables.

Section 5. Exception handlers

Exception – show you how to handle exceptions in a block.
Raise exceptions – learn how to raise an exception explicitly with the RAISE statement.
Using raise_application_error – raise an exception with a user-defined error message.
Exception propagation – learn about how PL/SQL propagates an unhandled exception from the current block to its enclosing block.
Handling other unhandled exceptions – show you how to use the SQLCODE and SQLERRM functions to handle other unhandled exceptions.

Section 6. Records

Record – learn how to use record type to make your code more efficient by shifting operations from field-level to record-level.

Section 7. Cursors

Cursor – introduce you to cursors including implicit and explicit cursors and shows you how to use them effectively to fetch data from a table.
Cursor FOR LOOP – show you how to use the cursor FOR LOOP statement to fetch and process each row from a cursor.
Cursor with parameters – learn how to use the cursor with parameters to fetch data according to the input arguments passed to the cursor each time it is opened.
Cursor Variables with REF CURSOR – guide you on how to use the cursor variable with ref cursor.
Updatable cursor – introduce you to the Oracle updatable cursor to update data in the table.

Section 8. Stored procedures and Functions

Procedure – a step-by-step guide to create, compile and execute a procedure from Oracle SQL Developer.
Implicit statement results – learn how to return one or more result sets from a procedure.
Function – show you how to develop a PL/SQL function and explain to you various ways to call a function.
Cursor variables – learn how to use cursor variables using REF CURSOR type.

Section 9. Packages

PL/SQL package – introduce you to PL/SQL packages and explain to you the advantages of using them in your application development.
Package specification – show you step-by-step how to create a package specification.
Package body – learn how to create a package body.
Drop Package – illustrate how to drop a PL/SQL package.

Section 10. Triggers

Triggers – introduce you to the Oracle trigger and show you how to create a new trigger.
Statement-level triggers – learn how to use statement-level triggers to enforce additional security to the transaction.
Row-level triggers – show you how to use row-level triggers for data-related activities.
INSTEAD OF triggers – learn how to use the INSTEAD OF triggers to update tables via their views which cannot be modified directly through DML statements.
Disable triggers – show you how to disable a trigger or all triggers of a table.
Enable triggers – describe steps to enable a trigger or all triggers of a table.
Drop Triggers – guide you on how to drop a trigger from the database.
How to fix the mutating table error – learn about the mutating table error and how to fix it using a compound trigger.

Section 11. PL/SQL Collections

Associative Arrays – introduce you to associative arrays including declaring associative arrays, populating values, and iterating over array elements.
Nested Tables – learn about nested tables.
VARRAY – learn about variable-sized arrays and how to manipulate their elements effectively.

Was this tutorial helpful?