create or replace procedure fenye(
tablename in varchar2, --emp_bak
pagesizes in number, --20
pagenow in number, --n
rowscount out number, -- 20*n
pagecount out number, --(conut()+20-1)/20 (总数量+单页数量-1)/单页数量 是整除
v_cur out pack_1.emp_cur )
is
v_sql varchar2(1000);
v_begin number(8):=(pagenow-1)*pagesizes+1;
v_end number(8):=pagenow*pagesizes;begin
v_sql := 'select * from (select a.*,rownum rn from (select* from tablename) a where rownum <=v_end) where rn>=v_begin';
open v_cur for v_sql;v_sql := 'select count(*) from tablename';
execute immediate v_sql into rowscount;
end;
declare
tablename varchar2 :=&t;
pagesizes number:=&num1;
pagenow number :=&num2;
rowscount number;
pagecount number;
v_cur pack_1.emp_cur;
v_emp emp%rowtype;
begin
fenye(tablename,pagesizes,pagenow);
open v_cur(v_begin);
loop
fetch v_cur into v_emp;
exit when v_cur%notfound;
dbms_output.put_line(v_emp.ename);
end loop;
close v_cur;
end;实在想不到了,。帮忙看哈,讲讲理由,谢谢了~~
tablename in varchar2, --emp_bak
pagesizes in number, --20
pagenow in number, --n
rowscount out number, -- 20*n
pagecount out number, --(conut()+20-1)/20 (总数量+单页数量-1)/单页数量 是整除
v_cur out pack_1.emp_cur )
is
v_sql varchar2(1000);
v_begin number(8):=(pagenow-1)*pagesizes+1;
v_end number(8):=pagenow*pagesizes;begin
v_sql := 'select * from (select a.*,rownum rn from (select* from tablename) a where rownum <=v_end) where rn>=v_begin';
open v_cur for v_sql;v_sql := 'select count(*) from tablename';
execute immediate v_sql into rowscount;
end;
declare
tablename varchar2 :=&t;
pagesizes number:=&num1;
pagenow number :=&num2;
rowscount number;
pagecount number;
v_cur pack_1.emp_cur;
v_emp emp%rowtype;
begin
fenye(tablename,pagesizes,pagenow);
open v_cur(v_begin);
loop
fetch v_cur into v_emp;
exit when v_cur%notfound;
dbms_output.put_line(v_emp.ename);
end loop;
close v_cur;
end;实在想不到了,。帮忙看哈,讲讲理由,谢谢了~~
create or replace package myPackage
as
type v_cursor is ref cursor;
end myPackage;
/
打印的存储过程
create or replace procedure println(name varchar2)
as
begin
dbms_output.put_line(name);
end;
/
create or replace procedure fenye(
PageSize number, --每页的条数
PageNow number, --当前页
myRows out number, --总条数
myPageCount out number, --总页数
p_cursor out myPackage.v_cursor
)
as
--定义sql语句 字符串
v_sql varchar2(1000);
--定义两个整数
v_begin number:=(PageNow-1)* PageSize+1;
v_end number:=PageNow * PageSize;
begin
--执行部分
v_sql:='select ename from (select t1.ename,rownum rn from (select ename from emp) t1 where rownum<='||v_end||') where rn>='||v_begin;
--把游标和sql关联
open p_cursor for v_sql;
--计算myRows和myPageCount
--组织一个sql
v_sql:='select count(*) from emp';
--执行sql,并把返回的值赋给myRows
execute immediate v_sql into myRows;
--计算myPageCount
if mod(myRows,PageSize)=0 then
myPageCount:=myRows/PageSize;
else
myPageCount:=floor(myRows/PageSize)+1;
end if;
end;
/declare
p_cursor myPackage.v_cursor;
v_e emp%rowtype;
myRows number;
myPageCount number;
begin
fenye(3,2,myRows,myPageCount,p_cursor);
dbms_output.put_line('总条数是:'||myRows);
dbms_output.put_line('总页数是:'||myPageCount);
dbms_output.put_line('该页中包含的员工有:');
loop
fetch p_cursor into v_e.ename;
exit when p_cursor%notfound;
dbms_output.put_line(v_e.ename);
end loop;
close p_cursor;
end;
/
pagesizes IN NUMBER, --20
pagenow IN NUMBER, --n
rowscount OUT NUMBER, -- 20*n
pagecount OUT NUMBER, --(conut()+20-1)/20
v_cur OUT SYS_REFCURSOR) AUTHID CURRENT_USER IS
v_sql VARCHAR2(1000);
v_begin NUMBER(8) := (pagenow - 1) * pagesizes + 1;
v_end NUMBER(8) := pagenow * pagesizes;
v_field VARCHAR2(30);
v_fields VARCHAR2(2000);
BEGIN
--通过数据字典表取得该表所有字段
v_sql := 'select t.COLUMN_NAME from user_tab_columns t where t.TABLE_NAME=upper(''' ||
tablename || ''')';
OPEN v_cur FOR v_sql;
LOOP
FETCH v_cur
INTO v_field;
EXIT WHEN v_cur%NOTFOUND;
v_fields := v_fields || ',' || v_field;
END LOOP;
CLOSE v_cur;
--取得指定页码的数据集
v_sql := 'select ' || ltrim(v_fields, ',') ||
' from (select a.*,rownum rn from (select* from ' || tablename ||
') a where rownum <=' || v_end || ') where rn>=' || v_begin;
OPEN v_cur FOR v_sql;
--取得总记录数
v_sql := 'select count(*) from ' || tablename;
EXECUTE IMMEDIATE v_sql
INTO rowscount;
--计算总页数
pagecount := ceil(rowscount / pagesizes);
END;
/--测试fenye存储过程
DECLARE
tablename VARCHAR2(30) := '&t';
pagesizes NUMBER := &num1;
pagenow NUMBER := &num2;
rowscount NUMBER;
pagecount NUMBER;
v_cur SYS_REFCURSOR;
v_emp emp%ROWTYPE;
BEGIN
fenye(tablename, pagesizes, pagenow, rowscount, pagecount, v_cur);
LOOP
FETCH v_cur
INTO v_emp;
EXIT WHEN v_cur%NOTFOUND;
dbms_output.put_line(v_emp.ename);
END LOOP;
CLOSE v_cur;
END;
/
非常感谢你,完成的已经很好了。但好像不能输入除了emp表以外的表。这个还能改进么??
你试试就知道不能针对别的表了。
代码里面有这个v_emp emp%ROWTYPE;
--测试fenye存储过程
DECLARE
tablename VARCHAR2(30) := '&t';
pagesizes NUMBER := &num1;
pagenow NUMBER := &num2;
rowscount NUMBER;
pagecount NUMBER;
v_cur SYS_REFCURSOR;
v_emp emp%ROWTYPE;
BEGIN
fenye(tablename, pagesizes, pagenow, rowscount, pagecount, v_cur); --这里是调用方法
LOOP
FETCH v_cur
INTO v_emp;
EXIT WHEN v_cur%NOTFOUND;
dbms_output.put_line(v_emp.ename);
END LOOP;
CLOSE v_cur;
END;
/
里面有个定义 v_emp emp%ROWTYPE;这个,我还能传入别的表????
唐兄的测试程序只是告诉你他的主程序好用,你脑袋被门挤过么。
fenye(tablename, pagesizes, pagenow, rowscount, pagecount, v_cur);