使用游标更新Oracle SQL

Question

我有一个关于如何使用光标通过使用新的电子邮件域名来更新电子邮件域名的问题。例如(gmail.com->hotmail.com)

执行procedure('gmail.com','hotmail.com' )

这是我写的。请帮帮我，谢谢！

create procedure PR_Q3
is P_NewEamil varchar2(50); P_Email_Address varchar2(50); exceptionforemail exception;
cursor E_info is select Email_Address from Broker where P_Email_Address = Email_Address
for update of Email_Address;
begin 
open E_info;
fetch E_info into P_NewEamil;
while E_info%found loop 
if(P_NewEamil like '%.com') then 
update Broker set Email_Address = P_NewEamil where P_Email_Address = Email_Address;

Answer 1

使用简单的update语句就可以完成时，为什么要使用游标，如下所示：

UPDATE BROKER
SET
    EMAIL_ADDRESS = REPLACE(P_NEWEAMIL, :OLD_DOMAIN, :NEW_DOMAIN)
WHERE
    REGEXP_LIKE ( P_NEWEAMIL,'.*@'|| :OLD_DOMAIN|| '$' );

请根据您的需求添加更多WHERE条件。

干杯！！