应该是你的第二个打开游标的语句无问题了,你可以用一个游标来打开看看有没有问题,不要这样直接打开,我没有这样用过,也不知道具体的情况!
12 cursor c2(v_id_in integer) is
13 select id,colorhistogram_rgb
14 from imageinfo
15 where id < v_id_in;
然后用这样打开
FOR someid IN c2(id_in) loop
end loop;
12 cursor c2(v_id_in integer) is
13 select id,colorhistogram_rgb
14 from imageinfo
15 where id < v_id_in;
然后用这样打开
FOR someid IN c2(id_in) loop
end loop;
解决方案 »
- oracle表空间关联
- 求解~!!Oracle RAC查询速度异常缓慢
- 关于表空间的一点小问题
- 存储过程中的执行语句中的某一块为什么一定要加begin end?
- 使用group by后怎么查询除分组字段外的字段?
- plsql中minus的問題
- Access数据库导出某种文件后,用Oracle数据库将某种文件导入.某种文件是什么样的文件最好呢?
- 新手问题: 创建数据库!!
- 数据库连接失败 ORA-01034: ORACLE not available的错误提示是什么意思?大家进来看看,急死了!
- 咨询下各位oracel大佬…
- 关于移动数据库的简单问题,大家都来帮帮忙啊,多谢!
- 如何在Oracle用SQl语句来得到某个Column数据类型?谢谢
SQL> CREATE OR REPLACE Procedure CHRgbIndexing
2 ( id_in IN integer )
3 IS
4 ch_rgb varchar2(28671);
5 TYPE rgbBins IS TABLE OF float;
6 binBig rgbBins;
7 binSmall rgbBins;
8 cursor c1 is
9 select colorhistogram_rgb
10 from imageinfo
11 where id = id_in;
12 cursor c2(v_id_in integer) is
13 select id,colorhistogram_rgb
14 from imageinfo
15 where id < v_id_in and RowNum < 10 and distance<2048 order by distance;
16 binsCount integer;
17 location1 integer;
18 location2 integer;
19 differ float;
20 indexInfo varchar2(2000);
21 idsmall integer;
22 rgbsmall varchar2(28671);
23 BEGIN
24
25 binsCount := 1;
26 location1 := 0;
27 location2 := 0;
28 differ := 0;
29 open c1;
30 --open c2;
31 fetch c1 into ch_rgb;
32
33
34 LOOP
35 IF binsCount > 4696 THEN
36 EXIT; -- exit loop immediately
37 END IF;
38
39 location2 := INSTR(ch_rgb,',',location1+1);
40 binBig(binsCount) := SUBSTR(ch_rgb,location1+1,location2-location1-1);
41 binsCount := binsCount+1;
42 location1 := location2;
43 END LOOP;
44
45
46 FOR someimg IN (SELECT id,colorhistogram_rgb FROM imageinfo where id <id_in)
47 LOOP
48 -- fetch c2 into idsmall,rgbsmall;
49 binsCount := 1;
50 location1 := 0;
51 location2 := 0;
52
53 LOOP
54 IF binsCount > 4696 THEN
55 EXIT; -- exit loop immediately
56 END IF;
57
58 location2 :=INSTR(ch_rgb,',',location1+1);
59 idsmall :=someimg.id;
60 rgbsmall :=someimg.colorhistogram_rgb;
61 binSmall(binsCount) := substr(rgbsmall,location1+1,location2-location1-1);
62
63 differ := differ+abs(binBig(binsCount)-binSmall(binsCount));
64
65 binsCount := binsCount+1;
66 location1 := location2;
67 END LOOP;
68 insert into CHRgbCompareIndices(idsmall ,idbig, distance)values(idsmall,id_in,differ);
69 END LOOP;
70 indexInfo :='';
71 FOR someid IN c2(id_in)
72 LOOP
73 idsmall :=someid.idsmall;
74 differ :=someid.distance;
75 indexInfo := indexInfo+someid.idsmall+','+someid.distance+',';
76 updateSmallNearID(idsmall,id_in,differ);
77 END LOOP;
78 update imageinfo set nearid=indexInfo where id=id_in;
79
80 commit;
81
82 close c1;
83
84 EXCEPTION
85 WHEN OTHERS THEN
86 raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLER
RM);
87 END;
88 /¾¯¸æ: ´´½¨µÄ¹ý³Ì´øÓбàÒë´íÎó¡£SQL> show error;
PROCEDURE CHRGBINDEXING ³öÏÖ´íÎó:LINE/COL ERROR
-------- -----------------------------------------------------------------
13/5 PL/SQL: SQL Statement ignored
15/69 PL/SQL: ORA-00904: "DISTANCE": invalid identifier
73/2 PL/SQL: Statement ignored
73/12 PLS-00364: loop index variable 'SOMEID' use is invalid
74/2 PL/SQL: Statement ignored
74/11 PLS-00364: loop index variable 'SOMEID' use is invalid
75/2 PL/SQL: Statement ignored
75/25 PLS-00364: loop index variable 'SOMEID' use is invalid
SQL>