database - 使用 pl/sql 在表中查找多个值并在不存在时插入的过程

Question

我有这个表有这种关系:UNIT --> CHARGE ---> ROLE

UNIT_CODE    |    CHARGE_CODE    |    ROLE_CODE
0101010001   |     9023409       |    AZAAAA
0102010002   |     8023409       |    AXAAAB
0103010003   |     7023409       |    ACAAAC
0104010004   |     6023409       |    AVAAAV
0101010001   |     5023409       |    NEWROL
0102010002   |     4023409       |    AZAAAA

这个表有将近 2.000 行/行。

所以，情况是这样的:

我必须为特定的 90 unit_code 插入特定的 ROLE_COD (NEWROL)。但此角色适用于特定负责的单位。

示例:我必须为这个单位 0101010001 和这个 插入role_code = NEWROLE充电 5023409

• 所以，我必须检查:

  1. 如果单元 0101010001 已经存在？ - 是的
  2. 如果 unit/charge 0101010001/5023409 关系已经存在？ - 是的
  3. 如果 unit/charge/rol 0101010001/5023409/NEWROL 已经存在？ - 是的

如果 3 个问题的答案是肯定的，我必须检查其他单位/费用组合:

0102010002/4023409

1. 如果 units 0102010002 已经存在？ - 是的
2. 如果 unit/charge 0102010002/4023409 关系已经存在？ - 是的
3. 如果 unit/charge/rol 0102010002/4023409/NEWROL 已经存在？ - 没有

所以我必须在表中插入角色:NEWROL 对于这个单位/费用并检查其他单位和费用。

注意:单位是独一无二的，但有些单位具有相同/多个角色或职责。

我必须检查 90 个单位，我需要一个程序来获取 unit_code 和 charge 进行验证并插入 role_code 如果必要的。

Answer 1

这是一个纯 SQL 解决方案:

insert into your_table t
select unit_code, charge_code, 'NEWROL'
from 
    ( select unit_code, charge_code from your_table
      where (unit_code = '0101010001' and charge_code = 5023409)
      or    (unit_code = '0102010002' and charge_code = 4023409)
      minus
      select unit_code, charge_code from your_table
      where  role_code = 'NEWROL' )
/

子查询返回没有 role_code='NEWROL' 的 (unit_code, charge_code) 集合。如果您确实需要存储过程，您可以调整上面的 WHERE 子句以使用 PL/SQL 参数。

---

这是一个程序化此查询的解决方案。它使用 SQL 类型传递目标 (unit_code, charge_code) 对:

create or replace type unit_charge_t as object
    (unit_code varchar2(10), charge_code number);
/
create or replace type unit_charge_nt as table of unit_charge_t;
/

这是一个简单的概念证明。您需要添加日志记录、异常处理等。

create or replace procedure ins_your_table
    (p_recs in unit_charge_nt
     , p_new_role in your_table.role_code%type := 'NEWROL')
as
begin
    insert into your_table t
    select unit_code, charge_code, 'NEWROL'
    from 
        ( select unit_code, charge_code from your_table
          where (unit_code, charge_code) in (select * from table(p_recs))
    minus
    select unit_code, charge_code from your_table
    where  role_code = 'NEWROL' );
    dbms_output.put_line('inserted records = '||sql%rowcount);
end ins_your_table;
/

调用示例:

SQL> set serveroutput on
SQL> declare 
  2      tgt_rows unit_charge_nt  
  3          := unit_charge_nt ( unit_charge_t('0101010001', 5023409)
  4                              , unit_charge_t('0102010002', 4023409) );
  5  begin
  6      ins_your_table(tgt_rows);   
  7  end;
  8  /
inserted records = 1

PL/SQL procedure successfully completed.

SQL> 

最大的挑战仍然是将文档中的目标键输入到数据库中。我个人会选择程序员的文本编辑器，例如 NotePad++。这将使使用正则表达式搜索'n'替换将值列表转换为那些类型赋值变得轻而易举。

search: ([0-9]+)\t([0-9]+)
replace: unit_charge_t\('\1', \z\)

另一种接口(interface)是使用外部表，将 .docx 更改为 .csv 文件。

有许多可能的解决方案；哪种最合适取决于您的具体情况。