如何在变量PL / SQL中插入多行

露西安·塔纳（Lucian Tarna）

我正在尝试收集member_id以及该人租了多少本不同的书。然后，我希望将此数据放入变量中，以便以后使用。我正在尝试这样做

DECLARE

  nr_imprumuturi RECORD%ROWTYPE;
  nr_total_titluri Number(4);
  procent Number(3);
BEGIN

  select count(*) into nr_total_titluri
  from title;

  select count(distinct r.title_id),r.member_id bulk collect into nr_imprumuturi
  from member m, rental r
  group by r.member_id;

  select nr_imprumuturi.Nr_impr/nr_total_titluri *100 into procent
  from dual;






END;
/

我希望将数据放入nr_imprumuturi，但出现此错误：

Error report:
ORA-06550: line 11, column 67:
PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list
ORA-06550: line 12, column 3:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 11, column 3:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

该表记录如下所示：

create table record(
 nr_impr Number(3),
 member_id Number(3),
 procent Number(3)
)

德米特里

可能您需要嵌套表。这是个小例子：

set serveroutput on;

create table test (id number, str varchar2(100));

insert into test values(1, 'a');
insert into test values(2, 'b');
insert into test values(3, 'c');
insert into test values(4, 'd');

declare
  type test_table is table of test%rowtype index by binary_integer;
  a_table test_table;
begin
  select *
  bulk collect into a_table
  from test
  where id > 1;

  dbms_output.put_line('Collection size is ' || a_table.count()); 
end;
/

drop table test;

您可以在不同的SQL语句中使用此类型，有关详细信息，请参阅文档：http : //docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#LNPLS99981

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。