Oracle分页存储过程

--------以下为oracle通用分页存储过程代码，直接在oracle中执行即可。------------------------- 

---------------------------------------- 

create or replace package package_page as 
  type cursor_page is ref cursor; 
  Procedure proc_page( 
             p_tablename varchar2,                --表名emp e 
             p_tablecolumn varchar2,             --查询列e.id,e.ename,e.job 
             p_order varchar2,                         --排序e.ename desc 
             p_pagesize Number,                   --每页大小 
             p_curpage Number,                     --当前页 
             p_where varchar2,                       --查询条件e.ename like '%S%' 
             p_rowcount out Number,             --总条数,输出参数 
             p_pagecount out number,            --总页数 
             p_cursor out cursor_page);        --结果集 
end package_page; 


CREATE OR REPLACE Package Body package_page 
Is 
       --存储过程 
      Procedure proc_page( 
             p_tablename varchar2,                --表名emp e 
             p_tablecolumn varchar2,             --查询列e.id,e.ename,e.job 
             p_order varchar2,                         --排序e.ename desc 
             p_pagesize Number,                   --每页大小 
             p_curpage Number,                     --当前页 
             p_where varchar2,                       --查询条件e.ename like '%S%' 
             p_rowcount out Number,             --总条数,输出参数 
             p_pagecount out number,            --总页数 
             p_cursor out cursor_page          --结果集 
      ) 
      is 
            v_count_sql varchar2(2000); 
            v_select_sql varchar2(2000); 
      begin 
            --查询总条数 
            v_count_sql:='select count(*) from '||p_tablename; 
            --连接查询条件(''也属于is null) 
            if p_where is not null  then 
               v_count_sql:=v_count_sql||' where '||p_where; 
            end if; 
            --执行查询,查询总条数 
            execute immediate v_count_sql into p_rowcount; 

            --dbms_output.put_line('查询总条数SQL=>'||v_count_sql); 
            --dbms_output.put_line('查询总条数Count='||p_rowcount); 

             --得到总页数 
             if mod(p_rowcount,p_pagesize)=0 then 
                p_pagecount:=p_rowcount/p_pagesize; 
             else 
                p_pagecount:=p_rowcount/p_pagesize+1; 
             end if; 

            --如果查询记录大于0则查询结果集 
            if p_rowcount>0 and p_curpage>=1 and p_curpage<=p_pagecount then 

               --查询所有(只有一页) 
               if p_rowcount<=p_pagesize then 
                  v_select_sql:='select '||p_tablecolumn||' from '||p_tablename; 
                  if p_where is not null then 
                     v_select_sql:=v_select_sql||' where '||p_where; 
                  end if; 
                  if p_order is not null then 
                      v_select_sql:=v_select_sql||' order by '||p_order; 
                  end if; 
               elsif p_curpage=1 then  --查询第一页 
                  v_select_sql:='select '||p_tablecolumn||' from '||p_tablename; 
                  if p_where is not null then 
                     v_select_sql:=v_select_sql||' where '||p_where||' and 

rownum<='||p_pagesize; 
                  else 
                     v_select_sql:=v_select_sql||' where rownum<='||p_pagesize; 
                  end if; 
                  if p_order is not null then 
                      v_select_sql:=v_select_sql||' order by '||p_order; 
                  end if; 
               else      --查询指定页 
                  v_select_sql:='select * from (select '|| p_tablename || '.' || 

