查询可以，但写成过程就出错。创建过程失败……

[icbm]

select cnum, marc_rec_no, slib.get_item_title(marc_rec_no) as title
from
(
select cnum, marc_rec_no
from
(
select count(*) as cnum, marc_rec_no
from
(
select i.marc_rec_no
from libsys.lend_lst s, libsys.item i, libsys.marc m
where s.prop_no = i.prop_no and i.marc_rec_no = m.marc_rec_no and m.marc_type='C'
union all
select i.marc_rec_no
from libsys.lend_hist s, libsys.item i, libsys.marc m
where s.prop_no_f = i.prop_no and i.marc_rec_no = m.marc_rec_no and m.marc_type='C'
)
group by marc_rec_no
)
order by cnum desc
)
where rownum <=1000;

这个查询单独运行正常。

我想创建一个过程，把查询的结果存到另一张表中去。


create or replace procedure update_top_all
is
cursor c1 is
select cnum, marc_rec_no, slib.get_item_title(marc_rec_no) as title
from
(
select cnum, marc_rec_no
from
(
select count(*) as cnum, marc_rec_no
from
(
select i.marc_rec_no
from libsys.lend_lst s, libsys.item i, libsys.marc m
where s.prop_no = i.prop_no and i.marc_rec_no = m.marc_rec_no and m.marc_type='C'
union all
select i.marc_rec_no
from libsys.lend_hist s, libsys.item i, libsys.marc m
where s.prop_no_f = i.prop_no and i.marc_rec_no = m.marc_rec_no and m.marc_type='C'
)
group by marc_rec_no
)
order by cnum desc
)
where rownum <=1000;
begin
delete from top_all;
commit;

for r in c1 loop

insert into slib.top_all(cnum, marc_rec_no, title)
values(r.cnum, r.marc_rec_no, r.title);
end loop;

commit;
end;

结果创建过程出错：

SQL>
SQL> create or replace procedure update_top_all
2 is
3 cursor c1 is
4 select cnum, marc_rec_no, slib.get_item_title(marc_rec_no) as title
5 from
6 (
7 select cnum, marc_rec_no
8 from
9 (
10 select count(*) as cnum, marc_rec_no
11 from
12 (
13 select i.marc_rec_no
14 from libsys.lend_lst s, libsys.item i, libsys.marc m
15 where s.prop_no = i.prop_no and i.marc_rec_no = m.marc_rec_no and m.marc_type='C'
16 union all
17 select i.marc_rec_no
18 from libsys.lend_hist s, libsys.item i, libsys.marc m
19 where s.prop_no_f = i.prop_no and i.marc_rec_no = m.marc_rec_no and m.marc_type='C'
20 )
21 group by marc_rec_no
22 )
23 order by cnum desc
24 )
25 where rownum <=1000;
26 begin
27 --truncate table slib.top_all;
28 delete from top_all;
29 commit;
30
31 for r in c1 loop
32
33 insert into slib.top_all(cnum, marc_rec_no, title)
34 values(r.cnum, r.marc_rec_no, r.title);
35 end loop;
36
37 commit;
38 end;
39 /

Warning: Procedure created with compilation errors

SQL> show errors
Errors for PROCEDURE SLIB.UPDATE_TOP_ALL:

LINE/COL ERROR
-------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
23/9 PLS-00103: Encountered the symbol "ORDER" when expecting one of the following: ) , with <an identifier> <a double-quoted delimited-identifier> group having intersect minus start union where connect The symbol ")" was substituted for "ORDER" to continue.
24/5 PLS-00103: Encountered the symbol ")" when expecting one of the following: , ; for

SQL>

请大侠指点。

[icbm]

我用的数据库是Oracle 8.1.5版本的。

[qiaozhiwei]

应该是在cursor中使用order的问题，但我认为是可以的，但现在我手边没有环境测试，你可以直接插入数据，而不需要用cursor吧
insert into slib.top_all(cnum, marc_rec_no, title)
select cnum, marc_rec_no, slib.get_item_title(marc_rec_no) as title
...

[plhui]

是呀,直接查询出插入就行,不用游标.

[leborety]

同意楼上直接插入

你是否有对slib.top_all的insert权限？

[mqmmx]

是不是没有为表加上别名，你把那两个子查询加个别名试试

[zhpsam109]

学习！

[rolandzhang]

cursor中用order没有任何问题，应经测试了。
cursor语句应该正确。
看看inser权限，字段类型

PLS-00103 found 'string' but expected one of the following: 'string'"},

Cause: This error message is from the parser. It found a token (language element) that is inappropriate in this context.

Action: Check previous tokens as well as the one given in the error message. The line and column numbers given in the error message refer to the end of the faulty language construct

[icbm]

一、两个子查询加上别名，还是同样的错误。

二、
insert into slib.top_all(cnum, marc_rec_no, title)
select cnum, marc_rec_no, slib.get_item_title(marc_rec_no) as title
...
还是同样的错误。

三、拥有对slib.top_all表的插入权限。

[icbm]

create or replace procedure update_top_all2
is
begin

delete from top_all;
commit;

select cnum, marc_rec_no, slib.get_item_title(marc_rec_no) as title
into slib.top_all(cnum, marc_rec_no, title)
from
(
select cnum, marc_rec_no
from
(
select count(*) as cnum, marc_rec_no
from
(
select i.marc_rec_no
from libsys.lend_lst s, libsys.item i, libsys.marc m
where s.prop_no = i.prop_no and i.marc_rec_no = m.marc_rec_no and m.marc_type='C'
union all
select i.marc_rec_no
from libsys.lend_hist s, libsys.item i, libsys.marc m
where s.prop_no_f = i.prop_no and i.marc_rec_no = m.marc_rec_no and m.marc_type='C'
) tb1
group by marc_rec_no
) tb2
order by cnum desc
)
where rownum <=1000;
commit;

end;

[icbm]

SQL> create or replace procedure update_top_all2
2 is
3 begin
4
5 delete from top_all;
6 commit;
7
8 select cnum, marc_rec_no, slib.get_item_title(marc_rec_no) as title
9 into slib.top_all(cnum, marc_rec_no, title)
10 from
11 (
12 select cnum, marc_rec_no
13 from
14 (
15 select count(*) as cnum, marc_rec_no
16 from
17 (
18 select i.marc_rec_no
19 from libsys.lend_lst s, libsys.item i, libsys.marc m
20 where s.prop_no = i.prop_no and i.marc_rec_no = m.marc_rec_no and m.marc_type='C'
21 union all
22 select i.marc_rec_no
23 from libsys.lend_hist s, libsys.item i, libsys.marc m
24 where s.prop_no_f = i.prop_no and i.marc_rec_no = m.marc_rec_no and m.marc_type='C'
25 ) tb1
26 group by marc_rec_no
27 ) tb2
28 order by cnum desc
29 )
30 where rownum <=1000;
31 commit;
32
33 end;
34 /

Warning: Procedure created with compilation errors

SQL> show errors
Errors for PROCEDURE SLIB.UPDATE_TOP_ALL2:

LINE/COL ERROR
-------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
28/9 PLS-00103: Encountered the symbol "ORDER" when expecting one of the following: ) , with group having intersect minus start union where connect The symbol ")" was substituted for "ORDER" to continue.
29/5 PLS-00103: Encountered the symbol ")" when expecting one of the following: , ; for

SQL>