Oracle 存儲過程教程

create or replace package DotNet is
-- Author : good_hy
-- Created : 2004-12-13 13:30:30
-- Purpose :
TYPE type_cur IS REF CURSOR; --定義游標(biāo)變量用于返回記錄集
PROCEDURE DotNetPagination(
Pindex in number, --分頁索引
Psql in varchar2, --產(chǎn)生dataset的sql語句
Psize in number, --頁面大小
Pcount out number, --返回分頁總數(shù)
v_cur out type_cur --返回當(dāng)前頁數(shù)據(jù)記錄
);
procedure DotNetPageRecordsCount(
Psqlcount in varchar2, --產(chǎn)生dataset的sql語句
Prcount out number --返回記錄總數(shù)
);
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
------------------------------------------------------------取分頁總數(shù)
v_sql := 'select count(*) from (' || Psql || ')';
execute immediate v_sql into v_count;
Pcount := ceil(v_count/Psize);
------------------------------------------------------------顯示任意頁內(nèi)容
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 into v_prcount;
Prcount := v_prcount; --返回記錄總數(shù)
end DotNetPageRecordsCount;
--**************************************************************************************
end DotNot;

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 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("總計(jì)頁數(shù) " 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, psql, 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 psql As String = "select rownum rn,t.* from cd_ssxl t"
Me.DataGrid1.CurrentPageIndex = e.NewPageIndex
gridbind(e.NewPageIndex, psql, 20)
End Sub