SQL 如何创建引用数据类型表列的 Oracle 类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9634535/
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
How do I create Oracle Type which refers to table columns for data type?
提问by AppleGrew
I am trying to define a typeusing the following code.
我正在尝试type使用以下代码定义 a 。
CREATE OR REPLACE TYPE MY_TYPE AS OBJECT (
app_id some_table_name.app_id%type
);
If I run this, I get the error.
如果我运行它，我会收到错误消息。
Error(4,32): PLS-00201: identifier 'some_table_name.app_id' must be declared
What is wrong with this?
这有什么问题？
回答by APC
What's wrong with it is that %typeis PL/SQL syntax. It isn't supported in SQL. Now we use PL/SQL to define Types (especially member functions, constructors, etc) but the Types themselves are SQL objects, and so follow SQL rules. That means we must declare Type attributes with explicit datatypes.
它的错误%type在于PL/SQL 语法。SQL 不支持它。现在我们使用 PL/SQL 来定义类型（尤其是成员函数、构造函数等），但类型本身是 SQL 对象，因此遵循 SQL 规则。这意味着我们必须使用显式数据类型声明 Type 属性。
I agree that's a shame, and it would be really neat if we could reference table columns in type declarations like this. Unfortunately Oracle have really slowed down the changes to their TYPE implementation over the last couple of versions, so I think it is unlikely this will change in the near future.
我同意这是一种耻辱，如果我们可以在这样的类型声明中引用表列，那就太棒了。不幸的是，在过去的几个版本中，Oracle 确实放慢了对其 TYPE 实现的更改，因此我认为这不太可能在不久的将来发生变化。
What I would reallylike to see is Oracle support this syntax:
我真正希望看到的是 Oracle 支持这种语法：
CREATE OR REPLACE TYPE MY_TYPE AS OBJECT
( one_row some_table_name.%rowtype );
Dynamic objects for interfaces: how cool would that be?
接口的动态对象：这有多酷？
回答by Tony Andrews
You cannot use some_table_name.app_id%typewhen declaring a type in the database, any more than you can do this:
some_table_name.app_id%type在数据库中声明类型时不能使用，就像你可以这样做：
create table emp (empno number,
deptno dept.deptnp%type, -- NOT ALLOWED
);
You must use either a built-in type such as NUMBER, VARCHAR2(10) or a user-defined type such as mytype
您必须使用内置类型（例如 NUMBER、VARCHAR2(10)）或用户定义的类型（例如 mytype
