Oracle 散装插入使用forall

嗨，汤姆，


我有带有存储所有电子邮件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)

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>
复制