Query Dinamica

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
Renato Freitas
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 13
Registrado em: Sáb, 23 Ago 2008 5:53 pm
Localização: são Paulo - SP

Pessoal,

Estou tendo algumas dúvidas sobre o uso do Package DBMS_SQL para query dinamica, então desenvolvi uma rotina um pouco mais engessada com o Execute Immediate.

Por favor se alguém tiver um exemplo pacana com o uso do DBMS_SQL, ou alguma dica a respeito desse meu procedimento.

Este procedimento le uma tabela de tabelas do meu schema, depois le as colunas dessa tabela e identifica quais delas são PK fazendo Join com uma tabela minha de constraint, depois monta uma query dinamica e aonde está comentado ele deve gravar o nome da tabela e a query que foi gerada no loop mais interno.


Selecionar tudo

CREATE OR REPLACE PROCEDURE QA_KEYRUS.PRC_VERIFICA_DUPLICIDADE_2 
IS
	
	V_ID_TABELA NUMBER;
	V_NOME_TABELA VARCHAR2(50);
	V_COUNT_TAB NUMBER := 1;
	V_AUX_COUNT_TAB NUMBER;
	V_ID_COLUNA NUMBER;
	V_NOME_COLUNA VARCHAR2(50);
	V_COUNT_COL NUMBER := 0;
	V_AUX_COUNT_COL_MIN NUMBER := 0;
	V_AUX_COUNT_COL_MAX NUMBER := 0;
	V_TOTAL_LINHA_DUP NUMBER := 0;
	
	V_QUERY VARCHAR2(20000) := 'SELECT ';
	V_QUER2 VARCHAR2(20000);
	
BEGIN

	SELECT MAX(ID_TABELA) AS ID_TABELA INTO V_AUX_COUNT_TAB FROM QA_KEYRUS.TABELAS;
	
	LOOP 
		SELECT ID_TABELA, NOME_TABELA INTO V_ID_TABELA, V_NOME_TABELA
		FROM QA_KEYRUS.TABELAS
		WHERE ID_TABELA = V_COUNT_TAB;
		
		LOOP
			SELECT MIN(ID_COLUNA) AS ID_COLUNA INTO V_AUX_COUNT_COL_MIN FROM QA_KEYRUS.COLUNAS WHERE ID_TABELA = V_ID_TABELA;
			SELECT MAX(ID_COLUNA) AS ID_COLUNA INTO V_AUX_COUNT_COL_MAX FROM QA_KEYRUS.COLUNAS WHERE ID_TABELA = V_ID_TABELA;
		
			V_AUX_COUNT_COL_MIN := V_AUX_COUNT_COL_MIN + V_COUNT_COL;
		
			SELECT A.ID_COLUNA, A.NOME_COLUNA INTO V_ID_COLUNA, V_NOME_COLUNA
			FROM QA_KEYRUS.COLUNAS A
			INNER JOIN
				QA_KEYRUS.CONSTRAINTS B
			ON A.ID_COLUNA = B.ID_COLUNA
			WHERE A.ID_TABELA = V_ID_TABELA
			AND A.ID_COLUNA = V_AUX_COUNT_COL_MIN
			AND B.INDICADOR_CONSTRAINT = 'PK';
	
			V_QUERY := V_QUERY || V_NOME_COLUNA || ',';
			
			V_QUERY_2 := V_QUERY_2 || V_NOME_COLUNA ||',';
	
		V_COUNT_COL := V_COUNT_COL +1;
		EXIT WHEN V_COUNT_COL > V_AUX_COUNT_COL_MAX
		END LOOP;
		
		V_QUERY := V_QUERY || ' COUNT(*) FROM QA_KEYRUS.' || V_NOME_TABELA || ' GROUP BY ' || V_QUERY_2 || ' HAVING COUNT(*) > 1;';
		
		EXECUTE IMMEDIATE V_QUERY;
		
		/* AQUI VAI O INSERT COM A STRING QUERY, A TABELA QUE ESTA´SENDO TESTADA.*/
		
	V_COUNT_TAB := V_COUNT_TAB +1;
	EXIT WHEN V_COUNT_TAB > V_AUX_COUNT_TAB;
	END LOOP;
END;
Trevisolli
Moderador
Moderador
Mensagens: 2016
Registrado em: Qua, 12 Jan 2005 3:25 pm
Localização: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Renato, beleza brother?

Dá uma olhada neste link com um exemplo e veja se te ajuda:

http://www.glufke.net/oracle/viewtopic. ... ht=dbmssql

Qualquer coisa, manda pra gente.
Renato Freitas
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 13
Registrado em: Sáb, 23 Ago 2008 5:53 pm
Localização: são Paulo - SP

Vou tentar trabalhar com cursores normais e o Execute Immediate agora a tarde, se não resolver com isso vou estudar mais o uso do Package DBMS_SQL, más este post que você me passou é ótimo.

Valeu

depois eu posto os resultados.
:-o

Renato Freitas
Renato Freitas
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 13
Registrado em: Sáb, 23 Ago 2008 5:53 pm
Localização: são Paulo - SP

Olha só,

