2 基礎の学習
この章の項目は次のとおりです。
この章では、埋込みSQLプログラムの機能について説明します。それらが動作する特殊な環境と、その環境がアプリケーション設計に及ぼす影響について調べます。
埋込みSQLプログラミングの基本概念とアプリケーション開発の手順について説明した後、簡単なプログラムを使用して、要点を具体的に説明します。
2.1 埋込みSQLプログラミングの基本概念
この項では、後に続く章の内容の基本概念について説明します。内容は次のとおりです。
2.1.1 埋込み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文の編集、環境パラメータの設定のための文が追加されています。)
2.1.2 実行文および宣言部
埋込みSQL文には、すべての対話型SQL文に加えて、Oracleとホスト・プログラムの間でデータを転送できるその他の文があります。埋込みSQL文には、実行文と宣言文の2種類があります。
実行文では、SQLLIB
ランタイム・ライブラリへのコールが発生します。実行文は、Oracleへの接続、Oracleデータの定義、問合せ、操作、Oracleデータへのアクセス制御およびトランザクション処理に使用します。他のホスト言語の実行文を配置できる位置であれば、どこにでも記述できます。
一方、宣言文ではSQLLIB
へのコールは発生せず、Oracleデータの操作も行われません。宣言文は、Oracleオブジェクト、通信領域およびSQL変数を宣言するために使用します。ホスト言語の宣言を配置できる位置であれば、どこにでも記述できます。
表2-1 埋込みSQL文
宣言SQL | 説明 |
---|---|
|
用途 |
|
PL/SQLでのホスト配列の使用 |
|
ホスト変数の宣言 |
|
Oracleオブジェクトの命名 |
|
ファイルへのコピー |
|
データ型の同値化 |
|
変数の同値化 |
|
ランタイム・エラーの処理 |
*対話形式はありません。
表2-2 実行SQL文とその説明
実行SQL | 説明 |
---|---|
|
用途 |
|
Oracleデータの定義および制御 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Oracleデータの問合せおよび操作 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
トランザクションの処理 |
|
|
|
|
|
|
|
動的SQLの使用 |
|
|
|
|
|
セッションの制御 |
|
*対話形式はありません。
2.1.3 埋込み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言語リファレンス』を参照してください。
2.1.4 静的SQL文と動的SQL文
大部分のアプリケーション・プログラムは、静的SQL文および固定的なトランザクションを処理するように設計されています。この場合、処理を実行する前に各SQL文およびトランザクションの構成がわかります。つまり、発行されるSQLコマンド、変更されるデータベースの表、更新される列などが事前にわかります。
しかし、実行時に有効なSQL文を受け入れて処理する必要のあるアプリケーションもあります。したがって、関係するSQLコマンド、データベース表および列が、実行時までわからないことがあります。
動的SQLは、プログラムの実行時にSQL文を受け取るかまたは作成し、データ型変換を明示的に管理する高度なプログラミング技術です。
2.1.5 埋込み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の使用方法を参照してください。
2.1.6 ホスト変数および標識変数
ホスト変数は、ホスト言語で宣言され、Oracleで共有される(つまり、プログラムとOracleの両方がその値を参照できる)スカラー変数または配列変数です。ホスト変数は、Oracleとプログラムとの間で通信を行うための鍵です。
プログラムでは、入力ホスト変数を使用してOracleにデータを渡します。Oracleでは、出力ホスト変数を使用してプログラムにデータおよびステータス情報を渡します。プログラムは入力ホスト変数に値を割り当て、Oracleは出力ホスト変数に値を割り当てます。
ホスト変数は、式を使用できる場所であればどこでも使用できます。ただし、SQL文では、Oracleオブジェクトと区別するために、ホスト変数に接頭辞としてコロン(:)を付ける必要があります。
任意のホスト変数に任意指定の標識変数を関連付けることができます。インジケータ変数は、関連付けられたホスト変数の値または条件を示す整変数です。インジケータ変数は、入力ホスト変数へのNULLの割当てと、出力ホスト変数に含まれるNULLまたは切捨て値の検出に使用されます。NULL値は、欠落している値、不明な値または適用不能な値です。
SQL文の場合、インジケータ変数には接頭辞のコロンを付けて、関連付けられたホスト変数の直後に記述する必要があります(そうしないで、さらに読みやすくするには、インジケータ変数の前にオプション・キーワードのINDICATOR
を付けます)。
2.1.7 Oracleのデータ型
通常、ホスト・プログラムからOracleにデータが入力され、Oracleからプログラムにデータが出力されます。Oracleではデータベース表に入力データが格納され、出力データはプログラム・ホスト変数に格納されます。データ項目を格納するために、Oracleではそのデータ型を認識する必要があり、データ型により値の記憶形式と有効範囲が指定されます。
Oracleでは、内部データ型と外部データ型という2種類のデータ型が認識されます。内部データ型は、Oracleでデータベース列にデータを格納する方法を指定します。Oracleでは、データベース擬似列を表す内部データ型も使用し、データベース擬似列からは、特定のデータ項目が戻されますが、表には実際の列はありません。
外部データ型は、データがホスト変数にどのように格納されるかを指定します。ホスト・プログラムからOracleにデータが入力されると、Oracleでは必要に応じて、入力ホスト変数の外部データ型とデータベース列の内部データ型の間で変換を行います。Oracleからホスト・プログラムにデータが出力されると、Oracleでは必要に応じて、データベース列の内部データ型と出力ホスト変数の外部データ型の間で変換を行います。
2.1.9 データ型の同値化
Oracleプリコンパイラではデータ型を同値化できるため、アプリケーションの柔軟性が向上します。つまり、Oracleで入力データを解釈し、出力データの書式を設定する方法をカスタマイズできます。
変数ごとに、サポートされているホスト言語のデータ型をOracleの外部データ型と同値化できます。
2.1.10 プライベートSQL領域、カーソルおよびアクティブ・セット
Oracleでは、SQL文を処理するために、プライベートSQL領域と呼ばれる作業領域がオープンされます。このプライベートSQL領域にはSQL文の実行に必要な情報が格納されます。カーソルと呼ばれる識別子を使用すると、SQL文に名前を付け、そのプライベート領域内の情報にアクセスし、その処理をある程度制御できます。
静的SQL文には、明示的および暗黙的という2種類のカーソルがあります。Oracleでは、1行のみ戻すSELECT
文(問合せ)などのすべてのデータ定義文とデータ操作文に対して、1つのカーソルが暗黙的に宣言されます。ただし、複数行を戻す問合せで2行目以降を処理する場合は、カーソルを明示的に宣言(またはホスト配列を使用)する必要があります。
取得された一連の行はアクティブ・セットと呼ばれ、そのサイズは問合せの検索条件と何行一致するかによって異なります。現在処理している行(カレント行と呼ばれる)を識別するには、明示カーソルを使用します。
たとえば、端末の画面に一連の行が戻されたとします。画面上のカーソルは、最初に処理される行、次に処理される行というように移動していきます。同様に、明示カーソルはアクティブ・セット内のカレント行を指し、これによりプログラムは行を1行ずつ処理できます。
2.1.11 トランザクション
トランザクションとは、論理的に関連のある一連のSQL文です(ある銀行勘定貸方に記帳し、別の銀行の借方に記帳する2つのUPDATE
など)。Oracleでは、トランザクションは1単位として扱われるため、それぞれの文による変更はすべて同時に確定されるか、取り消されるかします。現行のトランザクションは、最後のデータ定義、COMMIT
またはROLLBACK
文が実行された後に実行されるすべてのデータ操作文で構成されます。
データベースの整合性を確保するために、Oracleプリコンパイラでは、COMMIT
文、ROLLBACK
文およびSAVEPOINT
文を使用して、トランザクションを定義できます。COMMIT
では、現行のトランザクション中にデータベースに加えられた変更が確定されます。ROLLBACK
では、現行のトランザクションを終了し、トランザクションの開始以降に加えられた変更がすべて取り消されます。SAVEPOINT
では、トランザクションの現在の位置にマークが付けられ、ROLLBACK
と併用することで、トランザクションを部分的に取り消すことができます。
2.1.12 エラーと警告
埋込み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.2 埋込みSQLアプリケーションの開発ステップ
図2-1は、埋込みSQLアプリケーションの開発プロセスを示しています。
図のように、プリコンパイルの結果、通常のコンパイルが可能なソース・ファイルが生成されます。プリコンパイルを行うと従来の開発過程より1ステップ処理が増えますが、これによって柔軟性に富んだアプリケーションを開発できるという大きな利点があります。
2.3 サンプル・プログラム
埋込み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;
2.4 サンプル表
このマニュアルの大部分のプログラミングの例では、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))
2.4.1 サンプル・データ
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