CODEWITHSUNDEEP
sqlselectdata-structuresplsqlrecord
Tomás
Tomás

Reputation: 97

PL/SQL simple select into record variable

I have declared a record variable:

Declare
TYPE WORK_REC IS RECORD(
STRING1  VARCHAR (21),  
NUMBER1  NUMBER  (05)
);
TARGET_REC WORK_REC;
...

At certain point I get two values that I have to move to my record variable. Is it possible to use a single SELECT INTO like this?

SELECT (<PROPER STRING VALUE> , <PROPER NUMBER VALUE> ) INTO TARGET_REC

The point here is I'd like to fill my record in a simple maintaining way.

Upvotes: 3

Views: 10594

Answers (2)

XING
XING

Reputation: 9886

Comment 1:

At certain point I get two values that I have to move to my record variable.

SELECT ( , ) INTO TARGET_REC

Comment 2:

Good, but my values didn't come from a table - they are simple variables that I got from string operations or arithmetic operations.

If I could understand from the above 2 comments, you want to fill the collection with string value and then use the Collection in a Select Statement to fill another collection variable.

Of-course you can fill the collection using string as shown below, however you there is no need of a SELECT statement to move the value of already populated collection to another. See below demo & read inline comments to understand more.

declare 
  type work_rec is record(
                         string1  varchar2 (21),  
                         number1  number  (05)
                         );

  type target_rec is table of work_rec index by pls_integer;

   v_target_rec  target_rec;

   var   target_rec;

begin   
   -- Assiging values to the collection elements more than once
   v_target_rec(1).string1 := 'ABC'; --<-- first Element
   v_target_rec(1).number1 := 2;    

   v_target_rec(2).string1 := 'CDE'; --<-- Second Element
   v_target_rec(2).number1 := 4;

   --Displaying record elements
   for rec in 1..v_target_rec.count
   Loop
      dbms_output.put_line( 'rec=(' || v_target_rec(rec).string1 || ',' || v_target_rec(rec).number1 || ')' );
   End loop; 

   /* This part is simply reduced by using assignment operator below***
   SELECT (<PROPER STRING VALUE> , <PROPER NUMBER VALUE> ) INTO TARGET_REC 
   */

   var := v_target_rec; --<--replacement of Select Statement.

   for rec in 1..var.count
   Loop
      dbms_output.put_line( 'New-rec=(' || var(rec).string1 || ',' || var(rec).number1 || ')' );
   End loop; 

End;

Upvotes: 2

Belayer
Belayer

Reputation: 14936

Yes, you can do that. As long as the columns selected matches the target variables in both number and type. 

create table rec_test( s1 varchar2(21), n1 number(5));
insert into rec_test (s1,n1) values('ABC',2) ;

declare 
  type work_rec is record(
       string1  varchar2 (21),  
       number1  number  (05)
       );
  target_rec work_rec;
begin
  select s1, n1
    into target_rec 
    from rec_test;

  dbms_output.put_line( 'rec=(' || target_rec.string1 || ',' || target_rec.number1 || ')' ); 
end ;

Upvotes: 6

Related Questions