オラクルで行こう！

ここではオラクルSQLのTips（でもないか＾＾；）を紹介しています（ほとんど自分のメモ代わりという話もありますが．．．）。 「フッ、あほやなこいつ」とか「間違ってるぞ、ゴルァ！」という方は、 どしどし 突っ込みを入れてください。

SQL部分はSQLPlusにコピペすると実験できます。

■ 目次

■ Accessのテーブル作成クエリーはOracleではどうするの？


---とりあえず削除
DROP TABLE T1;
DROP TABLE T2;
DROP TABLE T3;

---元のテーブル
CREATE TABLE T1 (
F1 NUMBER(5),
F2 VARCHAR2(10)
);

---テストデータを入れます
INSERT INTO T1 VALUES(1, 'AAA');
INSERT INTO T1 VALUES(2, 'BBB');
INSERT INTO T1 VALUES(3, 'CCC');

---新しいテーブルを作成
CREATE TABLE T2
AS SELECT * FROM T1
WHERE F1 = 2;
---結果を見ます
SELECT * FROM T2;

---こうすると表定義だけを複写できる
CREATE TABLE T3
AS SELECT * FROM T1
WHERE 1 = 0;
---結果を見ます
SELECT * FROM T3;

■別のテーブルに同一キーのレコードが存在する（しない）場合に、値で更新するには


---とりあえず削除
DROP TABLE T1;
DROP TABLE T2;

---テーブルを作成
CREATE TABLE T1 (
F1 NUMBER(5),
F2 VARCHAR2(10),
F3 NUMBER(1)
);
CREATE TABLE T2 (
F1 NUMBER(5),
F2 VARCHAR2(10)
);

---テストデータを入れます
INSERT INTO T1 VALUES(1, 'AAA', 0);
INSERT INTO T1 VALUES(2, 'BBB', 0);
INSERT INTO T1 VALUES(3, 'CCC', 0);
INSERT INTO T1 VALUES(1, 'aaa', 0);
INSERT INTO T2 VALUES(1, 'xxx');

---T1のF3を更新してみます(存在する場合に更新)
UPDATE T1
SET F3 = 1
WHERE EXISTS (SELECT 'X' FROM T2
WHERE T1.F1 = T2.F1);
---結果を見ます
SELECT * FROM T1;

---T1のF3を更新してみます(存在しない場合に更新)
UPDATE T1
SET F3 = 1
WHERE NOT EXISTS (SELECT 'X' FROM T2
WHERE T1.F1 = T2.F1);
---結果を見ます
SELECT * FROM T1;

■別のテーブルに同一キーのレコードが存在する場合に、フィールド値で更新するには


---とりあえず削除
DROP TABLE T1;
DROP TABLE T2;

---テーブルを作成
CREATE TABLE T1 (
F1 NUMBER(5),
F2 NUMBER(5)
);
CREATE TABLE T2 (
F1 NUMBER(5),
F2 NUMBER(5)
);

---テストデータを入れます
INSERT INTO T1 VALUES(1, 0);
INSERT INTO T1 VALUES(2, 0);
INSERT INTO T1 VALUES(3, 0);
INSERT INTO T1 VALUES(4, 0);
INSERT INTO T2 VALUES(1, 1);
INSERT INTO T2 VALUES(3, 3);

---T1のF2をT2のF2で更新してみます(F1同士が一致するもの)
UPDATE T1
SET (F2) =
(SELECT T2.F2
FROM T2
WHERE T1.F1 = T2.F1(+)
AND ROWNUM = 1)
WHERE EXISTS (SELECT 'X'
FROM T2
WHERE T1.F1 = T2.F1(+));
---結果を見ます
SELECT * FROM T1;

■別のテーブルに同一キーのレコードがある（ない）場合に削除するには


---とりあえず削除
DROP TABLE T1;
DROP TABLE T2;

---テーブルを作成
CREATE TABLE T1 (
F1 NUMBER(5),
F2 NUMBER(5)
);
CREATE TABLE T2 (
F1 NUMBER(5),
F2 NUMBER(5)
);

---テストデータを入れます
INSERT INTO T1 VALUES(1, 0);
INSERT INTO T1 VALUES(2, 0);
INSERT INTO T1 VALUES(3, 0);
INSERT INTO T1 VALUES(4, 0);
INSERT INTO T2 VALUES(1, 1);

---T1のF1とT2のF1が一致するT1のレコードを削除します
DELETE FROM T1
WHERE EXISTS (SELECT 'X' FROM T2
WHERE T1.F1 = T2.F1 );
---結果を見ます
SELECT * FROM T1;

---T1のF1とT2のF1が一致しないT1のレコードを削除します
DELETE FROM T1
WHERE NOT EXISTS (SELECT 'X' FROM T2
WHERE T1.F1 = T2.F1 );
---結果を見ます
SELECT * FROM T1;

■NUMBER型のYYYYMMDDフィールドをDATE型のフィールドに変換するには


---とりあえず削除
DROP TABLE T1;

---テーブルを作成
CREATE TABLE T1 (
F1 NUMBER(8),
F2 DATE
);

