简单描述 LENGTH这个获得字符串长度的函数存在BUG
LENGTH这个PLSQL的基本函数相信大家都很熟悉了,他的功能就是获得指定字符串的字符数。一个中文也算是一个字符。
比如:
SQL> select LENGTH('中国') from dual;
LENGTH('中国')
----------------
2
SQL> select LENGTH('人才168') from dual;
LENGTH('人才168')
-----------------
5
但是,真的 LENGTH('中国')就等于2吗?其实不然,这里面有一个小BUG,大家在程序开发的时候要加以注意。
我使用的数据库版本是Oracle 9.2i.
LENGTH SUBSTR这些函数,我想Oracle内部是有一个判断标识的,用来处理国际字符,但是这些函数在函数内部调用的时候,这些标识某些时候不起作用,下面举例说明:
create table T_CODE_COUNTRY
(
ID VARCHAR2(10) not null,
DESCRIPT VARCHAR2(50) not null,
CHAR_LENGTH NUMBER
);
--添加测试数据
prompt Loading T_CODE_COUNTRY...
insert into T_CODE_COUNTRY (ID, DESCRIPT, CHAR_LENGTH)
values ('1', '塞内加尔', null);
insert into T_CODE_COUNTRY (ID, DESCRIPT, CHAR_LENGTH)
values ('2', '芬兰', null);
insert into T_CODE_COUNTRY (ID, DESCRIPT, CHAR_LENGTH)
values ('3', '1111111111', null);
insert into T_CODE_COUNTRY (ID, DESCRIPT, CHAR_LENGTH)
values ('4', '中国', null);
insert into T_CODE_COUNTRY (ID, DESCRIPT, CHAR_LENGTH)
values ('5', '意大利', null);
commit;
prompt 5 records loaded
--执行UPDATE 操作,更新CHAR_LENGTH字段
UPDATE T_CODE_COUNTRY SET CHAR_LENGTH = TO_CHAR(LENGTH(DESCRIPT));
COMMIT;
SELECT T.* FROM T_CODE_COUNTRY T;
---RESULT LIST
1塞内加尔4
2芬兰2
3111111111110
4中国2
5意大利3
这是正常的结果,但是如果在程序中不可能只用这么简单的标准函数,通常需要自定义函数来处理复杂的逻辑,所以,现在我们把这个LENGTH封装在一个自定义函数中,函数的代码如下:
CREATE OR REPLACE FUNCTION FN_TEST_GET_LENGTH(i_vString IN VARCHAR2)
RETURN VARCHAR2 IS
o_vResult VARCHAR2(300);
BEGIN
o_vResult := TO_CHAR(LENGTH(i_vString));
RETURN o_vResult;
END;
/
再次运行UPDATE语句:
UPDATE T_CODE_COUNTRY SET CHAR_LENGTH =FN_TEST_GET_LENGTH(DESCRIPT);
COMMIT;
SELECT T.* FROM T_CODE_COUNTRY T;
ID DESCRIPT CHAR_LENGTH
---------- ----------------- -----------
1 塞内加尔 4
2 芬兰 2
3 1111111111 10
4 中国 4
5 意大利 6
看到了吧,中国的长度变成了4,BUG出现了,LENGTH的结果不再是字符数,而变成了字节数。大家在编写自定义函数的时候一定要多加小心了。
那么这个问题怎么解决呢?如何避免这个BUG的发生呢?
我目前没有找到特别好的办法,不过还是被我发现了一个比较搞笑的、哭笑不得的解决方案,那就是,把自定义函数的残书中,加一个莫名的,没有用途的参数就可以了,如下:
CREATE OR REPLACE FUNCTION FN_TEST_GET_LENGTH(i_vString IN VARCHAR2,
i_vUnUsed IN VARCHAR2)
RETURN VARCHAR2 IS
o_vResult VARCHAR2(300);
BEGIN
o_vResult := TO_CHAR(LENGTH(i_vString));
RETURN o_vResult;
END;
/
再次运行UPDATE:
UPDATE T_CODE_COUNTRY SET CHAR_LENGTH =FN_TEST_GET_LENGTH(DESCRIPT,NULL);
COMMIT;
SELECT T.* FROM T_CODE_COUNTRY T;
ID DESCRIPT CHAR_LENGTH
---------- ----------------- -----------
1 塞内加尔 4
2 芬兰 2
3 1111111111 10
4 中国 2
5 意大利 3
问题解决了,但是知其然,不知其所以然,知情者请多多指教!