Tutoriel Oracle PL / SQL Dynamic SQL: exécution immédiate & SGBD_SQL

Table des matières:

Anonim

Qu'est-ce que Dynamic SQL?

Dynamic SQL est une méthodologie de programmation pour générer et exécuter des instructions au moment de l'exécution. Il est principalement utilisé pour écrire les 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.

Dans ce didacticiel, vous apprendrez-

  • Façons d'écrire du SQL dynamique
  • NDS (Native Dynamic SQL) - Exécution immédiate
  • DBMS_SQL pour Dynamic SQL

Façons d'écrire du SQL dynamique

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

  1. NDS - SQL dynamique natif
  2. SGBD_SQL

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

Le SQL dynamique natif 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 lors de l'exécution doivent être connus avant. Il offre également de meilleures performances et moins de complexité par rapport à DBMS_SQL.

Syntaxe 

EXECUTE IMMEDIATE()[INTO][USING ]
  • 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 de sélection 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.

DECLARElv_sql VARCHAR2(500);lv_emp_name VARCHAR2(50):ln_emp_no NUMBER;ln_salary NUMBER;ln_manager NUMBER;BEGINly_sql:=;SELECT emp_name,emp_no,salary,manager FROM emp WHEREemp_no=:empmo:;EXECUTE IMMEDIATE lv_sql INTO lv_emp_name,ln_emp_no:ln_salary,ln_managerUSING 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;/

Production

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

Explication du code:

  • Ligne de code 2-6 : déclaration des variables.
  • Ligne de code 8 : encadrement du SQL au moment de l'exécution. SQL contient la variable de liaison dans la condition où «: empno».
  • Ligne de code 9 : exécution du texte SQL encadré (ce 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 Dynamic SQL

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.

  • OPEN CURSOR : 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.
  • PARSE SQL : l'étape suivante consiste à analyser le SQL dynamique. Ce processus vérifiera simplement la syntaxe et maintiendra la requête prête à être exécutée.
  • Valeurs de la variable de liaison: l'étape suivante consiste à affecter les valeurs des variables de liaison, le cas échéant.
  • DEFINE COLUMN : l'étape suivante consiste à définir la colonne en utilisant leurs positions relatives dans l'instruction select.
  • EXECUTE : l'étape suivante consiste à exécuter la requête analysée.
  • FETCH VALUES : l'étape suivante consiste à récupérer les valeurs exécutées.
  • CLOSE CURSOR : 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.

DECLARElv_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;BEGINlv_sql:=‘SELECT emp_name,emp_no,salary,manager FROM emp WHEREemp_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);
LOOPIF DBMS_SQL.FETCH_ROWS(ln_cursor_id)=0THENEXIT;ELSEDBMS_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:/

Production

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

Explication du code:

  • Ligne de code 1-9 : déclaration de variable.
  • 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 affectée à l'ID du curseur à la place de ': empno'.
  • Ligne de code 14-17 : Définition du nom de la colonne en fonction de sa position relative dans l'instruction SQL. Dans notre cas, la position relative est (1) nom_emp, (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ération des enregistrements en utilisant une boucle et affichage de celle-ci.
  • Ligne de code 20: DBMS_SQL.FETCH_ROWS récupérera un enregistrement à partir 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 retournera 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 de l'exécution du SQL dynamique dans les deux sens. Nous avons également vu les exemples dans lesquels le même scénario est géré à la fois de manière NDS et DBMS_SQL pour effectuer une exécution au moment de l'exécution.