Tabela Virtual

[cleberzumba]

Bom dia pesoal,

dr_gori, poderia me mandar um exemplo de como criar aquela tabela virtual?...

[dr_gori]

heeeinnn?
Tabela Virtual ? Ta falando de tabela temporária ? (Global Temporary Table?)

Selecionar tudo

create global temporary table sua_tabela (
  x_ varchar2(100)
) on commit delete rows;

Pode ser ON COMMIT PRESERVE ROWS também.

[cleberzumba]

não dr_gori...aquela tabela TYPE IS RECORD, onde eu só utilizo durante a execução da procedure.

[cleberzumba]

Essa tabela serviria para esse select a baixo, mas será que não existe um jeito de rodar esse select sem precisar fazer essa tabela?..pois desta forma eu sei que não roda porque o segundo select irá retornar mais de um registro para comparação da data.

Selecionar tudo

select mov.cd_setor_dest, max(mov.dt_movimento) data_mov
 from db_scdweb.tbl_protocolo prt, 
        db_scdweb.tbl_movimento mov
where prt.nu_protocolo = mov.nu_protocolo
having  max(mov.dt_movimento) < (select a.data_mov    
             	                                       from (select max(mov.dt_movimento) data_mov, prt.nu_protocolo 
                                                                 from db_scdweb.tbl_protocolo prt, db_scdweb.tbl_movimento mov 
                                                           group by prt.nu_protocolo
                                        	           order by prt.nu_protocolo) a;

[Trevisolli]

Cleber, beleza?

Se trata de PL/SQL Record e PL/SQL Table.

Vou montar um exemplo e te mando ok?

[cleberzumba]

PL/SQL Table...ok manda aí por favor. O bicho está pegando hoje aqui...to com milhares de select para fazer, valeu pelo apoio.

[Trevisolli]

Vê se dá pra ter uma idéia brother:

Selecionar tudo

TABELA:
Describing TB_TESTE....
NAME                            Null?     Type
------------------------------- --------- -----
NRO_NF                                    VARCHAR2(10)
ID                              NOT NULL  NUMBER(10,0)

Bloco contendo types:

Selecionar tudo

DECLARE 
  
   TYPE rec_teste IS RECORD (nro_nf tb_teste.nro_nf%TYPE,
	                          id     tb_teste.id%TYPE);
	                          
	TYPE tab_teste IS TABLE OF rec_teste INDEX BY BINARY_INTEGER;                          

   v_tabela tab_teste;

   CURSOR cur_dados 
     IS 
       SELECT nro_nf, id FROM tb_teste;
	   
BEGIN 

   -- Populo types...
   FOR x IN cur_dados 
   LOOP     
      v_tabela(cur_dados%ROWCOUNT).nro_nf := x.nro_nf;
      v_tabela(cur_dados%ROWCOUNT).id     := x.id;
	END LOOP;					  
	
	-- Utilizo o type
	
	FOR x IN 1..v_tabela.COUNT 
	LOOP
	   dbms_output.put_line('Nro. da Nf. ' || v_tabela(x).nro_nf );
  	   dbms_output.put_line('Nro. ID.    ' || v_tabela(x).id );
	END LOOP;
   
END;

[cleberzumba]

Valeu brother, esse bloco ficou bom mas não serve para aquele select que eu mandei não.

[cleberzumba]

O que eu quero é criar uma tabela virtual e inserir nela e depois mostrar o resultado...tudo isso em tempo de execução.

[cleberzumba]

Depois que eu inserir numa tabela virtual como eu mostro o resultado na tela em tempo de execução?..

[Trevisolli]

Brother,

Na parte "Utilizo o Type" na verdade, estou listando minha tabela pl/sql.
Não seria isso?

[cleberzumba]

Seguinte, meu bloco é esse..criei a tabela, inseri nela e agora como eu mostro o resultado?

Selecionar tudo

DECLARE 

  
   TYPE rec_protocolo IS RECORD 
      (nu_protocolo tbl_protocolo.nu_protocolo%TYPE, 
       dt_movimento tbl_movimento.dt_movimento%TYPE); 
                             
   TYPE tab_protocolo IS TABLE OF rec_protocolo INDEX BY BINARY_INTEGER;                          

   v_tabela tab_protocolo; 

   CURSOR c_dados  IS 
   
 		select max(mov.dt_movimento) data_mov
		from db_scdweb.tbl_protocolo prt, 
		     db_scdweb.tbl_movimento mov
		where prt.nu_protocolo = mov.nu_protocolo
		and prt.cd_protocolo = mov.cd_protocolo;

       
BEGIN 

   -- Populo types... 
   FOR c_dados_rec IN c_dados LOOP   
   
       insert into tab_protocolo
       select a.data_mov, a.protocolo
         from (select max(mov.dt_movimento) data_mov, prt.nu_protocolo protocolo
                from db_scdweb.tbl_protocolo prt, 
                     db_scdweb.tbl_movimento mov
               where prt.nu_protocolo = mov.nu_protocolo
                 and prt.cd_protocolo = mov.cd_protocolo 
                 and mov.dt_movimento > to_date('01/01/2007 00:00:00','dd/mm/yyyy hh24:mi:ss')
                 and mov.cd_setor_dest in (670,671,672,1611,1723,1882,1883,2672,2990) 
            group by prt.nu_protocolo
            order by prt.nu_protocolo) a
       where a.data_mov > c_dados_rec.data_mov;
        
   END LOOP;                 
    
   -- Utilizo o type     
   FOR x IN 1..v_tabela.COUNT LOOP 


   END LOOP; 
    
END;

[Trevisolli]

Então brother, onde você colocou:

Selecionar tudo

BEGIN 

-- Populo types... 
FOR c_dados_rec IN c_dados LOOP 

insert into tab_protocolo 
select a.data_mov, a.protocolo 
from (select max(mov.dt_movimento) data_mov, prt.nu_protocolo protocolo 
from db_scdweb.tbl_protocolo prt, 
db_scdweb.tbl_movimento mov 
where prt.nu_protocolo = mov.nu_protocolo 
and prt.cd_protocolo = mov.cd_protocolo 
and mov.dt_movimento > to_date('01/01/2007 00:00:00','dd/mm/yyyy hh24:mi:ss') 
and mov.cd_setor_dest in (670,671,672,1611,1723,1882,1883,2672,2990) 
group by prt.nu_protocolo 
order by prt.nu_protocolo) a 
where a.data_mov > c_dados_rec.data_mov; 

END LOOP; 

No caso, você não daria um insert na pl/sql table criada, mas, sim, atribui valores à ela, como no exemplo:

Selecionar tudo

   -- Populo types... 
   FOR x IN cur_dados 
   LOOP      
      v_tabela(cur_dados%ROWCOUNT).nro_nf := x.nro_nf; 
      v_tabela(cur_dados%ROWCOUNT).id     := x.id; 
   END LOOP;                 
 

E, sobre como utilizá-lo, no final, o faço com DBMS_OUTPUT.PUT_LINE.
Mas, você poderia inserir em uma tabela física do banco, exibir a mensagem, etc.

O que deseja fazer?

[cleberzumba]

Inserir em uma tabela física no banco está fora de cojitação..tenho que fazer aquele select verificando quais registros tem a data maior que a data retornada do cursor e mostrar esses registros com as datas maiores.

[Trevisolli]

Brother,

Isso você consegue com o primeiro exemplo que te passei.

[cleberzumba]

Cara não consegui não..me ajuda aí, o meu codigo é esse aí a baixo. Olha como eu estou fazendo...mas não deu certo, está dando erro no DBMS_OUTPUT.

Selecionar tudo

DECLARE 

  
   TYPE rec_protocolo IS RECORD 
      (nu_protocolo tbl_protocolo.nu_protocolo%TYPE, 
       dt_movimento tbl_movimento.dt_movimento%TYPE); 
                             
   TYPE tab_protocolo IS TABLE OF rec_protocolo INDEX BY BINARY_INTEGER;                          

   v_tabela rec_protocolo; 

   CURSOR c_dados  IS 
   
 		select mov.dt_movimento data_mov
		from db_scdweb.tbl_protocolo prt, 
		     db_scdweb.tbl_movimento mov
		where prt.cd_protocolo = mov.cd_protocolo;

       
BEGIN 

   -- Populo types... 
   FOR c_dados_rec IN c_dados LOOP   
   
       insert into tab_protocolo (nu_protocolo, dt_movimento)
       select a.protocolo, a.data_mov
         from (select max(mov.dt_movimento) data_mov, prt.nu_protocolo protocolo
                from db_scdweb.tbl_protocolo prt, 
                     db_scdweb.tbl_movimento mov
               where prt.cd_protocolo = mov.cd_protocolo 
                 and mov.dt_movimento > to_date('01/01/2007 00:00:00','dd/mm/yyyy hh24:mi:ss')
                 and mov.cd_setor_dest in (670,671,672,1611,1723,1882,1883,2672,2990) 
            group by prt.nu_protocolo
            order by prt.nu_protocolo) a
       where a.data_mov > c_dados_rec.data_mov;
       

   END LOOP;                 

   -- Utilizo o type     
   FOR i IN 1..v_tabela.COUNT LOOP 
        dbms_output.put_line('Nro. Protocolo. ' || v_tabela(i).nu_protocolo ); 
        dbms_output.put_line('Nro. Data Mov.  ' || v_tabela(i).dt_movimento ); 
   END LOOP;
    
END;

[Trevisolli]

Vamos lá brow...

Você não pode dar insert na tab_protocolo (pois ela é a table pl/sql criada).

Faz o seguinte, tem como me mandar alguns registros das duas tabelas?
Daí faço o teste aqui com o teu caso, e, te mando ainda hoje, fica tranquilo.

ptrevisolli@ancora.inf.br

[cleberzumba]

Registros da tabela TBL_MOVIMENTO:

Selecionar tudo

dt_movimento            cd_setor_origem    cd_protocolo
4/1/2002 15:41:19	            658	1309
4/1/2002 15:42:41	            658	1322
4/1/2002 15:44:15	            430	1323
8/1/2002 16:59:53	            482	3394
8/1/2002 17:02:32	            710	1733
8/1/2002 17:02:48	            724	2927
8/1/2002 17:03:32	            437	1734
8/1/2002 17:06:10	            662	888
8/1/2002 17:08:41	            437	1739
8/1/2002 17:08:20	            684	1737
8/1/2002 17:06:55	            665	3526
8/1/2002 17:08:39	            432	1735
8/1/2002 17:10:03	            437	1741
8/1/2002 17:10:08	            437	1740
8/1/2002 17:11:39	            430	1742
8/1/2002 17:11:47	            430	1736
8/1/2002 17:08:54	            662	739
8/1/2002 17:12:55	            662	716

Registros da Tabela TBL_PROTOCOLO:

Selecionar tudo

NU_PROTOCOLO       cd_protocolo
25120000065200290	1282
25100000783200202	1284
25120000066200234	1285
25120000067200289	1287
25220000019200262	1288
25100000785200293	1290
25100000786200238	1291
25120000069200278	1293
25245000145200239	1294
25106000038200203	1295

[Trevisolli]

Brother, estou analisando o type pra você, beleza?

[Trevisolli]

Brother,

Fiz um teste aqui, com algumas informações do select que você me enviou.
Estou te enviando o teste mas, altere os selects para a tua necessidade, ok?
Caso não dê certo, poste novamente.

Selecionar tudo

DECLARE  

   TYPE rec_protocolo IS RECORD 
      (nu_protocolo tbl_protocolo.nu_protocolo%TYPE, 
       dt_movimento tbl_movimento.dt_movimento%TYPE); 
                              
   TYPE tab_protocolo IS TABLE OF rec_protocolo INDEX BY BINARY_INTEGER;                          

   v_tabela tab_protocolo;  
   
 CURSOR C_DADOS  
  IS 
   SELECT MOV.DT_MOVIMENTO DATA_MOV 
     FROM TBL_PROTOCOLO PRT, 
          TBL_MOVIMENTO MOV 
    WHERE PRT.CD_PROTOCOLO = MOV.CD_PROTOCOLO; 
   
 CURSOR C_DADOS_T  (P_DATA DATE)
  IS 
	 SELECT A.PROTOCOLO, A.DATA_MOV 
		         FROM (SELECT MAX(MOV.DT_MOVIMENTO) DATA_MOV, PRT.NU_PROTOCOLO PROTOCOLO 
		                FROM TBL_PROTOCOLO PRT, 
		                     TBL_MOVIMENTO MOV 
		               WHERE PRT.CD_PROTOCOLO = MOV.CD_PROTOCOLO 
		                 AND MOV.DT_MOVIMENTO > TO_DATE('01/01/2007 00:00:00','DD/MM/YYYY HH24:MI:SS') 
		                -- AND MOV.CD_SETOR_DEST IN (670,671,672,1611,1723,1882,1883,2672,2990) 
		            GROUP BY PRT.NU_PROTOCOLO 
		            ORDER BY PRT.NU_PROTOCOLO) A 
		       WHERE A.DATA_MOV < P_DATA;
BEGIN

  FOR x IN c_dados 
  LOOP
     -- Aqui, populo o type (tabela tab_protocolo, pela variável v_tabela)
	  FOR z IN c_dados_t(x.data_mov)
	  LOOP
	    v_tabela(c_dados_t%ROWCOUNT).nu_protocolo := z.protocolo;
	    v_tabela(c_dados_t%ROWCOUNT).dt_movimento := z.data_mov;
	  END LOOP;
  
  END LOOP;
  
  -- com o type populado... posso utilizá-lo, da forma que desejar...
  -- no meu exemplo, vou inserir em uma tabela e dar uma mensagem (dbms_output).
  
  FOR y IN 1..v_tabela.COUNT
  LOOP
		dbms_output.put_line('teste ' || v_tabela(y).nu_protocolo );
   	dbms_output.put_line('teste ' || v_tabela(y).dt_movimento );
   	
   	BEGIN 
   	  INSERT INTO tab_log_proc VALUES ( v_tabela(y).nu_protocolo || '' || v_tabela(y).dt_movimento );
   	  COMMIT;
      EXCEPTION
		  WHEN OTHERS THEN 
		    raise_application_error(-20001, 'Erro ao popular TAB_LOG_PROC: ' || SQLERRM); 
		END;	
   	
  END LOOP;

EXCEPTION 
  WHEN OTHERS THEN 
    raise_application_error (-20001, 'Erro no processo de montagem de types: ' || SQLERRM );  
END;
	       

[cleberzumba]

e aí trevisolli...primeiro obrigado pela atenção.
seguinte, está dando o seguinte erro:

Selecionar tudo

 existe ORA-06550: linha 20, coluna 5: PL/SQL: SQL Statement ignored ORA-06550: linha 35, coluna 25: PLS-00364: o uso da variável 'X' de índice

eu tirei o insert into, pois não vejo necessário para minha utilização.

[cleberzumba]

e aí trevisolli, obrigado pela atenção.
seguinte, está dando o seguinte erro:

Selecionar tudo

existe ORA-06550: linha 20, coluna 5: PL/SQL: SQL Statement ignored ORA-06550: linha 35, coluna 25: PLS-00364: o uso da variável 'X' de índice.

Eu tirei o insert into.

[Trevisolli]

Brother, coloca o bloco alterado por você aqui, pode ser?

[cleberzumba]

Alterei pouca coisa...tirei somente o insert..mas está aí. Acho que estamos perto de concluir...ehehehe

Selecionar tudo

DECLARE  

   TYPE rec_protocolo IS RECORD 
      (nu_protocolo tbl_protocolo.nu_protocolo%TYPE, 
       dt_movimento tbl_movimento.dt_movimento%TYPE); 
                              
   TYPE tab_protocolo IS TABLE OF rec_protocolo INDEX BY BINARY_INTEGER;                          

   v_tabela tab_protocolo;  
    
 CURSOR C_DADOS IS -- Retorna data geral 
 
   SELECT MOV.DT_MOVIMENTO DATA_MOV 
     FROM TBL_PROTOCOLO PRT, 
          TBL_MOVIMENTO MOV 
    WHERE PRT.CD_PROTOCOLO = MOV.CD_PROTOCOLO; 
    
 CURSOR C_DADOS_T (P_DATA DATE) IS -- Retorna data do grupo que eu quero e comparar com a data geral.
 
             SELECT A.PROTOCOLO, A.DATA_MOVI
               FROM (SELECT MAX(MOV.DT_MOVIMENTO) DATA_MOVI, PRT.NU_PROTOCOLO PROTOCOLO 
                      FROM TBL_PROTOCOLO PRT, 
                           TBL_MOVIMENTO MOV 
                     WHERE PRT.CD_PROTOCOLO = MOV.CD_PROTOCOLO 
                       AND MOV.DT_MOVIMENTO > TO_DATE('01/01/2007 00:00:00','DD/MM/YYYY HH24:MI:SS') 
                       AND MOV.CD_SETOR_DEST IN (670,671,672,1611,1723,1882,1883,2672,2990) 
                  GROUP BY PRT.NU_PROTOCOLO 
                  ORDER BY PRT.NU_PROTOCOLO) A 
             WHERE A.DATA_MOV > P_DATA; 
BEGIN 

  FOR x IN c_dados LOOP 

     -- Aqui, populo o type (tabela tab_protocolo, pela variável v_tabela) 
     FOR z IN c_dados_t(x.data_mov) LOOP 
       v_tabela(c_dados_t%ROWCOUNT).nu_protocolo := z.protocolo; 
       v_tabela(c_dados_t%ROWCOUNT).dt_movimento := z.data_movi; 
     END LOOP; 
  
  END LOOP; 
  
  -- com o type populado... posso utilizá-lo, da forma que desejar... 
  -- vou dar uma mensagem (dbms_output). 
  
  FOR y IN 1..v_tabela.COUNT LOOP 
  
      dbms_output.put_line('Nº Protocolo:      ' || v_tabela(y).nu_protocolo ); 
      dbms_output.put_line('Data Movimentação: ' || v_tabela(y).dt_movimento );  
       
  END LOOP; 

EXCEPTION 
  WHEN OTHERS THEN 
    raise_application_error (-20001, 'Erro no processo de montagem de types: ' || SQLERRM );  
END;

[Trevisolli]

Este aqui não deu erro brother.

Estava dando erro, porque você colocou:

Selecionar tudo

       WHERE a.data_mov > p_data;

E o correto é (data_movI):

Selecionar tudo

       WHERE a.data_movi > p_data;

Selecionar tudo

DECLARE
   TYPE rec_protocolo IS RECORD ( nu_protocolo   tbl_protocolo.nu_protocolo%TYPE,
                                  dt_movimento   tbl_movimento.dt_movimento%TYPE );

   TYPE tab_protocolo IS TABLE OF rec_protocolo INDEX BY BINARY_INTEGER;

   v_tabela   tab_protocolo;

   CURSOR c_dados
   IS                                                    -- Retorna data geral
      SELECT mov.dt_movimento data_mov
        FROM tbl_protocolo prt, tbl_movimento mov
       WHERE prt.cd_protocolo = mov.cd_protocolo;

   CURSOR c_dados_t (p_data DATE)
   IS       -- Retorna data do grupo que eu quero e comparar com a data geral.
      SELECT a.protocolo, a.data_movi
        FROM (SELECT   MAX (mov.dt_movimento) data_movi,
                       prt.nu_protocolo protocolo
                  FROM tbl_protocolo prt, tbl_movimento mov
                 WHERE prt.cd_protocolo = mov.cd_protocolo
                   AND mov.dt_movimento > TO_DATE ('01/01/2007 00:00:00','DD/MM/YYYY HH24:MI:SS')
                   AND mov.cd_setor_dest IN (670, 671, 672, 1611, 1723, 1882, 1883, 2672, 2990)
              GROUP BY prt.nu_protocolo
              ORDER BY prt.nu_protocolo) a
       WHERE a.data_movi > p_data;
BEGIN
   FOR x IN c_dados
   LOOP
-- Aqui, populo o type (tabela tab_protocolo, pela variável v_tabela)
      FOR z IN c_dados_t (x.data_mov)
      LOOP
         v_tabela (c_dados_t%ROWCOUNT).nu_protocolo := z.protocolo;
         v_tabela (c_dados_t%ROWCOUNT).dt_movimento := z.data_movi;
      END LOOP;
   END LOOP;

-- com o type populado... posso utilizá-lo, da forma que desejar...
-- vou dar uma mensagem (dbms_output).
   FOR y IN 1 .. v_tabela.COUNT
   LOOP
      DBMS_OUTPUT.put_line ('Nº Protocolo: ' || v_tabela (y).nu_protocolo);
      DBMS_OUTPUT.put_line ('Data Movimentação: ' || v_tabela (y).dt_movimento
                           );
   END LOOP;
EXCEPTION
   WHEN OTHERS THEN
      raise_application_error (-20001, 'Erro no processo de montagem de types: ' || SQLERRM );
END;

[cleberzumba]

Meu amigo..então não sei mais o que fazer. Está dando erro o uso da variável X

[cleberzumba]

Fala trevisolli...você não sabe o que tava pegando, eu não tava colocando o nome do schema na frente das tabelas...ahahahahaha...foi mal, está rodando não terminou ainda. Valeu cara.

[Trevisolli]

Verdade brother.
Eu o eliminei aqui para os testes.
Espero que funcione.
Qualquer dúvida, manda ai.

[tesmerine]

Aproveitando o tópico....
na parte do código onde é mencionado "Utilizo o type" é possível ao invés do dbms_output.put_line eu retornar um cursor com todas as informações da pl/sql table.

No ambiente que estou acessando, não posso criar nenhum tipo de objeto para auxilio (type,tabelas,etc)... Consigo retornar um cursor acessando as informações da pl/sql table com essas limitações?

Desde já obrigado!

[spernega]

Bom dia,

Você pode usar :

Selecionar tudo

CREATE OR REPLACE
PROCEDURE get_emp_rs (p_deptno    IN  emp.deptno%TYPE,
                      p_recordset OUT SYS_REFCURSOR) AS 
BEGIN 
  OPEN p_recordset FOR
    SELECT ename,
           empno,
           deptno
    FROM   emp
    WHERE  deptno = p_deptno
    ORDER BY ename;
END GetEmpRS;
/

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
  l_cursor  SYS_REFCURSOR;
  l_ename   emp.ename%TYPE;
  l_empno   emp.empno%TYPE;
  l_deptno  emp.deptno%TYPE;
BEGIN
  get_emp_rs (p_deptno    => 30,
              p_recordset => l_cursor);
            
  LOOP 
    FETCH l_cursor
    INTO  l_ename, l_empno, l_deptno;
    EXIT WHEN l_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(l_ename || ' | ' || l_empno || ' | ' || l_deptno);
  END LOOP;
  CLOSE l_cursor;
END;
/

exemplo de https://oracle-base.com/articles/misc/u ... recordsets

Acho que funciona também assim no sql:

Selecionar tudo

var rc refcursor
execute get_emp_rs(10,:rc)