A Oracle DB SQL - user000422/0 GitHub Wiki

基本

クォーテーション	用途
シングル	文字リテラル（INSERTのVALUEの文字列など）
ダブル	列別名（AS）オブジェクト名

-- ■SELECT
-- LIKE
SELECT * FROM sample_table WHERE sample_col LIKE 'A%';
SELECT * FROM sample_table WHERE sample_col LIKE '_A%'; -- _ 2文字目に指定文字があること

-- DISTINCT 重複データの除外
SELECT DISTINCT sample_col FROM sample_table;
SELECT DISTINCT sample_col, sample_col2 FROM sample_table; -- 組み合わせの重複データの除外

-- TRUNCATE 表の全てのレコードを削除
-- DELETEより高速
-- DDLのためロールバック不可
TRUNCATE TABLE sample_table;

-- ■JOIN
-- 結合処理で最も多く使われるのは内部結合

-- JOIN 内部結合
SELECT * FROM sample_table_a JOIN sample_table_b
  ON sample_table_a.sample_col = sample_table_b.sample_col;

-- LEFT OUTER JOIN 外部結合（左）
-- 結合条件（ON）を満たしていないデータも表示する
SELECT * FROM sample_table_a LEFT OUTER JOIN sample_table_b
  ON sample_table_a.sample_col = sample_table_b.sample_col;

-- UNION ALL
-- ソートされない

ROWNUM … 検索結果上限指定

SELECT * FROM sample_table WHERE ROWNUM <= 1

関数

-- SYSDATE Oracleが動作するサーバーのOSの現在日時（クライアント側ではない）
SELECT SYSDATE FROM dual;

-- SYSDATE WHERE句に利用例
SELECT * FROM sample_table WHERE sample_time > SYSDATE;

-- TO_DATE 文字型を日時型に変換
INSERT INTO sample_table(sample_col) 
  VALUES(TO_DATE('2023/01/01 00:00:00', 'YYYY/MM/DD HH244:MI:SS'));

-- TO_DATE 文字型を日時型に変換 日時形式指定なし（NLS_DATE_FORMATに依存）
INSERT INTO sample_table(sample_col) 
  VALUES(TO_DATE('2023/01/01 00:00:00'));

-- TO_TIMESTAMP 文字型をTIMESTAMP型に変換

-- TO_CHAR 数値型または日時型を文字型に変換
SELECT TO_CHAR(sample_date, 'YYYY/MM/DD') AS sampe_date FROM sample_table; -- 日時型

-- CONCAT 文字列結合
SELECT CONCAT(sample_col, 'sample_word') FROM sample_table; -- カラムの値と文字列を結合
SELECT CONCAT(sample_col, sample_name) FROM sample_table; -- カラム同士を結合
SELECT * FROM ORDER BY CONCAT(sample_col, sample_name) ASC; -- ORDER BY

-- SUBSTR 文字抜き出し
SELECT SUBSTR(sample_col, 2, 4) FROM sample_table; -- 対象, 何文字目から, 抜き出し文字数

-- TRIM 文字列前後の指定文字を削除
SELECT TRIM(sample_col) FROM sample_table; -- 文字指定なしの場合は「半角スペース」が削除される

-- NVL NULLを別の値に変換
SELECT NVL(sample_col, 'sample_val') FROM sample_table;

-- NVL2 NULLの場合とNULLではない場合で値を変換

-- CURRENT_TIMESTAMP システム時刻取得
SELECT CURRENT_TIMESTAMP FROM dual;

-- REGEXP_LIKE 正規表現
SELECT * FROM sample_table WHERE REGEXP_LIKE(sample_id, '[^0-9]'); -- 数値以外を条件に

-- COALESCE カラムnullの場合、第二引数の値を設定
-- 第一引数:カラム、第二引数:nullだった場合の値
SELECT COALESCS (sample_id, 1) FROM sample_table;

データディクショナリビュー

動的パフォーマンスビュー … 「V$」で始まり、OPEN状態以外でも参照可能

-- V$VERSION バージョン確認
SELECT BANNER_FULL FROM V$VERSION; -- BANNER_FULL バージョン確認

-- V$SQL 実行されたSQL
SELECT * FROM V$SQL;
SELECT * FROM V$SQL ORDER BY FIRST_LOAD_TIME DESC; -- 並び替え（作成時刻）

-- DBA_TABLESPACES 表領域の情報（表領域名、表領域ステータス、表領域内容）
SELECT * FROM DBA_TABLESPACES;

-- DBA_SYS_PRIVS 付与されているシステム権限（ユーザやロールに対する）
-- PRIVILEGE 付与権限
SELECT * FROM DBA_SYS_PRIVS;
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'sample_user'; -- ユーザ指定

