拼接email

题目是这样的：

假设给你一个表 test（name， email）。有些记录email 为null，有些有值，现要求为没有值的记录分配一个email 格式为：

name@gmail.com

SQL> ed Wrote file afiedt.buf 1 CREATE TABLE testCursor ( 2 name varchar2(20), 3 email varchar2(40) 4* ) SQL> / Table created. SQL> @ insert.sql 1 row created. 1 row created. 1 row created. 1 row created. SQL> ed Wrote file afiedt.buf 1* SELECT * FROM testCursor SQL> / NAME EMAIL -------------------- ---------------------------------------- Ann Ben MaoWeiting MaoWeiting@gmail.com mingchaoyan mingchaoyan@gmail.com SQL> ed Wrote file afiedt.buf 1 UPDATE testCursor 2 SET email = name || '@gmail.com' 3* WHERE email is null SQL> / 2 rows updated. SQL> ed Wrote file afiedt.buf 1* SELECT * FROM testCursor SQL> / NAME EMAIL -------------------- ---------------------------------------- Ann Ann@gmail.com Ben Ben@gmail.com MaoWeiting MaoWeiting@gmail.com mingchaoyan mingchaoyan@gmail.com SQL> rollback 2 / Rollback complete. SQL> @ insert.sql 1 row created. 1 row created. 1 row created. 1 row created. SQL> ed Wrote file afiedt.buf 1 UPDATE testCursor 2 SET email = concat(name,'@gmail.com') 3* WHERE email is null SQL> / 2 rows updated. SQL> ed Wrote file afiedt.buf 1* SELECT * FROM testCursor SQL> / NAME EMAIL -------------------- ---------------------------------------- Ann Ann@gmail.com Ben Ben@gmail.com MaoWeiting MaoWeiting@gmail.com mingchaoyan mingchaoyan@gmail.com SQL> rollback 2 / Rollback complete. SQL> @ insert.sql 1 row created. 1 row created. 1 row created. 1 row created. SQL> ed Wrote file afiedt.buf 1 DECLARE 2 cursor mycursor is 3 SELECT * FROM testCursor 4 WHERE email is null 5 FOR UPDATE of email; 6 BEGIN 7 FOR r IN mycursor loop 8 UPDATE testCursor 9 SET email = name || '@gmail.com' 10 WHERE current of mycursor; 11 END loop; 12* END; SQL> / PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1* SELECT * FROM testCursor SQL> / NAME EMAIL -------------------- ---------------------------------------- Ann Ann@gmail.com Ben Ben@gmail.com MaoWeiting MaoWeiting@gmail.com mingchaoyan mingchaoyan@gmail.com SQL> spool off

其中insert.sql为

INSERT INTO testCursor VALUES('Ann',null); INSERT INTO testCursor VALUES('Ben',null); INSERT INTO testCursor VALUES('MaoWeiting','MaoWeiting@gmail.com'); INSERT INTO testCursor VALUES('mingchaoyan','mingchaoyan@gmail.com');

PS：蠢到家了，一个直接可以用update搞定的语句，硬是用了游标。

还是听听头脑清醒呀~