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,--总页数 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",1000); //查询那几列 cmd.Parameters["p_tablecolumn"].Direction = ParameterDirection.Input; cmd.Parameters["p_tablecolumn"].Value = select_fileds; cmd.Parameters.Add("p_order",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",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,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,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,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;

Oracle分页存储过程

猜你在找的Oracle相关文章