ASP中使用預存程序

來源:互聯網
上載者:User
預存程序|預存程序 學習使用預存程序(Stored Procedure),是ASP程式員的必須課之一。

所有的大型資料庫都支援預存程序,比如Oracle、MS SQL等,(但MS Access不支援,不過,在Access裡可以使用參數化的查詢)。

預存程序是利用SQL Server所提供的Tranact-SQL語言所編寫的程式。Tranact-SQL語言是SQL Server提供專為設計資料庫應用程式的語言,它是應用程式和SQL Server資料庫間的主要程式式設計介面。它好比Oracle資料庫系統中的Pro-SQL和Informix的資料庫系統能夠中的Informix-4GL語言一樣。這類語言主要提供以下功能,讓使用者可以設計出符合引用需求的程式:
1)、變數說明
2)、ANSI相容的SQL命令(如Select,Update….)
3)、一般流程式控制制命令(if…else…、while….)
4)、內建函式

預存程序的書寫格


CREATE PROCEDURE [擁有者.]預存程序名[;程式編號]
[(參數#1,…參數#1024)]
[WITH
{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}
]
[FOR REPLICATION]
AS 程式行



其中預存程序名不能超過128個字。每個預存程序中最多設定1024個參數(SQL Server 7.0以上版本),參數的使用方法如下:


@參數名 資料類型 [VARYING] [=內定值] [OUTPUT]



每個參數名前要有一個“@”符號,每一個預存程序的參數僅為該程式內部使用,參數的類型除了IMAGE外,其他SQL Server所支援的資料類型都可使用。

[=內定值]相當於我們在建立資料庫時設定一個欄位的預設值,這裡是為這個參數設定預設值。[OUTPUT]是用來指定該參數是既有輸入又有輸出值的,也就是在調用了這個預存程序時,如果所指定的參數值是我們需要輸入的參數,同時也需要在結果中輸出的,則該項必須為OUTPUT,而如果只是做輸出參數用,可以用CURSOR,同時在使用該參數時,必須指定VARYING和OUTPUT這兩個語句。


CREATE PROCEDURE order_tot_amt @o_id int,@p_tot int output AS
SELECT @p_tot = sum(Unitprice*Quantity)
FROM orderdetails
WHERE ordered=@o_id




該例子是建立一個簡單的預存程序order_tot_amt,這個預存程序根據使用者輸入的定單ID號碼(@o_id),由定單明細表(orderdetails)中計算該定單銷售總額[單價(Unitprice)*數量(Quantity)],這一金額通過@p_tot這一參數輸出給調用這一預存程序的程式

使用預存程序有許多好處,它可以封裝複雜的資料邏輯,充分發揮大型資料庫本身的優勢。

我們知道,ASP並不適合做複雜的資料運算,而通過OLD DB訪問資料庫,由於資料需要在ASP和資料庫之間傳遞,相當消耗系統資源。

事實上,如果資料庫僅僅起著資料存放區的作用,那麼它的功能是遠遠沒有得到利用的。

關於如何建立預存程序,請參考MS SQL的相關文檔。

本文介紹預存程序如何在ASP中運用。

簡單的一個SQL語句:


select ID,Name,Picture,Time,Duty from employ



我們可以建立一個預存程序:


CREATE PROCEDURE sp_employ
AS
select ID,Name,Picture,Time,Duty from employ
Go



而SQL語句:


select ID,Name,Picture,Time,Duty from employ where ID=10230



對應的預存程序是:(用Alter替換我們已有的預存程序)


ALTER PROCEDURE sp_employ
@inID int
AS
select ID,Name,Picture,Time,Duty from employ where ID=@inID
Go




下面對比一下SQL和預存程序在ASP中的情況。首先看看直接執行SQL的情況:


<%
dim Conn, strSQL, rs
set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DSN=webData;uid=user;pwd=password"
strSQL = "select ID,Name,Picture,Time,Duty from employ "
Set rs = Conn.Execute(strSQL)
%>




再看看如何執行Stored Procedure:

<%
dim Conn, strSQL, rs
set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DSN=webData;uid=user;pwd=password" ’make connection
strSQL = "sp_employ"
Set rs = Conn.Execute(strSQL)
%>

而執行帶參數的Stored Procedure也是相當類似的:


<%
dim Conn, strSQL, rs, myInt
myInt = 1
set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open "DSN=webData;uid=user;pwd=password"
strSQL = "sp_myStoredProcedure " & myInt
Set rs = Conn.Execute(strSQL)
%>




在SQL Server中執行預存程序

在SQL Server的查詢分析器中,輸入以下代碼:
declare @tot_amt int
execute order_tot_amt 1,@tot_amt output
select @tot_amt

以上代碼是執行order_tot_amt這一預存程序,以計算出定單編號為1的定單銷售金額,我們定義@tot_amt為輸出參數,用來承接我們所要的結果


在ASP中調用預存程序


<!--必須載入adovbs.inc檔案,否則將出錯-->
<!--#include file="adovbs.inc"-->
<%
dim objCnn
dim objCmd
dim Rs
const o_id=112

'-----建立Connection對象----------
set objCnn=Server.CreateObject("Adodb.connection")
objCnn.Open "driver={sql server};server=localhost;uid=sa;pwd=cncanet;database=check;"
'-----建立Command對象-----------
set objCmd=Server.CreateObject("Adodb.Command")
objCmd.ActiveConnection=objCnn
objCmd.CommandText="order_tot_amt" '指定預存程序名稱
objCmd.CommandType=adCmdStoredProc '其為Stored Procedure
'-----準備stored procedure 的參數-------
objCmd.Parameters.Append _
objCmd.CreateParameter("o_id",adInteger,adParamInput,,o_id)
objCmd.Parameters.Append _
objCmd.CreateParameter("p_tot",adBigInt,adParamOutput,,0)
'-----執行預存程序----------------------
objCmd.Execute

'-----輸出參數以及處理結果--------------
for each parm in objCmd.Parameters
Response.Write parm.name &a



相關文章

Cloud Intelligence Leading the Digital Future

Alibaba Cloud ACtivate Online Conference, Nov. 20th & 21st, 2019 (UTC+08)

Register Now >

Starter Package

SSD Cloud server and data transfer for only $2.50 a month

Get Started >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。