The SQL and SQL*Plus commands that have so far been covered allow the programmer to create a database, manipulate the data, and generate reports. However, some database applications require more data processing capabilities than SQL or SQL*Plus have. In this circumstance programmers would often resort to using other languages such as COBOL, EasyTrieve, FORTRAN, C, Pascal, etc. to access their databases. Usually this would involve linking the program with a special SQL database interface library.

This method is good and is discussed in section 7.16.7. Embedded SQL. Programmers can use a language they are already comfortable with to access the large relational database using special interface libraries. The drawback, however, is that these common languages are usually multi-purpose languages that might not be suited for such intensive data processing.

Oracle includes a procedural programming language called PL/SQL that contains constructs similar to other procedural languages but also has facilities for more robust data processing and a better interface to the SQL and SQL*Plus capabilities of the Oracle database.

There are 2 versions of PL/SQL -- one is a database engine and is the core programming language (called database PL/SQL), the other is a separate engine embedded in a number of the Oracle tools which are usually used to create graphical forms and so forth (called tool PL/SQL).

This section will cover some of the features of database PL/SQL. CHARACTER SET

When programming in PL/SQL, you are limited to the following characters:

* lower case letters:

a b c d e f g h i j k l m n o p q r s t u v w x y z

* upper case letters:


* digits

0 1 2 3 4 5 6 7 8 9

* symbols

( ) + - * / < > = ! ~ ; : . ' @ % , " # $ ^ & _ | { } ? [ ]

Following are the common arithmetic operators:

Operator    Meaning             Operator    Meaning             
+           addition            -           subtraction         
*           multiplication      /           division            
**          exponentiation                                      

These are the relational operators:

Operator    Meaning             Operator    Meaning             
<>          not equal           !=          not equal           
^=          not equal           <           less than           
>           greater than        =           equal to            

Other miscellaneous symbols:

Symbol     Description                Example                                    
()         list separators            in ('Jones', 'Smith')                      
;          end of statement           procedure_name (v1, v2);                   
.          item separator             table_name.column_name                     
'          string quote               'This is a string.'                        
:=         assignment                 x := x + 1;                                
||         concatenation              name := 'Sue' || ' ' || 'Tyen';            
--         comment                    -- This is a comment ... woo woo!          
/* */      comment                    /* This is also a comment. */              

PL/SQL variables are based on the following rules:

* must start with a letter

* contain one or more letters, numbers, or the special characters $, #, or _

* must be less than or equal to 30 characters in length

* must not contain spaces

Variables may contain information of various sorts. Following are some of the more common variable types:

Type                Description                                                 
varchar2(size)      stores variable sized data with a maximum size of 2000      
number(l,d)         stores numeric data where l stands for the total length     
                    of the number and d for the number of decimal digits --     
                    so number(5,3) can have up to 2 integer digits and 3        
                    decimal digits                                              
date                Oracle stores dates in the format DD-MON-YY -- so July 1,   
                    1996 would be stored as 01-JUL-96                           
boolean             switch that holds status TRUE or FALSE               PROGRAMMING CONSTRUCTS

The indentation shown in the examples that follow is arbitrary. That is, indentation is not a required part of PL/SQL programming, but it makes understanding and maintaining the program easier for everyone involved. In order to help prevent errors and to allow other programmers to maintain your code, you should always indent your code nicely.

The following few sections describe some of the features of PL/SQL programming such as exceptions, decision making, loops, and so on. The only way to really learn a new computer language is to use it -- so read on and try to implement some of what is discussed. EXECUTING PL/SQL PROCEDURES

To execute a procedure written in PL/SQL it must first be loaded into the database. This is done by using the "@" character on the command line. This is identical to loading up other scriptfiles as discussed in previous sections. For example:

sqlplus username/password @procedure_file

Once a procedure is loaded into the database it is there forever until removed or replaced. It will be automatically compiled upon loading and Oracle will tell you if it encountered any errors. To list the errors, issue the following command at the SQL prompt:

sql> show error

The line and column numbers where errors occurred will be listed, as well as some kind of error message.

To execute the procedure, use the execute command at the SQL prompt:

sql> execute <procedure_name>; DECLARING VARIABLES

The DECLARE section in PL/SQL programs is where variables and cursors (note: see section Cursors further on in the manual) are defined. For example, to declare a number of variables for use in a PL/SQL procedure you might do something like this:

create or replace procedure sample1 (v1 in varchar2, v2 in varchar2) as



counter1 number;

