複合キー

複合キー
・複合キーの一部の列に対してNULLを入れることはできない。
・一部のキー列に対して、新規に索引をつけることはできる。

SYNONYM

『プライベート・シノニム』と『パブリック・シノニム』が存在する。

●プライベート・シノニム
・スキーマ内でのみ有効なシノニム。
・ユーザが作成し、そのユーザ自身が使う。

●パブリック・シノニム
・oracleデータベース全体で有効なシノニム。
・DBAユーザもしくはcreate public synonym権限を持つユーザが作成できる。
・シノニムのもととなるオブジェクトの利用権限を持つユーザが利用できる。
※スキーマ指定は不要。オブジェクトの所有者を隠すことができる。

※create public synonym権限はシステム権限
オブジェクト所有者は所有するオブジェクトのすべての権限を持ってる
取り消しはできない。

●ビューとの違い
所有者以外のビューへのアクセスはビューに対するオブジェト権限が必要。
シノニムの場合、基礎表へのアクセス権限が必要。

NULLIF

引数は2つ
第１引数=第２引数が同じ場合：nullを返す。
第１引数<>第２引数の場合：第一引数値を返す。
第１引数がnullの場合、エラーとなる。

select nullif(1,1) ansa, nullif(1,2) ansb from dual;

ansa : null
ansb : 1

DECODE

・ORACLE独自のCASE WHEN
※もちろんSQL標準のCASE WHENも存在する。
select decode(3,1,'a1',2,'a2','default') from dual;
上記の場合、答えはdefault

ちなみにdefaultが定義されていない場合、答えはnullとなる。

各種データ型の特徴

・date, long,clob型
桁数を指定できない。

MAX、MIN

・グループ関数の一つ。詳細は略。

Select age, max(*) from student;
一見うまくいきそうだが、エラーになる。
『グループ関数と他の列は指定できない。』という掟がある。
※上記の場合、group byでageを指定すればうまくいく。が期待結果は異なるな。

グループ関数同士であれば、正常に処理され、SQL結果が出力される。
Select min(*), max(*) from student;

NULLS FIRST, NULLS LAST

・ソートがかかるとき、NULL値は『最大値』として扱われる。
・『NULLS FIRST』『NULLS LAST』で明示的にソート時のNULL値の出力結果を変えることができる。

order by emp_no asc nulls first

DEFAULT

【概要】
表作成時に指定する。（表作成後につけることも可能)

DEFAULTには、リテラル、式、SQL関数などを使用することができる。
※他の列名や擬似列は使用できない。

e.x.)
create table emp
(
empno varchar2(10) not null,
registdate date default sysdate
)


【DEFAULT値を指定してDMLを実行する】
INSERT、UPDATE時に明示的に指定することができる。

E.X.)
※UPDATE
update テーブル set 項目 = default;
※INSERT
INSERT INTO USER_MASTER (
USER_ID, DEPT_NO, USER_NAME, CREATED_ON, MODIFIED_ON
) VALUES (
'0020', '1001',
'小泉 純一',
DEFAULT,
NULL
) ;

副問い合わせ

・GROUP BY句では副問い合わせできない。
※イメージ沸かないし。
GROUP BY句以外なら、どの句でもできる。

※『複数列副問合せ』というのがある。
Select deptno,ename,Sal from emp
where (deptno,Sal) in
(select deptno,max(Sal) from emp group by dept no);

With check option

VIEWの定義時に指定することができる。
ビューに対するupdate, insert文が検査される。
条件に合わない場合は、エラーが生じて操作は拒否される。

e.x.)
create view sales_employee
as
select emp_id,emp_fname,emp_place,dept_id
from employee
where dept_id = 200
with check option;

VIEWからVIEWを作成することもできるのだが、もちろんwith check optionの内容は
継承される。

with read only

VIEWの定義時に指定する。
VIEWを通じての操作は、読み取りだけ。(読み取り専用)

e.x.)
create view emp_view
as select detno,count() cnt from emp
group by deptno
with read only.

