我的需求如下:
1.在oracle中创建Insert触发器,这个触发器关系四个表(就以A,B,C,D个表为例)
2.A表结构(id number,content blob)
3.当A表中添加数据的时候就执行这样的操作,根据刚添加到A表中ID,根据这个ID到B表中去查询出相用的记录(B表是个配置信息表,其结构大概是这样的ID number,fieldA number(2),fieldB number(3),fieldC number(4)大家不要考虑太多A表和B表之间的关系,现在就当根据A表ID可以查到B表中的相用信息)
4.查出B表的信息以后,然后根据B表中的配置信息,把A表中的数据以符合B表各个字段的要求去转换,简单的说要求是这样,就是把A表中的content字段根据B表各个字段的长度,(因为B表中都是Number类型)把content字段的内容截取成多个值(例如A表中的content字段的内容是:ABCD123456789,就当这个ID对用B表的各个字段的长度分别是2,3,4 那么转换后是这样的(fieldA = AB ,fieldB= CD1, fieldC= 2345)就是这样,如果转换没有问题就添加到C表中(C表结构如下(id number,fieldA varchar2(20),fieldB varchar2(20),fieldC varchar2(20))如果转换失败,就报失败信息添加到D表中(C表结构id number,error_desc varchar2(200))
1.在oracle中创建Insert触发器,这个触发器关系四个表(就以A,B,C,D个表为例)
2.A表结构(id number,content blob)
3.当A表中添加数据的时候就执行这样的操作,根据刚添加到A表中ID,根据这个ID到B表中去查询出相用的记录(B表是个配置信息表,其结构大概是这样的ID number,fieldA number(2),fieldB number(3),fieldC number(4)大家不要考虑太多A表和B表之间的关系,现在就当根据A表ID可以查到B表中的相用信息)
4.查出B表的信息以后,然后根据B表中的配置信息,把A表中的数据以符合B表各个字段的要求去转换,简单的说要求是这样,就是把A表中的content字段根据B表各个字段的长度,(因为B表中都是Number类型)把content字段的内容截取成多个值(例如A表中的content字段的内容是:ABCD123456789,就当这个ID对用B表的各个字段的长度分别是2,3,4 那么转换后是这样的(fieldA = AB ,fieldB= CD1, fieldC= 2345)就是这样,如果转换没有问题就添加到C表中(C表结构如下(id number,fieldA varchar2(20),fieldB varchar2(20),fieldC varchar2(20))如果转换失败,就报失败信息添加到D表中(C表结构id number,error_desc varchar2(200))
create or replace trigger InsertTrigger
after insert on A
for each row
declare
v_msgid varchar2(16):='';
v_bufferdate blob;
v_count number(10):=0;
v_fieldA varchar2(100):='';
v_fieldB varchar2(100):='';
v_fieldC varchar2(100):='';
--定义变量存储截取后的值
v_f1 varchar2(100):='';
v_f2 varchar2(100):='';
v_f3 varchar2(100):='';begin
v_bufferdate := new:bufferdate;
--判断是否可以查询到记录
select count(*) into v_count from A where messageid = new:messageid;
--取出配置表中的相用字段
if v_count > 0 then
select fieldA,fieldB,fieldC into v_fieldA,v_fieldB,v_fieldC
from B;
end if ;
--根据配置表截取字符串
v_f1:=substr(v_bufferdate,fieldA-(fieldA-1),fieldA);
v_f2:=substr(v_bufferdate,fieldB-fieldA,fieldB);
v_f3:=substr(v_bufferdate,fieldC-fieldB,fieldC);
--截取后如果没有异常
--添加到表中
insert into C values(v_f1,v_f2,v_f3);
-- exception
--异常表
insert into D values(v_f1,v_f2,v_f3);
end;
end InsertTrigger;还是麻烦各位大侠放到数据库中执行一下吧!好多错误的.........
没事时多看点资料多做点测试,很多东西不是楼主那样想当然用的,刚好我好久没用blob存取了,顺便练练手,帮你测了一下,你自己看吧:
SQL> create table a (id number,content blob);Table createdSQL> create table b(ID number,fieldA number(2),fieldB number(3),fieldC number(4));Table createdSQL> create table c(id number,fieldA varchar2(20),fieldB varchar2(20),fieldC varchar2(20));Table createdSQL> create table d(id_a_struct number,error_desc varchar2(200));Table createdSQL> create or replace trigger InsertTrigger
2 after insert on A
3 for each row
4 declare
5 v_msgid varchar2(16):='';
6 v_id number;
7 v_bufferdate blob;
8 v_count number(10):=0;
9 v_fieldA varchar2(100):='';
10 v_fieldB varchar2(100):='';
11 v_fieldC varchar2(100):='';
12 --定义变量存储截取后的值
13 v_f1 varchar2(100):='';
14 v_f2 varchar2(100):='';
15 v_f3 varchar2(100):='';
16 v_raw_buffer raw(8000);
17 v_blob_len number;
18 v_str_buffer varchar2(8000);
19 myexception exception;
20 v_bb number := 8000;
21 v_errmsg varchar2(200);
22
23 begin
24
25 v_bufferdate := :new.content;
26 v_id := :new.id;
27 v_blob_len := dbms_lob.getlength(v_bufferdate);
28
29 --判断字符串是否过长
30 if nvl(v_blob_len,0) > 8000 or nvl(v_blob_len,0) = 0 then
31 raise myexception;
32 end if;
33
34 --将lob读取到字符串变量中
35 dbms_lob.read(v_bufferdate,v_blob_len,1,v_raw_buffer);
36 v_str_buffer := utl_raw.cast_to_varchar2(v_raw_buffer);
37
38 --判断是否可以查询到记录
39 select count(*) into v_count from B where id = :new.id;
40 --取出配置表中的相用字段
41 if v_count > 0 then
42 select fieldA,fieldB,fieldC into v_fieldA,v_fieldB,v_fieldC
43 from B;
44 else
45 raise myexception;
46 end if ;
47 --根据配置表截取字符串
48 v_f1:=substr(v_str_buffer,v_fieldA-(v_fieldA-1),v_fieldA);
49 v_f2:=substr(v_str_buffer,v_fieldB-v_fieldA,v_fieldB);
50 v_f3:=substr(v_str_buffer,v_fieldC-v_fieldB,v_fieldC);
51 --截取后如果没有异常
52 --添加到表中
53 insert into C values(:new.id,v_f1,v_f2,v_f3);
54 exception
55 when myexception then
56 insert into D values(v_id,'blob数据过长或B表中不存在此id!');
57 when others then
58 --异常表
59 v_errmsg := sqlcode || ':' || sqlerrm;
60 insert into D values(v_id,v_errmsg);
61 end InsertTrigger;
62 /Trigger createdSQL>
SQL> insert into b values(1,2,3,4);1 row insertedSQL> commit;Commit completeSQL> insert into a select 1, to_blob(utl_raw.cast_to_raw('ABCD123456789')) from dual;1 row insertedSQL> insert into a select 2, to_blob(utl_raw.cast_to_raw('ABCD123456789')) from dual;1 row insertedSQL> commit;Commit completeSQL> select * from a; ID CONTENT
---------- -------
1 <BLOB>
2 <BLOB>SQL> select * from b; ID FIELDA FIELDB FIELDC
---------- ------ ------ ------
1 2 3 4SQL> select * from c; ID FIELDA FIELDB FIELDC
---------- -------------------- -------------------- --------------------
1 AB ABC ABCDSQL> select * from d;ID_A_STRUCT ERROR_DESC
----------- --------------------------------------------------------------------------------
2 blob数据过长或B表中不存在此id!
AFTER INSERT ON A
FOR EACH ROW
DECLARE
blobData BLOB;
numID NUMBER;
numBlobLen NUMBER;
rowRecord B%ROWTYPE; rawBuffer RAW(8000);
strBuffer VARCHAR2(8000);
strData1 VARCHAR2(20); strData2 VARCHAR2(20);
strData3 VARCHAR2(20);
strErrMsg VARCHAR2(5000); INS_D_EXCE EXCEPTION;
BEGIN
numID := :new.id;
blobData := :new.content; numBlobLen := NVL(DBMS_LOB.GETLENGTH(:new.content),0); IF (numBlobLen > 8000) THEN
strErrMsg := 'BLOB数据过长!'; RAISE INS_D_EXCE;
END IF; IF (numBlobLen = 0) THEN
strErrMsg := 'BLOB数据不存在!';
RAISE INS_D_EXCE;
END IF; DBMS_LOB.READ(blobData,numBlobLen,1,rawBuffer);
strBuffer := UTL_RAW.CAST_TO_VARCHAR2(rawBuffer); SELECT * INTO rowRecord FROM B WHERE id = :new.id;
strData1 := SUBSTR(strBuffer,1 ,rowRecord.fieldA);
strData2 := SUBSTR(strBuffer,rowRecord.fieldA,rowRecord.fieldB);
strData3 := SUBSTR(strBuffer,rowRecord.fieldB,rowRecord.fieldC); INSERT INTO C VALUES(:new.id,strData1,strData2,strData3); EXCEPTION
WHEN NO_DATA_FOUND THEN
strErrMsg := 'ID***' || TO_CHAR(numID) || '***不存在!';
INSERT INTO D VALUES(numID, strErrMsg);
WHEN INS_D_EXCE THEN
INSERT INTO D VALUES(numID, strErrMsg);
WHEN OTHERS THEN strErrMsg := SQLCODE || ':' || SQLERRM;
INSERT INTO D VALUES(numID, strErrMsg);
END SplitStrTrig;
/
after insert on A
=========================
--判断是否可以查询到记录
select count(*) into v_count from A where messageid = new:messageid;
这个是不被Oracle允许的。