Oracle中按功能填充的虚拟列的数据类型的长度 [英] Length of Datatype for Virtual Column populated by Function in Oracle

问题描述

我在下面创建了一个基本示例，该示例显示了在Oracle中带有虚拟列的表.虚拟列由函数填充.

I have created a basic example below that shows a table with a virtual column in Oracle. The virtual column is populated by a function.

create or replace 
function awesomeness(
  val in varchar2
)
  return varchar2 deterministic as retVal varchar2(255);
  begin
    retVal := 'Some amazing value';
    return retval;
  end;
/  

drop table sometable;
/
create table sometable( 
  value varchar2(255),
  awesome varchar2(4000) as (awesomeness(value))
);
/

上面的SQL执行得很好，但是实际上我对4000个字符的"awesome"列不满意.我想把它缩小很多，但是根据文档功能上-您不能.但是它表明它可以适应呼叫者，除非它似乎没有这样做.

The above SQL executes fine, but actually I'm not happy with the 'awesome' column being 4000 characters. I want to make this much smaller, but according to the documentation on functions - you can't. However it suggests that it adapts to the caller, except it doesn't seem to be doing that.

数据类型不能指定长度，精度或小数位数. Oracle数据库从调用函数的环境中得出返回值的长度，精度或小数位数.

The datatype cannot specify a length, precision, or scale. Oracle Database derives the length, precision, or scale of the return value from the environment from which the function is called.

如果将表定义更改为以下所示，则会出现错误.

If I change the table definition to that shown below, I get an error.

drop table sometable;
/
create table sometable( 
  value varchar2(255),
  awesome varchar2(30) as (awesomeness(value))
);
/

Error report:
SQL Error: ORA-12899: value too large for column "AWESOME" (actual: 30, maximum: 4000)
12899. 00000 -  "value too large for column %s (actual: %s, maximum: %s)"
*Cause:    An attempt was made to insert or update a column with a value
           which is too wide for the width of the destination column.
           The name of the column is given, along with the actual width
           of the value, and the maximum allowed width of the column.
           Note that widths are reported in characters if character length
           semantics are in effect for the column, otherwise widths are
           reported in bytes.
*Action:   Examine the SQL statement for correctness.  Check source
           and destination column data types.
           Either make the destination column wider, or use a subset
           of the source column (i.e. use substring).

我的虚拟列是否真的需要为varchar2(4000)还是可以以某种方式减少它?

Does my virtual column really need to be varchar2(4000) or can I reduce it somehow?

推荐答案

将大小报告为4000似乎并不是一个问题-无论如何，您实际上将无法放入长值(假设您的函数是只返回短的)，而不是浪费空间.

Having the size reported as 4000 doesn't really seem to be a problem - you won't actually be able to put long values in anyway (assuming your function only returns short ones), and it's not like it's wasting space.

但是，如果您希望它更整洁并以较小的尺寸显示，则可以使用 cast() ，它与其他功能(甚至是诸如substr()之类的内置功能)的处理方式不同:

But if you want it to be neater and appear as a smaller size you can use cast(), which isn't treated the same as other functions (even built-in ones like substr()):

create table sometable( 
  value varchar2(255),
  awesome varchar2(30) as (cast(awesomeness(value) as varchar2(30)))
);

Table sometable created.

desc sometable

Name    Null Type          
------- ---- ------------- 
VALUE        VARCHAR2(255) 
AWESOME      VARCHAR2(30)  

如果您的函数以某种方式返回了更长的值(例如在示例中，因为retVal为255，尽管字符串文字足够短)，则您的虚拟列值将被截断为30个字符.

If your function somehow returns a longer value - as it could in your example since retVal is 255, though your string literal is short enough - your virtual column value would be truncated to 30 characters.

使用您的原始功能，当虚拟列对于返回的列而言太小时会发生这种情况:

With your original function, this is what happens when the virtual column is too small for what it returns:

create table sometable( 
  value varchar2(255),
  awesome varchar2(10) as (cast(awesomeness(value) as varchar2(10)))
);

insert into sometable (value) values ('X');

select awesome from sometable;

AWESOME   
----------
Some amazi 

这篇关于Oracle中按功能填充的虚拟列的数据类型的长度的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！