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'
);
To initialize a nested table or varray, you use a constructor, a system-defined function with the same name as the collection type. This function "constructs" collections from the elements passed to it. You must explicitly call a constructor for each varray and nested table variable.
Initializing Variables in PL/SQLcounter binary_integer := 0; greetings varchar2(20) DEFAULT 'Have a Good Day'; You can also specify that a variable should not have a NULL value using the NOT NULL constraint. If you use the NOT NULL constraint, you must explicitly assign an initial value for that variable.
A record type is a composite data type that consists of one or more identifiers and their corresponding data types. You can create user-defined record types by using the TYPE IS RECORD statement within a package or by using the CREATE TYPE (Object) statement.
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;
No, there is not. You have to assign each value explicitly. Documentation reference here.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With