Archives de catégorie : PL/SQL

Oracle/scripting, PL/SQL

[Oracle][PL/SQL] Requête pour balayer les données

Rechercher dans une base oracle un nombre (Search_Valeur), il est possible de filtrer les colonnes en renseignant Search_Colonne.


/* VERSION VALEUR NUMERIQUE */
SET SERVER OUTPUT ON
SET SERVEROUTPUT ON
DECLARE
TYPE curtype
IS
  REF
  CURSOR;
    Curseur_Table curtype;
    Curseur_Colonne curtype;
    Curseur_Search curtype;
    Counter              NUMBER:=0;
    RequeteSQL_Colonne   VARCHAR2(200);
    RequeteSQL_Table     VARCHAR2(500);
    RequeteSQL_Searching VARCHAR2(2048);
    Search_Table         VARCHAR (100):= '';
    Search_Colonne       VARCHAR (100):= '';

    Nom_Table            VARCHAR (200);
    Nom_Colonne          VARCHAR2(200);
    Search_Valeur        VARCHAR (200):='126980';
  BEGIN
    RequeteSQL_Table:='SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE ''%'||Search_Table||'%''';
    -- dbms_output.put_line(RequeteSQL_Table);
    OPEN Curseur_Table FOR RequeteSQL_Table;
    LOOP
      FETCH Curseur_Table INTO Nom_Table;
    EXIT
  WHEN Curseur_Table%notfound;
    RequeteSQL_Colonne:='SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME='''||Nom_Table||''' AND DATA_TYPE IN (''NUMBER'') AND COLUMN_NAME LIKE ''%'|| Search_Colonne ||'%''';
    -- dbms_output.put_line(RequeteSQL_Colonne);
    OPEN Curseur_Colonne FOR RequeteSQL_Colonne;
    LOOP
      FETCH Curseur_Colonne INTO Nom_Colonne;
      EXIT
    WHEN Curseur_Colonne%notfound;
      RequeteSQL_Searching :='SELECT count(*) FROM "'||Nom_Table||'" WHERE "'||Nom_Colonne||'"=' || Search_Valeur;
      -- dbms_output.put_line(RequeteSQL_Searching);
      EXECUTE IMMEDIATE RequeteSQL_Searching INTO Counter;
      IF Counter > 0 THEN
        dbms_output.put_line(''|| Search_Valeur ||' trouvé '||Counter || ' fois dans "'|| Nom_Table ||'"."'|| Nom_Colonne ||'" SELECT * FROM ' || Nom_Table || ' WHERE ' || Nom_Colonne || ' = ' || Search_Valeur || ';' );
      END IF;
    END LOOP;
    CLOSE Curseur_Colonne;
  END LOOP;
  CLOSE Curseur_Table;
END;

Rechercher dans une base oracle une chaine (Search_String), il est possible de filtrer les colonnes en renseignant Search_Colonne.


