Slide 1

Transcription

1 Oracle Direct Seminar <Insert Picture Here> ここからはじめよう Oracle PL/SQL 入門 日本オラクル株式会社

2 Agenda PL/SQLとは プログラムを作成してみる 例外処理 ストアド プログラム パッケージ機能でライブラリをつくる トランザクション制御 無償技術サービス Oracle Direct Concierge SQL Server からの移行アセスメント MySQL からの移行相談 PostgreSQL からの移行相談 Access からの移行アセスメント Oracle Database バージョンアップ支援 Oracle Developer/2000 Web アップグレード相談 パフォーマンス クリニック Oracle Database 構成相談 Oracle Database 高可用性診断 システム連携アセスメント システムセキュリティ診断 簡易業務診断 メインフレーム資産活用 Copyright 2009, Oracle. All rights reserved. 2

3 PL/SQL とは PL/SQL は SQL の手続き型拡張機能としてオラクル社が提供する言語です SQL はデータの集合を操作するための言語ですが 実際の業務処理 (= ビジネス ロジック ) では手続き型の処理が必要な場合があります Oracle Database の内部で SQL と緊密な連携をおこなうことで効率的に業務処理を実行できます Copyright 2009, Oracle. All rights reserved. 3

4 PL/SQL を使うメリット SQL 言語と緊密に統合されており 手続き型の処理を記述可能 SQL で取得した データの塊 ( 集合 ) を PL/SQL により業務上の要望に応じて加工 ( 集合演算とは異なる処理 ) PL/SQL でストアド ファンクションを作成することで SQL 操作の中に複雑な業務ロジックを組み込むことができる 複数のトランザクションを統合し 一連の業務処理をまとめて記述可能 ( バッチ処理を記述 ) Oracle Database のデータ操作をコンパクトに記述できる 例外処理 カプセル化 データ隠ぺいおよびオブジェクト指向のデータ等 プログラミングに必要充分な機能を持つ Oracle Database 上で稼動するので プラットフォームによるプログラミングの差異が無い (= 高い移植性 ) Copyright 2009, Oracle. All rights reserved. 4

5 PL/SQL プログラムと I/O PL/SQL プログラムでの情報の流れ 端末 ( パラメータ指定 ) 端末 ( 文字列出力 ) ファイル ファイル テーブル テーブル 入力 出力 Copyright 2009, Oracle. All rights reserved. 5

6 PL/SQL プログラムと I/O PL/SQL プログラムでの情報の流れ Oracle Database に対する操作 (OS に対して直接操作しない ) DBMS_OUTPUT パッケージ 端末 ( パラメータ指定 ) 端末 ( 文字列出力 ) ファイル UTL_FILE パッケージ ファイル UTL_FILE パッケージ SQL SQL テーブル テーブル (SELECT) 入力 出力 (INSERT /UPDATE/DELETE) Copyright 2009, Oracle. All rights reserved. 6

7 Agenda PL/SQLとは プログラムを作成してみる 例外処理 ストアド プログラム パッケージ機能でライブラリをつくる トランザクション制御 無償技術サービス Oracle Direct Concierge SQL Server からの移行アセスメント MySQL からの移行相談 PostgreSQL からの移行相談 Access からの移行アセスメント Oracle Database バージョンアップ支援 Oracle Developer/2000 Web アップグレード相談 パフォーマンス クリニック Oracle Database 構成相談 Oracle Database 高可用性診断 システム連携アセスメント システムセキュリティ診断 簡易業務診断 メインフレーム資産活用 Copyright 2009, Oracle. All rights reserved. 7

8 HELLO WORLD プログラム SQL*Plus 上に文字列を出力する DECLARE str VARCHAR2(30); str := 'HELLO WORLD'; DBMS_OUTPUT.PUT_LINE(str); 変数宣言 : strは変数 VARCHAR2(30) は型変数への値の代入変数の内容を端末へ出力事前定義のストアド プロシージャの実行 ( 他プログラミング言語のライブラリ相当 ) ブロック = プログラムのひとかたまり ~ で囲む Copyright 2009, Oracle. All rights reserved. 8

9 HELLO WORLD プログラムを実行する SQL*Plus 上に文字列を出力する ファイル : put_line.sql DECLARE str VARCHAR2(30); str := 'HELLO WORLD'; DBMS_OUTPUT.PUT_LINE(str); % sqlplus scott/tiger 8 / ファイル内容をバッファに読み込むバッファの内容を実行する PL/SQL プロシージャが正常に完了しました SQL> SQL> set serveroutput on 8 / HELLO WORLD 期待通りに表示されない SQL*Plus の設定 PL/SQL プロシージャが正常に完了しました SQL> Copyright 2009, Oracle. All rights reserved. 9

10 SQL 文を実行する (DML: INSERT/UPDATE/DELETE など ) テーブル A の内容をそのままテーブル B に挿入 ( あらかじめテーブルは作成済み ) ファイル : insert_a.sql DECLARE INSERT INTO B ( SELECT * FROM A ); % sqlplus scott/tiger SQL> SELECT COUNT(*) FROM B; COUNT(*) / PL/SQL プロシージャが正常に完了しました SQL> SELECT COUNT(*) FROM B; COUNT(*) Copyright 2009, Oracle. All rights reserved. 10