p_tablecolumn ||',rownum row_num from '|| p_tablename; 
                  if p_where is not null then 
                     v_select_sql:=v_select_sql||' where '||p_where; 
                  end if; 
                  if p_order is not null then 
                      v_select_sql:=v_select_sql||' order by '||p_order; 
                  end if; 
                  v_select_sql:=v_select_sql||') where row_num>'||((p_curpage-1) 

*p_pagesize)||' and row_num<='||(p_curpage*p_pagesize); 
               end if; 
               --执行查询 
               dbms_output.put_line('查询语句=>'||v_select_sql); 
               open p_cursor for v_select_sql; 
            else 
               --dbms_output.put_line('查询语句=>'||'select * from '||p_tablename||' where 

1!=1'); 
               open p_cursor for 'select * from '||p_tablename||' where 1!=1'; 
            end if; 

      end proc_page; 
end package_page; 


--------以上为oracle通用分页存储过程代码，直接在oracle中执行即可。------------------------- 

---------------------------------------- 


-------------------------------------------------------------------------------------------- 
-------------------执行存储过程的例子--------------------------------------------- 


declare 
       v_rowcount number(5,0); 
       v_pagecount number; 
       v_cursor package_page.cursor_page; 
begin  
       package_page.proc_page('TS_Student_Info','*','code desc',15,2,' 1=1 

',v_rowcount,v_pagecount,v_cursor); 
       dbms_output.put_line(v_rowcount); 
       dbms_output.put_line(v_pagecount); 
end; 

---------------------------------------------------------------------------------- 
--------------------------------------------------------------------------------- 




----------------------------------------------------------------------------------------- 
---------------------------下面是c#部分代码，仅供参考----------------------------------- 

    /// <summary> 
    /// 调用存储过程实现快速分页 
    /// </summary> 
    /// <param name="mTableName">表名</param> 
    /// <param name="select_fileds">查询的字段，比如：*或者code,name</param> 
    /// <param name="mOrderField">排序字段，比如：code desc或者code asc</param> 
    /// <param name="mPageSize">每页大小</param> 
    /// <param name="mPageIndex">查询第几页</param> 
    /// <param name="mTerm">查询条件，比如： 1=1 and code = 15</param> 
    /// <returns>返回的是游标形式的数据集</returns> 
    public DataSet QuickPage(string mTableName,string select_fileds, string mOrderField, int 

mPageSize, int mPageIndex, string mTerm) 
    { 
        //注意参数名称必须与数据库中存储过程的参数名称一致。 
        OracleConnection conn = new OracleConnection(ConfigurationSettings.AppSettings 

["Conn_Oracle"].ToString()); 
        OracleCommand cmd = new OracleCommand(); 
        cmd.Connection = conn; 
        cmd.CommandText = "package_page.proc_page"; 
        cmd.CommandType = CommandType.StoredProcedure; 

        cmd.Parameters.Add("p_tablename", OracleType.VarChar, 50);    //表  名 
        cmd.Parameters["p_tablename"].Direction = ParameterDirection.Input; 
        cmd.Parameters["p_tablename"].Value = mTableName; 

        cmd.Parameters.Add("p_tablecolumn", OracleType.VarChar, 1000);   //查询那几列 
        cmd.Parameters["p_tablecolumn"].Direction = ParameterDirection.Input; 
        cmd.Parameters["p_tablecolumn"].Value = select_fileds; 

        cmd.Parameters.Add("p_order", OracleType.VarChar, 100);   //排序字段 
        cmd.Parameters["p_order"].Direction = ParameterDirection.Input; 
        cmd.Parameters["p_order"].Value = mOrderField; 

        cmd.Parameters.Add("p_pagesize", OracleType.Int32);    //每页数量 
        cmd.Parameters["p_pagesize"].Direction = ParameterDirection.Input; 
        cmd.Parameters["p_pagesize"].Value = mPageSize; 

        cmd.Parameters.Add("p_curpage", OracleType.Int32);    //第几页 
        cmd.Parameters["p_curpage"].Direction = ParameterDirection.Input; 
        cmd.Parameters["p_curpage"].Value = mPageIndex; 

        cmd.Parameters.Add("p_where", OracleType.VarChar, 1000);  //过滤条件 
        cmd.Parameters["p_where"].Direction = ParameterDirection.Input; 
        cmd.Parameters["p_where"].Value = mTerm; 

        cmd.Parameters.Add("p_rowcount", OracleType.Int32);   //返回的总记录数 
        cmd.Parameters["p_rowcount"].Direction = ParameterDirection.Output; 
        cmd.Parameters["p_rowcount"].Value = 0; 

        cmd.Parameters.Add("p_pagecount", OracleType.Int32);   //总页数 
        cmd.Parameters["p_pagecount"].Direction = ParameterDirection.Output; 
        cmd.Parameters["p_pagecount"].Value = 0; 

        cmd.Parameters.Add("p_cursor", OracleType.Cursor);   //返回的游标 
        cmd.Parameters["p_cursor"].Direction = ParameterDirection.Output; 

        DataSet Ds = new DataSet(); 
        OracleDataAdapter adapter = new OracleDataAdapter(cmd); 
        adapter.Fill(Ds); 
        conn.Close(); 

        ////总记录数 
        //RecordCount = int.Parse(cmd.Parameters["TotalCount"].Value.ToString()); 

        return Ds; 
    } 
    
    
    
    
    
    
    
    
    CREATE OR REPLACE PROCEDURE DICTIONARY_ADD 
( 
m_dict_code varchar2, 
m_parent_id  varchar2, 
m_dict_name varchar2, 
m_dict_inuse integer, 
m_dict_customer_id varchar2, 
m_dict_customer_name varchar2 
) 
as 

max_code varchar2(100); 
dict_order integer; 


begin 



--如果是根节点，就是 0 == 0 
if m_parent_id ='0' then 
    select nvl(max(to_number(dict_code))+1,0) into max_code from dictionary where parent_id 

= '0'; 
else 
    select m_parent_id||substr((1000+nvl(max(to_number(dict_code)),0)+1),-3) into max_code 

from dictionary  where parent_id = m_parent_id; 
end if; 



   select nvl(max(dict_order),0)+1 into dict_order from dictionary where parent_id = 

m_parent_id; 
   insert into dictionary 

(dict_name,dict_code,dict_order,parent_id,dict_inuse,dict_customer_id,dict_customer_name) 
   values 
     

(m_dict_name,max_code,dict_order,m_parent_id,m_dict_inuse,m_dict_customer_id,m_dict_customer 

_name); 
   
   dbms_output.put_line(max_code); 
   dbms_output.put_line(dict_order); 

end DICTIONARY_ADD; 







CREATE OR REPLACE PROCEDURE TS_TEMP_ACCOMMODATION_BATCH_EX 
AS 


ISNO VARCHAR2(50); 
INFOCOUT integer; 

m_building_id varchar2(50); 
m_floor integer; 
m_room_id varchar2(50); 
m_bed_id varchar2(50); 
  ----住宿的时候占用了几个床位 
m_bed_count integer; 

--------------------------------------------循环---------------------------- 
begin 
for emprow in  (select code,student_id,student_name from TS_Temp_Accommodation_Exit order by 

code) loop 
   
   --0表示没有退宿信息错误出现。 
   ISNO:='0' ; 
    
   --dbms_output.put_line(emprow.code); 
      
  --查询学号姓名是否一致 
if ISNO= '0' then 
SELECT  count(*) into INFOCOUT FROM TS_Student_Info WHERE (student_id = 

emprow.student_id) AND (student_name = emprow.student_name); 
if INFOCOUT = 0 then 
update TS_Temp_Accommodation_Exit set reason = '导入学号、姓名与数据 

库中的学号和姓名不一致！' where code = emprow.code; 
isno:='1'; 
    end if; 
end if; 

------检查该学号是否存在住宿信息 
if ISNO='0' then 
SELECT count(*) into INFOCOUT FROM TS_Accommodation WHERE student_id = 

emprow.student_id and doublestate = 14001; 

if INFOCOUT = 0 then 
update TS_Temp_Accommodation_Exit set reason = '该学号的住宿信息不存 

在！' where code = emprow.code; 
isno:='1'; 
end if; 
end if; 

  ---获取该学号住宿时候用了几个床位 
  if ISNO='0' then 
---获取该人住在那个房间的那个床位上、该人住宿的时候占用了几个床位 
SELECT building_id,floor,room_id,bed_id,bed_count into 

m_building_id,m_floor,m_room_id,m_bed_id,m_bed_count  FROM TS_Accommodation WHERE student_id 

= emprow.student_id and doublestate = 14001; 

----更新床位占用信息、更新床位剩余数量 
update Ts_Room_Manage_Info Set occupy_bed =replace 

(occupy_bed,m_bed_id||'|',''),surplus_bed_sum = surplus_bed_sum + m_bed_count where 

building_id = m_building_id and building_floor = m_floor and room_id = m_room_id; 

--退宿 
update TS_Accommodation set state =13004 where student_id = 

emprow.student_id and doublestate = 14001; 


--=======================================下面是双床位的处理 

================== 
------检查该学号是否存在双床位 
SELECT count(*) into INFOCOUT FROM TS_Accommodation WHERE student_id = 

emprow.student_id  and doublestate = 14002; 
if INFOCOUT > 0 then 
---获取该人住在那个房间的那个床位上、该人住宿的时候占用了几个床位 
SELECT building_id,floor,room_id,bed_id,bed_count into 

m_building_id,m_floor,m_room_id,m_bed_id,m_bed_count  FROM TS_Accommodation WHERE student_id 

= emprow.student_id and doublestate = 14002; 
----更新床位占用信息、更新床位剩余数量 
update Ts_Room_Manage_Info Set occupy_bed =replace 

(occupy_bed,m_bed_id||'|',''),surplus_bed_sum = surplus_bed_sum + m_bed_count where 

building_id = m_building_id and building_floor = m_floor and room_id = m_room_id; 
--退宿 
update TS_Accommodation set state =13004 where student_id = 

emprow.student_id and doublestate = 14002; 
end if; 
---====================================以上是双床位的处理================== 



---删除该条退宿信息 
delete from TS_Temp_Accommodation_Exit WHERE code = emprow.code; 

end if; 
---insert into config (pa_name,pa_value) values ('1','1'); 
------------------------------------------------------------------------------------------- 

--------------------------- 
end loop; 
end TS_TEMP_ACCOMMODATION_BATCH_EX;