ここからはじめよう Oracle PL/SQL入門

ここからはじめよう Oracle PL/SQL入門日本オラクル株式会社 オラクルダイレクト �� 宇多津 真彦

されます。OracleとJavaは、Oracle Corporation �Copyright 2011, Oracle. All rights reserved.2

Agenda ザクション制御Copyright 2011, Oracle. All rights reserved.3

PL/SQLとは �オラクル社が提供する言語です �語ですが、実際の業務処理＇ �処理が必要な場合があります Oracle �きますCopyright 2011, Oracle. All rights reserved.4

PL/SQLを使うメリット �き型の処理を記述可能 ��加工 ＇集合演算とは異なる処理（ ��務ロジックを組み込むことができる ��ッチ処理を記述（ Oracle 述できる 持つ Oracle �無い＇ 高い移植性（Copyright 2011, Oracle. All rights reserved.5

PL/SQLプログラムとI/OOracle Databaseに対する操作 eDBMS OUTPUTパッケージUTL �ルUTL ��イルOracleデータファイル※ DML INSERT / UPDATE / DELETE 等Copyright 2011, Oracle. All rights reserved.6

Agenda ザクション制御Copyright 2011, Oracle. All rights reserved.7

HELLO WORLD プログラム DECLARE ＇宣言部（str VARCHAR2(30);BEGIN ＇処理部（str : 'HELLO WORLD';DBMS OUTPUT.PUT グ言語のライブラリ相当（ブロック プログラムのひとかたまりBEGIN END; で囲むCopyright 2011, Oracle. All rights reserved.8

HELLO WORLD プログラム SQL*Plus上に文字列を出力するDECLAREstr VARCHAR2(30);BEGINstr : 'HELLO WORLD';% sqlplus scott/tigerファイル内容をバッファにSQL @put line読み込む8 ��ージャが正常に完了しました。DBMS OUTPUT.PUT LINE(str);END;期待通りに表示されないSQL set serveroutput onSQL*Plusの設定SQL @put line8 /HELLO ました。SQL ファイル： put line.sqlCopyright 2011, Oracle. All rights reserved.9

SQL文を実行する＇DML： INSERT/UPDATE/DELETE など（ T INTO emp (empno,ename)VALUES(1000,'Ichiro');END;% sqlplus scott/tigerSQL @insert emp12 ��た。SQL SELECT empno,ename FROM emp2WHERE empno 1000;EMPNO ENAME---------- ---------1000 IchiroSQL ファイル： insert emp.sqlCopyright 2011, Oracle. All rights reserved.10

�ある失敗 ＇その１（ テーブル emp の件数を取得したいDECLAREBEGINSELECT count(*)FROM emp;END;ファイル： select cnt 01.sql% sqlplus scott/tigerSQL @select cnt 016 /SELECT ORA-06550: 行3、列3:PLS-00428: INTO句はこのSELECT文に入ります。 に格納しますDECLAREcnt NUMBER(4);BEGINSELECT count(*) INTO cntFROM emp;DBMS OUTPUT.PUT LINE(cnt);END;ファイル： select cnt 02.sql% sqlplus scott/tigerSQL set serveroutput onSQL @select cnt 028 /12 完了しました。SQL Copyright 2011, Oracle. All rights reserved.11

�ある失敗＇その２（ テーブル emp (複数行存在) str VARCHAR2(10);CURSOR c1 ISBEGINSELECT ename FROM emp;SELECT ename INTO str FROM emp;DBMS OUTPUT.PUT LINE(str);ファイル： select ename01.sqlEND;% sqlplus scott/tigerSQL @select col a017/str � OPENOPEN c1;LOOPFETCH c1 INTO str; 一件取り出しDECLAREEXIT WHEN C1%NOTFOUND; データが無くなればループを脱出*DBMS OUTPUT.PUT :END LOOP;ORA-01422: �の行を戻しましたCLOSE c1; カーソルを CLOSEEND;ORA-06512: 行4Copyright 2011, Oracle. All rights reserved.ファイル： select ename02.sql12

