Oracle 辞典

SQL*Plus コマンドSQL*Plus 自身の命令Lv1 で覚える

DESC 表
SET SERVEROUTPUT ON
SHOW USER

SQL ではないので ; は不要です。DESC で列と型、SET SERVEROUTPUT ON で DBMS_OUTPUT の表示が有効になります。

DESC emp
SET SERVEROUTPUT ON

DUAL計算だけをする 1 行の表Lv1 で覚える

SELECT 式 FROM DUAL;

Oracle では FROM が必須なので、計算や関数の結果だけを見るときに DUAL 表を使います。

SELECT SYSDATE FROM DUAL;
SELECT 3 * 4 FROM DUAL;

NVL / NVL2NULL を置き換える（Oracle）Lv3 で覚える

NVL(値, 代わりの値)
NVL2(値, NULL でないとき, NULL のとき)

COALESCE の Oracle 版です。

SELECT NVL(commission, 0) FROM emp;

DECODE値で場合分け（Oracle）Lv3 で覚える

DECODE(値, 候補1, 結果1, 候補2, 結果2, …, 既定)

単純 CASE の Oracle 版です。最後の引数が既定値（なければ NULL）。

SELECT DECODE(dept_id, 10, '開発', 20, '営業', 'その他') FROM emp;

ROWNUM行番号（Oracle）Lv5 で覚える

WHERE ROWNUM <= n
SELECT * FROM (SELECT … ORDER BY …) WHERE ROWNUM <= n

取り出した順に 1, 2, 3 … と付く番号。ORDER BY より先に付くので、上位 n 件は副問い合わせで並べてから ROWNUM で絞ります。

SELECT * FROM (SELECT * FROM emp ORDER BY salary DESC) WHERE ROWNUM <= 3;

(+) の外部結合Oracle の古い外部結合Lv6 で覚える

FROM a, b WHERE a.列 = b.列(+)

(+) を付けた側が「相手がいなければ NULL」になります。a.列 = b.列(+) は a LEFT JOIN b と同じ。

SELECT e.name, d.name FROM emp e, dept d WHERE e.dept_id = d.dept_id(+);

シーケンス番号を出す仕組みLv7 で覚える

CREATE SEQUENCE 名前 [START WITH n] [INCREMENT BY n];
nextval('名前')  currval('名前')  setval('名前', n)
名前.NEXTVAL / 名前.CURRVAL（Oracle）

呼ぶたびに次の番号を返します。SERIAL や IDENTITY も中ではシーケンスを使っています。

SELECT nextval('order_no');

CONNECT BY階層をたどる（Oracle）Lv8 で覚える

SELECT LEVEL, … FROM 表 START WITH 根の条件 CONNECT BY PRIOR 親の列 = 子の列
ORDER SIBLINGS BY 列
SYS_CONNECT_BY_PATH(列, '/')
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= n

組織図のような木をたどります。LEVEL は深さ。PRIOR を付けた側が親です。

SELECT LPAD(' ', (LEVEL - 1) * 2) || name FROM emp START WITH manager_id IS NULL CONNECT BY PRIOR emp_id = manager_id;

MERGEあれば更新・なければ追加（Oracle）Lv9 で覚える

MERGE INTO 表 t USING 元 s ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET …
WHEN NOT MATCHED THEN INSERT (…) VALUES (…);

2 つの表を突き合わせて、更新と追加を一度に行います。

PL/SQL ブロック手続きを書く（Oracle）Lv13 で覚える

DECLARE 変数 型 := 値; BEGIN … EXCEPTION … END;
/

最後に / だけの行を入れると実行されます。変数への代入は :=、SELECT … INTO 変数 で値を受け取ります。IF・LOOP・FOR が使えます。

BEGIN
  DBMS_OUTPUT.PUT_LINE('hello');
END;
/

DBMS_OUTPUT画面に表示する（Oracle）Lv13 で覚える

SET SERVEROUTPUT ON
DBMS_OUTPUT.PUT_LINE(値);

PL/SQL の中から表示します。先に SET SERVEROUTPUT ON をしないと何も出ません。

カーソル問い合わせの結果を 1 行ずつ（Oracle）Lv15 で覚える

FOR r IN (SELECT …) LOOP … END LOOP;
CURSOR c IS SELECT …; OPEN c; FETCH c INTO …; EXIT WHEN c%NOTFOUND; CLOSE c;

FOR … IN ループが一番簡単です。r.列 で各行の値を使います。%ROWTYPE で 1 行分の変数を宣言できます。

例外処理エラーを受け止める（Oracle）Lv16 で覚える

EXCEPTION WHEN NO_DATA_FOUND THEN … WHEN OTHERS THEN …
RAISE_APPLICATION_ERROR(-20001, 'メッセージ');

NO_DATA_FOUND（0 行）、TOO_MANY_ROWS（2 行以上）、ZERO_DIVIDE（0 で割る）、OTHERS（その他すべて）。SQLERRM でメッセージ。

プロシージャ・ファンクション名前をつけて保存する PL/SQL（Oracle）Lv17 で覚える

CREATE OR REPLACE PROCEDURE 名前 (引数 IN 型) IS 宣言 BEGIN … END; /
CREATE OR REPLACE FUNCTION 名前 (引数 型) RETURN 型 IS BEGIN … RETURN 値; END; /
EXEC 名前(引数)

プロシージャは EXEC で実行、ファンクションは SELECT の中で値として使えます。

トリガー変更のたびに自動で動くLv19 で覚える

PostgreSQL: CREATE FUNCTION f() RETURNS trigger … ; CREATE TRIGGER t BEFORE UPDATE ON 表 FOR EACH ROW EXECUTE FUNCTION f();
Oracle: CREATE OR REPLACE TRIGGER t BEFORE INSERT ON 表 FOR EACH ROW BEGIN … END; /

INSERT・UPDATE・DELETE の前後に処理を入れます。NEW は新しい行、OLD は古い行（Oracle は :NEW / :OLD）。