１．ＰＬ／ＳＱＬのブロック構造
ＰＬ／ＳＱＬは，ブロック構造をしており,宣言部，実行部，例外処理部から，構成される。
DECLARE
BEGIN
EXCEPTION
END; |
例）
/* PL/SQL Program */
DECLARE
num_avg NUMBER;
BEGIN
SELECT AVG(SAL) INTO num_avg FROM EMP WHERE JOB = 'SALESMAN';
INSERT INTO TEMP VALUES (num_avg,NULL,'AVG(SAL)');
COMMIT;
END;
/
・コメントは,/* */で囲う。
・文は，セミコロン(;)で終了する。
・最後の/は，ファイルの終了を意味する。（絶対必要）
| ひとくちアドバイス
・宣言部の変数のデータ型は,%TYPE 属性を使用する。 |
２．変数への代入
変数への代入は,直接代入と,SELECT文による代入がある。
・変数への代入
| <変数名> := <式>; |
右辺には,リテラル,変数，定数，SQL関数，式を置く。
代入には,データ型が同じか，互換性のある必要がある。
・SELECT文による代入
| SELECT EMPNO,ENAME,SAL INTO <変数名1>,<変数名2>,<変数名3> FROM EMP; |
SELECT文で取り出した値を，INTO 句に続く変数に格納する。
問い合わせ列と変数は,順番に関連付けられている。
３．分岐制御
IF文で分岐制御を行う。
条件には，比較演算子と，論理演算子（AND OR NOT)が使用できる。
| IF <条件１> THEN <処理１>; ELSEIF <条件２> THEN <処理２>; ELSE <処理３>; END IF; |
/* If then else */
DECLARE
num_avg EMP.SAL%TYPE;
up_rete1 CONSTANT NUMBER := 1.1;
up_rete2 CONSTANT NUMBER := 1.2;
BEGIN
SELECT AVG(SAL) INTO num_avg FROM EMP WHERE JOB = 'SALESMAN';
IF num_avg < 1750 THEN
UPDATE EMP SET SAL = SAL * up_rete2 WHERE JOB = 'SALESMAN';
ELSEIF num_avg < 2500 THEN
UPDATE EMP SET SAL = SAL * up_rete1 WHERE JOB = 'SALESMAN';
ELSE
NULL;
END IF;
COMMIT;
END;
/
４．反復制御
LOOP文を用いる。
| LOOP IF <条件> THEN EXIT; END IF; <処理>; END LOOP; |
/* Loop & Exit */
DECLARE
cnt NUMBER := 1;
ans NUMBER := 0;
BEGIN
LOOP
EXIT WHEN cnt > 10;
ans := ans + cnt;
cnt := cnt + 1;S
END LOOP;
INSERT INTO TEMP VALUES (ans,NULL,'Loop SUM 1..10');
COMMIT;
END;
/
| ひとくちアドバイス
反復制御のFOR文は，カーソル使用時にOPEN文やFETCH文を明示しなくても使えるので,逆に,判り難くなるので，基本的には，使わない。 |
５．明示カーソル
宣言文の構造
CURSOR <カーソル名> IS <SELECT文>;
<行名> <カーソル名>%ROWTYPE;
OPEN文の構造
OPEN <カーソル名>;
FETCH文の構文
FETCH <カーソル名> INTO <行名>;
CLOSE文の構文
CLOSE <カーソル名>
/* Using cursor */
DECLARE
CURSOR selline IS SELECT ENAME,SAL+NVL(COMM,0) SALS
FROM EMP ORDER BY SAL+NVL(COMM,0) DESC;
selrec selline%ROWTYPE;
BEGIN
OPEN selline;
LOOP
FETCH selline INTO selrec;
EXIT WHEN selline%NOTFOUND;
INSERT INTO TEMP VALUES (selrec.SALS,NULL,selrec.ENAME');
COMMIT;
END LOOP;
CLOSE selline;
END;
/
明示カーソルに対応したSQL文実行結果を得る事が出来る。
| 属性 | 内容 |
| <カーソル>%NOTFOUND | FETCHで取り出し行無しの時に TRUE |
| <カーソル>%FOUND | FETCHで取り出し行ありの時に TRUE |
| <カーソル>%ROWCOUNT | 取り出し行の類計 |
| <カーソル>%ISOPEN | カーソルオープン中の時に TRUE |
６．例外処理
例外とは,『PL/SQLでエラー条件を呼ぶ呼び方』の事であり，内部定義例外とユーザ定義例外に分類できる。
内部定義例外一覧
| 内部例外名 | エラーコード | 内容 |
| CURSOR_ALREADY_OPEN | －６５１１ | すでにOPENされているカーソルを再OPENしようとした |
| DUP_VAL_ON_INDEX | －１ | UNIQUE検索列への重複(INSERT,UPDATE) |
| INVALID_CURSOR | －１００１ | PL/SQL呼び出しで無効なカーソルを指定 |
| INVALID_NUMBER | －１７２２ | 文字列から数字への変換の失敗 |
| LOGIN_DENIED | －１０１７ | 無効なユーザー名，パスワード |
| NO_DATA_FOUND | ＋１００ | SELECT文が行を返さなかった (*注) |
| NOT_LOGGED_ON | －１０１２ | ORACLEにログオンしていない。 |
| PROGRAM_ERROR | －６５０１ | PL/SQL内部に問題発生 |
| STORAGE_ERROR | －６５００ | PL/SQLのメモリオーバー，メモリ破壊 |
| TIMEOUT_ON_RESOURCE | －５１ | ORACLEが資源待ちの間にタイムアウト発生 |
| TOO_MANY_ROWS | －１４２２ | SELECT文が複数行を戻した |
| TRANSACTION_BACKED_OUT | －６１ | リモートトランザクションがロールバックされた |
| VALUE_ERROR | －６５０２ | 演算，数字,文字，変換制約条件などにエラー |
| ZERO_DIVIDE | －１４７６ | 数値をゼロで除算 |
(*注)
| ひとくちアドバイス
NO_DATA_FOUND は，ユニークキー項目に対する,SELECT失敗時のみであり，カーソル定義で複数行検索時には,この例外は発行されない。 |
/* PL/SQL Exception */
DECLARE
up_rate CONSTANT NUMBER := 1.1;
up_name EMP.ENAME%TYPE;
up_sal EMP.SAL%TYPE;
sql_code NUMBER;
sql_errm VERCHAR2(55);
BEGIN
up_name := '&name';
SELECT SAL INTO up_sal FROM EMP WHERE ENAME = up_name;
IF up_sal >= 2000 THEN
NSERT INTO TEMP VALUES (NULL,NULL,'out of range SAL');
ELSE
UPDATE EMP SET SAL = SAL * up_rate WHERE ENAME = up_name;
INSERT INTO TEMP VALUES (NULL,NULL,'update complete');
END IF;
COMMIT;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO TEMP VALUES (NULL,NULL,'employee not found');
COMMIT;
WHEN OTHERS THEN
sql_code := SQLCODE;
sql_errm := SUBSTR(SQLERRM,1,55);
ROLLBACK;
INSERT INTO TEMP VALUES (sql_code,NULL,sql_errm);
COMMIT;
END;
/
７．プロシージャの作成
例１）empテーブルから，DEPTNOを指定して,SALの合計を求め,結果を DUMMY へ出力する。
/* insert_emp1.sql Test1 (user TEST9) 1998.11.05 K.Hasegawa */
CREATE OR REPLACE PROCEDURE insert_emp1
( emp_deptno IN EMP.DEPTNO%TYPE )
IS
/* num_sum EMP.SAL%TYPE; */
num_sum NUMBER;
BEGIN
SELECT SUM( SAL ) INTO num_sum FROM EMP WHERE DEPTNO = emp_deptno;
INSERT INTO DUMMY VALUES ( num_sum );
COMMIT;
END insert_emp1;
/
/* insert_emp1run.sql Test1 (user TEST9) 1998.11.05 K.Hasegawa */
set verify off
ACCEPT no PROMPT 'employee number : '
execute insert_emp1( &no )
select * from DUMMY
/
set verify on
例２）empテーブルから，SALが指定以上の場合に,SALの合計を求め,結果を DUMMY へ出力する。
CREATE OR REPLACE PROCEDURE insert_emp2
( emp_sal IN EMP.SAL%TYPE )
IS
CURSOR selline IS SELECT SAL FROM EMP WHERE SAL >= emp_sal;
selrec selline%ROWTYPE;
num_sum NUMBER;
BEGIN
OPEN selline;
num_sum := 0;
LOOP
FETCH selline INTO selrec;
EXIT WHEN selline%NOTFOUND;
num_sum := num_sum + selrec.SAL;
END LOOP;
INSERT INTO DUMMY VALUES ( num_sum );
COMMIT;
CLOSE selline;
END insert_emp2;
/
/* insert_emp2run.sql Test2 (user TEST9) 1998.11.05 K.Hasegawa */
set verify off
DELETE FROM DUMMY;
ACCEPT sal PROMPT 'minimam salary : '
EXECUTE insert_emp1( &sal )
SELECT * FROM DUMMY
/
set verify on
８．データベーストリガー
データベーストリガーは,特定の表に関連付けられたユーザー定義のPL/SQLロジックです。
以下に,標準パッケージである，DBMS_ALERTパッケージを用いた,アラートプログラムの使用方法に付いて,記述します。
| 前準備
初めに１度だけ,SYS ユーザーにて，CATPROC.sql を実行します。 %cd $ORACLE_HOME/rdbms/admin %sqlplus sys/change_on_install @CATPROC /* SYS でスクリプトを実行する。 */ ユーザーに，権限を与えます。 %sqlplus system/manager SQL>GRANT CREATE TRIGGER TO ユーザー; /* トリガ作成の権限を与えます。 */ SQL>GRANT EXECUTE ANY PROCEDURE TO ユーザー; /* PROCEDUREの実行権限を与えます。 */ |
例えば,emp表に,delete/insert/update が行われたときに,社員番号(empno)と変更前後の氏名（ename)を，emp_alert という名前のアラートに通知するトリガー(cre_tri.sql)は，
| drop trigger emp_trig;
CREATE TRIGGER emp_trig |
トリガの登録は,
%sqlplus scott/tiger @cre_trig
を実行します。
トリガを削除する場合は,
SQL> DROP TRIGGER <名前>
です。
トリガの作動／停止は,
SQL> ALTER TRIGGER <名前> ENABLE;
SQL> ALTER TRIGGER <名前> DISABLE;
を実行します。
これを実行する PRO*Cのプログラムを,以下に示します。
アラート（emp_alert)を登録後,そのアラートが，発生するのを待ち,（最大６０秒）アラートが発生したら,時刻と受け取ったメッセージの表示をし，また，アラートを待ちます。
６０秒間，アラートが発生しないと，プログラムは,終了します。
| <関連部分のみ抜粋>
EXEC SQL BEGIN DECLARE SECTION; main() EXEC SQL EXECUTE EXEC SQL WHENEVER SQLERROR continue; |
なお，Pro*Cでコンパイルするときは,sqlcheck=semantics の設定で行う必要があります。