Oracle PL/SQLの変更点

• 追加された行はこのように表示されます。
• 削除された行はこのように表示されます。

!!!Oracle PL/SQL
[[[Oracle Database10g PL/SQL|Oracle Database10g PL/SQL]]]

http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/appdev.102/B19257-01/overview.html#1310

!!特長

*SQLのデータ操作機能と手続き型言語の処理機能の両方が利用できる。
*プログラム・フローを制御できる。
*変数の宣言、プロシージャとファンクションの定義、ランタイム・エラーのトラップが可能。
*複数のアプリケーション間でその定義済みコードを再利用できる。
*単純なSQLで問題が解決できる場合、PL/SQLプログラム内でSQLコマンドを直接発行できる。
*データ型は、SQLの列型と対応している。

!!ブロック構造

*プログラムを構成する基本単位（プロシージャ、ファンクションおよび無名ブロック）
*内部で相互にネストできる論理ブロック。
*宣言はブロックの中で局所的に有効。

!!変数の宣言

*任意のSQLデータ型(CHAR、DATE、NUMBERなど)や、BOOLEAN、PLS_INTEGERなどのPL/SQL固有のデータ型を持つことがでる。
*宣言部の各行の最後には、セミコロン（;）を付ける。
*コンポジット・データ型TABLE、VARRAY、RECORDを使用して、ネストした表、可変サイズの配列（VARRAY）およびレコードも宣言できる。

 DECLARE
   part_no    NUMBER(6);
   part_name  VARCHAR2(20);
   in_stock   BOOLEAN;
   part_price NUMBER(6,2);
   part_desc  VARCHAR2(50);
!!変数への値の代入

変数に値を代入する方法は3つある。
::コロンに等号を付けた代入演算子（:=）を使用する。
変数は演算子の左に、ファンクション・コールを含む式は演算子の右に置きます。変数を宣言するときに、変数に値を代入できます。

 DECLARE
    wages          NUMBER;
    hours_worked   NUMBER := 40;
    hourly_salary  NUMBER := 22.50;
    bonus          NUMBER := 150;
    country        VARCHAR2(128);
    counter        NUMBER := 0;
    done           BOOLEAN;
    valid_id       BOOLEAN;
    emp_rec1       employees%ROWTYPE;
    emp_rec2       employees%ROWTYPE;
    TYPE commissions IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    comm_tab       commissions;
 BEGIN
    wages := (hours_worked * hourly_salary) + bonus;
    country := 'France';
    country := UPPER('Canada');
    done := (counter > 100);
    valid_id := TRUE;
    emp_rec1.first_name := 'Antonio';
    emp_rec1.last_name := 'Ortiz';
    emp_rec1 := emp_rec2;
    comm_tab(5) := 20000 * 0.15;
 END;
 /
::データベース値を選択またはフェッチして代入する方法。
 DECLARE
   bonus  NUMBER(8,2);
   emp_id NUMBER(6) := 100;
 BEGIN
   SELECT salary * 0.10 INTO bonus FROM employees 
     WHERE employee_id = emp_id;
 END;
 /
::値をOUTパラメータまたはIN OUTパラメータとしてサブプログラムに渡し、サブプログラム内で代入する方法。

 DECLARE
   new_sal NUMBER(8,2);
   emp_id  NUMBER(6) := 126;
   PROCEDURE adjust_salary(emp_id NUMBER, sal IN OUT NUMBER) IS  
     emp_job VARCHAR2(10);
     avg_sal NUMBER(8,2);
     BEGIN
       SELECT job_id INTO emp_job FROM employees WHERE employee_id = emp_id;
       SELECT AVG(salary) INTO avg_sal FROM employees WHERE job_id = emp_job;
       DBMS_OUTPUT.PUT_LINE ('The average salary for ' || emp_job 
                             || ' employees: ' || TO_CHAR(avg_sal));
       sal := (sal + avg_sal)/2; -- adjust sal value which is returned
     END; 
 BEGIN
   SELECT AVG(salary) INTO new_sal FROM employees;
   DBMS_OUTPUT.PUT_LINE ('The average salary for all employees: ' 
                         || TO_CHAR(new_sal));
   adjust_salary(emp_id, new_sal); -- assigns a new value to new_sal
   DBMS_OUTPUT.PUT_LINE ('The adjusted salary for employee ' || TO_CHAR(emp_id) 
                         || ' is ' || TO_CHAR(new_sal)); -- sal has new value
 END;
 /
!!バインド変数

*SQLのINSERT、UPDATE、DELETEまたはSELECT文を直接埋め込むと、WHERE句およびVALUES句内の変数を自動的にバインド変数に変換する。
*動的SQLの場合は、変数を通常使用する部分（WHERE句やVALUES句など）にバインド変数を指定する。
**リテラルおよび変数値を連結して単一の文字列にするかわりに、変数をバインド変数の名前（先頭にコロンを追加したもの）に置き換え、USING句を使用して対応するPL/SQL変数を指定します。

 'DELETE FROM employees WHERE employee_id = :id' USING emp_id;

!定数の宣言

*キーワードCONSTANTを付ける
*定数に直接値を代入する必要がある。

 credit_limit CONSTANT NUMBER := 5000.00;