| MSDE FunClub |
|
最終更新日 : 2000/08/18 |
|
Microsoft Data Engine FunClub
|
Since 2000.08.18
|
|
SQLServer7.0/MSDE 完全トレーニングテキスト（上巻） |
|
【第７章４３３ｐ 〜 ４４０ｐ掲載】 |
-- [SQL07_07.SQL]
-- SQLServer7.0 Transact-SQL言語
-- 日本技術ソフト開発 堀川 明
-- http://www.horikawa.ne.jp/msde/
--
-- このSQLプログラムは、キーセットドリブンカーソル
-- を使ったページ単位のレコード表示処理です
--
-- カレントデータベースを MySampleTest にする
USE MySampleTest
GO
-- (件処理されました)のメッセージを抑止する
SET NOCOUNT ON
GO
-- *********************************************************
-- 【指定された名前のストアドプロシージャを削除する】
-- *********************************************************
IF( (object_id('DELETE_MyPROC') IS NOT NULL) AND
OBJECTPROPERTY( object_id('DELETE_MyPROC'),'IsProcedure') = 1 )
BEGIN
PRINT '既存のストアドプロシージャDELETE_MyPROC を削除しました'
DROP PROCEDURE DELETE_MyPROC
END
GO
--////////////////////////////////
CREATE PROCEDURE DELETE_MyPROC
--////////////////////////////////
@proc_name varchar(40) -- ストアドプロシージャの名前
AS
IF( (object_id(@proc_name) IS NOT NULL) AND
OBJECTPROPERTY( object_id(@proc_name),'IsProcedure') = 1 )
BEGIN
PRINT '既存のストアドプロシージャ' + @proc_name + 'を削除しました'
EXECUTE( N'DROP PROCEDURE ' + @proc_name )
END
GO
-- ******************************************
-- 【共通ストアドプロシージャ】
-- グローバルカーソル処理で、共通に使われます
-- ******************************************
-- *********************************************************
-- 【グローバルカーソルを定義します】
-- カーソル名やSQL文などは、ストアドプロシージャの引数で渡されます
-- *********************************************************
DELETE_MyPROC 'DEFINE_CURSOR' -- 既存のものを削除する
GO
--////////////////////////////////
CREATE PROCEDURE DEFINE_CURSOR
--////////////////////////////////
@cur_name varchar(40) , -- グローバルの名前
@cur_sql varchar(512) , -- カーソルを定義するSQL文
@cur_up varchar(128) = '' -- FOR UPDATE of 句
AS
SET NOCOUNT ON
DECLARE @sql varchar(512)
-- グローバルカーソルを定義します
SET @sql = 'DECLARE ' + @cur_name + ' CURSOR ' +
'GLOBAL SCROLL KEYSET ' +
'OPTIMISTIC ' +
'TYPE_WARNING ' +
'FOR ' + @cur_sql + ' ' + @cur_up
-- グローバルカーソルなので、この接続期間中はどこでも参照OK
EXECUTE( @sql )
GO
-- *********************************************************
-- 【指定位置からのページ単位レコードの取得】
-- *********************************************************
DELETE_MyPROC 'FETCH_CURSOR' -- 既存のものを削除する
GO
--//////////////////////////////
CREATE PROCEDURE FETCH_CURSOR
--//////////////////////////////
@table_name varchar(20) , -- 作業用テーブル名
@fetch_procnm varchar(40) , -- カーソル操作ストアドプロシージャ名
@start int , -- レコード表示開始位置
@total int -- １ページ分に表示するレコード総数
AS
DECLARE @sql varchar(512)
DECLARE @Ret int
SET NOCOUNT ON
-- 指定されたページに対応するレコードを作業用テーブルに挿入する
EXECUTE @Ret = @fetch_procnm @start , @total
-- レコードが全然ありませんか？
IF( @Ret = -1 ) RETURN -1
-- 作業用テーブルの内容を全部出力します
-- ここで出力すると連結ができない
-- Access2000-ADPのことを考え、ここではSELECT文を実行しません
--** EXECUTE( 'SELECT * FROM ' + @table_name )
RETURN @Ret
GO
-- ******************************************
-- 【個別ストアドプロシージャ】
-- グローバルカーソル毎に作成する必要があります
-- ******************************************
-- ***************************************************************
-- 【グローバルカーソルを破棄する】
-- カーソルが未定義状態で呼び出されてもいいように対応すること
-- My_cur_initの中で呼び出されるので、先に記述しましょう
-- ***************************************************************
DELETE_MyPROC 'My_cur_term' -- 既存のものを削除する
GO
--//////////////////////////////
CREATE PROCEDURE My_cur_term
--//////////////////////////////
AS
SET NOCOUNT ON
DECLARE @ST SMALLINT
--グローバルカーソルの状況を取得する
SELECT @ST = CURSOR_STATUS( 'global' , 'My_cur' )
-- グローバルカーソルを閉じる
IF( @ST >= 0 ) CLOSE My_cur
-- 参照関係を解除する
IF( @ST >= -1 ) DEALLOCATE My_cur
GO
-- ***************************************************************
-- 【グローバルカーソルを作成します】
-- ***************************************************************
DELETE_MyPROC 'My_cur_init' -- 既存のものを削除する
GO
--//////////////////////////////
CREATE PROCEDURE My_cur_init
--//////////////////////////////
@st int , -- 検索開始番号
@ed int -- 検索終了番号
AS
SET NOCOUNT ON
DECLARE @sql varchar(512)
-- 既存のグローバルカーソルが有効のときは、それを破棄します
EXECUTE My_cur_term
-- カーソルを定義するSQL文を作成します
SET @sql = 'SELECT 社員コード,フリガナ,氏名 FROM MySampleTest..社員 ' +
'WHERE ( 社員コード BETWEEN ' + STR(@st) + ' AND ' + STR(@ed) + ') ' +
'ORDER BY 社員コード '
-- カーソルを定義する
EXECUTE DEFINE_CURSOR 'My_cur' , @sql
-- グローバルカーソルを開く
OPEN My_cur
-- グローバルカーソルにあるレコード総数をクライアント側に出力します
-- カーソルは、同期カーソルです
SELECT @@CURSOR_ROWS AS 検索結果レコード件数
--関数値でも、その値を戻します
RETURN @@CURSOR_ROWS
GO
-- ***************************************************************
-- 【グローバルカーソルのページ単位操作オペレーション】
-- 1)このストアドプロシージャは、グローバルカーソル毎に個別作成が
-- 必要です
-- 2)このプロシージャは、グローバルカーソルの指定位置から指定された
-- レコード数を一時テーブルに記憶します。
-- 3)一時テーブルに記憶されたレコードを、SELECT文で出力します
--【注意】
-- グローバルカーソルが未定義状態で呼び出されてもいいように対応すること
-- ***************************************************************
DELETE_MyPROC 'Main_GetRecord_From_My_cur' -- 既存のものを削除する
GO
--////////////////////////////////////////////
CREATE PROCEDURE Main_GetRecord_From_My_cur
--////////////////////////////////////////////
@fetch_procnm varchar(40) , -- カーソル操作を実際に行なうストアドプロシージャ名
@start int , -- レコード表示開始位置
@total int -- １ページ分に表示するレコード個数
AS
-- Access2000 ADPプロジェクトで、このストアドプロシージャが返す
-- レコードセットの列名リストを、Accessに教えます
-- GOTO文とラベルの間に、ストアドプロシージャのレコードセットと
-- 同じものを定義してください
GOTO L100
SELECT 社員.社員コード , 社員.フリガナ , 社員.氏名 FROM 社員
L100:
--**** プログラム開始 ****
DECLARE @Ret int
--グローバルカーソルが作成済みですか？
IF CURSOR_STATUS( 'global' , 'My_cur' ) = -3
BEGIN
RETURN -1 -- カーソルが存在しません
END
-- Test
--raiserror('START=%d total=%d',16,1,@start , @total)
--return
-- カーソルから取得したレコードを記憶する一時テーブルを作成する
-- この一時テーブルは、このストアドプロシージャが終わると自動消滅します
CREATE TABLE #TMP_My_cur(
社員コード int ,
フリガナ varchar(80) ,
氏名 varchar(40)
)
-- 共通SP(ストアドプロシージャFETCH_CURSOR)の中で全部処理します
-- 一時テーブルが共通SPの中では作成できないため、このSPが必要です
-- また一時テーブルは下位SPの中まで有効なことを活用します
EXECUTE @Ret = FETCH_CURSOR
'#TMP_My_cur' , -- 上で作成した作業用テーブル名
@fetch_procnm , -- カーソル操作を実際に行なうストアドプロシージャ名
@start , -- レコード表示開始位置
@total -- １ページ分に表示するレコード個数
--ここで連結する
SELECT 社員.社員コード, 社員.フリガナ , 社員.氏名 FROM 社員
INNER JOIN #TMP_My_cur on 社員.社員コード = #TMP_My_cur.社員コード
-- FETCH_CURSORストアドプロシージャの結果を戻す
RETURN @Ret
GO
-- *********************************************************************
-- 【グローバルカーソル My_cur の現在行の内容を一時テーブルに記憶する】
-- 1)このストアドプロシージャは、グローバルカーソル毎に個別作成が
-- 必要です
-- 2)このプロシージャは、グローバルカーソルの指定位置から指定された
-- レコード数を一時テーブルの中に書き込む実務作業を担当します。
-- *********************************************************************
DELETE_MyPROC 'GetRecord_From_My_cur' -- 既存のものを削除する
GO
--/////////////////////////////////////////
CREATE PROCEDURE GetRecord_From_My_cur
--/////////////////////////////////////////
@pos int , -- 読み込み開始位置
@rec_page int -- 読み込みレコード数
AS
SET NOCOUNT ON
DECLARE @社員コード int
DECLARE @フリガナ varchar(80)
DECLARE @氏名 varchar(40)
DECLARE @Ret int
DECLARE @Flag int
-- 指定された行番号に移動する
FETCH ABSOLUTE @pos FROM My_cur INTO @社員コード , @フリガナ , @氏名
-- 移動が成功したらその内容を一時テーブルに挿入する
SET @Flag = @@FETCH_STATUS
IF( @Flag = -1 )
BEGIN
RETURN -1 -- そのレコード番号は存在しません
END
IF( @Flag = 0 ) -- 正常に指定位置に移動ができました
BEGIN
INSERT INTO #TMP_My_cur VALUES( @社員コード , @フリガナ,@氏名 )
SET @rec_page = @rec_page - 1
END
-- 残りのレコードを読み込む
WHILE( @rec_page <> 0 )
BEGIN
-- １行読み込む
FETCH NEXT FROM My_cur INTO @社員コード , @フリガナ , @氏名
SET @Flag = @@FETCH_STATUS
IF( @Flag = 0 )
BEGIN
-- 正常読み込み
INSERT INTO #TMP_My_cur VALUES( @社員コード , @フリガナ , @氏名 )
SET @rec_page = @rec_page - 1
END
ELSE IF( @Flag = -1 )
RETURN -2 -- EOF? を検出しました(レコードが途中で無くなりました)
END
RETURN 0
GO
-- *********************************************************
-- 【プログラム使用見本例】
-- *********************************************************
-- グローバルカーソルを作成します
EXECUTE My_cur_init 100 , 400
GOTO L1000
PRINT '1行目から５行目の出力(1)'
EXECUTE Main_GetRecord_From_My_cur 'GetRecord_From_My_cur' , 1 , 5
PRINT '1行目から５行目の出力(2)'
EXECUTE Main_GetRecord_From_My_cur 'GetRecord_From_My_cur' , 1 , 1
EXECUTE Main_GetRecord_From_My_cur 'GetRecord_From_My_cur' , 2 , 1
EXECUTE Main_GetRecord_From_My_cur 'GetRecord_From_My_cur' , 3 , 1
EXECUTE Main_GetRecord_From_My_cur 'GetRecord_From_My_cur' , 4 , 1
EXECUTE Main_GetRecord_From_My_cur 'GetRecord_From_My_cur' , 5 , 1
PRINT '1行目から５行目の出力(3)'
EXECUTE Main_GetRecord_From_My_cur 'GetRecord_From_My_cur' , 1 , 2
EXECUTE Main_GetRecord_From_My_cur 'GetRecord_From_My_cur' , 3 , 2
EXECUTE Main_GetRecord_From_My_cur 'GetRecord_From_My_cur' , 5 , 1
L1000:
PRINT '1行目から１５行目の出力(1)'
EXECUTE Main_GetRecord_From_My_cur 'GetRecord_From_My_cur' , 1 , 15
PRINT '1行目から１５行目の出力(2)'
EXECUTE Main_GetRecord_From_My_cur 'GetRecord_From_My_cur' , 1 , 10
EXECUTE Main_GetRecord_From_My_cur 'GetRecord_From_My_cur' , 11 , 5
-- グローバルカーソルを破棄します
EXECUTE My_cur_term
GO
