日期:2014-05-17  浏览次数:21043 次

oracle双层游标问题
CREATE OR REPLACE PROCEDURE q_MainPage_SpecialDrug AS
BEGIN
  DECLARE
       beginDate  varchar(50);
       endDate    varchar(50);
       CommonName varchar(50);
       thisfdaid  varchar(50);
       thisfdaid2  varchar(50);
       tempfdaids varchar2(8000);
      
         set beginDare:=to_char(sysdate,'yyyy-mm-dd');
         set beginDare:=to_char(sysdate,'yyyy-mm-dd')||'23:59:59';

           --定义游标
            CURSOR cr_cursor IS
                   select CommonName from PointRegulationSet;
            BEGIN
              open cr_cursor;

              fetch  cr_cursor into CommonName;

              WHILE(cr_cursor%found) LOOP

                begin
                  --获取绑定的企业串
                   EXECUTE IMMEDIATE 'SELECT Fdaids FROM PointRegulationSet WHERE CommonName='||to_char(CommonName)
                   INTO tempfdaids;
                   
                  EXECUTE IMMEDIATE 'select * from table(split('||to_char(tempfdaids) ||','',''))'
                  INTO thisfdaid2;
                  CURSOR fda_cursor IS

                         thisfdaid2;
                  BEGIN
                    OPEN fda_cursor;

                    fetch fda_cursor into thisfdaid;

                    WHILE(fda_cursor%FOUND) LOOP