17,086
社区成员
发帖
与我相关
我的任务
分享sql转oracle分页存储过程
这个是sqlserver的分页存储过程，帮忙转下oracle的分页存储过程，能测试通过，在线等。。。
CREATE PROCEDURE GetRecordFromPage
@tblName varchar(255), -- 表名
@RetColumns varchar(1000) = '*', -- 需要返回的列，默认为全部
@Orderfld varchar(255), -- 排序字段名
@PageSize int = 10, -- 页尺寸
@PageIndex int = 1, -- 页码
@IsCount bit = 0, -- 返回记录总数, 非 0 值则返回
@OrderType varchar(50) = 'asc', -- 设置排序类型, 非 asc 值则降序
@strWhere varchar(1000) = '' -- 查询条件 (注意: 不要加 where)
AS
declare @strSQL varchar(1000) -- 主语句
declare @strTmp varchar(300) -- 临时变量
declare @strOrder varchar(400) -- 排序类型
if @IsCount != 0 --执行总数统计
begin
if @strWhere != ''
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where' + @strWhere
else
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
end
else --执行查询操作
begin
if @OrderType != 'asc'
begin
set @strTmp = '<(select min'
set @strOrder = ' order by [' + @Orderfld +'] desc'
end
else
begin
set @strTmp = '>(select max'
set @strOrder = ' order by [' + @Orderfld +'] asc'
end
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @RetColumns + ' from ['
+ @tblName + '] where [' + @Orderfld + ']' + @strTmp + '(['
+ @Orderfld + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @Orderfld + '] from [' + @tblName + ']' + @strOrder + ') as tblTmp)'
+ @strOrder
if @strWhere != ''
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @RetColumns + ' from ['
+ @tblName + '] where [' + @Orderfld + ']' + @strTmp + '(['
+ @Orderfld + ']) from (select top ' + str((@PageIndex-1)*@PageSize) + ' ['
+ @Orderfld + '] from [' + @tblName + '] where (' + @strWhere + ') '
+ @strOrder + ') as tblTmp) and (' + @strWhere + ') ' + @strOrder
if @PageIndex = 1
begin
set @strTmp=''
if @strWhere!=''
set @strTmp = ' where (' + @strWhere + ')'
set @strSQL = 'select top ' + str(@PageSize) + ' ' + @RetColumns + ' from ['
+ @tblName + ']' + @strTmp + ' ' + @strOrder
end
end
exec (@strSQL)
...全文
请发表友善的回复…
发表回复
小灰狼W 2010-05-18
- 打赏
- 举报
自己试试吧，超出oracle的范围了
无非就是if else 的判断来拼字符串，什么语言都能轻松做到
上面的case when也是类似于if else的判断
无非就是if else 的判断来拼字符串，什么语言都能轻松做到
上面的case when也是类似于if else的判断
tommy326 2010-05-18
- 打赏
- 举报
那你会写什么语言
小灰狼W 2010-05-18
- 打赏
- 举报
java我不会写，可以去java区问问
tommy326 2010-05-18
- 打赏
- 举报
在java里把这个存储过程改成一个函数传参数进去，经过拼接返回一条sql语句出来，怎么写啊 8楼
tommy326 2010-05-18
- 打赏
- 举报
那用java写个动态拼接的sql语句啊
小灰狼W 2010-05-17
- 打赏
- 举报
...那个测试代码print是要在sqlplus或oracle sql developer上调用
不过只是为了测试，分页过程用游标返回结果集。就看你的程序怎么处理这个游标。c#我不会，java能够很好地处理
由于是拼动态语句，所以只能使用动态游标来返回结果。要不就在程序端写个过程来拼好语句再传入后台查询
不过只是为了测试，分页过程用游标返回结果集。就看你的程序怎么处理这个游标。c#我不会，java能够很好地处理
由于是拼动态语句，所以只能使用动态游标来返回结果。要不就在程序端写个过程来拼好语句再传入后台查询
tommy326 2010-05-17
- 打赏
- 举报
我要的是可以在程序里调的啊，都怪我没说清楚
ojuju10 2010-05-17
- 打赏
- 举报
他的代码只能在plsql plus里面调用
tommy326 2010-05-17
- 打赏
- 举报
在C#里调你这个存储过程谢谢，急用
tommy326 2010-05-17
- 打赏
- 举报
问下一楼的我怎么在程序里调用这个存储过程？
小灰狼W 2010-05-17
- 打赏
- 举报
改下
测试
CREATE or replace
PROCEDURE GetRecordFromPage(
tblName VARCHAR2, -- 表名
RetColumns VARCHAR2 DEFAULT 't.*', -- 需要返回的列，默认为全部
Orderfld VARCHAR2, -- 排序字段名
PageSize NUMBER DEFAULT 10, -- 页尺寸
PageIndex NUMBER DEFAULT 1, -- 页码
IsCount NUMBER DEFAULT 0, -- 返回记录总数, 非 0 值则返回
OrderType VARCHAR DEFAULT 'asc', -- 设置排序类型, 非 asc 值则降序
strWhere VARCHAR DEFAULT NULL , -- 查询条件 (注意: 不要加 where)
cur OUT sys_refcursor )
AS
strSQL VARCHAR(1000); -- 主语句
--strTmp VARCHAR(300); -- 临时变量
strOrder VARCHAR(400); -- 排序类型
BEGIN
IF IsCount != 0 THEN --执行总数统计
OPEN cur FOR 'select count(*) as Total from '||tblName||
CASE
WHEN strWhere IS NOT NULL THEN
' where ' ||strWhere
END;
ELSE --执行查询操作
strOrder := ' order by ' ||Orderfld||' ' ||
CASE orderType
WHEN 'asc' THEN
'asc'
ELSE
'desc'
END;
strSQL := 'select '||retcolumns||' from (select '||RetColumns||','
||'row_number()over('||strOrder||') rn from ' ||tblName ||' t'||
CASE
WHEN strWhere IS NOT NULL THEN
' where '||strWhere
END ||strOrder || ') where rn<=:1 and rn>:2';
OPEN cur FOR strsql USING PageSize*PageIndex,PageSize*(PageIndex-1);
END IF;
END;测试
variable c1 refcursor;
BEGIN
GetRecordFromPage(tblName=>'emp',RetColumns=>'empno,ename,sal',Orderfld=>'sal',PageSize=>5,
PageIndex=>2,strWhere=>'comm is null',OrderType=>'desc',cur=>:c1);
END;
/
print c1;
匿名块已完成
C1
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EMPNO ENAME SAL
---------------------- ---------- ----------------------
7782 CLARK 2450
7934 MILLER 1300
7876 ADAMS 1100
7900 JAMES 950
7369 SMITH 800
ojuju10 2010-05-17
- 打赏
- 举报
CREATE OR REPLACE PROCEDURE TABLEPAGE_SELECT(v_page_size int, --the size of a page of list
v_current_page int, --the current page of list
v_table_name varchar2, --the talbe name
v_order_field varchar2,--the order field
v_order_sequence varchar2,--the order sequence should by "_desc"or "_asc",_is blank.
--v_sql_select varchar2, --the select sql for procedure
--v_sql_count varchar2, --the count sql for procedure
--v_out_recordcount OUT int, --the num of return rows
p_cursor OUT refcursor_pkg.return_cursor) as
v_sql varchar2(3000); --the sql for select all rows of list
v_sql_count varchar2(3000); --the count sql for procedure
v_sql_order varchar2(2000); --the order of list
v_count int; -- the amount rows fo original list
v_endrownum int; --the end row num of the current page
v_startrownum int; --the start row num of the current page
BEGIN
----set the order of list
if v_order_field!='NO' then
v_sql_order :=' ORDER BY '|| v_order_field ||' '||v_order_sequence;
else
v_sql_order :='';
end if;
----catch the amount rows of list
v_sql_count:='SELECT COUNT(ROWNUM) FROM '||v_table_name;
execute immediate v_sql_count into v_count;
-- v_out_recordcount := v_count;
----set the value of start and end row
if v_order_sequence='desc' then
v_endrownum:=v_count-(v_current_page-1)*v_page_size;
v_startrownum:=v_endrownum - v_page_size + 1;
else
v_endrownum:= v_current_page * v_page_size;
v_startrownum := v_endrownum - v_page_size + 1;
end if;
----the sql for page slide
v_sql := 'SELECT * FROM (SELECT '||v_table_name||'.*, rownum rn FROM '||v_table_name||' WHERE rownum <= ' ||
to_char(v_endrownum) ||' '|| v_sql_order||') WHERE rn >= ' ||
to_char(v_startrownum)||' '||v_sql_order;
open p_cursor for v_sql;
END TABLEPAGE_SELECT;
备注：输入order by 的sqeuence是，应该为“ desc”或者“ asc”
若输入两个order by则，v_order_field=" a[sequence] ,order by b "
小灰狼W 2010-05-17
- 打赏
- 举报
CREATE
PROCEDURE GetRecordFromPage(
tblName VARCHAR2, -- 表名
RetColumns VARCHAR2 DEFAULT '*', -- 需要返回的列，默认为全部
Orderfld VARCHAR2, -- 排序字段名
PageSize NUMBER DEFAULT 10, -- 页尺寸
PageIndex NUMBER DEFAULT 1, -- 页码
IsCount NUMBER DEFAULT 0, -- 返回记录总数, 非 0 值则返回
OrderType VARCHAR DEFAULT 'asc', -- 设置排序类型, 非 asc 值则降序
strWhere VARCHAR DEFAULT NULL , -- 查询条件 (注意: 不要加 where)
cur OUT sys_refcursor )
AS
strSQL VARCHAR(1000); -- 主语句
--strTmp VARCHAR(300); -- 临时变量
strOrder VARCHAR(400); -- 排序类型
BEGIN
IF IsCount != 0 THEN --执行总数统计
OPEN cur FOR 'select count(*) as Total from '||tblName||
CASE
WHEN strWhere IS NOT NULL THEN
' where ' ||strWhere
END;
ELSE --执行查询操作
strOrder := ' order by ' ||Orderfld||' ' ||
CASE orderType
WHEN 'asc' THEN
'asc'
ELSE
'desc'
END;
strSQL := 'select '||retcolumns||' from(' || 'select t.*,rownum rn from' ||'(select '||RetColumns||' from ' ||tblName ||
CASE
WHEN strWhere IS NOT NULL THEN
' where '||strWhere
END ||strOrder || ')t where rownum<=:1 ' ||') where rn>:2';
OPEN cur FOR strsql USING PageSize*PageIndex,PageSize*(PageIndex-1);
END IF;
END;
