Oracle PL/SQL の挿入、更新、削除、選択 [例]

PL/SQLのDMLトランザクション

DMLはデータ操作言語の略で、 SQL テーブルに格納されているデータを変更するコマンド。 PL/SQLブロックこれらのコマンドは操作処理を実行し、PL/SQLは周辺ロジックを提供します。DMLは以下の操作を処理します。

• データの挿入
• データ更新
• データの削除
• データの選択

PL/SQLでは、データ操作はSQLコマンドのみによって行われます。

データの挿入

PL/SQLでは、SQLコマンドINSERT INTOを使用してテーブルに行を追加します。このコマンドは、テーブル名、対象の列、および列の値を入力として受け取り、その値をベーステーブルに挿入します。

INSERTコマンドは、各列の値を指定する代わりに、SELECT文を使用して別のテーブルから直接値を取得することもできます。SELECT文を使用すれば、ソーステーブルに含まれる行数と同じ数の行を一度に挿入できます。

構文：

BEGIN
INSERT INTO <table_name>(<column1>,<column2>,...,<column_n>)
VALUES(<value1>,<value2>,...,<value_n>);
END;

上記の構文は、INSERT INTO コマンドを示しています。テーブル名と値は必須項目ですが、INSERT ステートメントでテーブルの各列に値を指定する場合は、列名は省略可能です。上記のように値を個別に指定する場合は、キーワード VALUES が必須となります。

構文：

BEGIN
INSERT INTO <table_name>(<column1>,<column2>,...,<column_n>)
SELECT <column1>,<column2>,...,<column_n> FROM <table_name2>;
END;

この2番目の形式のINSERT INTOは、値を直接取得します。 SELECTコマンドを使用します。値は個別に指定されないため、キーワードVALUESはここでは使用しないでください。

データ更新

データ更新とは、既存の行の列の値を変更することです。これは、テーブル名、列名、および新しい値を入力として受け取り、データを更新するUPDATE文を使用して行います。

構文：

BEGIN
UPDATE <table_name>
SET <column1>=<value1>,<column2>=<value2>,<column_n>=<value_n>
WHERE <condition that uniquely identifies the record that needs to be updated>;
END;

上記の構文はUPDATE文を示しています。キーワードSETは、PL/SQLエンジンに対し、指定された値で列を更新するように指示します。WHERE句は省略可能です。省略した場合、テーブル全体で指定された列の値が更新されます。

データの削除

データ削除とは、データベーステーブルからレコード全体を削除することを意味します。この目的にはDELETEコマンドが使用されます。

構文：

BEGIN
DELETE FROM <table_name>
WHERE <condition that uniquely identifies the record that needs to be deleted>;
END;

上記の構文はDELETEコマンドを示しています。キーワードFROMは省略可能で、FROM句の有無にかかわらずコマンドの動作は同じです。WHERE句も省略可能で、指定しない場合はテーブル全体が空になります。

データの選択

データプロジェクション、またはフェッチとは、データベーステーブルから必要なデータを取得することです。これは、SELECTコマンドとINTO句を組み合わせて行います。SELECTコマンドはデータベースから値を取得し、INTO句はそれらの値をPL/SQLブロックのローカル変数に割り当てます。

INTO句を含むSELECT文を使用する際には、以下の点を考慮する必要があります。

• INTO句を使用する場合、SELECT文は1つのレコードのみを返す必要があります。これは、1つの変数には1つの値しか格納できないためです。SELECT文が複数の行を返す場合、 TOO_MANY_ROWS 例外 上げられます。
• SELECT文はINTO句内の変数に値を割り当てるため、値を設定するには少なくとも1つのレコードが必要です。レコードが見つからない場合は、NO_DATA_FOUND例外が発生します。
• SELECT句の列数とそのデータ型は、INTO句の変数数とそのデータ型と一致する必要があります。
• 値は、ステートメントで説明されているのと同じ順序でフェッチされ、設定されます。
• WHERE句は省略可能で、取得するレコードにさらに制限を設けることができます。
• SELECT文は、他のDML文のWHERE条件内で使用して、条件の値を定義することができます。
• INSERT、UPDATE、またはDELETE文内で使用されるSELECT文には、INTO句を含めるべきではありません。これらの場合、INTO句は変数に値を代入しないためです。

構文：

BEGIN
SELECT <column1>,...,<column_n> INTO <variable1>,...,<variable_n>
FROM <table_name>
WHERE <condition to fetch the required records>;
END;

上記の構文はSELECT-INTOコマンドを示しています。キーワードFROMは必須で、データを取得するテーブルを指定します。WHERE句は省略可能です。指定しない場合は、テーブル全体からデータが取得されます。

例1： この例では、PL/SQLでDML操作を実行する方法を見ていきます。以下の4つのレコードをempテーブルに挿入します。

次に、「XXX」の給与を15000に更新し、従業員レコード「ZZZ」を削除し、最後に従業員「XXX」の詳細を投影します。

以下のスクリーンショットは、この例で使用されている完全なPL/SQLブロックを示しています。

DECLARE
l_emp_name VARCHAR2(250);
l_emp_no NUMBER;
l_salary NUMBER;
l_manager VARCHAR2(250);
BEGIN
INSERT INTO emp(emp_name,emp_no,salary,manager)
VALUES('BBB',1000,25000,'AAA');
INSERT INTO emp(emp_name,emp_no,salary,manager)
VALUES('XXX',1001,10000,'BBB');
INSERT INTO emp(emp_name,emp_no,salary,manager)
VALUES('YYY',1002,10000,'BBB');
INSERT INTO emp(emp_name,emp_no,salary,manager)
VALUES('ZZZ',1003,7500,'BBB');
COMMIT;
Dbms_output.put_line('Values Inserted');
UPDATE EMP
SET salary=15000
WHERE emp_name='XXX';
COMMIT;
Dbms_output.put_line('Values Updated');
DELETE emp WHERE emp_name='ZZZ';
COMMIT;
Dbms_output.put_line('Values Deleted');
SELECT emp_name,emp_no,salary,manager INTO l_emp_name,l_emp_no,l_salary,l_manager FROM emp WHERE emp_name='XXX';
Dbms_output.put_line('Employee Detail');
Dbms_output.put_line('Employee Name:'||l_emp_name);
Dbms_output.put_line('Employee Number:'||l_emp_no);
Dbms_output.put_line('Employee Salary:'||l_salary);
Dbms_output.put_line('Employee Manager Name:'||l_manager);
END;
/

出力：

Values Inserted
Values Updated
Values Deleted
Employee Detail
Employee Name:XXX
Employee Number:1001
Employee Salary:15000
Employee Manager Name:BBB

Code 説明：

• Code 2行目～5行目： 変数を宣言します。
• Code 7行目～14行目： empテーブルにレコードを挿入します。
• Code 15行目： 挿入トランザクションをコミットしています。
• Code 17行目～19行目： 従業員「XXX」の給与を15000に更新します。
• Code 20行目： 更新トランザクションをコミットしています。
• Code 22行目： 「ZZZ」の記録を削除します。
• Code 23行目： 削除トランザクションをコミットしています。
• Code 25行目： 「XXX」のレコードを選択し、変数l_emp_name、l_emp_no、l_salary、およびl_managerに値を設定します。
• Code 26行目～30行目： 取得したレコードの値を表示します。

よくあるご質問