用substr函数。
sqlplus下,看看:
select substr('a;b;c;d;e',1,1) from dual;
再参考用触发器和insert into b values(substr('a;b;c;d;e',5,1));
sqlplus下,看看:
select substr('a;b;c;d;e',1,1) from dual;
再参考用触发器和insert into b values(substr('a;b;c;d;e',5,1));
create or replace view a_v as
select substrb(a,1,instrb(a,';',1,1)-1) a ,
substrb(a,instrb(a,';',1,1)+1,instrb(a,';',1,2)-instrb(a,';',1,1)-1) b ,
substrb(a,instrb(a,';',1,2)+1,instrb(a,';',1,3)-instrb(a,';',1,2)-1) c ,
substrb(a,instrb(a,';',1,3)+1,instrb(a,';',1,4)-instrb(a,';',1,3)-1) d ,
substrb(a,instrb(a,';',1,4)+1,lengthb(a)-instrb(a,';',1,4)) e
from a;
for each row
declare
w_col varchar2(100);
i integer :=1;
j integer :=0;
begin
if :new.col1 is not null then
w_col := :new.col1;
j := instr(w_col, ';');
loop
exit when j < 0;
insert into tableB (col2)
values (substr(w_col,i,j-i));
i := j+1;
j := instr(w_col,';',i);
end loop;
insert into tableB (col2)
values (substr(w_col,i));
end if;
end;随手写的,没测试过,自己调整一下
create trigger mytrigger after insert on tableA
for each row
declare
w_col varchar2(100);
pkid number(10);
a_sdate date;
i integer :=1;
j integer :=0;
begin
if :new.col1 is not null then
w_col := :new.col1;
pkid := :new.PKID;
a_sdate := :new.sdate;
j := instr(w_col, ';');
loop
exit when j < 0;
insert into tableB (b_pkid,a_pkid,b_b,flag,b_sdate)
values (seqname.nextval,pkid,substr(w_col,i,j-i),'0',a_sdate);
i := j+1;
j := instr(w_col,';',i);
end loop;
insert into tableB (b_pkid,a_pkid,b_b,flag,b_sdate)
values (seqname.nextval,pkid,substr(w_col,i),'0',a_sdate);
end if;
//另一个字段col11同样执行一遍?
if :new.col11 is not null then
w_col := :new.col11;
pkid := :new.PKID;
a_sdate := :new.sdate;
j := instr(w_col, ';');
loop
exit when j < 0;
insert into tableB (b_pkid,a_pkid,b_b,flag,b_sdate)
values (seqname.nextval,pkid,substr(w_col,i,j-i),'0',a_sdate);
i := j+1;
j := instr(w_col,';',i);
end loop;
insert into tableB (b_pkid,a_pkid,b_b,flag,b_sdate)
values (seqname.nextval,pkid,substr(w_col,i),'0',a_sdate);
end if;
end;
怎么我写的触发器运行后没有效果?
表A:
A_PKID(Number(10)) A_USER(Varchar2(500)) A_SDATE(Date())
A_USER的值格式为以分号相隔的五位编码;如:'11111;22222;33333;44444'
表B:
B_PKID(Number(10)) A_PKID(Number(10)) B_USER(Char(5)) B_SDATE(Date())触发器如下:CREATE OR REPLACE TRIGGER messsendtri
AFTER INSERT
ON table_A
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
declare
user varchar2(500);
A_PKID number(10);
A_SDATE date;
i integer :=1;
j integer :=5;
begin
if :new.USER is not null then
user := :new.USER;
A_PKID := :new.A_PKID;
A_SDATE := :new.A_SDATE;
loop
exit when j < length(user);
insert into table_B (B_PKID,A_PKID,B_USER,B_SDATE)
values (i,A_PKID,substr(user,i,j),A_SDATE);
i := j+1;
j := j+6;
end loop;
end if;
end;测试数据:
insert into table_A(A_PKID,A_USER,A_SDATE) values('1','11111;22222;33333;44444','09-6月 -03');
表A插入数据成功,但表B中无数据?
有问题?