第 2 章 PL/SQL の基本記述この章では PL/SQL プログラムの基本的な記述方法について説明します 1. 宣言部 2. 実行部 3. 例外処理部

Size: px

Start display at page:

Download "第 2 章 PL/SQL の基本記述この章では PL/SQL プログラムの基本的な記述方法について説明します 1. 宣言部 2. 実行部 3. 例外処理部"

やすもりおおはし
6 years ago
Views:

1 はじめにコース概要と目的 Oracle 独自の手続き型言語である PL/SQL について説明します PL/SQL の基本構文ストアドサブプログラムトリガーの作成方法またストアドサブプログラムの管理について習得することを目的としています受講対象者これから PL/SQL を使用してアプリケーション開発をされる方前提条件 SQL トレーニングコースを受講された方もしくは同等の知識をお持ちの方 DML 文トランザクションについては既に習得済みであることテキスト内の記述について構文 [ ] 省略可能 { A B } A または B のどちらかを選択 n _ 数値の指定デフォルト値マーク指定バージョンからの新機能 ( 左記の場合 Oracle 12cR1 からの新機能 ) 知っておいたほうが良いテクニックもしくは注意事項参照ページデータディクショナリビュー

2 第 2 章 PL/SQL の基本記述この章では PL/SQL プログラムの基本的な記述方法について説明します 1. 宣言部 2. 実行部 3. 例外処理部

3 1. 宣言部宣言部では実行部で使用するオブジェクトを定義します必要でなければ省略することも可能です宣言部はキーワードで始まり実行部のキーワードにより暗黙的に終了します主に以下のオブジェクトを定義します変数定数カーソル例外 EXCEPTION 宣言部 ( 変数定数カーソル例外等の宣言 ) 実行部 ( SQL によるデータ操作手続き処理の記述 ) 例外処理部 ( 例外発生時の対処方法の記述 ) 株式会社アシスト Copyright(C) K.K. Ashisuto All Rights Reserved. 2-1

4 宣言部 ( 点線内 ) 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

5 (1) 変数と定数手続き型処理では処理で使用する値を変数や定数に格納 ( 代入 ) します変数や定数などの名前 ( 識別子 ) は宣言部で指定します 1) 変数変数とは処理で使用する値を格納しておく場所です処理の途中でデータベースのデータや計算結果を一時的に変数に格納 ( 代入 ) できます一度代入した変数に対して異なる値を上書きして再利用できます変数名データ型 [ NOT NULL ] [ { := DEFAULT } 値 ] ; 代入演算子(:=) または DEFAULT キーワードを指定して初期値を設定できますデフォルトでは変数は NULL 状態です NOT NULL キーワードを指定して変数に NULL が代入されないように定義することもできますその際には変数に初期値を指定する必要があります 2) 定数定数とは変数と同様処理で使用する値を格納しておく場所ですただし変数と異なり定数は 1 つの決まった値を保持するため定数に代入した値は上書きできませんそのためプログラム内で固定値を扱う場合に使用します定数名 CONSTANT データ型 { := DEFAULT } 値 ; 定数宣言時には必ず初期値を指定します株式会社アシスト Copyright(C) K.K. Ashisuto All Rights Reserved. 2-3

6 例 ) 変数定数の宣言を行う /* 変数 */ no NUMBER(5); name VARCHAR2(10) := 'SMITH'; /* 定数 */ prod CONSTANT VARCHAR2(3) := 'ABC'; 省略数値型の値を代入できる変数 no を宣言初期値を指定した変数 name を宣言定数の宣言では必ず初期値を指定しそれ以降は値の上書きはできない変数 NUMBER(5) 変数 no 変数 no にという数値データを代入 NUMBER(5) 変数 no 変数 no にという数値データを再度代入は上書きされる定数 VARCHAR2(3) 定数 prod ABC DEF 定数 prod に DEF という文字データを代入定数に対しては上書きできないためエラーとなる Copyright(C) K.K. Ashisuto All Rights Reserved. 株式会社アシスト 2-4

7 (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; 省略変数 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

8 %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

9 (3) 変数定数宣言時の考慮事項 1) 識別子の命名規則変数や定数などの PL/SQL オブジェクトに定義する名前のことを識別子と呼びます識別子は以下の命名規則に従って定義します識別子の長さは最大 30 バイトです先頭は英字です 2 文字目以降であれば数字ドル記号 ($) シャープ記号(#) アンダースコア (_) を使用できますハイフン(-) スラッシュ(/) アンパサンド(&) 空白などの文字は使用できません予約語は使用できません予約語キーワード一覧 ( 付 -5 ) 2) 識別子の有効範囲識別子は使用できる有効範囲が決められています識別子を指定する際は以下の点に注意します識別子の有効範囲は宣言されたブロックおよびサブブロックです同一ブロック内で重複した識別子を宣言できません異なるブロックであれば同じ識別子を宣言できますが判別しやすくするため異なる識別子で宣言することを推奨します 3) 注意事項 PL/SQL では前方参照ができません変数または定数を参照する場合は事前に宣言されている必要があります前方参照とはまだ宣言していないオブジェクト名やプログラム名を参照することです同一データ型の変数を一度に複数宣言することはできません変数は必ず 1 つずつ宣言してください株式会社アシスト Copyright(C) K.K. Ashisuto All Rights Reserved. 2-7

10 識別子の有効範囲 < 識別子を参照できる例 > < ブロック 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

第 2 章問合せの基本操作この章ではデータベースから情報を検索する際に使用する SELECT コマンドおよび SELECT コマンドと同時に使用する句について説明します 1. 問合せとは 2. 基本的な問合せ 3. 列の別名 4. 重複行を一意にする 5. 検索行の絞込み 6. 文字パター

第 2 章問合せの基本操作この章ではデータベースから情報を検索する際に使用する SELECT コマンドおよび SELECT コマンドと同時に使用する句について説明します 1. 問合せとは 2. 基本的な問合せ 3. 列の別名 4. 重複行を一意にする 5. 検索行の絞込み 6. 文字パターはじめにコース概要と目的データベース処理に使用する SQL の基本構文と使用方法について説明します受講対象者 SQL を使用してアプリケーション開発される方管理者となられる方前提条件 Oracle 概要コースを受講された方もしくは同等の知識をお持ちの方テキスト内の記述について構文 [ ] 省略可能 { A B } A または B のどちらかを選択 n _ 数値の指定デフォルト値

第 2 章 PL/SQL の基本記述 この章では PL/SQL プログラムの基本的な記述方法について説明します 1. 宣言部 2. 実行部 3. 例外処理部

第 2 章 PL/SQL の基本記述この章では PL/SQL プログラムの基本的な記述方法について説明します 1. 宣言部 2. 実行部 3. 例外処理部