分页打印

分页

第一种：

select * from a_matrix_navigation_map

where rowid not in(select rowid from a_matrix_navigation_map where rownum<=0) and rownum<=10

第二种：

SELECT * FROM

(SELECT A.*, rownum r FROM

(SELECT * FROM a_matrix_navigation_map) A

WHERE rownum <= 10) B

WHERE r > 0

===================================================================================1、首先建立一个包，用户创建一个游标类型

create or replace package pkg_query as

type cur_query is ref cursor;

end pkg_query;

2、创建存储过程

CREATE OR REPLACE PROCEDURE prc_query

(p_tableName in varchar2, --表名

p_strWhere in varchar2, --查询条件

p_orderColumn in varchar2, --排序的列

p_orderStyle in varchar2, --排序方式

p_curPage in out Number, --当前页

p_pageSize in out Number, --每页显示记录条数

p_totalRecords out Number, --总记录数

p_totalPages out Number, --总页数

v_cur out pkg_query.cur_query) --返回的结果集

IS

v_sql VARCHAR2(1000) := ''; --sql语句

v_startRecord Number(4); --开始显示的记录条数

v_endRecord Number(4); --结束显示的记录条数

BEGIN

--记录中总记录条数

v_sql := 'SELECT TO_NUMBER(COUNT(*)) FROM ' || p_tableName WHERE 1=1';

IF p_strWhere IS NOT NULL or p_strWhere <>'' THEN

v_sql := v_sql || p_strWhere;

END IF;

EXECUTE IMMEDIATE v_sql INTO p_totalRecords;

--验证页面记录大小

IF p_pageSize <0 THEN
p_pageSize := 0;END IF;

' ||

--根据页大小计算总页数

IF MOD(p_totalRecords,p_pageSize) = 0 THEN

p_totalPages := p_totalRecords / p_pageSize;

ELSE

p_totalPages := p_totalRecords / p_pageSize + 1;

END IF;

--验证页号

IF p_curPage <1 THEN

p_curPage := 1;

END IF;

IF p_curPage >p_totalPages THEN

p_curPage := p_totalPages;

END IF;

--实现分页查询

v_startRecord := (p_curPage - 1) * p_pageSize + 1;

v_endRecord := p_curPage * p_pageSize;

v_sql := 'SELECT * FROM (SELECT A.*, rownum r FROM ' ||

'(SELECT * FROM ' || p_tableName;

IF p_strWhere IS NOT NULL or p_strWhere <>'' THEN

v_sql := v_sql || ' WHERE 1=1' || p_strWhere;

END IF;

IF p_orderColumn IS NOT NULL or p_orderColumn <>'' THEN

v_sql := v_sql || ' ORDER BY ' || p_orderColumn || ' ' || p_orderStyle;

END IF;

v_sql := v_sql || ') A WHERE rownum <= ' || v_endRecord || ') B WHERE r >= '

|| v_startRecord; DBMS_OUTPUT.put_line(v_sql);

OPEN v_cur FOR v_sql;

END prc_query;

3、JAVA代码里取出结果集

String sql= \"{ call prc_query(?,?,?,?,?,?,?,?,?) }\";

CallableStatement call = con.prepareCall(sql);

// ……中间数据设置及注册省略

// 取出结果集

(ResultSet) call.getObject(9);

===================================================================================

创建一个package:CREATE OR REPLACE PACKAGE CURSPKG AS TYPE refCursorType IS REF CURSOR; procedure sp_Page(p_PageSize int, --每页记录数 p_PageNo int, --当前页码,从 1 开始 p_SqlSelect varchar2, --查询语句,含排序部分 p_SqlCount varchar2, --获取记录总数的查询语句 p_OutRecordCount out int, --返回总记录数 p_OutCursor out refCursorType);END;

oracle分页

----------

create or replace procedure zjx_emp(p_minnum number,p_maxnum number,p_rowcursor out sys_refcursor) as

begin

