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