第 4 部 関数 関数は引数と戻り値を持ちます。数学で習った、「 f ( x ) 」と同じです。 x に値 を与えると、その値に対応した何らかの一定した答えが返ってきます。 x に与 えた値が引数、返ってくる答えが戻り値です。ただし、実際に使われる関数 は引数を複数持っていたり、戻り値に型があったりと少々複雑です。 関数は、集計を行うための「集計関数」、文字列を扱うための「文字列関数」、 日付を操作するための「日付関数」、テータを変換するための「変換関数」、数 学的な「算術関数」に大きく分類されます。 この中で、集計関数だけは、引数の意味合いが違ってきます。集計関数は 引数に与えられた式をグループ内のすべての行に対して計算を行います。例 えば、集計関数である「 SIJM 」は、引数で与えられた式の値をグループ内にお いて、すべて合計します。その他の関数は、引数で与えられた 1 つの値から 何かしらの計算を行い、 ] つの値を返します。 関数は、戻り値により分類されることもあります。関数の戻り値が、表の ように行と列を返す関数を「表関数」と呼びます。 XMLTABLE が表関数に なります。 1 行だけを返すような関数を「行関数」と呼びます。値を 1 つだけ 返す関数は、「スカラー関数」と呼ばれます。多くの関数がスカラー関数です。 最近では、データ分析に特化した「分析関数」といった関数も一部のデータ べースで利用することができます。分析関数は、集計関数よりも、高度な機 能を持っています。このため、複雑な文法となっていますが、データ分析を 行う上では、非常に有用な関数です。 「ユーサ定義関数」は、ユーザが作成する関数です。独自の計算ロジックを 関数化することで、より高度なクエリーを簡単に発行できるようになります。 ここで紹介する関数は、あらかじめシステムに用意されている関数です。ユ ーザ定義関数については、「第 5 部プロシージャ内で使用できる命令」 ( P453 ) を参照して下さい。

