求此存储过程详解，解释清楚了的就直接给分了

Question

procedure GetConStateAndType(state in varchar,
                               strWhere in varchar,
                               userids  in NVARCHAR2,
                               cur_HJ_PRODUCTLIST OUT SYS_BASE.BASECURSOR,
                               cur_HJ_CONTRACTSTATE OUT SYS_BASE.BASECURSOR
                               )
  as
  v_sql varchar(2000);
  p_sql varchar(2000);
  con_sql varchar(2000);
  begin  --组合工程查询条件
  v_sql:='SELECT CS_ID,CS_CONTRACTID,CS_PRJNAME,CS_PRJID,CON_AREA,CS_MONEY,CS_BROAD,CS_DOOR,CS_UNIT,
  CS_TRANSPORT,CS_DELIVERYDATE,CS_CERTIFICATE,
  CS_DSSTATE,CS_DSPRJDATE,CS_DSCPYDATE,CS_PDCSTATE,CS_PDCPRJDATE,CS_PDCCPYDATE,CS_DLVSTATE,CS_DLVDATE,
  CS_SENDADDRESS,CS_BALANCETYPE,CS_MARK,CS_RECEIVEUNIT,CS_DELIVERYPERSON,CS_TELEPHONE,CS_LUPPERSON,CS_LUPDATE,CON_CONTRACTID,CON_STATUE,CS_DSINSTR
  ,hbm_GetHJLog(CS_ID,2) as Log,CS_DSLOG FROM HJ_CONTRACTSTATE left join hj_contract on CS_CONTRACTID=CON_ID 
  where 1=1  '||strWhere ||' order by CS_DELIVERYDATE';
             
   --检索工程关联清册          
       if(state = '设计阶段') then
        p_sql := ' SELECT CS_ID,CS_PDCPRJDATE,CS_PDCSTATE,CS_DELIVERYDATE,CS_CERTIFICATE,PL_PROJECTID,PL_ID,PL_PLTID,PJ_NAME,PL_PROJCODE,PL_CODE,PL_NAME,PL_OWNER,GetUserFullName(PL_OWNER) as PL_OWNERFULLNAME,PL_STATUS,PL_JHSJWCRQ,PL_EFFECTTIME,PL_XXTYPE,PL_ZHENGSHU,PL_JHBBBX,PL_JHBBSK,PL_JHTHBX,PL_JHTHSK,PL_JHBCMJ,PL_JHMSSK,PL_JHMKSK,
                   PL_JHMSSL,PL_JHDYDMSK,PL_JHDYMSSK,PL_JHDYMKSK,PL_JHDYBBSK,PL_JHDYTHSK,PL_JHDYXH,PL_JHDYTS,PL_NOTE,PL_SJRZ,PL_GYSTATUS,PL_SCSTATUS,PL_ZJSTATUS,PL_FYSTATUS,PLT_PLTNAME,PL_CREATETIME,PL_ECOUSER,PL_ECODATE,PL_GGTIMES,LT_PTID,getuserfullname(pl_aduser) as pl_aduserfullname,getuserfullname(pl_jduser) as pl_jduserfullname FROM 
                   HBM_VIEW_SJJD_YH T WHERE 1=1';
          if(userids is null) then
           p_sql := p_sql||strWhere||' order by PL_PROJECTID,PL_PROJCODE,PL_PLTID';
                open cur_HJ_CONTRACTSTATE for
                   v_sql;
                open cur_HJ_PRODUCTLIST FOR
                   p_sql;
           else--设计者不为空时，先检索清册再检索工程 
               p_sql :=p_sql||userids||' order by PL_PROJECTID,PL_PROJCODE,PL_PLTID';
               open cur_HJ_PRODUCTLIST FOR
                p_sql;
               con_sql := 'SELECT distinct(CS_ID),CS_CONTRACTID,CS_PRJNAME,CS_PRJID,CON_AREA,CS_MONEY,CS_BROAD,CS_DOOR,CS_UNIT,CS_TRANSPORT,CS_DELIVERYDATE,CS_CERTIFICATE,
                       CS_DSSTATE,CS_DSPRJDATE,CS_DSCPYDATE,CS_PDCSTATE,CS_PDCPRJDATE,CS_PDCCPYDATE,CS_DLVSTATE,CS_DLVDATE,
                       CS_SENDADDRESS,CS_BALANCETYPE,CS_MARK,CS_RECEIVEUNIT,CS_DELIVERYPERSON,CS_TELEPHONE,CS_LUPPERSON,CS_LUPDATE,CON_CONTRACTID,CON_STATUE,CS_DSINSTR
                       ,hbm_GetHJLog(CS_ID,2) as Log,CS_DSLOG FROM HJ_CONTRACTSTATE ,hj_contract,hj_plconrel,hj_productlist where CS_CONTRACTID=CON_ID and cs_id = pc_csid
                       and pl_id = pc_plid '||strWhere ||userids||' order by CS_DELIVERYDATE';
              open cur_HJ_CONTRACTSTATE FOR
                   con_sql;
           end if;
       ELSE
          open cur_HJ_CONTRACTSTATE for
                v_sql;
          if(state = '生产滚动计划') then
            p_sql := 'SELECT CS_ID, CS_PDCPRJDATE,CS_PDCSTATE,CS_DELIVERYDATE,CS_CERTIFICATE,PL_ID,PL_PLTID,PL_PROJCODE,PJ_NAME,PL_NAME,PL_SJBCMJ,PL_SJMSS,PL_SJDYTS,PL_SCSTATUS,PL_SCJHWGRQ,PL_SCYOUXIANJI,PL_SCQIDONGRQ,PL_BCSK,PL_MSSK,PL_MKSK,PL_TSSL,
                      PL_TSJD,PL_PROCESSUSERID,GetUserFullName(PL_PROCESSUSERID) as PL_PROCESSUSERIDFULLNAME,PL_GYSTATUS,PL_PCEFFECTTIME,PL_GYREMARK,PL_SCREMRAK,PL_SCWGREMARK,PL_SCWCRQ,PL_QCJSERROR,PL_QCGYERROR,PL_JYFKINFO,PLT_PLTNAME,PL_CODE,PL_OWNER,GetUserFullName(PL_OWNER) as PL_OWNERFULLNAME,
                      PL_STATUS,PL_EFFECTTIME,PL_NOTE,PL_SCTIAOZHENGREN,GetUserFullName(PL_SCTIAOZHENGREN) as PL_SCTIAOZHENGRENFULLNAME, PL_SCTZRQ,PL_CGDWQK,PL_ECOUSER ,COLOR,LT_PTID,PB_BANXING,tuhao,PL_GGTIMES,LT_PTID FROM HBM_VIEW_SCGDJH_YH where 1=1'||strWhere||'ORDER BY PL_SCJHWGRQ,PL_SCYOUXIANJI,PL_PROJCODE,PL_NAME';
             
          elsif state = '板材生产车间进度' then
            p_sql:= ' SELECT CS_ID, CS_PDCPRJDATE,CS_PDCSTATE,CS_DELIVERYDATE,CS_CERTIFICATE,PL_ID,PL_PLTID,PL_PROJCODE,PJ_NAME,PL_NAME,PL_SCSTATUS,PB_BANXING,PL_SJBCMJ,PL_DANWEI,PL_SCJHWGRQ,PL_SCYOUXIANJI,PL_TSSL,PL_TSJD,PL_BCSK,PB_GCDWQK,PB_XLSTATUS,PB_XLJHWCRQ,PB_XLSJWCRQ,PB_CCSTATUS,PB_CCJHWCRQ,PB_CCSJWCRQ,PB_ZWSTATUS,PB_ZWJHWCRQ,PB_ZWSJWCRQ,PB_GYSTATUS,PB_GYJHWCRQ,PB_GYSJWCRQ,PB_FHSTATUS,PB_FHJHWCRQ,PB_FHSJWCRQ,PB_DBSTATUS,PB_DBJHWCRQ,PB_DBSJWCRQ,PB_CJREMARK,PLT_PLTNAME,PL_CODE,PL_OWNER,GetUserFullName(PL_OWNER) as PL_OWNERFULLNAME,PL_NOTE,PL_PROCESSUSERID, GetUserFullName(PL_PROCESSUSERID) as PL_PROCESSUSERIDFULLNAME,PL_GYREMARK,
                      PL_SCQIDONGRQ,PB_CJNAME,PL_ECOUSER ,COLOR ,PL_GGTIMES,LT_PTID FROM HBM_VIEW_BCCJJD_YH T1 where 1=1'||strWhere||'ORDER BY PL_SCJHWGRQ,PL_SCYOUXIANJI,PL_PROJCODE,PL_NAME';
          elsif state = '防火门生产车间进度'then
            p_sql := 'SELECT CS_ID, CS_PDCPRJDATE,CS_PDCSTATE,CS_DELIVERYDATE,CS_CERTIFICATE,PL_ID,PL_PLTID,PL_PROJCODE,PJ_NAME,PL_NAME,PL_SCSTATUS,PL_SJMSS,PL_DANWEI,PL_SCJHWGRQ,PL_SCYOUXIANJI,PF_MKBLINFO,PF_MKBLJHRQ,PF_MKBLSJRQ,PF_MSBLINFO,PF_MSBLJHRQ,PF_MSBLSJRQ,PF_MKHJJD,PF_MKHJJHRQ,PF_MKHJSJRQ,PF_NKCHJJD,PF_NKCHJJHRQ,PF_NKCHJSJRQ,PF_MKYQJD,PF_MKYQJHRQ,PF_MKYQSJRQ,PF_MSYQJD,PF_MSYQJHRQ,PF_MSYQSJRQ,PF_MSFHJD,PF_MSFHJHRQ,PF_MSFHSJRQ,PF_ZSJD,PF_ZSJHRQ,PF_ZSSJRQ,PF_MKZPJD,PF_MKZPJHRQ,PF_MKZPSJRQ,PF_MSZPJD,PF_MSZPJHRQ,PF_MSZPSJRQ,PF_DBJD,PF_DBJHRQ,PF_DBSJRQ,PF_CJREMARK,PLT_PLTNAME,PL_CODE,PL_OWNER,GetUserFullName(PL_OWNER) as PL_OWNERFULLNAME,PL_NOTE,
                      PL_PROCESSUSERID,GetUserFullName(PL_PROCESSUSERID) as PL_PROCESSUSERIDFULLNAME,PL_GYREMARK,PL_SCQIDONGRQ,PL_TSSL,PL_TSJD,PF_CJNAME,PL_ECOUSER,COLOR,tuhao,PL_GGTIMES FROM HBM_VIEW_FHMCJJD_YH where 1=1'||strWhere||'ORDER BY PL_SCJHWGRQ,PL_SCYOUXIANJI,PL_PROJCODE,PL_NAME';
          elsif state = '单元生产车间进度' then
            p_sql:='SELECT CS_ID, CS_PDCPRJDATE,CS_PDCSTATE,CS_DELIVERYDATE,CS_CERTIFICATE,PL_ID,PL_PLTID,PL_PROJCODE,PJ_NAME,PL_NAME,PL_SCSTATUS,PL_SJDYTS,PL_DANWEI,PL_SCJHWGRQ,PL_SCYOUXIANJI,PD_DPBLJD,PD_DPBLJHRQ,PD_DPBLSJRQ,PD_DPHJJD,PD_DPHJJHRQ,PD_DPHJSJRQ,PD_DPYQJD,PD_DPYQJHRQ,PD_DPYQSJRQ,PD_CPDWJD,PD_CPJHRQ,PD_CPSJRQ,PD_WBDYJD,PD_WBDYJHRQ,PD_WBDYSJRQ,PD_GXAZJD,PD_GXAZJHRQ,PD_GXAZSJRQ,PD_WJJAZJD,PD_WJJAZJHRQ,PD_WJJAZSJRQ,PD_QXDBJD,PD_QXDBJHRQ,PD_QXDBSJRQ,PD_BCDWJD,PD_BCDWJHRQ,PD_BCDWSJRQ,PD_MDWJD,PD_MDWJHRQ,PD_MDWSJRQ,PD_DLDWJD,
                    PD_DLDWJHRQ,PD_DLDWSJRQ,PD_CJREMARK,PD_JXMDWJD,PD_JXMDWJHRQ,PD_JXMDWSJRQ,PLT_PLTNAME,PL_CODE,PL_OWNER,GetUserFullName(PL_OWNER) as PL_OWNERFULLNAME,PL_NOTE,PL_PROCESSUSERID,GetUserFullName(PL_PROCESSUSERID) as PL_PROCESSUSERIDFULLNAME,
                    PL_GYREMARK,PL_SCQIDONGRQ,PL_TSSL,PL_TSJD,PD_CJNAME,PL_ECOUSER,COLOR,PL_GGTIMES FROM HBM_VIEW_DYCJJD_YH where 1=1'||strWhere||'ORDER BY PL_SCJHWGRQ,PL_SCYOUXIANJI,PL_PROJCODE,PL_NAME';
          end if;
            OPEN cur_HJ_PRODUCTLIST FOR
                          p_sql;
         
          
          open cur_HJ_PRODUCTLIST FOR
                p_sql;
   end if;  end GetConStateAndType;   现在自己负责的一个项目，我改一个bug，跟到这个存储过程里面了，目前只希望尽快把这个存储过程弄懂。
   由于oracle存储过程没有学过，但是又要尽快了解，所以就来这里问高手了。   就我目前的理解，这个存储先传进来3个参数，然后返回2个游标。
   这个存储过程最后貌似可以查到2个表中的数据，你们只用把个存储过程的每一行说清楚就行了，越详细越好，谢了。

Answer 1

if(state = '设计阶段') 
 and if(userids is null) 
   把p_sql 的查询结果放到cur_HJ_PRODUCTLIST ，v_sql的结果放到游标cur_HJ_CONTRACTSTATE 
 else
   把p_sql 的查询结果放到cur_HJ_PRODUCTLIST ，con_sql的结果放到游标cur_HJ_CONTRACTSTATE 
else
   把v_sql的查询结果放到cur_HJ_CONTRACTSTATE 
   if(state = '生产滚动计划')后后续的流程控制都是在拼 p_sql 
   把p_sql的查询结果放到cur_HJ_PRODUCTLIST 里
返回
cur_HJ_PRODUCTLIST和cur_HJ_CONTRACTSTATE