问题描述
嗨，汤姆，
我有带有存储所有电子邮件id的列id的表格，现在我通过添加一个列作为用户id来更改表格。
我希望所有用户名都存储在userid列中。我已经编写了没有被编译的代码。
声明
类型aat是由pls_integer组成的电子邮件 % rowtype索引的表;
var aat;
开始
选择 * 从电子邮件批量收集到var;
forall i in 1 .. var.last
插入电子邮件 (userid) 从电子邮件中选择substr(var(i).id，1，instr(var(i).id，'@'，1,1)-1);
结束;
/
我有带有存储所有电子邮件id的列id的表格，现在我通过添加一个列作为用户id来更改表格。
我希望所有用户名都存储在userid列中。我已经编写了没有被编译的代码。
声明
类型aat是由pls_integer组成的电子邮件 % rowtype索引的表;
var aat;
开始
选择 * 从电子邮件批量收集到var;
forall i in 1 .. var.last
插入电子邮件 (userid) 从电子邮件中选择substr(var(i).id，1，instr(var(i).id，'@'，1,1)-1);
结束;
/
专家解答
感谢您为我们提供了一个livesql测试用例-使我们的生活更加轻松。
你非常接近-你实际上不需要PL/SQL来做到这一点，所以我提供了一个SQL解决方案和一个PL/SQL (如果你只是想了解forall)
你非常接近-你实际上不需要PL/SQL来做到这一点，所以我提供了一个SQL解决方案和一个PL/SQL (如果你只是想了解forall)
SQL> create table email (id varchar2(30));
Table created.
SQL> insert into email values('gajanan1@gmail.com');
1 row created.
SQL> insert into email values('gajanan1@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> insert into email values('gajanana@gmail.com');
1 row created.
SQL> alter table email add userid varchar2(30);
Table altered.
SQL>
SQL>
SQL> desc email
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
ID VARCHAR2(30)
USERID VARCHAR2(30)
SQL>
SQL> update email
2 set userid=substr(id,1,instr(id,'@',1,1)-1);
55 rows updated.
SQL>
SQL> select * from email;
ID USERID
------------------------------ ------------------------------
gajanan1@gmail.com gajanan1
gajanan1@gmail.com gajanan1
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
gajanana@gmail.com gajanana
55 rows selected.
SQL>
SQL> declare
2 type aat is table of email%rowtype index by pls_integer;
3 var aat;
4 begin
5 select * bulk collect into var from email;
6 forall i in 1..var.last
7 update email set userid = substr(var(i).id,1,instr(var(i).id,'@',1,1)-1)
8 where id = var(i).id;
9 end;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL>
SQL>
SQL>
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