/* VERSION CHAINE DE CARACTERES */
SET SERVEROUTPUT ON
SET SERVER OUTPUT ON
DECLARE
TYPE curtype
IS
  REF
  CURSOR;
    Curseur_Table curtype;
    Curseur_Colonne curtype;
    Curseur_Search curtype;
    Counter              NUMBER:=0;
    RequeteSQL_Colonne   VARCHAR2(200);
    RequeteSQL_Table     VARCHAR2(500);
    RequeteSQL_Searching VARCHAR2(2048);
    Search_String        VARCHAR (100) := '' ;
    Search_Table         VARCHAR (100) := '' ;
    Nom_Table            VARCHAR (200) ;
    Nom_Colonne          VARCHAR2(200);
  BEGIN
    RequeteSQL_Table:='SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME LIKE ''%'||Search_Table||'%''';
    --    dbms_output.put_line(RequeteSQL_Table);
    OPEN Curseur_Table FOR RequeteSQL_Table;
    LOOP
      FETCH Curseur_Table INTO Nom_Table;
    EXIT
  WHEN Curseur_Table%notfound;
    RequeteSQL_Colonne:='SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME='''||Nom_Table||''' AND DATA_TYPE IN (''VARCHAR2'',''NVARCHAR2'')';
    -- dbms_output.put_line(RequeteSQL_Colonne);
    OPEN Curseur_Colonne FOR RequeteSQL_Colonne;
    LOOP
      FETCH Curseur_Colonne INTO Nom_Colonne;
      EXIT
    WHEN Curseur_Colonne%notfound;
      RequeteSQL_Searching :='SELECT count(*) FROM "'||Nom_Table||'" WHERE "'||Nom_Colonne||'" LIKE ''%'||Search_String||'%''';
      -- dbms_output.put_line(RequeteSQL_Searching);
      EXECUTE IMMEDIATE RequeteSQL_Searching INTO Counter;
      IF Counter > 0 THEN
        dbms_output.put_line(''''||Search_String ||''' trouvé '||Counter || ' fois dans "'|| Nom_Table ||'"."'|| Nom_Colonne ||'"');
      END IF;
    END LOOP;
    CLOSE Curseur_Colonne;
  END LOOP;
  CLOSE Curseur_Table;
END;
MySQL

SPLIT

Retourne une partie de 1 à n; Si n> nombre de partie, renvoie null;
Autre source: http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/
La fonction que je propose est moins performante 🙁 que le lien ci-dessus.
[code lang= »sql »]
DROP FUNCTION IF EXISTS SPLIT;
DELIMITER $$;
CREATE FUNCTION `SPLIT`( _text TEXT,_lim VARCHAR(255), _part INTEGER ) RETURNS text CHARSET utf8 COLLATE utf8_unicode_ci
BEGIN
DECLARE _substring TEXT;
DECLARE _index INTEGER ;
DECLARE _len_index INTEGER;
DECLARE _len_substring INTEGER;
DECLARE _len_lim INTEGER ;
set _part=_part-1;
set _len_lim=char_length(_lim)+1;
SET _len_index = LENGTH(_text) – LENGTH(REPLACE(_text, _lim,  »));
SET _index=0;

_indexwhile: WHILE _index <= _len_index DO
set _substring= SUBSTRING_INDEX(_text,_lim,1);
if( _part=_index) then
RETURN _substring;
END IF;
set _len_substring= char_length(_substring);
set _text=SUBSTR(_text,_len_substring+_len_lim,char_length(_text)-_len_substring);
SET _index = _index + 1;
END WHILE _indexwhile;
RETURN NULL;
END
[/code]

Pour la tester avec une procédure stocké:

[code lang= »sql »]DROP PROCEDURE IF EXISTS READ_SPLIT;
DELIMITER $$;
CREATE PROCEDURE `READ_SPLIT`( _text TEXT, _lim VARCHAR(255))
BEGIN
DECLARE _index INTEGER ;
DECLARE _len_index INTEGER;
SET _len_index = 1 + LENGTH(_text) – LENGTH(REPLACE(_text, _lim,  »));
SET _index=1;
CREATE TABLE IF NOT EXISTS RESULT(_text_result TEXT);
_indexwhile: WHILE _index <= _len_index DO
INSERT INTO RESULT ( _text_result) SELECT SPLIT(_text,_lim,_index);
SET _index = _index + 1;
END WHILE _indexwhile;
SELECT * FROM RESULT;
DROP TABLE RESULT;
END
[/code]
[code lang= »sql »]CALL READ_SPLIT("Hello World, this is a split function," ");
[/code]
Pour pousser l’histoire:
[code lang= »sql »]DROP PROCEDURE IF EXISTS LOOP_READ_SPLIT;
DELIMITER $$;
CREATE PROCEDURE `LOOP_READ_SPLIT`( _text TEXT, _lim VARCHAR(255),_len_index INTEGER)
BEGIN
DECLARE _index INTEGER ;
SET _index=1;
_indexwhile: WHILE _index <= _len_index DO
CALL READ_SPLIT(_text,_lim);
SET _index = _index + 1;
END WHILE _indexwhile;
END
[/code]
[code lang= »sql »]CALL LOOP_READ_SPLIT("Hello World, this is a split function," ",100);
[/code]