我做了一下试验 CREATE OR REPLACE PROCEDURE update_photo( p_id NUMBER,p_photo VARCHAR2) AS f_photo BFILE; b_photo BLOB; BEGIN SELECT photo INTO b_photo FROM students WHERE sid=p_id FOR UPDATE; f_photo:=bfilename('IMAGE',p_photo); dbms_lob.fileopen(f_photo,dbms_lob.file_readonly); dbms_lob.loadfromfile(b_photo, f_photo,dbms_lob.getlength(f_photo)); dbms_lob.fileclose(f_photo); COMMIT; EXCEPTION WHEN others THEN dbms_output.put_line('*** ERROR *** Check you procedure.'); END; create or replace trigger trg_update_lob AFTER UPDATE ON students FOR EACH ROW BEGIN INSERT INTO update_blob(id) VALUES(:new.sid); END trg_update_lob;^_^>select sid,dbms_lob.getlength(photo) as length from students where sid=2; SID LENGTH ---------- ---------- 2 4854 ^_^>desc update_blob; 名称 空? 类型 ----------------------------------------------------- -------- ------------------ ID NUMBER UPDATE_TIME DATE^_^>select * from update_blob; 未选定行^_^>declare 2 pid number; 3 fphoto varchar(100); 4 begin 5 pid:=2; 6 fphoto:='1z0-001.rar'; 7 update_photo(pid,fphoto); 8 end; 9 /PL/SQL 过程已成功完成。^_^>select sid,dbms_lob.getlength(photo) as length from students where sid=2; SID LENGTH ---------- ---------- 2 896078^_^>select * from update_blob;未选定行 不知道是不是我的update过程有问题,在这里触发器并没有触发, 可是我用pl/sql dev更新时,触发器被触发了,看下面: ^_^>select sid,dbms_lob.getlength(photo) as length from students where sid=2; SID LENGTH ---------- ---------- 2 9263^_^>select * from update_blob; ID UPDATE_TIM ---------- ---------- 2 13-5月 -03今天太晚了,明天再实验试验看
CREATE OR REPLACE PROCEDURE update_photo(
p_id NUMBER,p_photo VARCHAR2)
AS
f_photo BFILE;
b_photo BLOB;
BEGIN
SELECT photo INTO b_photo FROM
students WHERE sid=p_id FOR UPDATE;
f_photo:=bfilename('IMAGE',p_photo);
dbms_lob.fileopen(f_photo,dbms_lob.file_readonly);
dbms_lob.loadfromfile(b_photo,
f_photo,dbms_lob.getlength(f_photo));
dbms_lob.fileclose(f_photo);
COMMIT;
EXCEPTION
WHEN others THEN
dbms_output.put_line('*** ERROR *** Check you procedure.');
END; create or replace trigger trg_update_lob
AFTER UPDATE ON students FOR EACH ROW
BEGIN
INSERT INTO update_blob(id) VALUES(:new.sid);
END trg_update_lob;^_^>select sid,dbms_lob.getlength(photo) as length from students where sid=2; SID LENGTH
---------- ----------
2 4854
^_^>desc update_blob;
名称 空? 类型
----------------------------------------------------- -------- ------------------
ID NUMBER
UPDATE_TIME DATE^_^>select * from update_blob;
未选定行^_^>declare
2 pid number;
3 fphoto varchar(100);
4 begin
5 pid:=2;
6 fphoto:='1z0-001.rar';
7 update_photo(pid,fphoto);
8 end;
9 /PL/SQL 过程已成功完成。^_^>select sid,dbms_lob.getlength(photo) as length from students where sid=2; SID LENGTH
---------- ----------
2 896078^_^>select * from update_blob;未选定行
不知道是不是我的update过程有问题,在这里触发器并没有触发,
可是我用pl/sql dev更新时,触发器被触发了,看下面:
^_^>select sid,dbms_lob.getlength(photo) as length from students where sid=2; SID LENGTH
---------- ----------
2 9263^_^>select * from update_blob; ID UPDATE_TIM
---------- ----------
2 13-5月 -03今天太晚了,明天再实验试验看
希望大家指导、交流。
Email:[email protected]