Syronex

PL/SQL Tutorial

Need for PL/SQL — declarative vs. procedural — anonymous blocks — debugging — a first program — code compilation — code execution — procedures & functions — PL/SQL in SQL — SQL in PL/SQL — cursors & loops — operators & built-in functions reference tables.

Introduction

PL/SQL is a database-oriented programming language that extends Oracle SQL with procedural capabilities. We will review in this lab the fundamental features of the language and learn how to integrate it with SQL to help solve database problems.

Need for PL/SQL

SQL statements are defined in term of constraints we wish to fix on the result of a query. Such a language is commonly referred to as declarative. This contrasts with the so called procedural languages where a program specifies a list of operations to be performed sequentially to achieve the desired result. PL/SQL adds selective  (i.e. if...then...else...) and iterative constructs (i.e. loops) to SQL.
PL/SQL is most useful to write triggers  and stored procedures. Stored procedures are units of procedural code stored in a compiled form within the database.

PL/SQL Fundamentals

PL/SQL programs are organised in functions, procedures and packages (somewhat similar to Java packages). There is a limited support for object-oriented programming. PL/SQL is based on the Ada programming language, and as such it shares many elements of its syntax with Pascal.

Your first example in PL/SQL will be an anonymous block —that is a short program that is ran once, but that is neither named nor stored persistently in the database.

SQL> SET SERVEROUTPUT ON

SQL> BEGIN
  2   dbms_output.put_line('Welcome to PL/SQL');
  3  END;
  4  /

You are referred to Table 2 for a list of operators, and to Table 3 for some useful built-in functions.

Compiling your code.

PL/SQL code is compiled by submitting it to SQL*Plus. Remember that it is advisable to type your program in an external editor, as you have done with SQL (see Introduction to Oracle).

Debugging.

Unless your program is an anonymous block,  your errors will not  be reported. Instead, SQL*Plus will display the message ``warning: procedure created with compilation errors''. You will then need to type:

SQL> SHOW ERRORS

to see your errors listed. If yo do not understand the error message and you are using Oracle on UNIX, you may be able to get a more detailed description using the oerr utility, otherwise use Oracle's documentation (see References section). For example, if Oracle reports ``error PLS-00103'', you should type:

oerr PLS 00103

at the UNIX command prompt  (i.e. not in SQL*Plus).

Executing PL/SQL

If you have submitted the program above to Oracle, you have probably noticed that it is executed straight away. This is the case for anonymous blocks, but not for procedures and functions. The simplest way to run a function (e.g. sysdate) is to call it from within an SQL statement:

SQL> SELECT sysdate FROM DUAL
  2  /

Next, we will rewrite the anonymous block above as a procedure. Note that we now use the user function to greet the user.

CREATE OR REPLACE PROCEDURE welcome
IS
  user_name VARCHAR2(8) := user;
BEGIN  -- `BEGIN' ex
  dbms_output.put_line('Welcome to PL/SQL, ' 
    || user_name || '!');
END;
/

Make sure you understand the changes made in the code:

Once you have compiled the procedure, execute it using the EXEC command.


SQL> EXEC welcome

Both procedures and functions should remind you of Java methods. The similarities and differences between them are outlined in Table 1.


Table 1: Functions, procedures and Java methods compared.
Function Procedure Java Method
Parameters input, output input, output input
Returns value yes no optional
Can be called within SQL yes no


Contents
Need for PL/SQL
PL/SQL Fundamentals
Executing PL/SQL
PL/SQL Tutorial, Part 2
See Also
Oracle SQL
SQL Built-In Functions
Oracle's Data Dictionary
Oracle & Java
Advanced Oracle & Java
Tell Your Friends
Your name

Your friend's email

Add to digg Add to reddit Add to delicious Add to StumbleUpon Add to Google Bookmarks Add to Furl