[oracle_br] Tabela temporária
Alguém tem um exemplo de criação e manipulação de tabela temporária dentro de uma procedure?
RES: [oracle_br] Re: RES: RES: [oracle_b r] Re: Tabela Vári avel
Muito obrigado
De: oracle_br@yahoogrupos.com.br [mailto:oracle_br@yahoogrupos.com.br]
Enviada em: segunda-feira, 18 de fevereiro de 2019 16:29
Para: oracle_br@yahoogrupos.com.br
Assunto: [oracle_br] Re: RES: RES: [oracle_b r] Re: Tabela Vári avel
Pra não ficar só no blablablá, eis um exemplo (basicamente COPIADO e COLADO dos
links que te dei) duma rotina PL/SQL que executa um SQL sem saber nem o npme da
tabela nem a quantidade de colunas e seus datatypes) - logicamente, aqui eu só
Exibo via DBMS_OUTPUT, mas vc pode fazer o que bem quiser com os dados qye
recuperar :
scott@DESENV:SQL>set serveroutput on size 100
scott@DESENV:SQL>create or replace procedure PRINT_QUERY_COLUMNS (P_SQL
varchar2) is
2v_SQL VARCHAR2(32767);
3v_c NUMBER;
4v_executeNUMBER;
5v_columnCount INTEGER;
6v_recTab DBMS_SQL.DESC_TAB;
7v_varcharVal VARCHAR2(4000);
8v_numberVal NUMBER;
9v_dateValDATE;
10v_retNUMBER;
11 BEGIN
12v_sql := P_SQL; -- Exemplo : 'select * from employees where rownum=1'
13v_c := DBMS_SQL.OPEN_CURSOR;
14
15DBMS_SQL.PARSE(v_c, v_SQL, DBMS_SQL.NATIVE);
16
17v_execute := DBMS_SQL.EXECUTE(v_c);
18
19DBMS_SQL.DESCRIBE_COLUMNS(v_c, v_columnCount, v_recTab);
20
21FOR j in 1..v_columnCount
22
23 LOOP
24CASE v_recTab(j).col_type
25 WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(v_c,j,v_varcharVal,2000);
26 WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(v_c,j,v_numberVal);
27 WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(v_c,j,v_dateVal);
28 ELSE DBMS_SQL.DEFINE_COLUMN(v_c,j,v_varcharVal,2000);
29END CASE;
30 END LOOP;
31
32 LOOP
33v_ret := DBMS_SQL.FETCH_ROWS(v_c);
34 EXIT WHEN v_ret = 0;
35
36 FOR j in 1..v_columnCount
37LOOP
38 CASE v_recTab(j).col_type
39WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(v_c, j, v_varcharVal);
40 DBMS_OUTPUT.PUT_LINE(v_recTab(j).col_type || ' ' ||
41v_recTab(j).col_name || ' ' || v_varcharVal);
42WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(v_c,j,v_numberVal);
43 DBMS_OUTPUT.PUT_LINE(v_recTab(j).col_type || ' ' ||
44v_recTab(j).col_name || ' ' || v_numberVal);
45WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(v_c,j,v_dateVal);
46 DBMS_OUTPUT.PUT_LINE(v_recTab(j).col_type || ' ' ||
47v_recTab(j).col_name || ' ' || v_dateVal);
48ELSE
49 DBMS_OUTPUT.PUT_LINE(v_recTab(j).col_type || ' ' ||
50v_recTab(j).col_name || ' ' || v_varcharVal);
51 END CASE;
52END LOOP;
53 END LOOP;
54 END;
55 /
Procedimento criado.
==> Executo primeiro passando um SELECT numa tabela DEPT :
scott@DESENV:SQL>exec PRINT_QUERY_COLUMNS ('select * from dept');
2 DEPTNO 79
1 DNAME Depto 79
1 LOC
2 TESTE
2 DEPTNO 13
1 DNAME teste= !!
1 LOC
2 TESTE
2 DEPTNO 10
1 DNAME ACCOUNTING
1 LOC NEW YORK
2 TESTE
2 DEPTNO 20
1 DNAME RESEARCH
1 LOC DALLAS
2 TESTE
2 DEPTNO 30
1 DNAME SALES
1 LOC CHICAGO
2 TESTE
2 DEPTNO 40
1 DNAME OPERATIONS
1 LOC BOSTON
2 TESTE
Procedimento PL/SQL concluído com sucesso.
=> Agora chamo a rotina informando uma query onde informo Apenas Algumas
colunas da tabela EMP :
scott@DESENV:SQL>exec PRINT_QUERY_COLUMNS ('select empno, ename, sal from emp');
2 EMPNO 7369
1 ENAME SMITH
2 SAL 800
2 EMPNO 7499
1 ENAME ALLEN
2 SAL 1600
2 EMPNO 7521
1 ENAME WARD
2 SAL 1250
2 EMPNO 7566
1 ENAME JONES
2 SAL 2975
2 EMPNO 7654
1 ENAME MARTIN
2 SAL 1250
2 EMPNO 7698
1 ENAME BLAKE
2 SAL 2850
2 EMPNO 7782
1 ENAME CLARK
2 SAL 2450
2 EMPNO 7788
1 ENAME SCOTT
2 SAL 3000
2 EMPNO 7839
1 ENAME KING
2 SAL 5000
2 EMPNO 7844
1 ENAME TURNER
2 SAL 1500
2 EMPNO 7876
1 ENAME ADAMS
2 SAL 1100
2 EMPNO 7900
1 ENAME JAMES
2 SAL 950
2 EMPNO 7902
1 ENAME FORD
2 SAL 3000
2 EMPNO 7934
1 ENAME MILLER
2 SAL 1300
Procedimento PL/SQL concluído com sucesso.
==> Posso INCLUSIVE passar cláusulas de WHERE :
scott@DESENV:SQL>exec PRINT_QUERY_COLUMNS ('select empno, ename, sal from emp
where sal > 3000');
2 EMPNO 7839
1 ENAME KING
2 SAL 5000
Procedimento PL/SQL concluído com sucesso.
scott@DESENV:SQL>
==> OKDOC ??? mais fácil que isso não fica
[]s
Chiappa
RES: RES: [oracle_br] Re: Tabela Vári avel
A tabela é criada no momento em que o usuário faz a consulta, com a seguinte nomenclatura “TABELA_ANALIT_”, concatenado com o código do usuário que fez a consulta, a questão é que eu preciso saber as colunas dessa tabela na PROCEDURE, não sei se consegue entender. De: oracle_br@yahoogrupos.com.br [mailto:oracle_br@yahoogrupos.com.br] Enviada em: sexta-feira, 15 de fevereiro de 2019 11:46 Para: oracle_br@yahoogrupos.com.br Assunto: Re: RES: [oracle_br] Re: Tabela Vári avel Continuo sem entender completamente (em especial essa expressão "objeto tabela", que até onde sei Não Faz Sentido NENHUM no contexto do RDBMS Oracle), mas pelo jeito da coisa, TALVEZ o que vc queira é ter um TABLE ** TYPE ** (ie, um TYPE baseado numa tabela) e NÃO 'criar uma tabela a partir da variável', mas COM o ponto adicional que a tabela a ser referenciada é DINÂMICA, vc não sabe o nome dela, sim sim ?? Se for isso, não há um jeito fácil de se fazer isso, um TYPE (seja ROWTYPE, seja TYPE simples) ** tem ** que ser baseado numa tabela ou numa coluna ou num cursor PREDEFINIDO e CONHECIDO, ok ?? Uma alternativa poe ser vc criar um CURSOR SQL dinâmico e basear teu TYPE nele... E sempre, quando se fala em SQL dinâmico, vc tem várias alternativas, a mais flexível/adequada seria o DBMS_SQL, veja https://stackoverflow..com/questions/26549823/rowtype-variable-from-table-name e https://asktom.oracle.com/pls/apex/f?p=100:11:0P11_QUESTION_ID:952567346741460 para alguns exemplos... ==> Porém, TENHO que frisar : esse negócio de sair criando tabelas on-the-fly NÃO É UMA BOA PRÁTICA no RDBMS Oracle : além da DIFICULDADE/COMPLEXIDADE de ter que ficar usando SQL dinâmico, como eu disse antes vc FACILMENTE pode ter problemas de performance por falta de estatísticas e/ou plano gerado inadequadamente - o CORRETO é vc criar a tabela uma só vez antes de começar a codificar, já com TODAS as colunas eventualmente necessárias, aí os programas SEMPRE usam essa tabela já conhecida... []s Chiappa
RES: [oracle_br] Re: Tabela Váriavel
Essa tabela é criada por outro processo “TABELA_ANALIT_” || pCdPessoaUsr, onde a tabela analítica fica “TABELA_ANALIT_2”, (2 é o código do usuário), eu quero transformar a variável em objeto tabela, ex.: reg_Tabela variável%rowtype; De: oracle_br@yahoogrupos.com.br [mailto:oracle_br@yahoogrupos.com.br] Enviada em: sexta-feira, 15 de fevereiro de 2019 09:21 Para: oracle_br@yahoogrupos.com.br Assunto: [oracle_br] Re: Tabela Váriavel Não entendi *** patavina *** do que vc quis dizer com " criar uma tabela dessa variável", até porque uma TABELA vai conter N valores, enquanto uma variável vai conter um só, mas o primeiro ponto que TEM que ser compreendido é que no RDBMS Oracle, muito embora vc POSSA criar objetos on-the-fly (normalmente com SQL dinâmico, via DBMS_SQL ou com EXECUTE IMMEDIATE) o método de trabalho PREFERIDO é vc criar TODAS AS TABELAS que vc precisa ANTES dos seus programas serem codificados, ok ? Entre outras coisas porque o RDBMS na hora de compilar suas procedures/packages/functions/triggers MANTÉM uma relação dos objetos/tabelas/etc dos quais dependem, E porque na hora de montar um Plano de Execução otimizado para um SQL o RDBMS precisa ter Estatísticas das tabelas todas... Não esquecendo também, qualquer RDBMS exige que a tabela tenha um nome E que os dados estejam 'divididos' em COLUNAS, colunas essas que vc (preferencialmente) indica o NOME e o DATATYPE delas : às vezes vc vê uma tabela chamada T com uma só coluna C string, aí o programa bota o que quiser do jeito que quiser nessa tabela - isso via de regra é um ERRO, já que além de perder a Integridade de dados (pois string aceita qquer coisa!!) o RDBMS ** não é programado/pensado ** para funcionar assim, nem a linguagem SQL A segunda coisa é : estando criada a tabela, para inserir o Oracle aceita tanto as tabelas "normais/comuns", cujos dados ficam PERMANENTES após um COMMIT, quanto as tabelas TEMPORÁRIAS, cujos dados só existem enquanto a sessão que os criou estiver conectada no banco, quando essa sessão desconecta essa tabela é automaticamente limpa... E a terceira e última Obs é : seja qual for a tabela que vc criou, vc introduz dados nela com INSERT, alterar dados já introduzidos com UPDATE, remove dados com DELETE e pesquisa dados já introduzidos anteriormente com SELECT, ok ?? Não importa como foi criada, de que tipo é, a linguagem SQL funciona assim Espero ter dados alguns esclarecimentos, mas se Ainda tem dúvidas, PLEASE explica (muito!) melhor o que vc quer fazer e posso tentar dar um exemplo []s Chiappa
[oracle_br] Tabela Váriavel
Eu tenho o seguinte código: Tem como criar uma tabela dessa variável? vcTabelaTemp := Trim(pUserNameConsWeb) || '.EL_CVISITAIMOB_ANALIT_' || pCdPessoaUsr; CREATE OR REPLACE PROCEDURE SP_CVISITAIMOB ( pCdPessoaUsrIN USUARIO.CDPESSOAUSR%TYPE , pUserNameConsWeb IN CHAR ) AS -- -- Variaveis de retorno -- vcMsg VARCHAR2(32767); vcTabelaVARCHAR2(500); vcProc VARCHAR2(500); -- -- Variaveis de trabalho -- vcTabelaBaseVARCHAR2(32767); BEGIN -- vcProc:= 'SP_CVISITAIMOB'; vcTabela := ''; vcMsg := 'Registro não processado com sucesso.'; -- -- Tabela Temporaria - Analitica -- IF pUserNameConsWeb IS NULL THEN -- RAISE_APPLICATION_ERROR(-2, 'Name ConsWeb não informado !'); -- END IF; -- vcTabelaTemp := Trim(pUserNameConsWeb) || '.EL_CVISITAIMOB_ANALIT_' || pCdPessoaUsr; -- EXCEPTION -- WHEN NO_DATA_FOUND THEN -- vcMsg := Trim(vcProc) || ':' || Trim(vcTabela) || ' - Dados N?o Cadastrados.' || ' Comando SQL --> ' || SubStr(vcSQL, 1, 400); -- -- Retorno -- RAISE_APPLICATION_ERROR(-2, vcMsg); -- END; /