・戻り値 / , の→文字列 RPAD ( s, , 員 , 冂 ) →文字列 RPAD ( s お け 注 点 MySQL OracIe PostgreSQL ・文字列式 ・調整後の文字列長 ・充填する文字列 ーしまし 戻り値 長さことなるように、を右から充填した文字列 文法の説明の中に、「→」がある場合、 当該の演算した結果、どうなるか ( どういう値が返るか ) を示しています。 演算子 当該の関数を実行した結果、どういう戻り値が返るかを示しています。関数 関数 によっては、戻り値が存在しない場合、「→」は表記されていません。 ・実行例 住所録テーブルの年齢列が NULL でない人を SELECT します。 SELECT 氏名 , 年齢 FROM 住所録 WHERE 年齢工 s NOT NULL 氏名 年齢 山田太郎 鈴木花子 32 佐藤次郎 17 田中花子 1 9 高橋次郎 上記の例のように、 SQL ステートメント (SELECT で始まる 1 文 ) の下に、表組 みのようなものがある場合、当該 SQL ステートメントを実行させた結果を示して います。 この場合は、年齢が NULL でない ( 値がある ) 人を住所録テープルから SELECT した結果を表示しています。

CREATE SEQUENCE Å 6 ' 悪準 : 、 シーケンスの作成 CREATE SEQUENCE se 肥〃 ce ーれのれ , e ー INCREMENT BY をⅡ MINVALUE 襯加Ⅱ MAXVALUE ”】 [ START WITH s Ⅱ CYCLE ー NOCYCLE 】 CREATE SE QUENCE se 肥れ ce ーのれ e ー INCREMENT BY をⅡ MINVALUE 襯加Ⅱ MAXVALUE 襯 a 】 [ START WITH s Ⅱ CYCLE ー NO CYCLE 】 CREATE SEQUENCE s 夜肥 e - れのれ e [INCREMENT をⅡ MINVALUE 襯加Ⅱ MAXVALUE ”ェ】 [ START s Ⅱ CYCLE ] バ弖区三ダ 2 れ・ 1 2 3 4 5 6 コマンド命令 racl DB2 ostgre SQL„ Oracle DB2 PostgreSQL ・・作成するシーケンス名 ・シーケンスが N EXTVAL により足し算される値 ・シーケンスの最小値 ・シーケンスの最大値 ・シーケンスの初期値 シーケンスを作成するには「 CREATESEQUENCE 」を使用します。 FINCREMENT BY 」によって、シーケンスが NEXTVAL により足し算される 値を指定できます。デフォルトは 1 です。負の数を指定すると、 NEXTVAL によって シーケンスの値は減算されていきます。 PostgreSQL では、 BY を記述しません。 「 MINVALUE 」、「 MAXVALUE 」には、シーケンスの最小値、最大値を設定で きます。 「 STARTWITH 」では、シーケンスの初期値を決定できます。この値からシー ケンスが始まります。 「 CYCLE 」、「 NOCYCLE 」はどちらかを指定します。デフォルトは NOCYCLE です。シーケンスの値が最小値、最大値に到達した際に CYCLE オプションが指 定されていた場合には MINVALUE の値に戻って続けられます。 NOCYCLE とな っている場合はエラーとなります。 シーケンスは、トランザクションとは無関係に動作する点に注意して下さい。 データ定義命令 aa-J 1 66 .

SQL CONTINUE 命令 CONTINUE CONTINUE 卩佖わ el Ⅱ WHEN 俶ア ss 加れ】 ; ・どのループに戻るかのラベル指定 厄わ el ・ ・先頭に戻る条件式 e ア ? ℃ SS ~ 0 れ・ 「 CONTINUE 」命令を使用すると、ループの先頭に戻って処理が続けられます。 変数 i の値が ] O で割り切れる値の場合に、ループの先頭に戻り、 1 OO 以上になったとき、ループを抜けます。 DECLARE @土土 n し SET @土 0 WH 工 LE @土 < 2 0 Ø BEG 工 N @ 土 + 1 SET @i 工 F ( @土 10 0 ) CONTINUE 工 F ( @土 > = 10 Ø ) BREAK PR 工 NT @土 END Oracle 、 PostgreSQL での CONTINUE は、「 WHEN 」を使用して戻る条件を付 けることができます。 WHEN は省略できます。省略した場合、無条件にループの 先頭に戻ります。 変数 i を O から ] OO までループし、 10 で割り切れるときに CON 羽 NUE します。 DECLARE 土 NUMBER; BEGIN FOR i 工 N Ø . . 1 ØØ LOOP CONT 工 NUE WHEN 土 MOD IØ DBMS_OUTPUT . PUT_L 工 NE い土ニ END LOOP : END : 1 2 3 4 5 6 プロシージャ内で使用できる命令 OracI CONTINUE ostgre SQLServer OracIe PostgreSQL SQLServer ループ処理をするには Oracle ・・ P483 BREAK ・・ ・・ P473 参照 WHILE ・・ 484

1 2 3 4 5 6 関数 BITAND 関数 ~ - DB2 vracl ビットごとの AND BITAND ( , れ 2 ) →整数値 れ 7 ・ 戻値 れ 7 とれ 2 のビット毎の AND 「 BITAND 」は、引数 nl と n2 を「ビットごとに AND 演算」し、その結果を返 します。 Oracle では、ビット操作を行う演算子が存在しません。 OR 演算は + 演 算子で代用できますが、 AND 演算は代用するための演算子がありません。この ため、ビットごとの AND 演算は、 BITAND 関数により行います。 列 a と列 b のビットことの AND 演算を行います。 SELECT a,b ′ BITAND(a ′ b) FROM f 〇〇 BlTAND(a,b) ・任意の整数値 ・任意の整数値 DB2 では、 BITAND の他、 BITOR 、 BITXOR 、 BITNOT 関数も存在します。 参照 & 演算子・ ・・ P241 402

