Reputation: 15876
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'
);
Upvotes: 8
Views: 23140
Reputation: 36922
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.)
Upvotes: 6
Reputation: 31
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;
/
Upvotes: 3
Reputation: 1157
You can create a function that return that record type.
See below sample code:
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');
v_loc_rec location_record_type;
FUNCTION new_loc_rec RETURN location_record_type
IS
v_new_loc_rec location_record_type;
BEGIN
return v_new_loc_rec;
END;
BEGIN
v_loc_rec := new_loc_rec;
v_loc_rec.state_province := 'SomeState';
v_loc_rec.country_id := 'SU';
dbms_output.put_line('State: '||v_loc_rec.state_province||'; Country_ID: '||v_loc_rec.country_id);
v_loc_rec := new_loc_rec;
dbms_output.put_line('State: '||v_loc_rec.state_province||'; Country_ID: '||v_loc_rec.country_id);
END;
Upvotes: 0
Reputation: 1075
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;
Upvotes: 9
Reputation: 146349
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.
Upvotes: 3
Reputation: 43553
No, there is not. You have to assign each value explicitly. Documentation reference here.
Upvotes: 4