interagir avec oracle

Copyright © Oracle, 2004. Tous droits réservés.

Interagir avec le serveur Oracle


Objectifs

A la fin de ce chapitre, vous pourrez :

• déterminer les instructions SQL pouvant être

incluses directement dans un bloc exécutable

PL/SQL

• manipuler des données à l'aide d'instructions LMD

dans du code PL/SQL

• utiliser des instructions de gestion des transactions

dans le code PL/SQL

• utiliser la clause INTO pour le stockage des valeurs

renvoyées par une instruction SQL

• distinguer les curseurs implicites des curseurs

explicites

• utiliser des attributs de curseur SQL


Instructions SQL en langage PL/SQL

• Extrayez une ligne de la base de données à l'aide de la commande SELECT.

• Modifiez des lignes de la base de données en

utilisant des commandes LMD.

• Contrôlez une transaction avec la commande COMMIT, ROLLBACK.


Instructions SELECT en langage PL/SQL

Extrayez les données de la base à l'aide d'une instruction SELECT.

Syntaxe :

SELECT select_list

INTO {variable_name[, variable_name]...

| record_name}

FROM table

[WHERE condition];


Instructions SELECT en langage PL/SQL

• La clause INTO est obligatoire.

• Les interrogations doivent renvoyer une seule ligne.

Exemple :

SET SERVEROUTPUT ON

DECLARE

fname VARCHAR2(25);

BEGIN

SELECT first_name INTO fname

FROM employees WHERE employee_id=200;

DBMS_OUTPUT.PUT_LINE(' First Name is : '||fname);

END;

/


Extraire des données en langage PL/SQL

Extrayez les valeurs hire_date et salary de

l'employé désigné.

Exemple :

DECLARE

emp_hiredate employees.hire_date%TYPE;

emp_salary employees.salary%TYPE;

BEGIN

SELECT hire_date, salary

INTO emp_hiredate, emp_salary

FROM employees

WHERE employee_id = 100;

END;

/


SET SERVEROUTPUT ON

DECLARE

sum_sal NUMBER(10,2);

deptno NUMBER NOT NULL := 60;

BEGIN

SELECT SUM(salary) -- group function

INTO sum_sal FROM employees

WHERE department_id = deptno;

DBMS_OUTPUT.PUT_LINE ('The sum of salary is '

|| sum_sal);

END;

/

Extraire des données en langage PL/SQL

Renvoyez la somme des salaires de tous les employés

du département désigné.

Exemple :


Manipuler les données en langage PL/SQL

Modifiez des tables de base de données en utilisant

des instructions LMD :

• INSERT

• UPDATE

• DELETE

INSERT

UPDATE

DELETE


Insérer des données

Ajoutez les informations relatives à un nouvel employé à la table EMPLOYEES.

Exemple :

BEGIN

INSERT INTO employees

(employee_id, first_name, last_name, email,

hire_date, job_id, salary)

VALUES(employees_seq.NEXTVAL, 'Ruth', 'Cores',

'RCORES',sysdate, 'AD_ASST', 4000);

END;

/


Mettre à jour des données

Augmentez le salaire de tous les employés chargés du

contrôle des stocks.

Exemple :DECLARE

sal_increase employees.salary%TYPE := 800;

BEGIN

UPDATE employees

SET salary = salary + sal_increase

WHERE job_id = 'ST_CLERK';

END;

/


Supprimer des données

Supprimez les lignes appartenant au département 10 à partir de la table employees.

Exemple :

DECLARE

deptno employees.department_id%TYPE := 10;

BEGIN

DELETE FROM employees


END;

/


Curseur SQL

• Un curseur est un pointeur vers la zone de

mémoire privée allouée par le serveur Oracle.

• Il existe deux types de curseur :

– Curseurs implicites : créés et gérés en interne par

le serveur Oracle afin de traiter les instructions SQL

– Curseurs explicites : déclarés explicitement par le

programmeur


Attributs de curseur SQL pour les

curseurs implicites

