PL/SQL Programming

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 a
s 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. 
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>