oracle 在PLSQL中从一个CLOB列存储多行元素列表

我有两个表，TableA和TableB。
表A有5列：

create table tableA
(
    Job_id varchar2(50),
    Job_type varchar2(50),
    Employee_list clob,
    current_date date,
    salary varchar2(15)
);

字符串
Job_ID、Job_Type、Employee_list、Current_Date和Salary

的数据
这里 employee_list 是clob数据类型。
我想从表A创建另一个表B，它将有4列：

create table tableB
(
    Job_id varchar2(50),
    Employee varchar2(20),
    current_date date,
    salary varchar2(15)
);

型
Job_ID、Employee、Current_Date和Salary

的
我添加了一个新列'Employee'来存储单个雇员的姓名。问题是查询执行的时间非常长。
我的做法如下：

INSERT INTO tableB (job_id, employee, current_date,salary)
SELECT job_id,
       REGEXP_SUBSTR(employee_list, '[^;]+', 1, LEVEL) as employee, current_date,salary
FROM (
select job_id,employee_list,current_date,salary from
tableA where 
job_type = 'Managerial' and current_date = sysdate  
)
CONNECT BY job_id = PRIOR job_id AND PRIOR SYS_GUID() IS NOT NULL
  AND LEVEL <= REGEXP_COUNT(employee_list, '[^;]+');

型
程序持续运行了几个小时，但没有解决方案。请注意，employee_list包括大于4000个字符的字符串。

正则表达式比简单的字符串函数慢。
为每一行生成GUID的开销很大。
关联这样的分层表将生成大量重复的行，这些行需要通过CONNECT BY子句过滤掉，随着行数的增加，CONNECT BY子句的开销将呈指数级增加。

相反，您可以使用简单的字符串函数和递归查询：

INSERT INTO tableb (job_id, employee, current_date, salary)
WITH bounds (job_id, employee_list, current_date, salary, spos, epos) AS (
  SELECT job_id,
         employee_list,
         a.current_date,
         salary,
         1,
         INSTR(employee_list, ',', 1)
  FROM   tablea a
  -- WHERE  job_type = 'Managerial'
  -- AND    a.current_date = sysdate
UNION ALL
  SELECT job_id,
         employee_list,
         b.current_date,
         salary,
         epos + 1,
         INSTR(employee_list, ',', epos + 1)
  FROM   bounds b
  WHERE  epos > 0
)
SEARCH DEPTH FIRST BY job_id SET order_id
SELECT job_id,
       TRIM(
         CASE epos
         WHEN 0
         THEN SUBSTR(employee_list, spos)
         ELSE SUBSTR(employee_list, spos, epos - spos)
         END
       ),
       b.current_date,
       salary
FROM   bounds b;

字符串

注意：current_date是一个内置函数，虽然您可以 * 调用列current_date，但可能不建议这样做，因为您需要始终使用表名或别名作为列名的前缀，否则函数值将优先于列值返回。帮你自己一个忙，通过调用列而不是内置函数的名称来简化事情。

其中，对于样本数据：

create table tableA
(
    Job_id varchar2(50),
    Job_type varchar2(50),
    Employee_list clob,
    current_date date,
    salary varchar2(15)
);

create table tableB
(
    Job_id varchar2(50),
    Employee varchar2(20),
    current_date date,
    salary varchar2(15)
); 

INSERT INTO tableA (job_id, job_type, employee_list, current_date, salary)
SELECT  10102, 'Non Managerial', 'Steven, Bob', DATE '1999-01-01', 2e4 FROM DUAL UNION ALL
SELECT 102033, 'Managerial', 'David, Charlie, Keren', DATE '1999-01-02', 3e4 FROM DUAL;

型
在INSERT之后，tableb将包含：
| 员工|当前_日期|薪资| SALARY |
| --|--|--| ------------ |
| 史蒂文|1999-01-01 00：00：00|两万| 20000 |
| 鲍勃|1999-01-01 00：00：00|两万| 20000 |
| 大卫|2019 -01- 22 00：00：00|三万| 30000 |
| 查理|2019 -01- 22 00：00：00|三万| 30000 |
| 克伦|2019 -01- 22 00：00：00|三万| 30000 |
您还可以用途：

INSERT INTO tableB (job_id, employee, current_date,salary)
SELECT a.job_id,
       TRIM(REGEXP_SUBSTR(a.employee_list, '[^,]+', 1, l.idx)),
       a.current_date,
       a.salary
FROM   tableA a
       CROSS APPLY(
         SELECT LEVEL AS idx
         FROM   DUAL
         CONNECT BY LEVEL <= LENGTH(employee_list) - LENGTH(REPLACE(employee_list,',')) + 1
       ) l
--WHERE  a.job_type = 'Managerial'
--AND    a.current_date = sysdate;

型
但recursive query is probably still going to be more efficient（即使它是更多的类型）。
fiddle的

oracle 在PLSQL中从一个CLOB列存储多行元素列表

1条答案

相关问题

热门标签

最新问答