Initialising a pl/sql record type
Solution 1
No, there is not. You have to assign each value explicitly. Documentation reference here.
Solution 2
Use a function to act as a kind of "constructor" function (look at function f()):
DECLARE
TYPE ty_emp IS RECORD(
id INTEGER,
name VARCHAR(30),
deptcode VARCHAR(10)
);
TYPE ty_tbl_emp IS TABLE OF ty_emp;
tbl_emp ty_tbl_emp;
FUNCTION f ( -- <==============
id INTEGER,
name VARCHAR,
deptcode VARCHAR) RETURN ty_emp IS
e ty_emp;
BEGIN
e.id := id;
e.name := name;
e.deptcode := deptcode;
RETURN e;
END f;
BEGIN
tbl_emp := ty_tbl_emp(
f(1, 'Johnson', 'SALES'),
f(2, 'Peterson', 'ADMIN'));
Dbms_Output.put_line(tbl_emp(2).name);
END;
Solution 3
Oracle 18c allows record initialization with qualified expressions:
declare
type location_record_type is record (
street_address varchar2(40),
postal_code varchar2(12),
city varchar2(30),
state_province varchar2(25),
country_id char(2) not null := 'US'
);
myvar location_record_type;
myvar2 location_record_type := location_record_type(street_address => 'my street'
,postal_code => 'my code'
,city => 'my city'
,state_province => 'my state'
,country_id => 'GB'
);
begin
dbms_output.put_line(myvar.country_id);
dbms_output.put_line(myvar2.city);
end;
/
Output of the above is ...
US
my city
You can run the above sample code in Oracle Live SQL here. (Unfortunately that site requires a logon.)
Solution 4
Record types are really designed for holding rows from SELECT statements.
....
type location_record_type is record (
street_address varchar2(40),
postal_code varchar2(12),
city varchar2(30),
state_province varchar2(25),
country_id char(2) not null := 'US'
);
type location_record_nt is table of location_record_type;
loc_recs location_record_nt;
begin
select street_name
, pcode
, city
, region
, country_code
bulk collect into loc_recs
from t69
where ....
Obviously for cases where the query isn't a SELECT * FROM a single table (because in that scenario we can use %ROWTYPE
instead.
Solution 5
Record initialization is performed in its declaration and record assignment by selecting into from DUAL:
declare
type location_record_type is record
(
street_address varchar2(40) := '1234 Fake Street',
postal_code varchar2(12) := '90210',
city varchar2(30) := 'Springfield',
state_province varchar2(25) := 'KY',
country_id char(2) not null := 'US'
);
v_location location_record_type;
begin
select
'4321 Another St.', '48288', 'Detroit', 'MI', v_location.country_id
into v_location from dual;
end;
/
ziggy
Updated on July 29, 2022Comments
-
ziggy almost 2 years
In PL/SQL, a
varray
can be initialised at creation time as:TYPE colour_tab IS VARRAY(3) OF VARCHAR2(20); french_colours colour_tab := colour_tab('RED','WHITE','BLUE');
Is there an equivalent method of initialisation for PL/SQL record types?
type location_record_type is record ( street_address varchar2(40), postal_code varchar2(12), city varchar2(30), state_province varchar2(25), country_id char(2) not null := 'US' );
-
im_chc over 9 yearsSee my answer at stackoverflow.com/a/28208606/214728, this should work similar to the colour_tab := colour_tab('RED','WHITE','BLUE') codings