What is PL/SQL?

PLSQL stands for "Procedural Language extensions to SQL", and can be used in Oracle databases. PL SQL is closely integrated into the SQL language, yet it adds programming constructs that are not native to SQL. PL/SQL also implements basic exception handling. This tutorial contains an introduction to beginning pl sql. This Oracle pl/sql tutorial also provides a hands on experience for beginning plsql. It contains many free plsql examples which can be reused in your code.

Pl/Sql is the procedural implementation of sql i.e. you can pass sql statements in procedural format using pl/sql. Normal sql does not have any procedural capabilities moreover you can only pass one statement at a time to Oracle Engine. Hence, pl/sql have come up to avoid this limitation. Hence, pl/sql is the structured programming language for oracle. It's structure is similar to any other procedural language such as C or C++

The following document was written by Yu-May Chang and Jeff Ullman of Stanford University for CS145, Autumn 1997; revised by Jun Yang for Prof. Jennifer Widom's CS145 class in Spring, 1998; additional material by Jeff Ullman, Autumn 1998; further revisions by Jun Yang, Spring 1999; minor revisions by Jennifer Widom, Spring 2000. Linked here

PL/SQL Tutorial

Basic Structure of PL/SQL

PL/SQL stands for Procedural Language/SQL. PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL. The basic unit in PL/SQL is a block. All PL/SQL programs are made up of blocks, which can be nested within each other. Typically, each block performs a logical action in he program. A block has the following structure:

DECLARE

/* Declarative section: variables, types, and local subprograms. */

BEGIN

/* Executable section: procedural and SQL statements go here. */

/* This is the only section of the block that is required. */

EXCEPTION

/* Exception handling section: error handling statements go here. */

END;

Only the executable section is required. The other sections are optional. The only SQL statements allowed in a PL/SQL program are SELECT, INSERT, UPDATE, DELETE and several other data manipulation statements plus some transaction control. However, the SELECT statement has a special form in which a single tuple is placed in variables; more on this later. Data definition statements like CREATE, DROP, or ALTER are not allowed. The executable section also contains constructs such as assignments, branches, loops, procedure calls, and triggers, which are all described below (except triggers). PL/SQL is not case sensitive. C style comments (/* ... */) may be used.

To execute a PL/SQL program, we must follow the program text itself by

* A line with a single dot ("."), and then
* A line with run;

As with Oracle SQL programs, we can invoke a PL/SQL program either by typing in sqlplus.

Variables and Types

Information is transmitted between a PL/SQL program and the database through variables. Every variable has a specific type associated with it. That type can be

* One of the types used by SQL for database columns
* A generic type used in PL/SQL such as NUMBER
* Declared to be the same as the type of some database column

The most commonly used generic type is NUMBER. Variables of type NUMBER can hold either an integer or a real number. The most commonly used character string type is VARCHAR(n), where n is the maximum length of the string in bytes. This length is required, and there is no default. For example, we might declare:

DECLARE

price NUMBER;

myBeer VARCHAR(20);

Note that PL/SQL allows BOOLEAN variables, even though Oracle does not support BOOLEAN as a type for database columns.

Types in PL/SQL can be tricky. In many cases, a PL/SQL variable will be used to manipulate data stored in a existing relation. In this case, it is essential that the variable have the same type as the relation column. If there is any type mismatch, variable assignments and comparisons may not work the way you expect. To be safe, instead of hard coding the type of a variable, you should use the %TYPE operator. For example:

DECLARE

myBeer Beers.name%TYPE;

gives PL/SQL variable myBeer whatever type was declared for the name column in relation Beers.

A variable may also have a type that is a record with several fields. The simplest way to declare such a variable is to use %ROWTYPE on a relation name. The result is a record type in which the fields have the same names and types as the attributes of the relation. For instance:

DECLARE

beerTuple Beers%ROWTYPE;

makes variable beerTuple be a record with fields name and manufacture, assuming that the relation has the schema Beers(name, manufacture).

The initial value of any variable, regardless of its type, is NULL. We can assign values to variables, using the ":=" operator. The assignment can occur either immediately after the type of the variable is declared, or anywhere in the executable portion of the program. An example:

DECLARE

a NUMBER := 3;

BEGIN

a := a + 1;

END;

run;

This program has no effect when run, because there are no changes to the database.


Simple Programs in PL/SQL

The simplest form of program has some declarations followed by an executable section consisting of one or more of the SQL statements with which we are familiar. The major nuance is that the form of the SELECT statement is different from its SQL form. After the SELECT clause, we must have an INTO clause listing variables, one for each attribute in the SELECT clause, into which the components of the retrieved tuple must be placed.

Notice we said "tuple" rather than "tuples", since the SELECT statement in PL/SQL only works if the result of the query contains a single tuple. The situation is essentially the same as that of the "single-row select" discussed in Section 7.1.5 of the text, in connection with embedded SQL. If the query returns more than one tuple, you need to use a cursor. Here is an example:

CREATE TABLE T1(

e INTEGER,

f INTEGER

);



DELETE FROM T1;

INSERT INTO T1 VALUES(1, 3);

INSERT INTO T1 VALUES(2, 4);

/* Above is plain SQL; below is the PL/SQL program. */

DECLARE

a NUMBER;

b NUMBER;

BEGIN

SELECT e,f INTO a,b FROM T1 WHERE e>1;

INSERT INTO T1 VALUES(b,a);

END;

run;

Fortuitously, there is only one tuple of T1 that has first component greater than 1, namely (2,4). The INSERT statement thus inserts (4,2) into T1.


Control Flow in PL/SQL

PL/SQL allows you to branch and create loops in a fairly familiar way.

An IF statement looks like:

IF <condition> THEN <statement_list> ELSE <statement_list> END IF;

The ELSE part is optional. If you want a multiway branch, use:

IF <condition_1> THEN ...

ELSIF <condition_2> THEN ...

... ...

ELSIF <condition_n> THEN ...

ELSE ...

END IF;

The following is an example, slightly modified from the previous one, where now we only do the insertion if the second component is 1. If not, we first add 10 to each component and then insert:

DECLARE

a NUMBER;

b NUMBER;

BEGIN

SELECT e,f INTO a,b FROM T1 WHERE e>1;

IF b=1 THEN

INSERT INTO T1 VALUES(b,a);

ELSE

INSERT INTO T1 VALUES(b+10,a+10);

END IF;

END;

run;

Loops are created with the following:

LOOP

<loop_body> /* A list of statements. */

END LOOP;

At least one of the statements in <loop_body> should be an EXIT statement of the form

EXIT WHEN <condition>;

The loop breaks if <condition> is true. For example, here is a way to insert each of the pairs (1, 1) through (100, 100) into T1 of the above two examples:

DECLARE

i NUMBER := 1;

BEGIN

LOOP

INSERT INTO T1 VALUES(i,i);

i := i+1;

EXIT WHEN i>100;

END LOOP;

END;

.run;

Some other useful loop-forming statements are:

* EXIT by itself is an unconditional loop break. Use it inside a conditional if you like.
* A WHILE loop can be formed with

· WHILE <condition> LOOP

· <loop_body>

END LOOP;

* A simple FOR loop can be formed with:

· FOR <var> IN <start>..<finish> LOOP

· <loop_body>

·

END LOOP;

Here, <var> can be any variable; it is local to the for-loop and need not be declared. Also, <start> and <finish> are constants.