oracle 初始化 pl/sql 记录类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8041411/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Initialising a pl/sql record type
提问by ziggy
In PL/SQL, a varraycan be initialised at creation time as:
在 PL/SQL 中， avarray可以在创建时初始化为：
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?
PL/SQL 记录类型是否有等效的初始化方法？
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'
);
采纳答案by DCookie
No, there is not. You have to assign each value explicitly. Documentation reference here.
不，那里没有。您必须明确分配每个值。文档参考在这里。
回答by im_chc
Use a function to act as a kind of "constructor" function (look at function f()):
使用函数充当一种“构造函数”（查看函数 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;
回答by APC
Record types are really designed for holding rows from SELECT statements.
记录类型实际上是为保存 SELECT 语句中的行而设计的。
....
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 %ROWTYPEinstead.
显然，对于查询不是 SELECT * FROM 单个表的情况（因为在这种情况下我们可以%ROWTYPE改用。
回答by koksmaster
Record initialization is performed in its declaration and record assignment by selecting into from DUAL:
通过从 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;
/
回答by Jon Heller
Oracle 18c allows record initialization with qualified expressions:
Oracle 18c 允许使用限定表达式初始化记录：
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.)
您可以在此处在 Oracle Live SQL 中运行上述示例代码。（不幸的是，该站点需要登录。）
回答by eifla001
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;
