oracle双层游标问题

Question

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
                    
                    BEGIN
                      insert into SpecialDrugStatistic(cEnterpriseCode,
                         cName,
                         fdaid,
                         commonName,
                         kc_sl,
                         xs_sl,
                         ys_sl,
                         yh_sl,
                         countDate)
                    select a.cEnterpriseCode,a.cName,a.FDAID, to_char(CommonName) commonName,a.kc,a.xs,a.ys,a.yh,beginDate countDate
                    from
                    (select a.cEnterpriseCode,cname,f.FDAID,isnull(kcsl,0) kc,isnull(xssl,0) xs,isnull(yssl,0) ys,isnull(yhsl,0) yh
                     from
                     (select a.cEnterpriseCode,kcsl,xssl,yssl,yhsl
                      from
                      (select f.cEnterpriseCode from typkc t join fdaenterprise f on t.cEnterpriseCode=f.cEnterpriseCode
                       where 1=1 AND sptym like '%'  to_char(CommonName)  '%' AND sp_datetime >= to_char(beginDate) AND sp_datetime <= to_char(endDate) AND f.fdaid = to_char(thisfdaid)
                       union
                       select f.cEnterpriseCode from txsjl t join fdaenterprise f on t.cEnterpriseCode=f.cEnterpriseCode
                       where 1=1 AND xs_tym like '%' to_char(CommonName) '%' AND Xs_datetime >= to_char(beginDate) AND Xs_datetime <= to_char(endDate) AND f.fdaid = to_char(thisfdaid)
                       union
                       select f.cEnterpriseCode from tysjl t join fdaenterprise f on t.cEnterpriseCode=f.cEnterpriseCode
                       where 1=1 AND ys_tym like '%'  to_char(CommonName)  '%' AND Ys_datetime >=to_char(beginDate) AND Ys_datetime <= to_char(endDate) AND f.fdaid = to_char(thisfdaid)
                       union
                       select f.cEnterpriseCode from tyhjl t join fdaenterprise f on t.cEnterpriseCode=f.cEnterpriseCode
                       where 1=1 AND yh_tym like '%'  to_char(CommonName) '%' AND Yh_datetime >= to_char(beginDate) AND Yh_datetime <= to_char(endDate) AND f.fdaid = to_char(thisfdaid) ) a
                      left join
                      (select cEnterpriseCode,sum(spkcsl) kcsl from typkc
                       where 1=1 AND sptym like '%' to_char(CommonName)  '%' AND sp_datetime >= to_char(beginDate) AND sp_datetime <= to_char(endDate)
                       group by cEnterpriseCode) b
                      on a.cEnterpriseCode=b.cEnterpriseCode
                      left join
                      (select cEnterpriseCode,sum(xs_sl) xssl from txsjl
                       where 1=1 AND xs_tym like '%' to_char(CommonName) '%' AND Xs_datetime >= to_char(beginDate) AND Xs_datetime <= to_char(endDate)
                       group by cEnterpriseCode) c
                      on a.cEnterpriseCode=c.cEnterpriseCode
                      left join
                      (select cEnterpriseCode,sum(ys_sl) yssl from tysjl
                       where 1=1 AND ys_tym like '%'  to_char(CommonName) '%' AND Ys_datetime >= to_char(beginDate) AND Ys_datetime <= to_char(endDate)
                       group by cEnterpriseCode) d
                      on a.cEnterpriseCode=d.cEnterpriseCode
                      left join
                      (select cEnterpriseCode,sum(yh_yhsl) yhsl from tyhjl
                       where 1=1 AND yh_tym like '%'  to_char(CommonName) '%' AND Yh_datetime >= to_char(beginDate) AND Yh_datetime <= to_char(endDate)
                       group by cEnterpriseCode) e
                       on a.cEnterpriseCode=e.cEnterpriseCode ) a
                     left join fdaenterprise f on a.cEnterpriseCode=f.cEnterpriseCode) a
                     order by a.FDAID asc;
                     FETCH fda_cursor into thisfdaid;
                     end;
                     end loop;
                     Close fda_cursor;              fetch  cr_cursor into CommonName;              end;
              end;
              end loop;                --关闭游标
              Close cr_cursor;            END;       END;
END;
红色部分报错：PROCEDURE DRUG_RR.Q_MAINPAGE_SPECIALDRUG 编译错误错误：PLS-00103: 出现符号 "FDA_CURSOR"在需要下列之一时：
        := . ( @ % ;
行：31
文本：CURSOR fda_cursor ISfda_cursor 是第二个游标，要根据第一个游标中获取的CommonName循环获取第二个游标中的值。
小菜鸟一枚，求高手指点

Answer 1

EXECUTE IMMEDIATE 'select * from table(split('||to_char(tempfdaids) ||','',''))' INTO thisfdaid2;
这句改成thisfdaid2 := 'select * from table(split('||to_char(tempfdaids) ||','',''))';

Answer 2

很多语法是错误的。
CREATE OR REPLACE PROCEDURE q_MainPage_SpecialDrug AS
  beginDate   varchar(50);
  endDate     varchar(50);
  CommonName  varchar(50);
  thisfdaid   varchar(50);
  thisfdaid2  varchar(50);
  tempfdaids  varchar2(8000);
  fda_cursor  REF CURSOR;
BEGIN
  beginDare := to_char(sysdate,'yyyy-mm-dd')||'23:59:59';
  
  FOR commonNames IN (select CommonName from PointRegulationSet) LOOP
    EXECUTE IMMEDIATE 'SELECT Fdaids FROM PointRegulationSet WHERE CommonName='''||commonNames.CommonName||'' INTO tempfdaids;
    OPEN fda_cursor FOR 'select * from table(split('||to_char(tempfdaids) ||','',''))';
      ...
    Close fda_cursor;
  END LOOP;
END q_MainPage_SpecialDrug;

Answer 3

求教大神啊，我只会sqlserver的，oracle一点也不会的，真翻译不出来啊。
按你的格式修改以后，“fda_cursor  REF CURSOR;”这里报错
 编译错误错误：PLS-00201: 必须声明标识符 'CURSOR'
行：7
文本：fda_cursor  REF CURSOR;错误：PL/SQL: Item ignored
行：7
文本：fda_cursor  REF CURSOR;错误：PLS-00320: 此表达式的类型声明不完整或格式不正确
行：18
文本：OPEN fda_cursor FOR 'select * from table(split('||to_char(tempfdaids) ||','',''))';错误：PL/SQL: Statement ignored
行：18
文本：OPEN fda_cursor FOR 'select * from table(split('||to_char(tempfdaids) ||','',''))';错误：PLS-00320: 此表达式的类型声明不完整或格式不正确
行：72
文本：Close fda_cursor;错误：PL/SQL: SQL Statement ignored
行：72
文本：Close fda_cursor;

Answer 4

fda_cursor  REF CURSOR;
想要改成
type cur is REF CURSOR;
fda_cursor cur;这样应该就可以了，QQ用的少

Answer 5

FOR commonNames IN (select CommonName from PointRegulationSet) LOOP 
用for 循环就能实现我之前所定义的第一次游标
 CURSOR cr_cursor IS
                   select CommonName from PointRegulationSet;
            BEGIN
              open cr_cursor;              fetch  cr_cursor into CommonName;              WHILE(cr_cursor%found) LOOP这个功能么