存储过程使用小结

写道

create or replace procedure maomao_1217
as

for_date date;
for_btntid number;
test int;

CURSOR cur_date IS
select distinct t.thedate from domain_score t;
CURSOR cur_btntid IS
select distinct t.btntid from domain_score t;
begin
for for_date in cur_date loop

for for_btntid in cur_btntid loop

insert into domain_score_tmp
(select a.* from (select * from domain_score t where t.btntid= for_btntid and t.thedate= for_date order by t.score desc) a
where rownum < 100);

end loop;

---dbms_output.put_line('This NUMBER is '||for_btntid);
end loop;
end maomao_1217;

 这是我同学写的一个存储过程，她问我为什么编译不过去，我开始也觉得奇怪，和网上写的都差不多，但为什么编译不过去后来仔细看了下是标红的地方有问题，实际上第一是这种利用for in loop 打开游标的方式是不需要提前定义每项的，第二，也是最重要的是，并不是说for_btntid 就是一个number型的编号，而for_btntid.btntid才是一个number型的变量，所以修改就很简单了

写道

create or replace procedure maomao_1217
as

for_date2 date;
for_btntid2 number;
test int;

CURSOR cur_date IS
select distinct t.thedate from domain_score t;
CURSOR cur_btntid IS
select distinct t.btntid from domain_score t;
begin
for for_date in cur_date loop

for for_btntid in cur_btntid loop

insert into domain_score_tmp
(select a.* from (select * from domain_score t where t.btntid=for_btntid.btntid and t.thedate=for_date.thedate order by t.score desc) a
where rownum < 100);

end loop;

---dbms_output.put_line('This NUMBER is '||for_btntid.btntid);
end loop;
end maomao_1217;