包定义中的 Oracle 用户定义类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8299089/
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
Oracle User defined type inside package definition
提问by llasarov
is it possible to have a User Defined Type inside Oracle package definition? When I try following
是否可以在 Oracle 包定义中包含用户定义类型？当我尝试跟随
CREATE OR REPLACE PACKAGE AF_CONTRACT AS -- spec
-- PROCEDURE my_rpcedure (emp_id NUMBER);
TYPE DTO_GRID AS OBJECT
(
ROWKEY NVARCHAR2(200),
COLUMNKEY NVARCHAR2(200),
CELLVALUE NVARCHAR2(200),
OLDVALUE NVARCHAR2(200),
TAG NVARCHAR2(200)
);
END AF_CONTRACT;
/
CREATE OR REPLACE PACKAGE BODY AF_CONTRACT AS -- body
-- PROCEDURE my_procedure (emp_id NUMBER) IS
-- BEGIN
--
-- END my_procedure;
END AF_CONTRACT;
I always get an error
我总是收到错误
Error: PLS-00540: object not supported in this context.
in the type definition.
在类型定义中。
采纳答案by Ollie
Object types have to be declared at the database level. You could declare a collection type in a package specification.
必须在数据库级别声明对象类型。您可以在包规范中声明集合类型。
Possible duplicate of: Possible to create Oracle Database object types inside of PL/SQL?
可能的重复项：是否 可以在 PL/SQL 中创建 Oracle 数据库对象类型？
回答by APC
No, it's not permitted:
不，这是不允许的：
SQL> CREATE OR REPLACE PACKAGE AF_CONTRACT AS -- spec
-- PROCEDURE my_rpcedure (emp_id NUMBER);
TYPE DTO_GRID AS OBJECT
(
ROWKEY NVARCHAR2(200),
COLUMNKEY NVARCHAR2(200),
CELLVALUE NVARCHAR2(200),
OLDVALUE NVARCHAR2(200),
TAG NVARCHAR2(200)
);
END AF_CONTRACT;
/
2 3 4 5 6 7 8 9 10 11 12
Warning: Package created with compilation errors.
SQL> SQL> sho err
Errors for PACKAGE AF_CONTRACT:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/4 PLS-00540: object not supported in this context.
SQL>
If you want to create a type which is just passing data between PL/SQL procedures then use the PL/SQL RECORD syntax:
如果你想创建一个只在 PL/SQL 过程之间传递数据的类型，那么使用 PL/SQL RECORD 语法：
SQL> CREATE OR REPLACE PACKAGE AF_CONTRACT AS -- spec
-- PROCEDURE my_rpcedure (emp_id NUMBER);
TYPE DTO_GRID IS RECORD
(
ROWKEY NVARCHAR2(200),
COLUMNKEY NVARCHAR2(200),
CELLVALUE NVARCHAR2(200),
OLDVALUE NVARCHAR2(200),
TAG NVARCHAR2(200)
);
END AF_CONTRACT;
/
2 3 4 5 6 7 8 9 10 11 12
Package created.
SQL>
However, if you want a type which you can use in a SQL statement - that is, as the input to a TABLE()function - you will need to create it as a SQL type. SQL and PL/SQL use two different engines, and only SQL types are visible to the SQL engine.
但是，如果您想要一种可以在 SQL 语句中使用的类型 - 即作为TABLE()函数的输入- 您需要将其创建为 SQL 类型。SQL 和 PL/SQL 使用两种不同的引擎，并且只有 SQL 类型对 SQL 引擎可见。
My advice about the necessity of SQL Types is no longer true for later versions of Oracle. Certainly in 11gR2 and 12c the SQL engine will support SQL in PL/SQL packages which uses PL/SQL tables in a TABLE()clause. The Types have to be declared in the package spec, hence public and visible to the SQL engine. Under the covers Oracle generates SQL types for each declaration. You can spot these Types because their names start SYS_PLSQL_followed by numeric identifiers.
我关于 SQL 类型必要性的建议不再适用于更高版本的 Oracle。当然，在 11gR2 和 12c 中，SQL 引擎将支持在TABLE()子句中使用 PL/SQL 表的 PL/SQL 包中的 SQL 。类型必须在包规范中声明，因此对 SQL 引擎是公开的和可见的。在幕后，Oracle 为每个声明生成 SQL 类型。您可以发现这些类型，因为它们的名称SYS_PLSQL_以数字标识符开头。
