这里应该不会出现两位数的数字,比如十室十厅吧 直接写触发器好了 create table ttext(a varchar2(10),b number,c number); create or replace trigger tgtest before insert or update on ttext for each row declare flag boolean:=false; v number; begin :new.b:=null; :new.c:=null; if :new.a is null then return; end if; for i in 1..length(:new.a) loop v:=case substr(:new.a,i,1) when '一' then 1 when '二' then 2 when '三' then 3 when '四' then 4 when '五' then 5 when '六' then 6 when '七' then 7 when '八' then 8 when '九' then 9 when '零' then 0 when '两' then 2 end; if v is not null then if flag=false then :new.b:=v; flag:=true; else :new.c:=v; return; end if; end if; end loop; end tgtest; case 部分可以根据需要增删
这样比较好 create or replace trigger tgtest before insert or update on ttext for each row declare v1 number; v2 number; begin v1:=instr(:new.a,'室')-1; v2:=instr(:new.a,'厅')-1; if v1>0 then :new.b:=(case substr(:new.a,v1,1) when '一' then 1 when '二' then 2 when '三' then 3 when '四' then 4 when '五' then 5 when '六' then 6 when '七' then 7 when '八' then 8 when '九' then 9 when '零' then 0 when '两' then 2 end); else :new.b:=null; end if; if v2>0 then :new.c:=(case substr(:new.a,v2,1) when '一' then 1 when '二' then 2 when '三' then 3 when '四' then 4 when '五' then 5 when '六' then 6 when '七' then 7 when '八' then 8 when '九' then 9 when '零' then 0 when '两' then 2 end); else :new.c:=null; end if; exception when others then :new.b:=null; :new.c:=null; end tgtest;
直接写触发器好了
create table ttext(a varchar2(10),b number,c number);
create or replace trigger tgtest
before insert or update on ttext
for each row
declare
flag boolean:=false;
v number;
begin
:new.b:=null;
:new.c:=null;
if :new.a is null then return; end if;
for i in 1..length(:new.a) loop
v:=case substr(:new.a,i,1) when '一' then 1
when '二' then 2
when '三' then 3
when '四' then 4
when '五' then 5
when '六' then 6
when '七' then 7
when '八' then 8
when '九' then 9
when '零' then 0
when '两' then 2 end;
if v is not null then
if flag=false then :new.b:=v;
flag:=true;
else :new.c:=v;
return;
end if;
end if;
end loop;
end tgtest;
case 部分可以根据需要增删
create or replace trigger tgtest
before insert or update on ttext
for each row
declare
v1 number;
v2 number;
begin
v1:=instr(:new.a,'室')-1;
v2:=instr(:new.a,'厅')-1;
if v1>0 then
:new.b:=(case substr(:new.a,v1,1) when '一' then 1
when '二' then 2
when '三' then 3
when '四' then 4
when '五' then 5
when '六' then 6
when '七' then 7
when '八' then 8
when '九' then 9
when '零' then 0
when '两' then 2 end);
else :new.b:=null;
end if;
if v2>0 then
:new.c:=(case substr(:new.a,v2,1) when '一' then 1
when '二' then 2
when '三' then 3
when '四' then 4
when '五' then 5
when '六' then 6
when '七' then 7
when '八' then 8
when '九' then 9
when '零' then 0
when '两' then 2 end);
else :new.c:=null;
end if;
exception
when others then
:new.b:=null;
:new.c:=null;
end tgtest;