-----包头
CREATE OR REPLACE PACKAGE "SA"."DOTNET" is
TYPE Type_Cur IS REF CURSOR;
PROCEDURE DotNetPagination(
Psql in varchar2,
Psize in number,
Pindex in number,
lrsj out date,
sp_id out varchar2,
sp_name out varchar2,
jhjg out number,
unitprice out number,
imageurl out varchar2,
jianjie out varchar2,
csjg out number,
lb out varchar2,
Pcount out number,
v_cur out type_cur
);
procedure DotNetPageRecordsCount(
Psqlcount in varchar2,
Prcount out number
);
end DotNet;
----包体CREATE OR REPLACE PACKAGE BODY "SA"."DOTNET" is
PROCEDURE DotNetPagination(
Psql in varchar2,
Psize in number,
Pindex in number,
lrsj out date,
sp_id out varchar2,
sp_name out varchar2,
jhjg out number,
unitprice out number,
imageurl out varchar2,
jianjie out varchar2,
csjg out number,
lb out varchar2,
Pcount out number,
v_cur out type_cur
)
as
Sql_Search VARCHAR2(4000);
v_sql VARCHAR2(3000);
v2_sql VARCHAR2(3000);
v_count number;
v_Plow number;
v_Phei number;
Begin
v_sql := 'select count(*) from (' || Psql || ')';
execute immediate v_sql into v_count;
Pcount := ceil(v_count/Psize);
v_Plow := (Pindex - 1) * Psize + 1;
v_Phei := v_Plow + Psize;
Sql_Search := 'select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T04 where shbz=''1'' and sp_name like ' || Psql ||
' union select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T13 where shbz=''1'' and sp_name like ' || Psql ||
' union select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T14 where shbz=''1'' and sp_name like ' || Psql ||
' union select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T09 where shbz=''1'' and sp_name like ' || Psql ||
' union select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T01 where shbz=''1'' and sp_name like ' || Psql ||
' union select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T10 where shbz=''1'' and sp_name like ' || Psql ||
' union select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T06 where shbz=''1'' and sp_name like ' || Psql ||
' union select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T12 where shbz=''1'' and sp_name like ' || Psql ||
' union select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T15 where shbz=''1'' and sp_name like ' || Psql ||
' union select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T08 where shbz=''1'' and sp_name like ' || Psql ||
' union select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T07 where shbz=''1'' and sp_name like ' || Psql ||
' union select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T03 where shbz=''1'' and sp_name like ' || Psql ||
' union select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T02 where shbz=''1'' and sp_name like ' || Psql ||
' union select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T05 where shbz=''1'' and sp_name like ' || Psql ||
' union select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T16 where shbz=''1'' and sp_name like ' || Psql ||
' order by LRSJ desc';
v2_sql := 'select * from (' || Sql_Search || ') where rownum <= ' || v_Phei || 'minus select * from (' || Sql_Search || ') where rownum <= ' || v_Plow;
open v_cur for v2_sql;
End DotNetPagination; procedure DotNetPageRecordsCount(
Psqlcount in varchar2,
Prcount out number
)
as
SqlCount VARCHAR2(4000);
v_sql varchar2(2000);
v_prcount number;
begin
SqlCount := 'select count(sp_id) as bb from (select sp_id from t04 where sp_name like ' || Psqlcount || ' and shbz=''1'' union
select sp_id from t13 where sp_name like ' || Psqlcount || ' and shbz=''1'' union
select sp_id from t14 where sp_name like '|| Psqlcount || ' and shbz=''1'' union
select sp_id from t09 where sp_name like '|| Psqlcount || ' and shbz=''1'' union
select sp_id from t01 where sp_name like '|| Psqlcount || ' and shbz=''1'' union
select sp_id from t10 where sp_name like '|| Psqlcount || ' and shbz=''1'' union
select sp_id from t06 where sp_name like '|| Psqlcount || ' and shbz=''1'' union
select sp_id from t12 where sp_name like '|| Psqlcount || ' and shbz=''1'' union
select sp_id from t15 where sp_name like '|| Psqlcount || ' and shbz=''1'' union
select sp_id from t08 where sp_name like '|| Psqlcount || ' and shbz=''1'' union
select sp_id from t07 where sp_name like '|| Psqlcount || ' and shbz=''1'' union
select sp_id from t03 where sp_name like '|| Psqlcount || ' and shbz=''1'' union
select sp_id from t02 where sp_name like '|| Psqlcount || ' and shbz=''1'' union
select sp_id from t05 where sp_name like '|| Psqlcount || ' and shbz=''1'' union
select sp_id from t16 where sp_name like '|| Psqlcount || ' and shbz=''1'')';
v_sql := 'select count(*) from (' || SqlCount || ')';
execute immediate v_sql into v_prcount;
Prcount := v_prcount;
end DotNetPageRecordsCount;
end DotNet;
这是我写的多表查询分页出储过程,请问高手我该怎样在asp里面调用呢?我总是出错,nnd,麻烦高手说一下我的存储过程是否对和该怎样调用。
CREATE OR REPLACE PACKAGE "SA"."DOTNET" is
TYPE Type_Cur IS REF CURSOR;
PROCEDURE DotNetPagination(
Psql in varchar2,
Psize in number,
Pindex in number,
lrsj out date,
sp_id out varchar2,
sp_name out varchar2,
jhjg out number,
unitprice out number,
imageurl out varchar2,
jianjie out varchar2,
csjg out number,
lb out varchar2,
Pcount out number,
v_cur out type_cur
);
procedure DotNetPageRecordsCount(
Psqlcount in varchar2,
Prcount out number
);
end DotNet;
----包体CREATE OR REPLACE PACKAGE BODY "SA"."DOTNET" is
PROCEDURE DotNetPagination(
Psql in varchar2,
Psize in number,
Pindex in number,
lrsj out date,
sp_id out varchar2,
sp_name out varchar2,
jhjg out number,
unitprice out number,
imageurl out varchar2,
jianjie out varchar2,
csjg out number,
lb out varchar2,
Pcount out number,
v_cur out type_cur
)
as
Sql_Search VARCHAR2(4000);
v_sql VARCHAR2(3000);
v2_sql VARCHAR2(3000);
v_count number;
v_Plow number;
v_Phei number;
Begin
v_sql := 'select count(*) from (' || Psql || ')';
execute immediate v_sql into v_count;
Pcount := ceil(v_count/Psize);
v_Plow := (Pindex - 1) * Psize + 1;
v_Phei := v_Plow + Psize;
Sql_Search := 'select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T04 where shbz=''1'' and sp_name like ' || Psql ||
' union select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T13 where shbz=''1'' and sp_name like ' || Psql ||
' union select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T14 where shbz=''1'' and sp_name like ' || Psql ||
' union select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T09 where shbz=''1'' and sp_name like ' || Psql ||
' union select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T01 where shbz=''1'' and sp_name like ' || Psql ||
' union select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T10 where shbz=''1'' and sp_name like ' || Psql ||
' union select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T06 where shbz=''1'' and sp_name like ' || Psql ||
' union select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T12 where shbz=''1'' and sp_name like ' || Psql ||
' union select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T15 where shbz=''1'' and sp_name like ' || Psql ||
' union select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T08 where shbz=''1'' and sp_name like ' || Psql ||
' union select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T07 where shbz=''1'' and sp_name like ' || Psql ||
' union select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T03 where shbz=''1'' and sp_name like ' || Psql ||
' union select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T02 where shbz=''1'' and sp_name like ' || Psql ||
' union select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T05 where shbz=''1'' and sp_name like ' || Psql ||
' union select lrsj,sp_id,sp_name,jhjg,unitprice,imageurl,jianjie,csjg,lb from T16 where shbz=''1'' and sp_name like ' || Psql ||
' order by LRSJ desc';
v2_sql := 'select * from (' || Sql_Search || ') where rownum <= ' || v_Phei || 'minus select * from (' || Sql_Search || ') where rownum <= ' || v_Plow;
open v_cur for v2_sql;
End DotNetPagination; procedure DotNetPageRecordsCount(
Psqlcount in varchar2,
Prcount out number
)
as
SqlCount VARCHAR2(4000);
v_sql varchar2(2000);
v_prcount number;
begin
SqlCount := 'select count(sp_id) as bb from (select sp_id from t04 where sp_name like ' || Psqlcount || ' and shbz=''1'' union
select sp_id from t13 where sp_name like ' || Psqlcount || ' and shbz=''1'' union
select sp_id from t14 where sp_name like '|| Psqlcount || ' and shbz=''1'' union
select sp_id from t09 where sp_name like '|| Psqlcount || ' and shbz=''1'' union
select sp_id from t01 where sp_name like '|| Psqlcount || ' and shbz=''1'' union
select sp_id from t10 where sp_name like '|| Psqlcount || ' and shbz=''1'' union
select sp_id from t06 where sp_name like '|| Psqlcount || ' and shbz=''1'' union
select sp_id from t12 where sp_name like '|| Psqlcount || ' and shbz=''1'' union
select sp_id from t15 where sp_name like '|| Psqlcount || ' and shbz=''1'' union
select sp_id from t08 where sp_name like '|| Psqlcount || ' and shbz=''1'' union
select sp_id from t07 where sp_name like '|| Psqlcount || ' and shbz=''1'' union
select sp_id from t03 where sp_name like '|| Psqlcount || ' and shbz=''1'' union
select sp_id from t02 where sp_name like '|| Psqlcount || ' and shbz=''1'' union
select sp_id from t05 where sp_name like '|| Psqlcount || ' and shbz=''1'' union
select sp_id from t16 where sp_name like '|| Psqlcount || ' and shbz=''1'')';
v_sql := 'select count(*) from (' || SqlCount || ')';
execute immediate v_sql into v_prcount;
Prcount := v_prcount;
end DotNetPageRecordsCount;
end DotNet;
这是我写的多表查询分页出储过程,请问高手我该怎样在asp里面调用呢?我总是出错,nnd,麻烦高手说一下我的存储过程是否对和该怎样调用。
解决方案 »
- 带分隔符的查询
- 此处不允许约束条件说明??
- oracle如何读取表或物化视图的properties
- 大家谈谈索引在进行select,update,delete,insert操作时对数据库性能的影响
- 求个简单函数,请赐教:)
- 求助:PL/SQL中对项目数据库的导入/导出问题
- 一个字段分为两列显示,只用一句SQL实现可以吗?
- oracle(8.1.6)的联机帮助.是怎样搞的?
- 请问如何把Excel描述的表结构导入数据库中?
- 调试了一个上午没有搞定,求如何在 select语句中嵌入 string变量,我用的是JAVAJDBC看下面代码哪里错了啊
- 请问,在Oracle10g里,有不支持的SQL文吗?急!!
- 求一解决方法!~~~~~~~~~~~~~
是建存储过程出错?还是运行出错?还是asp调用出错?