PL/SQL Tutorial

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.

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.