关于 varchar2 的最大长度

关于 varchar2 的最大长度


　　varchar2有两个最大长度：一个是在字段类型4000；一个是在PL/SQL中变量类型32767。今天犯了一个小错误，就是函数的varchar2类型的返回值长度也是4000，而不是我以为的32767。


　　想了一下，这是一个比较容易出错的地方。因为在函数中我可以声明长度超过4000的字符串变量，并且将它作为返回值，这里是不会提示编译错误的。这个函数平时都可以正常执行，而一旦这个字符串长度超过4000，函数执行就会出错。所以这个问题虽然比较简单，仍然记录一下。


　　C:\Documents and Settings\yuechao.tianyc>sqlplus


　　SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 5月 5 17:15:59 2009


　　Copyright (c) 1982, 2005, Oracle. All rights reserved.


　　请输入用户名: test/test


　　连接到:


　　Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production


　　With the Partitioning, OLAP and Data Mining options


　　SQL> create or replace function funny return varchar2


　　2 is


　　3 v_yct varchar2(32767);


　　4 begin


　　5 v_yct := rpad('a', 4001, 'b');


　　6 return v_yct;


　　7 end;


　　8 /


　　函数已创建。


　　SQL> select funny from dual;


　　select funny from dual


　　*


　　第 1 行出现错误:


　　ORA-06502: PL/SQL: 数字或值错误 : 字符串缓冲区太小


　　ORA-06512: 在 "TEST.FUNNY", line 6


　　本来以为记录下来就好。刚才跟一位同事讨论了一下，认为有可能在schema级varchar2的长度限制都是4000，而在PL/SQL代码级的长度限制是32767。下面继续测试：


　　-- 1. 作为函数入参的限制是32767


　　SQL> create or replace function funny( p_char in varchar2 ) return number


　　2 is


　　3 begin


　　4 return length(p_char);


　　5 end;


　　6 /


　　函数已创建。


　　SQL> declare


　　2 v_char varchar2(32767);


　　3 begin


　　4 v_char := rpad('a', 32767, 'b');


　　5 dbms_output.put_line(funny(v_char));


　　6 end;


　　7 /


　　32767


　　PL/SQL 过程已成功完成。


　　-- 2. 同样的函数，在schema级和在PL/SQL代码级长度限制不同


　　SQL> select length(rpad('a', 4001, 'b')) from dual;


　　LENGTH(RPAD('A',3276558,'B'))


　　-----------------------------


　　4000


　　SQL> begin


　　2 dbms_output.put_line(length(rpad('a', 4001, 'b')));


　　3 end;


　　4 /


　　4001


　　PL/SQL 过程已成功完成。


　　如果中间结果字符串长度超过限制会怎样呢？


　　（1）在PL/SQL中，如果中间结果超过32767，没有影响：


　　SQL> declare


　　2 v_char1 varchar2(32767);


　　3 v_char2 varchar2(32767);


　　4 v_char3 varchar2(10);


　　5 begin


　　6 v_char1 := lpad('a', 32767, 'a');


　　7 v_char2 := lpad('b', 32767, 'b');


　　8 v_char3 := substr(v_char1 || v_char2, 32763, 10);


　　9 dbms_output.put_line(v_char3);


　　10 end;


　　11 /


　　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


　　2 is


　　3 v_yct varchar2(32767);


　　4 begin


　　5 v_yct := rpad('a', 4001, 'b');


　　6 return v_yct;


　　7 end;


　　8 /


　　函数已创建。


　　SQL> select funny from dual;


　　FUNNY


　　--------------------------------------------------------------------------------


　　abbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb


　　SQL> set long 10000


　　SQL> select funny from dual;


　　FUNNY


　　--------------------------------------------------------------------------------


　　abbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb


　　bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb


　　bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb


　　bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb


　　.


　　这样返回字符串的最大长度就是32767了。

如果想了解更多相关的资料请关注(广州轮椅出租 www.jblunyi.com)官网