替换电子邮件域 [英] Replacing Email Domains

问题描述

我正在尝试将输入的域名更改为新域名，例如，"hotmail.com"变为"outlook.com".我相信我在正确的轨道上，只是我的子字符串关闭了.

I'm trying to change the inputted domain name to a new domain name, for example, "hotmail.com" becomes "outlook.com". I believe I'm on the right track, just my substring is off.

CREATE OR REPLACE PROCEDURE PR_ChangeDomain
(P_Email_Address varchar2)
IS
Cursor C_Email IS
  Select Email_Address, Broker_Number
  From Customer
  Where Email_Address = P_Email_Address;

  V_Email varchar2(50);

BEGIN
  Open C_Email;
  Fetch C_Email into V_Email;

  While C_Email%FOUND LOOP 
  Update Customer 
  Set Email_Address = SUBSTR((Email_Address), V_Email)
  Where Email_Address = P_Email_Address; 

  Fetch C_Email into V_Email;
  End Loop;
  Close C_Email;
  End PR_ChangeDomain;
  /

推荐答案

您编写的代码没有多大意义；过多的提取操作不起作用(将两个列合并到一个变量中?).

Code you wrote doesn't make much sense; too much fetching which won't work (two columns into a single variable?).

这是一个例子:一个测试表:

Here's an example: a test table:

SQL> create table test (email varchar2(30));

Table created.

SQL> insert into test
  2    select '[e-mail]' from dual union all
  3    select '[e-mail]' from dual union all
  4    select '[e-mail]' from dual union all
  5    select '[e-mail]' from dual;

4 rows created.

如何从中拆分域部分(以下SELECT的第二列)，并将电子邮件地址更新到新域(第三列):

How to split domain part from it (2nd column of the following SELECT) and update e-mail addresses to a new domain (3rd column):

SQL> select email,
  2    substr(email, instr(email, '@') + 1) domain,
  3    replace(email,
  4            substr(email, instr(email, '@') + 1),
  5            'new_domain.com'
  6           ) result
  7  from test;

EMAIL                     DOMAIN          RESULT
------------------------- --------------- -------------------------
[e-mail]            hotmail.com     lf@new_domain.com
[e-mail]            net.hr          bigfoot@new_domain.com
[e-mail]           gmail.com       stack@new_domain.com
[e-mail]        gmail.com       overflow@new_domain.com

SQL>

让我们仅将Gmail电子邮件地址更新为新域:

Let's update only Gmail e-mail addresses to a new domain:

SQL> update test set
  2    email = replace(email,
  3                    substr(email, instr(email, '@') + 1),
  4                    'new_domain.com'
  5                   )
  6  where substr(email, instr(email, '@') + 1) = 'gmail.com';

2 rows updated.

SQL> select * From test;

EMAIL
-------------------------
[e-mail]
[e-mail]
stack@new_domain.com
overflow@new_domain.com

SQL>

如果要将其转换为过程，没问题:

If you want to convert it to a procedure, no problem:

SQL> rollback;

Rollback complete.

SQL> create or replace procedure p_change_domain
  2    (par_old_domain in varchar2,
  3     par_new_domain in varchar2)
  4  is
  5  begin
  6    update test set
  7      email = replace(email,
  8                      substr(email, instr(email, '@') + 1),
  9                      par_new_domain
 10                     )
 11    where substr(email, instr(email, '@') + 1) = par_old_domain;
 12  end;
 13  /

Procedure created.

SQL> exec p_change_domain('gmail.com', 'new_domain_2.com');

PL/SQL procedure successfully completed.

SQL> select * From test;

EMAIL
-------------------------
[e-mail]
[e-mail]
stack@new_domain_2.com
overflow@new_domain_2.com

SQL>

如果您非常想使用游标(我不知道您为什么要这样做；它可能是效率最低的选择)，请执行以下操作:

If you desperately want to use cursors (I don't know why would you want to do that; it'll be probably the most inefficient option), here you go:

SQL> rollback;

Rollback complete.

SQL> create or replace procedure p_change_domain
  2    (par_old_domain in varchar2,
  3     par_new_domain in varchar2)
  4  is
  5  begin
  6    for cur_r in (select email from test
  7                  where substr(email, instr(email, '@') + 1) = par_old_domain
  8                 )
  9    loop
 10      update test set
 11        email = replace(email,
 12                        substr(email, instr(email, '@') + 1),
 13                        par_new_domain
 14                       )
 15        where email = cur_r.email;
 16    end loop;
 17  end;
 18  /

Procedure created.

SQL> exec p_change_domain('gmail.com', 'new_domain_3.com');

PL/SQL procedure successfully completed.

SQL> select * From test;

EMAIL
-------------------------
[e-mail]
[e-mail]
stack@new_domain_3.com
overflow@new_domain_3.com

SQL>

Cursor FOR循环比您尝试的要容易维护(创建游标和游标变量，打开游标，从中获取游标，注意退出循环，关闭游标).

Cursor FOR loop is easier to maintain than your attempt (creating a cursor and a cursor variable, opening the cursor, fetching from it, taking care about exiting the loop, closing the cursor).

但是，如果您不能没有它，那就去吧:

But, if you can't live without it, here you go:

SQL> rollback;

Rollback complete.

SQL> create or replace procedure p_change_domain
  2    (par_old_domain in varchar2,
  3     par_new_domain in varchar2)
  4  is
  5    cursor c1 is
  6      select email from test
  7      where substr(email, instr(email, '@') + 1) = par_old_domain;
  8    c1r c1%rowtype;
  9  begin
 10    open c1;
 11    loop
 12      fetch c1 into c1r;
 13      exit when c1%notfound;
 14
 15      update test set
 16        email = replace(email,
 17                        substr(email, instr(email, '@') + 1),
 18                        par_new_domain
 19                       )
 20        where email = c1r.email;
 21    end loop;
 22    close c1;
 23  end;
 24  /

Procedure created.

SQL> exec p_change_domain('gmail.com', 'new_domain_4.com');

PL/SQL procedure successfully completed.

SQL> select * From test;

EMAIL
-------------------------
[e-mail]
[e-mail]
stack@new_domain_4.com
overflow@new_domain_4.com

SQL>

我的建议?如果可能，请使用纯SQL.或第一个PL/SQL过程.请勿为此使用游标.

My suggestion? Use pure SQL, if possible. Or the first PL/SQL procedure. Don't use cursors for this purpose.

这篇关于替换电子邮件域的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！