レコード単位での処理 �場合、レコードを利用DECLARECURSOR c1 ISSELECT empno,ename FROM emp;TYPE emp rec type IS RECORD (レコードの定義empno NUMBER(4),ename VARCHAR2(10));emp rec emp rec type; ド変数を定義OPEN c1;LOOPFETCH c1 INTO emp rec;EXIT WHEN C1%NOTFOUND;DBMS OUTPUT.PUT(TO CHAR(emp rec.empno));DBMS OUTPUT.PUT LINE(' ' emp rec.ename);END LOOP;CLOSE c1;END;Copyright 2011, Oracle. All rights �」とよぶ レコード変数 . フィールド名 でアクセス可能13

効果的な変数定義 ＇％TYPE（ %TYPEをつかう �修正しなくともよいDECLARECURSOR c1 ISSELECT empno,ename FROM emp;TYPE emp rec type IS RECORD (empno NUMBER(4),ename VARCHAR2(10));emp rec emp rec type;BEGINOPEN c1;LOOPFETCH c1 INTO emp rec;EXIT WHEN C1%NOTFOUND;DECLARECURSOR c1 ISSELECT empno,ename FROM emp;TYPE emp rec type IS RECORD (empno emp.empno%TYPE,emp テーブルのename emp.ename%TYPE);empno カラムemp rec emp rec type;と同じ型定義BEGINOPEN c1;LOOPFETCH c1 INTO emp rec;EXIT WHEN C1%NOTFOUND;DBMS OUTPUT.PUT(TO CHAR(emp rec.empno));DBMS OUTPUT.PUT LINE(' ' emp rec.ename);END LOOP;CLOSE c1;END;DBMS OUTPUT.PUT(TO CHAR(emp rec.empno));DBMS OUTPUT.PUT LINE(' ' emp rec.ename);END LOOP;CLOSE c1;END;Copyright 2011, Oracle. All rights reserved.14

効果的な変数定義 ＇％ROWTYPE（ 簡単になりますDECLARECURSOR c1 ISSELECT empno,ename FROM emp;TYPE emp rec type IS RECORD (empno emp.empno%TYPE,ename emp.ename%TYPE);emp rec emp rec type;BEGINOPEN c1;LOOPFETCH c1 INTO emp rec;EXIT WHEN C1%NOTFOUND;CURSOR c1 ISSELECT empno,ename FROM emp;emp rec ド変数を定義CURSOR c1 ISSELECT empno,ename FROM emp;emp rec emp%ROWTYPE;DBMS OUTPUT.PUT(TO CHAR(emp rec.empno));DBMS OUTPUT.PUT LINE(' ' emp rec.ename);END LOOP;CLOSE c1;END;Copyright 2011, Oracle. All rights �変数を定義15

� �を指定できますDECLAREemp rec emp%ROWTYPE;BEGINemp rec.empno : 1000;emp rec.ename : 'Ichiro';INSERT INTO emp (empno, ename)VALUES(emp rec.empno, emp rec.ename);END;通常のINSERT構文DECLAREemp rec emp%ROWTYPE;BEGINemp rec.empno : 1000;emp rec.ename : 'Ichiro';INSERT INTO emp VALUES emp rec;END;INSERTの拡張構文DECLAREemp rec emp%ROWTYPE;BEGINemp rec.empno : 1000;emp rec.ename : 'Ichiro01';emp rec.deptno : 10;UPDATE empSET ename emp rec.ename,deptno emp rec.deptnoWHERE empno emp rec.empno;END;通常のUPDATE構文DECLAREemp rec emp%ROWTYPE;BEGINemp rec.empno : 1000;emp rec.ename : 'Ichiro01';emp rec.deptno : 10;UPDATE empSET ROW emp recWHERE empno emp rec.empno;END;UPDATEの拡張構文Copyright 2011, Oracle. All rights reserved.16

