2 defender defender_ 于 2014.06.09 15:15 提问

初用oracle和PL/SQL 网上找了个分页存储过程,直接拿过来怎么报错。。求解

直接上代码`

create or replace package p_page is
TYPE type_cur IS REF CURSOR; --定义游标变量用于返回记录集

PROCEDURE Pagination(Pindex in number, --分页索引
Psql in varchar2, --产生dataset的sql语句
Psize in number, --页面大小
Pcount out number, --返回分页总数
Prcount out number,--返回总条数
v_cur out type_cur --返回当前页数据记录
);
end p_page;
/
create or replace package body p_page is
PROCEDURE Pagination(
Pindex in number,
Psql in varchar2,
Psize in number,
Pcount out number,
Prcount out number,
v_cur out type_cur
)
AS
v_sql varchar2(2000);
v_count number;
v_Plow number;
v_Phei number;
v_prcount number;

Begin
------------------------------------------------------------取分页总数
--v_sql := 'select count(*) into '||v_count||' from (' || Psql || ')';
v_sql:='select count(*) into '||v_count||' from ('||Psql||')';
execute immediate v_sql;
Pcount := ceil(v_count/Psize);
------------------------------------------------------------显示总条数
v_sql := 'select count(*) from (' || Psql || ')';
execute immediate v_sql into v_prcount;
Prcount := v_prcount; --返回记录总数

------------------------------------------------------------显示任意页内容
v_Phei := Pindex * Psize + Psize;
v_Plow := v_Phei - Psize + 1;
--Psql := 'select rownum rn,t.* from zzda t' ; --要求必须包含rownum字段
v_sql := 'select * from (' || Psql || ') where rn between ' || v_Plow || ' and ' || v_Phei ;

open v_cur for v_sql;

End Pagination;

--**************************************************************************************
end p_page;

--测试:
declare
cur SYS_REFCURSOR;
l_data TMSTEST.BASE_STATION%ROWTYPE;
pageindex number;
pagesize number;
sqll varchar2(2000);
ddd number;
prcount number;
begin
sqll:='select rownum rn,ID from TMSTEST.BASE_STATION';
pageindex:=1;
pagesize:=5;
prcount:=0;
ddd:=0;
p_page.Pagination(pageindex,sqll,pagesize,ddd,prcount,cur);
loop
exit when cur%notfound;
FETCH cur INTO l_data;
DBMS_OUTPUT.put_line (l_data.name);
end loop;
CLOSE cur;
END;

1个回答

defender_
defender_   2014.06.09 15:19

在执行sql那里,报这个错:ORA-00936:missing expression

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!