oracle – 虚拟列表达式中的连接数会引发ORA-12899：值对于列太

我自己做了一个测试,并找出了在连接两个NUMBER类型列时虚拟列表达式所占用的数据大小的问题.虽然在连接两个字符时没有问题.

数据库版本：

SQL> select banner from v$version where rownum = 1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

SQL>

测试用例1：连接字符串

SQL> CREATE TABLE t(
  2  ID varchar2(2),3  num varchar2(2),4  text VARCHAR2(10) generated always as (id||'_'||num) VIRTUAL
  5  );

Table created.

SQL>
SQL> INSERT INTO t(ID,num) VALUES ('a','e');

1 row created.

SQL> INSERT INTO t(ID,num) VALUES ('b','f');

1 row created.

SQL> INSERT INTO t(ID,num) VALUES ('c','g');

1 row created.

SQL>
SQL> SELECT * FROM T;

ID NU TEXT
-- -- ----------
a  e  a_e
b  f  b_f
c  g  c_g

SQL>

因此,连接两个字符类型列没有问题.

测试用例2：连接数字

SQL> CREATE TABLE t(
  2  ID NUMBER,3  num NUMBER,4  text VARCHAR2(10) generated always as (to_char(id)||'_'||to_char(num)) VIRTUAL
  5  );
text VARCHAR2(10) generated always as (to_char(id)||'_'||to_char(num)) VIRTUAL
*
ERROR at line 4:
ORA-12899: value too large for column "TEXT" (actual: 10,maximum: 81)

不允许？咦！让我们增加尺寸 –

SQL> CREATE TABLE t(
  2  ID NUMBER,4  text VARCHAR2(81) generated always as (to_char(id)||'_'||to_char(num)) VIRTUAL
  5  );

Table created.

SQL>
SQL> INSERT INTO t(ID,num) VALUES (1,4);

1 row created.

SQL> INSERT INTO t(ID,num) VALUES (2,5);

1 row created.

SQL> INSERT INTO t(ID,num) VALUES (3,6);

1 row created.

SQL>
SQL> SELECT * FROM T;

        ID        NUM
---------- ----------
TEXT
--------------------------------------------------------------------------------
         1          4
1_4

         2          5
2_5

         3          6
3_6


SQL> set linesize 200
SQL> SELECT * FROM T;

        ID        NUM TEXT
---------- ---------- ----------------------------------------------------------------------------------------------------
         1          4 1_4
         2          5 2_5
         3          6 3_6

SQL>

那么现在发生了什么？表已经创建了,但是当预期数据大小只有3个字节时,为什么VIRTUAL COLUMN会占用那么多大小,但它需要81个字节.

检查长度,值是正确的,但是,数据大小要大得多.例如,我希望长度为3,所以我将列的大小声明为10个字节.但虚拟列表达式产生的值大小远远大于该值.

SQL> CREATE TABLE t(
  2  ID NUMBER,4  text VARCHAR2(10) generated always as (length(to_char(id)||'_'||to_char(num))) VIRTUAL
  5  );
text VARCHAR2(10) generated always as (length(to_char(id)||'_'||to_char(num))) VIRTUAL
*
ERROR at line 4:
ORA-12899: value too large for column "TEXT" (actual: 10,maximum: 40)


SQL>
SQL> CREATE TABLE t(
  2  ID NUMBER,4  text VARCHAR2(81) generated always as (length(to_char(id)||'_'||to_char(num))) VIRTUAL
  5  );

Table created.

SQL>
SQL> INSERT INTO t(ID,6);

1 row created.

SQL>
SQL> SELECT * FROM T;

        ID        NUM TEXT
---------- ---------- ----------------------------------------------------------------------------------------------------
         1          4 3
         2          5 3
         3          6 3

SQL> clear columns
columns cleared
SQL> SELECT * FROM T;

        ID        NUM TEXT
---------- ---------- ---------------------------------------------------------------------------------
         1          4 3
         2          5 3
         3          6 3

任何见解都非常受欢迎.

UDPATE感谢Alex Poole.我没有考虑隐式转换,所以我不关心明确地表达CAST表达式.那么,以下作品 –

SQL> DROP TABLE t PURGE;

Table dropped.

SQL>
SQL> CREATE TABLE t(
  2  ID NUMBER,4  text VARCHAR2(10) generated always as (cast(to_char(id)||'_'||to_char(num) as varchar2(3))) VIRTUAL
  5  );

Table created.

SQL>
SQL> INSERT INTO t(ID,6);

1 row created.

SQL>
SQL> SELECT * FROM T;

        ID        NUM TEXT
---------- ---------- ----------
         1          4 1_4
         2          5 2_5
         3          6 3_6

SQL>

话虽如此,约束数列不会反映在虚拟列长度中 – 使得NUMBER(1,0)列仍然留下需要81个字符的串联.获取生成值won’t work either的子字符串,在这种情况下获取ORA-12899：值对于“TEXT”列来说太大(实际：10,最大值：40).为每个to_char()调用提供格式模型,例如FM999),可以工作,但直接限制下划线两侧的值而不是整体长度.

如果要限制列大小,可以将其强制转换为相同的数据类型和大小,这更明确：

text VARCHAR2(10) generated always as 
    (cast(to_char(id)||'_'||to_char(num) as VARCHAR2(10))) VIRTUAL

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!