预言机:过程/游标 - 更新，设置

Question

我一直在做课堂作业，由于某种原因，我唯一无法解决的是带有课程员的过程......有人可以给我提供指导吗？ 所以我需要获取电子邮件的域并更新它。示例：[e-mail] 进入 [e-mail]

规则需要游标：

CREATE OR REPLACE PROCEDURE PR_Q3
(P_Domain Varchar2)
AS
V_Old Varchar2(30);
V_New Varchar2(30);
EmailDomain Broker%ROWTYPE;
CURSOR DomainCursor IS SELECT Email_Address
FROM Broker
WHERE Email_Address = SUBSTR(Email_address,'@') -1;
BEGIN 
OPEN DomainCursor;
LOOP 
FETCH DomainCursor INTO V_Old;
EXIT WHEN DomainCursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(EmailDomain.Email_Address || ' , ' );
UPDATE Broker
SET Email_Address = V_New
WHERE Email_Address = SUBSTR(Email_Address, '@' ) - 1;
END LOOP;
CLOSE DomainCursor;
END PR_Q3;
/
SHOW ERROR;

EXEC('now.ca','gmail.com')

Answer 1

如果没有找到实际查询，请尝试这个匿名块：

Declare
V_Old Varchar2(30);
V_New Varchar2(30);
name_wo_domain Varchar2(30);
cursor DomainCursor is select Email_Address from Broker;
v_domain Varchar2(30) :='gmail.com';
BEGIN 
FOR i in DomainCursor
LOOP
select REGEXP_SUBSTR(i.Email_Address,'[^@]+',1,1) 
into name_wo_domain
from dual;
DBMS_OUTPUT.PUT_LINE(i.Email_Address || ' , ' );
UPDATE Broker
SET Email_Address = name_wo_domain||'@'||v_domain
WHERE Email_Address = i.Email_Address;
END LOOP;
COMMIT;
END ;