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 n日後の日付の求め方
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 テーブル情報取得

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

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の項目の注意

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

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


008 SUBSTR

SUBSTR ( char , m [ , n] )

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

が省略されたとき、終わりの文字までを返す。
が0以下のとき、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 n日後の日付の求め方

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

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

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

 

ちなみに、1ヶ月後の日付は、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')

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

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

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
もし、注文番号だけで検索した場合は、インデックスは使われない。営業所だけで検索した場合はインデックスが使用される。


020 テーブルどうしの関連を設定する方法

アクセスなどでは、テーブル間のリレーションを宣言することができるが、Oracleにも当然同じような機能があるようです。

例)

売上マスタに、
「売上マスタの顧客コードは、顧客マスタの顧客コードの外部キーである」
として宣言します。

CREATE TABLE 売上マスタ (
  伝票No      char(4) , 
  顧客コード  char(3) ,
  金額       NUMBER(7) , 
 
    CONSTRAINT 売上マスタ参照1
      FOREIGN KEY (顧客コード) REFERENCES 顧客マスタ (顧客コード) , 
    CONSTRAINT 売上マスタPK_key
      PRIMARY KEY ( 伝票No ) ;

 2001/12/17


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

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

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

 2002/01/22


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

SYSDATEも同様と思われる。

2002/03/14


022 一番簡単なバックアップ

 
CREATE TABLE BACKUP_TBL AS SELECT * FROM SOUCE_TBL;

SOUCE_TBLの全項目、全レコードを、BACKUP_TBLを新たに作成し、複写する。

ということは、ASの右側に書いた内容により、なんでも出来るわけだ。項目を選択したり、レコードを指定したり。

ちなみに、プライマリキー情報などは複製されません。項目単位の情報だけです。

2002/03/14


戻し方

 
INSERT INTO SOUCE_TBL SELECT * FROM BACKUP_TBL

2002/03/20


023 IF文もどき  DECODE 式

構文 DECODE(比較対照,比較式1,比較1が正の場合の戻り値
                , 比較式2,比較2が正の場合の戻り値
                ,合致しない時の戻り値 )

 
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 SHARESHARE 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
----------
         1
 
SQL>  SELECT TEST_SEQ.NEXTVAL FROM DUAL;
 
   NEXTVAL
----------
         2

トランザクションのロールバックに関係なく、連番を返します。 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
のとき(ID9まで)、パーティション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_2ID < 16の列が取得される。(10<= ID <16 の値となる)

3.INSERT

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

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

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

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

4.UPDATE

 
UPDATE TEST SET ID=21 WHERE ID = 5;

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

 
UPDATE TEST SET ID=1 WHERE ID = 5;

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

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

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 両方外部結合したい時

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

テーブルA

 

テーブルB

 

 

 

 

テーブルC

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

select 
 nvl(test_a.key,test_b.key)
from test_a,test_b 
where
 test_a.key(+) = test_b.key(+)

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

SQLサーバーなど、テーブル結合子に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 一括して、連番を振りなおす

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

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型なので、DATA1側に空白を追加して、判定してくれているらしく

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
(
    SEQ                         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 される 初期値

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

利点として、

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

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文とは

SQLにヒント構文を使用することによって強制的に検索パスを 指定することが出来る。
ヒント文は SELECT、UPFDATE、DELETEキーワードの 直後に
"/* +
ヒント構文 */"を付けることにより使用できる。

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

VIEWでのUPDATEを行う時、1レコードに対し複数回更新を行おうとした場合
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
/

 

 


戻る

inserted by FC2 system