17,086
社区成员
发帖
与我相关
我的任务
分享
create or replace procedure fenye(
--测试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;
/
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
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;
/