● 2_04_36-SQLの拡張 PL/pgSQL
PL/pgSQLはSQLに制御構造が使えるようにした拡張言語です。条件式、ループ処理が可能になります。
バージョンが7.3辺りは下記のコマンドで登録したのですが、8からはデフォルトで使えるみたいです。
いつから、変わったんだろう？でもWindowsだけなのか？
createlang plpgsql kaden_hanbai |
調べたら、ちゃんと「template1」にplpgsqlが登録済みでした。
私、個別にデータベースに登録してるので、登録したはずないし。
下はLinuxサーバー、FedoraCore4です。こっちは登録されてない。うん？
もしPL/pgSQLを使う時に登録されてなかったら先のコマンドで登録して下さい。
これはコンソールで実行します。
下記はコードの説明です。まだ制御構造はありません。e_shouhin_t、商品情報テーブルにデータを入れるだけの関数です。これを雛型にして使って下さい。
コードの記載を説明するため、わざと1箇所ミスをしています。
| drop function e_shouhin_t_input(varchar
, varchar , varchar , varchar , int , varchar , varchar ); --
関数を削除するコマンド -- drop function 関数名( 引数 )が構文です、引数はデータ型となります。 -------------------------------------------ここからが関数の作成文です。 create function e_shouhin_t_input(varchar , varchar , varchar , varchar , int , varchar , varchar ) -- create function 関数名 ( 引数 )で作成します。 returns varchar as ' -- 必ず戻り値が必要です。ここの記載は必ず必要です。引数にあるデータ型なら、なんでもけっこうです。 declare -- 変数の宣言 -- 引数にそれぞれ変数を割り当てて、別名を設定します。 COAD_1 alias for $1; -- 1番目の引数$1をCOAD_1という別名を付ける COAD_2 alias for $2; -- 2番目の引数$1をCOAD_2という別名を付ける COAD_3 alias for $3; -- 3番目の引数$1をCOAD_3という別名を付ける COAD_4 alias for $4; -- 4番目の引数$1をCOAD_4という別名を付ける COAD_5 alias for $5; -- 5番目の引数$1をCOAD_5という別名を付ける COAD_6 alias for $6; -- 6番目の引数$1をCOAD_6という別名を付ける COAD_7 alias for $7; -- 7番目の引数$1をCOAD_7という別名を付ける begin -- 処理文の始めを宣言 insert into e_shouhin_t( e_shouhin_id , e_shouhin_name , e_shouhin_color , e_shouhin_size , e_shouhin_kakaku , e_shouhin_group , e_shouhin_date ) values( COAD_1 , COAD_2 , COAD_3 , COAD_4 , COAD_5 , COAD_6 , COAD_7 ); return COAD_2; -- COAD_2を戻り値として返す end; -- 処理文の終了を宣言 ' language 'plpgsql'; -- 使用している言語を宣言 ------------------------------------------- |
上のコードは説明用に全角スペースが入っているので下のコードを使って下さい。
create function e_shouhin_t_input(varchar , varchar , varchar , varchar , int , varchar , varchar )returns varchar as ' declare -- 変数の宣言 COAD_1 alias for $1; -- 1番目の引数$1をCOAD_1という別名を付ける COAD_2 alias for $2; -- 2番目の引数$1をCOAD_2という別名を付ける COAD_3 alias for $3; -- 3番目の引数$1をCOAD_3という別名を付ける COAD_4 alias for $4; -- 4番目の引数$1をCOAD_4という別名を付ける COAD_5 alias for $5; -- 5番目の引数$1をCOAD_5という別名を付ける COAD_6 alias for $6; -- 6番目の引数$1をCOAD_6という別名を付ける COAD_7 alias for $7; -- 7番目の引数$1をCOAD_7という別名を付ける begin insert into e_shouhin_t( e_shouhin_id , e_shouhin_name , e_shouhin_color , e_shouhin_size , e_shouhin_kakaku , e_shouhin_group , e_shouhin_date ) values( COAD_1 , COAD_2 , COAD_3 , COAD_4 , COAD_5 , COAD_6 , COAD_7 ); return COAD_2; -- COAD_2を戻り値として返す end; -- 処理文の終了を宣言 ' language 'plpgsql'; -- 使用している言語を宣言 |
下記のように実行して、メッセージを確認します。
次は下記で関数を実行します。
select e_shouhin_t_input('0200' , 'a33' , 're' , 'LL' , '10000' , 'sh' , '2006-10-11 10:12:00' ); -- この文によって関数を実行します。 |
以下のエラーが出たはずです、column "e_shouhin_date"はtimestampの型ですよ、なのにtimestamp（可変長文字列が）来てます。と、文句を言ってます。
SQL実行中に以下のエラーが発生しました。 |
文法は間違っていませんが、テーブルと仕様が合っていません。
今度は修整します。
|
drop function e_shouhin_t_input(varchar , varchar , varchar , varchar , int ,
varchar , timestamp ); -- 関数を削除するコマンド -- drop function 関数名( 引数 )が構文です、引数はデータ型となります。 -------------------------------------------ここからが関数の作成文です。 create function e_shouhin_t_input(varchar , varchar , varchar , varchar , int , varchar , timestamp) -- create function 関数名 ( 引数 )で作成します。 returns varchar as ' -- 必ず戻り値が必要です。ここの記載は必ず必要です。引数にあるデータ型なら、なんでもけっこうです。 declare -- 変数の宣言 -- 引数にそれぞれ変数を割り当てて、別名を設定します。 COAD_1 alias for $1; -- 1番目の引数$1をCOAD_1という別名を付ける COAD_2 alias for $2; -- 2番目の引数$1をCOAD_2という別名を付ける COAD_3 alias for $3; -- 3番目の引数$1をCOAD_3という別名を付ける COAD_4 alias for $4; -- 4番目の引数$1をCOAD_4という別名を付ける COAD_5 alias for $5; -- 5番目の引数$1をCOAD_5という別名を付ける COAD_6 alias for $6; -- 6番目の引数$1をCOAD_6という別名を付ける COAD_7 alias for $7; -- 7番目の引数$1をCOAD_7という別名を付ける begin -- 処理文の始めを宣言 insert into e_shouhin_t( e_shouhin_id , e_shouhin_name , e_shouhin_color , e_shouhin_size , e_shouhin_kakaku , e_shouhin_group , e_shouhin_date ) values( COAD_1 , COAD_2 , COAD_3 , COAD_4 , COAD_5 , COAD_6 , COAD_7 ); return COAD_2; -- COAD_2を戻り値として返す end; -- 処理文の終了を宣言 ' language 'plpgsql'; -- 使用している言語を宣言 ------------------------------------------- |
不要な関数を作ってしまったので下記で削除します。
drop function e_shouhin_t_input(varchar , varchar , varchar , varchar , int , varchar , varchar ); -- 関数を削除するコマンド |
「処理が正常に終了しました。」のメッセージが出ればOKです。
修整したコードを実行します。
create function e_shouhin_t_input(varchar , varchar , varchar , varchar , int , varchar , timestamp )returns varchar as ' declare -- 変数の宣言 COAD_1 alias for $1; -- 1番目の引数$1をCOAD_1という別名を付ける COAD_2 alias for $2; -- 2番目の引数$1をCOAD_2という別名を付ける COAD_3 alias for $3; -- 3番目の引数$1をCOAD_3という別名を付ける COAD_4 alias for $4; -- 4番目の引数$1をCOAD_4という別名を付ける COAD_5 alias for $5; -- 5番目の引数$1をCOAD_5という別名を付ける COAD_6 alias for $6; -- 6番目の引数$1をCOAD_6という別名を付ける COAD_7 alias for $7; -- 7番目の引数$1をCOAD_7という別名を付ける begin insert into e_shouhin_t( e_shouhin_id , e_shouhin_name , e_shouhin_color , e_shouhin_size , e_shouhin_kakaku , e_shouhin_group , e_shouhin_date ) values( COAD_1 , COAD_2 , COAD_3 , COAD_4 , COAD_5 , COAD_6 , COAD_7 ); return COAD_2; -- COAD_2を戻り値として返す end; -- 処理文の終了を宣言 ' language 'plpgsql'; -- 使用している言語を宣言 |
「処理が正常に終了しました。」が出たでしょうか。
もう一度下記を実行します。
select e_shouhin_t_input('0200' , 'a33' , 're' , 'LL' , '10000' , 'sh' , '2006-10-11 10:12:00' ); -- この文によって関数を実行します。 |
処理が成功すると下記の値が返ります。「return COAD_2; -- COAD_2を戻り値として返す」とありますよね。
商品コード、「0200」で検索すると。設定した引数の通りにデータが登録されています。
なんだ、これじゃあ、データ入力と変わらないではないか、・・・・ですか。
とにかく、手順通りに行きましょう。一番単純なもので型をイメージしてもらうのが目的です。
長くなりそうなので、条件式、ループはページを分けます。上のコードを流用していきます。
PL/pgSQLの短所・長所を書いておきます。
| PL/pgSQLの短所 | PL/pgSQLの長所 |
コードはコンパイルされてデータベースに格納されます。 |
PHPなどでSQLをループして処理が重たくなる可能性がある時にデータベース内部で処理を持たせることができる場合がある。 |
通常のPHP、SQLで処理が十分な時はPL/pgSQLを無理に使う必要はありません。
使わないで済むなら、そのほうがベターです。現状、将来のシステムの問題を解決できるなら・・・、という事だと思います。
戻る