Simple Programs in PL/SQL for JNTU Students

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, as described in the next section. 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 THEN ELSE END IF;
The ELSE part is optional. If you want a multiway branch, use:
IF THEN ...

ELSIF THEN ...

... ...

ELSIF 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

/* A list of statements. */

END LOOP;
At least one of the statements in should be an EXIT statement of the form
EXIT WHEN ;
The loop breaks if 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 LOOP
•
•
•
END LOOP;
• A simple FOR loop can be formed with:
• FOR IN .. LOOP
•
•
•
END LOOP;
Here, can be any variable; it is local to the for-loop and need not be
________________________________________