実行可能なSQL文を使用すれば、Oracleデータの問合せ、操作および制御ができ、表、ビュー、索引などのOracleオブジェクトの作成、定義およびメンテナンスができます。この章では、データ操作文(DML)とカーソル制御文を中心に取り上げます。次のSQL文により、Oracleデータの問合せと操作ができます。
SELECT
: 1つ以上の表から行を戻します。
INSERT
: 新しい行を表に追加します。
UPDATE
: 表内の行を変更します。
DELETE
: 表から行を削除します。
INSERT
、UPDATE
またはDELETE
などのデータ操作文を実行するときは、入力ホスト変数の値の設定以外に、文が成功するか失敗するかのみを考えます。これは、SQLCAを調べればわかります。(SQL文を実行すると、SQLCA変数が設定されます。)次の2通りの方法で調べることができます。
WHENEVER
文による暗黙的チェック
SQLCA変数の明示的なチェック
または、MODE={ANSI|ANSI14}
の場合、状態変数SQLSTATE
またはSQLCODE
をチェックできます。詳細は、MODE={ANSI|ANSI14}の場合の状態変数の使用についてを参照してください。
ただし、SELECT
文(問合せ)を実行する場合は、戻されるデータの行も処理する必要があります。問合せは次のように分類できます。
行を戻さない問合せ(有無のみを調べる)
1行のみ戻す問合せ
複数行を戻す問合せ
複数の行を戻す問合せには、明示的に宣言されたカーソルまたはカーソル変数(または、ホスト配列の使用方法で説明されているホスト配列の使用)が必要です。明示カーソルの定義および制御は、次の埋込みSQL文で行います。
DECLARE
: カーソルを指定し、問合せと関連付けます。
OPEN
: 問合せを実行し、アクティブ・セットを識別します。
FETCH
: カーソルを進め、アクティブ・セット内の行を1行ずつ取得します。
CLOSE
: カーソルを無効にします(アクティブ・セットは未定義になります)。
後続の項では、まずINSERT
、UPDATE
、DELETE
と1行のSELECT
文のコードの方法を学習します。その後、複数行のSELECT
文に進みます。
データベースに対する問合せは、一般的なSQL操作です。問合せを発行するには、SELECT
文を使用します。次の例では、EMP
表に問合せを行います。
EXEC SQL SELECT ENAME, JOB, SAL + 2000 INTO :emp_name, :job_title, :salary FROM EMP WHERE EMPNO = :emp_number;
キーワードSELECT
の後の列名と式により、選択リストが作成されます。例の選択リストには、3つの項目が含まれています。WHERE
句(および存在する場合はそれに続く句)で指定された条件のもと、OracleではINTO
句のホスト変数に列値を戻します。選択リスト内の項目数は、INTO
句内のホスト変数と同数であり、すべての戻り値を格納する場所があります。
最も簡単な場合、1つの問合せで1行が戻されると、その形式は前述の例のようになります(EMPNO
は一意キーです)。ただし、1つの問合せで複数行を戻すことができる場合、カーソルを使用してそれらの行をフェッチするか、行を選択してホスト配列に入れる必要があります。
1行のみを戻す問合せを作成したにもかかわらず、実際には複数行が戻される場合、その結果は、SELECT_ERROR
オプションをどのように指定するかによって異なります。SELECT_ERROR
=
YES
(デフォルト)の場合、複数行が戻されると、次のメッセージが表示されます。
ORA-01422: exact fetch returns more than requested number of rows
SELECT_ERROR=NO
の場合、1行が戻され、エラーは発生しません。
SELECT
文では、次の標準的なSQL句をすべて使用できます。INTO
、FROM
、WHERE
、CONNECT
BY
、START
WITH
、GROUP
BY
、HAVING
、ORDER
BY
およびFOR
UPDATE
OF
です。
表またはビューに行を追加するには、INSERT
文を使用します。次の例では、EMP
表に1行追加します。
EXEC SQL INSERT INTO EMP (EMPNO, ENAME, SAL, DEPTNO) VALUES (:emp_number, :emp_name, :salary, :dept_number);
列リストで指定する各列は、INTO
句で指定した表のものである必要があります。VALUES
句では、挿入される値の行を指定しています。値には、定数、ホスト変数、SQL式または擬似列(USER
やSYSDATE
など)を指定できます。
VALUES
句の値の数は、列リストにある名前の数と同じにする必要があります。ただし、VALUES
句にCREATE
TABLE
で定義した表の各列の値が定義どおりの順序で含まれている場合は、列リストを省略してもかまいません。
SELECT
、UPDATE
およびDELETE
文のWHERE
、HAVING
およびSTART
WITH
句での比較の値を指定
CREATE
TABLE
またはINSERT
文で挿入する行のセットを定義
UPDATE
文のSET
句の値を定義
たとえば、ある表から別の表に行をコピーするには、INSERT
文のVALUES
句を次のように副問合せに置き換えます。
EXEC SQL INSERT INTO EMP2 (EMPNO, ENAME, SAL, DEPTNO) SELECT EMPNO, ENAME, SAL, DEPTNO FROM EMP WHERE JOB = :job_title;
中間結果を取得するために、INSERT
文で副問合せがどのように使用されているかに注意してください。
表またはビューの指定した列の値を変更するには、UPDATE
文を使用します。次の例では、EMP
表のSAL
列とCOMM
列を更新します。
EXEC SQL UPDATE EMP SET SAL = :salary, COMM = :commission WHERE EMPNO = :emp_number;
オプションのWHERE
句を使用すれば、行が更新される条件を指定できます。WHERE句の使用方法を参照してください。
SET
句には、値を指定する必要のある1つ以上の行の名前をリストします。副問合せを使用すれば、次の例のように値を指定できます。
EXEC SQL UPDATE EMP SET SAL = (SELECT AVG(SAL)*1.1 FROM EMP WHERE DEPTNO = 20) WHERE EMPNO = :emp_number;