【SQL Server(連載)】スカラ関数 -26-

www.pon-x.jp

前回の続き

今回は、関数を自作する際の説明をしていきます。

スカラー関数

スカラ―関数とは、単一の値を返してくれる関数であり、例えば同様の処理をたくさんやるときなどに関数定義をしておくと、簡単な構文で実装できることになります。

使い方はこんな感じ

CREATE FUNCTION [関数名] (
    [変数] [データ型],
    [変数] [データ型],
    [変数] [データ型],
    ・・・
)  
RETURNS [戻値]
AS
BEGIN

    --##############--
    --　何らかの処理
    --##############--

    RETURN [戻値]  

END

では、さっそくつくってみよう。
今回試してみるのは、年月日を入力したら年度を返してくれる関数をつくってみます。
こんなかんじです。

CREATE FUNCTION JP_YEAR (
    @YMD DATE
)  
RETURNS INT
AS
BEGIN

    DECLARE @YYYY INT
    
    IF MONTH(@YMD) < 4
        SET @YYYY = YEAR(@YMD) - 1
    ELSE
        SET @YYYY = YEAR(@YMD)


    RETURN @YYYY

END

実際の画面はこんなかんじ。

構文の説明

分解して説明していきます。

CREATE FUNCTION JP_YEAR (
    @YMD DATE
)  
RETURNS INT

上記は、JP_YEARという関数について、日付型の@YMDを引数にして定義しました。
なお、戻り値は整数型です。

BEGIN

    DECLARE @YYYY INT
    
    IF MONTH(@YMD) < 4
        SET @YYYY = YEAR(@YMD) - 1
    ELSE
        SET @YYYY = YEAR(@YMD)


    RETURN @YYYY

END

BEGIN~ENDで処理をひとまとめにしてあげています。
処理の中で、@YYYYを定義し、最終的にはこれを戻り値にしてあげています。
＠YYYYには、引数に入力された年月日の月が4未満であれば、年から1引いた値を、
そうでなければ、引数の年を代入してあげています。

実行してみる

スカラ関数は、SELECT句やWHERE句などさまざまな場面で活用できます。
ひとまずSELECT句に入れてみます。

SELECT 
    dbo.JP_YEAR('2020/12/11'),
    dbo.JP_YEAR('2021/1/11'),
    dbo.JP_YEAR('2021/4/11')

こんなかんじの出力です。

しっかり動いてそうですね。

ちなみに、dboとはスキーマといいますが、今の時点ではそう書くんだなとおもっていてください。ｍ（＿　＿）ｍ
そのうち説明したいと思います。

その他

関数を
変更する際は、ALTER FUNCTION ~~
削除する際は、DROP FUNCTION
になります。テーブルの作成と同じですね。

以上、今回はここまで。

【SQL Server(連載)】変数 -25-

www.pon-x.jp

前回の続き

今回は、変数の扱いについて説明していきます。

変数宣言

これまで、変数というのは使わずにきましたが、SQLでも変数を使う場面というのは多分にあります。
例えば、システムに組み込むときに、WHERE句の抽出条件を可変にしたいときに、引数から代入したいときなど変数を使うことになろうと思います。

では、早速使い方の説明です。
まずは、変数の作り方です。
変数を作ることを、変数を宣言するといいます。
以下の通り変数宣言できます。

DECLARE @変数名 データ型

※変数名の頭には必ず@をつけてください。

具体的にやってみます。
数値型の変数と、10文字までの文字型変数を作ります。
※データ型についてもっと調べたい方は、ここあたりがよいかとおもいます。

DECLARE  @NUM INT
DECLARE  @TXT NVARCHAR(10)

こんなかんじで変数宣言ができます。

変数に値を代入する。

変数が出来たら値を入れないと意味がありません。
変数に値を入れる方法はこんな感じです。

SET @変数名 = 値

具体的に値を入れてみるとこんな感じです。