Agenda ザクション制御Copyright 2011, Oracle. All rights reserved.17

例外処理とは 処理3処理3EXCEPTION例外処理部WHEN OTHERS THENEXCEPTION例外処理されないWHEN OTHERS 処理部へ遷移例外処理END;Copyright 2011, Oracle. All rights reserved.18

ブロックの入れ子と例外処理 ��BEGIN処理１例外処理2EXCEPTIONWHEN OTHERS ��END;BEGIN例外処理2EXCEPTIONWHEN OTHERS �れないEXCEPTIONWHEN OTHERS THEN例外処理END;処理されるEXCEPTIONWHEN OTHERS THEN例外処理END;Copyright 2011, Oracle. All rights reserved.19

特定の例外を捕捉する �理をおこないますDECLAREBEGININSERT INTO emp (empno,ename)VALUES (1000,'Ichiro');BEGININSERT INTO emp (empno,ename)VALUES (1000,'Ichiro 2');EXCEPTIONWHEN DUP VAL ON INDEX THENUPDATE empSET ename 'Ichiro 2'WHERE empno 1000;WHEN OTHERS ONWHEN OTHERS THENDBMS OUTPUT.PUT LINE('Error');END;ファイル： ins upd emp01.sqlSQL @ins upd emp20 ��た。SQL SELECT empno,ename FROM emp2WHERE empno 1000;EMPNO ENAME---------- ---------1000 Ichiro 2SQL �して提供されていますCopyright 2011, Oracle. All rights reserved.20

捕捉した例外情報を表示 取得しますDECLARE-- emp表のempnoカラムはNUMBER(4)BEGINSQL @insert emp err0118/ORA-01438: �な値ですINSERT INTO emp (empno,ename) VALUES ��正常に完了しました。SQL EXCEPTIONWHEN OTHERS THENDBMS OUTPUT.PUT LINE( SQLERRM );DBMS OUTPUT.PUT LINE( SQLCODE );END;ファイル： insert emp err01.sqlCopyright 2011, Oracle. All rights reserved.21

ユーザ独自の例外定義 �ハンドリングできます RAISE APPLICATION LAREusr excp EXCEPTION;usr excp num NUMBER : -20001;usr excp str VARCHAR2(30) : 'User Exception';PRAGMA EXCEPTION INIT(usr excp, -20001);BEGINRAISE APPLICATION ERROR(usr excp num, エラー・コードを指定usr excp str);EXCEPTIONWHEN usr excp THENDBMS OUTPUT.PUT LINE( SQLERRM );DBMS OUTPUT.PUT LINE( SQLCODE );WHEN OTHERS THENDBMS OUTPUT.PUT LINE('Error');END;ファイル： raise appl err01.sqlSQL @raise appl err0120 /ORA-20001: User QL �：-20000から-20999 Copyright 2011, Oracle. All rights reserved.22

参考（ユーザ定義例外のRAISE ませんDECLAREusr excp EXCEPTION;usr excp num NUMBER : -20001;usr excp str VARCHAR2(30) : 'User Exception';PRAGMA EXCEPTION INIT(usr excp, -20001);BEGINRAISE usr excp;SQL @raise appl err0217 /ORA-20001: XCEPTIONWHEN usr excp THENDBMS OUTPUT.PUT LINE( SQLERRM );DBMS OUTPUT.PUT LINE( SQLCODE );WHEN OTHERS THENDBMS OUTPUT.PUT LINE('Error');END;ファイル： raise appl err02.sqlSQL ��ありますCopyright 2011, Oracle. All rights reserved.23

Agenda ザクション制御Copyright 2011, Oracle. All rights reserved.24

