| MSDE FunClub |
|
最終更新日 : 2000/07/12 |
|
Microsoft Data Engine FunClub
|
Since 2000.07.12
|
|
SQLServer7.0/MSDE 完全トレーニングテキスト（上巻） |
|
【第７章３７９ｐ 〜 ３８８ｐ掲載】 |
-- [SQL07_01.SQL]
-- SQLServer7.0 Transact-SQL言語
-- 日本技術ソフト開発 堀川 明
-- http://www.horikawa.ne.jp/msde/
--
-- このSQLプログラムは、
-- (1)AccessのNorthwind[社員]テーブルを社員コードで検索します
-- (もちろん、SQLServer側へ転送したものを使います)
-- (2)検索結果を1度にクライアント側へ返すのではなく、
-- 作業用テーブルに検索結果を保存します
-- (3)検索結果をページ単位に分割して、クライアントからの
-- 要求に応じて指定されたページの検索結果だけを小出し
-- にして返します
-- このような処理を、カーソルを使わずに作成したものです
--
-- カレントデータベースを MySampleTest にする
USE MySampleTest
GO
-- (件処理されました)のメッセージを抑止する
SET NOCOUNT ON
GO
-- *******************************************************
-- [検索結果記録用テーブル]
-- レコード検索で得られた主キーの値（社員コード）を記憶します
-- マルチユーザ環境で使われるので、検索を依頼したユーザを特定
-- する工夫が必要です
-- *******************************************************
-- 既存テーブルが存在したら削除します
IF( (object_id('検索HIT') IS NOT NULL) AND
OBJECTPROPERTY( object_id('検索HIT'),'ISTABLE') = 1 )
BEGIN
PRINT '検索HIT テーブルが存在したので削除しました'
DROP TABLE 検索HIT
END
GO
-- 検索結果保存用テーブル
CREATE TABLE 検索HIT (
-- 検索結果に付番を振るための番号が必要です。ID列を用意します
NO int IDENTITY(1,1) ,
-- 多人数が同時に検索を実行することを想定し、その検索結果の区別が必要
-- ユーザＩＤの識別だけでは不十分。ログイン名が異なってもdboユーザに
-- なる人が多い
-- ここでは厳密を期すため、SQLServerがユーザ管理を行なうために使う３個
-- の識別子を全部組み合わせます
-- 検索命令を実行したコンピュータ端末識別子
-- HOST_ID()関数は、接続端末IDを返します
HID char(8) DEFAULT HOST_ID() ,
-- 検索命令を実行したログイン識別子
-- SUSER_SID()関数は、ログイン識別子を返します
SID int DEFAULT SUSER_SID() ,
-- 検索命令を実行したデータベースユーザ識別子
-- USER_ID()関数は、データベースユーザ識別子を返します
UID int DEFAULT USER_ID() ,
-- 検索結果で得られた主キーの値です
社員コード int ,
-- このテーブルの主キーは、NO 列です
CONSTRAINT PK_検索HIT PRIMARY KEY ( NO )
)
-- 検索依頼者の識別子列にインデックスを付ける
CREATE INDEX IDX_HSUID ON 検索HIT ( HID , SID , UID )
GO
-- **********************************************
-- 検索表示の作業用テーブル
-- 検索結果のNO付番の最小値を記憶する
-- 検索表示処理でその都度最小値を探すのを省く
-- **********************************************
-- 既存テーブルが存在したら削除します
IF( (object_id('検索HIT作業用') IS NOT NULL) AND
OBJECTPROPERTY( object_id('検索HIT作業用'),'ISTABLE') = 1 )
BEGIN
PRINT '検索HIT作業用 テーブルが存在したので削除しました'
DROP TABLE 検索HIT作業用
END
CREATE TABLE 検索HIT作業用 (
-- 検索命令を実行したコンピュータ端末識別子
HID char(8) ,
-- 検索命令を実行したログイン識別子
SID int ,
-- 検索命令を実行したデータベースユーザ識別子
UID int ,
-- 検索HIT の付番NO の最小値を記憶します
START_NO int ,
-- 検索で得られたレコード件数を記録する
TOTAL int
)
GO
-- *************************************************
-- 検索実行プロシージャ
-- レコードを検索し、その主キーを作業用テーブルに登録
-- *************************************************
-- 既存プロシージャが存在したら削除します
IF( (object_id('P_検索実行') IS NOT NULL) AND
OBJECTPROPERTY( object_id('P_検索実行'),'IsProcedure') = 1 )
BEGIN
PRINT 'P_検索実行 プロシージャが存在したので削除しました'
DROP PROCEDURE P_検索実行
END
GO
--/////////////////////////////
CREATE PROCEDURE P_検索実行
--/////////////////////////////
-- 社員コード番号の検索範囲
@Start int = 0 , -- 開始番号
@End int = 999 -- 終了番号
AS
--このストアドプロシージャ実行時に表示される
-- (件処理されました)のメッセージを抑止
SET NOCOUNT ON
DECLARE @TOTAL int -- 検索で得られたレコード件数
DECLARE @HID char(8) -- コンピュータ端末識別子
DECLARE @SID int -- ログイン識別子
DECLARE @UID int -- Database ユーザ識別子
-- **********************************************************
-- SELECT,INSERT,DELETEなどの権限調査を行なう
-- 権限が無くて動かなかった というエラーを事前にチェック
-- **********************************************************
-- 社員表に対するSELECT権限調査を行う
IF ( (PERMISSIONS(OBJECT_ID('社員')) & 1) <> 1 )
BEGIN
RAISERROR('社員表に対してSELECT権限がありません',16,1)
RETURN -1
END
-- 検索HIT表に対するSELECT権限調査を行う
IF ( (PERMISSIONS(OBJECT_ID('検索HIT')) & 1) <> 1 )
BEGIN
RAISERROR('検索HIT表に対してSELECT権限がありません',16,1)
RETURN -1
END
-- 検索HIT表に対するINSERT権限調査を行う
IF ( (PERMISSIONS(OBJECT_ID('検索HIT')) & 8) <> 8 )
BEGIN
RAISERROR('検索HIT表に対してINSERT権限がありません',16,1)
RETURN -1
END
-- 検索HIT表に対するDELETE権限調査を行う
IF ( (PERMISSIONS(OBJECT_ID('検索HIT')) & 16) <> 16 )
BEGIN
RAISERROR('検索HIT表に対してDELETE権限がありません',16,1)
RETURN -1
END
-- 検索HIT作業用に対するINSERT と DELETE権限調査を行う
-- この例のように、複数の権限を同時にチェックすることができる
IF ( (PERMISSIONS(OBJECT_ID('検索HIT作業用')) & 24) <> 24 )
BEGIN
RAISERROR('検索HIT作業用表に対してINSERTまたはDELETE権限がありません',16,1)
RETURN -1
END
-- *****************
-- 権限は OK です
-- *****************
-- 今、実行している人です
SELECT @HID = HOST_ID() ,
@SID = SUSER_SID(),
@UID = USER_ID()
-- 前回の検索結果を削除します
DELETE FROM 検索HIT WHERE((HID = @HID) AND (SID=@SID) AND (UID = @UID))
DELETE FROM 検索HIT作業用 WHERE((HID = @HID) AND (SID=@SID) AND (UID = @UID))
-- 新しい検索を実行し、その主キーを作業テーブルに登録する
INSERT INTO 検索HIT ( 社員コード )
SELECT 社員コード FROM 社員
WHERE (社員コード BETWEEN @Start AND @End)
ORDER BY 社員コード
-- 検索で得られたレコード件数の値（挿入レコード件数の値）
SELECT @TOTAL = @@ROWCOUNT
-- クライアント側に検索結果を教えます
SELECT @TOTAL AS 検索結果レコード件数
-- PRINT '検索結果のレコード総数 = ' + STR(@TOTAL)
-- 検索結果が得られましたか？
IF( @TOTAL = 0 ) RETURN 0
-- ID列の最小番号を記憶する
INSERT INTO 検索HIT作業用(HID,SID,UID,START_NO,TOTAL)
SELECT @HID AS HID , @SID AS SID , @UID AS UID ,
( SELECT MIN(NO) FROM 検索HIT
WHERE((HID = @HID) AND (SID=@SID) AND (UID = @UID)) ) AS START_NO,
@TOTAL AS TOTAL
RETURN @TOTAL
GO
-- *************************************************
-- ページ単位の検索表示用プロシージャ
-- 第nページ目の検索結果をクライアントに送信します
-- *************************************************
-- 既存プロシージャが存在したら削除します
IF( (object_id('P_検索表示') IS NOT NULL) AND
OBJECTPROPERTY( object_id('P_検索表示'),'IsProcedure') = 1 )
BEGIN
PRINT 'P_検索表示 プロシージャが存在したので削除しました'
DROP PROCEDURE P_検索表示
END
GO
--/////////////////////////////////
CREATE PROCEDURE P_検索表示
--/////////////////////////////////
@Page_No int = 1 , -- 表示を行なうページ番号(1,2,3...)
@Page_Data int = 1 , -- １ページに表示するレコード個数
@Mode int = 0 -- ストアドプロシージャの実行モード
-- 0:レコードSELECT出力（標準）
-- 1:出力するレコード件数の取得
-- 普通は１ページあたりの個数と同じ
AS
--*****************************************************************
-- 【注意】
-- Access2000のADPでは、この 『P_検索表示』ストアドプロシージャが
-- 複雑過ぎて、このストアドプロシージャから返されるレコードセット
-- の列名が把握できないようです。
-- フォームのレコードソースプロパティで、この『P_検索表示』がリスト
-- ボックスで選択できるのに、実際選択すると、この『P_検索表示』オブ
-- ジェクトが見つからないというエラーが表示されます
-- 原因を調べると、レコードソースで選択したものは、必ずその出力される
-- 列名が把握できないといけないようです。
-- フォームデザインの［表示］−［フィールドリスト］を実行し、レコード
-- ソースから返されるフィールド名一覧表を見ると、複雑なストアドプロシ
-- ージャを選択すると、フィールド名表示が空欄になります。
-- 簡単なストアドプロシージャは、フィールドリストに出力される列名が
-- 表示されます
-- 何とかして、このストアドプロシージャから返される列名を認識させる
-- 方法がないか、いろいろ調べました。
-- 結局、次のGOTO文を入れると、無事認識してくれることがわかりました。
-- だからみなさんも、複雑なストアドプロシージャと連結するフォームを
-- 作成する場合は、このようなテクニックを使ってください。
--
-- なおこのテクニックは、私が見つけたものです
-- 参考文献としてこの本を紹介せずに、あたかも、自分が発見したような
-- 記述があったら怒りますよ！ (^.^;;
-- 問題を回避するプログラムテクニック部分も、著作物に含まれるかな？
--（参考文献でこの本を紹介しているなら、許しますけど...）
--
-- 願わくば、こんな苦労は、勘弁して欲しいです
--
-- ****************************************************************
--このGOTO文は、Access2000のADPに対して、このストアドプロシージャが
--返す列名を認識させるためにあります
--SQLServer7.0(SP1)では、この方法で上手くいきました
GOTO L100
--ここに、このストアドプロシージャが出力する列名を列挙します
SELECT 社員.社員コード , 社員.フリガナ , 社員.氏名 FROM 社員
L100:
--*** 本来のプログラムです ***
SET NOCOUNT ON -- メッセージの抑止
DECLARE @HID char(8) -- コンピュータ端末識別子
DECLARE @SID int -- ログイン識別子
DECLARE @UID int -- Database ユーザ識別子
DECLARE @START_NO int -- ID列の付番開始番号（第１ページ目の最初）
DECLARE @SEARCH_ST int -- 検索開始番号
DECLARE @SEARCH_ED int -- 検索終了番号
DECLARE @TOTAL_REC int -- 検索結果で得られたレコード件数
DECLARE @MAX_NO int -- 付番最大値
DECLARE @EndOfPage int -- 0:正常終了 1:これ以上の検索はできません
DECLARE @RecordTotal int -- 出力するレコード件数
-- パラメータの最低限のテスト
IF( @Page_No < 1 )
BEGIN
RAISERROR('@Page_NO パラメータの値がおかしい = %d です' ,
16 , 1 , @Page_No )
RETURN -1
END
IF( @Page_Data < 1 )
BEGIN
RAISERROR('@Page_Data パラメータの値がおかしい = %d です' ,
16 , 1 , @Page_Data )
RETURN -1
END
-- 今、実行している人です
SELECT @HID = HOST_ID() ,
@SID = SUSER_SID(),
@UID = USER_ID()
-- 最小のNO（START_NO）と検索結果のレコード総数を取得する
-- この付番レコードに記録された社員番号が第１ページ目の最初です
SELECT @START_NO = START_NO ,
@TOTAL_REC = TOTAL
FROM 検索HIT作業用
WHERE((HID = @HID) AND (SID=@SID) AND (UID = @UID))
IF (@@rowcount = 0 )
BEGIN
IF( @Mode = 0 )
BEGIN
--標準動作時（レコード出力）はエラー出力を行なう
RAISERROR('検索第１ページ目が見つかりません HID=%s SID=%d UID=%d'
,16,1,@HID,@SID,@UID)
RETURN -1
END
-- =1 レコード件数取得
SELECT @RecordTotal = 0
GOTO L9999
End
-- ページ番号を0から始める
SELECT @Page_No = @Page_No - 1
-- 付番上限値の計算
SELECT @MAX_NO = @START_NO + @TOTAL_REC -1
-- @EndOfPage変数が 1 のときは、レコード表示はできません
SELECT @EndOfPage = 0
-- 第nページ目の 検索開始番号を計算する
-- 付番は連続で振られているものと仮定する
SELECT @SEARCH_ST = @START_NO + @Page_No*@Page_Data
IF (@SEARCH_ST > @MAX_NO)
BEGIN
IF( @Mode = 0 )
BEGIN
-- 標準動作モードです
RAISERROR('検索開始番号が最終結果を超えた ST(%d) > MAX(%d)'
,16,1,@SEARCH_ST , @MAX_NO )
RETURN 1
END
-- =1 レコード件数取得
SELECT @RecordTotal = 0
GOTO L9999
End
-- 検索終了番号
SELECT @SEARCH_ED = @START_NO + @Page_No*@Page_Data + @Page_Data - 1
-- 付番最大値を超えますか？
IF (@SEARCH_ED >= @MAX_NO)
BEGIN
-- 最終検索になります
SELECT @SEARCH_ED = @MAX_NO
SELECT @EndOfPage = 1
END
-- *****************************************
-- 第 n ページに表示するレコードを検索する
-- *****************************************
IF( @Mode = 0 )
BEGIN
-- 次のようなSQL文でもよい
-- JOINする前に候補値を明示的に検索させるように指示した
--** SELECT 社員.社員コード , 社員.フリガナ , 社員.氏名
--** FROM 社員 INNER JOIN (
--** SELECT 社員コード , NO FROM 検索HIT
--** WHERE ( NO BETWEEN @SEARCH_ST AND @SEARCH_ED )
--** ) AS HITTBL
--** ON 社員.社員コード = HITTBL.社員コード
--** ORDER BY HITTBL.NO
--** RETURN @EndOfPage
SELECT 社員.社員コード , 社員.フリガナ , 社員.氏名
FROM 社員 INNER JOIN 検索HIT
ON 社員.社員コード = 検索HIT.社員コード
WHERE ( 検索HIT.NO BETWEEN @SEARCH_ST AND @SEARCH_ED )
ORDER BY 検索HIT.NO
RETURN @EndOfPage
END
-- //////////////////////////////
-- 表示予定のレコード件数の取得
-- //////////////////////////////
SELECT @RecordTotal = 0
SELECT @RecordTotal = COUNT(社員.社員コード) FROM 社員
INNER JOIN 検索HIT ON 社員.社員コード = 検索HIT.社員コード
WHERE ( 検索HIT.NO BETWEEN @SEARCH_ST AND @SEARCH_ED )
L9999:
--レコードセットの形式で、クライアントに出力します
SELECT @RecordTotal AS 表示件数
--RETURN値としても同じ値を返します
RETURN @RecordTotal
GO
-- ************************************************************
-- ［Access2000 ADPプロジェクト］
-- ［P_検索表示］を使ってフォーム連結するための、再同期コマンドの作成
-- 社員番号が渡されますので、レコードを出力します
-- ************************************************************
-- 既存プロシージャが存在したら削除します
IF( (object_id('P_再同期CMD') IS NOT NULL) AND
OBJECTPROPERTY( object_id('P_再同期CMD'),'IsProcedure') = 1 )
BEGIN
PRINT 'P_再同期CMD プロシージャが存在したので削除しました'
DROP PROCEDURE P_再同期CMD
END
GO
--////////////////////////////////
CREATE PROCEDURE P_再同期CMD
--////////////////////////////////
@社員番号 int -- Access2000から渡されます
AS
SELECT 社員.社員コード , 社員.フリガナ , 社員.氏名
FROM 社員
WHERE ( 社員.社員コード = @社員番号 )
GO
