游标

Create Or Replace procedure p_select_tcp5page
(
        v_strPageSize             In  Varchar2, --每页显示的数量
        v_strPageNum              In  Varchar2, --当前页数
        v_strFSupplierNumber      In  Varchar2, --供应商编码
        v_strFNeedGoodsDeptNumber In  Varchar2, --要货方
        v_strFOutGoodsDeptNumber  In  Varchar2, --出货地
        v_strFToGoodsPlaceNumber  In  Varchar2, --到货地
        v_strFPartNumber          In  Varchar2, --零件编号
        v_strDateTimeS            In  Varchar2, --对象日期起
        v_strDateTimeE            In  Varchar2, --对象日期止
        v_strLoginOrgID           In  Varchar2,  --用户组织ID
        v_ResulTset               Out Sys_Refcursor --定义游标    
)
Is
        v_sql Varchar2(10000);   --sql语句
        v_strBeginNum Number(10);  --起始页
        v_strEndNum   Number(10);  --结束页
        v_TotalSql  VARCHAR2(10000);   --统计总条数脚本
        v_strTotalNum Number(10); --总记录数
Begin   
        v_strBeginNum :=(v_strPageNum - 1) * v_strPageSize + 1;
        v_strEndNum := v_strPageNum * v_strPageSize;
       
        --实现分页查询
        v_sql :='SELECT 
           ROWNUM rownumr,
               tpc5.FObjectDate_1,
               tpc5.OrgUnitID,
               tpc5.FID,
               tpc5.FPartNumber,
               tpc5.FPartENName,
               tpc5.FInAndOut,
               tpc5.FWorkPlace,
               tpc5.FProduce,
               tpc5.FReceiverWay,
               tpc5.FGpartition,
               tpc5.FCYCLE,
               tpc5.FKeysToneMark,
               tpc5.FSNEP,
               tpc5.FPackNumber,
               tpc5.FToGoodsPlaceNumber,
               tpc5.FDeliveryDept,
               tpc5.FDischargePlace,
               tpc5.FCurMonth,
               tpc5.FObjectWeek_1,
               tpc5.FSum_PreMonthWeek_3,
               tpc5.FSum_PreMonthWeek_4,
               tpc5.FSum_CurMonthWeek_1,
               tpc5.FSum_CurMonthWeek_2,
               tpc5.FSum_CurMonthWeek_3,
               tpc5.FSum_CurMonthWeek_4,
               tpc5.FSum_CurMonthWeek_5,
               tpc5.FSum_NextMonthWeek_1,
               tpc5.FSum_NextMonthWeek_2,
               tpc5.FSum_NextMonthWeek_3,
               tpc5.FSum_NextMonthWeek_4,
               tpc5.FSum_NextMonthWeek_5,
               Tpc5.Createtime,
               Row_Number() Over(Order By Tpc5.Createtime Desc) As Rid,
               (Select Fname_L1
                  From T_Org_Orgunit
                 Where Tpc5.Orgunitid = Fid) As Orgname,
               (Select Personname
                  From V_Org_Personinfo
                 Where Tpc5.Createby = Userid) As Createbyname,
               (Select Fnumber From T_Po_Db_Parts Where Tpc5.Fpartid = Fid) As Fpartnum,
               (Select Fname_L2
                  From T_Po_Db_Parts
                 Where Tpc5.Fpartid = Fid) As Fpartename,
              SUPP.FNAME_L1 As Fsuppliername,
               supp.fnumber As Fsuppliernum,
               OutGoodsDept.Fname_L1 As Foutgoodsdeptname,
               OutGoodsDept.Fnumber As Foutgoodsdeptnum,
               ToGoodsPlace.Fnumber As Ftogoodsplacenum,
               ToGoodsPlace.Fname_L1 As Ftogoodsplacename,
              NeedGoodsDept.Fnumber As Fneedgoodsdeptnum,
               NeedGoodsDept.Fname_L1 As Fneedgoodsdeptname
          From T_Po_Cq_No4daily725 Tpc5
          left join   t_org_orgunit supp  on  Tpc5.Fsupplyid=supp.fid   
          left join   T_ORG_OrgUnit OutGoodsDept on Tpc5.foutgoodsdept=OutGoodsDept.Fid
          left join   t_PO_DB_ToGoodsPlace ToGoodsPlace on Tpc5.ftogoodsplace=ToGoodsPlace.Fid
          left join   t_PO_DB_Factory NeedGoodsDept on Tpc5.fneedgoodsdept=NeedGoodsDept.fid where 1=1';
          
          
          --组织ID
           If  v_strLoginOrgID Is Not Null Or v_strLoginOrgID <> '' Then 
             v_sql :=v_sql || 'and exists
                              (select ''X'' from 
                              (select  ou.fid from t_org_orgunit ou
                                        start with ou.fid='||v_strLoginOrgID||'  
                                        connect by prior ou.fid =ou.fparentid ) a 
                                        where a.fid=Tpc5.OrgUnitID)';
           End If;
                   
           --供应商
           If v_strFSupplierNumber Is Not Null Or v_strFSupplierNumber <> '' Then
             v_sql := v_sql || 'and supp.fnumber=''' || v_strFSupplierNumber||'''';
           END IF;
           
           --要货方
           If v_strFNeedGoodsDeptNumber Is Not Null Or v_strFNeedGoodsDeptNumber <> '' Then
             v_sql := v_sql || 'and NeedGoodsDept.FNumber=''' || v_strFNeedGoodsDeptNumber ||'''';
           END IF;
           
           --出货地
           If v_strFOutGoodsDeptNumber Is Not Null Or v_strFOutGoodsDeptNumber <> '' Then
             v_sql := v_sql || 'and OutGoodsDept.FNumber=''' || v_strFOutGoodsDeptNumber ||'''';
           END IF;
           
           --到货地
           If v_strFToGoodsPlaceNumber Is Not Null Or v_strFToGoodsPlaceNumber <> '' Then
             v_sql := v_sql || 'and ToGoodsPlace.FNUMBER=''' || v_strFToGoodsPlaceNumber ||'''';
           END IF;
           
           --零件号
           If v_strFPartNumber Is Not Null Or v_strFPartNumber <> '' Then 
             v_sql :=v_sql ||  'and Tpc5.FPartNumber like ''%'|| v_strFPartNumber|| '%''';
           End If; 
           
           --对象日期起
           If v_strDateTimeS Is Not Null Or v_strDateTimeS <> '' Then 
             v_sql :=v_sql || 'and Tpc5.FCurMonth||Tpc5.FObjectDate_1>=''' || v_strDateTimeS || '''';
           End If; 
           
           --对象日期止
           If v_strDateTimeE Is Not Null Or v_strDateTimeE <> '' Then 
             v_sql :=v_sql || 'and Tpc5.FCurMonth||Tpc5.FObjectDate_1<=''' || v_strDateTimeE || '''';
           End If; 
                              
           --获取总条数sql
           v_TotalSql := 'SELECT COUNT(1) FROM ('||v_sql||')';  
           
            --添加到总记录数
           Execute Immediate v_TotalSql into v_strTotalNum;   
            
           dbms_output.put_line();  
           
           
           /* --分页SQL
           v_sql :='Select TP.*,'||v_strTotalNum||' totalnum  From ('|| v_sql ||' And Rownum <= '|| v_strEndNum ||') TP Where rownumr >= '||v_strBeginNum;
 
           --打开游标       
           OPEN v_ResulTset FOR v_sql;*/
 
           End p_select_tcp5page;