国产探花免费观看_亚洲丰满少妇自慰呻吟_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ā)表
主站蜘蛛池模板: 尼玛县| 教育| 姚安县| 青龙| 永康市| 浑源县| 杂多县| 社旗县| 元江| 龙山县| 临桂县| 大冶市| 麟游县| 绥芬河市| 北碚区| 太白县| 雷山县| 平果县| 虹口区| 凯里市| 新乡市| 盐边县| 于都县| 扬中市| 莱州市| 永吉县| 横山县| 临泽县| 长汀县| 丹棱县| 香河县| 云浮市| 金秀| 宾川县| 东平县| 泉州市| 木里| 汝阳县| 桂东县| 密云县| 达拉特旗|