存储过程如下:
--包头
create or replace package PageTest is
TYPE T_CURSOR IS REF CURSOR;
Procedure Per_QuickPage
(
p_PageSize in number, --每页记录数
p_PageNo in number, --当前页码,从 1 开始
p_SqlSelect in varchar2, --查询语句,含排序部分
p_SqlCount in varchar2, --获取记录总数的查询语句
p_OutRecordCount out number,--返回总记录数
p_OutCursor out T_CURSOR
);
end PageTest;--包体create or replace package body PageTest
is
procedure Per_QuickPage
(
p_PageSize in number, --每页记录数
p_PageNo in number, --当前页码,从 1 开始
p_SqlSelect in varchar2, --查询语句,含排序部分
p_SqlCount in varchar2, --获取记录总数的查询语句
p_OutRecordCount out number,--返回总记录数
p_OutCursor out T_CURSOR
)
as
v_sql varchar2(3000);
v_count 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_sql := 'SELECT *
FROM (
SELECT A.*, rownum rn
FROM ('|| p_SqlSelect ||') A
WHERE rownum <= '|| to_char(v_heiRownum) || '
) B
WHERE rn >= ' || to_char(v_lowRownum) ;
OPEN p_OutCursor FOR v_sql;end Per_QuickPage;
end PageTest;--调用
exec PageTest. Per_QuickPage(10,3,'test','select count(*) from test');
显示的错误如下:
ORA-06550: 第 1 行, 第 7 列:
PLS-00306: 调用 'PER_QUICKPAGE' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored
--包头
create or replace package PageTest is
TYPE T_CURSOR IS REF CURSOR;
Procedure Per_QuickPage
(
p_PageSize in number, --每页记录数
p_PageNo in number, --当前页码,从 1 开始
p_SqlSelect in varchar2, --查询语句,含排序部分
p_SqlCount in varchar2, --获取记录总数的查询语句
p_OutRecordCount out number,--返回总记录数
p_OutCursor out T_CURSOR
);
end PageTest;--包体create or replace package body PageTest
is
procedure Per_QuickPage
(
p_PageSize in number, --每页记录数
p_PageNo in number, --当前页码,从 1 开始
p_SqlSelect in varchar2, --查询语句,含排序部分
p_SqlCount in varchar2, --获取记录总数的查询语句
p_OutRecordCount out number,--返回总记录数
p_OutCursor out T_CURSOR
)
as
v_sql varchar2(3000);
v_count 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_sql := 'SELECT *
FROM (
SELECT A.*, rownum rn
FROM ('|| p_SqlSelect ||') A
WHERE rownum <= '|| to_char(v_heiRownum) || '
) B
WHERE rn >= ' || to_char(v_lowRownum) ;
OPEN p_OutCursor FOR v_sql;end Per_QuickPage;
end PageTest;--调用
exec PageTest. Per_QuickPage(10,3,'test','select count(*) from test');
显示的错误如下:
ORA-06550: 第 1 行, 第 7 列:
PLS-00306: 调用 'PER_QUICKPAGE' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored
p_OutRecordCount number;--返回总记录数
p_OutCursor T_CURSOR;
begin
exec PageTest. Per_QuickPage(10,3,'test','select count(*) from test',p_OutRecordCount,p_OutCursor);
dbms_output.put_line(p_OutRecordCount);
end;
ORA-06550: 第 5 行, 第 8 列:
PLS-00103: 出现符号 "PAGETEST"在需要下列之一时:
:= . ( @ % ;
符号 ":=" 被替换为 "PAGETEST" 后继续。
改了半天,不知哪里出错,所以只有出来麻烦你了!
SqlStr varchar2(4000);
sqlStr1 varchar2(4000);
RecordCount int;
type cur_emp is ref cursor;
Rst cur_emp;
begin
sqlStr:='select * from table1';
sqlstr1:='select count(*) from table1';
PageTest.Per_QuickPage(20,1,sqlstr,sqlstr1,RecordCount,Rst);
dbms_output.put_line(RecordCount);
end;
execute immediate p_SqlCount into v_count;
p_OutRecordCount := v_count;
我这两句会出错?
的使用有问题