专注于互联网--专注于架构

 最新标签
网站地图
文章索引
Rss订阅

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

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

来源: 发布时间:星期四, 2009年2月5日 浏览:1次 评论:0
="t18"> 在看了众多分页存储过程以后发现都是针对sqlserver而没有Oracle因此想写个有关oracle存储过程我用到数据库是oracle.
---------------------------------------------------------------------------------------
oracle分页存储过程思路于sqlserver思路是样但是我这里做了点改动在oracle语法和规则区别所以oracle分页
存储过程看上去有点不样见笑见笑！ 在oracle存储过程中返回记录集需要用到游标变量oracle不能像sqlserver那样可以直接返回个记录集
由于设想在.net中把复杂sql语句生成所以在存储过程中没有去考虑生成sql语句问题
-----------------------------------------------------------------------------------
以下是在oracle中实现分页存储过程 create or replace package DotNet is -- Author : good_hy
-- Created : 2004-12-13 13:30:30
-- Purpose :

TYPE type_cur IS REF CURSOR; --定义游标变量用于返回记录集

PROCEDURE DotNetPagination(
Pindex in number, --分页索引
Psql in varchar2, --产生datasql语句
Psize in number, --页面大小
Pcount out number, --返回分页总数
v_cur out type_cur --返回当前页数据记录
);

procedure DotNetPageRecordsCount(
Psqlcount in varchar2, --产生datasql语句
Prcount out number --返回记录总数
);

end DotNot; ------------------------------------------------------------------------------- create or replace package body DotNet is --***************************************************************************************

PROCEDURE DotNetPagination(
Pindex in number,
Psql in varchar2,
Psize in number,
Pcount out number,
v_cur out type_cur
)
AS v_sql VARCHAR2(1000);
v_count number;
v_Plow number;
v_Phei number;
Begin
------------------------------------------------------------取分页总数
v_sql := 'select count(*) from (' Psql ')';
execute immediate v_sql o v_count;
Pcount := ceil(v_count/Psize);
------------------------------------------------------------显示任意页内容
v_Phei := Pindex * Psize + Psize;
v_Plow := v_Phei - Psize + 1;
--Psql := 'select rownum rn,t.* 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 varchar2,
Prcount out number
)
as

v_sql varchar2(1000);
v_prcount number;

begin

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

end DotNetPageRecordsCount;

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

end DotNot; ------------------------------------------------------------------------------------------
以下是在.net中oracle分页存储过程步骤(vb.net)
在.net返回记录集存储过程需要用到datareader但是datareader不支持在datagrid中分页因此需要利用datagrid
自定义分页功能
Protected WithEvents DataGrid1 As .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 Response.Write("总计页数 " & cmd.Parameters("pcount").Value)
End Sub ---------------------------------------------------------------------------------------- Private Sub Page_Load(ByVal sender As .Object, ByVal e As .EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
Dim psql As String = "select rownum rn,t.* from cd_ssxl t"
gridbind(0, psql, 20)

End If End Sub --------------------------------------------------------------------------------------- Private Sub DataGrid1_PageIndexChanged(ByVal source As Object, ByVal e As .Web.UI.WebControls.DataGridPageChangedEventArgs) Handles DataGrid1.PageIndexChanged
Dim psql As String = "select rownum rn,t.* from cd_ssxl t"
Me.DataGrid1.CurrentPageIndex = e.NewPageIndex
gridbind(e.NewPageIndex, psql, 20)
End Sub

标签：oracle分页 oracle存储过程 oracle调用函数 oracle调用存储过程

相关文章

读者评论

发表评论

  • 昵称：
  • 内容：

热门标签

精华推荐

最新标签

Dig排行

阅读排行

最新文章