Plsql

PL/SQL (Procedural Language / Structured Query Language)

PL/SQL ➢

Procedural Language Extensions to SQL ➢

SQLへの手続き型言語拡張

➢

標準的なRDB言語にはないプログラミング構造を提供 ➢

➢

➢

IFとかFORとか使える

EXCEPTION（例外）もサポート

Oracle Databaseで使用できる

特徴 ➢

制御文やカーソルによる行操作など複雑な処理が可能

➢

ファンクション、プロシージャによるSQLロジックの再利用性 ➢

➢

パッケージによるカプセル化

　DB上にコンパイルされ格納される ➢

➢

パフォーマンス向上 ➢

複数文のブロック全体をOracleに一度に送信できる

➢

コンパイル済みプロシージャをメモリにキャッシュ

クライアント言語を意識しない ➢

DBを利用するサービスに対してビジネスロジックを一元化

早速プロンプトで実行してみる ➢

OracleクライアントのSQL*Plusで接続 SET SERVEROUTPUT ON BEGIN DBMS_OUTPUT.PUT_LINE('Hello World!'); END; /

「Hello World!」と表示される

➢

実際のプロンプトでは SQL> と出ている（ここでは省略）

➢

最後のバックスラッシュで実行

➢

SET SERVEROUTPUT ON にて標準出力ON

ファイルから実行する ➢

先ほどのPL/SQLをhello.sqlとして保存 @hello.sql 「SYSDATE is 08-09-29」と表示される

➢

@ファイル名で実行できる

PL/SQLの基本の基本

変数宣言・定数宣言 ➢

変数宣言 DECLARE foo VARCHAR2(10);

➢

変数宣言 + 値代入 DECLARE foo VARCHAR2(10) := 'bar';

➢

定数宣言 DECLARE foo CONSTANT CHAR(3) := 'bar';

変数型 ➢

型を指定する DECLARE foo VARCHAR2(10);

➢

テーブルの型に合わせる DECLARE foo foo%TYPE;

➢

テーブルのレコードに合わせる DECLARE foo_row foo%ROWTYPE;

PL/SQLの基本的な書き方

ブロック（無名ブロック） ➢

➢

DECLARE（宣言部） ➢

変数・定数やカーソルの宣言

➢

省略可能

BEGIN（実行部） ➢

➢

➢

処理を記述

EXCEPTION（例外部） ➢

処理で発生した例外の処理

➢

省略可能

END; ➢

終端文字

ブロック（無名ブロック）の例 ➢

%ROWTYPE でテーブルの一行の型定義ができる DECLARE CREATE TABLE bookmark ( bookmark_row bookmark%ROWTYPE; url VARCHAR2 (255), BEGIN name VARCHAR2 (100) SELECT url, name ); INTO bookmark_row FROM bookmark; DBMS_OUTPUT.PUT_LINE(bookmark_row.url); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('データが見つかりません。'); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('複数レコードを取得しました。'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('その他の例外です。'); END; /

ストアド・プロシージャ ストアド・ファンクション

プロシージャ・ファンクション ➢

名前付きのブロックで呼び出す（コール）ことができる

➢

プロシージャ

➢

➢

引数による IN / OUT

➢

RETURN による戻り値はなし

ファンクション ➢

RETURN で単一の値を戻す

➢

引数による IN / OUT を使用することは推奨されない ➢

ファンクションの目的は、引数をとらず、1つの値を戻すこと

プロシージャを作成 ➢

引数を渡すことができる ➢

IN は入力

➢

OUT は出力

➢

戻り値はなし

CREATE OR REPLACE PROCEDURE greet( message IN VARCHAR2, greeting OUT VARCHAR2 ) AS BEGIN DBMS_OUTPUT.PUT_LINE(message); END greet; /

プロシージャをコール ➢

名前付きで呼び出すことができる ➢

OUTモードの引数に値が代入される

DECLARE greeting VARCHAR2(50); BEGIN greet('Hello', greeting); DBMS_OUTPUT.PUT_LINE(greeting); END; /

「Hello」と表示される

例外処理

例外処理 ➢

例外の種類によって処理を振り分けることができる

➢

未処理例外は上位ブロックに通知 ➢

➢

未処理例外で処理が中断した場合は暗黙にロールバック

例外の種類 ➢

EXCEPTION宣言を行ったユーザー定義例外

➢

NO_DATA_FOUNDなどのPL/SQL事前定義例外

➢

ORA-00900やORA-02015などのSQLエラー

➢

RAISE_APPLICATION_ERROR()によるアプリケーション例外 ➢

ユーザー定義のエラー番号とメッセージを発生させる

例外処理の例 ➢

EXCEPTIONブロックでWHEN-THENによって振り分ける DECLARE bookmark_row bookmark%ROWTYPE; BEGIN SELECT url, name INTO bookmark_row FROM bookmark; DBMS_OUTPUT.PUT_LINE(bookmark_row.url); EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('データが見つかりません。'); WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE('複数レコードを取得しました。'); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('その他の例外です。'); END; /

PL/SQLの文法

条件制御 ➢

IF-THEN、IF-THEN-ELSE、IF-THEN-ELSIF DECLARE name VARCHAR2(50); BEGIN name := 'cocoiti'; IF name = 'cloned' THEN DBMS_OUTPUT.PUT_LINE('clonedです'); ELSIF name = 'cocoiti' THEN DBMS_OUTPUT.PUT_LINE('cocoitiです'); ELSE DBMS_OUTPUT.PUT_LINE('誰やねん'); END IF; END; / 「cocoitiです」と表示される

ループ ➢

LOOPを使った単純なループ

➢

WHILEループ

➢

FORループ（数値またはカーソル） ➢

数値を使ったFORループの例

DECLARE loop_counter NUMBER(10); BEGIN FOR loop_counter IN 1 .. 10 LOOP DBMS_OUTPUT.PUT_LINE(loop_counter); END LOOP; END; / 「1」～「10」までの数字が表示される

カーソル ➢

レコードを1行ずつ取り出して処理を行うことができる DECLARE CURSOR bookmark_cur IS SELECT url, name FROM bookmark; bookmark_rec bookmark_cur%ROWTYPE; BEGIN FOR bookmark_rec IN bookmark_cur LOOP DBMS_OUTPUT.PUT_LINE(bookmark_rec.url); END LOOP; IF bookmark_cur%ISOPEN THEN CLOSE bookmark_cur; END IF; END; /

その他の機能

パッケージを利用した複雑な処理 ➢

DBMS_OUTPUT ➢

➢

UTL_FILE ➢

➢

BASE64などのエンコーディング変換

UTL_COMPRESS ➢

➢

ファイルの読み込みや書き込みが可能

UTL_ENCODE ➢

➢

メッセージ出力

データの圧縮

その他にも数多くのパッケージが利用できる

END;