PostgreSQLのPL/pgSQLのストアドプロシージャのサンプルです。
確認環境 ・PostgreSQL 11.2 ・Windows 10 |
目次
サンプル | 文字列を出力する |
select intoで項目を取得する | |
select intoで複数項目を取得する | |
select文でカーソル(cursor)とfetchとloopを使用する | |
returnで処理を終了する | |
NULLで処理を何もしない | |
確認 | プロシージャの存在確認を行う |
削除 | プロシージャを削除する |
前提
PostgreSQLをインストールしています。
PostgreSQLをインストールする手順(Windows)
文字列を出力する
文字列を出力します。
CREATE OR REPLACE PROCEDURE test1()
AS $$
DECLARE
str1 VARCHAR(20);
BEGIN
str1 := 'こんにちは';
RAISE INFO 'msg:%', STR1; --'msg:こんにちはと出力される
END;
$$
LANGUAGE plpgsql;
4行目は、変数を宣言しています。
6行目は、変数に値をセットしています。
7行目は、変数の値を出力します。コメントはハイフン2つ続けます。
10行目は、言語の指定で必要です。
プロシージャを実行する
上記で作成したプロシージャをpsqlで実行するサンプルです。
call プロシージャ名()で実行します。
call test1();
select intoで値を取得する
入力として数値を渡してsql intoで値を取得します。
CREATE OR REPLACE PROCEDURE test1(id1 int)
AS $$
DECLARE
str1 VARCHAR(20);
BEGIN
select name into str1 from SYAIN
where ID = id1;
RAISE INFO 'msg:%', STR1; --'msg:鈴木と出力される
END;
$$
LANGUAGE plpgsql;
1行目は、引数としてintの値を受け取ります。
6行目はselect intoで取得したnameを右の変数のstr1にセットしています。
7行目は、外部から受け取った数値で条件を指定します。
プロシージャを実行する
上記で作成したプロシージャをpsqlで実行するサンプルです。
引数として1を指定しています。
call test1(1);
select intoで複数項目を取得する
DECLARE
str1 VARCHAR(20);
str2 VARCHAR(20);
BEGIN
select name,romaji into str1,str2 from SYAIN
where ID = id1;
RAISE INFO 'msg:%,%', STR1,STR2; --'msg:鈴木 suzukiと出力される
END;
selectで取得する項目を複数にし、intoの後の変数も複数にすれば複数項目を取得できます。
select文でカーソル(cursor)とfetchとloopを使用する
CREATE OR REPLACE PROCEDURE test1()
AS $$
DECLARE
cur1 CURSOR FOR
select name from SYAIN;
syain_rec RECORD;
BEGIN
OPEN cur1;
LOOP
FETCH cur1 INTO syain_rec;
EXIT WHEN NOT FOUND;
RAISE INFO '%', syain_rec.name;
END LOOP;
CLOSE cur1;
END;
$$
LANGUAGE plpgsql;
プロシージャを実行する
上記で作成したプロシージャをpsqlで実行するサンプルです。
call test1();
returnで処理を終了する
CREATE OR REPLACE PROCEDURE test1()
AS $$
DECLARE
num1 int;
BEGIN
num1 := 1;
if num1 = 1 then
RAISE INFO '処理1'; --'出力される
return;
end if;
RAISE INFO '処理2';
END;
$$
LANGUAGE plpgsql;
9行目は、returnです。このプロシージャの処理が終了します。
実行した場合、8行目の「処理1」が出力されます。
11行目の「処理2」は出力されません。
NULLで処理を何もしない
CREATE OR REPLACE PROCEDURE test1()
AS $$
DECLARE
num1 int;
BEGIN
num1 := 1;
if num1 = 1 then
NULL;
end if;
RAISE INFO '処理2';--'出力される
END;
$$
LANGUAGE plpgsql;
NULLを記述すると何もしません。
NULLがない場合でもエラーにはなりません。
プロシージャの存在確認を行う
SELECT proname,proowner,pronargs,prosrc FROM pg_proc
WHERE proname = 'test1';
pg_procテーブルに情報があります。
https://www.postgresql.jp/document/9.0/html/catalog-pg-proc.html
プロシージャを削除する
DROP PROCEDURE test1();
DROP PROCEDURE test1(int);
プロシージャに引数がある場合は、引数のデータ型を指定します。
関連の記事