- テーブル操作関連
- テーブルのコピー
- テーブルのコピー(データなし)
- テーブル名の変更
- テーブルに列を追加(VARCHAR(20)の列を追加)
- テーブルの列定義を変更(VARCHAR(10)に変更)
- テーブルの列を削除(8i以降)
- ユーザー一覧の取得
- テーブル一覧の取得
- テーブルの情報を取得する(DESC [テーブル名]の代わり)
- SQL*PLUSで取得する(接続しているユーザーのテーブルのみ)
- SQLのSELECTで取得する(DESC [テーブル名]の代わり)
- テーブルの制約を取得する。
- テーブルのCREATE文取得(9I限定)
- テーブルが使用している領域のサイズの取得
- 関数一覧
- 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]
[通知用URL]
Tweet
最終更新時間:2009年02月04日 23時20分13秒