SET @NUM = 123
SET @TXT = 'あいうえお'

変数宣言と代入をセットで行うこともできる。

表題のままだが、そんなこともできる。

DECLARE @変数名 データ型 = 値

具体的にはこんな感じ

DECLARE  @NUM INT = 123
DECLARE  @TXT NVARCHAR(10) = 'あいうえお'

変数を使ってみる

変数はさまざまなところに応用できます。
個々ではいくつかの例を試してみたいと思います。

Person.Person テーブルからBusinessEntityIDをWHERE句で抽出する際に、値を変数からもってくるようにします。

DECLARE  @NUM INT = 123

SELECT * FROM Person.Person WHERE BusinessEntityID = @NUM

結果は以下の通り

変数の値を変えることで、抽出結果を変えられるのでこれから先の応用では必要な手法になってきます。

IF文に使ってみる

DECLARE  @NUM INT = 123
DECLARE  @TXT NVARCHAR(10) = 'あいうえお'

IF @NUM = 123
    PRINT @NUM
ELSE
    PRINT @TXT

結果はこんな感じ

結果からわかるように、作成した変数はスカラなので、活用場所がスカラであればほぼどこでも使えます。

以上、今回はここまで。

東京創業ステーションにてプログラミングセミナーを開催しました！！

12月4日(金)に東京創業ステーションにて、Pythonを始めてみたいという方向けにセミナーを開催させていただきました。
ちなみに告知はこんなかんじでした。

startup-station.jp

普段は高校教師をしているため、社会人向けでさらにフルリモートでの講義は経験なく不安もありましたが、大変ご好評いただきました！！
多くの方がプログラミングを楽しいとかんじていただいたようでとてもやったかいがあったとおもいます。
ご支援いただいた、東京創業ステーションの関係者のみなさまには本当に感謝でございます。m(＿ ＿)m

当日の資料はしばらく公開しておくので、参加された方は復習に、気になるなーと思われる方は見ていただけれ幸いです。

www.pon-x.jp

学校教育でもプログラミング教育が必要になってきている状況の中でさまざまな場面でアルゴリズムを学びたいという需要がみえてきたところ、これからもこんな普及活動に努めていきたいと思っています。

職員研修や新人研修、各種セミナー等ご検討のかたいらっしゃいましたら、ぜひご相談いただければ可能な限りお手伝いさせていただきます。ぜひともご依頼ください！！ お問い合わせはこちらよりお願いします！！

引き続き、これからもプログラ生活をよろしくお願いいたします☆

【SQL Server(連載)】IF文 -24-

www.pon-x.jp

前回の続き

今回はIF ELSE文をやります。

PRINTコマンド

IF文とは直接関係ありませんが、PRINTコマンドというのを使いながらIF文の動きを見ていこうと思います。
SELECTはテーブルのデータを出力していたのに対して、PRINTはコメントが表示されるということに違いがあります。

PRINT('ABCDE')

こんなかんじに出力されます。

IF ELSE文

では、本題に移ります。 IF ELSE文は、もし〇〇が真なら××を実行、そうでなければ△△を実行するということを行うのが目的です。
では試しに書いてみましょう。

IF 1 = 1
    PRINT('A')
ELSE
    PRINT('C')

以下の通り「A」が出力されます。

これは、1=1が真であるため、IFの中身が実行されました。

では、以下はどうでしょう。

IF 1 = 2
    PRINT('A')
ELSE
    PRINT('C')

以下の通り「C」が出力されます。

これは、1=2が偽であるため、ELSEの中身が実行されました。

さまざまな比較

上記では、等しいかという比較だけでしたが、さまざまな比較方法があります。
簡単に紹介します。

-- 否定 (3つとも、１と２は等しくないという表し方)
IF NOT 1 = 2
    PRINT('A')

IF 1 != 2
    PRINT('B')

IF 1 <> 2
    PRINT('C')

