|
|
| |
|
|
| |
| SELECT文の記述方法 |
|
SQL文の基本形
SELECT 列名1, 列名2,・・・・
FROM 表名 ;
|
|
アスタリスク（ * ）の使用
FROM句に指定する表の全列を対象にする場合、SELECT句にそれぞれの列名に代えてアスタリスク（*）を用いることができます。
SELECT * FROM employees;
|
|
表名と列名をドット（ . ）で結ぶ
FROM句に複数の表でリレーションを結んだ表を指定する場合、SELECT句に指定する列の表名を省略することはできません。このような倍意は、表名と列名をドット（ . ）で結びます
SELECT employees.employee_name , departments.department_name FROM employees , departments;
|
|
（SQL文の応用形）
WHERE レコードの抽出条件を指定します
SELECT department_id FROM employees WHERE salary > 1000;
DISTINCT 重複した行をまとめます
SELECT DISTINCT department_id FROM employees WHERE salary > 1000;
GROUP BY 集計を行います（特定の列をキーにした合計値や平均値などを求めます）
SELECT department_id, AVR(salary) FROM employees GROUP BY department_id;
ORDER BY レコードを並び替えます
SELECT * FROM employees ORDER BY employee_id;
|
|
（演算子）
比較
= 等しいかどうかを検査します
SELECT employee_name FROM employees WHERE salary = 1000;
|
!=, ^=, <> 等しくないかどうかを検査します
SELECT employee_name FROM employees WHERE salary ^= 1000;
|
> より大きいかどうかを検査します
SELECT employee_name FROM employees WHERE salary > 1000;
|
< より小さいかどうかを検査します
SELECT employee_name FROM employees WHERE salary < 1000;
|
>= より大きいかまたは等しいかを検査します
SELECT employee_name FROM employees WHERE salary >= 1000;
|
<= より小さいかまたは等しいかを検査します
SELECT employee_name FROM employees WHERE salary <= 1000;
|
IN 要素のいずれかに等しいかを検査します
SELECT employee_name FROM employees WHERE employee_id IN ('200', '300');
SELECT employee_name FROM employees WHERE employee_id IN
(SELECT department_id FROM departments WHERE location_id = '1800');
|
ANY/SOME リスト内の各値または問合せで返された値と指定された値を比較します
SELECT location_id FROM locations WHERE city = SOME ('Tokyo','Toronto');
|
NOT IN 「!=ANY」に相当します
SELECT location_id FROM locations WHERE city NOT IN ('Tokyo','Toronto');
|
ALL リスト内のすべての値または問合せで返された値と指定された値を比較します
SELECT employee_name FROM employees WHERE salary >= ALL (1400, 3000);
|
[NOT] BETWEEN x and y x以上y以下の範囲にあります（ありません）
SELECT employee_name FROM employees WHERE salary BETWEEN 3000 AND 5000;
|
EXISTS サブクエリ（副問合せ）が1行以上を返す場合はTRUEです
SELECT * FROM employees WHERE
EXISTS (SELECT employee_name FROM employees WHERE manager IS NULL);
|
x [NOT] LIKE y [ESCAPE z] xがyのパターンに一致する（しない）場合はTRUEです
SELECT employee_id FROM employees WHERE employee_name LIKE '%E%';
|
IS [NOT] NULL NULLかどうかを検査します
SELECT employee_id FROM employees WHERE last_name IS NOT NULL AND salary > 1500; |
論理
NOT 後続する条件がFALSEの場合、TRUEを返します
SELECT * FROM employees WHERE NOT (manager IS NULL);
|
AND 両方のコンポーネント条件がTRUEの場合、TRUEを返します
SELECT * FROM employees WHERE manager='ROBERT' AND department_id = 1800;
|
OR いずれかのコンポーネント条件がTRUEの場合、TRUEを返します
SELECT * FROM employees WHERE manager='ROBERT' OR department_id = 1800; |
集合
UNION 両方の問合せで選択された重複していない行をすべて返します
SELECT * FROM employees WHERE salary = '1000'
UNION
SELECT * FROM employees WHERE manager = 'Robert';
|
UNION ALL 重複行も含めて、どちらかの問合せで選択された行をすべて返します
SELECT * FROM employees WHERE salary = '1000'
UNION ALL
SELECT * FROM employees WHERE manager = 'Robert';
|
INTERSECT 両方の問合せで選択された重複していない行をすべて返します
SELECT * FROM employees WHERE salary = '1000'
INTERSECT
SELECT * FROM employees WHERE manager = 'Robert';
|
MINUS 最初の問合せで選択された、他と重複しない行をすべて返します
2番目の問合せで選択された行は返されません
SELECT * FROM employees WHERE salary = '1000'
MINUS
SELECT * FROM employees WHERE manager = 'Robert'; |
|
|
|
|
（表の結合） |
|
等価結合 特定のキーで表を結合します。両方の表で一致する行だけが取り扱われます
SELECT employees.first_name , departments.department_name FROM employees
, departments
WHERE employees.department_id = departments.department_id ;
（INNER JOINを使用）
SELECT employees.first_name , departments.department_name FROM
employees INNER JOIN departments ON employees.department_id = departments.department_id
;
（三つの表を結合）
SELECT employees.first_name , departments.department_name , jobs.job_title
FROM employees , departments , jobs
WHERE employees.department_id = departments.department_id
AND employees.job_id = jobs.job_id ;
（INNER JOINを使用）
SELECT employees.first_name , departments.department_name , jobs.job_title
FROM
( employees INNER JOIN departments ON employees.department_id = departments.department_id
)
INNER JOIN jobs ON employees.job_id = jobs.job_id ;
|
外部結合
一方の条件に対するデータが存在しなかった場合でも片方のデータを結果セ ットに含める指定ができます。
他方の表に一致しない行も求めたい場合に用います。
（LEFT JOIN） 左側に指定された表のすべての行が取り扱われます。
右側の表の該当するデータがない部分は、NULLが入ります。
SELECT * FROM employees LEFT JOIN jobs ON employees.job_id = jobs.job_id
;
SELECT * FROM employees ,jobs WHERE employees.job_id = jobs.job_id(+)
;
Oracle では、left join、right join を外部結合演算子 (+) で指定できます。
(+)は、一致する行を持っていない方の条件に付けます。
（RIGHT JOIN） 右側に指定された表のすべての行が取り扱われます。
左側の表の該当するデータがない部分は、NULLが入ります。
SELECT * FROM employees RIGHT JOIN jobs ON employees.job_id = jobs.job_id
;
SELECT * FROM employees ,jobs WHERE employees.job_id(+) = jobs.job_id
;
|
再帰結合(内部結合)
再帰結合は、同じ表を表別名を使用して結合します。
FROM句の表が同じ名前になるので、別名を付けて表名を区別します。
SELECT A.employee_id ,
A.first_name ,
A.manager_id ,
B.first_name kanri
FROM employees A ,
employees B
WHERE A.manager_id = B.employee_id ;
employees表のemployee_idと合致するmanager_idより管理者の名前を取得します。
|
|
|
|
|
（サブクエリ） |
|
複数のクエリを組み合わせて、1つのクエリが生成した出力で、他のクエリの出力を制御することができます。クエリを入れ子にして、内側のクエリが値を生成し、それを外側のクエリの述語が評価して
TRUE かどうか判断します。
SELECT employee_id FROM employees WHERE department_id
IN (SELECT department_id FROM departments WHERE location_id = '1800'));
（三つの表を結合）
SELECT employee_id FROM employees WHERE department_id
IN (SELECT department_id FROM departments WHERE location_id
IN (SELECT location_id FROM locations WHERE city = 'Toronto')); |
|
| |
| SELECT文 （１レコードの取得） |
|
SELECT 文を INTO を使用して記述します。
SELECT INTO文は、１レコードの取得しか出来ません。複数のレコードを取り扱うには カーソル を使用します。
SELECT カラム名 INTO 変数 FROM 表 [WHERE 検索条件など]
例
Declare
nm_job_id job_id%TYPE;
st_job_title job_title%TYPE;
Begin
SELECT job_id, job_title INTO nm_job_id, st_job_title FROM jobs;
End; |
| |
| カーソル （複数レコードの取得） |
|
Oracle は、作業領域を使用してSQL 文の実行や処理情報を格納します。「カーソル」と呼ばれるPL/SQL の構造体を使用すると、作業領域に名前を付けて、そこに格納されている情報にアクセスできます。
「カーソル」は、SELECT文の結果セット（レコードセット）に対して、１レコードづつ処理していく時に利用します。
フェッチ
Declare
CURSOR カーソル変数 IS SELECT文; -- 明示カーソル
レコード格納変数 カーソル変数%ROWTYPE; -- 取得レコードを格納する変数定義
Begin
OPEN カーソル変数
LOOP
FETCH カーソル変数 INTO レコード格納変数; -- レコード1件を取り出す
EXIT WHENカーソル変数%NOTFOUND; -- レコードの最後でループを終了
：
：
END LOOP
close カーソル変数
End;
例
Declare
CURSOR cur IS SELECT * FROM jobs;
jobsrec cur%ROWTYPE;
Begin
OPEN cur;
LOOP
FETCH cur INTO jobsrec;
EXIT WHEN cur%NOTFOUND;
DBMS_OUTPUT_LINE (jobsrec.job_id);
END LOOP;
CLOSE cur;
End;
カーソルFORループ
カーソルFOR ループは、データベースからフェッチされた行をレコードとして暗黙的にループ索引を宣言します。次にカーソルをオープンし、結果セットから行の値をフェッチしてレコード中のフィールドに入れるという作業を繰り返し、すべての行を処理した後にカーソルをクローズします。
Declare
CURSOR カーソル変数 IS SELECT文; -- 明示カーソル
Begin
FOR レコード IN カーソル変数 LOOP
レコード;カラム名 ・・・ レコードのカラムを取り出す
：
：
END LOOP
End;
例 カーソルFOR ループは、jobsrec をレコードとして暗黙的に宣言しています。
Declare
CURSOR cur IS SELECT * FROM jobs;
Begin
FOR jobsrec IN cur LOOP
DBMS_OUTPUT_LINE (jobsrec.job_id);
END LOOP;
End;
カーソル属性
カーソル変数%FOUND -- FETCH が行を戻せば TRUE
カーソル変数%ISOPEN -- オープンされていれば TRUE
カーソル変数%NOTFOUND -- FETCH が行を戻さなければ TRUE
カーソル変数%ROWCOUNT -- FETCH された行数 |
|
|
| INSERT 文 |
|
例
Declare
Begin
INSERT INTO jobs VALUES ('1000', 'IT_ENG', 1000, 2000');
COMMIT;
End;
/
|
|
|
| UPDATE 文 |
|
例
Declare
Begin
UPDATE jobs set job_title = 'PROG' WHERE job_id = '1000';
COMMIT;
End;
/ |
|
|
| DELETE 文 |
|
例
Declare
Begin
DELETE FROM jobs WHERE job_id = '1000';
COMMIT;
End;
/ |
|
|
| コミット・ロールバック( COMMIT、ROLLBACK ) |
|
COMMIT文
COMMIT [WORK];
COMMIT 文は、カレント・トランザクションの間にデータベースに加えられた変更を、明示的に確定します。データベースに加えられた変更は、コミットされるまで確定されたとはみなされません。また、コミットは変更内容が他のユーザーからも見えるようにします。
ROLLBACK文
ROLLBACK [WORK];
ROLLBACK 文はCOMMIT 文の逆です。ROLLBACK文はカレント・トランザクションでデータベースに加えられたすべての変更または一部の変更を取り消します。 |
