facebook google plus twitter
Webucator's Free Oracle PL/SQL Tutorial

Lesson: PL/SQL Basics

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

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. Image of PL/SQL Subprogram Characteristics

Lesson Goals

  • Learn about PL/SQL structure and syntax
  • Learn about available PL/SQL program units:
    1. Stored procedures
    2. Functions
    3. Triggers
    4. Packages
  • Learn the structure of a PL/SQL program block
  • Learn about subprogram storage and execution

PL/SQL Block

The following is an example of a minimal PL/SQL block. It does nothing, but is syntactically correct. It starts with the BEGIN key word and ends with the END keyword.

Note that PL/SQL keywords are not case sensitive. The END and all executable statements must be followed by a semicolon.

begin
	null;
end;

At least one executable statement is required, either a PL/SQL statement or a SQL DML statement. In this case, NULL is the statement. The NULL statement simply passes control to the next statement. Since it takes no other action, it is useful for creating placeholders and stub subprograms.

Anonymous Block Structure

An anonymous block is an unnamed block of PL/SQL code that is not persisted in the database. The minimal PL/SQL block above is an example of an anonymous block. In addition to the mandatory section of the block containing executable statements, an anonymous block can include variable declarations (in a DECLARE section prior to the BEGIN keyword). Image of PL/SQL Anonymous Block Structure It also can include exception handling code. Code in exception handlers is called if a runtime error occurs in one of the executable statements in the mandatory section of the block. The declaration and exception handling sections are optional.

Named Block Structure

The named block structure builds upon the anonymous block structure shown above. It includes a header section which contains the name and type of the subprogram. Image of PL/SQL Named Block Structure The header section might also include parameter specifications. There are a number of other variable elements of the header section that vary based upon the subprogram type in use. For example, a function will include information about its return value and a trigger will contain information about the trigger event that causes it to be fired.

Executing Blocks

PL/SQL Blocks can be executed from within SQL*Plus or SQLDeveloper. The following block includes two styles of comments available in PL/SQL. Comments contain text that is ignored by the compiler. One style of comments is useful for comments that span multiple lines and includes a closing character sequence. The other applies to single line comments and does not require any closing character sequence. The executable statement references a stored procedure called PUT_LINE in the DBMS_OUTPUT package supplied with Oracle. The intended result of executing the block is to display the character string passed as a parameter to the procedure on the screen.

Code Sample:

Language-Features/Demos/hello_world_from_a_block.sql
begin

/* 
 A slash followed by an asterisk indicates a multiline comment
 that continues until an asterisk followed by a slash is encountered.
*/

--Lines that begin with two dashes are comments and will be ignored.

  dbms_output.put_line('Hello World - from inside a PL/SQL block.');
end;

Code Explanation

You should see "Hello World - from inside a PL/SQL block." displayed. As indicated in the last chapter, in SQL*Plus you must run a SET SERVEROUTPUT ON command in order to see results of this program (and any other program that utilizes the DBMS_OUTPUT package for output). SQLDeveloper includes a button to toggle SERVEROUTPUT on or off. You can click this button on the DBMS output tab instead of running the command.

Calling PL/SQL Functions

PL/SQL functions return a value and can be called from within SQL statements (as well as from PL/SQL blocks). Functions are typically tested using an Oracle table called DUAL. The DUAL table is a special table available in all Oracle databases that contains one row and one column. It is useful for creating syntactically valid SQL statements that are used to call functions. They can also be used to return a result set with a single row for other testing purposes.

Code Sample:

Language-Features/Demos/calling_a_function_using_dual.sql
select dbms_metadata.get_ddl('TABLE','EMPLOYEES')
from dual;

Code Explanation

This demonstration shows a call to a function in the context of a SQL statement. This illustrates one way that PL/SQL is tightly integrated with Oracle SQL.

The DBMS_METADATA package contains a number of subprograms related to retrieving information on the structure of database objects. This demonstration illustrates a function that is part of this package that can be called using the DUAL table. Some adjustments are required to format the display correctly in SQL*Plus.

set long 2000000000
set pages 0

More information on these and other SQL*Plus formatting options can be found in the SQL*Plus help as well as the online SQL*Plus documentation.

Executing PL/SQL Blocks and Functions

Duration: 10 to 15 minutes.
  1. Using a SQL*Plus/SQL Developer command, call a procedure in an Oracle supplied package to print out "Hello Universe!".
  2. Create and execute a call to the same procedure that will print out "Hello Universe!" using an anonymous block.
  3. Execute a function call to print out the structure of the JOBS table using a function from an Oracle supplied package.

Solution:

Language-Features/Solutions/development_cycle_solution.sql
exec dbms_output.put_line('Hello Universe!');

begin
   dbms_output.put_line('Hello Universe!');
end;
/

select dbms_metadata.get_ddl('TABLE','JOBS')
from dual;

Code Explanation