在oracle中构建动态查询

Question

你能帮我建立下面的查询吗？

TABLE_A

id /T1581-1997国际商品直销商品的商品价格、价格、商品价格、价格、商品价格、

1

2

3

4

TABLE_B

品牌再标准

A         color=grey和size=X

B         color=red

C         size=M

我想构建一个更新表A的查询，答案应该是：

TABLE_A

id /T1581-1997国际商品直销商品的商品价格、价格、商品价格、

1商品

2目的

3

4

正如你所看到的，列上的数据“标准”应该是购买或不购买的先例。

我想使用一个合并，如下所示

合并到TABLE_A a

使用

(

准则选择标识、品牌、案例，然后选择'Y‘否则'N’端yn_buy

水煤浆TABLE_A a

TABLE_B b ON a.brand = b.brand

(B)

ON (a.id = b.id)

匹配后，更新集a.yn_buy = b.yn_buy

有可能做这样的事吗？也许是立即执行，某种约束……？

谢谢

Answer 1

如果在您的特定情况下我没有遗漏一些东西，那么您就不需要动态SQL --您可以简单地更改TABLE_B结构并使用静态合并(因为我不知道您的标准可能有多复杂--这只是一个例子)：

SQL> create table table_a (id, brand, color, size#, model#, tn_buy) as
  2  select 1,    'A',          'blue',             'M',           'A', cast(null as varchar2(3)) from dual union all
  3  select 2,    'A',         'grey',              'X',          'C', cast(null as varchar2(3)) from dual union all
  4  select 3,    'B',         'red',               'X',           'B', cast(null as varchar2(3)) from dual union all
  5  select 4,    'C',        'blue',              'S',           'C', cast(null as varchar2(3)) from dual
  6  /

Table screated.

SQL> create table TABLE_B (brand, color, size#)
  2  as
  3  select 'A',          'grey', 'X' from dual union all
  4  select 'B',           'red', null from dual union all
  5  select 'C',          null, 'M' from dual
  6  /

Table created.

SQL> merge into TABLE_A a USING (
  2     select a.id, a.brand, CASE
  3     WHEN a.color = nvl(a.color, a.color) and a.size# = nvl(b.size#,a.size#)
  4     THEN 'Y' ELSE 'N' END yn_buy FROM
  5       TABLE_A a
  6       left join TABLE_B b ON a.brand = b.brand
  7  ) b
  8  ON (a.id = b.id)
  9  WHEN MATCHED THEN UPDATE set a.yn_buy = b.yn_buy
 10  /

4 rows merged.

SQL> select * from table_a order by id;

        ID B COLO S M YN_                                                       
---------- - ---- - - ---                                                       
         1 A blue M A N                                                         
         2 A grey X C Y                                                         
         3 B red  X B Y                                                         
         4 C blue S C N             

但是，您的条件很难使用简单的静态条件来实现，那么您可以使用动态SQL：

SQL> create table TABLE_B1 (brand, criteria)
  2  as
  3  select 'A',          q'[color='grey' and size# in ('X','M')]' from dual union all
  4  select 'B',           q'[color = 'red']' from dual union all
  5  select 'C',          q'[size#='M']' from dual
  6  /

Table created.

SQL> update table_a set yn_buy = null;

4 rows updated.

SQL> commit;

Committed.

SQL> begin
  2    for cur in (select brand, criteria from table_b1) loop
  3      execute immediate
  4      'update table_a set yn_buy = case when '||cur.criteria||
  5      q'[ then 'Y' else 'N' end where brand = :1]' using cur.brand;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure completed.

SQL> select * from table_a;

        ID B COLO S M YN_                                                       
---------- - ---- - - ---                                                       
         1 A blue M A N                                                         
         2 A grey X C Y                                                         
         3 B red  X B Y                                                         
         4 C blue S C N