您现在正在浏览：站长之家 >> 数据库 >> 详细内容

一个简单的oracle分页数据库存储过程的实现和调用

时间:2007-12-23 来源:不详作者:迈克DB

v_prcount number;

begin

v_sql := 'select count(*) from (' || Psqlcount || ')';
execute immediate v_sql into v_prcount;
Prcount := v_prcount; --返回记录总数

end DotNetPageRecordsCount;

--**************************************************************************************

end DotNot;

------------------------------------------------------------------------------------------
以下是在.net中调用oracle分页存储过程的步骤。(vb.net)
在.net调用返回记录集的存储过程，需要用到datareader，但是datareader不支持在datagrid中的分页，因此需要利用datagrid
自定义分页功能。

Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid

织梦内容管理系统

Dim conn As New OracleClient.OracleConnection()
Dim cmd As New OracleClient.OracleCommand()
Dim dr As OracleClient.OracleDataReader

Private Sub gridbind(ByVal pindex As Integer, ByVal psql As String, Optional ByVal psize As Integer = 10)

织梦内容管理系统

conn.ConnectionString = "Password=gzdlgis;User ID=gzdlgis;Data Source=gzgis"
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
conn.Open()

织梦好，好织梦

'------------------------------------------------------------------------------------
cmd.CommandText = "DotNot.DotNetPageRecordsCount"
'------------------------------------------------------------------------------------
cmd.Parameters.Add("psqlcount", OracleType.VarChar).Value = psql
cmd.Parameters.Add("prcount", OracleType.Number).Direction = ParameterDirection.Output 织梦好，好织梦

cmd.ExecuteNonQuery() 织梦内容管理系统

Me.DataGrid1.AllowPaging = True
Me.DataGrid1.AllowCustomPaging = True
Me.DataGrid1.PageSize = psize
Me.DataGrid1.VirtualItemCount = cmd.Parameters("prcount").Value 织梦内容管理系统

cmd.Parameters.Clear()
'------------------------------------------------------------------------------------
cmd.CommandText = "DotNot.DotNetPagination"
'------------------------------------------------------------------------------------
cmd.Parameters.Add("pindex", Data.OracleClient.OracleType.Number).Value = pindex
cmd.Parameters.Add("psql", Data.OracleClient.OracleType.VarChar).Value = psql '"select rownum rn,t.* from cd_ssxl t"
cmd.Parameters.Add("psize", Data.OracleClient.OracleType.Number).Value = psize
cmd.Parameters.Add("v_cur", Data.OracleClient.OracleType.Cursor).Direction = ParameterDirection.Output
cmd.Parameters.Add("pcount", Data.OracleClient.OracleType.Number).Direction = ParameterDirection.Output 织梦内容管理系统

dr = cmd.ExecuteReader()

织梦好，好织梦

Me.DataGrid1.DataSource = dr
Me.DataGrid1.DataBind() 织梦好，好织梦

dr.Close()
conn.Close()

内容来自dedecms

Response.Write("总计页数 " & cmd.Parameters("pcount").Value)
End Sub

----------------------------------------------------------------------------------------