oracle explicit cursor loop

Question

I want to replace the domain name using cursor without a loop, which is an explicit cursor. but I also want to changes all the same domain names in the database by given pass a string. example: exec PR_Q3( 'usa.com','hotmail.com'); all the domain name with 'usa.com'in database will change to 'hotmail.com'.

create or replace procedure PR_Q3
(old_email in varchar2, new_email in varchar2)
authid current_user
is 
cursor E_info is select Email_Address from broker
where REGEXP_LIKE (substr(Email_Address, instr(Email_Address,'@')+1), old_email);
v_email E_info%rowtype;
begin 
open E_info;
loop
fetch E_info into v_email;
exit when E_info%notfound;
update broker set 
Email_Address = replace(Email_Address,substr(Email_Address,instr(Email_Address,'@')+1),new_email) 
where Email_Address = v_email.Email_Address;
end loop;
close E_info;
end PR_Q3;

it works if I delete the loop, but it only changes the domain name once. I need changes all the same domain name. I want to do the same thing without a loop. Can I?

Popeye · Accepted Answer · 2020-02-26 06:25:27Z

2

You can use the following simple update in your procedure:

CREATE OR REPLACE PROCEDURE PR_Q3 (
    OLD_EMAIL   IN          VARCHAR2,
    NEW_EMAIL   IN          VARCHAR2
)
    AUTHID CURRENT_USER
IS
BEGIN
    UPDATE BROKER
    SET
        EMAIL_ADDRESS = REPLACE(EMAIL_ADDRESS, OLD_EMAIL, NEW_EMAIL)
    WHERE
        REGEXP_LIKE ( EMAIL_ADDRESS,
                      '.*@' || OLD_EMAIL || '$' );

    COMMIT;
END PR_Q3;
/

Cheers!!

edited Feb 26, 2020 at 6:25

answered Feb 26, 2020 at 6:19

Popeye

36k4 gold badges12 silver badges31 bronze badges

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

Collectives™ on Stack Overflow

oracle explicit cursor loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related