Oracleのデータベースを使う現場で、PL/SQLという言語でパッチを作ることになり
「PL/SQLって何？」と思ったので、PL/SQLの特徴や書き方についてまとめようと思います！
この記事でわかること
PL/SQLとは？
PL/SQLはデータベースに手続きする言語のSQLを拡張したOracleのプログラミング言語です。
※PL/SQLのはPLは、procedural Languageの略です。
どんなものか見てみましょう。
DECLARE -- 宣言部 /*変数やカーソルを取得したりする*/ BEGIN -- 実行部 /*UPDATEやINSERTを実行したり*/ EXCEPTION -- 例外処理部 /*Javaでいうtry-catchみたいなところ*/ END; /
プログラミング言語なので、変数定義やIF文やFor文なども使用することができます。
SQLを使うだけではなく、プログラミングで実装するような処理も一緒に、行うことができる言語というわけです。
これは便利ですよね。普通にJavaプログラミングで書くとdatebaseにアクセスするインスタンス作って、SQL文やパラメータのデータをインスタンスに渡して〜みたいなことを実装しないといけないですもんね。
PL/SQLってどんな特徴なの？
PL/SQLの特徴は、SQL文を含めたプログラムを一括でDatabaseに送信することができます。
例えばPL/SQLを使わない場合で、SQLでデータを複数件、SELECTして別のテーブルに取得したデータをINSERTする場合
JavaでやるとJavaでデータベースにコネクションとSQLを渡して問い合わせ
Javaで問い合わせ結果をパラメータに取得件数の数だけループして、INSERT文をデータベースに投げるみたいなことをコーディングすると思います。
簡単にいうとPL/SQLを使わない場合、プログラムからデータベースに対して複数回呼び出しを行い
データベースはSQL文を1行ずつ行います。
アプリケーションとデータベースの間に通信が負担になりパフォーマンスが悪くなります。
PL/SQLなら、SELECTして件数文ループしてINSERTまでの処理を一括でDatabaseに送信して、できるので
データベースに送信する回数の観点から性能が向上するメリットがあります。
PL/SQLの書き方
PL/SQLは、ブロック構成で記載します。ブロックは３つの構成の中で書くことができます。
宣言部、実行部、例外処理部の３つです。
宣言部と例外処理部は省略することが可能です。
実装の最後には"END;"を記載します。
END;
構成するブロックを１つずつ解説していきます。
宣言部(DECLARE)
宣言部は 、DECLARE と宣言したブロックで記載します。
変数やカーソル、ユーザー定義例外の宣言をここでは行います。
DECLAREは英語で”宣言する”です。 そのままですね。
カーソルや変数を実際に宣言した例を下に書きます。
DECLARE str VARCHAR(10); //変数strをVARCHARで宣言 CURSOR cur IS //カーソルの宣言 SELECT userid ,crateday FROM worktable BEGIN EXCEPTION END; /
実行部(BEGIN)
実行部は、SQL文によるデータの操作やFor文やIF文などの、実際に行う処理を記述するブロックです。」
宣言部で宣言したカーソルを別テーブルにINSERTする例を書きます。
DECLARE str VARCHAR(10); CURSOR cur IS SELECT userid ,crateday FROM worktable BEGIN for REC IN cur loop //宣言部で宣言したカーソルの数だけINSERTを繰り返す INSERT INTO worktable2 userid ,crateday VALUES ( REC.userid REC.crateday ) ; end loop; EXCEPTION END; /
例外処理部
PL/SQL は、 BEGIN と END 間でもし発生した例外は、EXCEPTIONブロックで定義した例外処理を行います。
JavaでいうところのTry-catch文のようなものですね。
DECLARE str VARCHAR(10); CURSOR cur IS SELECT userid ,crateday FROM worktable BEGIN for REC IN cur loop INSERT INTO worktable2 userid ,crateday VALUES ( REC.userid REC.crateday ) ; end loop; EXCEPTION WHEN OTHERS THEN ROLLBACK; END; /
