Utilisation de la fonction CURSOR

Version imprimable

Bonjour,

J'ai besoin de lister l'ensemble des colonnes de l'ensemble des tables dont j'ai accès dans une BDD sous oracle.

j'ai essayé ceci :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
DECLARE
CURSOR cur IS
	SELECT table_name FROM all_tables
        where table_name like ('TF_%') 
        OR table_name like ('TD_%') 
        or table_name like ('TN_%') 
        or table_name like ('TU_%')
        or table_name like ('VN_%');
	BEGIN
	FOR rec IN cur loop
Select COLUMN_NAME from ??? ;
	END LOOP;
END;

le soucis est que la sortie de

Code:

1
2
3
4
5
6
7
 
SELECT table_name FROM all_tables
        where table_name like ('TF_%') 
        OR table_name like ('TD_%') 
        or table_name like ('TN_%') 
        or table_name like ('TU_%')
        or table_name like ('VN_%');

me donne le nom des tables sans le nom du modèle (ex : TF_TBLACP_J pour la table DWH.TBLACP_J)

Donc je ne sais comment faire dans cette partie de mon code

Code:

1
2
3
4
5
6
 
BEGIN
	FOR rec IN cur loop
Select COLUMN_NAME from ??? ;
	END LOOP;
END;

quelqu'un aurait-il une idée ?

Merci d'avance.

14/05/2012, 17h33
Bibeleuh
Pour avoir le nom complet de la table il suffit de préfixer TABLE_NAME par OWNER non ?
Code:

1 2 select owner||'.'||table_name from all_tables
Et pour lister les colonnes des tables, tu disposes de la table : ALL_TAB_COLUMNS

Et au passage :

Ton SQL pour sélectionner tes tables

Code:

1
2
3
4
5
6
7
 
SELECT table_name FROM all_tables
        WHERE table_name LIKE ('TF_%') 
        OR table_name LIKE ('TD_%') 
        OR table_name LIKE ('TN_%') 
        OR table_name LIKE ('TU_%')
        OR table_name LIKE ('VN_%');

Pourrait être simplifié en :

Code:

1
2
3
 
SELECT table_name FROM all_tables
        WHERE SUBSTR(table_name,0,3) IN ('TF_','TD_','TN_','TU_','VN_')

;)

15/05/2012, 10h03
McM

Pas vraiment, le _ est un symbole spécial pour le like qui correspond à "N'importe quel caractère"
Pour être juste faudrait remplacer par

Code:

WHERE SUBSTR(table_name,0,2) IN ('TF','TD','TN','TU','VN') AND length(table_name) > 2

Sinon pour répondre à Ueshiba : Pour passer des noms de table en variable, il faut utiliser du dynamique. Dans ton cas, il faudrait utiliser

Code:

open cur for 'SELECT .. FROM '|| r.table_name

, mais ce n'est pas ce que tu veux.

Plus simple pour toi : utiliser la vue all_tab_columns, que tu peux selecter directement.

Citation:

Envoyé par McM

Pas vraiment, le _ est un symbole spécial pour le like qui correspond à "N'importe quel caractère"

C'est un symbole spécial pour le LIKE mais pas pour le IN j'ai l'impression :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 SELECT * FROM ( SELECT 'TA_TEST' AS VAL FROM DUAL UNION SELECT 'FA_TEST' AS VAL FROM DUAL UNION SELECT 'TAG_TEST' AS VAL FROM DUAL UNION SELECT 'T_TEST' AS VAL FROM DUAL ) WHERE SUBSTR(VAL,0,3) IN ('TA_','FA_')
Si le '_' était un caractère spécial , j'aurais en retour la valeur TAG_TEST mais j'ai uniquement :
Code:

1 2 3 VAL FA_TEST TA_TEST
Edit : Autant pour moi , j'ai pas compris de quoi tu parlais mais oui lui il veut bien les noms de tables qui commencent par 2 caractères précis et ont une longueur d'au moins 3 , donc ma requête est fausse

Bonjour et merci pour vos réponses.

Bon, en effet, j'ai fait bien trop compliqué :mrgreen:

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
select a.owner||'.'||a.table_name||'.'||a.COLUMN_NAME as CHAMPS,
       b.COMMENTS as DESCRIPTIF,
       a.DATA_TYPE as FORMAT,
       a.DATA_LENGTH as LONGUEUR
 
       FROM ALL_TAB_COLUMNS a,
            ALL_COL_COMMENTS b
 
        WHERE a.table_name LIKE ('TF_%') 
        OR a.table_name LIKE ('TD_%') 
        OR a.table_name LIKE ('TN_%') 
        OR a.table_name LIKE ('TU_%')
        OR a.table_name LIKE ('VN_%')
        and a.COLUMN_NAME=b.COLUMN_NAME
 
ORDER BY CHAMPS ASC

est beaucoup plus simple....

A ma décharge, je suis simple utilisateur mais n'empêche je faisais trop compliqué.

Sujet clôturé.

Ca ne va pas.. Tu fais une jointure entre columns et comment.. donc une colonne sans commentaire ne sera pas sélectionnée.
Ensuite au niveau des (), inutiles pour le like et préférable niveau lisibilité pour les OR et AND car le AND est prioritaire sur le OR, donc ta requête est fausse sur ce point
Code:

1 2 3 4 SELECT 1 FROM dual where 1 = 1 or 1 = 2 and 1 = 2 renvoie 1 Equivalent à SELECT 1 FROM dual where 1 = 1 or (1 = 2 and 1 = 2)
donc voici un code correct
Code:

1 2 3 4 5 6 7 8 9 10 SELECT a.owner||'.'||a.table_name||'.'||a.COLUMN_NAME AS CHAMPS, b.COMMENTS AS DESCRIPTIF, a.DATA_TYPE AS FORMAT, a.DATA_LENGTH AS LONGUEUR FROM ALL_TAB_COLUMNS a, ALL_COL_COMMENTS b WHERE ( a.table_name LIKE 'TF_%' OR a.table_name LIKE 'TD_%' OR a.table_name LIKE 'TN_%' OR a.table_name LIKE 'TU_%' OR a.table_name LIKE 'VN_%') AND a.COLUMN_NAME=b.COLUMN_NAME (+) ORDER BY CHAMPS

Oui sur le fond je faisait une jointure stricte car j'avais eu confirmation que toutes les colonnes possédaient un commentaire.
Sauf que ceci est le constat actuel, et par forcément celui de demain...donc remarque plus que pertinente

Sur la priorité entre OR et AND, tu as raison également. Même si dans mon cas de figure, je n'avais pas d'impact.

Donc je prends acte en modifiant :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 select distinct(a.owner||'.'||a.table_name||'.'||a.COLUMN_NAME) as CHAMPS, b.COMMENTS as DESCRIPTIF, a.DATA_TYPE as FORMAT, a.DATA_LENGTH as LONGUEUR FROM (select * FROM ALL_TAB_COLUMNS WHERE table_name LIKE 'TF_%' OR table_name LIKE 'TD_%' OR table_name LIKE 'TN_%' OR table_name LIKE 'TU_%' OR table_name LIKE 'VN_%' )a, ALL_COL_COMMENTS b WHERE a.OWNER=b.OWNER(+) and a.TABLE_NAME=b.TABLE_NAME(+) and a.COLUMN_NAME=b.COLUMN_NAME(+) ORDER BY CHAMPS ASC
qui en plus tourne bien plus rapidement sur mon modèle.

Merci pour vos commentaires éclairés ;)