米琪卡哇伊
** 综合练习:设计一个存储过程,实现oracle中任意表数据的分页综合案例预备create or replace package type_packistype cur_type is ref cursor;end;create or replace procedure all_data_pro(v_tableName varchar2,v_all_data out type_pack.cur_type,v_all_records out number)isv_sql varchar2(200);v_sql1 varchar2(200);beginv_sql:='select * from '||v_tableName;open v_all_data for v_sql;v_sql1:='select count(*) from '||v_tableName;execute immediate v_sql1 into v_all_records;--立即执行一条SQL语句end;语句块调用以上存储过程declarev_all_data type_pack.cur_type;v_all_records number;v_row_data emp%rowtype;beginall_data_pro('emp',v_all_data,v_all_records);fetch v_all_data into v_row_data;dbms_output.put_line(v_row_data.ename);dbms_output.put_line(v_all_records);end;综合案例实现:create or replace procedure page_access_pro(v_tableName varchar2,v_page number,v_pagesize number,v_page_data out type_pack.cur_type,v_maxpage out number)isv_page_sql varchar2(200);v_all_records_sql varchar2(100);v_count_records number;v_begin number;v_end number;beginv_begin:=(v_page-1)*v_pagesize;v_end:=v_page*v_pagesize;v_page_sql:='select empno,ename,job,mgr,HIREDATE,SAL,COMM,DEPTNO from (select e.*, rownum rn from(select * from '||v_tableName||')e where rownum<='||v_end||') where rn>'||v_begin;dbms_output.put_line(v_page_sql);open v_page_data for v_page_sql;v_all_records_sql:='select count(*) from '||v_tableName;execute immediate v_all_records_sql into v_count_records; --立即执行v_maxpage:=ceil(v_count_records/v_pagesize);end;测试调用(注意,抓取游标中数据,类型的问题)declarev_page_data type_pack.cur_type;v_maxpage number;v_data_row emp%rowtype;beginpage_access_pro('emp',1,2,v_page_data,v_maxpage);dbms_output.put_line('最大页数是:'||v_maxpage);fetch v_page_data into v_data_row;while v_page_data%found loopdbms_output.put_line(v_data_row.ename);fetch v_page_data into v_data_row;end loop;close v_page_data;end;