Souvent, lors de la résolution de problèmes d'intégration de système, il est nécessaire de présenter une certaine quantité de données dans un format ou un autre. Dans le même temps, n'importe qui peut être un consommateur de données, mais la source est presque toujours une base de données d'entreprise. Par exemple, un fabricant peut exiger du fournisseur qu'il fasse périodiquement rapport sur le mouvement de ses marchandises au format XLSX ou XML, etc.
Il existe de nombreux outils pour convertir les données dans différents formats, et la possibilité de leur utilisation dépend de la pile technologique adoptée dans l'entreprise et de l'architecture logicielle. En même temps, vous voulez toujours que la chaîne composée de diverses bibliothèques, frameworks, couches système utilisées pour convertir les données source soit aussi courte que possible. Cela réduirait le temps consacré au développement de la solution et augmenterait son efficacité productive.
Si nous supposons qu'en fait, la requête SQL se trouve à la racine du processus de sélection des données, alors idéalement, la chaîne de transformations serait souhaitable pour voir ceci:
où
- les données source,
- Requête SQL pour récupérer des données,
- une fonction qui convertit la sélection au format souhaité,
- des données dans le format requis.
Pour Oracle PL / SQL, il existe un certain nombre de packages intégrés et tiers qui implémentent cette fonctionnalité. Ce sont DBMS_XMLGEN, DBMS_XMLQUERY, AS_XLSX, PL / JSON et autres.
Cependant, lorsque la question s'est posée de convertir les données au format CSV, pour une raison quelconque, il n'y avait pas de solutions toutes faites. Je devais le faire moi-même, alors on verra comment.
Énoncé du problème
Créez un outil (package PL / SQL) qui reçoit une requête SELECT arbitraire sous forme de chaîne ou de variable de curseur à l'entrée et renvoie un objet de type CLOB encapsulant des données au format CSV à la sortie. En cas d'erreur, NULL doit être retourné. Le format CSV lui-même n'a pas besoin d'être représenté - ce sont des chaînes dont les éléments sont séparés par un caractère, le plus souvent ";", mais dans le cas général, un caractère arbitraire peut agir comme séparateur. Supposons que les caractères 0x0D + 0x0A sont utilisés pour séparer les chaînes. La première ligne du fichier CSV est généralement l'en-tête et définit les noms de colonne.
Définir l'interface du package
CREATE OR REPLACE PACKAGE pp_csv AS PROCEDURE query2sheet( stmt IN VARCHAR2, sheet IN OUT CLOB, delimeter IN VARCHAR2 DEFAULT ';' ); PROCEDURE query2sheet( ref_cursor IN OUT SYS_REFCURSOR, sheet IN OUT CLOB, delimeter IN VARCHAR2 DEFAULT ';' ); END;
Il existe deux procédures surchargées, la différence entre elles est que l'une d'entre elles reçoit la demande sous forme de chaîne et l'autre sous forme de lien vers le curseur. Le deuxième paramètre est la sortie, c'est le résultat souhaité dans l'objet CLOB. Enfin, le troisième paramètre est le séparateur CSV.
Dans la mise en œuvre de ces procédures, le package DBMS_SQL intégré nous aidera, ce qui vous permet de travailler avec des curseurs dynamiques quand on ne sait pas à l'avance (au stade de la compilation) combien de colonnes sont impliquées dans la sélection.
Les fonctionnalités DBMS_SQL vous permettent d'analyser et d'exécuter dynamiquement des requêtes arbitraires. Pour une procédure qui accepte une demande en tant que chaîne, cela se produit comme suit:
AS cur PLS_INTEGER := DBMS_SQL.OPEN_CURSOR;
Pour une procédure qui prend une variable de curseur, tout est plus simple - à partir de la 11ème version d'Oracle, la conversion "variable de curseur → numéro de curseur SQL" est devenue disponible.
La fonction DBMS_SQL.TO_CURSOR_NUMBER convertit la variable REFCURSOR (fortement ou faiblement typée) en numéro de curseur SQL, qui peut ensuite être transmis aux routines DBMS_SQL. Dans ce cas, la variable de curseur doit être ouverte avant d'être transmise à la fonction DBMS_SQL.TO_CURSOR_NUMBER.
cur PLS_INTEGER := DBMS_SQL.TO_CURSOR_NUMBER(ref_cursor);
Ainsi, les deux versions de l'appel se résumaient à l'obtention du
numéro du curseur dynamique et de l'ensemble de données associé. L'étape suivante consiste à obtenir des informations sur les colonnes de cet ensemble et à extraire les données elles-mêmes.
Le package DMBS_SQL vous permet de décrire les colonnes d'un curseur dynamique, en renvoyant des informations sur chaque colonne dans un tableau associatif d'enregistrements.
Pour ce faire, vous devez déclarer la collection PL / SQL en fonction du type de collection DBMS_SQL.DESC_TAB (ou DESC_TAB2 si la requête peut renvoyer des noms de colonne de plus de 30 caractères). Après cela, vous pouvez utiliser des méthodes de collecte pour parcourir la table et récupérer des informations sur le curseur.
AS cols DBMS_SQL.DESC_TAB2; ncols NUMBER;
Ensuite, le package DBMS_SQL doit être informé du type de chaque colonne sélectionnée à l'aide de la requête dynamique. Cela se fait en appelant DEFINE_COLUMN.
FOR i IN 1 .. ncols LOOP DBMS_SQL.DEFINE_COLUMN(cur, i, col_val_chr, 32767); END LOOP;
Dans le deuxième argument DEFINE_COLUMN, un nombre est passé - la position séquentielle de la colonne dans la liste. Le troisième argument définit le type de données de la colonne du curseur. Il transmet une expression du type approprié. En d'autres termes, DBMS_SQL.DEFINE_COLUMN ne reçoit pas une chaîne avec un nom de type (par exemple, "VARCHAR2"), mais une variable définie avec le type VARCHAR2.
Lors de la définition d'une colonne de type caractère dans le quatrième argument, vous devez spécifier la longueur maximale des valeurs pouvant être chargées dans le curseur.
Les opérations préparatoires sont terminées, vous pouvez maintenant créer une ligne d'en-tête et commencer à extraire les données.
AS cap CLOB;
Les données sont récupérées ligne par ligne à l'aide de DBMS_SQL.FETCH_ROWS et des appels ultérieurs à DBMS_SQL.COLUMN_VALUE pour obtenir les valeurs des colonnes individuelles.
AS bod CLOB;
Ensuite, il ne reste plus qu'à collecter le CSV résultant
DBMS_LOB.APPEND(sheet, cap); DBMS_LOB.APPEND(sheet, c_line_break); DBMS_LOB.APPEND(sheet, bod);
Gérer les erreurs
EXCEPTION WHEN OTHERS THEN sheet := NULL;
Et fermez le curseur
DBMS_SQL.CLOSE_CURSOR(cur);
Options d'utilisation du package
DECLARE csv CLOB; BEGIN DBMS_LOB.CREATETEMPORARY(csv, TRUE, DBMS_LOB.SESSION); pp_csv.query2sheet('SELECT empcode, fio FROM employee WHERE ROWNUM < 10', csv); DBMS_OUTPUT.PUT_LINE(csv); DBMS_LOB.FREETEMPORARY(csv); END;
DECLARE csv CLOB; cur SYS_REFCURSOR; BEGIN OPEN cur FOR SELECT empcode, fio FROM employee WHERE ROWNUM < 10; DBMS_LOB.CREATETEMPORARY(csv, TRUE, DBMS_LOB.SESSION); pp_csv.query2sheet(cur, csv); DBMS_OUTPUT.PUT_LINE(csv); DBMS_LOB.FREETEMPORARY(csv); END;
En fait, c'est tout, le
code source est joint .
Le livre a aidé au développement
Feuerstein S., Arrived B. - Oracle PL / SQL. Pour les professionnels.