create or replace procedure updateclob ( v_tablename in varchar2, --表名 v_imagename in varchar2, --图片名(主键) v_image in blob, --图片 ) is lobloc blob; query_str varchar2(1000); begin
query_str :='select image from '||v_tablename||' where imagename= :id '; EXECUTE IMMEDIATE query_str INTO lobloc USING v_imagename;
(
v_tablename in varchar2, --表名
v_imagename in varchar2, --图片名(主键)
v_image in blob, --图片
)
is
lobloc blob;
query_str varchar2(1000);
begin
query_str :='select image from '||v_tablename||' where imagename= :id ';
EXECUTE IMMEDIATE query_str INTO lobloc USING v_imagename;
dbms_lob.write(lobloc,DBMS_lob.Getlength(lobloc),1,lobloc);
commit;
end;
连接到:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production SQL> drop table IMAGE_LOB; 表已丢弃。 SQL> CREATE TABLE IMAGE_LOB (
2 T_ID VARCHAR2 (5) NOT NULL,
3 T_IMAGE BLOB NOT NULL
4 ); 表已创建。 SQL> CREATE OR REPLACE DIRECTORY IMAGES AS 'C:\Inetpub\wwwroot'; 目录已创建。 SQL> CREATE OR REPLACE PROCEDURE IMG_INSERT (
2 TID VARCHAR2,
3 FILENAME VARCHAR2) AS
4 F_LOB BFILE;
5 B_LOB BLOB;
6 BEGIN
7 INSERT INTO IMAGE_LOB (T_ID, T_IMAGE) valueS (TID,
8 EMPTY_BLOB ()) RETURN T_IMAGE INTO B_LOB;
9 F_LOB:= BFILENAME ('IMAGES', FILENAME);
10 DBMS_LOB.FILEOPEN (F_LOB, DBMS_LOB.FILE_READONLY);
11 DBMS_LOB.LOADFROMFILE (B_LOB, F_LOB,
12 DBMS_LOB.GETLENGTH (F_LOB));
13 DBMS_LOB.FILECLOSE (F_LOB);
14 COMMIT;
15 END;
16 / 过程已创建。 SQL> BEGIN
2 IMG_INSERT('1','win2000.gif');
3 END;
4 / PL/SQL 过程已成功完成。 SQL> select length(t_image) from image_lob where t_id='1';