ASP やストアド プロシージャ (Stored Procedure) に関する記事は数多くありますが、著者が実際に実践しているかどうかは疑問です。私が初心者だった頃、多くの関連情報を調べたところ、提供されている方法の多くが実際には同じではないことがわかりました。単純なアプリケーションの場合、これらのマテリアルは役立つかもしれませんが、基本的に同じで相互にコピーされるため、これらのマテリアルはこれに限定されます。少し複雑なアプリケーションの場合は、すべてが不明瞭になります。
ここでは、基本的にストアド プロシージャを呼び出して SQL Server にアクセスします。次のテキストは、皆さんの参考になれば幸いです。
ストアド プロシージャは、実行可能オブジェクトとしてデータベースに保存された 1 つ以上の SQL コマンドです。
定義は常に抽象的です。ストアド プロシージャは、実際には特定の操作を完了できる SQL ステートメントのセットですが、このステートメントのセットはデータベースに配置されます (ここでは SQL Server についてのみ説明します)。 ASP でストアド プロシージャを作成し、ストアド プロシージャを呼び出すと、SQL ステートメントと ASP コードの混在を避けることができます。これを行うことには少なくとも 3 つの利点があります。
まず、効率が大幅に向上します。ストアド プロシージャ自体の実行速度は非常に高速であり、ストアド プロシージャを呼び出すことでデータベースとの対話の数を大幅に減らすことができます。
第二に、安全性の向上です。 ASP コードに SQL ステートメントを混在させると、コードが侵害されると、ライブラリ構造も侵害されることになります。
第三に、SQL ステートメントの再利用に役立ちます。
ASP では、ストアド プロシージャは通常、コマンド オブジェクトを通じて呼び出されます。状況に応じて、この記事では他の呼び出し方法も紹介します。説明の便宜上、ストアド プロシージャの入出力に基づいて次のように簡単に分類します。
1. 単一のレコード セットのみを返すストアド プロシージャは、
次のストアド プロシージャを想定しています (この記事の目的は説明ではありません)
。T-SQL 構文のため、ストアド プロシージャはコードのみを提供し、説明はありません):
/*SP1*/
CREATE PROCEDURE dbo.getUserList
as
set nocount on
begin
select * from dbo.[userinfo]
end
go
上記のストアド プロシージャはすべてのレコードを取得しますuserinfo テーブル内のレコード セットを返します。コマンド オブジェクトを通じてストアド プロシージャを呼び出すための ASP コードは次のとおりです。
'**コマンド オブジェクトを通じてストアド プロシージャを呼び出す**
DIM MyComm,MyRst
Set MyComm = Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection = MyConStr 'MyConStr はデータベース接続です String
MyComm.CommandText = "getUserList" 'ストアド プロシージャ名を指定します
MyComm.CommandType = 4 'これがストアド プロシージャであることを示します
MyComm.Prepared = true '最初に SQL コマンドをコンパイルする必要があります
Set MyRst = MyComm.Execute
Set MyComm = 何も
ストアド プロシージャ 取得したレコード セットを MyRst に割り当てます。次に、MyRst を操作できます。
上記のコードでは、CommandType 属性はリクエストのタイプを示します。値と説明は次のとおりです。
-1 は CommandText パラメータのタイプを決定できないことを示します
。1 は CommandText パラメータが一般的なコマンド タイプである
ことを示します。 CommandText パラメーターは既存のテーブル名です。
4 は、CommandText パラメーターが次のとおりであることを示します。ストアド プロシージャの名前は、
Connection オブジェクトまたは Recordset オブジェクトを通じてストアド プロシージャを呼び出すこともできます。メソッドは次のとおりです。
'**Connection を通じてストアド プロシージャを呼び出します
。object**
DIM MyConn,MyRst
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn .open MyConStr 'MyConStr はデータベース接続文字列です
Set MyRst = MyConn.Execute("getUserList",0,4) '最後のパラメータCommandType と同じ意味です
Set MyConn = Nothing
'**Recordset オブジェクトを通じてストアド プロシージャを呼び出す**
DIM MyRst
Set MyRst = Server.CreateObject("ADODB.Recordset")
MyRst.open "getUserList",MyConStr,0,1 ,4
'MyConStr はデータベース接続文字列で、最後のパラメータは CommandType 2 と同じ意味を持ちます。
入出力ストレージはありません。プロセスについては次のストアド プロシージャを参照してください
。
/*SP2*/
CREATE PROCEDURE dbo.delUserAll
as
set nocount on
begin
delete from dbo.[userinfo]
end
go
このストアド プロシージャは、入力または出力を行わずに userinfo テーブル内のすべてのレコードを削除します。 メソッドは基本的に上記と同じですが、レコード セットを取得する必要がない点が異なります。 :
'**コマンド オブジェクトを通じてストアド プロシージャを呼び出します**
DIM MyComm
Set MyComm = Server.CreateObject("ADODB.Command")
MyComm.ActiveConnection = MyConStr 'MyConStr はデータベース接続文字列です
MyComm.CommandText = "delUserAll" '指定しますストアド プロシージャ名
MyComm.CommandType = 4 'これがストアド プロシージャであることを示します
MyComm.Prepared = true '最初に SQL コマンドをコンパイルする必要があります
MyComm.Execute 'ここで設定されたレコードを取得する必要はありませ
ん Set MyComm = Nothing
もちろん、このようなストアド プロシージャは、Connection オブジェクトまたは Recordset オブジェクトを通じて呼び出すこともできますが、Recordset オブジェクトはレコードセットを取得するために作成されます。レコードセットが返されない場合は、Command オブジェクトを使用することをお勧めします。
3.SP2 と同様の操作を実行する場合、
戻り値を持つストアド プロシージャは、
データの一貫性を維持するために SQL Server の強力なトランザクション処理機能を最大限に活用する必要があります。さらに、ストアド プロシージャが実行ステータスを返す必要がある場合があります。そのためには、SP2 を次のように変更します。
/*SP3*/
CREATE PROCEDURE dbo.delUserAll
as
set nocount on
begin
BEGIN TRANSACTION
delete from dbo.[userinfo]
IF error= 0
begin
COMMIT TRANSACTION
return 1
end
ELSE
begin
ROLLBACK TRANSACTION
return 0
end
return
end
go
上記のストアド プロシージャは、削除が正常に実行された場合は 1 を返し、そうでない場合は 0 を返し、ロールバック操作を実行します。 ASP で戻り値を取得するには、Parameters コレクションを使用してパラメーターを宣言する必要があります。
'**戻り値を使用してストアド プロシージャを呼び出し、戻り値を取得します**
DIM MyComm,MyPara
Set MyComm = Server.CreateObject ("ADODB.Command")
MyComm.ActiveConnection = MyConStr 'MyConStr はデータベース接続文字列です
MyComm.CommandText = "delUserAll" 'ストアド プロシージャ名を指定します
MyComm.CommandType = 4 'これがストアド プロシージャであることを示します
MyComm.Prepared = true '最初に SQL コマンドをコンパイルする必要があります
' ステートメント 戻り値
Set Mypara = MyComm.CreateParameter("RETURN",2,4)
MyComm.Parameters.Append MyPara
MyComm.Execute
'戻り値を取得します
DIM retValue
retValue = MyComm(0) 'または retValue = MyComm.Parameters(0)
Set MyComm = Nothing
MyComm.CreateParameter("RETURN",2,4) では、各パラメーターの意味は次のとおりです。
最初のパラメーター ("RETURE") はパラメーター名です。パラメータ名は任意に設定できますが、通常はストアドプロシージャ内で宣言したパラメータ名と同じにする必要があります。通常は「RETURE」に設定する戻り値を次に示します。2
番目のパラメータ (2) は、パラメータのデータ型を示します。一般的に使用される型コードは、ADO リファレンスを参照してください。
Adbigint
：
128
-1 から - 99 は予約値です。3
番目のパラメータ (4) はパラメータの性質を示し、4 はこれが戻り値であることを示します。このパラメータの値の説明は次のとおりです:
0: タイプを決定できません; 1: 入力パラメータ; 3: 入力パラメータまたは出力パラメータ; 4: 戻り
値実際、これが最も複雑なコードです。
Set Mypara = MyComm.CreateParameter("RETURN",2,4)
MyComm.Parameters.Append MyPara は
、MyComm.Parameters.Append MyComm.CreateParameter()
に簡略化できます
。"RETURN"、2、4)
またはさらに単純化を続けることができます。これについては後で説明します。
パラメータ付きのストアド プロシージャの場合、Command オブジェクトを使用してのみ呼び出すことができます (Connection オブジェクトまたは Recordset オブジェクトを通じて呼び出せるという情報もありますが、試していません)。
4. 入力パラメータと出力パラメータを持つストアド プロシージャの戻り値は、
実際には特別な出力パラメータです。多くの場合、入力パラメータと出力パラメータの両方を持つストアド プロシージャを使用します。たとえば、ユーザー情報テーブル内の特定の ID を持つユーザーのユーザー名を取得したいとします。 --user ID、および出力パラメータ----ユーザー名。この関数を実装するストアド プロシージャは次のとおりです。
/*SP4*/
CREATE PROCEDURE dbo.getUserName
@UserID int,
@UserName varchar(40) Output
as
set nocount on
begin
if @UserID is null return
select @UserName=username
from dbo .[ userinfo]
where userid=@UserID
return
end
go
ストアド プロシージャを呼び出す ASP コードは次のとおりです。
'**入出力パラメータを使用してストアド プロシージャを呼び出します**
DIM MyComm,UserID,UserName
UserID = 1
Set MyComm = Server.CreateObject(" ADODB.Command")
MyComm.ActiveConnection = MyConStr 'MyConStr はデータベース接続文字列です
MyComm.CommandText = "getUserName" 'ストアド プロシージャ名を指定します
MyComm.CommandType = 4 'これがストアド プロシージャ
MyComm であることを示します。 Prepared = true 'SQL が必要 最初にコマンドをコンパイルし
、パラメータを宣言します。
MyComm.Parameters.append MyComm.CreateParameter("@UserID",3,1,4,UserID)
MyComm.Parameters.append MyComm.CreateParameter("@UserName", 200,2,40)
MyComm. Execute
'パラメータを取得
UserName = MyComm(1)
Set MyComm = Nothing
上記のコードでは、戻り値の宣言とは異なり、入力パラメータを宣言する場合は 5 つのパラメータが必要であることがわかります。出力パラメータを宣言する場合はパラメータが必要です。入力パラメータを宣言する場合、パラメータ名、パラメータのデータ型、パラメータの型、データ長、パラメータ値の 5 つのパラメータが使用されます。出力パラメータを宣言する場合、最後のパラメータ、つまりパラメータ値はありません。
パラメータを宣言するときは、順序がストアド プロシージャで定義されているものと同じである必要があり、各パラメータのデータ型と長さもストアド プロシージャで定義されているものと同じである必要があることに特別な注意を払う必要があります。
ストアド プロシージャに複数のパラメータがある場合、ASP コードは複雑に見えます。 with コマンドを使用してコードを簡略化できます。
'**入力パラメータと出力パラメータを使用してストアド プロシージャを呼び出します (簡略化されたコード)**
DIM MyComm,UserID,UserName。
UserID = 1
Set MyComm = Server.CreateObject("ADODB.Command")
with MyComm
.ActiveConnection = MyConStr 'MyConStr はデータベース接続文字列です。CommandText
= "getUserName" 'ストアド プロシージャ名を指定します。CommandType
= 4 'これがストアド プロシージャ。Prepared
= true '最初に SQL コマンドをコンパイルする必要があります。Parameters.append
.CreateParameter("@UserID",3,1,4,UserID)
.Parameters.append .CreateParameter("@UserName",200, 2,40)
.Execute
end with
UserName = MyComm(1)
Set MyComm = Nothing
ID 1 ～ 10 の 10 人のユーザーのユーザー名を取得したい場合、Command オブジェクトを 10 回作成する必要がありますか?いいえ。同じストアド プロシージャを複数回呼び出す必要がある場合は、入力パラメータを変更するだけで、異なる出力が得られます。
'**同じストアド プロシージャへの複数の呼び出し**
DIM MyComm,UserID,UserName
UserName = ""
Set MyComm = Server .CreateObject( "ADODB.Command")
for UserID = 1 to 10
with MyComm
.ActiveConnection = MyConStr 'MyConStr はデータベース接続文字列です。CommandText
= "getUserName" 'ストアド プロシージャ名を指定します。CommandType
= 4 'これがストアド プロシージャ.Prepared
= true 'UserID = 1 の場合は
最初に SQL コマンドをコンパイルする必要があります
。次に.Parameters.append .CreateParameter("@UserID",3,1,4,UserID)
.Parameters.append .CreateParameter("@UserName" ,200,2,40 )
.Execute
else
'入力パラメータを再割り当てします(この時点でパラメータ値が変化しない入力パラメータおよび出力パラメータは再宣言する必要はありません)
.Parameters("@UserID") = UserID
.Execute
end if
end with
UserName = UserName + MyComm( 1) + "," 'おそらく、配列を使用して
次の
Set MyComm = Nothing を格納する必要があるかもしれません。
上記のコードからわかるように、同じストアド プロシージャを呼び出す場合です。このメソッドは、入力パラメータと出力パラメータが複数あり、呼び出されるたびに 1 つの入力パラメータのみの値が変更される場合に、コード量を大幅に削減できます。大幅に減少しました。
5. 戻り値、入力パラメタ、出力パラメタを同時に持つストアドプロシージャ
前述のように、ストアドプロシージャを呼び出すときのパラメタの宣言順序は、ストアドプロシージャで定義された順序と同じである必要があります
。。特に注意すべきもう 1 つの点は、ストアド プロシージャに戻り値と入出力パラメータの両方がある場合、戻り値を最初に宣言する必要があることです。
この場合の呼び出しメソッドを示すために、上記の例を改良してみましょう。 ID 1 のユーザーのユーザー名を引き続き取得しますが、ユーザーが存在しない可能性があります (ユーザーは削除されており、userid は自己増加フィールドです)。ストアド プロシージャは、ユーザーが存在するかどうかに応じて異なる値を返します。このときのストアドプロシージャとASPコードは以下のとおりです。
/*SP5*/
CREATE PROCEDURE dbo.getUserName
--「シーケンス」の印象を深めるため、以下の2つのパラメータの定義順序を逆にします
@UserName varchar( 40) 出力、
@UserID int
as
set nocount on
begin
if @UserID is null return
select @UserName=username
from dbo.[userinfo]
where userid=@UserID
if rowcount> 0
return 1
else
return 0
return
end
go
'**呼び出しには戻り値と入力の両方があります パラメーターと出力パラメーターのストアド プロシージャ**
DIM MyComm,UserID,UserName
UserID = 1
Set MyComm = Server.CreateObject("ADODB.Command")
with MyComm
.ActiveConnection = MyConStr 'MyConStr はデータベース接続ですstring.CommandText
= "getUserName" 'ストアド プロシージャ名を指定します。CommandType
= 4 'これがストアド プロシージャであることを示します
。 Prepared = true '最初に SQL コマンドをコンパイルする必要があります
' 戻り値は最初に宣言する必要があります。
.CreateParameter("RETURN",2,4)
'それに応じて、次の 2 つのパラメータの宣言順序も逆になります。Parameters.append
.CreateParameter("@UserName",200,2,40)
.Parameters.append .CreateParameter(" @UserID",3,1,4,UserID)
. 実行
end with
if MyComm(0) = 1 then
UserName = MyComm(1)
else
UserName = "このユーザーは存在しません"
end if
Set MyComm = Nothing
6. ストアド プロシージャパラメータとレコードセットを同時に返す
ストアド プロシージャが必要になる場合があります。たとえば、ページングにストアド プロシージャを使用する場合、レコード セットや合計データ ボリュームなどのパラメータを同時に返す必要があります。時間。以下はページング処理用のストアド プロシージャです。
/*SP6*/
CREATE PROCEDURE dbo.getUserList
@iPageCount int OUTPUT, --総ページ数
@iPage int, --現在のページ番号
@iPageSize int --ページごとのレコード数
as
set nocount on
begin
--一時テーブルを
作成しますcreate table #t (ID int IDENTITY, --自動インクリメント フィールド
userid int,
username varchar(40))
--一時テーブルにデータを書き込みます
insert into #t
select userid, dbo.[UserInfo] からのユーザー名
userid 順
-- レコードの合計数を
取得します destroy @iRecordCount int
set @iRecordCount = rowcount
-- 合計ページ数を決定します
IF @iRecordCount%@iPageSize=0
SET @iPageCount=CEILING(@ iRecordCount/@iPageSize)
ELSE
SET @iPageCount=CEILING(@iRecordCount/@iPageSize)+1
-- 要求されたページ番号が総ページ数より大きい場合、最後のページを表示
IF @iPage > @iPageCount
SELECT @iPage = @iPageCount
-- 現在のページの開始レコードと終了レコードを
決定します DECLARE @iStart int --start Record
DECLARE @iEnd int --end Record
SELECT @iStart = (@iPage - 1) * @iPageSize
SELECT @iEnd = @iStart + @iPageSize + 1
--現在のページ レコードを取得します
select * from # t where ID> @iStart および ID <@iEnd
--一時テーブルを削除します
DROP TABLE #t
--レコードの総数を返します
return
@iRecordCount
end
goIn
上記のストアド プロシージャでは、現在のページ番号とページごとのレコード数を入力し、現在のページ、レコード セット、合計ページ数、および合計レコード数を返します。より一般的には、レコードの総数が戻り値として返されます。以下は、ストアド プロシージャを呼び出す ASP コードです (特定のページング操作は省略されています):
'**ページング ストアド プロシージャを呼び出す**
DIM pagenow,pagesize,pagecount,recordcount
DIM MyComm,MyRst
pagenow = Request("pn")
'Custom この関数は自然数を検証するために使用されます
if CheckNar(pagenow) = false then pagenow = 1
pagesize = 20
Set MyComm = Server.CreateObject("ADODB.Command")
with MyComm
.ActiveConnection = MyConStr 'MyConStr はデータベース接続文字列です.CommandText
= "getUserList" 'ストアド プロシージャ名を指定します。CommandType
= 4 'これがストアド プロシージャであることを示します
。 Prepared = true '最初に SQL コマンドをコンパイルする必要があります。
' 戻り値 (レコードの総数)
.Parameters.Append。 CreateParameter("RETURN",2,4 )
'出力パラメータ (総ページ数)
.Parameters.Append .CreateParameter("@iPageCount",3,2)
'入力パラメータ (現在のページ番号)
.Parameters.append .CreateParameter( "@iPage",3,1,4 ,pagenow)
'入力パラメータ（ページあたりのレコード数）
.Parameters.append .CreateParameter("@iPageSize",3,1,4,pagesize)
Set MyRst = .Execute
end with
if MyRst.state = 0 then 'データが取得されていません。MyRst close
recordcount = -1
else
MyRst.close '注: パラメーター値を取得するには、最初にレコード セット オブジェクトを閉じる必要があります。
Recordcount = MyComm(0)
pagecount = MyComm( 1)
if cint(pagenow)> =cint(pagecount) then pagenow=pagecount
end if
Set MyComm = Nothing
'以下はレコードを表示します
if Recordcount = 0 then
Response.Write "No Record"
elseif Recordcount > 0 then
MyRst.open
do until MyRst.EOF
……
loop
'以下はページング情報を表示します
...
else 'recordcount=-1
Response.Write "パラメータエラー"
end if
上記コードに関して、修正が必要な点は1点だけです説明: レコード セットとパラメーターを同時に返すとき、パラメーターを取得したい場合は、最初にレコード セットを閉じてから、使用するときにレコード セットを開く必要があります。
7. 複数のレコード セットを返すストアド プロシージャ
この記事で最初に紹介するのは、レコード セットを返すストアド プロシージャです。 ASP では、ストアド プロシージャが複数のレコード セットを返す必要がある場合がありますが、これらのレコード セットを同時に取得するにはどうすればよいでしょうか。この問題を説明するために、userinfo テーブルに usertel と usermail という 2 つのフィールドを追加し、ログインしたユーザーのみがこれら 2 つのコンテンツを表示できるように設定します。
/*SP7*/
CREATE PROCEDURE dbo.getUserInfo
@userid int,
@checklogin bit
as
set nocount on
begin
if @userid is null または @checklogin is null return
select username
from dbo.[usrinfo]
where userid=@userid
--if Log in user, get usertel and usermail
if @checklogin=1
select usertel,usermail
from dbo.[userinfo]
where userid=@userid
return
end
go
以下は ASP コードです:
'**複数のレコード セットを返すストアド プロシージャを呼び出す**
DIM checklg,UserID,UserName,UserTel,UserMail
DIM MyComm,MyRst
UserID = 1
'checklogin() は、訪問者がログインしているかどうかを判断するカスタム関数です
checklg = checklogin()
Set MyComm = Server.CreateObject("ADODB.Command" )
with MyComm
.ActiveConnection = MyConStr 'MyConStr はデータベース接続文字列です。CommandText
= "getUserInfo" 'ストアド プロシージャ名を指定します。CommandType
= 4 'これがストアド プロシージャであることを示します。Prepared
= true 'SQL コマンドをコンパイルする必要がありますfirst.Parameters.append
.CreateParameter ("@userid",3,1,4,UserID)
.Parameters.append .CreateParameter("@checklogin",11,1,1,checklg)
Set MyRst =
.Set MyComm
で終了を
実行= Nothing
'最初からレコード セットから値を取得
UserName = MyRst(0)
'MyRst が Nothing でない場合は
2 番目のレコード セットから値を取得
Set MyRst = MyRst.NextRecordset()
UserTel = MyRst(0)
UserMail = MyRst(1)
end if
Set MyRst = Nothing
上記のコードでは、Recordset オブジェクトの NextRecordset メソッドを使用して、ストアド プロシージャによって返される複数のレコード セットを取得します。
これまで、この記事では、ASP がストアド プロシージャを呼び出すさまざまな状況について比較的包括的に説明してきました。最後に、ASP プログラムで複数のストアド プロシージャを呼び出すさまざまな方法について説明します。
ASP プログラムでは、少なくとも次の 3 つの方法で複数のストアド プロシージャを呼び出すことができます。
1. 複数のコマンド オブジェクトを作成します
。DIM MyComm
Set MyComm = Server.CreateObject("ADODB.Command")
'ストアド プロシージャ 1 を呼び出します
。 ...
Set MyComm = Nothing
Set MyComm = Server.CreateObject("ADODB.Command")
'ストアド プロシージャ 2 を呼び出します
...
Set MyComm = Nothing
...
2. Command オブジェクトを 1 つだけ作成し、呼び出しを終了するときにそのオブジェクトをクリアしますパラメータ
DIM MyComm
Set MyComm = Server.CreateObject("ADODB.Command")
'ストアド プロシージャ 1 を呼び出します
...
'パラメータをクリアします (パラメータが 3 つあると仮定します)
MyComm.Parameters.delete 2
Parameters.delete 1
MyComm.Parameters。 delete 0
' ストアド プロシージャ 2 を呼び出し、パラメータをクリアします
。
Set MyComm = Nothing
このとき、パラメータをクリアする順序はパラメータ宣言の順序と逆であることに注意してください。理由はわかりません。 。
3. Parameters データ コレクションの Refresh メソッドを使用して、Parameter オブジェクトをリセットします。
DIM MyComm
Set MyComm = Server.CreateObject("ADODB.Command")
' ストアド プロシージャ 1 を呼び出します
...
' Parameters データ コレクションに含まれるすべての Parameter オブジェクトをリセットします
MyComm.Parameters.Refresh
'呼び出しストアド プロシージャ 2
...
Set MyComm = Nothing
オブジェクトを繰り返し作成するのは効率が低い方法であると一般に考えられていますが、テスト (テスト ツールは Microsoft Application Center Test) 後、予期しない結果が得られます。
方法 2 > = 方法 1 >> 方法 3
方法 2 の実行速度は方法 1 以上です (最大約 4% 高速) これら 2 つの方法の実行速度は、方法 3 よりもはるかに高速です (最大 130)。 %) なので、パラメータが多い場合は方法 1 を使用し、パラメータが少ない場合は方法 2 を使用することをお勧めします。
ASP でストアド プロシージャを呼び出す際の私の表面的な経験の一部を最終的に文書化するのに 1 日かかりました。その中には、結果だけが分かっていて原因が分からないものもあり、間違っているものもあるかもしれませんが、これらは全て私個人の実践によるものです。読者の皆様、どうか批判的に受け止めてください。異なるご意見がございましたら、事前にお知らせください。