【在线相册】存储过程

SQL> conn ymc/ymc Connected. SQL> show user USER is "YMC" SQL> ed Wrote file afiedt.buf 1 CREATE TABLE image( 2 id VARCHAR2(5) PRIMARY KEY, 3 image blob NOT null 4* ) SQL> / Table created. SQL> ed Wrote file afiedt.buf 1 SELECT index_name 2 FROM user_indexes 3* WHERE table_name=upper('image') SQL> / INDEX_NAME ------------------------------ SYS_IL0000051031C00002$$ SYS_C005420 SQL> conn sys as sysdba Enter password: *********** Connected. SQL> show user USER is "SYS" SQL> ed Wrote file afiedt.buf 1* CREATE OR REPLACE directory dir AS 'c:/photos' SQL> / Directory created. SQL> ed Wrote file afiedt.buf 1* GRANT read,write on directory dir to ymc SQL> / Grant succeeded. SQL> conn ymc/ymc Connected. SQL> ed Wrote file afiedt.buf 1 CREATE OR REPLACE PROCEDURE insertimg(ID VARCHAR2,FILENAME VARCHAR2) 2 AS 3 F_LOB BFILE; 4 B_LOB BLOB; 5 BEGIN 6 INSERT INTO image(id, image) 7 VALUES (ID,EMPTY_BLOB ()) RETURN image INTO B_LOB; 8 F_LOB:= BFILENAME ('DIR', FILENAME); 9 DBMS_LOB.FILEOPEN (F_LOB, DBMS_LOB.FILE_READONLY); 10 DBMS_LOB.LOADFROMFILE (B_LOB, F_LOB, 11 DBMS_LOB.GETLENGTH (F_LOB)); 12 DBMS_LOB.FILECLOSE (F_LOB); 13 COMMIT; 14* END; SQL> / Procedure created. SQL> ed Wrote file afiedt.buf 1 BEGIN 2 insertimg('1','1.JPG'); 3* END; 4 / PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 SELECT length(image) 2 FROM image 3* WHERE id = '1' SQL> / LENGTH(IMAGE) ------------- 3493938 SQL> ed Wrote file afiedt.buf 1 BEGIN 2 insertimg('2','2.JPG'); 3* END; SQL> / PL/SQL procedure successfully completed. SQL> ed Wrote file afiedt.buf 1 SELECT length(image) 2 FROM image 3* WHERE id = '2' 4 / LENGTH(IMAGE) ------------- 1916447 SQL>

如果

CREATE OR REPLACE directory dir AS 'c:/photos' 这里写的是 dir

那么存储过程要大写

1 CREATE OR REPLACE PROCEDURE insertimg(ID VARCHAR2,FILENAME VARCHAR2)
2 AS
3 F_LOB BFILE;
4 B_LOB BLOB;
5 BEGIN
6 INSERT INTO image(id, image)
7 VALUES (ID,EMPTY_BLOB ()) RETURN image INTO B_LOB;
8 F_LOB:= BFILENAME ('DIR', FILENAME);
9 DBMS_LOB.FILEOPEN (F_LOB, DBMS_LOB.FILE_READONLY);
10 DBMS_LOB.LOADFROMFILE (B_LOB, F_LOB,
11 DBMS_LOB.GETLENGTH (F_LOB));
12 DBMS_LOB.FILECLOSE (F_LOB);
13 COMMIT;
14* END;

要么据说

CREATE OR REPLACE directory ‘dir’ AS 'c:/photos' 这里写的是 ‘dir’