Oracle Tutoriel PL/SQL Dynamic SQL : Exécution immédiate et DBMS_SQL

Par : Fiona Brun Fiona Brun
Hours Le kit de préparation mis à jour

Qu’est-ce que le SQL dynamique ?

Dynamique SQL est une méthodologie de programmation permettant de générer et d'exécuter des instructions au moment de l'exécution. Il est principalement utilisé pour écrire des programmes polyvalents et flexibles dans lesquels les instructions SQL seront créées et exécutées au moment de l'exécution en fonction des besoins.

Façons d'écrire du SQL dynamique

PL/SQL propose deux façons d'écrire du SQL dynamique

  1. NDS – SQL dynamique natif
  2. DBMS_SQL

NDS (Native Dynamic SQL) – Exécution immédiate

Native Dynamic SQL est le moyen le plus simple d’écrire du SQL dynamique. Il utilise la commande 'EXECUTE IMMEDIATE' pour créer et exécuter le SQL au moment de l'exécution. Mais pour utiliser cette méthode, le type de données et le nombre de variables à utiliser au moment de l'exécution doivent être connus au préalable. Il offre également de meilleures performances et moins de complexité par rapport à DBMS_SQL.

Syntaxe 

EXECUTE IMMEDIATE(<SQL>)
[INTO<variable>]
[USING <bind_variable_value>]
  • La syntaxe ci-dessus montre la commande EXECUTE IMMEDIATE.
  • La clause INTO est facultative et utilisée uniquement si le SQL dynamique contient une instruction select qui récupère les valeurs. Le type de variable doit correspondre au type de variable de l'instruction select.
  • La clause USING est facultative et utilisée uniquement si le SQL dynamique contient une variable de liaison.

Exemple 1: Dans cet exemple, nous allons récupérer les données de la table emp pour emp_no '1001' à l'aide de l'instruction NDS.

NDS - Exécuter immédiatement