11 SQL 文を実行する (SELECT: よくある失敗 1) テーブル emp の件数を取得したい ファイル : select_cnt01.sql DECLARE SELECT count(*)from emp; % sqlplus scott/tiger 6 / SELECT count(*) FROM emp; * 行 3 でエラーが発生しました : ORA-06550: 行 3 列 3: PLS-00428: INTO 句はこの SELECT 文に入ります PL/SQL において SELECT 文は先程のように SQL 文の内部で利用するか INTO 句を利用して一時的に変数に格納する ファイル : select_cnt02.sql DECLARE cnt NUMBER(4); SELECT count(*) INTO cnt FROM emp; DBMS_OUTPUT.PUT_LINE(cnt); % sqlplus scott/tiger SQL> set serveroutput on 8 / 2 件数 :2 件 PL/SQL プロシージャが正常に完了しました SQL> Copyright 2009, Oracle. All rights reserved. 11

12 SQL 文を実行する (SELECT: よくある失敗 2) テーブル emp ( 複数行存在 ) のカラム内容を取得したい ファイル : select_ename01.sql DECLARE str VARCHAR2(10); SELECT ename INTO str FROM emp; DBMS_OUTPUT.PUT_LINE(str); 実行するとエラー発生 % sqlplus scott/tiger 8 / DECLARE * 行 1 でエラーが発生しました : ORA-01422: 完全フェッチがリクエストよりも多くの行を戻しました ORA-06512: 行 4 ファイル : select_emane01.sql DECLARE CURSOR c1 IS SELECT ename FROM emp; str VARCHAR2(10); OPEN c1; LOOP FETCH c1 INTO str; EXIT WHEN C1%NOTFOUND; DBMS_OUTPUT.PUT_LINE(str); END LOOP; CLOSE c1; カーソルを OPEN カーソル定義 一件取り出し データが無くなればループを脱出 カーソルを CLOSE Copyright 2009, Oracle. All rights reserved. 12

13 レコード単位での処理 テーブルのデータを行毎に処理したい場合 レコードを利用 DECLARE CURSOR c1 IS SELECT empno,ename FROM emp; TYPE emp_rec_type IS RECORD ( empno NUMBER(4), ename VARCHAR2(10) ); emp_rec emp_rec_type; OPEN c1; LOOP FETCH c1 INTO emp_rec; EXIT WHEN C1%NOTFOUND; 定義したレコード型を元に変数定義 レコードの定義 TYPE emp_rec_type IS RECORD ( empno NUMBER(4), ename VARCHAR2(10) ); 個々のメンバーを フィールド と呼ぶ DBMS_OUTPUT.PUT(TO_CHAR(emp_rec.empno)); DBMS_OUTPUT.PUT_LINE(' ' emp_rec.ename); END LOOP; CLOSE c1; < レコード変数 >.< フィールド名 > で アクセス可能 Copyright 2009, Oracle. All rights reserved. 13

14 効果的な変数定義 (%TYPE) %TYPE をつかう ( テーブル定義が変更されてもソースは修正しなくともよい ) DECLARE CURSOR c1 IS SELECT empno,ename FROM emp; TYPE emp_rec_type IS RECORD ( empno NUMBER(4), ename VARCHAR2(10) ); emp_rec emp_rec_type; OPEN c1; LOOP FETCH c1 INTO emp_rec; EXIT WHEN C1%NOTFOUND; DECLARE CURSOR c1 IS SELECT empno,ename FROM emp; TYPE emp_rec_type IS RECORD ( empno emp.empno%type, ename emp.ename%type ); emp_rec emp_rec_type; OPEN c1; LOOP FETCH c1 INTO emp_rec; EXIT WHEN C1%NOTFOUND; emp テーブルの empno カラムと同じ型定義 DBMS_OUTPUT.PUT(TO_CHAR(emp_rec.empno)); DBMS_OUTPUT.PUT_LINE(' ' emp_rec.ename); END LOOP; CLOSE c1; DBMS_OUTPUT.PUT(TO_CHAR(emp_rec.empno)); DBMS_OUTPUT.PUT_LINE(' ' emp_rec.ename); END LOOP; CLOSE c1; Copyright 2009, Oracle. All rights reserved. 14

15 効果的な変数定義 (%ROWTYPE) %ROWTYPE をつかうとレコード変数定義が容易 CURSOR c1 IS SELECT empno,ename FROM emp; カーソル定義をもとにレコード変数を定義 TYPE emp_rec_type IS RECORD ( CURSOR c1 IS empno NUMBER(4), SELECT empno,ename FROM emp; ename VARCHAR2(10) emp_rec c1%rowtype; ); emp_rec emp_rec_type; テーブル定義をもとにレコード変数を定義 ( テーブルの全カラムがフィールドとして定義 ) CURSOR c1 IS SELECT empno,ename FROM emp; emp_rec emp%rowtype; Copyright 2009, Oracle. All rights reserved. 15

16 Agenda PL/SQLとは プログラムを作成してみる 例外処理 ストアド プログラム パッケージ機能でライブラリをつくる トランザクション制御 無償技術サービス Oracle Direct Concierge SQL Server からの移行アセスメント MySQL からの移行相談 PostgreSQL からの移行相談 Access からの移行アセスメント Oracle Database バージョンアップ支援 Oracle Developer/2000 Web アップグレード相談 パフォーマンス クリニック Oracle Database 構成相談 Oracle Database 高可用性診断 システム連携アセスメント システムセキュリティ診断 簡易業務診断 メインフレーム資産活用 Copyright 2009, Oracle. All rights reserved. 16

