ホチキス先生の「プログラマーと呼ばれたい」

InfoPath & SQL Server !

Archive for the ‘ストアドプロシージャ’ Category

Microsoft Azure SQL Database を使う – その1 – 概要

leave a comment »

Microsoft Azure SQL Database はクラウド上に展開する SQL Server だ。Microsoft Azure を利用すると、クラウド上にデータベースを構築することができ、サーバー管理の必要なくデータベースを利用できる。Microsoft Azure SQL Database にデータベースを構築し、Windows Form でデータベースアプリケーションを作って利用する概要は次のとおりである。

まず Microsoft Azure にユーザー登録し、SQL Database を作成する。作成した SQL Server データベースの管理は、Microsoft Azure アカウントの管理ポータルで行う。

SQL_Azure_001_mid_640

<Fig.1 : Microsoft Azure アカウントの管理ポータル>

データベースを作成したら、管理ポータルから SQL Database のページへ移動し、デザインのボタンでテーブルを作成することができる。テーブル作成は SQL Server Management Studio のテーブル作成画面とほぼ同じなので、SQL Server のデータベース開発やAccessを使った経験があれば簡単に使うことができるだろう。

SQL_Azure_002_mid_640

<Fig.2 : Microsoft Azure アカウントの管理ポータルからテーブルを作成する>

Visual Studio による開発では、Microsoft Azure アカウントの管理ポータルから、SQL データベース用のスタートプロジェクトをダウンロードする、のリンクをクリックすると、Visual Studio のプロジェクトがダウンロードできる。このプロジェクトには、ここで作成した Microsoft Azure SQL Database がサーバーオブジェクトとして登録されたプロジェクトになっている。

このプロジェクトを Visual Studio Community 2013 で開くと、オンプレミスの SQL Server でデータベースアプリケーションを開発するのとほとんと同じ手順で Windows Form データベースアプリケーションを開発することができる。

SQL_Azure_003_mid_640

<Fig.3 : Microsoft Visual Studio Community 2013 による Windows Form データベースアプリケーション開発>

Microsoft Visual Studio Community 2013 では全くコードを書くことなく、数ステップのウィザードで1対1でテーブルと関連付けるデータベースアプリケーションを作ることができる。ビルドして実行すると Microsoft Aure SQL Dabase のデータを編集することができる。

SQL_Azure_004

<Fig.4 : Microsoft Visual Studio Community 2013 で作った Windows Form データベースアプリケーションを実行する>

データを追加しデータベースに保存する。Microsoft Azure SQL Database にデータがストアされたことは、Microsoft Azure アカウントの管理ポータルでクエリを発行して確かめることができる。たとえば次の SELECT 文を発行する。

——————————————————————–
select ID,sname,sbirthday from students
——————————————————————–

SQL_Azure_005_mid_640

<Fig.5 : Microsoft Azure アカウントの管理ポータルでクエリを発行する>

以上が Microsoft Aure SQL Database にデータベースを作成し、Windows Form データベースアプリケーションで利用する手順だ。Microsoft Azure SQL Database はデータベースをクラウド上にもたせることでサーバーマシンの管理コストをなくすことができ、インターネットに接続できるあらゆる場所から利用することができる。そして Microsoft Azure SQL Database を利用する WIndows Form データベースアプリケーションの作成は、Microsoft Visual Studio Community 2013 を利用するとオンプレミスの SQL Server を利用する場合とほとんど変わらない開発ができ、単にテーブルと1対1のフォームなら全くコードを書くことなく短い時間で開発できる。

広告

神戸三宮の駅前で定期的に開催する神戸SQL Server Users Group(KSSUG)のSQL Serverセミナーは第二回「初級1」と「初級2」を終了した。

leave a comment »

神戸三宮の駅から歩いて数分の貸会議室を借りて、実習形式のSQL Serverセミナーを開催している。昨日は第二回目のセミナーで、「初級1」と「初級2」を連続で実施した。

