Know How of Oracle SQL

LastUpdate: 2011/07/29 01:32:09

001 システム日付の取得
 002 項目名の置き換え
 003 ログインIDの取得
 004 テーブル情報取得
 005 範囲選択
 006 NULLの判定
 007 NULLの項目の注意
 008 SUBSTR文
 009 TO_DATE
010 グループ関数
 011 LIKE検索
 012 INSERT 文
 013 UPDATE 文
 014 DELETE 文
 015 CREATE TABLE 文
 016 ｎ日後の日付の求め方
 017 TO_CHAR 使用時の注意
 018 where　文の記述順序について
 019 主キーの検索について
 020 テーブルどうしの関連を設定する方法
 021 時分秒まで含む項目に対し年月日までで検索する方法
 022 一番簡単なバックアップ
 023 IF文もどき　　DECODE　式
 024 LOCK TABLE
025 SEQUENCE 連番取得
 026 テーブルへの項目追加
 027 テーブル定義の情報を調べるSQL
028 コメント
 029 オラクルの項目の型
 030 テーブルを縦に結合
 031 ミリ秒まで取得したい！
032 文字列型はバイト指定？文字列指定？
033 削除する時には早い TRUNCATE
20060425 パーティションについて
 20060605 両方外部結合したい時
 20080116 一括して、連番を振りなおす
 20080514 追加モレのレコードだけを、追加する。（既にあるものは追加しない）
20080529 表結合のいろいろ
 20080616 階層検索
 20080618 CHARどうしの比較は空白を埋めてくれるけど、関数を経由するとうまくいかない
 20081009 一時表
 20081212 テーブルに設定さている主キーを知りたい
 20090730_SYSDATE_SYSTIMESTAMP__
20100121_テーブルに対する変更SQLまとめ
 20101025_HINT文
 20101031_ORA-01779

001 システム日付の取得

SELECT SYSDATE FROM DUAL;

SYSDATE
--------
01-07-15

