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

使用游标进行循环。最后执行使用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;


阅读更多
上一篇vmware安装centos以及实现win7和centos自由拖放文件
下一篇导出表空间名称为V3SNS的数据后,导入数据库步骤
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