トップ 差分 一覧 ソース 置換 検索 ヘルプ PDF RSS ログイン

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;
-- テーブルの列が1つしかない場合はできない。
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

 関数一覧

関数 書式 意味
LENGTH LENGTH([文字列]) 文字列の桁数を返す
SUBSTR SUBSTR([文字列], [開始位置], [文字数]) 文字列の開始位置桁目から文字数分抽出した値を返す
REPLACE REPLACE([文字列], [検索文字列], [置換文字列]) 文字列のうち検索文字列に該当する文字を置換文字列に置き換えて返す
LPAD LPAD([文字列], [桁数], [埋込文字]) 文字列が桁数に満たない場合、埋込文字を先頭に埋めて返す
RPAD RPAD([文字列], [桁数], [埋込文字]) 文字列が桁数に満たない場合、埋込文字を後尾に埋めて返す
INITCAP INITCAP([項目名]) 文字列の先頭を大文字に変換して返す
LOWER LOWER([項目名]) 文字列を小文字に変換して返す
UPPER UPPER([項目名]) 文字列を大文字に変換して返す
DECORD DECORD([項目名], [比較値], [変換値]) 項目名の値が比較値と等しい場合に変換値に変換して返す
INSTR INSTR('[文字列1]','[文字列2]', [検索開始位置], [回数]) 文字列1の検索開始位置から文字列2が回数分出現した位置を返す
TO_NUMBER TO_NUMBER([項目名], '[変換書式]') 項目名の値を変換書式に則って変換した数値を返す
TO_CHAR TO_CHAR([項目名], '[変換書式]') 項目名の値を変換書式に則って変換した文字列を返す
TO_DATE TO_CHAR([項目名], '[変換書式]') 項目名の値を変換書式に則って変換した日付を返す

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


和暦年の取得方法

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

 SQLスクリプト

実行結果を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



[カテゴリ: データベース > Oracle]



  • Hatenaブックマークに追加
  • livedoorクリップに追加
  • del.icio.usに追加
  • FC2ブックマークに追加

最終更新時間:2009年02月04日 23時20分13秒