如何创建将表列引用为数据类型的Oracle类型? [英] How do I create Oracle Type which refers to table columns for data type?

问题描述

我正在尝试使用以下代码定义type.

I am trying to define a type using the following code.

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

这有什么问题?

推荐答案

%type是PL/SQL语法是问题所在. SQL不支持它.现在，我们使用PL/SQL定义类型(尤其是成员函数，构造函数等)，但是类型本身是SQL对象，因此遵循SQL规则.这意味着我们必须使用显式数据类型声明Type属性.

What's wrong with it is that %type is 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.

我同意这是一个耻辱，如果我们可以像这样在类型声明中引用表列，那真的很整洁.不幸的是，Oracle在过去的两个版本中确实放慢了对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支持以下语法:

What I would really like to see is Oracle support this syntax:

CREATE OR REPLACE TYPE MY_TYPE AS OBJECT 
      (     one_row        some_table_name.%rowtype ); 

接口的动态对象:那有多酷?

Dynamic objects for interfaces: how cool would that be?

这篇关于如何创建将表列引用为数据类型的Oracle类型?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！