-- DBA_TAB_PRIVS 付与されているオブジェクト権限（ユーザやロールに対する）
-- PRIVILEGE 付与権限
-- TABLE_NAME オブジェクト名
-- GRANTOR 権限を付与したユーザー
SELECT PRIVILEGE, TABLE_NAME, GRANTOR FROM DBA_TAB_PRIVS WHERE GRANTEE = 'sample_user'; -- ユーザ指定

-- DBA_ROLE_PRIVS 付与されているロール情報
-- GRANTED_ROLE 付与権限
SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'sample_user';

-- DBA_FREE_SPACE データファイルの空き領域
SELECT * FROM DBA_FREE_SPACE;

-- DBA_USERS ユーザー（スキーマ）情報
-- username ユーザ（スキーマ）名
SELECT * FROM DBA_USERS;
SELECT username FROM DBA_USERS;

-- V$CONTROLFILE 制御ファイルのフルパス
SELECT name FROM V$CONTROLFILE; -- name

-- EM Express ポート番号取得
SELECT dbms_xdb_config.gethttpsport FROM DUAL;

-- DBA_DATAPUMP_JOBS DataPumpジョブ
SELECT * FROM DBA_DATAPUMP_JOBS;

-- V$process 実行中プロセス数
SELECT COUNT(*) FROM V$process;

権限

-- GRANT 権限を付与
GRANT CREATE TABLE TO sample_user; -- CREATE TABLE 表作成権限付与
GRANT DBA TO sample_user; -- ロールを付与
GRANT CREATE SESSION, CREATE TABLE TO sample_user; -- 複数 ※未検証

-- WITH ADMIN OPTION 他のユーザへの付与（削除）する権限も同時に付与
GRANT CREATE TABLE TO sample_user WITH ADMIN OPTION; -- CREATE TABLE 表作成権限付与

-- REVOKE 権限を削除
REVOKE DBA FROM sample_user; -- ロールを削除

INSERT

-- 連番 INSERT時に連番を振る
INSERT INTO sample_table(sample_col) VALUES(sample_seq.nextval);

-- INSERT ALL 複数同時にINSERT
-- 他のDBMSのようにVALUESに複数はできない
INSERT ALL
INTO sample_table(sample_col) VALUES('red')
INTO sample_table(sample_col) VALUES('blue')
SELECT * FROM DUAL;

TABLE

-- CREATE TABLE
CREATE TABLE sample_table(
  id   NUMBER(4) CONSTRAINTS pk_sample PRIMARY KEY --主キー制約
  name varchar2(255)
)
ROW STORE COMPRESS BASIC; -- 表圧縮適用

-- テーブルをコピー
CREATE TABLE to_table AS SELECT * FROM from_table;

-- ALTER
-- ADD カラム追加
ALTER TABLE sample_table ADD (sample_col CHAR(10));

-- ADD CONSTRAINT 制約定義（CONSTRAINT:制約名）
ALTER TABLE sample_table ADD CONSTRAINT sample_pk PRIMARY KEY(sample_id);

-- DROP カラム削除（カッコが必須）
ALTER TABLE sample_table DROP (sample_col);

ALTER

ALTER SYSTEM 初期化パラメータ変更

-- 基本型
ALTER SYSTEM SET db_cache_size = 150M;

-- SCOPE指定 BOTH 稼働中インスタンスとSPFILE両方に適用される
-- BOTHと省略は同じ扱い
ALTER SYSTEM SET db_cache_size = 150M SCOPE = BOTH;
ALTER SYSTEM SET db_cache_size = 150M;

FLASHBACK（フラッシュバック）

-- DROP TABLE を取り消す
FLASHBACK TABLE sample_table TO BEFORE DROP;

ユーザー

-- ユーザー作成
CREATE USER sample_user
  IDENTIFIED BY samplepass      -- パスワード
  DEFAULT TABLESPACE sample_tbs -- デフォルト表領域
  TEMPORARY TABLESPACE temp     -- デフォルト一時領域
;

-- ユーザーの削除
-- オブジェクトを所持しているユーザーを削除する場合は、`CASCADE`オプションが必要
DROP USER sample_user CASCADE;

-- ALTER USER（IDENTIFIED）パスワードの変更
ALTER USER sample_user IDENTIFIED BY new_password;

-- ALTER USER（ユーザ名以外の属性を変更可能）
-- ロック
ALTER USER sample_user ACCOUNT LOCK;   -- ロック
ALTER USER sample_user ACCOUNT UNLOCK; -- アンロック

表領域（TABLESPACE）

-- CREATE TABLESPACE 作成
CREATE TABLESPACE sample_tablespace
DATAFILE '$ORACLE_BASE/sample_path/sample_tablespace.dbf' -- データファイル（既存ファイルと同様に配置推奨）
SIZE 100M -- データファイルサイズ
AUTOEXTEND ON -- 自動拡張
NEXT 100M -- 自動拡張時追加サイズ
MAXSIZE UNLIMITED -- 最大サイズ
SEGMENT SPACE MANAGEMENT AUTO -- セグメント領域管理方式（初期値「AUTO」）
;

