はじめに コース概要と目的 Oracle 独自の手続き型言語である PL/SQL について説明します PL/SQL の基本構文 ストアド サブプログラム トリガーの作成方法 またストアド サブプログラムの管理について習得することを目的としています 受講対象者 これから PL/SQL を使用してアプリケーション開発をされる方 前提条件 SQL トレーニング コースを受講された方 もしくは 同等の知識をお持ちの方 DML 文 トランザクションについては既に習得済みであること テキスト内の記述について 構文 [ ] 省略可能 { A B } A または B のどちらかを選択 n _ 数値の指定 デフォルト値 マーク 指定バージョンからの新機能 ( 左記の場合 Oracle 12cR1 からの新機能 ) 知っておいたほうが良いテクニック もしくは注意事項 参照ページ データ ディクショナリ ビュー
第 2 章 PL/SQL の基本記述 この章では PL/SQL プログラムの基本的な記述方法について説明します 1. 宣言部 2. 実行部 3. 例外処理部
1. 宣言部 宣言部では実行部で使用するオブジェクトを定義します 必要でなければ省略することも可能です 宣言部は キーワードで始まり 実行部の キーワードにより暗黙的に終了します 主に以下のオブジェクトを定義します 変数 定数 カーソル 例外 EXCEPTION 宣言部 ( 変数 定数 カーソル 例外等の宣言 ) 実行部 ( SQL によるデータ操作 手続き処理の記述 ) 例外処理部 ( 例外発生時の対処方法の記述 ) 株式会社アシスト Copyright(C) K.K. Ashisuto All Rights Reserved. 2-1
宣言部 ( 点線内 ) dept_up NUMBER; 変数の宣言 rate CONSTANT NUMBER := 1.03; 定数の宣言 CURSOR emp_cr IS カーソルの宣言 SELECT empno,sal,deptno FROM emp; emp_rec emp_cr%rowtype; sal_no_data EXCEPTION; 例外の宣言 OPEN emp_cr; LOOP FETCH emp_cr INTO emp_rec; EXIT WHEN emp_cr%notfound; IF emp_rec.sal IS NULL THEN RAISE sal_no_data; END IF; CASE emp_rec.deptno WHEN 10 THEN dept_up := 1.05; WHEN 20 THEN dept_up := 1.06; WHEN 30 THEN dept_up := 1.07; WHEN 40 THEN dept_up := 1.08; ELSE dept_up := rate; END CASE; UPDATE emp SET sal = TRUNC(sal * dept_up) WHERE deptno = emp_rec.deptno; END LOOP; CLOSE emp_cr; EXCEPTION WHEN sal_no_data THEN raise_application_error(-20001,' 給与のデータが存在しません '); Copyright(C) K.K. Ashisuto All Rights Reserved. 株式会社アシスト 2-2
(1) 変数と定数 手続き型処理では 処理で使用する値を変数や定数に格納 ( 代入 ) します 変数や定数などの名前 ( 識別 子 ) は宣言部で指定します 1) 変数 変数とは 処理で使用する値を格納しておく場所です 処理の途中でデータベースのデータや計算結果を一時的に変数に格納 ( 代入 ) できます 一度代入した変数に対して 異なる値を上書きして再利用できます 変数名データ型 [ NOT NULL ] [ { := DEFAULT } 値 ] ; 代入演算子(:=) または DEFAULT キーワードを指定して初期値を設定できます デフォルトでは変数は NULL 状態です NOT NULL キーワードを指定して変数に NULL が代入されないように定義することもできます その際には変数に初期値を指定する必要があります 2) 定数 定数とは 変数と同様 処理で使用する値を格納しておく場所です ただし 変数と異なり定数は 1 つの決まった値を保持するため 定数に代入した値は上書きできません そのため プログラム内で固定値を扱う場合に使用します 定数名 CONSTANT データ型 { := DEFAULT } 値 ; 定数宣言時には必ず初期値を指定します 株式会社アシスト Copyright(C) K.K. Ashisuto All Rights Reserved. 2-3
例 ) 変数 定数の宣言を行う /* 変数 */ no NUMBER(5); name VARCHAR2(10) := 'SMITH'; /* 定数 */ prod CONSTANT VARCHAR2(3) := 'ABC'; 省略 数値型の値を代入できる変数 no を宣言 初期値を指定した変数 name を宣言 定数の宣言では必ず初期値を指定し それ以降は値の上書きはできない 変数 NUMBER(5) 変数 no 11111 11111 1 変数 no に 11111 という数値データを代入 NUMBER(5) 変数 no 22222 22222 2 変数 no に 22222 という数値データを再度代入 11111 は上書きされる 定数 VARCHAR2(3) 定数 prod ABC DEF 定数 prod に DEF という文字データを代入 定数に対しては上書きできないためエラーとなる Copyright(C) K.K. Ashisuto All Rights Reserved. 株式会社アシスト 2-4
(2) %TYPE %ROWTYPE 属性 %TYPE %ROWTYPE 属性を使用すると データベースの表の列や行 既に宣言された変数のデータ型を参照する変数を宣言できます 列や行を参照するため 表の定義が変更されたような場合もコードを変更する必要がありません そのため 表のデータを取出して処理する場合に便利です 1) %TYPE 属性 特定の表の列または既存の変数のデータ型を参照します %TYPE 属性を使用した変数に初期値を指定することが可能です 2) %ROWTYPE 属性 %ROWTYPE 属性は特定の表 ( またはビュー ) の行構造定義を参照します %ROWTYPE 属性は以下の特徴を持 ちます 変数は表の行構造と同じ数のフィールドを持ちます 各フィールドの名前とデータ型は参照表の列の名前とデータ型が対応付けられます %ROWTYPE 属性を使用した変数は初期値を指定できません 各フィールドの参照は 変数名. フィールド名 で指定します 例 )%TYPE %ROWTYPE 属性を使用して変数を宣言する /* %TYPE 属性 */ num_1 dept.dname%type; num_2 num_1%type; e_job emp.job%type := 'SALESMAN'; /* %ROWTYPE 属性 */ d_rec dept%rowtype; 省略 UPDATE dept SET loc = d_rec.loc WHERE deptno = 10; 省略 1 2 3 4 5 1 変数 num_1 は DEPT 表の DNAME 列と同じデータ型で定義されます 2 変数 num_2 は 変数 num_1 と同じデータ型で定義されます 3 変数 e_job は EMP 表の JOB 列と同じデータ型で定義され 初期値 SALESMAN が代入されます 4 変数 d_rec は DEPT 表の各列と同じデータ型 名前が各フィールドで定義されます 5 変数 d_rec の LOC フィールドを参照するには d_rec.loc と指定します 株式会社アシスト Copyright(C) K.K. Ashisuto All Rights Reserved. 2-5
%TYPE 属性 DEPT.DNAME%TYPE DEPT.DNAME%TYPE 変数 num_1 VARCHAR2(14) 変数 num_1 VARCHAR2(20) DEPT 表 DEPT 表 参照しているため 変数の定義変更の必要がない DEPTNO NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) DEPTNO NUMBER(2) DNAME VARCHAR2(20) LOC VARCHAR2(13) DNAME 列の定義を変更 %ROWTYPE 属性 DEPT%ROWTYPE 変数 d_rec D_REC.DEPTNO NUMBER(2) D_REC.DNAME VARCHAR2(14) D_REC.LOC VARCHAR2(13) DEPT 表 DEPTNO NUMBER(2) DNAME VARCHAR2(14) LOC VARCHAR2(13) 各フィールドには列名と同じ名前とデータ型が対応付けられる Copyright(C) K.K. Ashisuto All Rights Reserved. 株式会社アシスト 2-6
(3) 変数 定数宣言時の考慮事項 1) 識別子の命名規則 変数や定数などの PL/SQL オブジェクトに定義する名前のことを識別子と呼びます 識別子は以下の命名 規則に従って定義します 識別子の長さは最大 30 バイトです 先頭は英字です 2 文字目以降であれば 数字 ドル記号 ($) シャープ記号(#) アンダースコア (_) を使用できます ハイフン(-) スラッシュ(/) アンパサンド(&) 空白などの文字は使用できません 予約語は使用できません 予約語 キーワード一覧 ( 付 -5 ) 2) 識別子の有効範囲 識別子は使用できる有効範囲が決められています 識別子を指定する際は以下の点に注意します 識別子の有効範囲は 宣言されたブロックおよびサブブロックです 同一ブロック内で重複した識別子を宣言できません 異なるブロックであれば同じ識別子を宣言できますが 判別しやすくするため 異なる識別子で宣言することを推奨します 3) 注意事項 PL/SQL では前方参照ができません 変数または定数を参照する場合は 事前に宣言されている必要があります 前方参照とは まだ宣言していないオブジェクト名やプログラム名を参照することです 同一データ型の変数を 一度に複数宣言することはできません 変数は必ず 1 つずつ宣言してください 株式会社アシスト Copyright(C) K.K. Ashisuto All Rights Reserved. 2-7
識別子の有効範囲 < 識別子を参照できる例 > < ブロック 1> a NUMBER(2); < ブロック 2> a := 10; サブブロック 変数 a は ブロック 1 で宣言されているため ブロック 1 とそのサブブロックであるブロック 2 で参照できる < 識別子を参照できない例 > < ブロック 1> < ブロック 2> b VARCHAR2(3); サブブロック ; b := 'ABC'; 変数 b は ブロック 2 で宣言されているため ブロック 1 では参照できない Copyright(C) K.K. Ashisuto All Rights Reserved. 株式会社アシスト 2-8