如何替换电子邮件域名

Question

我正试图编写一个更新电子邮件域的过程(例如：转换[e-mail]进入[e-mail]当我传递2个字符串gmail.com和hotmail.com时(，同时在我的过程中有一个光标

create of replace procedure pr_update_email
(Old_Email Varchar2, New_Email Varchar2)
As
V_OldDomain Varchar2(50);
Cursor C_Domains IS Select Email_Address
From Customer
where Email_Address Like '%@'||Old_Email;       
Begin
Open C_Domains;
Fetch C_Domains INTO V_OldDomain; 
While C_Domains %Found loop
Update Customer
Set Email_Address = regexp_replace(Email_Address, '^(.*@.*)'||Old_Email||'1'||New_Email)
WHERE Email_Address LIKE V_OldDomain;   
Fetch C_Domains into New_Email;
End Loop;
Close C_Domains;
End pr_update_email;
/
Show Errors;

正在获取错误：19/11 PL/SQL:SQL语句已忽略

19/32 PLS-00403:表达式"NEW_EMAIL"不能用作SELECT/FETCH语句

Answer 1

您可以在不创建Stored Procedure的情况下直接更新，但可以使用Regexp_Replace()和Instr()函数：

SQL> Update Customer 
Set Email_Address = Regexp_Replace(Email_Address,'(.*)@[gmail]+.(.*)','1@hotmail.2')
Where Instr(Email_Address,'@gmail.') > 0;
SQL> Commit;

如果你想创建一个通用结构，以便也能用于其他情况，那么将上面的Update语句转换为：

SQL> Create Or Replace Procedure pr_update_email(Old_Email Varchar2, New_Email Varchar2) As
Begin
Update Customer
Set Email_Address = Regexp_Replace(Email_Address,'(.*)@['||Old_Email||']+.(.*)','1@'||New_Email||'.2')
Where Instr(Email_Address,'@'||Old_Email||'.')>0 ;
End;
/
SQL> Begin
pr_update_email('gmail','hotmail');
End;
/
SQL> Commit;

Answer 2

为什么要使用cursor？它可以使用简单的update语句来处理。

你的程序应该是这样的：

create procedure pr_update_email (p_oldemail in varchar2,
p_newemail in varchar2)
As
Begin
UPDATE BROKER
SET
EMAIL_ADDRESS = REPLACE(EMAIL_ADDRESS, p_oldemail, p_newemail)
WHERE
REGEXP_LIKE ( EMAIL_ADDRESS,'.*@'|| p_oldemail|| '$' );
Commit;
End;
/

或者，如果你真的想使用循环，那么

create of replace procedure pr_update_email
(Old_Email Varchar2, New_Email Varchar2)
As
Begin
For i in (Select Email_Address
From Customer
where Email_Address Like '%@'||Old_Email) loop
Update Customer
Set Email_Address = Replace(Email_Address, old_email, new_email)
WHERE Email_Address = i.Email_Address;   
End Loop;
Commit;
End pr_update_email;
/

要调用此过程，您需要按如下方式传递两个域：

Begin
pr_update_email('gmail.com', 'hotmail.com');
End;
/

是的，代码中的问题如下：

Fetch C_Domains into New_Email;

New_Email是一个输入参数，您不能为输入参数指定任何值。

干杯！！