-- 既存のデータファイルを手動で拡張
ALTER DATABASE DATAFILE 'sample_path/sample_tablespace.dbf'
RESIZE 1G; -- 拡張サイズ

-- 既存のデータファイルに自動拡張設定
ALTER DATABASE DATAFILE 'sample_path/sample_tablespace.dbf'
AUTOEXTEND ON
NEXT 512k MAXSIZE 500M;

-- TABLESPACE 削除
ALTER TABLESPACE sample_tablespace OFLINE; -- 表領域がアクティブの場合、オフラインに
DROP TABLESPACE sample_tablespace INCLUDING CONTENTS; -- INCLUDING CONTENTS 格納されているオブジェクトごと削除

-- ユーザのデフォルトTABLESPACE変更
ALTER USER sample_user DEFAULT TABLESPACE sample_tablespace;

ビュー

-- 作成
CREATE VIEW sample_view
  AS SELECT sample_col FROM sample_table WHERE sample_col > 10
  WITH CHECK OPTION -- DML実行時にビューの「WHERE」を満たしているか検査 ※省略可
;

-- SELECT 参照
SELECT * FROM sample_view;

-- INSERT（ビューにINSERTすることが可能 参照先のテーブルに反映される）
INSERT sample_view(sample_col) VALUE(100);

ディレクトリオブジェクト

-- [ディレクトリ名] AS [対応するOSのディレクトリパス]
CREATE OR REPLACE DIRECTORY dmp_dir AS '/u01/work/oracle_dmp_dir';

PL/SQL（拡張SQL）

DECLARE … 宣言部変数などを定義 BEGIN～END … 処理部

SELECT INTO … SELECT結果を変数に代入（主にMAXやCOUNTで使う）結果が0件、複数件の場合はエラー

-- SELECT INTO 基本型
DECLARE
 c_userId sample_table.userId%TYPE; -- 変数宣言（SELECT結果を代入）
BEGIN
  SELECT userId
  INTO c_userId -- 
  FROM sample_table;
END

CASE … 分岐 WHEN … 条件式、THEN … 処理文単純CASE式では複雑な条件が指定できない IFより簡潔に記述できる ORDER BYに対しても使える

-- 単純CASE式 基本型
SELECT
  userId,
  CASE color -- 対象カラム
    WHEN 'red' THEN 'apple' -- 条件
    ELSE 'no fruits' -- 条件に一致しなかった場合
  END AS result -- 出力カラム（自身で定義）
FROM sample_table;

-- 検索CASE式 基本型
SELECT
  userId,
  CASE
    WHEN age < 19 THEN 'young' -- 条件
    ELSE 'old' -- 条件に一致しなかった場合
  END
FROM sample_table;

WHILE LOOP（応用）

DECLARE
  i NUMBER := 0;
  WHILE i < 10
  LOOP
    SELECT * INTO sample FROM sample_table; -- INTO句 必須
    i := i + 1;
  END LOOP
END

その他

並べ替えした検索結果の件数指定（ORDER BY したものを ROWNUM）

SELECT * 
FROM (
  SELECT * 
  FROM sample_table
  ORDER BY sample_column
)
WHERE ROWNUM <= 1

■重複しているレコードを検索（重複しているカラム）

SELECT sample_col FROM sample_table GROUP BY sample_col HAVING COUNT(sample_col) > 1;

■行ロック（排他制御） トランザクションを終了させるまで他セッションからSELECTもUPDATEやINSERTも行えない。

begin

// 行ロック
SELECT * FROM sample_table WHERE color = 'red' FOR UPDATE;

// ここで他セッションから同じ行へのアクセスができない

commit;

■間違えてテーブルのデータをすべて削除してしまった場合の対応

SELECT * FROM sampleTable
AS OF TIMESTAMP TO_TIMESTAMP('2021-01-01 10:00:00'), ('YYYY-MM-DD HH24:MI:SS');

■間違ってテーブルのデータをUPDATEしてしまった場合の対応 10分前のテーブルを取得

SELECT * FROM sample_table AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '10' MINUTE);

■シーケンスを進める

-- 調べる（シーケンスが進むことはない）
SELECT sequence_name, last_number FROM user_sequences WHERE sequence_name = 'SAMPLE_SEQUENCE';

-- PLSQL
-- これで進める 例は100進める
DECLARE 
   dummy NUMBER;
BEGIN
   FOR i IN 1..100 LOOP
      SELECT SAMPLE_SEQUENCE.NEXTVAL INTO dummy FROM dual;
   END LOOP;
END;
/