时间戳

首页 > 解决方案 > 减少 pl/sql 的过载

oracle - 减少 pl/sql 的过载

问题描述

我需要一个一个地匹配几个属性。我希望避免使用多个选择语句。下面是示例。

    Table1
    Col1|Price|Brand|size
    -----------------------
    A|10$|BRAND1|SIZE1
    B|10$|BRAND1|SIZE1
    C|30$|BRAND2|SIZE2
    D|40$|BRAND2|SIZE4


    Table2
    Col1|Col2|Col3
    --------------
    B|XYZ|PQR
    C|ZZZ|YYY


    Table3
    Col1|COL2|COL3|LIKECOL1|Price|brand|size
    -----------------------------------------
    B|XYZ|PQR|A|10$|BRAND1|SIZE1
    C|ZZZ|YYY|D|NULL|BRAND2|NULL

在表 3 中，我需要通过检查以下条件从表 2 中插入数据。

  1. 查找表 2 中记录的匹配项，如果品牌和尺寸、价格匹配
  2. 如果未找到匹配项，则仅尝试品牌、尺寸
  3. 仍然找不到匹配项，仅尝试品牌

在上面的示例中，对于 table2 中的第一条记录，发现与所有 3 个属性匹配，因此插入到 table3 和第二条记录中，记录 'D' 匹配但只有 'Brand'。

我能想到的就是将 3 个不同的插入语句（如下所示）写入 oracle pl/sql 块。

 insert into table3 
   select from tab2 
    where all 3 attributes are matching;

 insert into table3 
   select from tab2 
    where brand and price are matching 
      and not exists in table3 (not exists is to avoid 
                                inserting the same record which was already 
                                inserted with all 3 attributes matched);

 insert into table3 
   select from tab2 
    where Brand is matching and not exists in table3;

任何人都可以建议一种更好的方法来以更好的方式实现它，避免多次从 table2 中选择。

标签： oracleplsqlquery-performance

解决方案

这是一个案例OUTER APPLY。

OUTER APPLY是一种横向连接，允许您连接引用出现在您的FROM子句前面的表的动态视图。使用该功能，您可以定义一个动态视图来查找所有匹配项，按照您指定的排序顺序对它们进行排序，然后使用FETCH FIRST 1 ROW ONLY仅在结果中包含第一个匹配项。

UsingOUTER APPLY意味着如果没有匹配，你仍然会得到表 B 的记录——只是所有的匹配列null。如果你不想这样，你可以更改OUTER APPLY为CROSS APPLY.

这是一个工作示例（带有分步注释），无耻地从 Michael Piankov 的回答中窃取表创建脚本：

create table Table1 (Col1,Price,Brand,size1)
as select 'A','10','BRAND1','SIZE1' from dual union all
   select 'B','10','BRAND1','SIZE1' from dual union all
   select 'C','30','BRAND2','SIZE2' from dual union all
   select 'D','40','BRAND2','SIZE4'from dual 

create table Table2(Col1,Col2,Col3)
as select 'B','XYZ','PQR' from dual union all 
   select'C','ZZZ','YYY' from dual;

-- INSERT INTO table3
SELECT t2.col1, t2.col2, t2.col3,
t1.col1 likecol1, 
decode(t1.price,t1_template.price,t1_template.price, null) price,
decode(t1.brand,t1_template.brand,t1_template.brand, null) brand,
decode(t1.size1,t1_template.size1,t1_template.size1, null) size1
FROM 
-- Start with table2
table2 t2
-- Get the row from table1 matching on col1... this is our search template
inner join table1 t1_template on
t1_template.col1 = t2.col1
-- Get the best match from table1 for our search 
-- template, excluding the search template itself
outer apply ( 
SELECT * FROM table1 t1 
WHERE 1=1
-- Exclude search template itself
and t1.col1 != t2.col1
-- All matches include BRAND
and t1.brand = t1_template.brand
-- order by match strength based on price and size
order by case when t1.price = t1_template.price and t1.size1 = t1_template.size1 THEN 1
when t1.size1 = t1_template.size1 THEN 2
else 3 END
-- Only get the best match for each row in T2
FETCH FIRST 1 ROW ONLY) t1;

推荐阅读