Oracle の基本的なSQL

http://www.full-so.com/notebook/oracle.html

 テーブル操作関連

テーブルのコピー

CREATE TABLE [新テーブル名]
AS SELECT * FROM [旧テーブル名]

テーブルのコピー(データなし）

CREATE TABLE [新テーブル名]
AS SELECT * FROM [旧テーブル名] WHERE 1=2

テーブル名の変更

RENAME [旧テーブル名] TO [新テーブル名]

テーブルに列を追加（VARCHAR(20)の列を追加）

ALTER TABLE [テーブル名] ADD [列名] VARCHAR(10);

テーブルの列定義を変更（VARCHAR(10)に変更）

ALTER TABLE [テーブル名] MODIFY [列名] VARCHAR(20);

テーブルの列を削除(8i以降)

-- データがあると消せない場合すべてNULLに変更
UPDATE [テーブル名] SET [列名] = NULL;
COMMIT;
-- テーブルの列が１つしかない場合はできない。
ALTER TABLE [テーブル名] DROP COLUMN [列名]； 

 ユーザー一覧の取得

SELECT * FROM ALL_USERS;

 テーブル一覧の取得

SELECT * FROM ALL_TABLES;

または

SELECT *
FROM   ALL_OBJECTS 
WHERE  OBJECT_TYPE = 'TABLE';

該当ユーザーのみ取得する場合（SELECT * FROM TAB の代わり）

SELECT *
FROM   ALL_TABLES
WHERE  OWNER = '[ユーザー名]';

 テーブルの情報を取得する（DESC [テーブル名]の代わり）

SQL*PLUSで取得する(接続しているユーザーのテーブルのみ）

DESC [テーブル名]

SQLのSELECTで取得する（DESC [テーブル名]の代わり）

SELECT COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,
       DATA_SCALE,NULLABLE,CHARACTER_SET_NAME,CHAR_COL_DECL_LENGTH
FROM   ALL_TAB_COLUMNS 
WHERE  OWNER      = '[ユーザー名]'
AND    TABLE_NAME = '[テーブル名]'
ORDER  BY COLUMN_ID

テーブルの制約を取得する。

SELECT A.*,B.COLUMN_NAME,B.POSITION
FROM   ALL_CONSTRAINTS A,
       ALL_CONS_COLUMNS B
WHERE  A.TABLE_NAME      = B.TABLE_NAME(+)
AND    A.CONSTRAINT_NAME = B.CONSTRAINT_NAME(+)
AND    A.TABLE_NAME      = '[テーブル名]'

CONSTRAINT_TYPEの意味
C（表でのチェック制約） 
P（主キー） 
U（一意のキー） 
R（参照整合性） 
V（ビューでのチェック・オプション付き） 
O（ビューで読取り専用）

テーブルのCREATE文取得(9I限定)

SELECT DBMS_METADATA.GET_DDL('TABLE', '[テーブル名]') FROM DUAL

 テーブルが使用している領域のサイズの取得

SELECT SEGMENT_NAME, TO_CHAR(SUM(BYTES),'999,999,999') BYTES 
FROM USER_SEGMENTS 
GROUP BY SEGMENT_NAME

 関数一覧

TO_DATE,TO_CHAR,TO_NUMBERで指定可能な書式文字

書式文字	意味
SS	秒	０〜５９の範囲
SSSSS	真夜中からの経過秒数	０〜８６３９９の範囲
MI	分	０〜５９の範囲
HH	12時間表記	０〜１２の範囲
HH24	24時間表記	０〜２３の範囲
DD	日付	１〜３１の範囲
DAY	曜日英語	SUNDAY〜SATURDAYの範囲
D	曜日数字	１〜７の範囲
DDD	年間日付	１〜３６６の範囲
MM	月	１〜１２の範囲
MON	月英略	JAN〜DECの範囲
MONTH	月英語	JANUARY〜DECEMBERの範囲
YY	西暦下２桁	１９９９年の場合９９
YYYY	西暦	１９９９年の場合１９９９
YEAR	完全年
CC	世紀	１９９９年の場合２０
Q	４半期	１〜４の範囲
J	ユリウス日付
W	月の週	１〜５の範囲
WW	年の週	１〜５２の範囲
,(カンマ)	指定した位置にカンマを返す
.(ピリオド)	指定した位置に小数点を返す(書式の中で1個のみ指定)
0	先行0を返します
9	正の値は空白を埋め込み、負の値は(-)を先頭に埋め込み、桁数固定で返す。
S	負の値は(-)を、正の値は(+)を前に付ける。(Sが先頭の場合)
FM	前後に空白を付けずに返す

和暦年の取得方法

TO_CHAR(sysdate ,'E.YY.MM.DD','nls_calendar=''Japanese Imperial''')

 ＳＱＬスクリプト

実行結果をSPOOLでログに出力するSQLファイルをバッチファイルで実行する例

起動するDOSバッチファイル

SQLPLUS [ユーザーID]/[パスワード]@[接続先] [SQLファイル] > [ログファイル]

日付を取得するSQLファイル

-- SQL*PLUSの日付の表示書式設定方法
ALTER SESSION SET NLS_DATE_FORMAT='YYYY/MM/DD HH24:MI:SS';
-- 一行の表示サイズを指定
SET LINESIZE 1000;
-- ヘッダーの表示間隔（行数）
SET PAGESIZE 1000;
SET LONG 10000;
-- 出力結果の右スペースを削除して出力する場合指定
SET TRIMSPOOL ON;
-- SELECT結果をカンマ区切りで出力する場合指定
SET COLSEP ',';
-- オートコミットしない。
SET AUTOCOMMIT OFF;
-- NULLを'NULL'と表示する。
SET NULL 'NULL';
-- SQLもSPOOL出力する場合指定
SET ECHO ON;
SPOOL SPOOL.LOG;

-- 日付を表示
SELECT SYSDATE FROM DUAL;

SPOOL OFF;
-- SQL*PLUSを終了
EXIT