17,140
社区成员




创建表B的语句如下:
create table B as select * from A where 1=2;
触发器如下:
create or replace trigger messageMove
after insert on A
for each row
begin
insert into B(FD_MESSAGE_ID,fd_message_code,fd_message_body,fd_message_state,fd_message_state_info,fd_message_send_time,fd_message_group_name,fd_message_operate_time,fd_message_operate_version,fd_message_operate_info)
values(:new.fd_message_id,:new.fd_message_code,:new.fd_message_body,:new.fd_message_state,:new.fd_message_state_info,:new.fd_message_send_time,:new.fd_message_group_name,:new.fd_message_operate_time,:new.fd_message_operate_version,:new.fd_message_operate_info);
end messageMove;
--在触发器中只是update of关键字后不能是lob类型字段
--取用:new值插入另一表是没有问题的
SQL> create table lob_a(id number,content blob);
Table created
SQL> create table lob_b as select * from lob_a;
Table created
SQL>
SQL> CREATE OR REPLACE TRIGGER tr_lob_a
2 AFTER INSERT ON lob_a
3 FOR EACH ROW
4 BEGIN
5 INSERT INTO lob_b (id, content) VALUES (:new.id, :new.content);
6 END;
7 /
Trigger created
SQL> insert into lob_a values('1','aa');
1 row inserted
SQL> commit;
Commit complete
SQL> select utl_raw.length(content) from lob_b;
UTL_RAW.LENGTH(CONTENT)
-----------------------
1
SQL>
--参考:
--添加blob数据
create table demo
( id int primary key,
theBlob blob
)
/
create or replace directory my_files as '/export/home/tkyte/public_html';
declare
l_blob blob;
l_bfile bfile;
begin
insert into demo values ( 1, empty_blob() )
returning theBlob into l_blob;
l_bfile := bfilename( 'MY_FILES', 'aria.gif' );
dbms_lob.fileopen( l_bfile );
dbms_lob.loadfromfile( l_blob, l_bfile,
dbms_lob.getlength( l_bfile ) );
dbms_lob.fileclose( l_bfile );
end;
/
--11g中使用dbms_lob.append:
CREATE OR REPLACE PROCEDURE Example_1a IS
dest_lob BLOB;
src_lob BLOB;
BEGIN
-- get the LOB locators
-- note that the FOR UPDATE clause locks the row
SELECT b_lob INTO dest_lob
FROM lob_table
WHERE key_value = 12
FOR UPDATE;
SELECT b_lob INTO src_lob
FROM lob_table
WHERE key_value = 21;
dbms_lob.append(dest_lob, src_lob);
COMMIT;
END;