oracle substr/length之中文字符

今天碰到一件比较丢份的事情,截取字符串时候遇到的.拿个简化的实例记录下

 

--robert/robwert

create table t_test1(name varchar2(500));

declare
v_name varchar2(10000);

begin
for i in 1..103 loop
v_name :=v_name ||'计算机';
end loop;

v_name :=substr(v_name,1,500);
insert into t_test1 values(v_name);
commit;
end;
/

报下面的错误:

...

ORA-12899:列"ROBERT"."T_TEST1"."NAME"的值太大(实际值:618,最大值:500)

...

 

很显然,将要插入的值超过字段的限制.

....

....

 

将变量长度输出到屏幕

set serveroutput on

 

declare
v_name varchar2(10000);

begin
for i in 1..103 loop
v_name :=v_name ||'计算机';
end loop;

v_name :=substr(v_name,1,500);

dbms_output.put_line(to_char(length(v_name)));

insert into t_test1 values(v_name);
commit;

 

end;
/

 

set serveroutput off

 

309

...

ORA-12899:列"ROBERT"."T_TEST1"."NAME"的值太大(实际值:618,最大值:500)

...

 

---

这下可以肯定length返回的是中文字符串的个数.而在存储的时候中文字符是要占2个byte的.

汗颜,在这里又倒了一次.

 

改成下面的代码就OK了,

 

declare
v_name varchar2(10000);

begin
for i in 1..103 loop
v_name :=v_name ||'计算机';
end loop;

v_name :=substr(v_name,1,250);

insert into t_test1 values(v_name);
commit;
end;
/

特意上来丢下人,下回不能在遇到这样的错误,