为什么Oracle varchar2具有强制大小作为定义参数? [英] Why does Oracle varchar2 have a mandatory size as a definition parameter?

问题描述

我想知道为什么Oracle在VARCHAR2的定义中需要size参数.

I want to know why Oracle needs the size parameter in the definition of the VARCHAR2.

我认为这是为了约束.甲骨文将此参数作为可选参数(例如NUMBER dataType)会是一个更好的选择吗?

I think that is for constraint. Would it be a better option that oracle takes this parameter as an optional like NUMBER dataType?

我经常在将旧表的大小调整为更大的大小时遇到​​问题，因为有时值大于VARCHAR2列的大小定义.

I often have problems resizing old tables to larger sizes, because sometimes a value is bigger than the size definition of the VARCHAR2 column.

定义VARCHAR2(10)或VARCHAR2(1000)的类型是相同的.

It's the same to define a type of VARCHAR2(10) or VARCHAR2(1000).

我想这是不必要的约束.如果不是，您是否知道这种约束导致有用的实际情况?以及为什么在NUMBER类型中没有这样的声明?

I guess, it's an unnecessary constraint. If not, do you know of a real case when this constraint resulted in something useful? And why no such declaration in NUMBER type ?

推荐答案

定义类型相同 varchar2(10)或varchar2(1000).

It's the same to define a type of varchar2(10) or varchar2(1000).

不，这根本不是一回事.

No, it is not the same thing at all.

1. 该列的长度对于开发人员构建屏幕是有用的元数据.
2. 类似的自动查询工具(如TOAD和SQL Developer)在呈现结果时也会使用列的长度.
3. 在为PL/SQL集合分配内存时，数据库使用变量的长度.当该内存从PGA中耗尽时，由于服务器内存不足，变量声明可能导致程序失败.
4. 在PL/SQL程序中声明单个变量也存在类似的问题，只是集合倾向于使问题成倍增加.
5. 超大列会为复合索引带来问题.以下是在具有8K块的数据库上

....

SQL> create table t23 (col1 varchar2(4000), col2 varchar2(4000))
  2  /

Table created.

SQL> create index t23_i on t23(col1,col2)
  2  /
create index t23_i on t23(col1,col2)
                      *
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded


SQL>

但最重要的是，列大小是错误检查的一种形式.如果该列应为十个字符长，并且某些自动过程正在尝试加载一千个字符，则出问题了.该过程应该失败，因此我们可以调查为什么要加载duff数据.替代方案是一个充满垃圾的数据库，如果这是我们想要的，我们应该给每个人Excel并完成它.

But above all else, columns sizes are a form of error checking. If the column is supposed to be ten characters long and some autonomic process is trying to load a thousand characters then something is wrong. The process ought to fail, so we can investigate why we are loading duff data. The alternative is a database full of garbage, and if that is what was wanted we should just have given everybody Excel and have done with it.

的确，事实证明我们低估了更改列大小的工作可能会很麻烦.但这并不经常发生，我们可以通过在PL/SQL中使用％TYPE和SUBTYPE声明而不是对可变长度进行硬编码来减轻很多麻烦.

It is true that changing the column size when it turns out we have underestimated can be tiresome. But it doesn't happen very often, and we can mitigate a lot of the pain by using %TYPE and SUBTYPE declarations in our PL/SQL instead of hard-coding variable lengths.

为什么NUMBER类型中没有这样的声明"

"why no such declaration in NUMBER type"

数字不同.首先，数字的最大大小比等效文本小得多(保证精度的38位数字).

Numbers are different. For a start, the maximum size of a number is much smaller than the text equivalent (38 digits of guaranteed precision).

但是主要区别在于Oracle存储数字值用科学记数法表示，因此数字的算术大小与其占用的存储空间之间没有直接的关系.

But the key difference is that Oracle stores numeric values in scientific notation so there is not a straightforward relationship between the arithmetical size of the number and the storage space it consumes.

SQL> select vsize(123456789012345678901) n1
  2         , vsize(999999999999999999999999999999) n2
  3         , vsize(0.000000000000000000001) n3
  4         , vsize(1000000000000000000000000) n4
  5  from dual
  6  /

        N1         N2         N3         N4
---------- ---------- ---------- ----------
        12         16          2          2

SQL> 

尽管如此，仍然是在所有可能的地方指定小数位数和精度的优良作法，尤其是在我们处理整数(例如整数)或金钱时.

Nevertheless, it remains good practice to specify scale and precision wherever possible, especially when we are dealing with integers, say, or money.

这篇关于为什么Oracle varchar2具有强制大小作为定义参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！