CODEWITHSUNDEEP
sqloracle-databaseplsql
user2541576
user2541576

Reputation: 67

where should we use PL/SQL Records in Oracle

what is the main use and purpose of PL/SQL Records, where should we use PL/SQL Records

    declare
  type locationRecordType is record (
   streetAddress       varchar2(40),
   postalCode          varchar2(12),
   city                 varchar2(30),
   stateProvince       varchar2(25),
   countryID           char(2) not null := 'US'
  );

 myLocation locationRecordType;
 begin
    myLocation.streetAddress := '4564 Culloden';
    myLocation.postalCode := '20190';
    myLocation.city := 'Regina';
    myLocation.stateProvince := 'SK';
    dbms_output.put_line( 'MY LOCATION IS:' );
    dbms_output.put_line( myLocation.streetAddress );
    dbms_output.put( myLocation.city||', '||myLocation.stateProvince )
    dbms_output.put_line( '  '||myLocation.postalCode );
    dbms_output.put_line( myLocation.countryID );
    end;

Upvotes: 1

Views: 462

Answers (1)

APC
APC

Reputation: 146249

PL/SQL records are complex variable data-types.

As you know, in PL/SQL our queries must SELECT into a variable. If the query is a SELECT * we can define a variable using the %ROWTYPE syntax, e.g.

emp_rec emp%rowtype;

If we are selecting a single column we just define the variable with the appropriate data type, e.g. for SELECT COUNT(*) it would be

emp_cnt pls_integer;

But suppose our query has a more complicated projection? Say if comprises only a few columns from a table? Or some columns from several tables joined in the query? In that case we would want to define a PL/SQL record.

Records should always populated with SELECTs, not assignments as you show. The other caveat is that we cannot use records in DML statements such as FORALL. If you need to do either of those things you probably ought to be using a SQL type instead.

Upvotes: 2

Related Questions