Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / PL/SQL Tutorial

PL/SQL Tutorial

PL/SQL Tutorial

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

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

As you go through the entire tutorial series, you will learn a lot of new facts about PL/SQL programming. These PL/SQL tutorial series provide the best practices on PL/SQL programming language. The best part is that they contain a lot of examples and helpful script in each tutorial.

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 shows 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 shows 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 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 shows 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 efficiently 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 fro 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

  • Introduction to the 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 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 array and how to manipulate its elements effectively.
  • Was this tutorial helpful?
  • YesNo
Previous Oracle Sequence
Next Oracle Aggregate Functions

PL/SQL Getting Started

  • What is PL/SQL
  • PL/SQL Anonymous Block
  • PL/SQL Data Types
  • PL/SQL Variables
  • PL/SQL Comments
  • PL/SQL Constants

PL/SQL Conditional Control

  • PL/SQL IF THEN
  • PL/SQL CASE
  • PL/SQL GOTO
  • PL/SQL NULL Statement

PL/SQL Loops

  • PL/SQL LOOP
  • PL/SQL FOR LOOP
  • PL/SQL WHILE Loop
  • PL/SQL CONTINUE

PL/SQL Select Into

  • PL/SQL SELECT INTO

PL/SQL Exception Handlers

  • PL/SQL Exception
  • PL/SQL Exception Propagation
  • PL/SQL RAISE Exceptions
  • RAISE_APPLICATION_ERROR

PL/SQL Records

  • PL/SQL Record

PL/SQL Cursors

  • PL/SQL Cursor
  • PL/SQL Cursor FOR LOOP
  • PL/SQL Cursor with Parameters
  • PL/SQL Updatable Cursor

PL/SQL Procedures & Functions

  • PL/SQL Procedure
  • PL/SQL Function
  • PL/SQL Cursor Variables

PL/SQL Packages

  • PL/SQL Package
  • PL/SQL Package Specification
  • PL/SQL Package Body

PL/SQL Triggers

  • PL/SQL Triggers
  • PL/SQL Statement-level Triggers
  • PL/SQL Row-level Triggers
  • PL/SQL INSTEAD OF Triggers
  • PL/SQL Disable Triggers
  • PL/SQL Enable Triggers
  • PL/SQL Drop Triggers
  • Oracle Mutating Table Error

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.