在没有游标的选择查询中使用批量收集结果 [英] Use Bulk Collect result in a select query without cursor

问题描述

我是PL/SQL的新手，我想知道是否可以使用这样的批量收集结果:

I am new to PL/SQL and I was wondering if I can use result of a bulk collect like this:

Declare
type result_bulk_type is Table of table1.ID%type;
result_bulk result_bulk_type;
BEGIN
SELECT id BULK COLLECT INTO result_bulk FROM table1;
UPDATE table2 SET status=1 WHERE id IN result_bulk;
END;

编译时出现错误:

PL/SQL:忽略了SQL语句

PL/SQL: SQL statement ignored

PL/SQL:ORA-00932:数据类型不一致:预期的编号为SYS_PLSQL_22223_23_1

PL/SQL: ORA-00932: inconsistent datatypes: expected NUMBER got SYS_PLSQL_22223_23_1

感谢您的帮助！

推荐答案

否，不能以这种方式完成.请使用FORALL语句.
阅读此内容: http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/forall_statement.htm#LNPLS01321

一个例子:

No, it can't be done in this way. Use FORALL statement instead.
Read this: http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/forall_statement.htm#LNPLS01321

An example:

Declare
  type result_bulk_type is Table of table1.ID%type;
  result_bulk result_bulk_type;
BEGIN
  SELECT id BULK COLLECT INTO result_bulk FROM table1;
  FORALL i IN INDICES OF result_bulk
    UPDATE table2 SET status=1 WHERE id = result_bulk( i );
END;
/

演示: http://sqlfiddle.com/#!4/b3a72/1

这篇关于在没有游标的选择查询中使用批量收集结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！