facebook google plus twitter

Webucator's Free Oracle PL/SQL Tutorial

Welcome to our free Oracle PL/SQL tutorial. This tutorial is based on Webucator's Oracle PL/SQL Training course.
Start Tutorial or choose from a lesson below
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
In this lesson, you will learn about database triggers.
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.
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.
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.