create or replace procedure prc ( itablename in varchar2, -- 表名 istartnum in number, -- 起始记录数 iendnum in number, -- 终止记录数 icolumn in varchar, -- 排序项 ocursor out sys_refcursor -- 输出游标 ) as str_sql varchar2(2000); begin open ocursor for 'select * from (select a.*,row_number() over(order by '||icolumn||') n from '||itablename||' a)'||chr(10) ||'where n between '||istartnum||' and '||iendnum; end;
where rn between 25 and 35;
函数的输入参数有:tableName varchar2, keyName varchar2,startNum integer,entNum integer。变量的定义如下:tableName--表名称,keyName--排序的关键词,startNum--起始记录九,entNum--终止记录数。
函数的输出为查找出的记录数。
谢谢!
tableName varchar2,--表名
keyName varchar2,--排序关键字
startNum number,--数据起始位置
entNum number--数据终止位置
)
return number
is
v_name varchar2(5):='v1';--试图名称 用来存储结果集
p_str varchar2(255);
num number;--记录数
begin
p_str :='create or replace view ' || v_name || ' as select * from ' ||
'((select ' || tableName || '.*, row_number() over(order by ' || keyName || ') rn from ' || tableName ||
')) where rn between ' || startNum || ' and ' || entNum;
execute immediate p_str;
execute immediate 'select count(*) from ' || v_name into num;
return num;
end fun;
/
sql语句中只包含这两列就可以了
返回的结果集可供C#等等调用 具体的例子 搜索下吧 应该难度不大
(
itablename in varchar2, -- 表名
istartnum in number, -- 起始记录数
iendnum in number, -- 终止记录数
icolumn in varchar, -- 排序项
ocursor out sys_refcursor -- 输出游标
)
as
str_sql varchar2(2000);
begin
open ocursor for 'select * from (select a.*,row_number() over(order by '||icolumn||') n from '||itablename||' a)'||chr(10)
||'where n between '||istartnum||' and '||iendnum;
end;