--　大小関係
IF 1 < 2 -- 2は１より大きい
    PRINT('D')

IF 1 <= 2　-- 2は1以上
    PRINT('E')

こんなかんじ

BEGIN ～ END

次に以下を試しに実行してみてください。

IF 1 = 1
    PRINT('A')
    PRINT('B')
ELSE
    PRINT('C')

以下の通りエラーになります。
これは、IFの中身に複数の処理がかかれているためにそうなりました。
SQLServerのIF ELSE文はその中で処理は1回しかおこなえません。
しかし、複数処理したいということはけっこうあるので、この問題を解決してくれるのがBEGIN ～ ENDです。
複数処理をBEGIN ～ ENDで括ってあげると、それらをまとめて一つの処理とみなしてくれます。

試しに以下の通りかいてみます。

IF 1 = 1
    BEGIN
        PRINT('A')
        PRINT('B')
    END
ELSE
    PRINT('C')

無事に2つのPRINTコマンドが実行されました。

応用

では、これまで学んできたことと組み合わせて使ってみたいと思います。
以下のSQLに特別な意図はなく、あくまで練習なのでそういうようにとらえてください。

IF (SELECT PhoneNumberTypeID FROM Person.PersonPhone WHERE BusinessEntityID = 1) = 1
    BEGIN
        UPDATE Person.Person
        SET Suffix = 1
        WHERE BusinessEntityID = 1

        SELECT Suffix FROM Person.Person WHERE BusinessEntityID = 1
    END
ELSE
    PRINT('PASS')

説明をすると、Person.PersonPhoneのBusinessEntityIDが1のレコードを抽出し、さらにカラムをPhoneNumberTypeIDだけに抽出します。
BusinessEntityIDｈPrimary keyなのでスカラの値で比較ができます。
もし、この結果が1と等しければ、Person.PersonのBusinessEntityIDが1のレコードにおけるSuffixを1に更新する処理を行い、さらにその結果をSELECTで表示します。
もし、IF文が偽であれば「PASS」という文字列をコメント表示させるという内容です。

ちなみに結果は真なので、以下の通りになるはずです。

以上、今回はここまで。

【SQL Server(連載)】ビューの変更・削除 -23-

www.pon-x.jp

前回の続き

前回からビューをやっておりますが、今回はその続きです。
今回はビューの変更と削除を行います。

ビューの確認

まず、ビューってどこにしまわれているの！？という疑問があろうと思うので、格納場所を確認しておきます。
SSMSの左側にオブジェクトエクスプローラーがデフォルトで表示されていると思います。
その中から、「データベース」→「AdventureWorks2019」→「ビュー」を開くとビューの一覧が表示され、自分が作成したビューも確認できます。
こんなかんじです。

ビューの変更

ビューの中身を変更する方法を紹介します。
ちなみに、前回作ったビューの中身をおさらいしておきます。

CREATE VIEW TEST_VIEW
AS
SELECT 
    PersonType,
    COUNT(PPP.BusinessEntityID) AS CNT
FROM Person.Person PP
    INNER JOIN Person.PersonPhone PPP
        ON PP.BusinessEntityID = PPP.BusinessEntityID
WHERE PPP.PhoneNumberTypeID = 1
GROUP BY PersonType

こんなかんじでした。
これについて、WHERE句を変更してみたいと思います。

やり方はとっても簡単です。
CREATEだった部分をALTERに変えるだけ。
あとは、本文の変更したい箇所を変えておきましょう。

ALTER VIEW TEST_VIEW
AS
SELECT 
    PersonType,
    COUNT(PPP.BusinessEntityID) AS CNT
FROM Person.Person PP
    INNER JOIN Person.PersonPhone PPP
        ON PP.BusinessEntityID = PPP.BusinessEntityID
WHERE PPP.PhoneNumberTypeID = 2
GROUP BY PersonType

こんなかんじに変更できました。

