关于 varchar2 的最大长度
varchar2有两个最大长度:一个是在字段类型4000;一个是在PL/SQL中变量类型32767。这是一个比较容易出错的地方。因为在函数中可以声明长度超过4000的字符串变量,并且将它作为返回值,这里是不会提示编译错误的。这个函数平时都可以正常执行,而一旦这个字符串长度超过4000,函数执行就会出错。
SQL> create or replace function funny return varchar2
函数已创建。
SQL> select funny from dual;
select funny from dual
第 1 行出现错误:
ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小 ORA-06512: 在
"MYMOTIF.FUNNY", line 6
本来以为记录下来就好。刚才跟一位同事讨论了一下,认为有可能在schema级varchar2的长度限制都是4000,而在PL/SQL代码级的长度限制是32767。下面继续测试:
-- 1. 作为函数入参的限制是32767
SQL> create or replace function funny( p_char in varchar2 ) return number
函数已创建。
SQL> declare
32767
PL/SQL 过程已成功完成。
-- 2. 同样的函数,在schema级和在PL/SQL代码级长度限制不同
SQL> select length(rpad('a', 4001, 'b')) from dual;
LENGTH(RPAD('A',3276558,'B'))
-----------------------------
SQL> begin
4001
PL/SQL 过程已成功完成。
如果中间结果字符串长度超过限制会怎样呢?
(1)在PL/SQL中,如果中间结果超过32767,没有影响:
SQL> declare
aaaaabbbbb
PL/SQL 过程已成功完成。
(2)在schema级如果中间结果超过4000,会提示错误:
SQL> select substr(rpad('a', 4000, 'a')||rpad('b', 4000, 'b'), 3996, 10) from dual;
select substr(rpad('a', 4000, 'a')||rpad('b', 4000, 'b'), 3996, 10) from dual
第 1 行出现错误:
ORA-01489: 字符串连接的结果过长
如果字符串长度超过限制怎么办?可以使用CLOB类型。比如最开始的那个例子,可以将返回值改为CLOB类型:
SQL> create or replace function funny return clob
函数已创建。
SQL> select funny from dual;
FUNNY
--------------------------------------------------------------------------------
abbbbbbbbbbbbbbbbbbbbbbb bbbbbbbbbbbbbbbbbbbbbbbb bbbbbbbbbbbbbbbbbbbbbbbb bbbbbb
bb
SQL> set long 10000
SQL> select funny from dual;
FUNNY
--------------------------------------------------------------------------------
abbbbbbbbbbbbbbbbbbbbbbb bbbbbbbbbbbbbbbbbbbbbbbb bbbbbbbbbbbbbbbbbbbbbbbb bbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbb bbbbbbbbbbbbbbbbbbbbbbbb bbbbbbbbbbbbbbbbbbbbbbbb bbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbb bbbbbbbbbbbbbbbbbbbbbbbb bbbbbbbbbbbbbbbbbbbbbbbb bbbbbbbb
bbbbbbbbbbbbbbbbbbbbbbbb bbbbbbbbbbbbbbbbbbbbbbbb bbbbbbbbbbbbbbbbbbbbbbbb bbbbbbbb
.
这样返回字符串的最大长度就是32767了。