国产探花免费观看_亚洲丰满少妇自慰呻吟_97日韩有码在线_资源在线日韩欧美_一区二区精品毛片,辰东完美世界有声小说,欢乐颂第一季,yy玄幻小说排行榜完本

首頁(yè) > 數(shù)據(jù)庫(kù) > Oracle > 正文

Oracle 存儲(chǔ)過(guò)程教程

2024-08-29 13:56:04
字體:
來(lái)源:轉(zhuǎn)載
供稿:網(wǎng)友
Oracle分頁(yè)存儲(chǔ)過(guò)程的思路于sqlserver的思路是一樣的,但是我這里做了點(diǎn)改動(dòng),在因?yàn)镺racle的語(yǔ)法和規(guī)則的不同所以,Oracle分頁(yè)存儲(chǔ)過(guò)程看上去有點(diǎn)不一樣。見(jiàn)笑,見(jiàn)笑!
在Oracle的存儲(chǔ)過(guò)程中返回記錄集,需要用到游標(biāo)變量,Oracle不能像sqlserver那樣可以直接返回一個(gè)記錄集。
由于設(shè)想在.net中把復(fù)雜的sql語(yǔ)句生成,所以在存儲(chǔ)過(guò)程中沒(méi)有去考慮生成sql語(yǔ)句的問(wèn)題。
以下是在Oracle中實(shí)現(xiàn)的分頁(yè)存儲(chǔ)過(guò)程。

復(fù)制代碼 代碼如下:


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, --分頁(yè)索引
Psql in varchar2, --產(chǎn)生dataset的sql語(yǔ)句
Psize in number, --頁(yè)面大小
Pcount out number, --返回分頁(yè)總數(shù)
v_cur out type_cur --返回當(dāng)前頁(yè)數(shù)據(jù)記錄
);
procedure DotNetPageRecordsCount(
Psqlcount in varchar2, --產(chǎn)生dataset的sql語(yǔ)句
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
------------------------------------------------------------取分頁(yè)總數(shù)
v_sql := 'select count(*) from (' || Psql || ')';
execute immediate v_sql into v_count;
Pcount := ceil(v_count/Psize);
------------------------------------------------------------顯示任意頁(yè)內(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;


以下是在.net中調(diào)用Oracle分頁(yè)存儲(chǔ)過(guò)程的步驟。
在.net調(diào)用返回記錄集的存儲(chǔ)過(guò)程,需要用到datareader,但是datareader不支持在datagrid中的分頁(yè),因此需要利用datagrid自定義分頁(yè)功能。

復(fù)制代碼 代碼如下:


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ì)頁(yè)數(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

發(fā)表評(píng)論 共有條評(píng)論
用戶(hù)名: 密碼:
驗(yàn)證碼: 匿名發(fā)表
主站蜘蛛池模板: 岱山县| 淳化县| 双流县| 汉沽区| 左贡县| 阳山县| 清苑县| 英德市| 五指山市| 大荔县| 枣强县| 洛南县| 正阳县| 桓仁| 永和县| 赣榆县| 佛坪县| 三门县| 喀喇| 观塘区| 衡阳县| 隆化县| 苍溪县| 北京市| 长春市| 阜康市| 辽源市| 满洲里市| 宿松县| 健康| 汉源县| 开阳县| 黄陵县| 贺兰县| 泽库县| 休宁县| 达拉特旗| 鹤山市| 十堰市| 成武县| 沈丘县|