在看了众多的分页存储过程以后发现都是针对sqlserver的而没有oracle的因此想写一个关于oracle的存储过程因为我用到的数据库是oracle
oracle分页存储过程的思路于sqlserver的思路是一样的但是我这里做了点改动在因为oracle的语法和规则的不同所以oracle分页
存储过程看上去有点不一样见笑见笑！
在oracle的存储过程中返回记录集需要用到游标变量oracle不能像sqlserver那样可以直接返回一个记录集
由于设想中把复杂的sql语句生成所以在存储过程中没有去考虑生成sql语句的问题
以下是在oracle中实现的分页存储过程
create or replace package DotNet is
Author : good_hy
Created : ::
Purpose :
TYPE type_cur IS REF CURSOR; 定义游标变量用于返回记录集
PROCEDURE DotNetPagination(
Pindex in number 分页索引
Psql in varchar 产生dataset的sql语句
Psize in number 页面大小
Pcount out number 返回分页总数
v_cur out type_cur 返回当前页数据记录
);
procedure DotNetPageRecordsCount(
Psqlcount in varchar 产生dataset的sql语句
Prcount out number 返回记录总数
);
end DotNot;
create or replace package body DotNet is
***************************************************************************************
PROCEDURE DotNetPagination(
Pindex in number
Psql in varchar
Psize in number
Pcount out number
v_cur out type_cur
)
AS
v_sql VARCHAR();
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_Phei := Pindex * Psize + Psize;
v_Plow := v_Phei Psize + ;
Psql := select rownum rnt* from cd_ssxl t ; 要求必须包含rownum字段
v_sql := select * from ( || Psql || ) where rn between || v_Plow || and || v_Phei ;
open v_cur for v_sql;
End DotNetPagination;
**************************************************************************************
procedure DotNetPageRecordsCount(
Psqlcount in varchar
Prcount out number
)
as
v_sql varchar();
v_prcount number;
begin
v_sql := select count(*) from ( || Psqlcount || );
execute immediate v_sql into v_prcount;
Prcount := v_prcount; 返回记录总数
end DotNetPageRecordsCount;
**************************************************************************************
end DotNot;
以下是中调用oracle分页存储过程的步骤()
调用返回记录集的存储过程需要用到datareader但是datareader不支持在datagrid中的分页因此需要利用datagrid
自定义分页功能
Protected WithEvents DataGrid As SystemWebUIWebControlsDataGrid
Dim conn As New OracleClientOracleConnection()
Dim cmd As New OracleClientOracleCommand()
Dim dr As OracleClientOracleDataReader
Private Sub gridbind(ByVal pindex As Integer ByVal psql As String Optional ByVal psize As Integer = )
connConnectionString = Password=gzdlgis;User ID=gzdlgis;Data Source=gzgis
cmdConnection = conn
cmdCommandType = CommandTypeStoredProcedure
connOpen()
cmdCommandText = DotNotDotNetPageRecordsCount
cmdParametersAdd(psqlcount OracleTypeVarChar)Value = psql
cmdParametersAdd(prcount OracleTypeNumber)Direction = ParameterDirectionOutput
cmdExecuteNonQuery()
MeDataGridAllowPaging = True
MeDataGridAllowCustomPaging = True
MeDataGridPageSize = psize
MeDataGridVirtualItemCount = cmdParameters(prcount)Value
cmdParametersClear()
cmdCommandText = DotNotDotNetPagination
cmdParametersAdd(pindex DataOracleClientOracleTypeNumber)Value = pindex
cmdParametersAdd(psql DataOracleClientOracleTypeVarChar)Value = psql select rownum rnt* from cd_ssxl t
cmdParametersAdd(psize DataOracleClientOracleTypeNumber)Value = psize
cmdParametersAdd(v_cur DataOracleClientOracleTypeCursor)Direction = ParameterDirectionOutput
cmdParametersAdd(pcount DataOracleClientOracleTypeNumber)Direction = ParameterDirectionOutput
dr = cmdExecuteReader()
MeDataGridDataSource = dr
MeDataGridDataBind()
drClose()
connClose()
ResponseWrite(总计页数 & cmdParameters(pcount)Value)
End Sub
Private Sub Page_Load(ByVal sender As SystemObject ByVal e As SystemEventArgs) Handles MyBaseLoad
If Not PageIsPostBack Then
Dim psql As String = select rownum rnt* from cd_ssxl t
gridbind( psql )
End If
End Sub
Private Sub DataGrid_PageIndexChanged(ByVal source As Object ByVal e As SystemWebUIWebControlsDataGridPageChangedEventArgs) Handles DataGridPageIndexChanged
Dim psql As String = select rownum rnt* from cd_ssxl t
MeDataGridCurrentPageIndex = eNewPageIndex
gridbind(eNewPageIndex psql )
End Sub
