Início / dba / Perguntas / 2710
Accepted
Martin
Asked: 2011-05-14 03:32:27 +0800 CST

Alternativas para concatenar strings ou seguir procedimentos para evitar a repetição do código de consulta SQL?

  • 772

Isenção de responsabilidade: por favor, tenha paciência comigo como alguém que usa bancos de dados apenas uma pequena fração de seu tempo de trabalho. (Na maioria das vezes eu faço programação C++ no meu trabalho, mas todo mês preciso pesquisar/consertar/adicionar algo em um banco de dados Oracle.)

Eu precisei repetidamente escrever consultas SQL complexas, tanto para consultas ad-hoc quanto para consultas incorporadas a aplicativos, onde grandes partes das consultas eram apenas "código" repetido.

Escrever tais abominações em uma linguagem de programação tradicional causaria sérios problemas, mas eu ( eu ) ainda não consegui encontrar nenhuma técnica decente para evitar a repetição do código de consulta SQL.

Editar: 1º, quero agradecer aos respondentes que forneceram excelentes melhorias ao meu exemplo original . No entanto, esta questão não é sobre o meu exemplo. É sobre repetitividade em consultas SQL. Como tal, as respostas ( JackP , Leigh ) até agora fazem um ótimo trabalho em mostrar que você pode reduzir a repetitividade escrevendo consultas melhores . No entanto, mesmo assim, você enfrenta alguma repetitividade que aparentemente não pode ser removida: isso sempre me incomodou com o SQL. Em linguagens de programação "tradicionais", posso refatorar bastante para minimizar a repetitividade no código, mas com o SQL parece que não há ferramentas (?) Que permitam isso, exceto para escrever uma instrução menos repetitiva para começar.

Observe que removi a tag Oracle novamente, pois ficaria genuinamente interessado se não houvesse banco de dados ou linguagem de script que permitisse algo mais.

Aqui está uma joia que montei hoje. Ele basicamente informa a diferença em um conjunto de colunas de uma única tabela. Por favor, dê uma olhada no código a seguir, esp. a consulta grande no final. Continuo abaixo.

--
-- Create Table to test queries
--
CREATE TABLE TEST_ATTRIBS (
id NUMBER PRIMARY KEY,
name  VARCHAR2(300) UNIQUE,
attr1 VARCHAR2(2000),
attr2 VARCHAR2(2000),
attr3 INTEGER,
attr4 NUMBER,
attr5 VARCHAR2(2000)
);

--
-- insert some test data
--
insert into TEST_ATTRIBS values ( 1, 'Alfred',   'a', 'Foobar', 33, 44, 'e');
insert into TEST_ATTRIBS values ( 2, 'Batman',   'b', 'Foobar', 66, 44, 'e');
insert into TEST_ATTRIBS values ( 3, 'Chris',    'c', 'Foobar', 99, 44, 'e');
insert into TEST_ATTRIBS values ( 4, 'Dorothee', 'd', 'Foobar', 33, 44, 'e');
insert into TEST_ATTRIBS values ( 5, 'Emilia',   'e', 'Barfoo', 66, 44, 'e');
insert into TEST_ATTRIBS values ( 6, 'Francis',  'f', 'Barfoo', 99, 44, 'e');
insert into TEST_ATTRIBS values ( 7, 'Gustav',   'g', 'Foobar', 33, 44, 'e');
insert into TEST_ATTRIBS values ( 8, 'Homer',    'h', 'Foobar', 66, 44, 'e');
insert into TEST_ATTRIBS values ( 9, 'Ingrid',   'i', 'Foobar', 99, 44, 'e');
insert into TEST_ATTRIBS values (10, 'Jason',    'j', 'Bob',    33, 44, 'e');
insert into TEST_ATTRIBS values (12, 'Konrad',   'k', 'Bob',    66, 44, 'e');
insert into TEST_ATTRIBS values (13, 'Lucas',    'l', 'Foobar', 99, 44, 'e');

