| MSDE FunClub |
|
最終更新日 : 2000/11/23 |
|
Microsoft Data Engine FunClub
|
Since 2000.11.23
|
|
SQLServer7.0/MSDE 完全トレーニングテキスト（下巻） |
|
【第２３章４７１ｐ 〜 ４７６ｐ掲載】 |
-- [ADPTEST.SQL]
-- SQLServer7.0 Transact-SQL言語
-- 日本技術ソフト開発 堀川 明
-- http://www.horikawa.ne.jp/msde/
--
-- このSQLプログラムは、
-- Access2000 ADPプロジェクトのサーバー側環境設定
-- プログラムです
-- データベースやテーブル、ストアドプロシージャなど
-- 必要なオブジェクトをすべて作成します
--
-- [注意]
-- このプログラムの実行は System Administrators
-- などの権限が必要です
--
-- ( 件処理されました)のメッセージの表示を抑止します
SET NOCOUNT ON
GO
--*********************************
--【データベースを新規に作成する】
--*********************************
IF EXISTS ( SELECT name FROM master..sysdatabases
WHERE( name = 'AC20_ADPTEST' ) )
BEGIN
RAISERROR('AC20_ADPTESTデータベースが存在します。中止しました',16,1)
--*** 削除命令はコメント DROP DATABASE AC20_ADPTEST
RETURN
END
--データベースをデフォルトで作成する
CREATE DATABASE AC20_ADPTEST
GO
--*****************************************
-- 指定されたオブジェクトを削除する
-- ローカル一時ストアドプロシージャの登録
--*****************************************
USE tempdb --接続先DBの定義
IF( (Object_id('tempdb..#Delete_Object') IS NOT NULL) AND
(OBJECTPROPERTY(Object_id('tempdb..#Delete_Object'),'IsProcedure')=1) )
BEGIN
DROP PROCEDURE #Delete_Object
--PRINT '#Delete_Objectを削除しました'
END
GO
CREATE PROCEDURE #Delete_Object
@name sysname , --削除するオブジェクトの名前
@type char(2) --オブジェクトのタイプ
AS
DECLARE @word varchar(20)
DECLARE @cmd varchar(100)
IF EXISTS ( SELECT name FROM AC20_ADPTEST..sysobjects
WHERE( (name=@name) AND (type=@type) ) )
BEGIN
SELECT @word = CASE @type
WHEN 'U ' THEN 'TABLE'
WHEN 'V ' THEN 'VIEW'
WHEN 'P ' THEN 'PROCEDURE'
END
--オブジェクトの削除
SELECT @cmd = 'DROP '+ @word + ' ' + @name
--PRINT 'cmd=' + @cmd
EXEC( @cmd )
END
GO
--*****************************************
-- オブジェクトの削除を行なう
--*****************************************
USE AC20_ADPTEST --接続先を元に戻す
GO
EXEC #Delete_Object '患者' , 'U ' --多側テーブルを先に削除
EXEC #Delete_Object '医師' , 'U ' --１側テーブル
EXEC #Delete_Object '医師VIEW' , 'V '
EXEC #Delete_Object '医師患者' , 'V '
EXEC #Delete_Object '医師患者2' , 'V '
EXEC #Delete_Object 'PROC_医師' , 'P '
EXEC #Delete_Object 'PROC_医師2' , 'P '
EXEC #Delete_Object 'PROC_医師患者' , 'P '
EXEC #Delete_Object 'PROC_医師患者2' , 'P '
EXEC #Delete_Object 'Resync_医師患者', 'P '
EXEC #Delete_Object 'Proc_sp_databases', 'P '
GO
--*****************************************
-- テーブルを新規に作成します
--*****************************************
--【１対多】の１側テーブルの作成
CREATE TABLE 医師 (
医師ID int NOT NULL PRIMARY KEY ,
医師名 varchar(50) NULL ,
医師TEL varchar(50) NULL
)
PRINT '医師テーブルを作成しました'
--【１対多】の多側テーブルの作成
CREATE TABLE 患者 (
患者ID int NOT NULL PRIMARY KEY ,
姓 varchar(50) NULL ,
名 varchar(50) NULL ,
主治医ID int NULL ,
-- 外部参照の定義
FOREIGN KEY( 主治医ID ) REFERENCES 医師 ( 医師ID )
)
PRINT '患者テーブルを作成しました'
GO
-- 【医師】見本データの登録
INSERT INTO 医師 VALUES( 10 , '田中' , '(206)-882-80xx' )
INSERT INTO 医師 VALUES( 20 , '佐藤' , '(206)-936-43xx' )
INSERT INTO 医師 VALUES( 30 , '山田' , '(206)-912-25xx' )
-- 【患者】見本データの登録
INSERT INTO 患者 VALUES( 1 , '鈴木' , '一郎' , 10 )
INSERT INTO 患者 VALUES( 2 , '中村' , '恵子' , 20 )
INSERT INTO 患者 VALUES( 3 , '山田' , '太郎' , 20 )
INSERT INTO 患者 VALUES( 4 , '高橋' , '富夫' , 10 )
GO
--*****************************************
-- ビューを新規に作成します
--*****************************************
CREATE VIEW 医師VIEW
AS
SELECT * FROM 医師
GO
CREATE VIEW 医師患者
AS
SELECT 患者.患者ID ,
患者.姓 ,
患者.名 ,
患者.主治医ID ,
医師.医師名 ,
医師.医師TEL
FROM 患者 INNER JOIN 医師
ON 患者.主治医ID = 医師.医師ID
GO
CREATE VIEW 医師患者2
AS
SELECT 患者.患者ID AS 患者番号 ,
患者.姓 AS 患者姓 ,
患者.名 AS 患者名 ,
患者.主治医ID AS 医師番号 ,
医師.医師名 AS 医師氏名 ,
医師.医師TEL AS 医師電話
FROM 患者 INNER JOIN 医師
ON 患者.主治医ID = 医師.医師ID
GO
--*****************************************
-- ストアドプロシージャを作成します
--*****************************************
CREATE PROCEDURE PROC_医師
AS
SELECT * FROM 医師
GO
CREATE PROCEDURE PROC_医師2
@no int = 0
AS
SELECT * FROM 医師
WHERE( 医師ID = @no )
GO
CREATE PROCEDURE PROC_医師患者
AS
SELECT 患者.患者ID AS 患者番号 ,
患者.姓 AS 患者姓 ,
患者.名 AS 患者名 ,
患者.主治医ID AS 医師番号 ,
医師.医師名 AS 医師氏名 ,
医師.医師TEL AS 医師電話
FROM 患者 INNER JOIN 医師
ON 患者.主治医ID = 医師.医師ID
GO
CREATE PROCEDURE PROC_医師患者2
@st int , --検索開始
@ed int --検索終了
AS
SELECT 患者.患者ID AS 患者番号 ,
患者.姓 AS 患者姓 ,
患者.名 AS 患者名 ,
患者.主治医ID AS 医師番号 ,
医師.医師名 AS 医師氏名 ,
医師.医師TEL AS 医師電話
FROM 患者 INNER JOIN 医師
ON 患者.主治医ID = 医師.医師ID
WHERE( 患者.患者ID BETWEEN @st AND @ed )
GO
--*****************************************
-- 再同期コマンド用ストアドプロシージャ
--*****************************************
CREATE PROCEDURE Resync_医師患者
@no int
AS
SELECT 患者.患者ID AS 患者番号 ,
患者.姓 AS 患者姓 ,
患者.名 AS 患者名 ,
患者.主治医ID AS 医師番号 ,
医師.医師名 AS 医師氏名 ,
医師.医師TEL AS 医師電話
FROM 患者 INNER JOIN 医師
ON 患者.主治医ID = 医師.医師ID
WHERE( 患者.患者ID = @no )
GO
--*****************************************
-- システムストアドプロシージャと連結する
-- 結果セットをクライアント側に表示する
--*****************************************
CREATE PROCEDURE Proc_sp_databases
AS
GOTO SKIP
-- Access ADPのフォームで列名を認識させる
SELECT 'Dummy' AS DATABASE_NAME ,
'Dummy' AS DATABASE_SIZE ,
'Dummy' AS REMARKS
SKIP:
-- ストアドプロシージャを実行する
EXEC sp_databases
GO
PRINT '終了しました'
GO
