在oracle9i 的sql plus中编译的,是一个向数据库中重复插入图片的procedure,但是总提示编译错误,哪位帮帮忙啊,先谢谢了代码如下:
create table image_lob(T_ID VARCHAR2 (5) NOT NULL,T_IMAGE BLOB NOT NULL);
CREATE OR REPLACE DIRECTORY IMAGES AS 'C:\Oracle';
Create or replace procedure img_insert(T_amount1 number ,T_amount2 number) as
F_lob bfile;
B_lob blob;
Temp varchar2;
Temp2 varchar2;
Begin
Temp:=”user”;
Temp2:=”.jpg”;
Loop
If n_amount1>n_amount2 then
Exit;
End if;
INSERT INTO IMAGE_LOB (T_ID, T_IMAGE)
Values(T_amount1,empty_blob()) return T_IMAGE into B_lob;
F_LOB:= BFILENAME ('IMAGES', temp.concat(n_amount1,temp2));
DBMS_LOB.FILEOPEN (F_LOB, DBMS_LOB.FILE_READONLY);
DBMS_LOB.LOADFROMFILE (B_LOB, F_LOB,
DBMS_LOB.GETLENGTH (F_LOB));
DBMS_LOB.FILECLOSE (F_LOB);
Commit;
N_amount1:=n_amount1+1;
End loop;
End;
程序所用到的图片是以user1,user2,....命名的,
create table image_lob(T_ID VARCHAR2 (5) NOT NULL,T_IMAGE BLOB NOT NULL);
CREATE OR REPLACE DIRECTORY IMAGES AS 'C:\Oracle';
Create or replace procedure img_insert(T_amount1 number ,T_amount2 number) as
F_lob bfile;
B_lob blob;
Temp varchar2;
Temp2 varchar2;
Begin
Temp:=”user”;
Temp2:=”.jpg”;
Loop
If n_amount1>n_amount2 then
Exit;
End if;
INSERT INTO IMAGE_LOB (T_ID, T_IMAGE)
Values(T_amount1,empty_blob()) return T_IMAGE into B_lob;
F_LOB:= BFILENAME ('IMAGES', temp.concat(n_amount1,temp2));
DBMS_LOB.FILEOPEN (F_LOB, DBMS_LOB.FILE_READONLY);
DBMS_LOB.LOADFROMFILE (B_LOB, F_LOB,
DBMS_LOB.GETLENGTH (F_LOB));
DBMS_LOB.FILECLOSE (F_LOB);
Commit;
N_amount1:=n_amount1+1;
End loop;
End;
程序所用到的图片是以user1,user2,....命名的,
这句话里的两个参数应该是n_amount1,和n_amount2
Values(T_amount1,empty_blob()) return T_IMAGE into B_lob;
这句话里的T_amount1应换为n_amount1,我马虎了可是还是不行啊
Temp2 varchar2(30);--定义必须有长度用show error命令看错误信息。
F_lob bfile;
B_lob blob;
temp varchar2(20);
temp2 varchar2(20);
Begin
temp:='user';
temp2:='.jpg';
Loop
If n_amount1>n_amount2 then
Exit;
End if;
INSERT INTO IMAGE_LOB (T_ID, T_IMAGE)
Values(n_amount1,empty_blob()) return T_IMAGE into B_lob;
F_LOB:= BFILENAME ('IMAGES', temp.concat(n_amount1,temp2));
DBMS_LOB.FILEOPEN (F_LOB, DBMS_LOB.FILE_READONLY);
DBMS_LOB.LOADFROMFILE (B_LOB, F_LOB,
DBMS_LOB.GETLENGTH (F_LOB));
DBMS_LOB.FILECLOSE (F_LOB);
Commit;
n_amount1:=n_amount1+1;
End loop;
End;
/提示如下:
PL/SQL: Statement ignored
PLS-00487: 对变量 'TEMP' 的引用无效
PLS-00363: 表达式 'N_AMOUNT1' 不能用作赋值目标
PL/SQL: Statement ignored哪位大哥帮忙调试一下啊,
名称 空? 类型
----------------------------------------------------- -------- ------------------
T_ID NOT NULL VARCHAR2(5)
T_IMAGE NOT NULL BLOBSQL> CREATE OR REPLACE DIRECTORY IMAGES AS 'C:\Oracle';目录已创建。SQL> create or replace procedure img_insert(
2 n_amount1 in number,
3 n_amount2 in number
4 )
5 as
6 F_lob bfile;
7 B_lob blob;
8 Temp varchar2(20);
9 Temp2 varchar2(20);
10 n number default n_amount1; -- 加一个变量
11 errstr varchar2(1000); -- 出差信息提示
12 Begin
13 Temp:='user';
14 Temp2:='.jpg';
15 Loop
16 If n>n_amount2 then -- 此处用n代替n_amount1
17 Exit;
18 End if;
19 INSERT INTO IMAGE_LOB (T_ID, T_IMAGE)
20 Values(n,empty_blob()) return T_IMAGE into B_lob;
21 F_LOB:= BFILENAME ('IMAGES', Temp||n||temp2); -- 此处修改了
22 DBMS_LOB.FILEOPEN (F_LOB, DBMS_LOB.FILE_READONLY);
23 DBMS_LOB.LOADFROMFILE(B_LOB, F_LOB,DBMS_LOB.GETLENGTH(F_LOB));
24 DBMS_LOB.FILECLOSE (F_LOB);
25 Commit;
26 n:=n+1; -- 此处修改了
27 End loop;
28 exception when others then
29 errstr:=sqlerrm(sqlcode);
30 dbms_output.put_line(errstr);
31 End;
32 /过程已创建。SQL> execute img_insert(1,2);PL/SQL 过程已成功完成。SQL> select t_id,dbms_lob.getlength(t_image) from image_lob order by t_id;T_ID DBMS_LOB.GETLENGTH(T_IMAGE)
----- ---------------------------
1 423539
2 423539已选择2行。