关闭

oracle数据库存储过程范例(使用游标进行循环)

373人阅读 评论(0) 收藏 举报

使用游标进行循环。最后执行使用exec Counting;即可

create or replace PROCEDURE Counting AS
a_count number;
cursor authorListCursor is select ID from SCOPUS_AUTHOR ;
BEGIN
  delete from A_TEMP;
  for curAuthor in authorListCursor loop
    begin
--  找出某个作者有过合作的所有作者
      DECLARE cursor authorMapCursor is select distinct A.PUB_ID,A.AUTHOR_ID as aId,B.AUTHOR_ID as bId from SCOPUS_PUBLICATION_AUTHOR A,SCOPUS_PUBLICATION_AUTHOR B where A.AUTHOR_ID = curAuthor.id and B.PUB_ID = A.PUB_ID and B.AUTHOR_ID != curAuthor.id;
      begin
        for curMap in authorMapCursor loop
          begin
            select count(*) into a_count from A_Temp where ( AUTHOR1ID = curMap.aId and AUTHOR2ID = curMap.bId ) or ( AUTHOR1ID = curMap.bId and AUTHOR2ID = curMap.aId );
--          查看是否有合作记录 没有,则插入。 有,则更新Times  
            if(a_count = 0) then
              insert into A_TEMP(ID,AUTHOR1ID,AUTHOR2ID,TIMES) values (sequence_temp.NEXTVAL,curMap.aId,curMap.bId,1);
            else
              update A_TEMP set Times = (Times + 1) where ( AUTHOR1ID = curMap.aId and AUTHOR2ID = curMap.bId ) or ( AUTHOR1ID = curMap.bId and AUTHOR2ID = curMap.aId );
            end if;
          end;
        end loop;
      end;
    end;
  end loop;
END Counting;


0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:13843次
    • 积分:426
    • 等级:
    • 排名:千里之外
    • 原创:51篇
    • 转载:0篇
    • 译文:0篇
    • 评论:2条
    文章分类
    最新评论