Grâce aux attributs de curseur SQL, vous pouvez

tester le résultat de l'exécution des instructions SQL.

SQL%FOUND Attribut booléen qui prend la valeur TRUE si la

dernière instruction SQL a renvoyé au moins

une ligne

SQL%NOTFOUND Attribut booléen qui prend la valeur TRUE si la

dernière instruction SQL n'a renvoyé aucune

ligne

SQL%ROWCOUNT Valeur entière qui représente le nombre de

lignes affectées par l'instruction SQL la plus

récente


Attributs de curseur SQL pour les

curseurs implicites

Supprimez de la table employees les lignes avec l'ID

d'employé désigné. Affichez le nombre de lignes

supprimées.

Exemple :

VARIABLE rows_deleted VARCHAR2(30)

DECLARE

empno employees.employee_id%TYPE := 176;

BEGIN

DELETE FROM employees

WHERE employee_id = empno;

:rows_deleted := (SQL%ROWCOUNT ||

' row deleted.');

END;

/

PRINT rows_deleted


Utiliser des curseurs explicites


Objectifs

A la fin de ce chapitre, vous pourrez :

• faire la différence entre un curseur implicite et un

curseur explicite

• déterminer quand et pourquoi utiliser un curseur

explicite

• déclarer et contrôler des curseurs explicites

• utiliser une boucle simple et une boucle FOR de

curseur pour extraire (fetch) des données

• déclarer et utiliser des curseurs avec des paramètres

• verrouiller des lignes à l'aide de la clause FOR

UPDATE

• référencer la ligne actuelle avec la clause WHERE

CURRENT


A propos des curseurs

A chaque instruction SQL exécutée par le serveur

Oracle est associé un curseur individuel :

• Curseurs implicites : déclarés et gérés par le

compilateur PL/SQL pour toutes les instructions LMD et les instructions SELECT PL/SQL

• Curseurs explicites : déclarés et gérés par le

programmeur


Opérations de curseur explicite

Ensemble actif

Table

100 King AD_PRES

101 Kochhar AD_VP

102 De Haan AD_VP

. . .

. . .

. . .

139 Seo ST_CLERK

140 Patel ST_CLERK

. . .


Contrôler les curseurs explicites

• Charger

la ligne

en cours

dans des

variables

FETCH

• Tester

l'existence

de lignes

VIDE?

• Si des

lignes

existent,

revenir à FETCH

Non

• Libérer

l'ensemble

actif

CLOSEOui

• Créer une

zone SQL

nommée

DECLARE

• Identifier

l'ensemble

actif

OPEN


Contrôler les curseurs explicites

Extraction (fetch) d'une ligne

Fermeture du curseur

Pointeur de curseur

Ouverture du curseur1

2

3

Pointeur de curseur

Pointeur de curseur


Déclarer le curseur

Syntaxe :

CURSOR cursor_name IS

select_statement;

Exemples :

DECLARE

CURSOR emp_cursor IS

SELECT employee_id, last_name FROM employees

WHERE department_id =30;

DECLARE

locid NUMBER:= 1700;

CURSOR dept_cursor IS

SELECT * FROM departments

WHERE location_id = locid;

...


Ouvrir le curseur

DECLARE




...

BEGIN

OPEN emp_cursor;


Extraire des données du curseur

SET SERVEROUTPUT ON

DECLARE




empno employees.employee_id%TYPE;

lname employees.last_name%TYPE;

BEGIN

OPEN emp_cursor;

FETCH emp_cursor INTO empno, lname;

DBMS_OUTPUT.PUT_LINE( empno ||' '||lname);

...

END;

/


Extraire des données du curseur

SET SERVEROUTPUT ON

DECLARE





lname employees.last_name%TYPE;

BEGIN

OPEN emp_cursor;

LOOP


EXIT WHEN emp_cursor%NOTFOUND;


END LOOP;

...

END;

/


Fermer le curseur

...

LOOP


EXIT WHEN emp_cursor%NOTFOUND;