Consegui gerar minhas queries dinamicamente, e gravar em uma tabela, usando apenas cursores normais e concatenando os campos identificados como PKs, por enquanto não vou precisar executar as minhas Strings Queries geradas na minha Procedure.

Valeu Pela Ajuda, e segue o código aí, se alguém tiver más melhorias para ele por favor fiquem a vontade.

Só tem um bug, quando alguma tabela que não possui PK aparece no looping, ele grava apenas a String

Selecionar tudo

SELECT COUNT(*) FROM TABELA GROUP BY HAVING COUNT(*)  >1
sem nenhum campo;

Vou tentar fazer algumas condições para evitar isso.

Selecionar tudo

CREATE OR REPLACE PROCEDURE QA_KEYRUS.PRC_VERIFICA_DUPLICIDADE_3 
IS

	V_ID_TABELA QA_KEYRUS.TABELAS.ID_TABELA%TYPE;
	V_NOME_TABELA QA_KEYRUS.TABELAS.NOME_TABELA%TYPE;
	V_OWNER QA_KEYRUS.TABELAS.NOME_OWNER%TYPE;
	
	V_ID_COLUNA QA_KEYRUS.COLUNAS.ID_COLUNA%TYPE;
	V_NOME_COLUNA QA_KEYRUS.COLUNAS.NOME_COLUNA%TYPE;
	
	
	V_QUERY QA_KEYRUS.QUALIDADE.QUERY_QUALIDADE%TYPE := 'SELECT ';
	V_QUERY_GROUP QA_KEYRUS.QUALIDADE.QUERY_QUALIDADE%TYPE := ' GROUP BY ';
	
	CURSOR C_TABELAS IS
	SELECT ID_TABELA, NOME_TABELA, NOME_OWNER
	FROM QA_KEYRUS.TABELAS;
	
	CURSOR C_COLUNAS(P_ID_TABELA NUMBER) IS
	SELECT A.ID_COLUNA, A.NOME_COLUNA
	FROM QA_KEYRUS.COLUNAS A
	INNER JOIN
		QA_KEYRUS.CONSTRAINT B
	ON A.ID_COLUNA = B.ID_COLUNA
	WHERE A.ID_TABELA = P_ID_TABELA
	AND B.INDICADOR_CONSTRAINT = 'PK';
		
BEGIN
	FOR X IN C_TABELAS 
		LOOP
			V_ID_TABELA := X.ID_TABELA;
			V_NOME_TABELA := X.NOME_TABELA;
			V_OWNER := X.NOME_OWNER;
			
			FOR Y IN C_COLUNAS(V_ID_TABELA)
				LOOP	
					V_ID_COLUNA := Y.ID_COLUNA;
					V_NOME_COLUNA := Y.NOME_COLUNA;
					
					V_QUERY := V_QUERY || V_NOME_COLUNA || ',';
					
					IF V_QUERY_GROUP = ' GROUP BY ' THEN
					
						V_QUERY_GROUP := V_QUERY_GROUP || V_NOME_COLUNA;
					ELSE
						V_QUERY_GROUP := V_QUERY_GROUP || ',' || V_NOME_COLUNA;
					END IF;
					
				END LOOP;
				
				V_QUERY := V_QUERY || ' COUNT(*) FROM ' || V_OWNER || '.' || V_NOME_TABELA || V_QUERY_GROUP || ' HAVING COUNT(*) > 1;';
				
			BEGIN
				INSERT INTO QA_KEYRUS.QUALIDADE
				(
					 ID_QUALIDADE
					,NOME_TABELA
					,QUERY_QUALIDADE
				)
				VALUES
				(
					 QA_KEYRUS.SEQ_QUERIES_QUALIDADE.NEXTVAL
					,V_NOME_TABELA
					,V_QUERY
				);
				
				EXCEPTION
					WHEN DUP_VAL_ON_INDEX THEN
					RAISE_APPLICATION_ERROR(-20003,'QUERY JÁ CADASTRADA.');
					WHEN OTHERS THEN
					RAISE_APPLICATION_ERROR(-20003,'ERRO ORACLE: ' || SQLERRM);
			END;
			
			V_QUERY := 'SELECT '; 
			V_QUERY_GROUP := ' GROUP BY ';
					
		END LOOP;
END;

Valeu

Abraços
Renato Freitas
:-o
Renato Freitas
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 13
Registrado em: Sáb, 23 Ago 2008 5:53 pm
Localização: são Paulo - SP

Pessoal,

Agora preciso executar essa query com Execute Immediate depois do loop mais interno e gravar os resultados em uma tabela.

Minha dúvida é como tratar essa execussão quando for retornado mais de um registro, O execute Immediate suporta isso?
Renato Freitas
Rank: Estagiário Sênior
Rank: Estagiário Sênior
Mensagens: 13
Registrado em: Sáb, 23 Ago 2008 5:53 pm
Localização: são Paulo - SP

Pessoal

Estou tentando executar esta query más está me retornando caractere invalido.