17 エラーを補足する SQL のエラーが発生すると処理を中断する ファイル : insert_emp01.sql DECLARE -- emp 表の empno カラムは NUMBER(4) INSERT INTO emp (empno,ename) VALUES (1001,'Ichiro'); INSERT INTO emp (empno,ename) VALUES (10010,'Ichiro*10'); INSERT INTO emp (empno,ename) VALUES (1002,'Ichiro+1'); EXCEPTION WHEN OTHERS THEN 桁あふれでエラーが発生する ブロック毎に例外処理を記述できる DBMS_OUTPUT.PUT_LINE('Error'); 17 / Error PL/SQL プロシージャが正常に完了しました SQL> select empno,ename from emp where ename like 'Ichi%'; EMPNO ENAME Ichiro SQL> エラーが発生した時点で例外処理部に処理が移るので 処理が中断されることになる Copyright 2009, Oracle. All rights reserved. 17

18 エラーを補足し エラー情報を表示する SQLERRM SQLCODE を利用する ファイル : insert_emp02.sql DECLARE -- emp 表の empno カラムは NUMBER(4) INSERT INTO emp (empno,ename) VALUES (1001,'Ichiro'); 18 / ORA-01438: この列に許容される指定精度より大きな値です PL/SQL プロシージャが正常に完了しました INSERT INTO emp (empno,ename) VALUES (10010,'Ichiro*10'); INSERT INTO emp (empno,ename) VALUES (1002,'Ichiro+1'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE(SQLCODE); SQL> select empno,ename from emp where ename like 'Ichi%'; EMPNO ENAME Ichiro SQL> Copyright 2009, Oracle. All rights reserved. 18

19 エラーを無視し 処理を継続する ブロックを入れ子にする ファイル : insert_emp03.sql DECLARE -- emp 表のempnoカラムはNUMBER(4) INSERT INTO emp (empno,ename) VALUES (1001,'Ichiro'); INSERT INTO emp (empno,ename) VALUES (10010,'Ichiro*10'); EXCEPTION WHEN OTHERS THEN NULL; エラーが発生しても何もしない INSERT INTO emp (empno,ename) VALUES (1002,'Ichiro+1'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error'); 19 / PL/SQL プロシージャが正常に完了しました SQL> select empno,ename from emp where ename like 'Ichi%'; EMPNO ENAME Ichiro 1002 Ichiro+1 SQL> 処理が継続された為 2 件のデータが登録されている Copyright 2009, Oracle. All rights reserved. 19

20 特定のエラーを補足し エラーに応じた処理をおこなう 特定のエラーを補足する ファイル : ins_upd_emp01.sql DECLARE INSERT INTO emp (empno,ename) VALUES (1001,'Ichiro'); INSERT INTO emp (empno,ename) VALUES (1001,'Ichiro 2'); EXCEPTION WHEN DUP_VAL_ON_INDEX THEN UPDATE emp SET ename = 'Ichiro 2' WHERE empno = 1001; WHEN OTHERS THEN NULL; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error'); キー重複エラーを補足し UPDATE 20 / PL/SQL プロシージャが正常に完了しました SQL> select empno,ename from emp where ename like 'Ichi%'; EMPNO ENAME Ichiro 2 SQL> Copyright 2009, Oracle. All rights reserved. 20

21 エラー処理を上位ブロックにまかせる ブロックを入れ子にする ファイル : insert_emp04.sql DECLARE -- emp 表のempnoカラムはNUMBER(4) INSERT INTO emp (empno,ename) VALUES (1001,'Ichiro'); INSERT INTO emp (empno,ename) VALUES (10010,'Ichiro*10'); EXCEPTION WHEN OTHERS THEN RAISE; INSERT INTO emp (empno,ename) VALUES (1002,'Ichiro+1'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error'); 19 / Error PL/SQL プロシージャが正常に完了しました SQL> select empno,ename from emp where ename like 'Ichi%'; EMPNO ENAME Ichiro SQL> 入れ子のブロック内でエラーが発生すると 上位ブロックの例外処理部が実行される (3 番目の INSERT は実行されない ) Copyright 2009, Oracle. All rights reserved. 21

22 ユーザー独自の例外を発生させる RAISE_APPLICATION_ERROR を利用 ファイル : raise_appl_err01.sql DECLARE DBMS_OUTPUT.PUT_LINE('1---'); RAISE_APPLICATION_ERROR( , 'User Exception'); 15 / 1--- ORA-20101: User Exception メッセージも取得可能 PL/SQL プロシージャが正常に完了しました SQL> DBMS_OUTPUT.PUT_LINE('2---'); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE(SQLCODE); 実行されない ユーザが利用できるエラー番号の範囲 : から まで Copyright 2009, Oracle. All rights reserved. 22

23 例外を定義する 例外を定義すると 例外処理部で独自のハンドリングが可能 ファイル : raise_appl_err02.sql DECLARE usr_excp EXCEPTION; usr_excp_num NUMBER := ; usr_excp_str VARCHAR2(30) := 'User Exception'; PRAGMA EXCEPTION_INIT(usr_excp, ); DBMS_OUTPUT.PUT_LINE('1---'); RAISE_APPLICATION_ERROR(usr_excp_num, DBMS_OUTPUT.PUT_LINE('2---'); EXCEPTION WHEN usr_excp THEN usr_excp_str); DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE(SQLCODE); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error'); 19 / 1--- ORA-20101: User Exception PL/SQL プロシージャが正常に完了しました SQL> Copyright 2009, Oracle. All rights reserved. 23

24 RAISE によるユーザ定義例外処理 RAISE ではエラーメッセージを定義できない ファイル : raise_appl_err03.sql DECLARE usr_excp EXCEPTION; usr_excp_num NUMBER := ; usr_excp_str VARCHAR2(30) := 'User Exception'; PRAGMA EXCEPTION_INIT(usr_excp, ); DBMS_OUTPUT.PUT_LINE('1---'); RAISE usr_excp; DBMS_OUTPUT.PUT_LINE('2---'); EXCEPTION WHEN usr_excp THEN DBMS_OUTPUT.PUT_LINE(SQLERRM); DBMS_OUTPUT.PUT_LINE(SQLCODE); WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Error'); 17 / エラーメッセージが取得できない 1--- ORA-20101: PL/SQL プロシージャが正常に完了しました SQL> ユーザ定義例外を作成することで例外処理部が記述しやすくなる 例外を発生させるときは RAISE_APPLICATION_ERROR を利用する Copyright 2009, Oracle. All rights reserved. 24

25 入れ子のブロックにおける例外処理の流れ 例外発生時に処理全体を中止 ( 外側のブロックの例外処理部へ ) EXCEPTION WHEN OTHERS THEN RAISE; EXCEPTION WHEN OTHERS THEN 処理 1 処理 2 例外発生時には 処理 2 は実行されない 例外発生時に当該ブロックの処理のみ中止 EXCEPTION WHEN OTHERS THEN EXCEPTION /* RAISE; */ WHEN OTHERS THEN 処理 1 処理 2 Copyright 2009, Oracle. All rights reserved. 25

26 Agenda PL/SQLとは プログラムを作成してみる 例外処理 ストアド プログラム パッケージ機能でライブラリをつくる トランザクション制御 無償技術サービス Oracle Direct Concierge SQL Server からの移行アセスメント MySQL からの移行相談 PostgreSQL からの移行相談 Access からの移行アセスメント Oracle Database バージョンアップ支援 Oracle Developer/2000 Web アップグレード相談 パフォーマンス クリニック Oracle Database 構成相談 Oracle Database 高可用性診断 システム連携アセスメント システムセキュリティ診断 簡易業務診断 メインフレーム資産活用 Copyright 2009, Oracle. All rights reserved. 26

27 ストアド プログラム ストアド プロシージャ PL/SQL で記述した一連の処理手続きに名前をつけ Oracle Database に登録したもの DECLARE CREATE PROCEDURE proc IS ストアド ファンクション PL/SQL で記述した なんらかの値を返却する処理手続きに名前 をつけ Oracle Database に登録したもの DECLARE IF cnt > 20 THEN END IF; DECLARE IF func(cnt,20) THEN END IF; CREATE FUNCTION func (p1 NUMBER, p2 NUMBER) RETURN BOOLEAN IS IF p1 > p2 THEN RETURN TRUE; END IF; RETURN FALSE; Copyright 2009, Oracle. All rights reserved. 27

28 プロシージャとファンクションの使い分け プロシージャ バッチ処理などの一連の処理手続きはプロシージャにて実装 プロシージャ内部で INSERT/UPDATE/DELETE を実施 エラーを例外にて捕捉でき 必要に応じて処理を取り消す (ROLLBACK) ことができる ファンクション パラメータにて渡された値の判定 変換処理 値の抽出をおこなう 一般的にファンクション内部では INSERT/UPDATE/DELETE を実施しない エラーは例外で捕捉するものの例外処理は行なわず エラーが発生したことを示すなんらかの値を返却することが多い SQL 文中で利用することが可能 ( 一部例外あり ) Copyright 2009, Oracle. All rights reserved. 28

29 ファンクションをつくる テーブルより特定の情報を取得する 消費税テーブルから指定日のレートを取得する CREATE OR REPLACE FUNCTION IS get_sales_tax(date_in IN DATE) RETURN NUMBER tax sales_tax.tax%type; SELECT tax INTO tax FROM sales_tax WHERE FROM_DATE <= date_in AND TO_DATE RETURN tax; EXCEPTION WHEN OTHERS THEN RETURN NULL; >= date_in; データ変換をおこなう 指定日がどの四半期に含まれるかを返す CREATE OR REPLACE FUNCTION IS get_quarter(date_in IN DATE) RETURN NUMBER mm_str VARCHAR2(2); mm_str := TO_CHAR(date_in,'MM'); CASE mm_str WHEN '01' THEN RETURN 3; WHEN '02' THEN RETURN 3; WHEN '03' THEN RETURN 4; WHEN '04' THEN RETURN 4; WHEN '05' THEN RETURN 4; WHEN '06' THEN RETURN 1; WHEN '07' THEN RETURN 1; WHEN '08' THEN RETURN 1; WHEN '09' THEN RETURN 2; WHEN '10' THEN RETURN 2; WHEN '11' THEN RETURN 2; WHEN '12' THEN RETURN 3; ELSE END CASE; EXCEPTION WHEN OTHERS THEN RETURN NULL; RETURN NULL; Copyright 2009, Oracle. All rights reserved. 29

30 Agenda PL/SQLとは プログラムを作成してみる 例外処理 ストアド プログラム パッケージ機能でライブラリをつくる トランザクション制御 無償技術サービス Oracle Direct Concierge SQL Server からの移行アセスメント MySQL からの移行相談 PostgreSQL からの移行相談 Access からの移行アセスメント Oracle Database バージョンアップ支援 Oracle Developer/2000 Web アップグレード相談 パフォーマンス クリニック Oracle Database 構成相談 Oracle Database 高可用性診断 システム連携アセスメント システムセキュリティ診断 簡易業務診断 メインフレーム資産活用 Copyright 2009, Oracle. All rights reserved. 30

31 パッケージ機能でライブラリをつくる パッケージとは 論理的に関連する PL/SQL の型 変数およびサブプログラムをまとめる為のスキーマ オブジェクト パッケージの利点 モジュール性 アプリケーション共通で利用するものを一元管理 情報の隠蔽 サブプログラムのオーバーロード機能 オブジェクトの永続性 Copyright 2009, Oracle. All rights reserved. 31

32 パッケージをつくる ファイル ( パッケージ仕様部 ): pkg01.pks CREATE OR REPLACE PACKAGE pkg IS PROCEDURE p (p_in IN VARCHAR2); FUNCTION f_n (p_in IN NUMBER) RETURN NUMBER; FUNCTION f_c (p_in IN VARCHAR2) RETURN VARCHAR2; ファイル ( パッケージ本体 ): pkg01.pkb CREATE OR REPLACE PACKAGE BODY pkg IS PROCEDURE p(p_in IN VARCHAR2)IS DBMS_OUTPUT.PUT_LINE(p_in); END p; FUNCTION f_n (p_in IN NUMBER) RETURN NUMBER IS RETURN p_in; END f_n; FUNCTION f_c (p_in IN VARCHAR2) RETURN VARCHAR2 IS RETURN p_in; END f_c; 6 / パッケージが作成されました 実行する際は 15 / execute にて指定パッケージ本体が作成されました SQL> execute pkg.p('test') test PL/SQL プロシージャが正常に完了しました SQL> select pkg.f_n(1000) from dual; PKG.F_N(1000) プロシージャを SQL*Plus で SQL> select pkg.f_c('test') from dual; PKG.F_C('TEST') test Copyright 2009, Oracle. All rights reserved. 32

33 パッケージのモジュール性 アプリケーション共通で利用するものを一元管理できます 定数 ( アプリで利用するマジックナンバー ) 例外 カーソル 型 ( レコード ) パッケージ仕様部に記載します 他のアプリケーションから実行されたくないサブプログラムなどを隠蔽できます パッケージ本体のみに記述することで パッケージ内でのみ利用可能となります パッケージ内のみ利用可能なプライベート変数も定義できます /* パッケージ仕様部 */ CREATE OR REPLACE PACKAGE xxx IS 外部から利用できるオブジェクト定義外部に公開するサブプログラムの仕様 END xxx; / /* パッケージ本体 */ CREATE OR REPLACE PACKAGE BODY xxx IS パッケージ内プライベート変数パッケージ内のみ利用可能なサブプログラム外部に公開するサブプログラムの本体 END xxx; / Copyright 2009, Oracle. All rights reserved. 33

34 オーバーロード同じ名前 ( 引数の数 順序 型は異なる ) のサブプログラムを定義 ファイル : pkg02.pks CREATE OR REPLACE PACKAGE pkg IS PROCEDURE p (p_in IN VARCHAR2); FUNCTION p (p_in IN NUMBER) RETURN NUMBER; FUNCTION p (p_in IN VARCHAR2) RETURN VARCHAR2; ファイル : pkg02.pkb CREATE OR REPLACE PACKAGE BODY pkg IS PROCEDURE p(p_in IN VARCHAR2)IS DBMS_OUTPUT.PUT_LINE(p_in); END p; FUNCTION p(p_in IN NUMBER) RETURN NUMBER IS RETURN p_in; END p; FUNCTION p(p_in IN VARCHAR2) RETURN VARCHAR2 IS RETURN p_in; END p; 6 / パッケージが作成されました 15 / パッケージ本体が作成されました SQL> execute pkg.p('test') test PL/SQL プロシージャが正常に完了しました SQL> select pkg.p(1000) from dual; PKG.P(1000) SQL> select pkg.p('test') from dual; PKG.P('TEST') test 適切に選択される Copyright 2009, Oracle. All rights reserved. 34

35 パッケージオブジェクトの永続性 パッケージで定義されている変数の変更は セッションが切れるまで有効です セッションが同じであれば 例えば 複数のトランザクション ( 後述 ) 間で グローバル変数 のように利用可能 同一セッションで アプリ基準日付 を使いまわしたい テーブルへのアクセスは セッション開始後の初回実行時のみ CREATE OR REPLACE PACKAGE BODY base_date IS /* プライベート変数 */ p_base_date DATE := NULL; FUNCTION get_base_date RETURN DATE IS IF p_base_date IS NOT NULL THEN dbms_output.put_line('no select'); RETURN p_base_date; END IF; SELECT app_date INTO p_base_date FROM CONTROL_MST WHERE id = 2; RETURN p_base_date; EXCEPTION WHEN OTHERS THEN RETURN NULL; END get_base_date; END base_date; / アプリ基準日付の取得 Copyright 2009, Oracle. All rights reserved. 35

36 Agenda PL/SQLとは プログラムを作成してみる 例外処理 ストアド プログラム パッケージ機能でライブラリをつくる トランザクション制御 無償技術サービス Oracle Direct Concierge SQL Server からの移行アセスメント MySQL からの移行相談 PostgreSQL からの移行相談 Access からの移行アセスメント Oracle Database バージョンアップ支援 Oracle Developer/2000 Web アップグレード相談 パフォーマンス クリニック Oracle Database 構成相談 Oracle Database 高可用性診断 システム連携アセスメント システムセキュリティ診断 簡易業務診断 メインフレーム資産活用 Copyright 2009, Oracle. All rights reserved. 36

37 トランザクション管理 一つのトランザクション 10 万円の銀行振込を行う場合 自分の口座の金額確認自分の口座を減額 (-10 万円 ) 振り込み先口座を増額 (+10 万円 ) データ更新確定 ( コミット ) 更新処理を全て元に戻す ( ロールバック ) 障害発生 Copyright 2009, Oracle. All rights reserved. 37

38 トランザクションの制御 Oracle Database では DML 文や DDL 文が最初に実行された時からトランザクションが開始される ( 宣言は不要 ) 処理 1 処理 2 COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; 通常 処理部の最後に COMMIT 例外処理部分に ROLLBACK トランザクションとして制御したい単位でストアド プロシージャ ( 後述 ) に分割する Copyright 2009, Oracle. All rights reserved. 38

39 トランザクションの制御 ( バッチ的な処理 ) エラーが発生した時に 再実行可能な単位でトランザクションを制御 ( コミット / ロールバック ) する 開始 処理 1 COMMIT? 処理 1-1 処理 1-2 すべての処理が正常終了するまでコミットしない エラーが発生するとすべてをロールバックし エラー原因を対処し 再実行 処理 2 処理 2-1 処理 2-2 処理単位毎にコミットする 処理がどこまで正常に終了しているかを記録しておく必要がある 終了 処理 3 処理 3-1 処理 3-2 再実行時には正常終了部分の処理はスキップする処理を組み込む PL/SQL で作成するジョブの単位を適切に検討する Copyright 2009, Oracle. All rights reserved. 39

40 自律型トランザクション 実行中のトランザクションとは別トランザクションのサブプログラムを呼び出すことができる 呼び出されるサブプログラムには自律型トランザクションの設定 PRAGMA AUTONOMOUS_TRANSACTION 句 を記述する PROCEDURE proc_a IS INSERT INTO table_a proc_b ROLLBACK; PROCEDURE proc_b IS PRAGMA AUTONOMOUS_TRANSACTION; INSERT INTO table_b COMMIT; table_a へデータは登録されないが table_b へはデータが登録される エラー情報を登録するなどの用途で利用 Copyright 2009, Oracle. All rights reserved. 40

41 自律型トランザクション利用例 自律型トランザクションを使わない場合では エラー情報を エラー情報格納用のテーブルに保存する場合 エラー処理を慎重に書く必要があります トランザクション処理の順番 例外処理で COMMIT を実施しなければならない 自律型トランザクション 未使用 EXCEPTION WHEN OTHERS THEN ROLLBACK; INSERT INTO err_tbl (..) VALUES (..); COMMIT; 必ずこの順番 EXCEPTION WHEN OTHERS THEN ROLLBACK; 自律型トランザクション error_proc(..); 順番はどちらでも良い 使用 CREATE PROCEDURE error_proc IS PRAGMA AUTONOMOUS_TRANSACTION; INSERT INTO err_tbl (..) VALUES (..); COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; /*OS 上のファイルへログ出力等 */ END error_proc; Copyright 2009, Oracle. All rights reserved. 41

42 バッチプログラムを作成するときの Tips 複数の SQL を単一のブロックにいれない 必ず例外処理部でエラー処理をおこなう ブロック毎に名前をつけて パッケージ変数に名前をセット エラーが発生するとパッケージ変数にエラー情報をセット 最上位ブロックの例外処理部では上記のパッケージ変数を出力することでエラー発生箇所を特定 sub_main01(); sub_main02(); EXCEPTION WHEN OTHERS THEN dbms_output.put_line(err.func); dbms_output.put_line(err.errcd); IF is_execute() THEN RETURN; /* 処理をスキップ */ END IF; func01(); func02(); COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE; err.func := 'func01'; /* SQL 処理 */ EXCEPTION WHEN OTHERS THEN err.errcd := SQLCODE; RAISE; Copyright 2009, Oracle. All rights reserved. 42

43 まとめ PL/SQL により Oracle Database の内部で効率的に業務処理を実行できます ストアド プログラムを使い業務ロジックを作成することで Oracle Database 利用者は業務ロジックを共用することができます 例外処理 トランザクション処理 パッケージ機能の特性など PL/SQL を利用する上で知っておくべきことを解説しました Copyright 2009, Oracle. All rights reserved. 43

44 OTN ダイセミでスキルアップ!! 一般的な技術問題解決方法などを知りたい! セミナ資料など技術コンテンツがほしい! Oracle Technology Network(OTN) を御活用下さい 一般的技術問題解決には OTN 掲示版の データベース一般 をご活用ください OTN 掲示版は 基本的に Oracle ユーザー有志からの回答となるため 100% 回答があるとは限りません ただ 過去の履歴を見ると 質問の大多数に関してなんらかの回答が書き込まれております 過去のセミナ資料 動画コンテンツは OTN の OTN セミナーオンデマンドコンテンツ へ ダイセミ事務局にダイセミ資料を請求頂いても お受けできない可能性がございますので予めご了承ください ダイセミ資料は OTN コンテンツオンデマンドか セミナ実施時間内にダウンロード頂くようお願い致します Copyright 2009, Oracle. All rights reserved. 44

45 OTN セミナーオンデマンドコンテンツ期間限定にて ダイセミの人気セミナーを動画配信中!! ダイセミのライブ感はそのままに お好きな時間で受講頂けます OTN オンデマンド 掲載のコンテンツ内容は予告なく変更になる可能性があります 期間限定での配信コンテンツも含まれております お早めにダウンロード頂くことをお勧めいたします Copyright 2009, Oracle. All rights reserved. 45

46 オラクルクルクルキャンペーン あの Oracle Database Enterprise Edition が超おトク!! Oracle Database のライセンス価格を大幅に抑えて ご導入いただけます 多くのお客様でサーバー使用期間とされる 5 年間にライセンス期間を限定 期間途中で永久ライセンスへ差額移行 5 年後に新規ライセンスを購入し継続利用 5 年後に新システムへデータを移行 Enterprise Editionはここが違う!! 圧倒的なパフォーマンス! データベース管理がカンタン! データベースを止めなくていい! もちろん障害対策も万全! 詳しくはコチラ お問い合わせフォーム Copyright 2009, Oracle. All rights reserved. 46

47 あなたにいちばん近いオラクル Oracle Direct まずはお問合せください Oracle Direct 検索 システムの検討 構築から運用まで ITプロジェクト全般の相談窓口としてご支援いたします システム構成やライセンス / 購入方法などお気軽にお問い合わせ下さい Web 問い合わせフォームフリーダイヤル 専用お問い合わせフォームにてご相談内容を承ります フォームの入力には Oracle Direct Seminar 申込時と同じログインが必要となります こちらから詳細確認のお電話を差し上げる場合がありますので ご登録さ れている連絡先が最新のものになっているか ご確認下さい 月曜 ~ 金曜 9:00~12:00 13:00~18:00 ( 祝日および年末年始除く ) Copyright 2009, Oracle. All rights reserved. 47

48 Appendix よく使う SQL*Plus の機能 <Insert Picture Here> SQL*Plusとは SQL*PlusとSQLスクリプトにおける I/O SQLスクリプトの実行 SQL*Plusの表示機能 表示内容のファイルへの保存 SQLスクリプト内の文字列置換 SQL*Plusバインド変数の使用と値の表示 SQLスクリプト終了時にOSにエラーを返却 Copyright 2009, Oracle. All rights reserved. 48

49 SQL*Plus とは SQL*Plus は Oracle Database へのコマンドライン インターフェースであり 以下のようなことを実施可能 Oracle Database 管理操作 テーブルやオブジェクト定義の調査 Oracle Databaseへのバッチ処理スクリプトの実行 SQLの実行およびデータ抽出結果の表示や表示形式のフォーマット Copyright 2009, Oracle. All rights reserved. 49

50 SQL*Plus と SQL スクリプトにおける I/O SQL*Plus における情報の流れ 端末 (SQL コマンド SQL*PLUS の設定 ) 端末 ( 実行結果 ) 実行結果のファイル化 テーブル テーブル 入力 出力 Copyright 2009, Oracle. All rights reserved. 50

51 SQL スクリプトの実行 定型処理は SQL*Plus で実行可能な SQL や PL/SQL をテキストファイルにひとつにまとめたものを SQL スクリプトとして実行します SQL*Plus コマンドラインにて指定 C: > sqlplus ファイル名 [. 拡張子 ]> < パラメータ > SQL*Plus で Oracle Database に接続した後に指定 C: > sqlplus username/[password] SQL> start < ファイル名 [. 拡張子 ]> < パラメータ > ファイル名 [. 拡張子 ]> < パラメータ > パラメータを利用する際には SQL スクリプトファイル中にて &1 および &2 のような置換変数 ( 後述 ) を利用します Copyright 2009, Oracle. All rights reserved. 51

52 SQL*Plus の表示機能 ( その 1) ページ設定 SET PAGES[IZE] {14 n} 1 行の長さ SET LIN[ESIZE] {80 n} LINESIZE レポートの列ヘッダ情報の出力有無 レポートの列ヘッダーのアンダーラインで利用する文字 数値のフォーマット SET HEA[DING] {ON OFF} SET UND[ERLINE] {- c ON OFF} SET NUMF[ORMAT] format HEADING UNDERLINE 数値のデフォルト桁 SET NUM[WIDTH] {10 n} NULL 値を表す文字列 画面出力の制御 端末出力の行末スペース削除 ファイル出力時の行末スペース削除 実行結果の表示 列セパレータの指定 SET NULL text SET TERM[OUT] {ON OFF} SET TRIM[OUT] {ON OFF} SET TRIMS[POOL] {ON OFF} SET FEED[BACK] {6 n ON OFF} SET COLSEP { text} PAGESIZE <NULL> NULL xx 行が選択されました FEEDBACK NUMWIDTH 99,999,999 NUMFORMAT Copyright 2009, Oracle. All rights reserved. 52

53 SQL*Plus の表示機能 ( その 2) 列の書式設定 COLUMNコマンドで設定 列の書式設定 数値型 文字列型 日付型 : column < カラム名 > format < 書式 > SQL> column sal format 99,999,999 column < カラム名 > format a< 長さ > SQL> column ename format a10 必要に応じて SQL 文上で TO_CHAR 等を利用して文字列型に SQL> select * from emp where empno = 7900; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO JAMES CLERK SQL> column empno format 9999 SQL> column mgr format 9999 SQL> column sal format 000,009 SQL> select * from emp where empno = 7900; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO JAMES CLERK , 数値型のデフォルトの 桁長は 10 桁 Copyright 2009, Oracle. All rights reserved. 53

54 表示内容のファイルへの保存 SPOOL コマンドを利用して実行結果を保存する SQL> spool c: temp emp.txt SQL> select ename from emp where empno = 7900; ENAME JAMES SQL> spool off SQL> exit D: > D: > type c: temp emp.txt SQL> select ename from emp where empno = 7900; ENAME JAMES SQL> spool off D: > 作成されるファイルは LINESIZE までスペース詰されているので 不要時は SET TRIMSPOOL ON を設定 D: > type emp7900.sql select ename from emp where empno = 7900; D: > sqlplus scott/tiger ( 略 ) SQL> set termout off SQL> spool c: temp emp7900.txt SQL> spool off SQL> exit ( 略 ) D: > type c: temp emp7900.txt ENAME JAMES SQL> spool off D: > SQL スクリプトが作成済みで 出力内容を画面に表示したくない場合は SET TERMOUT OFF を設定する Copyright 2009, Oracle. All rights reserved. 54

55 SQL スクリプト内の文字列置換 置換変数 : 任意の変数の前に '&' もしくは '&&' をつけたもの ファイル : replace01.sql select '&pswd01' from dual; select '&&pswd02' from dual; pswd01に値を入力してください : test1 旧 1: select '&pswd01' from dual 新 1: select 'test1' from dual 単一 & 置換変数はスクリプト中で出現する度に入力を促す 二重 & 置換変数は スクリプト中の初回は入力を促すが 後続の変数では初回と同じ値を利用する ( 何回も問い合わせてこない ) 1 回目の実行 2 回目の実行 (1 回目に続けて実行 ) pswd01に値を入力してください : test-001 旧 1: select '&pswd01' from dual 新 1: select 'test-001' from dual 'TEST test1 pswd02に値を入力してください : test2 旧 1: select '&&pswd02' from dual 新 1: select 'test2' from dual 'TEST test2 'TEST test-001 旧新 'TEST test2 1: select '&&pswd02' from dual 1: select 'test2' from dual Copyright 2009, Oracle. All rights reserved. 55

56 SQL*Plus バインド変数の使用と値の表示 バインド変数は SQL*Plus で作成し PL/SQL および SQL で参照することのできる変数 ファイル : val00.sql VARIABLE ret_val NUMBER :ret_val := 0; PRINT PRINT ret_val ファイル : val01.sql :ret_val := :ret_val + 4; / 初期化 PL/SQL プロシージャが正常に完了しました PL/SQL プロシージャが正常に完了しました RET_VAL PL/SQL プロシージャが正常に完了しました RET_VAL SQL> VARIABLE で設定し 表示は PRINT でおこなう 複数のスクリプト間で変数の受け渡しが可能 Copyright 2009, Oracle. All rights reserved. 56

57 SQL スクリプト終了時に OS にエラーを返却 EXIT および WHENEVER SQLERROR を利用する ファイル : exit01.sql WHENEVER SQLERROR EXIT SQL.SQLCODE ROLLBACK SELECT * FROM &tname where empno = 9999; EXIT 0 D: > sqlplus -s scott/tiger tnameに値を入力してください : emp 旧 1: SELECT * FROM &tname where empno = 9999 新 1: SELECT * FROM emp where empno = 9999 レコードが選択されませんでした D: > echo %=ExitCode% D: > sqlplus -s scott/tiger tnameに値を入力してください : em 旧 1: SELECT * FROM &tname where empno = 9999 新 1: SELECT * FROM em where empno = 9999 SELECT * FROM em where empno = 9999 * 行 1でエラーが発生しました : ORA-00942: 表またはビューが存在しません D: > echo %=ExitCode% AE 3AE(16 進数 )=942(10 進数 ) SQL.SQLCODE は UNIX 系 OS では終了コードが 1byte しかないので 256 で割った余りを返す (KROWN#10025 ) Copyright 2009, Oracle. All rights reserved. 57

58 以上の事項は 弊社の一般的な製品の方向性に関する概要を説明するものです また 情報提供を唯一の目的とするものであり いかなる契約にも組み込むことはできません 以下の事項は マテリアルやコード 機能を提供することをコミットメント ( 確約 ) するものではないため 購買決定を行う際の判断材料になさらないで下さい オラクル製品に関して記載されている機能の開発 リリースおよび時期については 弊社の裁量により決定されます Oracle PeopleSoft JD Edwards 及び Siebel は 米国オラクル コーポレーション及びその子会社 関連会社の登録商標です その他の名称はそれぞれの会社の商標の可能性があります Copyright 2009, Oracle. All rights reserved. 58