PL/SQL Tutorial

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

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?