Oracle分页存储过程

不念不忘少年蓝@ 2022-06-14 10:10 263阅读 0赞

\--------以下为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;