INSERT

・INSERT文では通常、列を指定するが、指定しない場合は『全部の値』を指定する必要がある。

e.x.テーブル構成が以下の場合
empno number(4)
ename varchar2(15)
job. varchar2(10)
deptno. Number(2)

以下のSQLはエラーとなる。全部の列の値を指定しなければならない。
Insert into emp values(1000,'scott','clerk')

TRUNCATE

・表の所有者かdrop any table権限を持っている必要がある。

DELETEとの違い
・DDLなのでロールバック不可
・データの領域を解放する。
・削除トリガーを起動しない。
・参照整合性制約の親表は削除不可。

TRANSACTION

●TRANSACTIONの終了条件
・commitの実行
・rollbackの実行
・DDL文(create,alter,truncate,dropなど)の実行(暗黙のコミット)
・DCL文(GRANT,REVOKEなど)の実行(暗黙のコミット)
・SQL*Plus SQL Developerの終了(暗黙のコミット)
・マシン障害やシステムクラッシュの発生

FOREIGN KEY

・親キーは主キーか一意キーであること。

集合演算子

・Union intersect minus
いずれも重複行は除外する。

・union all
例外で重複値もそのまま表示する。デフォルトでソートはされない。

なぜかというとUnion intersect minusは重複除外するために必然的にソートしてしまうのだ。

Purge

10gからゴミ箱機能が追加された。
ゴミ箱に捨ててから取り出すことができる。(※フラッシュバックテーブル機能)
ゴミ箱に入れずに直接削除したい場合、『purge』をつける。
drop table hoge purge;

USING

usingは自然結合と異なる部分がある。
・結合列の名前は同じだが、データ型が異なる場合に使用する。
※自然結合でやるとエラーになってしまう。
・複数の列名が一致するが、その一部だけを結合列として使用する。

SEQUENCE

【構文】
create sequence 順序名
[start with 開始番号]
[increment by 増分値]
[minvalue 最小値 | nominvalue]
[maxvalue 最大値 | nomaxvalue]
[cache キャッシュ数 | nocachr]
[cycle | nocycle];

【留意事項】
・sequenceは、順序名以外はデフォルト設定があるため、順序名だけでも作成できる。
E.X.)
create sequence seq_no1;
※Startwithのデフォルト値は1。なので一回目のnextvalの値は2。

【留意事項(その2)】
・順序の変更は順序の所有者またはALTER権限を持つユーザができる。
・ALTER SEQUENCE文で変更した値は、次の生成時から有効
・START WITHオプションは変更できない。
・現在の採番済みの順序番号よりも小さいMAXVALUEに変更することはできない。

※cacheはパフォーマンスに影響するらしいが、bronzeでは、細かく問われないので無視する。

order by句

order by句は絶対に最後。

集合演算子の場合も、selectが二つあるが、一番最後にしか記述できない。
※副問い合わせの場合は副問い合わせ一つでSQLが完結してるので記述可能。

having句

Having句はgroup byの前後どちらでも構わない。
またhaving句はgroup byがなくても使用できる。

列別名

【列別名の構文】
・列名の後に続けて空白後、列別名を記述する。
select sysdate today from dual;

・長いSQL構文の場合、わかりにくくなるため、ASをつけてもよい。
select sysdate as today from dual;

・列別名の命名規則に違反した特殊記号や空白、先頭が数字で始まる名前を使用したい場合
select sysdate "Hello World" from dual;
select sysdate as "Hello World" from dual;
※二重引用符であることに気をつける！！


留意事項）
・列別名はhaving, where ,group by では指定できない。
理由）
from句、where句、group by句、having句、select句、order by句の順に評価される。
なので列別名はorder byでしか指定できない。
※しかし、副問合わせで生成されたテーブルの場合は、主問い合わせでは指定できる。

留意事項(その2)
表結合時、列名を表名で修飾するが、1つの表にしかない列も修飾することで、
どの表から取り出すかを解析する負荷が軽減されるため、パフォーマンスがアップする。