DECLARE
lv_sql VARCHAR2(500);
lv_emp_name VARCHAR2(50):
ln_emp_no NUMBER;
ln_salary NUMBER;
ln_manager NUMBER;
BEGIN
ly_sql:=;SELECT emp_name,emp_no,salary,manager FROM emp WHERE
emp_no=:empmo:;
EXECUTE IMMEDIATE lv_sql INTO lv_emp_name,ln_emp_no:ln_salary,ln_manager
USING 1001;
Dbms_output.put_line('Employee Name:‘||lv_emp_name);
Dbms_output.put_line('Employee Number:‘||ln_emp_no);
Dbms_output.put_line(‘Salary:'||ln_salaiy);
Dbms_output.put_line('Manager ID:‘||ln_manager);
END;
/

Sortie

Employee Name : XXX 
Employee Number: 1001 
Salary: 15000 
Manager ED: 1000

Explication du code :

  • Ligne de code 2-6: Déclaration de variables.
  • Ligne de code 8: Cadrage du SQL au moment de l'exécution. SQL contient la variable de liaison dans la condition Where ': empno'.
  • Ligne de code 9: Exécution du texte SQL encadré (qui se fait dans la ligne de code 8) à l'aide de la commande NDS 'EXECUTE IMMEDIATE'
  • Les variables de la clause 'INTO' (lv_emp_name, ln_emp_no, ln_salary, ln_manager) sont utilisées pour contenir les valeurs extraites de la requête SQL (emp_name, emp_no, salaire, manager)
  • La clause 'USING' donne les valeurs à la variable de liaison dans la requête SQL (:emp_no).
  • Ligne de code 10-13: Affichage des valeurs récupérées.

DBMS_SQL pour SQL dynamique

PL/SQL fournit le package DBMS_SQL qui vous permet de travailler avec du SQL dynamique. Le processus de création et d'exécution du SQL dynamique contient le processus suivant.

  • OUVRIR LE CURSEUR: Le SQL dynamique s'exécutera de la même manière qu'un curseur. Donc, pour exécuter l'instruction SQL, nous devons ouvrir le curseur.
  • PARSER SQL: L'étape suivante consiste à analyser le SQL dynamique. Ce processus vérifiera simplement la syntaxe et gardera la requête prête à être exécutée.
  • Valeurs de la variable de liaison: L'étape suivante consiste à attribuer les valeurs des variables de liaison, le cas échéant.
  • DÉFINIR LA COLONNE: L'étape suivante consiste à définir la colonne en utilisant leurs positions relatives dans l'instruction select.
  • EXÉCUTER: L'étape suivante consiste à exécuter la requête analysée.
  • RÉCUPÉRER LES VALEURS: L'étape suivante consiste à récupérer les valeurs exécutées.
  • FERMER LE CURSEUR: Une fois les résultats récupérés, le curseur doit être fermé.

Exemple 1: Dans cet exemple, nous allons récupérer les données de la table emp pour emp_no '1001' à l'aide de l'instruction DBMS_SQL.

DBMS_SQL pour SQL dynamique

DECLARE
lv_sql VARCHAR2(500);
lv_emp_name VARCHAR2(50);
ln_emp_no NUMBER;
ln_salary NUMBER;
ln_manager NUMBER;
ln_cursor_id NUMBER;
ln_rows_processed;
BEGIN
lv_sql:=‘SELECT emp_name,emp_no,salary,manager FROM emp WHERE
emp_no=:empmo’;
in_cursor_id:=DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE(ln_cursor_id,lv_sql,DBMS_SQL.NATIVE);

DBMS_SQL.BIXD_VARLABLE(ln_cursor_id,:‘empno‘,1001);

DBMS_SQL.DEFINE_COLUMN(ln_cursor_ici,1,ln_emp_name);
DBMS_SQL.DEFINE_COLUMN(ln_cursor_id,2,ln_emp_no);
DBMS_SQL .DEFINE_COLUMN(ln_cursor_id,3,ln_salary);
DBMS_SQL .DEFINE_COLUMN(ln_cursor_id,4,ln_manager);

ln_rows__processed:=DBMS_SQL.EXECUTE(ln_cursor_id);

DBMS_SQL pour SQL dynamique

LOOP
IF DBMS_SQL.FETCH_ROWS(ln_cursor_id)=0
THEN
EXIT;
ELSE
DBMS_SQL.COLUMN_VALUE(ln_cursor_id,1,lv_emp_name); 
DBMS_SQL.COLUMN_VALUE(ln_cursor_id,2,ln_emp_no);
DBMS_SQL.COLUMN_VALUE(ln_cursor_id,3,In_salary);
DBMS_SQL.COLUMN_VALUE(ln_cursor_id,4,In_manager);
Dbms_output.put_line('Employee Name:‘||lv_emp_name); 
Dbms_output.put_line('Employee Number:l‘||ln_emp_no); 
Dbms_output.put_line(‘Salary:‘||ln_salary); 
Dbms_output.put_line('Manager ID :‘| ln_manager);
END IF;
END LOOP;

DBMS_SQL.CLOSE_ClIRSOR(ln_cursor_id);

END:
/

Sortie

Employee Name:XXX 
Employee Number:1001 
Salary:15000 
Manager ID:1000

Explication du code :

  • Ligne de code 1-9: Déclaration de variables.
  • Ligne de code 10: Encadrement de l'instruction SQL.
  • Ligne de code 11: Ouverture du curseur à l'aide de DBMS_SQL.OPEN_CURSOR. Il renverra l’identifiant du curseur qui est ouvert.
  • Ligne de code 12: Une fois le curseur ouvert, le SQL est analysé.
  • Ligne de code 13: La variable de liaison '1001' est attribuée à l'identifiant du curseur à la place de ':empno'.
  • Ligne de code 14-17: Définition du nom des colonnes en fonction de leur position relative dans l'instruction SQL. Dans notre cas, la position relative est (1) emp_name, (2) emp_no (3) salaire (4) manager. Donc, sur la base de cette position, nous définissons la variable cible.
  • Ligne de code 18: Exécution de la requête à l'aide de DBMS_SQL.EXECUTE. Il renvoie le nombre d'enregistrements traités.
  • Ligne de code 19-33: Récupérer les enregistrements à l'aide d'une boucle et les afficher.
  • Ligne de code 20 : DBMS_SQL.FETCH_ROWS récupérera un enregistrement des lignes traitées. Il peut être appelé à plusieurs reprises pour récupérer toutes les lignes. S'il ne peut pas récupérer les lignes, il renverra 0, sortant ainsi de la boucle.

Résumé

Dans cette section, nous avons discuté du SQL dynamique et des moyens d'exécuter DYNAMIC SQL. Nous avons également vu les différentes étapes d'exécution du SQL dynamique dans les deux sens. Nous avons également vu des exemples dans lesquels le même scénario est géré à la fois de manière NDS et DBMS_SQL pour effectuer l'exécution au moment de l'exécution.

Tu pourrais aimer: