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_PageRecordCount out int, --返回当前页记录数 p_OutCursor out refCursorType); END;CREATE OR REPLACE PACKAGE BODY "CURSPKG" as procedure sp_Page(p_PageSize int, --每页记录数 p_PageNo int, --当前页码,从 1 开始 p_SqlSelect varchar2, --查询语句,含排序部分 p_SqlCount varchar2, --获取记录总数的查询语句 p_OutRecordCount out int, --返回总记录数 p_PageRecordCount out int, --返回当前页记录数 p_OutCursor out refCursorType) is v_sql varchar2(3000); v_countsql varchar2(3000); v_count int; v_pagecount int; v_heiRownum int; v_lowRownum int; begin ----取记录总数 execute immediate p_SqlCount into v_count; p_OutRecordCount := v_count;
v_heiRownum := p_PageNo * p_PageSize; v_lowRownum := v_heiRownum - p_PageSize + 1; ----取当前页记录数 v_countsql := 'SELECT COUNT(1) FROM (SELECT A.*, rownum rn FROM (' || p_SqlSelect || ') A WHERE rownum <= ' || to_char(v_heiRownum) || ') B WHERE rn >= ' || to_char(v_lowRownum); execute immediate v_countsql into v_pagecount; p_PageRecordCount := v_pagecount;
----执行分页查询 v_sql := 'SELECT * FROM (SELECT A.*, rownum rn FROM (' || p_SqlSelect || ') A WHERE rownum <= ' || to_char(v_heiRownum) || ') B WHERE rn >= ' || to_char(v_lowRownum); --注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn OPEN p_OutCursor FOR v_sql;
end sp_Page;end;
create or replace procedure storage_days(goods_id_in goods.id%type) as v_days_1 number(5); v_days_2 number(5); v_days_3 number(5); begin select storage_days into v_days_1 from warehouse_1 where goods_id=goods_id_in;
select storage_days into v_days_2 from warehouse_2 where goods_id=goods_id_in;
select storage_days into v_days_3 from warehouse_3 where goods_id=goods_id_in;
dbms_output.put_line('The total number of days stored:' ||v_days_1+v_days_2+v_days_3); exception when others then dbms_output.put_line(sqlerrm); end storage_days;
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_PageRecordCount out int, --返回当前页记录数
p_OutCursor out refCursorType);
END;CREATE OR REPLACE PACKAGE BODY "CURSPKG" as procedure sp_Page(p_PageSize int, --每页记录数
p_PageNo int, --当前页码,从 1 开始
p_SqlSelect varchar2, --查询语句,含排序部分
p_SqlCount varchar2, --获取记录总数的查询语句
p_OutRecordCount out int, --返回总记录数
p_PageRecordCount out int, --返回当前页记录数
p_OutCursor out refCursorType) is
v_sql varchar2(3000);
v_countsql varchar2(3000);
v_count int;
v_pagecount int;
v_heiRownum int;
v_lowRownum int;
begin ----取记录总数
execute immediate p_SqlCount
into v_count;
p_OutRecordCount := v_count;
v_heiRownum := p_PageNo * p_PageSize;
v_lowRownum := v_heiRownum - p_PageSize + 1;
----取当前页记录数
v_countsql := 'SELECT COUNT(1) FROM (SELECT A.*, rownum rn
FROM (' || p_SqlSelect || ') A
WHERE rownum <= ' || to_char(v_heiRownum) || ') B
WHERE rn >= ' || to_char(v_lowRownum);
execute immediate v_countsql
into v_pagecount;
p_PageRecordCount := v_pagecount;
----执行分页查询 v_sql := 'SELECT * FROM (SELECT A.*, rownum rn
FROM (' || p_SqlSelect || ') A
WHERE rownum <= ' || to_char(v_heiRownum) || ') B
WHERE rn >= ' || to_char(v_lowRownum);
--注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn OPEN p_OutCursor FOR v_sql;
end sp_Page;end;
create or replace procedure storage_days(goods_id_in goods.id%type)
as
v_days_1 number(5);
v_days_2 number(5);
v_days_3 number(5);
begin
select storage_days into v_days_1
from warehouse_1
where goods_id=goods_id_in;
select storage_days into v_days_2
from warehouse_2
where goods_id=goods_id_in;
select storage_days into v_days_3
from warehouse_3
where goods_id=goods_id_in;
dbms_output.put_line('The total number of days stored:'
||v_days_1+v_days_2+v_days_3);
exception
when others then
dbms_output.put_line(sqlerrm);
end storage_days;