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 errorsSQL> 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> 请大侠指点。
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 errorsSQL> 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> 请大侠指点。
insert into slib.top_all(cnum, marc_rec_no, title)
select cnum, marc_rec_no, slib.get_item_title(marc_rec_no) as title
...
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
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表的插入权限。
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;
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 errorsSQL> 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>