END LOOP;

CLOSE emp_cursor;

END;

/


Curseurs et enregistrements

Traitez les lignes de l'ensemble actif en extrayant

(fetch) les valeurs pour les placer dans un

enregistrement PL/SQL.

DECLARE




emp_record emp_cursor%ROWTYPE;

BEGIN

OPEN emp_cursor;

LOOP

FETCH emp_cursor INTO emp_record;

...


Boucles FOR de curseur

Syntaxe :

• La boucle FOR de curseur simplifie le traitement

des curseurs explicites.

• Des opérations d'ouverture, d'extraction (fetch),

de sortie et de fermeture ont lieu de manière

implicite.

• L'enregistrement est déclaré implicitement.

FOR record_name IN cursor_name LOOP

statement1;

statement2;

. . .

END LOOP;


Boucles FOR de curseur

SET SERVEROUTPUT ON

DECLARE




BEGIN

FOR emp_record IN emp_cursor

LOOP

DBMS_OUTPUT.PUT_LINE( emp_record.employee_id

||' ' ||emp_record.last_name);

END LOOP;

END;

/


Attributs d'un curseur explicite

Obtenir les informations d'état concernant un curseur

Booléen Prend la valeur TRUE si le curseur est

ouvert

Prend la valeur TRUE si la dernière

extraction (fetch) ne renvoie pas de

ligne

Prend la valeur TRUE si la dernière

extraction renvoie une ligne ; complément de %NOTFOUND

Prend la valeur correspondant au

nombre total de lignes renvoyées

jusqu'à présent

Booléen

Booléen

Nombre

%ISOPEN

%NOTFOUND

%FOUND

%ROWCOUNT

Attribut Type Description


Attribut %ISOPEN

• Extrayez (fetch) les lignes uniquement lorsque le

curseur est ouvert.

• Utilisez l'attribut de curseur %ISOPEN avant de

réaliser une extraction pour déterminer si le

curseur est ouvert.

Exemple :

IF NOT emp_cursor%ISOPEN THEN

OPEN emp_cursor;

END IF;

LOOP

FETCH emp_cursor...


Exemple d'utilisation des attributs %ROWCOUNT et %NOTFOUND

SET SERVEROUTPUT ON

DECLARE


ename employees.last_name%TYPE;

CURSOR emp_cursor IS SELECT employee_id,

last_name FROM employees;

BEGIN

OPEN emp_cursor;

LOOP

FETCH emp_cursor INTO empno, ename;

EXIT WHEN emp_cursor%ROWCOUNT > 10 OR

emp_cursor%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(TO_CHAR(empno)

||' '|| ename);

END LOOP;

CLOSE emp_cursor;

END ;

/


Boucles FOR de curseur utilisant

des sous-interrogations

Il n'est pas nécessaire de déclarer le curseur.

Exemple :

SET SERVEROUTPUT ON

BEGIN

FOR emp_record IN (SELECT employee_id, last_name

FROM employees WHERE department_id =30)

LOOP

DBMS_OUTPUT.PUT_LINE( emp_record.employee_id ||'

'||emp_record.last_name);

END LOOP;

END;

/


Curseurs avec paramètres

Syntaxe :

• Transmettez des paramètres au curseur au

moment de son ouverture et de l'exécution

de l'interrogation.

• Ouvrez un curseur explicite à plusieurs reprises,

en renvoyant un ensemble actif différent à chaque

fois.

CURSOR cursor_name

[(parameter_name datatype, ...)]

IS

select_statement;

OPEN cursor_name(parameter_value,.....) ;


Curseurs avec paramètres

SET SERVEROUTPUT ON

DECLARE

CURSOR emp_cursor (deptno NUMBER) IS

SELECT employee_id, last_name

FROM employees


dept_id NUMBER;

lname VARCHAR2(15);

BEGIN

OPEN emp_cursor (10);

...

CLOSE emp_cursor;

OPEN emp_cursor (20);

...

interagir avec oracle

Documents