1. Introduction:
The main unit of PL/SQL
The basic unit in PL/SQL is a block. A PL/SQL program is made up
of blocks. Each block performs a logical action in he program.
The structure of a block contains the following four actions:
DECLARE
In this first section, we declare:
section: types, variables, and local subprograms.
BEGIN
In this required second section, the procedures, and the SQL statements
are executed.
EXCEPTION
In this third section, we state errorr handling in the form
of exception:
END;
To end the action.
Note that PL/SQL is not case sensitive.
2. PL/SQL program, main features:
In a PL/SQL program, only SELECT, INSERT, UPDATE, DELETE SQL statements
are allowed. CREATE, ALTER, or DROP or are not allowed.
To execute a PL/SQL program, the four actions (of the block) are
followed by a line with a single dot ("."), and then a line with run;
A PL/SQL program is invoked by sqlplus over the command line interpreter
(prompt) or by a file that contains the program.
3. Variables:
Variables are declared within the DECLARE section of the block. The variables
allow PL/SQL to manipulate data stored in a database. As many languages, every variable is
specified by its type.
In PL/SQL, we have three kinds of types:
- The type used in SQL related to the columns of a table in a database,
- The generic type used in PL/SQL program such as NUMBER, VARCHAR(n), ...
- The declared type to be the same as the type used by a database column.
The Variables of type NUMBER can hold either an integer or a real number. The variables of
type VARCHAR(n) hold characters or strings. The digit "n" is required. It is the maximum
length of the string in bytes. BOOLEAN variables are allowed in PL/SQL and not supported in
Oracle.
The type variables in PL/SQL program and their corresponding database columns must me
the same. To make sure that it is really the case, we declare this type by using the
%TYPE operator. For example: In the table (or relation) "Physicists", the column
"last_name" has the type VARCHAR2(15). To declare another variable such as a nick_name to
have the same type as "last_name", we use:
DECLARE
nick_name Physicists.last_name%TYPE;
DECLARE
variable1 Relation.variable2%TYPE;
/* variable1 is a PL/SQL variable, variable2 is a column database name (or field name),
nad "Relation" is the name of the table in the related database.*/
within a table (relation) a row (record) corresponds generally to several columns (field). A record
has a type. To declare such a variable, we use the operator %ROWTYPE as follows:
DECLARE
this_person Physicists%ROWTYPE;
The variable "this_person" is a record. Its type maintains the same names and types as the fields of
the related table "Physicists".
We assign values to variables by using the ":=" operator. The assignment can occur during
the declaration or anywhere in the executable sectionn of the program. The initial value of any variable
is set NULL. For example:
4. Printing Variables
When we want to print a value with SQL, we use SELECT. With PL/SQ, we define a bind variable, that will
be printed with print. Bind variables work like that:
- Declare a bind variable alone (before DECLARE) as follows:
VARIABLE name type
The type can be: NUMBER, CHAR, or CHAR(n) only.
- Assign to this bind variable prefixed with a colon, a value or an expression
- Execute after the PL/SQL statementa (after run;) as:
PRINT :name;
Other way to print:
We can also print to the screen from PL/SQL by using the existing DBMS_OUTPUT package
An ouput result is set in a buffer that we can display on a screen. We can do this from SQL*Plus
by issuing the SET SERVEROUTPUT ON; command. For example:
SQL> SET SERVEROUTPUT ON
/*To prevent overflow ouput buffer, we use: set serveroutput on size xxxxxx ( the
large value xxxxxx = 350000 for example*/
SQL> SET SERVEROUTPUT ON
SQL> begin
2 dbms_output.put_line('Regards');
3 dbms_output.put_line('The best');
4 end;
5 /
Regards
The best
PL/SQL procedure successfully completed.
SQL>
To print a blanck line, we use the SET SERVEROUTPUT ON FORMAT WRAP. For
example:
SQL> SET SERVEROUTPUT ON FORMAT WRAP
SQL> begin
2 dbms_output.put_line('Regards .. ');
3 dbms_output.put_line(''); /* new line */
4 dbms_output.put_line('The best of luck ..');
5 end;
6 /
Regards ..
The best of luck ..
PL/SQL procedure successfully completed.
SQL>
5. Example of a bsic program:
VARIABLE surface NUMBER
DECLARE
radius NUMBER;
my_var_pi NUMBER := 3.14016;
BEGIN
radius := 2;
:surface := radius * my_var_pi * radius;
END;
.
run;
This program calculates the surface of a disk of radius 2.
The output is:
SQL> VARIABLE surface NUMBER
SQL> DECLARE
2 radius NUMBER;
3 my_var_pi NUMBER := 3.14016;
4 BEGIN
5 radius := 2;
6 :surface := radius * my_var_pi * radius;
7 END;
8 .
SQL> run;
1 DECLARE
2 radius NUMBER;
3 my_var_pi NUMBER := 3.14016;
4 BEGIN
5 radius := 2;
6 :surface := radius * my_var_pi * radius;
7* END;
PL/SQL procedure successfully completed.
SQL> print :surface;
SURFACE
----------
12,56064
SQL>
6. Structure of a PL/SQL program:
A basic PL/SQL program contains some declarations (in DECLARE section) follwed by the
executable section (BEGIN) that contains SQL statements. The particular statement is
SELECT. In PL/SQl, SELECT must be followed by INTO clause. The attributes of SELECT
clause are the column variables from the table (relation). These attributes will be retreived and
placed in the INTO clause declared variables. The statement is then as follows:
SELECT attributes (clomn variables) INTO declared_variables FROM A_table- WHERE condition;
The result of the query must be a sigle row or a single tuple (sequense or ordered list of
components). This is because that's the way SELECT in PL/SQL works. In the case that the
query needs many tuples, we have to use the cursor.
Example:
We have already created a tables "Physisicts"; that contains the following fields:
phys_id NUMBER(3,0) NOT NULL,
first_name VARCHAR2(10) NOT NULL,
last_name VARCHAR2(15),
birth_date NUMBER(4,0),
death_date NUMBER(4,0),
discipline VARCHAR2(20));
Now, we built a simple PL/SQL program that will output the physicists that
died before the world war I (1914 to 1918).
DECLARE
x VARCHAR2(10);
y VARCHAR2(15);
z NUMBER(4,0);
BEGIN
SELECT first_name,last_name,death_date INTO x,y,z FROM Physicists WHERE death_date <= 1918;
DBMS_OUTPUT.PUT_LINE('This is the result:');
DBMS_OUTPUT.PUT_LINE(' '||x ||' '||y ||' '||z);
END;
.
run;
The pouput is:
SQL> DECLARE
2 x VARCHAR2(10);
3 y VARCHAR2(15);
4 z NUMBER(4,0);
5 BEGIN
6 SELECT first_name,last_name,death_date INTO x,y,z FROM Physicists WHERE death_date <= 1918;
7 DBMS_OUTPUT.PUT_LINE('This is the result:');
8 DBMS_OUTPUT.PUT_LINE(' '||x ||' '||y ||' '||z);
9 END;
10 .
SQL> run;
1 DECLARE
2 x VARCHAR2(10);
3 y VARCHAR2(15);
4 z NUMBER(4,0);
5 BEGIN
6 SELECT first_name,last_name,death_date INTO x,y,z FROM Physicists WHERE death_date <= 1918;
7 DBMS_OUTPUT.PUT_LINE('This is the result:');
8 DBMS_OUTPUT.PUT_LINE(' '||x ||' '||y ||' '||z);
9* END;
This is the result:
Ludwig Boltzmann 1906
PL/SQL procedure successfully completed.
SQL>
7. Cursors:
In the case of an answer to a query will output a result with several rows (tuples), we need
to use the cursor. It is a variable that goes through the tuples of a table (relation).
Example:
This program select and print the physicists from the relation "Physicists" whose
died before the World War II (1945).
SET SERVEROUTPUT ON
DECLARE
x Physicists.first_name%TYPE;
y Physicists.last_name%TYPE;
z Physicists.death_date%TYPE;
CURSOR Phys_Cursor IS
SELECT first_name, last_name, death_date
FROM Physicists
WHERE death_date < 1945;
BEGIN
DBMS_OUTPUT.PUT_LINE('This is the result:');
OPEN Phys_Cursor;
LOOP
FETCH Phys_Cursor INTO x, y, z;
EXIT WHEN Phys_Cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(' '||x ||' '||y ||' '||z);
END LOOP;
CLOSE Phys_Cursor;
END;
.
run;
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 x Physicists.first_name%TYPE;
3 y Physicists.last_name%TYPE;
4 z Physicists.death_date%TYPE;
5 CURSOR Phys_Cursor IS
6 SELECT first_name, last_name, death_date
7 FROM Physicists
8 WHERE death_date < 1945;
9 BEGIN
10 DBMS_OUTPUT.PUT_LINE('This is the result:');
11 OPEN Phys_Cursor;
12 LOOP
13 FETCH Phys_Cursor INTO x, y, z;
14 EXIT WHEN Phys_Cursor%NOTFOUND;
15 DBMS_OUTPUT.PUT_LINE(' '||x ||' '||y ||' '||z);
16 END LOOP;
17 CLOSE Phys_Cursor;
18 END;
19 .
Execution
---------
SQL> run;
1 DECLARE
2 x Physicists.first_name%TYPE;
3 y Physicists.last_name%TYPE;
4 z Physicists.death_date%TYPE;
5 CURSOR Phys_Cursor IS
6 SELECT first_name, last_name, death_date
7 FROM Physicists
8 WHERE death_date < 1945;
9 BEGIN
10 DBMS_OUTPUT.PUT_LINE('This is the result:');
11 OPEN Phys_Cursor;
12 LOOP
13 FETCH Phys_Cursor INTO x, y, z;
14 EXIT WHEN Phys_Cursor%NOTFOUND;
15 DBMS_OUTPUT.PUT_LINE(' '||x ||' '||y ||' '||z);
16 END LOOP;
17 CLOSE Phys_Cursor;
18* END;
The output is:
---------------
This is the result:
Joseph Thomson 1940
Ernest Rutherford 1937
Ludwig Boltzmann 1906
PL/SQL procedure successfully completed.
SQL>
|