神戸SQL Serverセミナー「初級1」2013年12月14日(土)http://kobesqlserverusersgroup.wordpress.com/2013/11/26/sql-server%e3%82%bb%e3%83%9f%e3%83%8a%e3%83%bc%e5%88%9d%e7%b4%9a%ef%bc%91-2013%e5%b9%b412%e6%9c%8814%e6%97%a5%ef%bc%88%e5%9c%9f%ef%bc%89-900%ef%bd%9e1030-%e4%b8%89%e5%ae%ae/

神戸SQL Serverセミナー「初級2」2013年12月14日(土)http://kobesqlserverusersgroup.wordpress.com/2013/11/28/sql-server%e3%82%bb%e3%83%9f%e3%83%8a%e3%83%bc%e5%88%9d%e7%b4%9a2-2013%e5%b9%b412%e6%9c%8814%e6%97%a5%ef%bc%88%e5%9c%9f%ef%bc%89-1030%ef%bd%9e1150-%e4%b8%89%e5%ae%ae/

「初級1」はSQL Serverをやってみたいが触ったことのない人を想定して、データベースの作成、テーブル作成から始める実習、「初級2」はユーザー定義関数とストアドプロシージャ作成の基本実習、を行った。1回のセミナーは1時間20分で、ノートパソコンを使った実習形式だ。

今後は高度なSQL文、実務的なストアドプロシージャ、カーソルの使い方、直積やPIVOPビュー、そしてユーザー管理とActiveDirectoryの連携、またVisual Studioを使ったクライアントアプリケーションの開発、など少しずつ内容を深めていきたいと思っている。

ほぼ月1回、できれば月2回程度開催し、少しでも多くの人にSQL Serverに興味と関心を持ってもらいたい。私自身、自学自習の一般ユーザーにすぎないが、この6年間、実務でSQL Serverによるデータベースシステムの開発と運用、管理をやってきた経験をふまえ、ユーザーに知識と技能を伝えられたらと思っている。

神戸SQL Server Users GroupのSQL Serverセミナー(初級1)を実施した

leave a comment »

校務の情報化にはデータベースの知識と技術が必要なことから、SQL Serverを勉強するコミュニティを作った。とりわけ今年度から指導要録のデータベース化が一歩前進することから、各学校の担当者はデータベース化に取り組まざるを得ないはずだ。

神戸SQL Server Users Group
http://kobesqlserverusersgroup.wordpress.com/

昨日、2013年11月9日(土)には神戸SQL Server Users Groupと特定非営利活動法人 情報技術相互支援協会の共催で、SQL Serverセミナー(初級1)を開催した。

このSQL Serverセミナーでは、SQL Serverの初心者がSQL Serverの基礎を学び、データベースを使ったシステムを作り運用することができるようにすることを目標としている。1回のトレーニングを90分とし、初級講座を1、2と分けて企画した。初級1ではSQL Management Studioの使い方、テーブル作成、データベースダイアグラム、ビューとリレーションシップ、データの型、簡単なSQL文、を内容とした。初級2ではストアドプロシージャ、トリガ、ユーザー定義関数、データベースダイアグラム、SQLの応用、を計画している。

初級セミナーは1と2でおおよそ基本を網羅するように考えたが、やはり90分では深まりに欠ける。あらかじめ用意した初級1のプリントをひととおり網羅したが、やりたいと思っていた直積やPIVOTまで説明できなかったし、SQL文もごく基本的なものに留まった。次回は初級1.5的なセミナーとし、フォローアップを考えている。

今後もSQL Serverセミナーは神戸三宮で定期的に開催する予定だ。日程は決まり次第、神戸SQL Server Users Groupのblog http://kobesqlserverusersgroup.wordpress.com/ に公開するので、興味がある人はチェックしてほしい。

SQL Server : フィールドにIDENTITYを設定して自動的に連番が設定されるようにしたテーブルでIDENTITY_INSERTをONにして明示的に数値を指定したデータをINSERTする

with one comment