counter2 number;

my_date date := sysdate; -- notice that you can also initialize

-- variable in the DECLARE section

flag1 varchar2(1);

mess_text varchar2(80);

cursor my_cursor is ......;






Example of a DECLARE section in a PL/SQL procedure.

Notice the first line of the procedure. This is a directive to either create or replace an already existing procedure titled "sample1". The procedure is then stored in the database and can be executed as described in section 7.16.6. Programming With PL/SQL.

The above example also contains portions of code other than the DECLARE section. These are described later in the manual. EXCEPTIONS

Exceptions are the PL/SQL method of handling error conditions. For example, if you are searching through a database for certain information but do not find it, Oracle will generate an error condition or exception.

Following is a table of some of the most common exceptions:

Exception Name          Description                                            
no_data_found           This exception is raised when a SELECT statement is    
                        unable to retrieve any rows of information.            
too_many_rows           Since a cursor is only able to retrieve one row of     
                        information at a time, this exception detects the      
                        existence of more than one.  (note:  see section       
               Cursors further on in the manual.)           
dup_val_on_index        This exception is raised when an attempt is made to    
                        create a row of information with an already existing   
                        primary key value.                                     
value_error             When a value is assigned into a variable that is too   
                        small to hold it, this exception is raised.            

The following example is an exception handler. If no data is retrieved, the sortf value is set. If some other exception occurs, the program is made to exit:


exception -- error when SELECT statement fails

when no_data_found

sortf := 'F';

when others -- handles other error conditions



Example of the EXCEPTION PL/SQL command. if LOGIC STRUCTURES

There are 3 types of if-statements in PL/SQL. They are outlined below. if-then

This is the most basic version of the if-statement. Basically, it checks to see if a condition is true. If it is, a block of code is executed.

if condition then

block of code to execute

end if;

if-then programming structure

The following are some examples of the basic if-then statement:

if var1 < var2 then (1)

var1 := var3;

end if;

if not(var1 < var2) then (2)

if var3 < var2 then

var1 := var3;

end if;

end if;

The 1st example is straight-forward. The 2nd example demonstrates the nesting of if statements. Notice that each if-then block is terminated by its own "end if" statement. if-then-else

This is similar to the simple if-then, except that if the condition is not met the statements after the else are executed.

if condition then

block of code to execute


block of code to execute

end if;

if-then-else programming structure

Following is an example of a nested if-then-else statement:

if x > 5 then

y := TRUE;


if x < 5 then

y := FALSE;


x := x * 0.3;

end if;

end if; if-then-elsif

This is an alternative to the if-then-else construct. For example, the previous example could have been written:

if x > 5 then

y := TRUE;

elsif x < 5 then

y := FALSE;


x := x * 0.3;

end if;

Important Note:

There is no matching end if with an elsif. DO NOTHING

Sometimes, especially with if-then style logic, you might want to end up doing nothing. In the programming world, this is commonly referred to as a no-op. In PL/SQL it is called the null construct. For example:

if z >= 10 then



insert into my_table values (z, 'Less than 10');

end if;

Example of the no-op, or null construct. LOOPS

Looping provides the ability to execute a block of code over and over again, a set number of times or until some conditions are met. The following sections demonstrate the use of the 4 basic looping structures in PL/SQL. loop-exit-end

This is the most basic loop construct. To exit the loop, you must explicitly test for some condition with an if statement of some kind.

counter := 1; -- initialize loop counter


counter := counter + 1; -- increment loop counter

if counter > 100 then -- test condition to exit loop

exit; -- exit out of loop

end if;


end loop; -- end of the loop

Example of the loop statement. loop-exit when-end

This is very similar to the above example. The exit when statement inside the loop tests for the exit condition accordingly.

counter := 1; -- initialize loop counter


counter := counter + 1; -- increment loop counter

exit when counter > 100 -- exit condition


end loop; -- end of the loop

Example of the loop statement with the "exit when" construct. while-loop-end

The while loop has the exit condition implicitly built-in. The condition is tested at the head of the loop.

counter := 1; -- initialize loop counter

while counter <= 100 loop -- test for exit condition at start


counter := counter + 1; -- increment loop counter


end loop; -- end of the loop

Example of the while loop. for-in-loop-end

The for loop is a counted loop. That is, it allows repetitive execution of a loop a predetermined number of times.

counter := 1; -- initialize loop counter

for counter in 1..100 loop


end loop; -- end of the loop

Example of the counted for loop. CURSORS