既存のビューの中身を確認する

変更するときに、すでに作成されているビューを確認して修正したいっていうときはよくあって、そんなときは参照したうえで修正をします。
オブジェクトエクスプローラーから「対象のビューを右クリック」→「ビューのスクリプト化」→「新規作成」→「新しいクエリ　エディタ　ウインドウ」を選択
以下の通りです。

以下のような画面が出てくると思います。

すでに、ALTER表記がされているので、それ以下のSQLを書き換えてあげるだけでOKです。
便利ですよね！！

ビューの削除

ビューの削除は細かいことはありません。
DROPで行えます。

DROP VIEW TEST_VIEW

こんなかんじです。

以上、今回はここまで。

【SQL Server(連載)】ビューの作成 -22-

www.pon-x.jp

前回の続き

複雑なSQLを作成していた場合に、簡単に呼び出す方法のひとつにビューがあります。
今回は、ビューの登録方法を紹介したいと思います。

CREATE VIEW

例えば、以下のようなSQLがあったとします。

SELECT 
    PersonType,
    COUNT(PPP.BusinessEntityID) AS CNT
FROM Person.Person PP
    INNER JOIN Person.PersonPhone PPP
        ON PP.BusinessEntityID = PPP.BusinessEntityID
WHERE PPP.PhoneNumberTypeID = 1
GROUP BY PersonType

結果はこんな感じ

例えば、この集計結果は定期的に値が変わるので頻繁に出力したいというときに、上記のSQLを保存しておいたら便利ですよね。
ざっくりいうとビューとはその目的が一つあります。

CREATE VIEW TEST_VIEW
AS
SELECT 
    PersonType,
    COUNT(PPP.BusinessEntityID) AS CNT
FROM Person.Person PP
    INNER JOIN Person.PersonPhone PPP
        ON PP.BusinessEntityID = PPP.BusinessEntityID
WHERE PPP.PhoneNumberTypeID = 1
GROUP BY PersonType

上記でビューが作成できました。

ビューの活用

さきほど、SQLを保存しておく目的があるといいましたが、使ってみてわかる利点としてテーブルとして再加工ができるという利点があります。

では早速活用してみよう。
まずは、通常に呼び出す。 FROM句に入れてあげればOKです。

SELECT * FROM TEST_VIEW

結果は以下の通り

再加工も簡単です。
単純にビューをテーブルだとおもっていじれはいいのです！！
ためしに並び替えをしてみる。

SELECT * 
FROM TEST_VIEW  
ORDER BY CNT

結果はこんな感じ

以上、今回はここまで。
次回も引き続きビューをやります。

【SQL Server(連載)】テーブルの削除 -21-

www.pon-x.jp

前回の続き

今回はテーブルの削除です。
削除は、定義も何もわずらわしいことはないので、とっても簡単です！！

DROP TABLE

前回作ったテーブルを削除してみましょう。

DROP TABLE Telephone_List

結果はこんな感じ。

ないものは消せない

上記のSQLをもう一度実行するとエラーになります。

こんなかんじ

すでに一度削除しているので存在しませんから、エラーになります。

例えば、UPDATE文やDELETE文なんかは変更レコード数が0行でもエラーにはなりません。
これは変更したいレコードの抽出結果がなかったということにすぎないということになります。

一方、ないものは消せないということで、存在しないテーブルを削除しようとするとエラーになります。

あった時だけ削除する。

では、テーブルが存在した場合にのみ削除を行い、存在しなかったときはエラーにしないにはどうすればよいか。
答えはこんなかんじ。

DROP TABLE IF EXISTS テーブル名

以下の通り、エラーが出ませんでした。

テーブルの存在チェックに絡めて削除するときってあるの？？と疑問に思いますよね。
今後やりますが、SQLを複数組み合わせて処理をするという場面に応用していく際には結構使っていくものなのですよ！





以上、今回はここまで。