Assim minha query dinamica gerada nos loops retorna mais de um registro dependendo da tabela que ele vai ler, então pesquisei um pouco sobre Bulk Collect e estou tentando implementar más estou caindo nestes erros, se alguém poder me dar um luz, AONDE ESTOU ERRANDO OU SE POSSO IMPLEMENTAR ESSA ROTINA DE OUTRA FORMA.

Selecionar tudo

CREATE OR REPLACE PROCEDURE QA_KEYRUS.PRC_VERIFICA_DUPLICIDADE_2
/* PROCEDURE QUE GERA AS QUERYS PARA VERIFICAÇÃO DE DUPLICIDADE NA BASE DO CIBE 
*AUTOR: RENATO FREITAS 
*DATA DE CRIACAO: 26/08/2008
****************************************************************************************/

IS
	/* DECLARACAO DE VARIÁVEIS UTILIZADAS NA PROCEDURE */
	V_ID_TABELA QA_KEYRUS.TABELAS.ID_TABELA%TYPE;
	V_NOME_TABELA QA_KEYRUS.TABELAS.NOME_TABELA%TYPE;
	V_OWNER QA_KEYRUS.TABELAS.NOME_OWNER%TYPE;
	
	V_ID_COLUNA QA_KEYRUS.COLUNAS.ID_COLUNA%TYPE;
	V_NOME_COLUNA QA_KEYRUS.COLUNAS.NOME_COLUNA%TYPE;
	
	V_QUERY QA_KEYRUS.QUALIDADE.QUERY_QUALIDADE%TYPE := 'SELECT ';
	V_QUERY_GROUP QA_KEYRUS.QUALIDADE.QUERY_QUALIDADE%TYPE := ' GROUP BY ';
	
	TYPE T IS TABLE OF NUMBER;
	COL_RESULT T;
	
	/* CURSOR DE TABELAS */
	CURSOR C_TABELAS IS
	SELECT ID_TABELA, NOME_TABELA, NOME_OWNER
	FROM QA_KEYRUS.TABELAS;
	
	/* CURSOR DE COLUNAS QUE IDENTIFICA AS CHAVES PRIMARIAS
	DE UMA TABELA PASSADA COMO PARAMETRO PELO CURSOR DE TABELAS */
	CURSOR C_COLUNAS(P_ID_TABELA NUMBER) IS
	SELECT A.ID_COLUNA, A.NOME_COLUNA
	FROM QA_KEYRUS.COLUNAS A
	INNER JOIN
		QA_KEYRUS.CONSTRAINT B
	ON A.ID_COLUNA = B.ID_COLUNA
	WHERE A.ID_TABELA = P_ID_TABELA
	AND B.INDICADOR_CONSTRAINT = 'PK';
		
BEGIN
	/* ESTE LOOP MAIS EXTERNO CONTROLA AS TABELAS DO CURSOR DE TABELAS */
	FOR X IN C_TABELAS 
	LOOP
		V_ID_TABELA := X.ID_TABELA;
		V_NOME_TABELA := X.NOME_TABELA;
		V_OWNER := X.NOME_OWNER;
		
		/* ESTE LOOP MAIS INTERNO IDENTIFICA AS COLUNAS QUE SÃO PKs
		E UTILIZA AS VARIAVEIS V_QUERY E V_QUERY_GROUP PARA CONCATENA-LAS*/
		FOR Y IN C_COLUNAS(V_ID_TABELA)
		LOOP	
			V_ID_COLUNA := Y.ID_COLUNA;
			V_NOME_COLUNA := Y.NOME_COLUNA;
			
			V_QUERY := V_QUERY || V_NOME_COLUNA || ',';
			
			/* ESTE CONDICIONAL SERVE PARA QUE A VIRGULA NÃO ENTRE NA 1º PASSAGEN DO LOOP*/
			IF V_QUERY_GROUP = ' GROUP BY ' THEN
			
				V_QUERY_GROUP := V_QUERY_GROUP || V_NOME_COLUNA;
			ELSE
				V_QUERY_GROUP := V_QUERY_GROUP || ',' || V_NOME_COLUNA;
			END IF;
			
		END LOOP;
			

		IF V_QUERY = 'SELECT ' THEN
			V_QUERY := NULL;
		ELSE	
			V_QUERY := V_QUERY || ' COUNT(*) FROM ' || V_OWNER || '.' || V_NOME_TABELA || V_QUERY_GROUP || ' HAVING COUNT(*) > 1;';
		END IF;
		
		IF V_QUERY IS NOT NULL THEN
			EXECUTE IMMEDIATE V_QUERY
			BULK COLLECT INTO COL_RESULT;
			
			FOR Z IN COL_RESULT.FIRST ..  COL_RESULT.LAST
			LOOP
				INSERT INTO QA_KEYRUS.RESULTADO_MEDIDAS_TMP
				VALUES( V_NOME_TABELA, COL_RESULT(Z) );
			END LOOP;
		END IF;
		
		V_QUERY := 'SELECT '; 
		V_QUERY_GROUP := ' GROUP BY ';
	END LOOP;
END;
OBRIGADO

RENATO FREITAS :o
Responder
  • Informação
  • Quem está online

    Usuários navegando neste fórum: Nenhum usuário registrado e 1 visitante