Aprenda PL/SQL

Essa procedure pode ser muito útil em algumas situações: Ela pega as linhas de uma coluna e retorna cada linha separada por ví­rgula na mesma string.

CREATE OR REPLACE FUNCTION rowtocol (
   p_slct IN VARCHAR2,
   p_dlmtr IN VARCHAR2 DEFAULT ',' ) RETURN VARCHAR2 AUTHID CURRENT_USER 
AS
   TYPE c_refcur IS REF CURSOR;
   lc_str VARCHAR2(4000);
   lc_colval VARCHAR2(4000);
   c_dummy c_refcur;
   l number;
BEGIN
   OPEN c_dummy FOR p_slct;
   LOOP
     FETCH c_dummy INTO lc_colval;
     EXIT WHEN c_dummy%NOTFOUND;
     lc_str := lc_str || p_dlmtr || lc_colval;
   END LOOP;
   CLOSE c_dummy;
   RETURN SUBSTR(lc_str,2);
END;

Agora vamos ao exemplo:

SQL> CREATE OR REPLACE FUNCTION rowtocol (
  2     p_slct IN VARCHAR2,
  3     p_dlmtr IN VARCHAR2 DEFAULT ',' )
  4  RETURN VARCHAR2
  5     AUTHID CURRENT_USER AS
  6     TYPE c_refcur IS REF CURSOR;
  7     lc_str VARCHAR2(4000);
  8     lc_colval VARCHAR2(4000);
  9     c_dummy c_refcur;
 10     l number;
 11  BEGIN
 12     OPEN c_dummy FOR p_slct;
 13     LOOP
 14       FETCH c_dummy INTO lc_colval;
 15       EXIT WHEN c_dummy%NOTFOUND;
 16       lc_str := lc_str || p_dlmtr || lc_colval;
 17     END LOOP;
 18     CLOSE c_dummy;
 19     RETURN SUBSTR(lc_str,2);
 20  END;
 21  /

Function created.

SQL> SELECT ROWTOCOL('SELECT ENAME FROM EMP') FROM DUAL;

ROWTOCOL('SELECTENAMEFROMEMP')
------------------------------------------------------------------
SMITE,ALLEN,WARD,JONES,MARTIN,BLAKE,SCOTT,TURNER,ADAMS,JAMES,FORD

SQL>

Comente aqui

Comments are closed.