Oracle PL/SQL Free Tutorial

Welcome to our free Oracle PL/SQL tutorial. This tutorial is based on Webucator's Oracle PL/SQL Training course.

Contact Us or call 1-877-932-8228
Start Tutorial or choose from a lesson below

Lesson 1: The Environment for PL/SQL Development

Oracle PL/SQL is designed to extend and integrate with Oracle SQL. While SQL itself is powerful and can accomplish many tasks, it is a set-oriented language designed to obtain and manipulate data in a relational database system (RDBMS). Even though Oracle SQL includes additional functionality unavailable in ANSI standard SQL, it lacks the features available in procedural languages. A procedural language provides the ability to assign values to variables, execute statements in sequence, iterate, and conditionally process commands. Hence, PL/SQL (or Procedural Language extensions to the Structured Query Language) was designed by Oracle to provide such features within the database. A SQL user typically constructs a query to perform an operation on data in the database. Although several queries can be executed in sequence, the fundamental programming unit in SQL is a single statement. A PL/SQL user typically creates a program that includes variable declaration and assignment, conditional statements (e.g. IF/ELSE statements), and loops (e.g. FOR or WHILE statements). The program can contain many SQL statements, including statements that are dynamically generated. In addition, PL/SQL provides mechanisms to seamlessly integrate SQL and so effectively extends the capabilities present in SQL alone. Many languages can be used with SQL embedded in them, but PL/SQL executes within the database itself. Although there are other languages that can execute within the Oracle database (Java for instance), they are simply general purposes languages that are available in this environment. PL/SQL was specifically designed for to interact closely with SQL within the Oracle database.

Lesson 2: PL/SQL Basics

In PL/SQL code groups of commands are arranged within a block. A block groups related declarations or statements. A block can be contained within another block or in a larger programming unit called a subprogram. A subprogram can be a stored procedure, function, trigger or package. A stored procedure or simply procedure is essentially an encapsulated named block of code stored within the database. This distinguishes it from an anonymous block which is an unnamed block of PL/SQL code which is not stored in the Oracle database. Although an anonymous block is not stored within the database, it is executed within the database. A function is distinguished from a procedure in that it is able to return a value. Both functions and procedures can take parameters and are called interactively. A procedure cannot be called directly from SQL. A function (in most cases) can be called from a SQL statement. A trigger cannot take parameters and is called automatically when a database event occurs. Examples of triggering events include the execution of DML statements (an insert, update or delete) or DDL statements (create, alter, drop). A Package serves as a container for functions, procedures and PL/SQL types. One or more parameters can be specified for stored procedures or functions. A parameter is created with a name, data type, and mode. As PL/SQL is tightly integrated with SQL, PL/SQL data types are a superset of SQL datatypes. The parameter mode determines a parameter's behavior. It can be set to input only (IN), output only (OUT), or input and output (IN OUT). Therefore, even though a procedure cannot directly return a value, a similar effect is possible using an output parameter. The following table summarizes distinguishing characteristics of PL/SQL anonymous blocks and subprograms.

Lesson 3: Declaring Variables

A PL/SQL block can contain an optional section where types and variables are defined. These items are accessed and manipulated in the executable section of the block. This chapter describes the types of variables available and how to create, access, and modify the values that variables contain.

Lesson 4: Within the Block

PL/SQL - by definition - is a procedural language. It is specifically a procedural extension to SQL. A procedural (or imperative) language is used to specify steps to be taken to reach a desired state. Often the steps involved differ based on the values of the variables in the program. PL/SQL includes several control structures to vary the execution flow. Conditional statements are used to differentiate the execution path taken (or steps taken to reach the desired state) based upon the value of one or more variables. Iterative processing (or looping) is used to represent repeated steps. Conditional statements and loops can be nested arbitrarily to produce many different possible results of running the same program.

Lesson 5: Exception Clause

It is important to distinguish the various types of error conditions that might exist in PL/SQL. A compile time error occurs when there is a problem with PL/SQL code at the time it is saved to the database. A run time error occurs when a syntactically valid program encounters a problem during program execution. Many run time errors can be dealt with in the EXCEPTION section of a PL/SQL block. An exception handler is the special code implemented to handle a given exception.

Lesson 6: Use of SQL in PL/SQL

A cursor is a control structure used to traverse and process database records in a result set. Every SQL statement involves the use of a cursor. Since PL/SQL is tightly integrated with SQL, the ability to reference and control cursors is a significant feature of the language. Cursors can be explicit or implicit. An implicit cursor is used when assigning a value to a variable using the SELECT INTO syntax. An implicit cursor is also created when a DML statement (INSERT/UPDATE/DELETE) is executed. An explicit cursor must be declared for SQL statements that return more than one row.

Lesson 7: Nested Blocks

