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;