[DECLARE
... déclarations et initialisation]
BEGIN
... instructions exécutables
[EXCEPTION
... interception des erreurs]
END;

BEGIN
   Null ;
END ;

 1 Booléen 
  2 Date/heure
  3 caractère
  4 Nombre

   Date : premier jour du mois courant
  Heure : minuit

 
	CHAR, 
	VARCHAR2, 
	LONG,
	RAW et 
	LONG RAW. 
 

TYPE RecipientTabTyp IS TABLE OF NUMBER(22)
INDEX BY BINARY_INTEGER;
...
g_recip_list RecipientTabTyp;

TYPE LineRecTyp IS RECORD
(merch_gross NUMBER := 0,
recip_num NUMBER := 0 );
...
li_info LineRecTyp;

OPEN c_line_item;
...
FETCH c_line_item
INTO li_info;

DECLARE
anniversaire  DATE;
emp_compt SMALLINT := 0;

DECLARE
	... --- les déclaration qui précident s'il y en a
	nom_constante CONSTANTTYPE_CONSTANTE := valeur_constante;
	... --- les déclarations qui suivent s'il y en a et la suite du programme

blood_type CHAR := 'O';-- est identique à la déclaration ci-dessus
blood_type CHAR DEFAULT 'O';

heures_travaillees INTEGER DEFAULT 40;
nombre_employees INTEGER := 0;

	DECLARE
	acct_id INTEGER(4) NOT NULL := 9999;

DECLARE
   credit PLS_INTEGER RANGE 1000..25000;
   debit  credit%TYPE;
   v_name VARCHAR2(20);
   name VARCHAR2(20) NOT NULL := 'Omara Aly';
-- If we increase the length of NAME, the other variables become longer also
   upper_name name%TYPE := UPPER(name);
   lower_name name%TYPE := LOWER(name);
   init_name name%TYPE := INITCAP(name);
BEGIN
-- display inherited default values
   DBMS_OUTPUT.PUT_LINE('name: ' || name || ' upper_name: ' || upper_name 
          || ' lower_name: ' || lower_name || ' init_name: ' || init_name);
-- lower_name := 'Omara Aly Mohamed'; invalid, character string is too long
-- lower_name := NULL; invalid, NOT NULL CONSTRAINT
-- debit := 50000; invalid, value out of range
END;
/

DECLARE
-- If the length of the column ever changes, this code
-- will use the new length automatically.
   the_trigger user_triggers.trigger_name%TYPE;

CREATE TABLE employees_temp (empid NUMBER(6) NOT NULL PRIMARY KEY, 
 deptid NUMBER(6) CONSTRAINT check_deptid CHECK (deptid BETWEEN 100 AND 200),
 deptname VARCHAR2(30) DEFAULT 'Sales');

DECLARE
   v_empid    employees_temp.empid%TYPE;
   v_deptid   employees_temp.deptid%TYPE;
   v_deptname employees_temp.deptname%TYPE;
BEGIN
   v_empid := NULL;  -- this works, null constraint is not inherited
-- v_empid := 10000002; -- invalid, number precision too large 
   v_deptid := 50; -- this works, check constraint is not inherited
-- the default value is not inherited in the following
   DBMS_OUTPUT.PUT_LINE('v_deptname: ' || v_deptname);
END;
/

DECLARE
   emprec    employees_temp%ROWTYPE;
BEGIN
   emprec.empid := NULL;  -- this works, null constraint is not inherited
-- emprec.empid := 10000002; -- invalid, number precision too large
   emprec.deptid := 50; -- this works, check constraint is not inherited
-- the default value is not inherited in the following
   DBMS_OUTPUT.PUT_LINE('emprec.deptname: ' || emprec.deptname); 
END;
/

DECLARE
-- %ROWTYPE can include all the columns in a table...
   emp_rec employees%ROWTYPE;
-- ...or a subset of the columns, based on a cursor.
   CURSOR c1 IS
      SELECT department_id, department_name FROM departments;
   dept_rec c1%ROWTYPE;
-- Could even make a %ROWTYPE with columns from multiple tables.
   CURSOR c2 IS
      SELECT employee_id, email, employees.manager_id, location_id
      FROM employees, departments
      WHERE employees.department_id = departments.department_id;
   join_rec c2%ROWTYPE;
BEGIN
-- We know EMP_REC can hold a row from the EMPLOYEES table.
   SELECT * INTO emp_rec FROM employees WHERE ROWNUM < 2;
-- We can refer to the fields of EMP_REC using column names
-- from the EMPLOYEES table.
   IF emp_rec.department_id = 20 AND emp_rec.last_name = 'JOHNSON' THEN
      emp_rec.salary := emp_rec.salary * 1.15;
   END IF;