open p_rowcursor for select empno,ename from (select rownum r,emp.* from emp where rownumwhere t.r>=p_minnum;

end;

==============================

declare

cur sys_refcursor;

sempno number;

sename varchar2(20);

begin

zjx_emp(4,6,cur);---查找的只有4和5 行的内容没有第六行的内容。

---没有第六行是因为rownumloop

fetch cur into sempno,sename;

exit when cur%notfound;

dbms_output.put_line(sempno||' '||sename);

end loop;

end;

课本游标，过程，函数，包

create table products(

id number,

name varchar2(20),

price number

);

insert into products values(1,'zhang',2999);

insert into products values(2,'li',3015);

insert into products values(3,'zhao',7813);

select * from products;

declare

v_id products.id%type;

v_name products.name%type;

v_price products.price%type;

cursor v_product_cursor is select * from products order by id;

begin

open v_product_cursor;

loop

fetch v_product_cursor into v_id,v_name,v_price;

exit when v_product_cursor%notfound;

dbms_output.put_line('v_id'||v_id||' v_price'||v_price);

v_name'||v_name||'

end loop;

close v_product_cursor;

end;

declare

cursor v_product_cursor is select id,name,price from products order by id;

begin

for v_product in v_product_cursor loop

dbms_output.put_line(v_product.id||' '||v_product.name||'

'||v_product.price);

end loop;

end;

--过程练习

create or replace procedure update_porduct_price

(p_product_id in products.id%type,p_factor in number)

as

v_product_count integer;

begin

select count(*)

into v_product_count

from products

where id = p_product_id;

if v_product_count = 1 then

update products

set price = price * p_factor

where id = p_product_id;

commit;

end if;

end ;

--函数的例题

create function circle_area(p_radius in number)return number

as

v_pi number:=3.14159;

v_area number;

begin

v_area:=v_pi*power(p_radius,2);

return v_area;

end circle_area;

--包练习

create package product_package as

type t_ref_cursor is ref cursor;

function get_products_ref_cursor return t_ref_cursor;

procedure update_product_price(

p_product_id in products.id%type,

p_factor in number

);

end product_package;

drop package product_package

create package body product_package as

function get_products_ref_cursor return t_ref_cursor is

v_products_ref_cursor t_ref_cursor;

begin

open v_products_ref_cursor for select id,name,price from products;

return v_products_ref_cursor;

end get_products_ref_cursor;

procedure update_product_price(

p_prodcut_id in products.id%type,

p_factor in number

)as

v_product_count integer;

begin

select count(*) into v_product_count from products where id=p_product_id;

if v_product_count=1 then

update products set price=price * p_product_id;

commit;

end if;

exception when others then rollback;

end update_product_price;

end product_package;

select product_package.get_products_ref_cursor from dual;

select prodcut_package.update_product_price from dual;

--触发器例题

create table product_price_audit(

id integer,

old_price number(5,2),

new_price number(5,2)

);

create trigger before_product_price_update before update of price on products

for each row when (new.pricebegin

dbms_output.put_line('id'||:old.id);

dbms_output.put_line('old price'||:old.price);

dbms_output.put_line('new price'||:new.price);

insert into product_price_audit(id,old_price,new_price)

values(:old.id,:old.price,:new.price);

end before_prodcut_price_update;

drop trigger before_prodcut_price_update;

update products set price=2000 where id=1;

游标

--显示游标

declare

cursor empcursor is select empno,ename from emp;

myempno emp.empno%type;

myempname emp.ename%type;

begin

open empcursor;

loop

fetch empcursor into myempno,myempname;

exit when empcursor%notfound;

dbms_output.put_line(myempno||' '||myempname);

end loop;

close empcursor;

end;

--隐式游标 for loop在游标中的应用，不需要关闭和开启游标

declare

cursor cur_emp is select empno,ename from emp;

begin

for semp in cur_emp loop

dbms_output.put_line(semp.ename||semp.ename);

end loop;

end;

--REf游标

--强类型（规定返回值）

declare

--声明游标数据类型（指出返回类型）

type mytype is REF cursor return dept%rowtype;

--声明变量

sdept dept%rowtype;

--声明一个游标变量

mycursor mytype;

begin

open mycursor for select * from dept;

loop

fetch mycursor into sdept;

exit when mycursor%notfound;

dbms_output.put_line(sdept.daeptno||' '||sdept.dname||' '||sdept.loc);

end loop;

close mycursor;

end;

--弱类型（不规定返回类型

declare

--声明游标类型

type mytype is REF cursor;

--声明游标变量

mycursor mytype;

semp emp%rowtype;

begin

open mycursor for select * from emp;

loop

fetch mycursor into semp;

exit when mycursor%notfound;

dbms_output.put_line(semp.empno);

end loop;

close mycursor;

end;