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