1. miltood

    Posted

    miltood
Changes in title
+SQL Server ストアドプロシージャの書き方
Changes in tags
Changes in body
Source | HTML | Preview
@@ -0,0 +1,79 @@
+#SQL Server ストアドプロシージャの書き方
+
+DBの値を加工するロジックについて、webアプリとデスクトップアプリの両方で必要となった。それぞれのプログラムで実装するよりも、一つのロジックを利用する方が保守性がよいため、ストアドプロシージャを使うことにした。
+ストアドプロシージャは特殊な用途において強力な反面、使う機会が少ないため書き方をすぐに忘れてしまう。
+そこで、書き方のポイントを記事にしておく。
+##環境
+WIndow 10 64bit
+SQL Server 2016(SP1)
+SQL Server 2016 Management Studio
+##ストアドの作成方法
+SQL Server Management Studioのオブジェクトエクスプローラーからデータベースを選択してから「新しいクエリ」をクリックし、入力ウィンドウを開く。以下のような空のCREATE文を書き、Testという名前のプロシージャを作る。
+
+```SQLQuery1.sql
+CREATE PROCEDURE [dbo].[Test]
+
+
+AS
+
+RETURN
+```
+
+メニュー -> クエリ -> 実行 でストアドプロシージャが作成され、データベースに登録される。
+##ストアドの修正方法
+メニュー -> データベース -> プログラミング -> ストアドプロシージャ から既存のストアドを探して右クリック -> 変更をクリックする。見付からない場合は、右クリック -> 最新の情報に更新 をクリックするか、F5キーを押す。
+![sutoado3PNG.PNG](https://qiita-image-store.s3.amazonaws.com/0/241999/9471c68c-f4ef-11b8-3a69-feec23334dfb.png)
+
+変更をクリックすると、以下のようにALTER文が開かれる。修正を加えたら、メニュー -> クエリ -> 実行 でALTER文が実行され、ストアドの内容が更新される。
+
+```SQLQuery1.sql
+USE [ProductMng]
+GO
+/****** Object: StoredProcedure [dbo].[Test] Script Date: 2018/03/27 17:40:01 ******/
+SET ANSI_NULLS ON
+GO
+SET QUOTED_IDENTIFIER ON
+GO
+ALTER PROCEDURE [dbo].[Test]
+ @INPUT_STR VARCHAR,
+ @INPUT_INT INT
+AS
+DECLARE @TMP_DATE DATETIME
+DECLARE @TMP_INT INT
+
+SET @TMP_INT = @INPUT_INT + 1
+
+SELECT @TMP_DATE, @TMP_INT, TestData FROM TestTable;
+```
+
+##パラメタの渡し方
+ALTER文の下の2行がパラメタ。一つ目はINPUT_STRがパラメタ名で、VARCHARは可変長文字列型であることを表している。二つ目はINPUT_INTがパラメタ名で、INTは数値型を表す。もっと増やしたいときは、カンマで区切って行を追加するとよい。AS句以降でアットマーク+パラメタ名で変数として使える。
+##変数の使い方
+DECLARE文の下の2行で宣言しているのが変数。一つ目はTMP_DATEが変数名で、DATETIMEは日付型を示す。二つ目はTMP_INTが変数名で、INTは数値型を示す。その下のSET文のように、値を代入できる。
+##プログラムからの呼び出し方
+###Transact-SQL
+Management Studio からSQLを使って呼び出すには、「新しいクエリ」を開き、データベースを選択してから以下のようにEXEC文、半角スペース、一つ目のパラメタ、カンマ、二つ目のパラメタ というふうに書いて実行する。
+
+```SQLQuery1.sql
+EXEC Test 'mojiretsu', 26
+```
+
+
+実行結果はこんな感じ。
+![sutoado4.PNG](https://qiita-image-store.s3.amazonaws.com/0/241999/3aae3689-c769-3a17-ca47-8caf4a6ae5f7.png)
+取得できた行の値のうち、一番目の列はNULLとなった。これは変数に値を入れていないため。二番目の列は、パラメタとして渡した26に、ストアドによって+1された値が返された。三番目はデータベースに格納されていたTestDataというデータの値。
+###Python
+言語にかかわらず、上記と同様の命令文を呼び出す側のプログラムで書けばよい。例としてPythonでは以下のように実装する。
+
+```test.py
+import pyodbc
+
+def GetTestData(str_param = "mojiretsu", int_param = 26):
+ cnxn = pyodbc.connect(connectionString)
+ cursor = cnxn.cursor()
+ qry = "EXEC Test '" + str_param + "', " + str(int_param)
+ cursor.execute(qry)
+ row = cursor.fetchone()
+
+```
+