値の変換 DECODE ( % s, , s,r …Ⅱ , d D →変換されたデータ DECODE 関数 、を 0 内 0 racl@ 4 5 6 関数 ・変換対象の式 検索値 ・結果値 ・デフォルト値 戻ツ ] 値 変換された値 ( 結果値またはデフォルト値 ) 「 DECODE 」関数を使用すると、値を評価し任意の「値に変換」することがで きます。 DECODE 関数の引数として、変換元の式 ( 引数 e ) をまず与えます。続 いて変換元の実際の値 ( 引数 s ) 、変換後の値 ( 引数 r ) をセットにして与えます。 このセットが複数続いてもかまいません。最後にどの値とも一致しなかった場合 変 関 に返す値 ( 引数 d ) を与えます。 数 Äcode 列が 1 の場合に文字列 ' success ' を、その他の場合は文字列 ' e 「「 0 「 ' に変換します。 ー error 勹 FROM f 〇 0 SELECT a,DECODE(), 1 ■ success' DECODE(a,1 ,'success','error') a 1 SUCCeSS 0 e 「「 0 r DECODE の使用は数値から文字列の変換にのみに限られているものではあり ません。文字列から文字列の変換や文字列から数値への変換、さらには条件式の 記述も可能ですので、様々な変換に対応することができます。また、 DECODE 関数は「 CASE 」により記述するが可能です。 * postg 「 eSQL と MYSQL の DECODE 関数は、復号化を行う関数です。 4.4 参照 CASE 演算子・ ・・ P278 386

1 2 3 4 5 6 コマンド命令 ALTER TABLE 厄わ厄ーれのれ e ALTER [ COLUMN ] co 襯れコ me { SET DEFAULT d ⅲ市ーの襯 DROP DEFAULT ー SET NOT NULL ー DROP NOT NULL ー TYPE e ーれのれ e } バラメータ ・テーブル名 尻 e ーれのれ e ・ ・列定義 CO ? れれ一 d イわ 0 れ・ ・列名 ・デフォルト値 Oracle では列の属性を変更することができます。ただし、型の変更時は、列の すべての行の値が NULL 値である必要があります。 列の属性を変更するには、「 MOD 旧 Y 」を使用します。 MODIFY に続けて列定 義を記述します。括弧内に、複数の列定義を行うと、複数の列が変更できます。 列 c を VA 日 CHA 日 2 型に変更します。 ALTER TABLE f00 MOD 工 FY (c VARCHAR2 ( 3Ø ) ) DB2 では、「 ALTER COLUMN 」により、列属性の一部を変更することができます。 VA 日 CHA 日型の列 c の最大長を変更します。 ALTER TABLE f 〇 0 ALTER c SET DATA TYPE VARCHAR ( 2Ø ) PostgreSQL では、 ALTER COLUMN により、列属性を変更できます。変更で きるのは、デフォルト値、 NOTNULL 制約、型の 3 つです。列のデフォルト値は、 「 SET DEFAULT 」で変更し、「 DROP DEFAULT 」で削除できます。 NOT NULL 制約は、「 SET NOT NULL 」で設定し、「 DROP NOT NULL 」で 削除できます。 型の変更は、「 TYPE 型」で行います。 列 c にテフォルト値 O を設定します。 ALTER TABLE f00 ALTER c SET DEFAULT Ø ・プライマリキー プライマリキーは、キーワード「 PRIMARYKEY 」によって追加することがで きます。制約名が必要ならば、 CONSTRAINT の後に制約名を付けます。必要な PostgreSQL データ定義命令 aa—l Oracle DB2 POStg 「 eSQL 126

ITERATE 命令 ITERATE 厄わ ; ・どのループの先頭に戻るかのラベル指定 厄わ el ・ DB2 、 MySQL では、 FITERATE 」命令を使用すると、ループの先頭に戻って処 き 理が続けられます。 変数 i の値が 1 0 で割り切れる値の場合に、ループの先頭に戻り、 1 OO 以上になったとき、ループを抜けます。 CREATE PROCEDURE test_iterate ( ) LANGUAGE SQL BEG 工 N DECLARE i INTEGER; SET i 1 〇〇 p ー label : WH 工 LE i < 2ØØ DO SET i 土 + 1 ー 工 F MOD ( i, 1 Ø ) 0 THEN 工 TERATE 100P ー label ー END 工 F; 工 F 土 > = 1 ØØ THEN LEAVE 1 〇〇 p ー label ー END 工 F; END WH 工 LE; END ITERATE D 日 2 ySQ DB2 MySQL ロ卩 ループ処理をするには 参照 LOOP ・・ REPEAT ・・ ・・ P471 ・・ P476 WHILE ・・ LEAVE ・・ ・・ P473 ・・ P485 486 .