insert into TEST_ATTRIBS values (14, 'DUP_Alfred',   'a', 'FOOBAR', 33, 44, 'e');
insert into TEST_ATTRIBS values (15, 'DUP_Chris',    'c', 'Foobar', 66, 44, 'e');
insert into TEST_ATTRIBS values (16, 'DUP_Dorothee', 'd', 'Foobar', 99, 44, 'e');
insert into TEST_ATTRIBS values (17, 'DUP_Gustav',   'X', 'Foobar', 33, 44, 'e');
insert into TEST_ATTRIBS values (18, 'DUP_Homer',    'h', 'Foobar', 66, 44, 'e');
insert into TEST_ATTRIBS values (19, 'DUP_Ingrid',   'Y', 'foo',    99, 44, 'e');

insert into TEST_ATTRIBS values (20, 'Martha',   'm', 'Bob',    33, 88, 'f');

-- Create comparison view
CREATE OR REPLACE VIEW TA_SELFCMP as
select 
t1.id as id_1, t2.id as id_2, t1.name as name, t2.name as name_dup,
t1.attr1 as attr1_1, t1.attr2 as attr2_1, t1.attr3 as attr3_1, t1.attr4 as attr4_1, t1.attr5 as attr5_1,
t2.attr1 as attr1_2, t2.attr2 as attr2_2, t2.attr3 as attr3_2, t2.attr4 as attr4_2, t2.attr5 as attr5_2
from TEST_ATTRIBS t1, TEST_ATTRIBS t2
where t1.id <> t2.id
and t1.name <> t2.name
and t1.name = REPLACE(t2.name, 'DUP_', '')
;

-- NOTE THIS PIECE OF HORRIBLE CODE REPETITION --
-- Create comparison report
-- compare 1st attribute
select 'attr1' as Different,
id_1, id_2, name, name_dup,
CAST(attr1_1 AS VARCHAR2(2000)) as Val1, CAST(attr1_2 AS VARCHAR2(2000)) as Val2
from TA_SELFCMP
where attr1_1 <> attr1_2
or (attr1_1 is null and attr1_2 is not null)
or (attr1_1 is not null and attr1_2 is null)
union
-- compare 2nd attribute
select 'attr2' as Different,
id_1, id_2, name, name_dup,
CAST(attr2_1 AS VARCHAR2(2000)) as Val1, CAST(attr2_2 AS VARCHAR2(2000)) as Val2
from TA_SELFCMP
where attr2_1 <> attr2_2
or (attr2_1 is null and attr2_2 is not null)
or (attr2_1 is not null and attr2_2 is null)
union
-- compare 3rd attribute
select 'attr3' as Different,
id_1, id_2, name, name_dup,
CAST(attr3_1 AS VARCHAR2(2000)) as Val1, CAST(attr3_2 AS VARCHAR2(2000)) as Val2
from TA_SELFCMP
where attr3_1 <> attr3_2
or (attr3_1 is null and attr3_2 is not null)
or (attr3_1 is not null and attr3_2 is null)
union
-- compare 4th attribute
select 'attr4' as Different,
id_1, id_2, name, name_dup,
CAST(attr4_1 AS VARCHAR2(2000)) as Val1, CAST(attr4_2 AS VARCHAR2(2000)) as Val2
from TA_SELFCMP
where attr4_1 <> attr4_2
or (attr4_1 is null and attr4_2 is not null)
or (attr4_1 is not null and attr4_2 is null)
union
-- compare 5th attribute
select 'attr5' as Different,
id_1, id_2, name, name_dup,
CAST(attr5_1 AS VARCHAR2(2000)) as Val1, CAST(attr5_2 AS VARCHAR2(2000)) as Val2
from TA_SELFCMP
where attr5_1 <> attr5_2
or (attr5_1 is null and attr5_2 is not null)
or (attr5_1 is not null and attr5_2 is null)
;

