主页 / dba / 问题 / 2710
Accepted
Martin
Asked: 2011-05-14 03:32:27 +0800 CST

连接字符串的替代方法或程序化以防止 SQL 查询代码重复？

  • 772

免责声明：作为一个只在其工作时间的一小部分中使用数据库的人，请多多包涵。（大部分时间我在工作中进行 C++ 编程，但每个奇数月我都需要在 Oracle 数据库中搜索/修复/添加一些东西。）

我反复需要编写复杂的 SQL 查询，既针对临时查询，也针对内置于应用程序中的查询，其中大部分查询只是重复“代码”。

用传统的编程语言编写这种可憎的东西会给你带来很大的麻烦，但我（我）还没有找到任何合适的技术来防止 SQL 查询代码重复。

编辑：第一，我要感谢为我的原始示例提供出色改进的回答者。但是，这个问题与我的例子无关。这是关于 SQL 查询的重复性。因此，迄今为止的答案（JackP、Leigh）很好地表明您可以通过编写更好的查询来减少重复性。然而即便如此，您仍面临一些显然无法消除的重复性：这总是让我对 SQL 感到厌烦。在“传统”编程语言中，我可以进行很多重构以最大程度地减少代码中的重复性，但是对于 SQL，似乎没有（？）工具可以做到这一点，除了编写一个较少重复的语句开始。

请注意，我再次删除了 Oracle 标记，因为我真的很感兴趣是否没有数据库或脚本语言可以提供更多功能。

这是我今天拼凑而成的一颗这样的宝石。它基本上报告单个表的一组列的差异。请浏览以下代码，尤其是。最后的大查询。下面我继续。

--
-- 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)
;

如您所见，生成“差异报告”的查询使用相同的 SQL SELECT 块 5 次（很可能是 42 次！）。这让我觉得绝对是脑死亡（我可以这么说，毕竟我写了代码），但我还没有找到任何好的解决方案。

  • 如果这将是一些实际应用程序代码中的查询，我可以编写一个函数，将该查询拼凑为一个字符串，然后我将查询作为一个字符串执行。

    • -> 构建字符串是可怕的和可怕的测试和维护。如果“应用程序代码”是用诸如 PL/SQL 之类的语言编写的，那会感觉错得离谱。

  • 或者，如果从 PL/SQL 或类似方法中使用，我猜想有一些程序方法可以使这个查询更易于维护。

    • -> 将可以在单个查询中表达的内容展开为程序步骤，只是为了防止代码重复也感觉不对。

  • 如果需要此查询作为数据库中的视图，那么 - 据我所知 - 除了实际维护我上面发布的视图定义之外别无他法。（！！？）

    • -> 实际上，一旦与上述声明相差不远，我就必须对 2 页​​视图定义进行一些维护。显然，更改此视图中的任何内容都需要对视图定义进行正则表达式文本搜索，以确定是否在另一行中使用了相同的子语句以及是否需要在那里进行更改。

那么，正如标题所言——有什么技巧可以防止写出这样的可憎之物？

query dynamic-sql

3 个回答

  1. Best Answer

    你太谦虚了——考虑到你正在承担的任务，你的 SQL 写得很好而且简洁。几点建议：

    • t1.name <> t2.name如果t1.name = REPLACE(t2.name, 'DUP_', '')-您可以放弃前者，则始终为真
    • 通常你想要union all。union意味着union all然后删除重复项。在这种情况下可能没有区别，但始终使用union all是一个好习惯，除非您明确想要删除任何重复项。

    • 如果您愿意在转换为 varchar后进行数字比较，则可能值得考虑以下事项：

      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);

      第二种视图是一种unpivot操作 - 如果您至少使用 11g，则可以使用unpivot子句更简洁地执行此操作 - 请参见此处的示例

    • 我说如果你能在 SQL 中做到这一点，就不要走程序路线，但是......
    • 尽管您提到了测试和维护方面的问题，但动态 SQL 可能值得考虑

    - 编辑 -

    为了回答这个问题更笼统的一面，有一些技术可以减少 SQL 中的重复，包括：

    但是您不能将 OO 思想直接带入 SQL 世界——在许多情况下，如果查询可读且编写良好，则重复是可以的，而仅仅为了避免重复而求助于动态 SQL（例如）是不明智的。

    包括 Leigh 建议的更改和 CTE 而不是视图的最终查询可能如下所示：

    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. 这是 JackPDouglas (+1)提供的 test_attribs_unpivot 视图的替代方法，该视图适用于 11g 之前的版本，并且执行的全表扫描更少：

    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);

    他的最终查询可以在此视图中不变地使用。

    • 7

  3. 我经常遇到类似的问题来比较表的两个版本以查找新的、删除的或更改的行。一个月前，我在这里发布了一个使用 PowerShell 的 SQL Server 解决方案。

    为了适应您的问题，我首先创建两个视图以将原始行与重复行分开

    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_';

    然后我检查更改

    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;

    从这里我可以找到你的原始身份证

    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;

    顺便说一句：MINUS 和 UNION 和 GROUP BY 将不同的 NULL 视为相等。使用这些操作使查询更加优雅。

    给 SQL Server 用户的提示：MINUS 在此处命名为 EXCEPT，但工作方式类似。

    • 4

相关问题