重写表更新为set而不是迭代方法

Question

我在测试Oracle数据库中有一个用户电子邮件地址列表，这些地址当前都设置为相同的值。我想用一些无效的地址和空值来替换这些条目。我的表目前看起来像这样，总共大约有250 k行(为了节省一些空间，我已经排除了空项和无效条目)。

+-------------+--------------------+
| employee_id |       email        |
+-------------+--------------------+
|           1 | test@testemail.com |
|           2 | test@testemail.com |
|           3 | test@testemail.com |
|...          |...                 |
+-------------+--------------------+

我希望它看起来像这样

+-------------+---------------------+
| employee_id |        email        |
+-------------+---------------------+
|           1 | test1@testemail.com |
|           2 | test2@testemail.com |
|           3 | test3@testemail.com |
|...          |...                  |
+-------------+---------------------+

我编写了下面的PL/SQL来进行更改，它可以工作，但是它看起来效率很低。我可以使用另一种方法来利用set处理吗？

谢谢你在这方面的帮助。

DECLARE
    i number(20);
    l_employee_id hr.employees.employee_id%TYPE;
    output_query varchar2(1000);
    CURSOR c_cursor IS
        SELECT employee_id
        FROM hr.employees;
PROCEDURE update_sql(id_num IN hr.employees.employee_id%TYPE, 
                     email IN VARCHAR2) IS
BEGIN
    output_query := 'UPDATE hr.employees
                    SET email = '''|| email ||'''
                    WHERE employee_id = '|| id_num;
                    dbms_output.put_line(output_query); --for debug
    EXECUTE IMMEDIATE output_query;
END;
BEGIN
    OPEN c_cursor;
    i := 1;
    <<outer_loop>>
    LOOP
        For j IN 1..5 LOOP
            FETCH c_cursor INTO l_employee_id;
            EXIT outer_loop WHEN c_cursor%NOTFOUND;
            IF j <= 3 THEN
                update_sql(l_employee_id, ('test' || i || '@testemail.com'));
            ELSIF j = 4 THEN
                update_sql(l_employee_id, ('test' || i || 'testemail.com'));
            ELSIF j = 5 THEN
                update_sql(l_employee_id, ' ');
            END IF;
            i := i + 1;
        END LOOP;
    END LOOP outer_loop;
    CLOSE c_cursor;
END;
/

编辑-2016年9月26日-澄清表格的大小。

Answer 1

首先，即使您打算迭代地编写此代码，请不要在不需要使用动态SQL的地方使用它。而且，只有当您不知道在编译时要查询的表或列时，才有必要。

尽管如此，听起来你只是想

UPDATE employees
   SET email = (case when employee_id <= 3
                     then 'test' || employee_id || '@testemail.com'
                     when employee_id = 4
                     then 'test' || employee_id || 'testemail.com'
                     when employee_id = 5
                     then ' '
                     else null
                  end)

Answer 2

 create table emp (emp_id number, email varchar2(32));

 insert into emp select level as emp_id, 'test@testemail.com' as email 
 from dual connect by level<=2500000;

 update emp set email = regexp_replace(email, '(\w+)(@\w+\.\w+)', '\1' || emp_id || '\2');
 --250,000 rows updated ~16 sec.

EMP_ID, EMAIL
1   test1@testemail.com
2   test2@testemail.com
3   test3@testemail.com
...

drop table emp;

Answer 3

Oracle安装

create table employees (
  id    NUMBER,
  email VARCHAR2(100)
);

INSERT INTO employees
SELECT 1, 'test@testemail.com' FROM DUAL UNION ALL
SELECT 2, 'test@testemail.com' FROM DUAL UNION ALL
SELECT 3, 'test@testemail.com' FROM DUAL UNION ALL
SELECT 4, 'test@testemail.com' FROM DUAL UNION ALL
SELECT 5, 'test@testemail.com' FROM DUAL UNION ALL
SELECT 6, 'test@testemail.com' FROM DUAL;

查询

update employees
set email = CASE MOD( ROWNUM, 5 )
            WHEN 4 THEN 'test' || ROWNUM || 'testemail.com'
            WHEN 0 THEN ''
                   ELSE 'test' || ROWNUM || '@testemail.com'
            END;

输出

SELECT * FROM employees;

        ID EMAIL
---------- ------------------------
         1 test1@testemail.com
         2 test2@testemail.com
         3 test3@testemail.com
         4 test4testemail.com
         5 
         6 test6@testemail.com