You have already been introduced to PL/SQL blocks. Blocks are the fundamental structure within which PL/SQL code is written. They can be nested inside of each other to organize code, to limit variable scope or visibility, or to affect exception handling. Scope is the context in which a given variable can be accessed within a program. Scope is relevant when considering where a variable will be declared and where it will be referenced. Depending upon where it is declared, a variable might be accessible using its name, accessible using a qualified name or not accessible at all. Both traditional variables and exceptions are affected by scope considerations. In addition, the use of the EXCEPTION section of a block can affect the way errors are handled by your program.

Lesson 8: Introducing Subprograms

Up to this point, we have dealt primarily with anonymous blocks. These scripts are chunks of PL/SQL code that can be used to accomplish a wide variety of tasks. However, since PL/SQL is tightly integrated with SQL, it makes sense that code be available within the database itself. Subprograms provide all of the functionality possible in anonymous blocks but are stored in the database.

Lesson 9: Stored Procedures and Functions

The focus of this lesson is to describe the basic creation and maintenance of subprograms. All of the PL/SQL you have learned up to this point can be used in the context of such programs, but the examples will be simple demonstration programs that focus on the distinctive characteristics of Oracle functions and stored procedures. Subprograms serve as independent programming units but can be coupled together to produce complex applications. In order for subprograms to be useful and to interact with people and programs they need to be able to receive data, process it and send back results.

Lesson 10: Packages

As an application grows in size and complexity it becomes increasingly important to structure units of application code. Well organized code is easier to debug and maintain. Procedures and functions are the basic building blocks of PL/SQL code that are stored in the database. In this lesson, you will learn ways to organize these building blocks into groups of related functionality called packages.

Lesson 11: Database Triggers

A trigger - like a stored procedure - is a subprogram that does not return a value. The distinguishing characteristic of a trigger is that it is not called; rather it is fired in response to a triggering event. When a trigger is fired the PL/SQL code in the body of the trigger is executed. In this lesson you will learn how to create, drop and modify triggers to change their functionality. You will also learn how to view triggers within the database and obtain information concerning their current state. Because triggers are distinguished by the fact that they fire in response to an event, there are a few other operations unique to trigger maintenance. These include the ability to disable triggers to prevent them from firing without removing them from the database. Triggers that have been disabled can later be enabled to make them functional again. Purpose of Triggers There are a number of situations when you might want to use a trigger rather than another PL/SQL program type. A trigger can generate values for columns, provide validation (prevent invalid data, enforce security or referential integrity), implement specific business rules, provide auditing or logging (including replicating data), modify table data when views have DML run against them, or publish information to external applications. Keep in mind, though, that triggers should not be used as a replacement for what can be easily accomplished with database constraints; trigger performance is slower than database constraint enforcement. Triggers fire within the scope of a database transaction. You need to take special care when designing and using triggers to prevent problems with performance and application support. Because triggers fire without any explicit notice, their effects can be confusing. In addition, interactions between triggers can result in side effects that result in errors or situations that are difficult to debug. There are a number of situations when you might want to use a trigger rather than another PL/SQL program type. A trigger can generate values for columns, provide validation (prevent invalid data, enforce security or referential integrity), implement specific business rules, provide auditing or logging (including replicating data), modify table data when views have DML run against them, or publish information to external applications. Keep in mind, though, that triggers should not be used as a replacement for what can be easily accomplished with database constraints; trigger performance is slower than database constraint enforcement. Triggers fire within the scope of a database transaction. You need to take special care when designing and using triggers to prevent problems with performance and application support. Because triggers fire without any explicit notice, their effects can be confusing. In addition, interactions between triggers can result in side effects that result in errors or situations that are difficult to debug.

Lesson 12: Data Retrieval Techniques

PL/SQL functions as an extension of SQL and operates within the database environment. Oracle has provided a great deal of flexibility in the ways that PL/SQL can reference and operate upon data. Various data retrieval techniques available are discussed and demonstrated in this lesson.

Lesson 13: Using Oracle Supplied Packages

The Oracle database goes far beyond providing the base functionality expected in a standard RDBMS. Procedural programming is available through the use of PL/SQL. In addition, Oracle includes a large number of packages that are included with a standard database installation. Many other paid options available in Oracle are implemented (in full or in part) as PL/SQL packages.

Lesson 14: Specialized Topics

The features and options available in Oracle increase with every version. Some of these features are invisible to the end user and are enhancements to the availability, recoverability and performance of the database. However, many options provide application developers and data analysts new options for application design and data analysis. A few of these options are mentioned below along with guidelines to consider when creating applications that interact with the Oracle database.

Try The Complete Course

In addition to the free course material here, our complete self-paced course includes:

  • Video Presentations from Expert Instructors
  • Exercises
  • Interactive Quizzes
  • Readings and Tasks
Learn More