oracle 在 PL/SQL 中的记录字段上使用 %TYPE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/823612/
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
Using %TYPE on a record field in PL/SQL
提问by maxyfc
This has been driving me crazy for a while:
这让我发疯了一段时间：
DECLARE
TYPE AttrValueRec IS RECORD (
attr VARCHAR2(40),
val VARCHAR2(2000),
inst NUMBER(4)
);
FUNCTION create_attrval(attr AttrValueRec.attr%TYPE,
val AttrValueRec.val%TYPE,
inst AttrValueRec.inst%TYPE := 1)
RETURN AttrValueRec IS
attr_value AttrValueRec;
BEGIN
attr_value.attr := attr;
attr_value.val := val;
attr_value.inst := inst;
RETURN attr_value;
END;
BEGIN
NULL;
END;
Using %TYPEon a record field does not seem to work. It produces the following error:
%TYPE在记录字段上使用似乎不起作用。它产生以下错误：
ORA-06550: line 8, column 36:
PLS-00206: %TYPE must be applied to a variable, column, field or attribute, not to "ATTRVALUEREC.ATTR"
ORA-06550: line 8, column 5:
PL/SQL: Item ignored
While explicitly defining the type again works:
虽然明确定义类型再次有效：
DECLARE
TYPE AttrValueRec IS RECORD (
attr VARCHAR2(40),
val VARCHAR2(2000),
inst NUMBER(4)
);
FUNCTION create_attrval(attr VARCHAR2,
val VARCHAR2,
inst NUMBER := 1)
RETURN AttrValueRec IS
attr_value AttrValueRec;
BEGIN
attr_value.attr := attr;
attr_value.val := val;
attr_value.inst := inst;
RETURN attr_value;
END;
BEGIN
NULL;
END;
Can someone explain to me why it doesn't work? Is there a way to refer to the type declared in the record definition instead of explicitly defining it again in the function?
有人可以向我解释为什么它不起作用吗？有没有办法引用记录定义中声明的类型，而不是在函数中再次明确定义它？
Thanks.
谢谢。
采纳答案by drnk
look at documentation. %TYPE and %ROWTYPE - only use to refer database columns. but you try to make referer to user type.
查看文档。%TYPE 和 %ROWTYPE - 仅用于引用数据库列。但是您尝试将引用指向用户类型。
solution is define your pl/sql type with %TYPE-referer on a database column, and then create function with parameters that refer to the same database column.
解决方案是在数据库列上使用 %TYPE-referer 定义您的 pl/sql 类型，然后使用引用同一数据库列的参数创建函数。
UPDATE
更新
its not full truth because lead commentator post usefull idea. summary %TYPE and %ROWTYPE can refer not only to table columns. refer ot "real" objects like variables and cursors are good too.
这并不完全是事实，因为首席评论员发表了有用的想法。摘要 %TYPE 和 %ROWTYPE 不仅可以引用表列。引用像变量和游标这样的“真实”对象也很好。
回答by David
You need to actually create a variable of your type to refer to the attributes.
您需要实际创建一个您类型的变量来引用属性。
Add this after your type declaration and before the function.
在类型声明之后和函数之前添加它。
attrib_value AttribValueRec;
Then in your function header you can reference the type of the attributes in your function like this:
然后在您的函数标头中，您可以像这样引用函数中的属性类型：
attr attrib_value.attr%TYPE;
