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.
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:
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
* 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
bytes
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
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.
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>;
The DECLARE section in PL/SQL programs is where variables and cursors (note: see section 7.16.6.8. 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
begin
declare
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 ......;
begin
...
end;
end;
/
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 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
7.16.6.8. 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
exit;
...
Example of the EXCEPTION PL/SQL command.
There are 3 types of if-statements in PL/SQL. They are outlined below.
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.
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
else
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;
else
if x < 5 then
y := FALSE;
else
x := x * 0.3;
end if;
end if;
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;
else
x := x * 0.3;
end if;
Important Note:
There is no matching end if with an elsif.
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
null;
else
insert into my_table values (z, 'Less than 10');
end if;
Example of the no-op, or null construct.
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.
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
loop
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.
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
loop
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.
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.
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.
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.
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)
begin
declare
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)
from
spacecraft_engine, spacecraft_speed (2.2)
where
spacecraft_engine.sc_name = spacecraft_speed.sc_name
and
spacecraft_engine.sc_engine_alignment = 'N'; (2.3)
-- MAIN SECTION: your program code goes here (inner block)
begin
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');
else
null;
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.
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.
SQL>
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:
SC_NAME SC_LAST_M SC_NEXT_M SC_MAINT_STAT
------------------------- --------- --------- ------------------------
Klattu 03-JUN-96 Pending
Khan 03-JUN-96 Pending
Sample output of the SC_CUR1 procedure.