ITとdesign エンジニアのブログ by エンジ庭

IT業界のトレンドなどIT分野について学んだ事を書いていきます。プログラミング技術などについて現在Qiitaメインに書いてますので、そちらを参照ください。  https://qiita.com/kota_sho

T-SQL ユーザー定義関数、エラー処理 他

変数

DECLARE @変数名 型名

SET @変数名 = 代入値

または

SELECT @変数名 = 代入値

 

DECLARE 

   @変数名1 型名

   @変数名2 型名

SELECT

   @変数名1 = 代入値

,          @変数名2 = 代入値

 

テーブル変数

・実行時のみメモリ上に存在する仮想テーブル

・通常の変数同様に処理完了時に自動的に廃棄されるため、データの削除処理やテーブル自体の削除処理は不要

・大量のデータを挿入するとその分メモリを消費するが、高速に処理できる

 

DECLARE @変数名  table {

  列名1 型名 オプション

,      列名2 型名 オプション

}

 

主キーの設定

DECLARE @変数名  table {

  列名1 型名 オプション

,      列名2 型名 オプション

,      PRIMARY  KEY (

,      主キー

  )

 

INSERT  INTO  @変数名  VALUES ( 値1,  値2,  値3)

INSERT  INTO  @変数名  VALUES ( 値1,  値2,  値3)

 

UPDATE @変数名

SET カラム名 = 代入値

FROM @変数名

WHERE(条件)

 

一時テーブル

・変数ではなく本物のテーブルが一時的に作られる

・セッションが有効な間は削除されない(エディタを閉じるまで有効)

・処理は低速だが大量のデータを一時的に扱う場合は効果的

 

CREATE TABLE #テーブル名 {

列名1 型名 オプション

,      列名2 型名 オプション

}

 

エラー処理

・TRYの部分に実行したい処理を書く

→ もしその中でエラーがあったらCATCHに移動

CATCHにはエラーが起きた場合の処理を書く

 

BEGIN TRY

  処理

END TRY

BEGIN CATCH

 エラー処理

END CATCH

 

TRANSACTION ✖️ TRY CATCH の組み合わせ

BEGIN  TRY

    BEGIN TRANSACTION

  処理

    COMMIT TRANSACTION

END TRY

BEGIN  CATCH

 エラー処理

END  CATCH

 

ユーザー定義関数 

CREATE  FUNCTION  関数名

  @引数1 データ型

,      @引数2 データ型

RETURNS 戻り値 データ型

AS

BEGIN

  処理

  RETURN 戻り値

END

GO

 

以下、お店の商品ごとに割引するという処理

下記のようなテーブルがあったとして

idと金額を入れたら割引後の値段を出してくれるfnc_calc_discountという関数を作ります。

例:id1番は10%引きです。

Id

Discount

1

0.1

2

0.2

3

0.3

 

テーブル作成などの処理は省略します。

 

CREATE FUNCTION [dbo].[fnc_calc_discount](
@p_id int
, @p_price money
)
RETURNS money
AS
BEGIN
DECLARE
@w_money money
, @w_discount_rate money

SELECT
@w_discount_rate = campaign_discount.discount
FROM campaign_discount
WHERE(campaign_discount = ISNULL(@p_id, ''))

SELECT @w_money = ISNULL(@p_price, 0) * (1 - ISNULL(@w_discount_rate, 0))

RETURN @money

--実行
SELECT dbo.fnc_discount_rate(1, 500) → 450
SELECT dbo.fnc_discount_rate(2, 500) → 400
SELECT dbo.fnc_discount_rate(3, 500) → 350