埋込み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文には、すべての対話型SQL文に加えて、Oracleとホスト・プログラムの間でデータを転送できるその他の文があります。埋込みSQL文には、実行文と宣言文の2種類があります。

実行文では、SQLLIBランタイム・ライブラリへのコールが発生します。実行文は、Oracleへの接続、Oracleデータの定義、問合せ、操作、Oracleデータへのアクセス制御およびトランザクション処理に使用します。他のホスト言語の実行文を配置できる位置であれば、どこにでも記述できます。

一方、宣言文ではSQLLIBへのコールは発生せず、Oracleデータの操作も行われません。宣言文は、Oracleオブジェクト、通信領域およびSQL変数を宣言するために使用します。ホスト言語の宣言を配置できる位置であれば、どこにでも記述できます。

表2-1は、各種埋込みSQL文をグループ化したもので、表2-2は、各種実行文をグループ化したものです。

表2-1 埋込みSQL文

宣言SQL	説明
`STATEMENT`	PURPOSE
`ARRAYLEN`*	PL/SQLでのホスト配列の使用
`BEGIN` `DECLARE` `SECTION`* `END` `DECLARE` `SECTION`*	ホスト変数の宣言
`DECLARE`*	Oracleオブジェクトの命名
`INCLUDE`*	ファイルへのコピー
`TYPE`*	データ型の同値化
`VAR`*	変数の同値化
`WHENEVER`*	ランタイム・エラーの処理

*対話形式はありません。

表2-2 実行SQL文とその説明

実行SQL	説明
`STATEMENT`	PURPOSE
`ALLOCATE`*	Oracleデータの定義および制御
`ALTER`
`ANALYZE`
`AUDIT`
`COMMENT`
`CONNECT`*
`CREATE`
`DROP`
`GRANT`
`NOAUDIT`
`RENAME`
`REVOKE`
`TRUNCATE`
`CLOSE`*
`DELETE`	Oracleデータの問合せおよび操作
`EXPLAIN` `PLAN`
`FETCH`*
`INSERT`
`LOCK` `TABLE`
`OPEN`*
`SELECT`
`UPDATE`
`COMMIT`	To process transactions
`ROLLBACK`
`SAVEPOINT`
`SET` `TRANSACTION`
`DESCRIBE`*	動的SQLの使用
`EXECUTE`*
`PREPARE`*
`ALTER` `SESSION`	セッションの制御
`SET` `ROLE`

*対話形式はありません。

埋込み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コマンド、データベース表および列が、実行時までわからないことがあります。

動的SQLは、プログラムの実行時にSQL文を受け取るかまたは作成し、データ型変換を明示的に管理する高度なプログラミング技術です。

埋込みPL/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の詳細は、埋込みPL/SQLの使用方法を参照してください。

ホスト変数およびインジケータ変数

ホスト変数は、ホスト言語で宣言され、Oracleで共有される(つまり、プログラムとOracleの両方がその値を参照できる)スカラー変数または配列変数です。ホスト変数は、Oracleとプログラムとの間で通信を行うための鍵です。

プログラムでは、入力ホスト変数を使用してOracleにデータを渡します。Oracleでは、出力ホスト変数を使用してプログラムにデータおよびステータス情報を渡します。プログラムは入力ホスト変数に値を割り当て、Oracleは出力ホスト変数に値を割り当てます。

ホスト変数は、式を使用できる場所であればどこでも使用できます。ただし、SQL文では、Oracleオブジェクトと区別するために、ホスト変数に接頭辞としてコロン(:)を付ける必要があります。

任意のホスト変数に任意指定のインディケータ変数を関連付けることができます。インジケータ変数は、関連付けられたホスト変数の値または条件を示す整変数です。インジケータ変数は、入力ホスト変数へのNULLの割当てと、出力ホスト変数に含まれるNULLまたは切捨て値の検出に使用されます。NULL値は、欠落している値、不明な値または適用不能な値です。

SQL文の場合、インジケータ変数には接頭辞のコロンを付けて、関連付けられたホスト変数の直後に記述する必要があります(そうしないで、さらに読みやすくするには、インジケータ変数の前にオプション・キーワードのINDICATORを付けます)。

Oracleデータ型

通常、ホスト・プログラムからOracleにデータが入力され、Oracleからプログラムにデータが出力されます。Oracleではデータベース表に入力データが格納され、出力データはプログラム・ホスト変数に格納されます。データ項目を格納するために、Oracleではそのデータ型を認識する必要があり、データ型により値の記憶形式と有効範囲が指定されます。

Oracleでは、内部データ型と外部データ型という2種類のデータ型が認識されます。内部データ型は、Oracleでデータベース列にデータを格納する方法を指定します。Oracleでは、データベース擬似列を表す内部データ型も使用し、データベース擬似列からは、特定のデータ項目が戻されますが、表には実際の列はありません。

外部データ型は、データがホスト変数にどのように格納されるかを指定します。ホスト・プログラムからOracleにデータが入力されると、Oracleでは必要に応じて、入力ホスト変数の外部データ型とデータベース列の内部データ型の間で変換を行います。Oracleからホスト・プログラムにデータが出力されると、Oracleでは必要に応じて、データベース列の内部データ型と出力ホスト変数の外部データ型の間で変換を行います。

配列

Oracleプリコンパイラでは、配列ホスト変数(ホスト配列と呼ばれる)を定義して、1つのSQL文で操作できます。配列に対するSELECT、FETCH、DELETE、INSERTおよびUPDATE文を使用すると、大量のデータの問合せおよび操作が容易にできます。

データ型の同値化

Oracleプリコンパイラではデータ型を同値化できるため、アプリケーションの柔軟性が向上します。つまり、Oracleで入力データを解釈し、出力データの書式を設定する方法をカスタマイズできます。

変数ごとに、サポートされているホスト言語のデータ型をOracleの外部データ型と同値化できます。

プライベートSQL領域、カーソルおよびアクティブ・セット

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文に関する情報、オプションの設定、およびシステムの統計情報が含まれます。