---テストデータを入れます
INSERT INTO T1 VALUES(20020415, NULL);
INSERT INTO T1 VALUES(20010101, NULL);
INSERT INTO T1 VALUES(19991231, NULL);

---DATE型に変換します
UPDATE T1 SET
F2 = TO_DATE(SUBSTR(TO_CHAR(F1, '09999999'),6,2) || '/' ||
SUBSTR(TO_CHAR(F1, '09999999'),8,2) || '/' ||
SUBSTR(TO_CHAR(F1, '09999999'),2,4),'MM/DD/YYYY');
---結果を見ます
SELECT * FROM T1;

■DATE型のフィールドをNUMBER型のYYYYMMDDフィールドに変換するには


---とりあえず削除
DROP TABLE T1;
---テーブルを作成
CREATE TABLE T1 (
F1 DATE,
F2 NUMBER(8)
);

---テストデータを入れます
INSERT INTO T1 VALUES('2002-04-15', NULL);
INSERT INTO T1 VALUES('2001-01-01', NULL);
INSERT INTO T1 VALUES('1999-12-31', NULL);

---DATE型に変換します
UPDATE T1 SET
F2 = TO_CHAR(F1,'YYYY') * 10000 +
TO_CHAR(F1,'MM') * 100 +
TO_CHAR(F1,'DD');
SELECT * FROM T1;

■DECODEで<、=、>を判断するには


---とりあえず削除
DROP TABLE T1;

---テーブルを作成
CREATE TABLE T1 (
F1 NUMBER(5),
F2 NUMBER(5)
);

---テストデータを入れます
INSERT INTO T1 VALUES(100, 200);
INSERT INTO T1 VALUES(1000, 99);
INSERT INTO T1 VALUES(1999, 1999);

---DECODEで判定します
SELECT F1, F2,
DECODE(SIGN(F1 - F2), 0, 'F1=F2',
-1, 'F1<F2',
1, 'F1>F2') BIGORSMALL
FROM T1;

■剰余が０かどうかの判定(っていうかMOD使えよ)


---とりあえず削除
DROP TABLE T1;

---テーブルを作成
CREATE TABLE T1 (
F1 NUMBER(5),
F2 NUMBER(5)
);
---テストデータを入れます
INSERT INTO T1 VALUES(200, 100);
INSERT INTO T1 VALUES(1000, 99);
INSERT INTO T1 VALUES(1999, 1999);

---DECODEで判定します
SELECT F1, F2, DECODE(SUBSTR(TO_CHAR(F1 / F2,'0999.99999'), 6, 6),
'.00000', 'あまりなし','割り切れない；；') REMAINDER
FROM T1;

■指定件数ずつ抽出するには（ワークを使わない方法はないでしょうか．．．）


---とりあえず削除
DROP TABLE T1;
DROP TABLE T2;
DROP TABLE T3;
---テーブルを作成
CREATE TABLE T1 (
F1 NUMBER(5)
);
---テストデータを入れます
INSERT INTO T1 VALUES(200);
INSERT INTO T1 VALUES(100);
INSERT INTO T1 VALUES(1);
INSERT INTO T1 VALUES(19);
INSERT INTO T1 VALUES(18);
INSERT INTO T1 VALUES(2);
INSERT INTO T1 VALUES(3);
INSERT INTO T1 VALUES(4);
INSERT INTO T1 VALUES(5);
INSERT INTO T1 VALUES(6);
INSERT INTO T1 VALUES(7);
INSERT INTO T1 VALUES(8);
INSERT INTO T1 VALUES(9);
INSERT INTO T1 VALUES(10);
INSERT INTO T1 VALUES(11);

---T1の1件目から5件をT2へ取り出します
CREATE TABLE T2 AS
SELECT F1, ROWIDTOCHAR(ROWID) ID FROM T1
WHERE ROWNUM <= 5
ORDER BY ROWID;
SELECT * FROM T2;

---T1の6件目から5件をT3へ取り出します
CREATE TABLE T3 AS
SELECT F1, ROWIDTOCHAR(ROWID) ID FROM T1
WHERE ROWID > (SELECT MAX(CHARTOROWID(ID)) FROM T2)
AND ROWNUM <= 5
ORDER BY ROWID;
SELECT * FROM T3;

---T1の11件目から5件をT2へ取り出します
TRUNCATE TABLE T2;
INSERT INTO T2 SELECT F1, ROWIDTOCHAR(ROWID) ID FROM T1
WHERE ROWID > (SELECT MAX(CHARTOROWID(ID)) FROM T3)
AND ROWNUM <= 5
ORDER BY ROWID;
SELECT * FROM T2;

■SQL Plusのログオンが面倒


SQL Plusのショートカットを作ってリンク先を

?:\orant\BIN\SQLPLUSW.EXE user/pass@char

に変更するとSQL Plus起動にいちいちログオンダイアログが表示されない。
※もちろんパスやユーザ名、パスワード、接続文字列は適当に変えてね。

リンクはトップページにお願いします。