Como você pode ver, a consulta para gerar um "relatório de diferenças" usa o mesmo bloco SQL SELECT 5 vezes (pode facilmente ser 42 vezes!). Isso me parece absolutamente com morte cerebral (posso dizer isso, afinal eu escrevi o código), mas não consegui encontrar nenhuma boa solução para isso.

  • Se isso fosse uma consulta em algum código de aplicativo real, eu poderia escrever uma função que remenda essa consulta como uma string e, em seguida, executaria a consulta como uma string.

    • -> Construir strings é horrível e horrível de testar e manter. Se o "código do aplicativo" for escrito em uma linguagem como PL/SQL, parece tão errado que dói.

  • Como alternativa, se usado em PL/SQL ou algo semelhante, acho que há alguns meios processuais para tornar essa consulta mais fácil de manter.

    • -> Desdobrar algo que pode ser expresso em uma única consulta em etapas processuais apenas para evitar a repetição de código também parece errado.

  • Se essa consulta fosse necessária como uma exibição no banco de dados, então - pelo que entendi - não haveria outra maneira senão manter a definição de exibição conforme postei acima. (!!?)

    • -> Na verdade, tive que fazer alguma manutenção em uma definição de visualização de 2 páginas, uma vez que não estava muito longe da declaração acima. Obviamente, alterar qualquer coisa nessa exibição exigia uma pesquisa de texto regexp na definição da exibição para saber se a mesma subinstrução foi usada em outra linha e se precisava ser alterada lá.

Então, como diz o título - que técnicas existem para evitar ter que escrever tais abominações?

query dynamic-sql

3 respostas

  1. Best Answer

    Você é muito modesto - seu SQL está bem escrito e conciso, dada a tarefa que você está realizando. Algumas indicações:

    • t1.name <> t2.nameé sempre verdadeiro se t1.name = REPLACE(t2.name, 'DUP_', '')- você pode descartar o primeiro
    • geralmente você quer union all. unionsignifica union alldescartar duplicatas. Pode não fazer diferença neste caso, mas sempre usar union allé um bom hábito, a menos que você queira descartar explicitamente quaisquer duplicatas.

    • se você deseja que as comparações numéricas ocorram após a conversão para varchar, vale a pena considerar o seguinte:

      create view test_attribs_cast as 
select id, name, attr1, attr2, cast(attr3 as varchar(2000)) as attr3, 
       cast(attr4 as varchar(2000)) as attr4, attr5
from test_attribs;

create view test_attribs_unpivot as 
select id, name, 1 as attr#, attr1 as attr from test_attribs_cast union all
select id, name, 2, attr2 from test_attribs_cast union all
select id, name, 3, attr3 from test_attribs_cast union all
select id, name, 4, attr4 from test_attribs_cast union all
select id, name, 5, attr5 from test_attribs_cast;

select 'attr'||t1.attr# as different, t1.id as id_1, t2.id as id_2, t1.name, 
       t2.name as name_dup, t1.attr as val1, t2.attr as val2
