関数・プロシージャのまとめ【PostgreSQL】

2021年8月22日未分類

【頻出】関数・プロシージャ操作のまとめ

関数・プロシージャ操作のうち、特に頻出のものをまとめました。

 

1.変数の宣言

variable1   character(10);             --文字
variable2   character varying(10);     --文字
variable3   integer;                   --数値
variable4   numeric;                   --数値
variable5   boolean;                   --true/false
variable6   RECORD;                    --レコード
variable7   テーブル名%ROWTYPE;         --存在するテーブルの行
variable8   テーブル名.カラム名%TYPE;   --存在するテーブルの列
arr1        character varying[];       --配列
arr2        integer[];                 --配列
variable9   integer:=3;                --数値(初期値を設定)

ここで詳しく説明しています → 変数の宣言、変数宣言時に代入する、定数を宣言する方法

 

 

2.定数(constant)の宣言

variable1 constant character varying(10) := 'abcdefghij';
variable2 constant numeric := 1.08;  --定数にするには「constant」をつける

ここで詳しく説明しています → 変数の宣言、変数宣言時に代入する、定数を宣言する方法

 

 

3.1行コメント、複数行コメント

--1行コメント
/* 複数行のコメント1行目
   複数行のコメント2行目*/

ここで詳しく説明しています → コメントの書き方

 

 

4.比較演算子

if (a > b) and (a < c) then   --大なり小なり 
if (a >= b) or (a <= c) then --大なりイコール、小なりイコール
if (a = b) and (a != c) then  --イコール、ノットイコール

ここで詳しく説明しています → 比較演算子の紹介

 

 

5.IF文

if variable1 = 1 then
	--処理1;
elseif variable1 = 2 then
	--処理2;
else
	--処理3;
end if;
 
--否定の場合(!=をつける)
if variable1 != 1 then ・・・
 
--「select * into rec・・・」で取得した結果がない時の条件分岐例
if rec is null then ・・・

ここで詳しく説明しています → IF文の書き方(条件分岐)

 

 

6.SELECT結果を変数へ代入する①

select 列名 into 変数 from テーブル名; --「列名」の値を変数へ代入する
 
--例
select emp_name into nm from m_employee where ・・・;

ここで詳しく説明しています → SQLの結果を変数に格納する

 

 

7.SELECT結果を変数へ代入する②

select * into 変数 from テーブル名;  --SELECTで取得した行を変数へ入れる
 
--例
rec record;                         --変数「rec」をrecord型で宣言(declareへ書く)
select * into rec from m_employee;  --m_employeeをSELECTした結果をrecへ代入
if rec.code = '' then …             --recの列「code」の値で条件分岐

ここで詳しく説明しています → SQLの結果を変数に格納する

 

 

8.変数の中身を表示する(raise)

raise info '%' , 変数名;  --変数の中身を表示する
raise info '%' , var1;    --変数var1の中身を表示する例
--変数の中身は「データ出力」の2つ右にある「メッセージ」タブに表示されます

ここで詳しく説明しています → 変数の値を画面に出す(raise)

 

 

9.For文(単純なループ)

for i in 1..10 loop --1から10まで繰り返す
--処理内容
end loop;
 
for i in 1..10 loop
	IF i > 5 THEN
		EXIT;  --「EXIT」でループを抜け出す
	END IF;
end loop;

ここで詳しく説明しています → For文の書き方、ループから抜ける方法

 

 

10.For文(SELECT文の結果をループする方法)

FOR rec
	-- 列Aが'test'のレコードを取得しその結果をrecに入れる
	IN EXECUTE 'select * from XXX where 列A = ''test'' '
LOOP
	raise info '%', rec.code; --codeの値を表示
END LOOP;

ここで詳しく説明しています → SQL(SELECT文)の結果をループする方法

 

 

11.While文

while 条件文 loop
	--条件文がtrueの時にこの処理を繰り返す
	--処理
end loop;

ここで詳しく説明しています → While文の書き方、途中で抜ける方法

 

 

12.関数(FUNCTION)の作成・書き方 テンプレ用

CREATE OR REPLACE FUNCTION testfnc              --ファンクション名は適宜変更する
(arg1 character varying,arg2 numeric)           --引数をこの行で設定
RETURNS boolean                                 --返り値の型を定義
LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$  --この行は変更しない
declare
begin
	--処理を記載
	return true;   -- 返り値はboolean型なのでここはbooleanでリターン(とりあえず)
end;
$BODY$;

ここで詳しく説明しています

関数(FUNCTION)の作り方・書き方を解説する

CREATE FUNCTIONのテンプレート

 

 

13.関数の実行の仕方

--上のテンプレ関数作成を実行する(上の関数を作成後、コピー&貼り付けで実行できます)
select * From testfnc('a',1);
select testfnc('a',1);       --どちらでも同じ(結果も変わらない)

ここで詳しく説明しています → 関数(FUNCTION)を実行する・呼び出す方法

 

 

14.関数を削除する

--関数「testfnc」を削除する。引数も含んで書かなければならない点に注意
--DROP FUNCTION 関数名(引数);
DROP FUNCTION testfnc(arg1 character varying,arg2 numeric);

ここで詳しく説明しています → 関数(FUNCTION)を削除する方法

 

 

15.プロシージャ(PROCEDURE)の作成・書き方 テンプレ用

CREATE OR REPLACE PROCEDURE testproc(                   --プロシージャ名は要変更
INOUT num1 integer,INOUT num2 integer,IN num3 integer)  --引数(下に補足あり)
--引数:IN=引数のみ、INOUT=引数+返り値としても使用される ※OUTのみは不可
LANGUAGE 'plpgsql' AS $BODY$
BEGIN
	--処理を記載
END;
$BODY$;

ここで詳しく説明しています → CREATE PROCEDUREのテンプレート

 

 

16.プロシージャの実行の仕方

call testproc(1,2,3);  --プロシージャはcallを使う(selectは不可)

ここで詳しく説明しています → プロシージャ(PROCEDURE)を実行する・呼び出す方法

 

 

17.EXCEPTION

BEGIN
	--(略)
	EXCEPTION  --BEGINとENDの間に書く
	WHEN OTHERS THEN
	--エラーになった時の処理をここに書く
END;

ここで詳しく説明しています → エラーを取得する(EXCEPTION)