Oracle SQL Update using cursor

Question

I have a question about how to using a CURSOR to update an email domain name by using an new email domain name. ex(gmail.com->hotmail.com)

execute procedure('gmail.com','hotmail.com' )

Here is what I write. please help me out, thank you!

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;

Popeye · Accepted Answer · 2020-02-19 05:57:12Z

2

Why you want to use the cursor when it can be done using a simple update statement as following:

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

Please add more WHERE conditions according to your requirements.

Cheers!!

answered Feb 19, 2020 at 5:57

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Parco Guan Over a year ago

it has bad bind variable error with old_domain and new_domain

Popeye Over a year ago

Just replace old and new domain with your actual names. And remove : also

Collectives™ on Stack Overflow

Oracle SQL Update using cursor

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related