データを管理するとき、自動的に連番が設定されるようにしたい場合がある。これは例えばMicrosoft Accessでいう「オートナンバー型」のフィールドに相当する。Microsoft Accessの「オートナンバー型」は、データを生成するときに何も値を入れず、自動的に連番が設定されるフィールドだ。テーブルを構成するとき、このように自動的に連番が割り当てられるフィールドを作っておくと、その連番値自体に特段の意味はなくても、データを管理する場面で有効になる。システムの運用をはじめると、さまざまな想定外のことがおこる。間違ったデータを大量に作ってしまい特定の範囲のデータを全部まとめて修正しなければならない、重複したデータを作ってしまい後から作ったデータを削除しなければならない、ある一定期間に行われた処理に誤りがあり一括してデータを修正しなければならない、といったようなこことだ。これらの運用時に、データが生成された順番がわかっていると処理しなければならないデータの範囲を絞り込む重要な手掛かりになる。

Microsoft Accessには次のように「オートナンバー型」というデータ型がある。

SQL_identity_002_mid_640

<Fig.1 : Miceosoft Access 2013でフィールドのデータ型をオートナンバー型にする>

しかしSQL ServerにはAccessのようなオートナンバー型というデータ型はない。SQL ServerでAccessのオートナンバー型のように、数値が連番で自動的に設定されるようにするには、データ型を「bigint」、「decimal」、「int」、「numeric」、「smallint」、「tinyint」のような整数の性質を持っているものとし、列のプロパティで「IDENTITYの指定」の「(IDである)」を「はい」にし、「IDの増分」と「IDENTITYシード」を設定する。「IDENTITYシード」は最初に割り当てられる数値を意味し、「IDの増分」はデータが増えるたびに加えられる値を意味する。次の例は「IDENTITYシード」を10、「IDの増分」を3として作ったテーブルにデータをいくつか追加した例だ。

SQL_identity_004_mid_640

<fig.2 : IDフィールドのIDENTITYを指定し、IDENTITYシードを10、IDの増分を3と設定する>

SQL_identity_006_mid_640

<fig.3 : IDフィールドにIDENTITYを指定したテーブルにデータを3件追加した>

なおIDENTITYの設定は「float」、「real」のような実数型では指定することができない。また「money」、「smallmoney」でも指定することができない。

IDENTITYを指定したフィールドには明示的に値を指定することはできず、自動的に値が割り当てられるだけになる。例えば次のようなINSERT文は実行できない。

——————————————————————

insert into Table_1(ID,[values]) values(20,’testinsert’)

——————————————————————

SQL_identity_007_mid_640

<fig.4 : IDENTITYを指定したフィールドに明示的に値を指定したINSERT文を実行しエラーとなった>

IDENTITYを指定したフィールドは、たとえば次のように明示的に値を指定せずにINSERT文を実行することになるだろう。

——————————————————————

insert into Table_1([values]) values(‘testinsert’)

——————————————————————

SQL_identity_008_mid_640

<fig.5 : IDENTITYを指定したフィールドに明示的に値を指定せずINSERT文を実行した>

SQL_identity_009_mid_640

<fig.6 : IDENTITYを指定したフィールドに明示的に値を指定せずINSERT文を実行した結果>

INSERTされたデータのIDは、フィールドに指定した「IDの増分」だけ増えた値が自動的に割り当てられている。もちろんこれは期待どおりの結果だが、データの保守管理上はこのままでは困る場合が出てくる。たとえばあるテーブルのデータを別のテーブルに一時的に退避し、後に戻したいといった場合だ。データを退避するため「table_1」と同じフィールド構造をもつ「Table_2」を作っておくが、「Table_2」の「ID」フィールドのIDENTITYは設定しない。そして次のクエリのように、「Table_1」のデータを「Table_2」にコピーし、「Table_1」のデータを消去するが、そのあと、「Table_2」のデータを「Table_1」の戻すときにはエラーとなる。

——————————————————————

insert into Table_2(ID,[values]) select ID,[values] from Table_1
delete Table_1
insert into Table_1(ID,[values]) select ID,[values] from Table_2 –(エラーになる)

——————————————————————

SQL_identity_010_mid_640

<fig.7 : IDENTITYが設定されているフィールドに明示的に値を指定してINSERTしようとしてエラーになった>