SQLWKS> SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') FROM DUAL;
TO_CHAR(SYSDATE,'YY
-------------------
2001/09/06 10:09:08
1行選択されました。

002 項目名の置き換え

SELECT SYSDATE AA FROM DUAL;

AA
--------
01-07-15

003 ログインIDの取得

SELECT USER FROM DUAL;

USER
------------------------------
SCOTT

004 テーブル情報取得

Oracleは TABS という、システムテーブルに、各種テーブル情報を取得している。

DESC TABS

TABLE_NAME	VARCHAR2(30
TABLESPACE_NAME	VARCHAR2(30
CLUSTER_NAME	VARCHAR2(30)
IOT_NAME	VARCHAR2(30)
PCT_FREE	NUMBER
PCT_USED	NUMBER
INI_TRANS	NUMBER
MAX_TRANS	NUMBER
INITIAL_EXTENT	NUMBER
NEXT_EXTENT	NUMBER
MIN_EXTENTS	NUMBER
MAX_EXTENTS	NUMBER
PCT_INCREASE	NUMBER
FREELISTS	NUMBER
FREELIST_GROUPS	NUMBER
LOGGING	VARCHAR2(3)
BACKED_UP	VARCHAR2(1)
NUM_ROWS	NUMBER
BLOCKS	NUMBER
EMPTY_BLOCKS	NUMBER
AVG_SPACE	NUMBER
CHAIN_CNT	NUMBER
AVG_ROW_LEN	NUMBER
AVG_SPACE_FREELIST_BLOCKS	NUMBER
NUM_FREELIST_BLOCKS	NUMBER
DEGREE	VARCHAR2(10
INSTANCES	VARCHAR2(10
CACHE	VARCHAR2(5)
TABLE_LOCK	VARCHAR2(8)
SAMPLE_SIZE	NUMBER
LAST_ANALYZED	DATE
PARTITIONED	VARCHAR2(3)
IOT_TYPE	VARCHAR2(12
TEMPORARY	VARCHAR2(1)
NESTED	VARCHAR2(3)
BUFFER_POOL	VARCHAR2(7)

SELECT TABLE_NAME FROM TABS;

SELECT * FROM TAB;
とういうもっといいのがあるらしい。項目が少ないぞ！

005 範囲選択

項目名 BETWEEN スタート AND エンド

SELECT * FROM TEST_TBL1 WHERE KEY1 BETWEEN 'A' AND 'Z' ;

SELECT * FROM TEST_TBL1 WHERE TO_NUMBER(KEY1) BETWEEN 1 AND 10;

項目名 IN (値、値．．．)

SELECT * FROM TEST_TBL1 WHERE KEY1 IN ( '001' , '002' );

006 NULLの判定

項目名 IS NULL

SELECT * FROM TEST_TBL1 WHERE DATA2 IS NULL ;

2009/06/07
なぜ =NULLではいけないのか？
http://www.geocities.jp/mickindex/database/db_3vl.html

007 NULLの項目の注意

たとえば、ある項目が ’１’以外のものを全て取得したい場合、
WHERE 項目名 <> '1'
とすると、NULLは抽出されない。NULLは、値が無いものなので、比較ができないからだ。
NULLも含めて、’１’以外のレコードを抽出したい場合は次のようにする。

SELECT * FROM TEST_TBL1 WHERE DATA2 IS NULL OR DATA2 <> '1';

008 SUBSTR文

SUBSTR ( char , m [ , n] )

ｍが０のとき、1として扱う。
ｍが正のとき、左側から開始文字を探す。
ｍが負のとき、右側から開始文字を探す。

ｎが省略されたとき、終わりの文字までを返す。
ｎが０以下のとき、NULLを返す。

SELECT SUBSTR('ABCDEFG',3,4) FROM DUAL;

SUBS
----
CDEF

SELECT SUBSTR('ABCDEFG' , -2) FROM DUAL;

SU
--
FG

SELECT SUBSTR('ABCDEFG',-5,3) FROM DUAL;

SUB
---
CDE

009 TO_DATE

TO_DATE('2001/01/02 01:23:44' , 'YYYY/MM/DD HH24:MI:SS')

DATE型の変数に、値をセットする時は、TO_DATE関数を使いますが、引数はあくまで文字列です。間違えないように。

同様に、日付型から文字列へは、TO_CHAR を使用します。

TO_CHAR(SYSDATE,'YYYYMMDD')

010 グループ関数

グループ関数とそれ以外を併用する場合は、必ずGROUP BY をつけないと、エラーになります。

SQL> SELECT COUNT(KEY1),MAX(KEY1) FROM TEST_TBL1 WHERE KEY1 = '0010';

COUNT(KEY1) MAX(
----------- ----
1 0010 ←これはOK

SQL> SELECT COUNT(KEY1),MAX(KEY1) ,KEY1 FROM TEST_TBL1 WHERE KEY1 = '0010';

エラー行: 1: エラーが発生しました。
ORA-00937: 単一グループのグループ関数ではありません。

SQL> SELECT COUNT(KEY1),MAX(KEY1) ,KEY1 FROM TEST_TBL1 WHERE KEY1 = '0010' GROUP BY KEY1;

COUNT(KEY1) MAX( KEY1
----------- ---- ----
1 0010 0010 ←こうすれば良い

011 LIKE検索

項目名 LIKE 文字列

SELECT * FROM WHERE DATA1 LIKE '%A%' ;

文字列	働き
'%A'	最後がAで終わるもの
'A%'	Aで始まるもの
'%A%'	Aを含むもの

012 INSERT 文

INSERT INTO テーブル名 VALUES(値、値、、、)；

INSERT INTO TEST_TBL1 VALUES ('AA','BB','CC');

値は、全ての項目について指定する必要があるので、あまり実用的でない。項目の数、並びが変更されたら、変更しなくてはならないから。

INSERT INTO テーブル名（項目名、項目名、、、） VALUES ( 値、値、、、)；

INSERT INTO TEST_TBL1 (DATA1,KEY1) VALUES ('BB1','AA1');

通常はこれを使うべし。

INSERT INTO テーブル名（項目名、項目名、、、） SELECT * FROM テーブル名 WHERE ～；

上記のように、VALUES のかわりにSELECT文を使うこともできます。

013 UPDATE 文

UPDATE テーブル名 SET 項目名 = 値、項目名 = 値、、、、WHERE 条件文；

UPDATE TEST_TBL2 SET DATA2 = 9;

他のテーブルから値を更新したい場合の例

update tbl1
set (data2) = (select tbl2.DATA2 from tbl2 where tbl2.KEY1 = tbl1.KEY1)

更新対象項目が複数である場合を考慮、また、更新の元になるデータがあるときのみ更新するようにした例

update tbl1
set (data1,data2) = (
    select tbl2.DATA1, tbl2.DATA2 from tbl2 where tbl2.KEY1 = tbl1.KEY1
) 
WHERE EXISTS (
    SELECT 1 FROM tbl2 WHERE tbl2.KEY1 = tbl1.KEY1
)

014 DELETE 文

DELETE FROM TEST_TBL2 WHERE KEY = 1;

ROLLBACKの必要がないのなら、TRUNCATE を用いよ

015 CREATE TABLE 文

一番簡単な例

DROP TABLE TEST_TBL2;

CREATE TABLE TEST_TBL2 (

      KEY1    NUMBER(10)             NOT NULL,

      KEY2    NUMBER(10)             NOT NULL,

      DATA1   VARCHAR2(10) ,

      DATA2   VARCHAR2(10) ,

      CONSTRAINT PK_TEST_TBL2        PRIMARY KEY (KEY1,KEY2)

);

016 ｎ日後の日付の求め方

DATE型の変数にｎを増減することで、簡単に求まる。

例）前日を求める。
select sysdate - 1 from dula;

例）一時間後の時刻を求める。
select to_char(sysdate + 1/24,'YYYY/MM/DD HH24:MI:SS') from dual;

ちなみに、１ヶ月後の日付は、SQL関数を使えば簡単。

SELECT ADD_MONTHS(SYSDATE,-1) FROM DUAL;

例）
SQLWKS> SELECT ADD_MONTHS(TO_DATE('2001/01/31'),+1) FROM DUAL;
ADD_MONTHS(TO_DATE('
--------------------
01-02-28

2001/09/06

017 TO_CHAR 使用時の注意

数値型を文字型に変えるとき、

TO_CHAR(123,'0000')

とするが、戻り値は ’△０１２３’ と、先頭に符号のブランクが付くので注意。

ちなみに、マイナス値を入れるとよく判る。

SQLWKS> select '>' || to_char(5,'000') || '<' from dual;
'>'||T
------
> 005<

SQLWKS> select '>' || to_char(-5,'000') || '<' from dual;
'>'||T
------
>-005<

プラス値で、先頭にスペースが付かないようにする為にはFMを用いる

SQLWKS> select '>' || to_char(5,'FM000') || '<' from dual;
'>'||T
------
>005<

2002/01/28

018 where　文の記述順序について

オラクルは後ろから見ていくらしいので、一番先に候補が絞られるものから後ろに書いていくと良い。
例）
WHERE 県=愛知 AND 名字="渡辺"
愛知県の人口よりも、渡辺という名字の人の数の方が圧倒的に少ない

2001/10/31

019 主キーの検索について

オラクルの場合、Primary Keyは、自動的にインデックスが生成される。しかし、複数の項目でPrimary Keyとした場合、その先頭のを検索条件に使わなかった場合は、インデックスが使用されないことがあるので注意。

営業所 key
注文番号 key
もし、注文番号だけで検索した場合は、インデックスは使われない。営業所だけで検索した場合はインデックスが使用される。

021 時分秒まで含む項目に対し年月日までで検索する方法

select URIAGEBI from TABLE where trunc(URIAGEBI) = to_date('2001/11/11');

trunc は小数点以下を省いてくれる関数。オラクルは小数点以下で時分秒を持つのでこれでOK　

　2002/01/22

DATE型は、年/月/日時：分：秒　までしか保有できない。ミリセカンドは保持していないので注意

SYSDATEも同様と思われる。

2002/03/14

023 IF文もどき　　DECODE　式

構文　DECODE(比較対照，比較式１，比較１が正の場合の戻り値
　　　　　　　　　　　　　　　　，比較式２，比較２が正の場合の戻り値
　　　　　　　　　　　　　　　　，合致しない時の戻り値 )

例

SELECT DECODE(10,10,'OK','NG') FROM DUAL;

----------------

OK

SELECT DECODE(11,10,'OK','NG') FROM DUAL;

----------------

NG

SELECT DECODE(KEY,10,'A'

                 ,20,'B'

                 ,30,'C'

                    ,'D') FROM DUAL;

------------------------------------------------

KEYの値が10の時は、Aを返す

KEYの値が20の時は、Bを返す

KEYの値が30の時は、Cを返す

KEYの値が上記以外の時は、Dを返す

2002/05/01

IF文そのものが出来る関数として、CASE 式　があります。詳しくは、SQLリファレンスを！

2002/11/14

024 LOCK TABLE

テーブル単位のロック方法

用途
指定したモードで1つまたは複数の表をロックします。ユーザーの操作中に他のユーザーによる表またはビューへのアクセスを許可または制限するため、自動ロックを手動で置き換えます。

前提条件
表またはビューが自分のスキーマ内にあるか、LOCK ANY TABLEシステム権限が付与されているか、表またはビューに対するオブジェクト権限が必要です。
DBMS MACモードでTrusted Oracle7を使用している場合、DBMSラベルは表またはビューの作成ラベルを制御すいるか、またはREADUPシステム権限を持っていなければなりません。

キーワードとパラメータ

schema

表またはビューを含むスキーマです。schemaを省略すると、自分のスキーマ内に表またはビューが存在しているとみなされます。

table view

ロックする表です。viewを指定した場合、ビューの実表がロックされます。

dblink

表またはビューを位置づける、リモートOracle7データベースに対するデータベース・リンクの名前(完全または一部)です。データベース・リンクの指定に関する説明は、2 - 11ページ「リモート・データベース内のオブジェクトを参照する」を参照してください。分散オプション付きのOracle7を使用している場合に限り、リモート表またはビューに行を挿入することができます。LOCK TABLE文を使用してロックする表は、すべて同じデータベース上になければなりません。
dblinkの指定を省略すると、表またはビューはローカル・データベース中にあるとみなされます。

lockmode

ROW SHARE	ロックされた表への同時アクセスを可能にしますが、排他アクセスのために表全体をロックすることはできなくなります。 ROW SHARE は、SHARE UPDATE と同じ意味で、以前のバージョンのOracle との互換性を保つために用意されています。
ROW EXCLUSIVE	ROW SHARE と同じですが、SHARE モードでロックはできません。行の排他ロックは、更新、挿入、削除の実行時に自動的に適用されます。
SHARE UPDATE	ROW SHARE を参照してください。
SHARE	同時問合せは実行できますが、ロックされた表は更新できません。
SHARE ROW EXCLUSIVE	表全体を見る場合に使用します。これを使用すると他のユーザーがその表内の行は見ることはできますが、SHARE モードで表のロックまたは行の更新を行うことはできません。
EXCLUSIVE	ロックされた表上で問合せは実行できますが、他のアクティビティは実行できません。

使用上の注意
排他ロックによってロックされた表上では、問合せは実行できますが、他のアクティビティは実行できません。
共有ロックによって同時問合せは実行できますが、ロックされた表を更新することはできません。
行の共有ロックによって、ロックされた表への並行アクセスが可能になります。これにより、排他的アクセスのために表全体をロックするのを防ぎます。またROW SHAREとSHARE UPDATE句は同義となります。
行の排他ロックはROW SHAREロックと同じですが、SHAREモードでロックはできません。行の排他ロックは、更新、挿入、削除の実行時に自動的に適用されます。

行の共有排他ロックでは、表全体のロックまたは他のユーザーによる表の行の探索は可能ですが、SHAREモードの表のロックまたは行の更新はできません。
共有の更新ロックはROW SHAREと同義であり、Oracle7 RDBMSの従来のバージョンとの互換性があります。
同じ表に同時に設定できるロックもあります。他のロックは表ごとにひとつだけ設定できます。たとえば、複数ユーザーは同じ表に同時に複数のSHAREロックを設定できますが、EXCLUSIVEロックは同じ表に同時に1つしか設定できません。ロック・モードの相互作用に関する説明は、『Oracle7 Server概要』の「データの同時実行性」を参照してください。

表をロックする場合、他のユーザーによるアクセスを考慮します。ロックされた表は、ユーザー・トランザクションをコミットするか、あるいは全体をロールバックするか、表をロックする前にセーブポイントまでをロールバックするまでロックされています。
ロックしても他のユーザーは表を問い合わせることができます。問合せによって表をロックすることはできません。読込みプログラムは書込みプログラムをロックすることはなく、書込みプログラムが読込みプログラムをロックすることもありません。

例1 次の文は、EMP表を排他モードでロックします。他のユーザーがすでに表をロックしている場合でも、待ち状態にはなりません。

LOCK TABLE emp
IN EXCLUSIVE MODE
NOWAIT

例2 次の文は、データベース・リンクBOSTONを通じてアクセス可能なリモートACCOUNTS表をロックします。

LOCK TABLE accounts@boston
IN SHARE MODE

普通はこれでイイですね
lock table TEST_TBL1 in EXCLUSIVE mode;

2002/08/26

025 SEQUENCE 連番取得

SQL> CREATE SEQUENCE TEST_SEQ INCREMENT BY 1 ORDER;

順序が作成されました。

SQL> SELECT TEST_SEQ.NEXTVAL FROM DUAL;

   NEXTVAL

----------

SQL>  SELECT TEST_SEQ.NEXTVAL FROM DUAL;

   NEXTVAL

----------

トランザクションのロールバックに関係なく、連番を返します。　CREATE SEQUENCE　の細かいオプションはマニュアルを参照。

2002/09/05

026 テーブルへの項目追加

例）テーブル「TEST_TBL1」に、項目名：DATA3　属性CHAR(10) を　追加する場合。

ALTER TABLE TEST_TBL1 ADD(DATA3 CHAR(10))

027 テーブル定義の情報を調べるSQL

select * from all_objects WHERE OBJECT_TYPE='TABLE' AND OBJECT_NAME='TEST_TBL1';

オブジェクト名とコメントを表示する

select * from USER_TAB_COMMENTS

MaterialView も table_type=tableで扱われているようだ。

その他、テーブルのいろいろ
http://park14.wakwak.com/~ky1999/ORA/tables/T_MAIN.htm

2011/05/29

テーブル名、項目名、プライマリーキーなど、すべて列挙するSQL

select 
USER_OBJECTS.OBJECT_TYPE
-- テーブル情報
,USER_TAB_COLS.TABLE_NAME
,USER_TAB_COMMENTS.COMMENTS TABLE_COMMENTS

-- 列情報
,USER_TAB_COLS.COLUMN_NAME
,USER_TAB_COLS.DATA_TYPE
,NVL(USER_TAB_COLS.DATA_PRECISION,USER_TAB_COLS.CHAR_LENGTH) LENGTH
,USER_TAB_COLS.DATA_SCALE
,USER_TAB_COLS.DATA_LENGTH DATA_BYTE 

-- 制約条件
,USER_CONS_COLUMNS.CONSTRAINT_NAME
,USER_CONS_COLUMNS.POSITION
,USER_TAB_COLS.NULLABLE
,NVL2(USER_CONS_COLUMNS.CONSTRAINT_NAME, USER_CONSTRAINTS.CONSTRAINT_TYPE ,'')

-- 列 補足情報
,USER_TAB_COLS.COLUMN_ID
,USER_COL_COMMENTS.COMMENTS COL_COMMENTS
from 
USER_TAB_COLS 
LEFT JOIN USER_TAB_COMMENTS -- テーブル名のコメントを取得する為のもの
ON USER_TAB_COMMENTS.TABLE_NAME = USER_TAB_COLS.TABLE_NAME

LEFT JOIN USER_OBJECTS -- オブジェクトタイプを取得する為のもの
ON USER_OBJECTS.OBJECT_NAME = USER_TAB_COLS.TABLE_NAME

LEFT JOIN USER_COL_COMMENTS -- 列のコメントを取得する為のもの
ON USER_COL_COMMENTS.TABLE_NAME = USER_TAB_COLS.TABLE_NAME
AND USER_COL_COMMENTS.COLUMN_NAME = USER_TAB_COLS.COLUMN_NAME

LEFT JOIN USER_CONSTRAINTS -- テーブルに対する制約の種類を特定する為のもの
ON USER_CONSTRAINTS.TABLE_NAME = USER_TAB_COLS.TABLE_NAME
AND USER_CONSTRAINTS.CONSTRAINT_TYPE = 'P' -- プライマリ制約のみとする

LEFT JOIN USER_CONS_COLUMNS -- 列の制約を取得する為のもの
ON USER_CONS_COLUMNS.CONSTRAINT_NAME = USER_CONSTRAINTS.CONSTRAINT_NAME
AND USER_CONS_COLUMNS.COLUMN_NAME = USER_TAB_COLS.COLUMN_NAME

WHERE 
-- ( USER_CONSTRAINTS.CONSTRAINT_TYPE IS NULL OR USER_CONSTRAINTS.CONSTRAINT_TYPE = 'P') 

-- 削除されているテーブルは、除去する
not exists (select 'X' from RECYCLEBIN where USER_TAB_COLS.TABLE_NAME = RECYCLEBIN.OBJECT_NAME)

-- 不要なオブジェクトは、除外する
AND USER_OBJECTS.OBJECT_TYPE NOT IN ('INDEX PARTITION','INDEX','TABLE SUBPARTITION','TABLE PARTITION')


ORDER BY 
TABLE_NAME
,COLUMN_ID

コメント部分に、改行が入っているばあいは秀丸などで [^"]\n を検索して置き換え

2011/06/02

028 コメント

/* comment */ C言語スタイルでもOK

-- ハイフォン二つで、行コメント

2003/01/11

029 オラクルの項目の型

こちらを参照

030 テーブルを縦に結合

SELECT DATA FROM TEST_TBL

UNION

SELECT DATA FROM TEST_TBL2

UNION ALL　にすると、同じもの合っても集約されない

031 ミリ秒まで取得したい！

ORACLE9からは、SYSTIMESTAMP　という組み込み項目があるようです。

SQL> SELECT SUBSTR(TO_CHAR(systimestamp,'YYYYMMDDHH24MISSFF'),1,17) FROM dual;

SUBSTR(TO_CHAR(SYSTIMESTAMP,'YYYYMMDDHH24MISSFF'),1,17)

-----------------------------------------------------------------------------

20030515143404307

2003/05/15

032 文字列型はバイト指定？文字列指定？

varchar2(10) の10 は、何を設定するのか？

バイト数です！

オラクルマニュアル：組込みデータ型の概要より

VARCHAR2(size)
最大長がsize バイトの可変長文字列。最大サイズは4000、最小サイズは1 です。VARCHAR2 では、size を指定する必要があります。

NVARCHAR2(size)
最大長がsize 文字またはバイト（選択された各国語キャラクタ・セットによる）の可変長文字列。最大サイズは、各文字を保存するのに必要なバイト数によって決定されますが、最大4000 バイトです。NVARCHAR2 では、size を指定する必要があります。

列の最大長は、各国語キャラクタ・セットの定義によって決まります。文字データ型NVARCHAR2 の幅指定では、各国語キャラクタ・セットが固定幅の場合は文字数が参照され、各国語キャラクタ・セットが可変幅の場合はバイト数が参照されます。許容最大列サイズは4000 バイトです。固定幅のマルチバイト・キャラクタ・セットの場合は、列の許容最大長が、4000 バイト以下の文字数になります。
　

CHAR(size)
長さsize バイトの固定長文字データ。最大サイズは2000 バイトです。デフォルトおよび最小サイズは1 です。

2004/05/21

033 削除する時には早い TRUNCATE

テーブルやクラスタを中身のデータを素早く消せる。そのかわり、ROLLBACK できない。一瞬で全件削除してくれる。

TRUNCATE TABLE TEST_TBL2;

2004/09/03

20060425 パーティションについて

CREATE TEBLE文　例

CREATE TABLE TEST (

      ID NUMBER(5) NOT NULL,

      NAME VARCHAR2(100)

PARTITION BY RANGE ( ID )

(     PARTITION TEST_1 VALUES LESS THAN ( 10 ) TABLESPACE SYSTEM,

      PARTITION TEST_2 VALUES LESS THAN ( 20 ) TABLESPACE SYSTEM,

      PARTITION TEST_3 VALUES LESS THAN ( MAXVALUE ) TABLESPACE SYSTEM

);

上記の例では、
ID < 10のとき（IDが9まで)、パーティションTEST_1に値が入る。
10 <= ID < 20 のとき、パーティションTEST_2に値が入る。
20 <= ID のとき、パーティションTEST_3に値が入る。

２.SELECT文

パーティションを指定しない場合。

SELECT * FROM TEST;

上記の場合、すべてのパーティションの値が取れる。

パーティションを指定した場合。

SELECT * FROM TSET PARTITION(TEST_1);

上記の場合、ID < 10 の列が取得される。

条件式を指定した場合。

SELECT * FROM TEST WHERE ID <16;

上記の場合、ID < 16 の列がすべて取得される。

パーティションと条件式を指定した場合。

SELECT * FROM TEST PARTITION(TEST_2) WHERE ID <16;

上記の場合、パーティションTEST_2のID < 16の列が取得される。（10<= ID <16 の値となる）

３．INSERT文

INSERT INTO TEST PARTITION (TEST_1) VALUES (4,'44444');

上記の文は正常実行される。

INSERT INTO TEST PARTITION (TEST_3) VALUES (4,'44444');

上記の文は、IDがパーティション外のため、オラクルがエラーを返す。

４．UPDATE文

UPDATE TEST SET ID=21 WHERE ID = 5;

上記の文では、パーティション・キー列を更新しようとしているため、エラーが返る。

UPDATE TEST SET ID=1 WHERE ID = 5;

上記の文は正常に実行される。

つまり、パーティションが移動するような更新は、できない！

５．テーブルが複数の場合。

以下のようなテーブルを作成する。
　

CREATE TABLE TEST2 ( 

      ID NUMBER(5) NOT NULL,

      NAME VARCHAR2(100)

PARTITION BY RANGE ( ID )

(     PARTITION TEST_1 VALUES LESS THAN ( 10 ) TABLESPACE SYSTEM, 

      PARTITION TEST_2 VALUES LESS THAN ( 25 ) TABLESPACE SYSTEM，

      PARTITION TEST_X VALUES LESS THAN ( MAXVALUE ) TABLESPACE SYSTEM

);

以下のSELECT文を実行する。

SELECT TEST.ID , TEST2.ID FROM TEST, TEST2

WHERE TEST.ID = TEST2.ID;

上記の文では、IDが等しいものの列が取得される。

SELECT TEST.ID, TEST2.ID FROM TEST PARTITION(TEST_1), TEST2

WHERE TEST.ID = TEST2.ID;

上記の文では、TESTのパーティションTEST_1の値と、TEST2の値の等しい列が取得される。

SELECT TEST.ID, TEST2.ID FROM TEST PARTITION(TEST_2), TEST2 PARTITION(TEST_2)

WHERE TEST.ID = TEST2.ID;

上記の文では、TESTのパーティションTEST_2の値と、TEST2パーティションTEST_2の値の等しい列が取得される。
19という値は取れるが、20は取れない。（TESTのパーティションTEST_2は値が10以上、20未満を格納するため）

伊藤殿調査より…

20060605 両方外部結合したい時

以下のようなテーブル「テーブルＡ」、「テーブルＢ」から、「テーブルC」イメージを取得したい時

テーブルＡ		テーブルＢ
１		２
３		４
５		５

テーブルＣ

１

２

３

４

５

両方のどちらかに含まれていれば良いので、両方に（＋）をつければ良いのかと考えた

select

 nvl(test_a.key,test_b.key)

from test_a,test_b

where

 test_a.key(+) = test_b.key(+)

しかし、これはエラーになった

ＳＱＬサーバーなど、テーブル結合子にJoin文を書くと実現できるらしい

select

      nvl(test_a.key,test_b.key)

from

test_a  full outer join test_b

on  test_a.key = test_b.key

両方外部結合する…という意味のようです

オラクルでも、できました

20080116 一括して、連番を振りなおす

既存のレコードに対し、一連番号を振りなおしたい時、以下のようにすれば、１SQLで可能である。

update m_table a

set SubNo = (select count(b.No)+1 from m_table b where b.No < a.No);

例でいうと、SubNoは、あらかじめ、重複しない番号で存在している可能性がある

http://www.atmarkit.co.jp/bbs/phpBB/viewtopic.php?topic=30547&forum=26&5

20080514 追加モレのレコードだけを、追加する。（既にあるものは追加しない）

あるテーブルから、あるテーブルに移送するとき、存在したら更新、なかったら追加という処理は、oracle 9i 以降でサポートされている merge で、できる。

存在したら何もせず、なかったら追加するという処理について、例を示す。

この例では、該当の日付が既に登録済なら、登録しない。。。という意味合いである

test03

日付	時刻	コード
20080501	000001	aaaa
20080501	000002	bbbb
20080501	000003	cccc

test03_moto

日付	時刻	コード
20080501	000004	@@@@
20080501	000001	@@@@
20080502	000001	aaaa
20080502	000002	bbbb

insert into test03

select * from test03_moto where

not exists (select 'x' from test03 where test03.日付 = test03_moto.日付)

実行後の test03

日付	時刻	コード
20080501	000001	aaaa
20080501	000002	bbbb
20080501	000003	cccc
20080502	000002	bbbb
20080502	000001	aaaa

20080502 のレコードだけ、追加されている！

20080529 表結合のいろいろ

http://homepage2.nifty.com/sak/w_sak3/doc/sysbrd/sq_kj04_1.htm

20080616 階層検索

階層情報を展開するSQL、やっぱり、あったですね～

自分のID	自分の名前	親のID
1	自分	2
2	はは	3
3	おばあちゃん
4	妹	2

select LEVEL ,自分のID,自分の名前,親のID,SYS_CONNECT_BY_PATH(trim(自分の名前),'/')

from 階層TBL

start with 親のID is null

connect by prior  自分のID = 親のID

LEVEL	自分のID	自分の名前	親のID	SYS_CONNECT_BY_PATH(TRIM(自分の名前),'/')
1	3	おばあちゃん		/おばあちゃん
2	2	はは	3	/おばあちゃん/はは
3	1	自分	2	/おばあちゃん/はは/自分
3	4	妹	2	/おばあちゃん/はは/妹

自分からみた親を遡って展開したイメージ

詳しい解説

SELECT select_list FROM table_expression [ WHERE ...]

  [ START WITH start_expression ]

    CONNECT BY { PRIOR parent_expr = child_expr |

      child_expr = PRIOR parent_expr }

  [ ORDER SIBLINGS BY column1 [ ASC | DESC ]

      [, column2 [ ASC | DESC ] ] ...

  [ GROUP BY ...]

  [ HAVING ...]

  [ other ...]

http://www.enterprisedb.com/docs/jp/8.3/oracompat/EnterpriseDB_OraCompat_JP_8.3-24.htm

20080618 CHARどうしの比較は空白を埋めてくれるけど、関数を経由するとうまくいかない

KEY	DATA1	DATA2
1	ABCD	A

上記のテーブルに対し

まず、シンプルに判定してみる

SQL> SELECT * FROM TEST01

  2  WHERE

  3  DATA1 = 'ABCD';

       KEY DATA1      DATA2

---------- ---------- ----------

         1 ABCD       A

当然ヒットする

CHAR型なので、DATA１側に空白を追加して、判定してくれているらしく

SQL> SELECT * FROM TEST01

  2  WHERE

  3  DATA1 = 'ABCD               ';

       KEY DATA1      DATA2

---------- ---------- ----------

         1 ABCD       A

当然ヒットする

TRIMで空白を削除してみると

SQL> SELECT * FROM TEST01

  2  WHERE

  3  DATA1 = TRIM('ABCD               ');

レコードが選択されませんでした。

あれ？ヒットしない

空白を埋めて比較してくれるのは、CHAR型どうしの比較の時である。
TRIMの文字列は、CHAR型とは明記されていない。文字列という書き方がされている。

CHAR以外の文字列は、文字数が合っていないと等しいとはみなさないようだ。

解決方法は、空文字を連結して、文字数を合わすか、CAST演算子を用いて、完全にCHAR型にするかだ。

SQL> SELECT * FROM TEST01

  2  WHERE

  3  DATA1 = CAST( TRIM('ABCD               ') AS CHAR(10));

       KEY DATA1      DATA2

---------- ---------- --------------------

         1 ABCD       ABCD

CHAR(10)型に変換してみたら、ヒットした。

20081009 一時表

CREATE GLOBAL TEMPORARY TABLE MYPROJ.実験履歴_S

    ＳＥＱ                         VARCHAR2(10) NOT NULL,

    版数                           NUMBER(3,0) NOT NULL,

    キー                           VARCHAR2(10) NOT NULL,

    値                             VARCHAR2(10)

ON COMMIT DELETE ROWS

ON COMMIT DELETE ROWS ←トランザクションが終了した時に、TRUNCATE される
ON COMMIT PRESERVE ROWS ←セッションが終了した時に、TRUNCATE される 初期値

１次表は、セッションスコープの表である。セッション単位のワークテーブルとして利用価値がある。

利点として、

プライマリーキーにセッションIDを入れておく必要がない。
セッション終了時などに、行削除する手間が不要。

20081212 テーブルに設定さている主キーを知りたい

SELECT * FROM DBA_CONS_COLUMNS

WHERE

TABLE_NAME = 'TBL_TEST'

AND CONSTRAINT_NAME = (SELECT CONSTRAINT_NAME

FROM DBA_CONSTRAINTS

WHERE TABLE_NAME = 'TBL_TEST'

AND CONSTRAINT_TYPE = 'P')

ORDER BY POSITION

デクショナリテーブルを用いると、いろんな情報が参照できる

http://www6.atwiki.jp/sunbalcan/pages/29.html

20090730 SYSDATE SYSTIMESTAMP

SYSDATE の精度は YYYYMMDD HHMMSS である

SYSTIMESTAMPの精度は YYYYMMDD HHMMSS.000である

11g

20100121 テーブルに対する変更SQLまとめ

項目の追加

ALTER TABLE TEST_TBL1 ADD (

   Tuika1 VARCHAR2(10) NULL

COMMENT ON COLUMN TEST_TBL1.Tuika1 IS '追加項目'

項目の削除

項目名の変更

ALTER TABLE TEST_TBL1 RENAME COLUMN TUIKA1 TO TUIKA2

COMMENT ON COLUMN TEST_TBL1.TUIKA2 IS '追加項目2'

属性の変更

ALTER TABLE TEST_TBL1 MODIFY (

   TUIKA2 CHAR(15)

型の変更、バイト数の変更なども可能

NOT NULLの追加

ALTER TABLE TEST_TBL1 MODIFY (

  TUIKA2 NOT NULL

対象の項目にNULLがあると、エラーになる

NOT NULLの削除

ALTER TABLE TEST_TBL1 MODIFY (

   TUIKA2 NULL

項目の削除

ALTER TABLE test_tbl1  DROP COLUMN tuika2

    [CASCADE CONSTRAINT] [INVALIDATE]

主キーの追加

ALTER TABLE TEST_TBL1 ADD CONSTRAINT TEST_TBL1_PK PRIMARY KEY (

        KEY1 ,TUIKA2

主キーに項目を追加するときは、一度ドロップして作成かな？

20100128 選択無しの時は、where 条件を無効にしたい

select key1 from test_tbl1

where  ? is null or key1=?

nullならtrueにしてしまうのがポイントです

複数項目の場合

20101025 HINT文

HINT文とは

ＳＱＬにヒント構文を使用することによって強制的に検索パスを指定することが出来る。
ヒント文は　ＳＥＬＥＣＴ、ＵＰＦＤＡＴＥ、ＤＥＬＥＴＥキーワードの直後に
"/* + ヒント構文 */"を付けることにより使用できる。

select

 /*+ index (test_tbl) */

* from test_tbl

where

key1=100

and

key2=100

参考
http://oracle.na7.info/tuning1.html
http://tom384.ld.infoseek.co.jp/DF001.html

オプティマイザ・ヒントの使用方法
http://download.oracle.com/docs/cd/E16338_01/server.112/b56312/hintsref.htm

http://codezine.jp/article/detail/4847

20101031 ORA-01779

ＶＩＥＷでのＵＰＤＡＴＥを行う時、１レコードに対し複数回更新を行おうとした場合
「ORA-01779 キー保存されていない表にマップする列は変更出来ません。」と表意される。
このエラーを回避にはヒント句の BYPASS_UJVC を使用する。

例

CREATE TABLE TEST_TBL_KEY2

    KEY1                           NUMBER(10,0) NOT NULL,

    KEY2                           NUMBER(10,0) NOT NULL,

    DATA1                          NUMBER,

    CONSTRAINT PK_TEST_TBL_KEY2 PRIMARY KEY (KEY1, KEY2)

COMMENT ON TABLE TEST_TBL_KEY2 IS 'テスト用キーが2個'

CREATE TABLE TEST_TBL_KEY3

    KEY1                           NUMBER(10,0) NOT NULL,

    KEY2                           NUMBER(10,0) NOT NULL,

    KEY3                           NUMBER(10,0) NOT NULL,

    DATA1                          NUMBER,

    CONSTRAINT PK_TEST_TBL_KEY3 PRIMARY KEY (KEY1,KEY2,KEY3)

COMMENT ON TABLE TEST_TBL_KEY3 IS 'テスト用キーが3個'

UPDATE

SELECT

    /*+ BYPASS_UJVC */

     TEST_TBL_KEY2.DATA1 NEW_DATA

    ,TEST_TBL_KEY3.DATA1 OLD_DATA

FROM

    TEST_TBL_KEY2,TEST_TBL_KEY3

WHERE

    TEST_TBL_KEY2.KEY1 = TEST_TBL_KEY3.KEY1

SET

OLD_DATA = NEW_DATA

戻る

document.write(document.title); //タイトルの表示

テーブル名、項目名、プライマリーキーなど、すべて列挙するSQL

CREATE TEBLE文 例

２.SELECT文

３．INSERT文

４．UPDATE文

５．テーブルが複数の場合。

項目の追加

項目の削除

項目名の変更

属性の変更

NOT NULLの追加

NOT NULLの削除

項目の削除

主キーの追加

CREATE TEBLE文　例