SQL erv CREATE FUNCTION 1 D82 racl 2 OStgre 、 3 4 5 《 0 コマンド命令 SQ ストアードファンクションの作成 CREATE ー OR REPLACE ] FUNCTION れ肥 0 れ一れのれ e ー ( 迎襯 s ) 】 RETURN e { IS ー AS } s t のれ夜 CREATE FUNCTION 、ル c 0 れコ曜襯 e ( の襯可 e ) RETURNS e ー AS ] BEGIN s 阨襯夜 END CREATE FUNCTIONfunction_name ( の可催 s ) RETURNS e LANGUAGE I の夜れ夜 CREATE ー OR REPLACE 】 FUNCTIONfunction_name ( の襯 s ) RETURNS 盟 e AS m 夜 LANGUAGE 70 れ バ弖区三歹 ・ストアードファンクション名 ・ / ヾラメータ 戻り値の型 実行ステートメント ・ステートメントの言語 「 CREATE FUNCTION 」によりストアードファンクション ( ユーザ定義関数 ) を作成することができます。プロシージャと同じような機能を持ちますが、戻り 値があることが特徴です。戻り値は「 RETURN 」命令により返すことができます。 ・ Oracle Oracle でのストアードファンクションは、その処理内容を PL/SQL によって記 述することができます。パラメータを持たないファンクションは括弧を記述しま せん。パラメータを持っファンクションについては、括弧を記述しなければなり ません。この点に注意して下さい。 PL / SQL やパラメータについての詳細は、「第 5 部プロシージャ内で使用できる命令」 ( P453 ) にあります。 Oracle SQLServer DB2 MySQL PostgreSQL データ定義命令 aa-l 144

SQL SUM OVER 関数 1 2 3 4 5 6 関数 DB2 racl ANSI 標準 A 3 ウインドウを使った合計 SUM( e ) 0 、田 R い PARTITION BYp い・・・Ⅱ ORDER BY 0 [ , 0 …】 { ROWS 川 ? 偽ー ec ー RANGE 7 、佖れー ec } ) →数値 SUM( e ) 0 、 ( [ PARTITION BYp い… (I) →数値 引第 集計を行う式 ・・パーティッションを指定する任意の式 ・・順位を計算する上で評価を行う任意の式 ・・カレント行から、どこまでを範囲とするかを指定する式 7 ・ 0 ? む S ー ,S 盟 ec ・ ・範囲指定 ? 、 ange—spec ・ 戻ソ」値 合計値 Oracle DB2 SQLServer SUM や AVG といった集計関数に「 OVER 」を加えることで、「ウインドウ」と いった概念を持ち込み、高度な集計を行うことが可能になります。 分 析 「 PARTITION BY 」と「 ORDER BY 」は RANK 関数などの分析関数と同じで 数 す。これらを単純に使用すると、次のようになります。 OVER を使用してみます。 SELECT 年齢 , SUM ( 年齢 ) OVER (ORDER BY 年齢 ) FROM 従業員 ORDER BY 年齢 年齢 SUM( 年齢 ) OVER(ORDER BY 年齢 ) 20 39 60 ( 以下略 ) SUM は集計関数ですが、 OVER を付け、「分析関数」として利用すると、この ようになります。上記の例では、年齢列を小さい順にソートし、それを累計して いく様子がわかります。今現在の位置より上にある行の合計を計算した、と言い 換えることもできます。 PARTITIONBY を併用し、グループごとに累計を計算させることもできます。 4.6 436