このエラーを回避する最もわかりやすい方法は、「Table_1」の「ID」列のIDENTITY指定を一時的に解除することだ。しかし一時的ではあれテーブルのデザインを変更すると、そのあいだに他のユーザーが正しい方法でデータを挿入しようとしたときにエラーになるだろう。自分だけが使うデータベースであるとか、システムが小規模で他に与える影響がないと考えられる場合はいいかもしれないが、望ましいことではない。

このようにIDENTITYが設定されたフィールドに明示的に値を与えたいとき、IDENTITY_INSERTをONにする方法がある。先のエラーになったINSERT文の前後にIDENTITY_INSERT文を使おう。例えば次のクエリはエラーにならず成功する。

——————————————————————

set identity_insert Table_1 on
insert into Table_1(ID,[values]) select ID,[values] from Table_2
set identity_insert Table_1 off

——————————————————————

SQL_identity_011_mid_640

<fig.8 : IDENTITY_INSERTの設定によりINSERTが成功した>

set identity_insert Table_1 onによってIDENTITYを設定したフィールドにも明示的に値を与えることができるようになり、set identity_insert Table_1 offでデフォルトの状態に戻る。

たとえば次のようにIDに「20」という値を明示的に挿入するクエリも実行することができる。

——————————————————————

set identity_insert Table_1 on
insert into Table_1(ID,[values]) values(20,’ID20testdata’)
set identity_insert Table_1 off

——————————————————————

SQL_identity_012_mid_640

<fig.9 : IDENTITYを設定したIDフィールドに明示的に「20」の値を設定してINSERTした>

SQL_identity_013_mid_640

<fig.10 : IDENTITYを設定したIDフィールドに明示的に「20」の値を設定してINSERTした結果>

このように、テーブルにIDENTITYを設定したIDフィールドを持たせることはよくやることだが、IDENTITYを指定したフィールドには通常は明示的な値を与えることはないし、それはできないのだが、identity_insertをon、offすることによって明示的に値を与えることができ、これはデータの管理の手法として知っておくとよい。

SQL Server – ストアドプロシージャの雛形を作成した

leave a comment »

SQL Serverでストアドプロシージャを作成する時間が多くなった。管理のための作業をストアドプロシージャにする作業が多くなったからだ。そこで新しいストアドプロシージャを作るときに、雛形となるものを作ってみた。

このストアドプロシージャでは、引数として「対象」と「年度」を設定している。もちろん引数の意味を変更することや増やすことも可能だ。また処理の履歴を「処理記録」テーブルにストアすることにしている。そのとき、システムから「年度コード」、「日付」、「処理者」を取得し、パラメーターとして一緒にストアしている。これにより処理の履歴を追跡しやすくしている。参考になれば幸いである。

———————————————————————————————————————————-

create procedure ストアドプロシージャ雛形

    @P_対象 as varchar(50),
    @P_年度 as char(2)

as

/*
使い方

<実行例> execute ストアドプロシージャ雛形 ‘男性’,’10’

*/

–処理定数の取得
    declare @今年度 as char(2)
    declare @日付時間 as smalldatetime
    declare @処理者 as varchar(50)

set @今年度 = (select nendo_cd from const_nendo where now_cd = 1)
set @日付時間 = getdate()
set @処理者 = substring(suser_sname(),6,6)

–実際の処理
if @P_対象 = ‘男性’
begin

insert into 処理記録(日付,職員番号,備考)
values (@日付時間,@処理者,’「ストアドプロシージャ雛形」を実行した。引数は、’ + ‘引数1(対象):’ + @P_対象
        + ‘ 引数2(年度):’ + @P_年度 + ‘ 今年度 :’ + @今年度)

end

if @P_対象 = ‘女性’
begin

insert into 処理記録(日付,職員番号,備考)
values (@日付時間,@処理者,’「ストアドプロシージャ雛形」を実行した。引数は、’ + ‘引数1(対象):’ + @P_対象
        + ‘ 引数2(年度):’ + @P_年度 + ‘ 今年度 :’ + @今年度)

end

———————————————————————————————————————————-

Written by Yoshio Matsumoto

2011年7月5日 at 12:54 AM