PL/SQL Tutorial - Master PL/SQL Programming Quickly and Easily (2024)

Table of Contents

Section 1. Getting started with PL/SQL Section 2. Conditional control Section 3. Iterative processing with loops Section 4. Select Into Section 5. Exception handlers Section 6. Records Section 7. Cursors Section 8. Stored procedures and Functions Section 9. Packages Section 10. Triggers Section 11. PL/SQL Collections

Home » PL/SQL Tutorial

PL/SQL Tutorial - Master PL/SQL Programming Quickly and Easily (1)

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.

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 controlto a labeled block or statement.
NULLstatement – 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 LOOPstatement – learnhow 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?

PreviouslyOracle DROP SEQUENCE

Up NextWhat is PL/SQL