CODEWITHSUNDEEP
sqloracle-databaseplsql
Lucian Tarna
Lucian Tarna

Reputation: 1827

How to insert multiple rows in a variable PL/SQL

I am trying to collect the member_id and how many different books that guy rented. Then i want this data put inside a variable so i can use it after . i am trying to do it like this 

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;
/

i want the data to be put in nr_imprumuturi but i get this error :

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:

The table record looks like this :

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

Upvotes: 0

Views: 6579

Answers (1)

Dmitriy
Dmitriy

Reputation: 5565

Probably you need nested table. Here is little example:

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;

This type you can use in different SQL statements, for details see documentation: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#LNPLS99981

Upvotes: 2

Related Questions