NoteBook > Oracle Database Tips
Oracle Database Tips
主に、OracleのSql*Plusの使用方法および、Oracle特有のコマンドやSQLについての情報。
オラクルデータベースについての最新の情報や、詳細な情報は下記の公式ページで参照できます。
Oracle Database
Oracle マニュアル
テーブル操作関連
テーブルのコピー
CREATE TABLE 新テーブル名 AS SELECT * FROM 旧テーブル名テーブルのコピー(データなし）
CREATE TABLE 新テーブル名 AS SELECT * FROM 旧テーブル名 WHERE 1=2テーブル名の変更
RENAME 旧テーブル名 TO 新テーブル名テーブルに列を追加（VARCHAR(10)の列を追加）
ALTER TABLE テーブル名 ADD 列名 VARCHAR(10);テーブルの列定義を変更（VARCHAR(20)に変更）
ALTER TABLE テーブル名 MODIFY 列名 VARCHAR(20);テーブルの列を削除(8i以降)
ALTER TABLE テーブル名 DROP COLUMN 列名；※データがあると消せない場合は先に対象の列の内容をnullにアップデートしてください。
※テーブルの列が１つしかない場合は削除できません。
テーブルの削除（参照整合性制約も同時に削除）
DROP TABLE テーブル名 CASCADE CONSTRAINTS;行番号で検索する
検索結果に行番号を振る
SELECT ROWNUM , 列名 FROM テーブル名;ソート後の検索結果に行番号を振る
SELECT ROWNUM , 列名 FROM (SELECT 列名 FROM テーブル名 ORDER BY 列名);最初のｎ件を取り出す（最初の3件のデータを取り出す）
SELECT 列名 FROM テーブル名 WHERE ROWNUM <= 3;11件目から20件目までを取り出す
SELECT 列名 FROM テーブル名 WHERE ROWNUM <= 11 AND ROWNUM >= 20;DB管理関連
ユーザー一覧の取得
SELECT * FROM ALL_USERS;テーブル一覧の取得
SELECT * FROM ALL_TABLES;
又は
SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'TABLE';該当ユーザーのテーブルのみ取得する場合（SELECT * FROM TAB の代わり）
SELECT TABLE_NAME FROM USER_TABLES;
又は
SELECT * FROM ALL_TABLES WHERE OWNER = 'ユーザー名';テーブルの情報を取得する
DESC テーブル名テーブルの情報を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で取得する。
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インデックスの一覧の取得
SELECT INDEX_NAME,TABLE_NAME FROM USER_INDEXES;関数一覧
| 関数 | 書式 | 意味 |
|---|---|---|
| LENGTH | LENGTH(文字列) | 文字列の桁数を返す |
| SUBSTR | SUBSTR(文字列, 開始位置, 文字数) | 文字列の開始位置桁目から文字数分抽出した値を返す |
| REPLACE | REPLACE(文字列, 検索文字列, 置換文字列) | 文字列のうち検索文字列に該当する文字を置換文字列に置き換えて返す |
| LPAD | LPAD(文字列, 桁数, 埋込文字) | 文字列が桁数に満たない場合、埋込文字を先頭に埋めて返す |
| RPAD | RPAD(文字列, 桁数, 埋込文字) | 文字列が桁数に満たない場合、埋込文字を後尾に埋めて返す |
| INITCAP | INITCAP(項目名) | 文字列の先頭を大文字に変換して返す |
| LOWER | LOWER(項目名) | 文字列を小文字に変換して返す |
| UPPER | UPPER(項目名) | 文字列を大文字に変換して返す |
| DECORD | DECORD(項目名, 比較値, 変換値) | 項目名の値が比較値と等しい場合に変換値に変換して返す |
| INSTR | INSTR('文字列１','文字列２', 検索開始位置, 回数) | 文字列１の検索開始位置から文字列２が回数分出現した位置を返す |
| TO_NUMBER | TO_NUMBER(項目名, '変換書式') | 項目名の値を変換書式に則って変換した数値を返す |
| TO_CHAR | TO_CHAR(項目名, '変換書式') | 項目名の値を変換書式に則って変換した文字列を返す |
| TO_DATE | TO_CHAR(項目名, '変換書式') | 項目名の値を変換書式に則って変換した日付を返す |
日付と時刻
現在時刻の取得（DATE型）
SELECT SYSDATE FROM DUAL;DATE型の日時の表示形式の変更
ALTER SESSION SET NLS_DATE_FORMAT = 'YY-MM-DD HH24:MI:SS';和暦年の取得方法
TO_CHAR(sysdate ,'E.YY.MM.DD','nls_calendar=''Japanese Imperial''')日時の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 | 前後に空白を付けずに返す | |
ＳＱＬスクリプト サンプル
実行結果を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