| Oracle® Database Oracleプリコンパイラのためのプログラマーズ・ガイド 11g リリース2(11.2) B61344-01 |
|
 前へ |
 次へ |
この章の項目は次のとおりです。
この章では、埋込みSQLプログラムの機能について説明します。埋込みSQLプログラムが動作する特殊な環境と、その環境がアプリケーション設計に及ぼす影響について調べます。
埋込みSQLプログラミングの基本概念とアプリケーション開発の手順について説明した後、簡単なプログラムを使用して、要点を具体的に説明します。
この項では、後に続く章の内容の基本概念について説明します。ここで説明する内容は次のとおりです。
埋込みSQLとは、アプリケーション・プログラムに記述されているSQL文のことです。SQL文を含むアプリケーション・プログラムはホスト・プログラムと呼ばれ、その記述言語はホスト言語と呼ばれます。たとえば、Pro*COBOLプリコンパイラでは、SQL文をCOBOLホスト・プログラムに埋め込むことができます。
たとえば、Oracleデータの操作や問合せを行うには、INSERT、UPDATE、DELETEおよびSELECT文を使用します。INSERTではデータの行をデータベース表に追加し、UPDATEでは行を変更し、DELETEでは不要な行を削除し、SELECTでは検索条件と一致する行を取得します。
Oracleプリコンパイラでは、すべてのOracle文がサポートされています。たとえば、強力なSET ROLE文を使用すると、データベース権限を動的に管理できます。ロールとは、関連するシステム権限やオブジェクト権限、あるいはユーザーまたは他のロールに付与された関連するシステム権限やオブジェクト権限の名前付きグループです。ロールの定義は、Oracleデータ・ディクショナリに格納されます。アプリケーションでは、必要に応じてSET ROLE文を使用し、ロールを有効または無効にできます。
アプリケーション・プログラムでは、SQLのみが有効で、SQL*Plus文は無効です。(SQL*Plusには、環境パラメータの設定、編集およびレポートの書式設定のための文が追加されています。)
埋込みSQL文には、すべての対話型SQL文に加えて、Oracleとホスト・プログラムの間でデータを転送できるその他の文があります。埋込みSQL文には、実行文と宣言文の2種類があります。
実行文では、SQLLIBランタイム・ライブラリへのコールが発生します。実行文は、Oracleへの接続、Oracleデータの定義、問合せ、操作、Oracleデータへのアクセス制御およびトランザクション処理に使用します。他のホスト言語の実行文を配置できる位置であれば、どこにでも記述できます。
一方、宣言文ではSQLLIBへのコールは発生せず、Oracleデータの操作も行われません。宣言文は、Oracleオブジェクト、通信領域およびSQL変数を宣言するために使用します。ホスト言語の宣言を配置できる位置であれば、どこにでも記述できます。
表2-1は、各種埋込みSQL文をグループ化したもので、表2-2は、各種実行文をグループ化したものです。
表2-1 埋込みSQL文
| 宣言SQL | 説明 |
|---|---|
|
|
用途 |
|
|
PL/SQLでのホスト配列の使用 |
|
|
ホスト変数の宣言 |
|
|
Oracleオブジェクトの命名 |
|
|
ファイルへのコピー |
|
|
データ型の同値化 |
|
|
変数の同値化 |
|
|
ランタイム・エラーの処理 |
*対話形式なし
表2-2 実行SQL文とその説明
| 実行SQL | 説明 |
|---|---|
|
|
用途 |
|
|
Oracleデータの定義および制御 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Oracleデータの問合せおよび操作 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
トランザクションの処理 |
|
|
|
|
|
|
|
|
|
|
|
動的SQLの使用 |
|
|
|
|
|
|
|
|
セッションの制御 |
|
|
*対話形式なし
作成したアプリケーションでは、SQL文とホスト言語の文を自由に混在させ、SQL文にホスト言語の変数を使用できます。SQL文をホスト・プログラムに組み込むための唯一の特殊要件は、SQL文をキーワードのEXEC SQLで開始し、ホスト言語の終了文字で終了することです。プリコンパイラでは、すべての実行可能なEXEC SQL文がSQLLIBランタイム・ライブラリへのコールに変換されます。
大部分の埋込みSQL文と対話型のSQL文との違いは、新しい句が1つ追加されている、あるいはプログラム変数が使用されているという点のみです。次の対話型と埋込み型のROLLBACK文を比較してください。
ROLLBACK WORK; -- interactive EXEC SQL ROLLBACK WORK; -- embedded
埋込みSQL構文の概要は、『Oracle Database SQL言語リファレンス』を参照してください。
ほとんどのアプリケーション・プログラムは、静的SQL文と固定的なトランザクションを処理するように設計されています。この場合、実行前にそれぞれのSQL文およびトランザクションの構成を認識できます。つまり、どのSQLコマンドが発行され、どのデータベース表が変更され、どの列が更新されるかなどが事前にわかるのです。
ただし、アプリケーションによっては、任意の有効なSQL文を実行時に受け入れて処理することが必要な場合もあります。したがって、関係するSQLコマンド、データベース表および列が、実行時までわからないことがあります。
動的SQLは、実行時のプログラムでSQL文を受け入れるか作成し、データ型の変換を明示的に制御する高度なプログラミング技術です。
Oracleプリコンパイラでは、PL/SQLブロックが1つの埋込みSQL文と同様に扱われます。したがって、アプリケーション・プログラム内でSQL文を配置できる位置であれば、どこにでもPL/SQLブロックを記述できます。PL/SQLをホスト・プログラムに埋め込むには、単にPL/SQLと共有する変数を宣言し、PL/SQLブロックをEXEC SQL EXECUTEおよびEND-EXECキーワードで囲みます。
PL/SQLではすべてのSQLデータ操作コマンドとトランザクション処理コマンドをサポートしているため、埋込みPL/SQLブロックからOracleデータを柔軟かつ安全に操作できます。PL/SQLの詳細は、第5章「埋込みPL/SQLの使用方法」を参照してください。
ホスト変数は、ホスト言語で宣言され、Oracleで共有される(つまり、プログラムとOracleの両方がその値を参照できる)スカラー変数または配列変数です。ホスト変数は、Oracleとプログラムとの間で通信を行うための鍵です。
プログラムでは、入力ホスト変数を使用してOracleにデータを渡します。Oracleでは、出力ホスト変数を使用してプログラムにデータおよびステータス情報を渡します。プログラムは入力ホスト変数に値を割り当て、Oracleは出力ホスト変数に値を割り当てます。
ホスト変数は、式を使用できる場所であればどこでも使用できます。ただし、SQL文では、Oracleオブジェクトと区別するために、ホスト変数に接頭辞としてコロン(:)を付ける必要があります。
任意のホスト変数をオプションのインジケータ変数に関連付けることができます。インジケータ変数は、関連付けられたホスト変数の値または条件を示す整変数です。インジケータ変数は、入力ホスト変数へのNULLの割当てと、出力ホスト変数に含まれるNULLまたは切捨て値の検出に使用されます。NULL値は、欠落している値、不明な値または適用不能な値です。
SQL文の場合、インジケータ変数には接頭辞のコロンを付けて、関連付けられたホスト変数の直後に記述する必要があります(そうしないで、さらに読みやすくするには、インジケータ変数の前にオプション・キーワードのINDICATORを付けます)。
通常、ホスト・プログラムからOracleにデータが入力され、Oracleからプログラムにデータが出力されます。Oracleではデータベース表に入力データが格納され、出力データはプログラム・ホスト変数に格納されます。データ項目を格納するために、Oracleではそのデータ型を認識する必要があり、データ型により値の記憶形式と有効範囲が指定されます。
Oracleでは、内部データ型と外部データ型という2種類のデータ型が認識されます。内部データ型は、Oracleでデータベース列にデータを格納する方法を指定します。Oracleでは、データベース擬似列を表す内部データ型も使用し、データベース擬似列からは、特定のデータ項目が戻されますが、表には実際の列はありません。
外部データ型は、データをホスト変数に格納する方法を指定します。ホスト・プログラムからOracleにデータが入力されると、Oracleでは必要に応じて、入力ホスト変数の外部データ型とデータベース列の内部データ型の間で変換を行います。Oracleからホスト・プログラムにデータが出力されると、Oracleでは必要に応じて、データベース列の内部データ型と出力ホスト変数の外部データ型の間で変換を行います。
Oracleプリコンパイラでは、配列ホスト変数(ホスト配列と呼ばれる)を定義して、1つのSQL文で操作できます。配列に対するSELECT、FETCH、DELETE、INSERTおよびUPDATE文を使用すると、大量のデータの問合せおよび操作が容易にできます。
Oracleプリコンパイラではデータ型を同値化できるため、アプリケーションの柔軟性が向上します。つまり、Oracleで入力データを解釈し、出力データの書式を設定する方法をカスタマイズできます。
変数ごとに、サポートされているホスト言語のデータ型をOracleの外部データ型と同値化できます。
Oracleでは、SQL文を処理するために、プライベートSQL領域と呼ばれる作業領域がオープンされます。このプライベートSQL領域には、SQL文の実行に必要な情報が格納されます。カーソルと呼ばれる識別子を使用すると、SQL文に名前を付け、そのプライベート領域内の情報にアクセスし、その処理をある程度制御できます。
静的SQL文には、暗黙カーソルと明示カーソルという2種類のカーソルがあります。Oracleでは、1行のみ戻すSELECT文(問合せ)などのすべてのデータ定義文とデータ操作文に対して、1つのカーソルが暗黙的に宣言されます。ただし、複数行を戻す問合せで2行目以降を処理する場合は、カーソルを明示的に宣言(またはホスト配列を使用)する必要があります。
取得された一連の行はアクティブ・セットと呼ばれ、そのサイズは問合せの検索条件と何行一致するかによって異なります。現在処理している行(カレント行と呼ばれる)を識別するには、明示カーソルを使用します。
たとえば、端末の画面に一連の行が戻されたとします。画面上のカーソルは、最初に処理される行、次に処理される行というように移動していきます。同様に、明示カーソルはアクティブ・セット内のカレント行を指し、これによりプログラムは行を1行ずつ処理できます。
トランザクションとは、論理的に関連のある一連のSQL文です(ある銀行勘定貸方に記帳し、別の銀行の借方に記帳する2つのUPDATEなど)。Oracleでは、トランザクションは1単位として扱われるため、それぞれの文による変更はすべて同時に確定されるか、取り消されるかします。現行のトランザクションは、最後のデータ定義、COMMITまたはROLLBACK文が実行された後に実行されるすべてのデータ操作文で構成されます。
データベースの整合性を確保するために、Oracleプリコンパイラでは、COMMIT文、ROLLBACK文およびSAVEPOINT文を使用して、トランザクションを定義できます。COMMITでは、現行のトランザクション中にデータベースに加えられた変更が確定されます。ROLLBACKでは、現行のトランザクションを終了し、トランザクションの開始以降に加えられた変更がすべて取り消されます。SAVEPOINTでは、トランザクションの現在の位置にマークが付けられ、ROLLBACKと併用することで、トランザクションを部分的に取り消すことができます。
埋込みSQL文を実行すると、処理は成功するか失敗するかのいずれかで、エラーまたは警告が発生することがあります。これらの結果を処理する方法が必要になります。Oracleプリコンパイラには、4つのエラー処理方法が用意されています。
SQLCODE状態変数
SQLSTATE状態変数
SQL通信領域(SQLCA)およびWHENEVER文
Oracle通信領域(ORACA)
SQLCODE/SQLSTATE状態変数
SQL文の実行後、OracleサーバーからSQLCODEまたはSQLSTATEという変数にステータス・コードが戻されます。ステータス・コードは、そのSQL文が正常に実行されたか、あるいはエラーまたは警告の状態を引き起こしたかを示します。
SQLCAおよびWHENEVER文
SQLCAは、Oracleでプログラムにランタイム・ステータス情報を渡すために使用されるプログラム変数を定義するデータ構造体です。SQLCAを使用すると、直前に試みた処理に関するOracleからのフィードバックに基づいて、異なる処理を実行できます。たとえば、DELETE文が成功したかどうかを確認し、成功した場合は削除された行数を確認できます。
WHENEVER文を使用すると、Oracleでエラーまたは警告の状態が検出されたときに自動的に実行されるアクションを指定できます。これらのアクションには、次の文の処理続行、サブルーチンのコール、ラベル付きの文への分岐、停止などがあります。
ORACA
ランタイム・エラーについてSQLCAで提供されるより多くの情報が必要な場合は、ORACAを使用できます。ORACAは、Oracle通信を処理するデータ構造体です。これには、カーソル統計、現行のSQL文に関する情報、オプションの設定およびシステム統計が含まれています。
図2-1は、埋込みSQLアプリケーションの開発プロセスを示しています。
図のように、プリコンパイルの結果、通常のコンパイルが可能なソース・ファイルが生成されます。従来の開発プロセスにプリコンパイルの手順が加わりますが、この手順により、きわめて柔軟なアプリケーションを作成できるため、行う価値は十分にあります。
埋込みSQLをよく理解する方法の1つは、サンプル・プログラムの例を学習することです。
WHENEVER文によるエラーを処理するために、次のプログラムではOracleに接続し、ユーザーに従業員番号の入力を要求して、データベースに従業員の名前、給料、歩合を問合せ、その情報を表示して終了します。
-- declare host and indicator variables EXEC SQL BEGIN DECLARE SECTION; username CHARACTER(20); password CHARACTER(20); emp_number INTEGER; emp_name CHARACTER(10); salary REAL; commission REAL; ind_comm SMALLINT; -- indicator variable EXEC SQL END DECLARE SECTION; -- copy in the SQL Communications Area EXEC SQL INCLUDE SQLCA; display 'Username? '; read username; display 'Password? '; read password; -- handle processing errors EXEC SQL WHENEVER SQLERROR DO sql_error; -- log on to Oracle EXEC SQL CONNECT :username IDENTIFIED BY :password; display 'Connected to Oracle'; display 'Employee number? '; read emp_number; -- query database for employee's name, salary, and commission -- and assign values to host variables EXEC SQL SELECT ENAME, SAL, COMM INTO :emp_name, :salary, :commission:ind_comm FROM EMP WHERE EMPNO = :emp_number; display 'Employee Salary Commission'; display '-------- ------ ----------'; -- display employee's name, salary, and commission (if not null) IF ind_comm = -1 THEN -- commission is null display emp_name, salary, 'Not applicable'; ELSE display emp_name, salary, commission; ENDIF; -- release resources and log off the database EXEC SQL COMMIT WORK RELEASE; display 'Have a good day'; exit program; ROUTINE sql_error BEGIN -- avoid an infinite loop if the rollback results in an error EXEC SQL WHENEVER SQLERROR CONTINUE; -- release resources and log off the database EXEC SQL ROLLBACK WORK RELEASE; display 'Processing error'; exit program with an error; END sql_error;
このマニュアルの大部分のプログラミングの例では、DEPTとEMPの2つのサンプル・データベース表を使用しています。これらの表の定義は次のとおりです。
CREATE TABLE DEPT (DEPTNO NUMBER(2), DNAME VARCHAR2(14), LOC VARCHAR2(13)) CREATE TABLE EMP (EMPNO NUMBER(4) primary key, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2))
DEPT表とEMP表にはそれぞれ、次のデータの行が含まれています。
DEPTNO DNAME LOC ------- ---------- --------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ------- --------- ------ --------- ------ ------ ------- 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 30 7876 ADAMS CLERK 7788 23-MAY-87 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10
