大家好,我在网上查到一个oracle的分页存储过程,但是在sql plus中创建的时候,报错,错误如下:PLS-00201:必须说明标识符'REFCURSORTYPE'
然后,我看到那个存储过程前面还有一句话,是create or replace type refCursorType is REF CURSOR; --游标类型定义,用于返回数据集
由于我对oracle不是很熟悉,现在遇到了好几百万的数据量,希望大伙帮帮忙。
告诉我如何正确创建这个存储过程,谢谢
create or replace procedure sp_Page(p_PageSize int, --每页记录数
p_PageNo int, --当前页码,从 1 开始
p_SqlSelect varchar2, --查询语句,含排序部分
p_SqlCount varchar2, --获取记录总数的查询语句
p_OutRecordCount out int,--返回总记录数
p_OutCursor out refCursorType)
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) ;
--注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn
OPEN p_OutCursor FOR v_sql;end sp_Page;
然后,我看到那个存储过程前面还有一句话,是create or replace type refCursorType is REF CURSOR; --游标类型定义,用于返回数据集
由于我对oracle不是很熟悉,现在遇到了好几百万的数据量,希望大伙帮帮忙。
告诉我如何正确创建这个存储过程,谢谢
create or replace procedure sp_Page(p_PageSize int, --每页记录数
p_PageNo int, --当前页码,从 1 开始
p_SqlSelect varchar2, --查询语句,含排序部分
p_SqlCount varchar2, --获取记录总数的查询语句
p_OutRecordCount out int,--返回总记录数
p_OutCursor out refCursorType)
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) ;
--注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn
OPEN p_OutCursor FOR v_sql;end sp_Page;
is
type ResultData 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 ResultData);
end PKG_Tools;
/
create or replace package body PKG_Tools
is
procedure sp_Page(p_PageSize int, --每页记录数
p_PageNo int, --当前页码,从 1 开始
p_SqlSelect varchar2, --查询语句,含排序部分
p_SqlCount varchar2, --获取记录总数的查询语句
p_OutRecordCount out int,--返回总记录数
p_OutCursor out ResultData)
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) ;
--注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn OPEN p_OutCursor FOR v_sql; end sp_Page;end PKG_Tools;
/
Set cnDb = New ADODB.Connection
Set cmd = New ADODB.Command
Set rst = New ADODB.Recordset
cnDb.CursorLocation = adUseClient
cnDb.ConnectionString = "Provider=MSDAORA.1;Password=pwd;User ID=system;Data Source=orcl;Persist Security Info=True"
cnDb.Open
cmd.ActiveConnection = cnDb
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "PKG_Tools.sp_Page"
cmd.Parameters.Append cmd.CreateParameter("p_PageSize", adInteger, adParamInputOutput, 4, 50)
cmd.Parameters.Append cmd.CreateParameter("p_PageNo", adInteger, adParamInputOutput, 30, 1)
cmd.Parameters.Append cmd.CreateParameter("p_SqlSelect", adLongVarChar, adParamInputOutput, 1000, "select * from table1")
cmd.Parameters.Append cmd.CreateParameter("p_SqlCount", adLongVarChar, adParamInputOutput, 1000, "select count(*) from table1") cmd.Parameters.Append cmd.CreateParameter("p_OutRecordCount", adInteger, adParamInputOutput, 4, p_OutRecordCount)
Set rst = cmd.Execute
p_OutCursor的注释是什么呢,有什么用呢?
OraOLEDB 错误 '80040e14' ORA-06550: 第 1 行, 第 7 列: PLS-00201: 必须说明标识符 'SP_PAGE' ORA-06550: 第 1 行, 第 7 列: PL/SQL: Statement ignored /tpbx/AMoK/test.asp,行 29 test.asp行29是,Set SqlRs1 = SqlCmd1.Execute()这种调用方法,是我以前调用sqlserver分页存储过程的
<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="../const.asp" -->
<!--#include file="../function.asp" -->
<!--#include file="../Connections/TPBX.asp" -->
<!--#include file="../md5.asp" -->
<!--#include file="../inc/adovbs.inc" -->
<%
set SqlCmd1 = Server.CreateObject("ADODB.Command")
SqlCmd1.ActiveConnection = MM_TPBX_STRING
SqlCmd1.CommandType = 4
SqlCmd1.CommandTimeout = 0
SqlCmd1.Prepared = trueif ChkInfo(1,6,"","","",2,Request.QueryString("PageNo"),"")=0 then
PageNO = 1
else
PageNO = Request.QueryString("PageNo")
end ifSqlCmd1.CommandText = "sp_Page"SqlCmd1.Parameters.append SqlCmd1.CreateParameter("@p_PageSize",adInteger,1,4,20)
SqlCmd1.Parameters.append SqlCmd1.CreateParameter("@p_PageNo",adInteger,1,4,PageNO)
SqlCmd1.Parameters.append SqlCmd1.CreateParameter("@p_SqlSelect",advarchar,1,1000,"select * from ClientInfo")
SqlCmd1.Parameters.append SqlCmd1.CreateParameter("@p_SqlCount",advarchar,1,1000,"select count (CI_ID) as C_CI_ID from ClientInfo")
SqlCmd1.Parameters.append SqlCmd1.CreateParameter("@p_OutRecordCount",adInteger,4)
Set SqlRs1 = SqlCmd1.Execute()if SqlRs1.state=0 then '未取到数据,SqlRs1关闭
MaxRecords=0
else
SqlRs1.Close() '注意:若要取得参数值,需先关闭记录集对象
MaxRecords = SqlCmd1(4)
end ifSet SqlCmd1 = NothingResponse.Write(MaxRecords)
%>
依然报错
OraOLEDB 错误 '80040e14' ORA-06550: 第 1 行, 第 7 列: PLS-00306: 调用 'SP_PAGE' 时参数个数或类型错误 ORA-06550: 第 1 行, 第 7 列: PL/SQL: Statement ignored /tpbx/AMoK/test.asp,行 29
SqlCmd1.Parameters.append SqlCmd1.CreateParameter("@p_PageNo",adInteger,1,4,PageNO)
SqlCmd1.Parameters.append SqlCmd1.CreateParameter("@p_SqlSelect",advarchar,1,1000,"select * from ClientInfo")
SqlCmd1.Parameters.append SqlCmd1.CreateParameter("@p_SqlCount",advarchar,1,1000,"select count (CI_ID) as C_CI_ID from ClientInfo")
SqlCmd1.Parameters.append SqlCmd1.CreateParameter("@p_OutRecordCount",adInteger,1,4,MaxRecords)
Set SqlRs1 = SqlCmd1.Execute()if SqlRs1.state=0 then '未取到数据,SqlRs1关闭
MaxRecords=0
else
SqlRs1.Close() '注意:若要取得参数值,需先关闭记录集对象
MaxRecords = SqlCmd1(4)
end ifSet SqlCmd1 = NothingResponse.Write(MaxRecords)我已经写了啊,还是报刚才那个错误
对数据库 CPU 使用 压力比较大 忘个位 使用谨慎