T-SQL ストアドプロシージャー編
ストアドプロシージャー
ストアドプロシージャーとは
データベースに対する連続した複数の処理を１つのプログラムにまとめ、データとともに保存できるようにしたもの。
例えば、毎回SELECT文を書くのは手間なので、「こういう処理をしたい時のプログラム」としてあらかじめ作っておくことができる。
例：
「金額に現行の消費税を掛けて・・・」というクエリを毎回書くのは手間
→「消費税を計算する処理」を作っておき保存。
イメージで言えば、様々な処理手順を有した箱がある
→キーワードを入れればそれを加工して完成させてくれる。
消費税計算の例では、「日付」「金額」を入れれば税込金額が帰ってきます。
'2019/09/01', 1000 → 1080
'2019/10/01, 1000 → 1100
そうした「税率計算してくれる箱」「学生の情報を登録してくれる箱」「店舗の新情報を追加してくれる箱」とかをあらかじめ作っておけば、
金額いくら！ 日付いつ！、 名前は〜 番号は〜、 商品名は〜 単価は〜
と引数だけ箱に入れてやればあとは自動（あらかじめ設定した処理）で加工して完成してくれます。
雛形：
IF EXISTS ( SELECT ＊ FROM sys.objects WHERE object_id = OBJECT_ID
(N'[dbo].[ストアド名] ) AND TYPE in (タイプ名)
BEGIN
DROP PROCEDURE [dbo].[ストアド名]
END
GO
CREATE PROCEDURE [dbo].[ストアド名]
@引数 データ型 ＝ 初期値
AS
BEGIN
処理
END
GO
TRANSACTIONやエラー処理はSELECTでは使わなくても大丈夫そうです。
UPDATEやDELETEなど途中で問題が起きたら困る処理には必要です。
IF EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[student_registration]') AND type in N'TF')
BEGIN
END
GO
CREATE PROCEDURE [dbo].[student_registration](
@p_id int = NULL
, @p_name varchar(50) = ''
)
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
DECLARE @error_set table (
id int NULL
, message varchar(50) NULL
)
IF ISNULL(p@_id, 0) < 1
BEGIN
INSERT INTO @error_set(
id
, message
)
SELECT
1
, '学生番号が不正です'
END
ELSE
BEGIN
IF EXISTS(
SELECT students.country_id
FROM students
WHERE (country_id = @p_id)
)
BEGIN
INSERT INTO @error_set(
id
, message
)
SELECT
2
, '学生番号(' + CAST(@p_id AS varchar(8)) + ')は登録済みです'
END
END
IF ISNULL(@p_name, '') = ''
BEGIN
INSERT INTO error_set(
id
, message
)
SELECT
3
, '生徒名は必須入力です'
END
IF EXISTS(
SELECT error_set.id
FROM @error_set AS error_set
)
BEGIN
GOTO EXIT_SPC
END
--更新
INSERT INTO students(
id
, name
)
SELECT
@p_id AS id
, @ISNULL(@p_name, '') AS name
EXIT_SPC:
IF NOT EXISTS(
SELECT error_set.id
FROM @error_set AS error_set
)
BEGIN
COMMIT TRANSACTION
END
ELSE
BEGIN
ROLLBACK TRANSACTION
END
END TRY
BEGIN CATCH
INSERT INTO @error_set(
id
, message
)
SELECT
ERROR_NUMBER()
, '[Error] :' + CHAR(13) + CHAR(10) +
'Procedure: ' + ISNULL(ERROR_PROCEDURE(), '???') + CHAR(13) + CHAR(10) +
'Line: ' + ISNULL(CAST(ERROR_MESSAGE()), 'An unexpected error occured')
ROLLBACK TRANSACTION
END CATCH
--エラー内容返却
SELECT
error_set.id AS id
, error_set AS error_set
FROM @eeror_set AS error.SET
ORDER BY error_set.id ASC
END
GO