END;
/

DECLARE
   dept_rec1 departments%ROWTYPE;
   dept_rec2 departments%ROWTYPE;
   CURSOR c1 IS SELECT department_id, location_id FROM departments;
   dept_rec3 c1%ROWTYPE;
BEGIN
   dept_rec1 := dept_rec2;  -- allowed
-- dept_rec2 refers to a table, dept_rec3 refers to a cursor
-- dept_rec2 := dept_rec3;  -- not allowed
END;
/

DECLARE
   dept_rec departments%ROWTYPE;
BEGIN
   SELECT * INTO dept_rec FROM departments
      WHERE department_id = 30 and ROWNUM < 2;
END;
/

BEGIN
-- We assign an alias (complete_name) to the expression value, because
-- it has no column name.
   FOR item IN
   (  SELECT first_name || ' ' || last_name complete_name
      FROM employees WHERE ROWNUM < 11  )
   LOOP
-- Now we can refer to the field in the record using this alias.
     DBMS_OUTPUT.PUT_LINE('Employee name: ' || item.complete_name);
   END LOOP;
END;
/

DECLARE
-- la ligne ci-dessus n'est pas valable
-- i, j, k, l SMALLINT;
-- Instead, declare each separately.
-- ce qui suit est valable
   i SMALLINT;
   j SMALLINT;
-- To save space, you can declare more than one on a line.
   k SMALLINT; l SMALLINT;

<<nom>>
LOOP
(traitement répétitif)
END LOOP nom;

WHILE (expression) LOOP
(traitement de boucle)
END LOOP;

FOR i in 1..g_line_counter LOOP
(traitement de boucle)
END LOOP recip_list;

CURSOR c_line_item IS
(instruction sql)
BEGIN
FOR li_info IN c_line_item LOOP
(traitement de l'enregistrement extrait)
END LOOP;
END;

init_recip_list
IS
recipient_num NUMBER;
i BINARY_INTEGER;
j BINARY_INTEGER := 1;
k BINARY_INTEGER;
BEGIN
g_out_msg := 'init_recip_list';
<<recip_list>>
FOR i in 1..g_line_counter LOOP
IF i = 1 THEN
g_recip_list(j) := g_recipient_num(i);
j := j + 1;
g_recip_list(j) := 0;
ELSE
FOR k in 1..j LOOP
IF g_recipient_num(i) = g_recip_list(k) THEN
exit;
ELSIF k = j THEN
g_recip_list(j) := g_recipient_num(i);
j := j + 1;
14
g_recip_list(j) := 0;
end IF;
end LOOP;
end IF;
end LOOP recip_list;
END;

open c_line_item;
loop
fetch c_line_item
into li_info;
EXIT WHEN (c_line_item%NOTFOUND) or (c_line_item%NOTFOUND is NULL);

CURSOR c_line_item IS
(instruction sql)

OPEN c_line_item;
...
FETCH c_line_item
INTO li_info;
...
(traitement de la ligne extraite)
...
CLOSE c_line_item;

{ %NOTFOUND
{ %FOUND
{ %ROWCOUNT
{ %ISOPEN

CURSOR c_line_item (order_num IN NUMBER) IS
SELECT merch_gross, recipient_num
FROM line_item
WHERE order_num = g_order_num;

CURSOR c_line_item
(order_num INTEGER DEFAULT 100,
line_num INTEGER DEFAULT 1) IS ...

CREATE OR REPLACE PACKAGE order_total
AS
CURSOR c_line_item RETURN line_item.merch_gross%TYPE;
...
END order_total;
CREATE OR REPLACE PACKAGE BODY order_total
AS
CURSOR c_line_item RETURN line_item.merch_gross%TYPE
SELECT merch_gross
FROM line_item
WHERE order_num = g_order_num;
...
END order_total;

merch_gross NUMBER;
recip_count BINARY_INTEGER;

SELECT merch_gross
INTO merch_gross
FROM line_item
WHERE order_num = g_order_num;

CURSOR c_line_item IS
SELECT merch_gross, recipient_num
FROM line_item
WHERE order_num = g_ordnum;
li_info c_line_item%ROWTYPE;
Pour extraire les données, on utilise FETCH :
FETCH c_line_item
INTO li_info;

set echo on
spool order_total
CREATE OR REPLACE PACKAGE order_total
AS
(spécifications du package)
END order_total
CREATE OR REPLACE PACKAGE BODY order_total
AS
(spécifications du corps du package)
END order_total;
DROP PUBLIC SYNONYM order_total;
CREATE PUBLIC SYNONYM order_total for order_total;
GRANT EXECUTE ON order_total TO PUBLIC;
spool off
SELECT *
FROM user_errors
WHERE name='ORDER_TOTAL'
;