| ページ一覧 | ブログ | twitter |  書式 | 書式(表) |

MyMemoWiki

Oracle PL/SQL

提供: MyMemoWiki
ナビゲーションに移動 検索に移動

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;

制御

ループ