ストアド・プログラム ストアド・プロシージャ �前をつけ、Oracle Databaseに格納したものCREATE PROCEDURE proc ISBEGIN プロシージャ作成END;DECLAREBEGIN END; ストアド・ファンクション �する処理手続きに名前をつけ、Oracle Databaseに格納したものCREATE FUNCTION funcDECLAREBEGINIF cnt 20 THEN END IF;END;DECLAREBEGINIF func(cnt,20) THENEND IF;END;Copyright 2011, Oracle. All rights reserved.(p1 NUMBER, p2 NUMBER)RETURN BOOLEAN ISBEGINIF p1 p2 THENRETURN TRUE;END IF;RETURN FALSE;ファンクション作成END;25

�分け プロシージャ �プロシージャにて実装 実施 �できる ファンクション �換処理、値の抽出をおこなう T/UPDATE/DELETEを実施しない ��を示す値を返却することが多い �外あり（Copyright 2011, Oracle. All rights reserved.26

ファンクションをつくる �CREATE OR REPLACE FUNCTIONget sales tax(date in IN DATE)RETURN NUMBERIStax sales tax.tax%TYPE;BEGINSELECT tax INTO taxFROM sales taxWHERE FROM DATE date inAND TO DATE date in;RETURN tax;EXCEPTIONWHEN OTHERS THENRETURN NULL;END;例外発生時はNULLを戻すCREATE OR REPLACE FUNCTIONget quarter(date in IN DATE)RETURN NUMBERISmm str VARCHAR2(2);BEGINmm str : TO CHAR(date in,'MM');CASE mm strWHEN '01' THEN RETURN 3; WHEN '02'WHEN '03' THEN RETURN 4; WHEN '04'WHEN '05' THEN RETURN 4; WHEN '06'WHEN '07' THEN RETURN 1; WHEN '08'WHEN '09' THEN RETURN 2; WHEN '10'WHEN '11' THEN RETURN 2; WHEN '12'ELSERETURN NULL;END CASE;EXCEPTIONWHEN OTHERS THENRETURN �情報を取得Copyright 2011, Oracle. All rights 変換27

�ション ��ンスが悪くなることがありますSELECT FROMWHEREtbl1col1 get price(item id, sysdate-60) ;CREATE FUNCTION get price (item id in IN NUMBER, dt in IN DATE)RETURN NUMBERISret num NUMBER;BEGINSELECT price INTO ret num FROM price listWHERE item id item id inAND from dt TRUNC(dt in) AND to dt RETURN ret num;END get price;TRUNC(dt in) 利用するCopyright 2011, Oracle. All rights reserved.28

��ッシュ ＇11g R1 、Enterprise Edition（ ��きます �を組にして結果をキャッシュ �ると古いものから破棄 �変更されるとキャッシュは破棄CREATE OR REPLACE FUNCTION get price(item id in IN NUMBER, dt in IN DATE)RETURN NUMBERRESULT CACHERELIES ON ( price list )ISret num NUMBER;BEGINSELECT price INTO ret num FROM price listWHERE item id item id inAND from dt TRUNC(dt in)AND to dt 11g R2からはRELIES ON記述は不要TRUNC(dt in) ;RETURN ret num;END get price;Copyright 2011, Oracle. All rights reserved.29

Agenda ザクション制御Copyright 2011, Oracle. All rights reserved.30

� パッケージとは ��ーマ・オブジェクト パッケージの利点 モジュール性 �を一元管理 情報の隠蔽 � オブジェクトの永続性Copyright 2011, Oracle. All rights reserved.31

パッケージのモジュール性 �を一元管理できます パッケージ仕様部に記述します �（ E OR REPLACE PACKAGE 公開のサブプログラム仕様END xxx;パッケージ仕様部CREATE OR REPLACE PACKAGE BODY xxx ��ますIS パッケージ本体のみに記述します �ートEND変数も定義できますCopyright 2011, Oracle. All rights 体xxx;パッケージ本体32