from test_attribs_unpivot t1 join test_attribs_unpivot t2 on(
       t1.id<>t2.id and 
       t1.name = replace(t2.name, 'DUP_', '') and 
       t1.attr#=t2.attr# )
where t1.attr<>t2.attr or (t1.attr is null and t2.attr is not null)
      or (t1.attr is not null and t2.attr is null);

      a segunda visualização é um tipo de unpivotoperação - se você estiver usando pelo menos 11g, poderá fazer isso de forma mais concisa com a unpivotcláusula - veja aqui um exemplo

    • Eu digo para não seguir a rota processual se você puder fazer isso em SQL, mas...
    • Provavelmente vale a pena considerar o SQL dinâmico, apesar dos problemas que você mencionou com teste e manutenção

    --EDITAR--

    Para responder ao lado mais geral da questão, existem técnicas para reduzir a repetição em SQL, incluindo:

    Mas você não pode trazer ideias OO diretamente para o mundo do SQL - em muitos casos, a repetição é boa se a consulta for legível e bem escrita, e seria imprudente recorrer ao SQL dinâmico (por exemplo) apenas para evitar a repetição.

    A consulta final, incluindo a alteração sugerida por Leigh e um CTE em vez de uma exibição, pode ser algo como isto:

    with t as ( select id, name, attr#, 
                   decode(attr#,1,attr1,2,attr2,3,attr3,4,attr4,attr5) attr
            from test_attribs
                 cross join (select rownum attr# from dual connect by rownum<=5))
select 'attr'||t1.attr# as different, t1.id as id_1, t2.id as id_2, t1.name, 
       t2.name as name_dup, t1.attr as val1, t2.attr as val2
from t t1 join test_attribs_unpivot t2 
               on( t1.id<>t2.id and 
                   t1.name = replace(t2.name, 'DUP_', '') and 
                   t1.attr#=t2.attr# )
where t1.attr<>t2.attr or (t1.attr is null and t2.attr is not null)
      or (t1.attr is not null and t2.attr is null);
    • 13

  2. Aqui está uma alternativa para a visão test_attribs_unpivot fornecida por JackPDouglas (+1) que funciona em versões anteriores a 11g e faz menos verificações completas de tabelas:

    CREATE OR REPLACE VIEW test_attribs_unpivot AS
   SELECT ID, Name, MyRow Attr#, CAST(
      DECODE(MyRow,1,attr1,2,attr2,3,attr3,4,attr4,attr5) AS VARCHAR2(2000)) attr
   FROM TEST_ATTRIBS 
   CROSS JOIN (SELECT level MyRow FROM dual connect by level<=5);

    Sua consulta final pode ser usada inalterada com esta exibição.

    • 7

  3. Muitas vezes encontro o problema semelhante para comparar duas versões de uma tabela para linhas novas, excluídas ou alteradas. Há um mês publiquei aqui uma solução para SQL Server usando PowerShell .

    Para adaptá-lo ao seu problema, primeiro crio duas visualizações para separar o original das linhas duplicadas

    CREATE OR REPLACE VIEW V1_TEST_ATTRIBS AS 
select * from TEST_ATTRIBS where SUBSTR(name, 1, 4) <> 'DUP_'; 

CREATE OR REPLACE VIEW V2_TEST_ATTRIBS AS 
select id, REPLACE(name, 'DUP_', '') name, attr1, attr2, attr3, attr4, attr5 from TEST_ATTRIBS where SUBSTR(name, 1, 4) = 'DUP_';

    e então eu verifico as mudanças com

    SELECT 1 SRC, NAME, ATTR1, ATTR2, ATTR3, ATTR4, ATTR5 FROM V1_TEST_ATTRIBS
MINUS
Select 1 SRC, NAME, ATTR1, ATTR2, ATTR3, ATTR4, ATTR5 from V2_TEST_ATTRIBS
UNION
SELECT 2 SRC, NAME, ATTR1, ATTR2, ATTR3, ATTR4, ATTR5 FROM V2_TEST_ATTRIBS
MINUS
SELECT 2 SRC ,NAME, ATTR1, ATTR2, ATTR3, ATTR4, ATTR5 FROM V1_TEST_ATTRIBS
ORDER BY NAME, SRC;

    A partir daqui, posso encontrar seus IDs originais

    Select NVL(v1.id, v2.id) id,  t.name, t.attr1, t.attr2, t.attr3, t.attr4, t.attr5 from
(
SELECT 1 SRC, NAME, ATTR1, ATTR2, ATTR3, ATTR4, ATTR5 FROM V1_TEST_ATTRIBS
MINUS
Select 1 SRC, NAME, ATTR1, ATTR2, ATTR3, ATTR4, ATTR5 from V2_TEST_ATTRIBS
UNION
SELECT 2 SRC, NAME, ATTR1, ATTR2, ATTR3, ATTR4, ATTR5 FROM V2_TEST_ATTRIBS
MINUS
Select 2 SRC ,NAME, ATTR1, ATTR2, ATTR3, ATTR4, ATTR5 from V1_TEST_ATTRIBS
) t
LEFT JOIN V1_TEST_ATTRIBS V1 ON T.NAME = V1.NAME AND T.SRC = 1
LEFT JOIN V2_TEST_ATTRIBS V2 ON T.NAME = V2.NAME AND T.SRC = 2
ORDER by NAME, SRC;

    BTW: MINUS e UNION e GROUP BY tratam diferentes NULL's como iguais. O uso dessas operações torna as consultas mais elegantes.

    Dica para usuários do SQL Server: MINUS é nomeado EXCETO lá, mas funciona de maneira semelhante.

    • 4

relate perguntas