Oracle 存储过程教程
前端之家收集整理的这篇文章主要介绍了
Oracle 存储过程教程，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
Oracle分页存储过程的思路于sqlserver的思路是一样的，但是我这里做了点改动，在因为Oracle的语法和规则的不同所以，Oracle分页存储过程看上去有点不一样。见笑，见笑！
在Oracle的存储过程中返回记录集，需要用到游标变量，Oracle不能像sqlserver那样可以直接返回一个记录集。
由于设想在.net中把复杂的sql语句生成，所以在存储过程中没有去考虑生成sql语句的问题。
以下是在Oracle中实现的分页存储过程。
<div class="codetitle"><a style="CURSOR: pointer" data="92486" class="copybut" id="copybut92486" onclick="doCopy('code92486')"> 代码如下:
<div class="codebody" id="code92486">
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,--
分页索引
P
sql in varchar2,--产生dataset的
sql语句
Psize in number,--
页面大小
Pcount out number,--返回
分页总数
v_cur out type_cur --返回当前页数据记录
);
procedure DotNetPageRecordsCount(
P
sqlcount in varchar2,--产生dataset的
sql语句
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 typecur
)
AS
vsql VARCHAR2(1000);
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 + 1;
--Psql := 'select rownum rn,t. from cdssxl t' ; --要求必须包含rownum字段
vsql := 'select * from (' || Psql || ') where rn between ' || v_Plow || ' and ' || v_Phei ;
open vcur for vsql;
End DotNetPagination;
--**
procedure DotNetPageRecordsCount(
Psqlcount in varchar2,
Prcount out number
)
as
v_sql varchar2(1000);
vprcount number;
begin
vsql := 'select count(*) from (' || Psqlcount || ')';
execute immediate v_sql into v_prcount;
Prcount := v_prcount; --返回记录总数
end DotNetPageRecordsCount;
-- end DotNot;
以下是在.net中
调用Oracle
分页存储过程的步骤。
在.net
调用返回记录集的存储过程，需要用到datareader，但是datareader
不支持在datagrid中的
分页，因此需要利用datagrid
自定义分页功能。
<div class="codetitle">
<a style="CURSOR: pointer" data="84200" class="copybut" id="copybut84200" onclick="doCopy('code84200')"> 代码如下:
<div class="codebody" id="code84200">
rotected 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 p
sql 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("p
sqlcount",OracleType.VarChar).Value = p
sql 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("p
sql",Data.OracleClient.OracleType.VarChar).Value = p
sql '"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 System.Object,ByVal e As System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
Dim psql As String = "select rownum rn,t. from cd_ssxl t"
gridbind(0,p
sql,20)
End If
End Sub
---------------------------------------------------------------------------------------
Private Sub DataGrid1_PageIndexChanged(ByVal source As Object,ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles DataGrid1.PageIndexChanged
Dim p
sql As String = "select rownum rn,t.* from cd_ssxl t"
Me.DataGrid1.CurrentPageIndex = e.NewPageIndex
gridbind(e.NewPageIndex,20)
End Sub