A CURSOR is a chunk of memory that is used for processing SQL SELECT statements. Sometimes you can define the cursor (explicit ) while at other times you might let PL/SQL define the cursor (implicit).

Explicit cursors are more common than implicit cursors -- they are easier to use, more efficient, and less prone to error. When using implicit cursors, exception handling and error checking become very time consuming and prone to errors themselves. Besides, all the really cool people use explicit cursors. EXPLICIT CURSORS

You must declare the cursor, open it before using it, and close it when it is no longer needed.

The cursor must be part of the declare section. The SQL statements which define your cursor must contain only SELECT statements -- there can never be any other keywords used.

When coding explicit cursors, you must always code 4 basic components:

* the cursor is defined in the declare section of your PL/SQL block

* the cursor is opened after the initial begin in the PL/SQL block

* the cursor is fetched into one or more variables -- there must be the same number of variables in the FETCH command as there are in the cursor's SELECT

* the cursor is closed after you are done using it

Consider the following example of a PL/SQL block:

create or replace procedure sc_cur1 as

-- DECLARE SECTION: declare variables and any cursors you might use

-- (this is also referred to as the outer block)



shipname varchar2(30);

shipclass varchar2(15);

shipalign varchar2(1);

cursor noalign is (1)

select (2)

sc_engine_alignment, sc_class, spacecraft_speed.sc_name (2.1)


spacecraft_engine, spacecraft_speed (2.2)


spacecraft_engine.sc_name = spacecraft_speed.sc_name


spacecraft_engine.sc_engine_alignment = 'N'; (2.3)

-- MAIN SECTION: your program code goes here (inner block)


open noalign;

loop (3)

fetch noalign into shipalign, shipclass, shipname; (4)

exit when noalign%notfound; (5)

if noalign%found then (6)

insert into spacecraft_repair (7)

values (shipname, null, sysdate, 'Pending');



end if;

end loop; (8)

close noalign;

end; -- END of MAIN SECTION (inner block)

end; -- END of DECLARE SECTION (outer block)

/ (9)

Example of a PL/SQL block titled "SC_CUR1" that will add entries to the SPACECRAFT_REPAIR table.

Line 1 begins the cursor definition. The cursor is named "noalign" and is defined by a SELECT statement, line 2, which will return the columns named in line 2.1 from the tables named in line 2.2. The actual condition for selecting a row of data is in line 2.3 -- if the engine is not aligned the row is returned by the SELECT statement.

Line 4 executes the cursor -- the values returned by the cursor are placed into the 3 variables named by the fetch command. The variables are defined in the declare section of the PL/SQL block.

Line 6 is an if statement that checks to see if a row was found -- the condition "%found" is a PL/SQL condition that evaluates to TRUE if the last fetch returned a row or to FALSE if no row was returned. If a row is returned, some information is inserted into another table.

Lines 3, 5, and 8 are not required for this cursor to execute. They are the lines the define the loop. If the block did not contain a loop, the cursor would only return the first row that fit the SELECT search criteria. However, because of the loop, the cursor is executed over and over again until it returns all the rows, one at a time, that fit its search criteria. The condition in line 5, "%notfound", is the opposite of "%found". It will evaluate to TRUE when the cursor is not returning any more information. Because of this, we can use it to exit the loop.

Line 9 is the "/" character. It is used to mark the end of a PL/SQL block. IMPLICIT CURSORS

Oracle implicitly opens a cursor to process SQL statements that is not associated with an explicitly declared cursor. This implicit cursor is commonly referred to as the SQL Cursor. That is, you can check conditions by referring to the cursor as if it were named "sql", as in "sql%found" and so forth.

Explicit cursors are much easier to use, more efficient, and generally more robust than implicit cursors (a great deal of exception handling must be done with implicit cursors). As such, there will be no discussion of implicit cursors in this guide.

To execute a cursor just go through the same steps as executing any other PL/SQL procedure. For example, to execute the explicit cursor defined above:

SQL> execute sc_cur1;

PL/SQL procedure successfully completed.


The SC_CUR1 procedure adds rows of information, according to the SELECT criteria, to the SPACECRAFT_REPAIR table. If an engine is out of alignment the ship is scheduled for maintenance on the current date, specified by SYSDATE, and the maintenance status is set to PENDING. The table would look something like the following:


------------------------- --------- --------- ------------------------

Klattu 03-JUN-96 Pending

Khan 03-JUN-96 Pending

Sample output of the SC_CUR1 procedure.