「SQL」タグアーカイブ

SQL, データベース, ブログ

SQL：SELECT時にCASE文で条件指定する方法

2022年7月10日管理者コメントする

SQLではSELECT時などにCASE文を入れる事で「IF-THEN-ELSE式」を記載することも出来ます。

使用例

サンプルテーブル

「GOODS」

クエリー（SQL）

以下の例ではCASE文でGOODS_CODEが特定の値と一致した場合、NAME列に文字列を付与してTEST列として出力しています。

SELECT
NAME, GOODS_CODE,
(CASE GOODS_CODE WHEN 101 THEN '【PC】' || NAME 
				 WHEN 201 THEN '【DESK】' || NAME 
				 ELSE '【OTHER】' || NAME 
 END) test
FROM GOODS g;

SELECT

NAME, GOODS_CODE,

(CASE GOODS_CODE WHEN 101 THEN '【PC】' || NAME

WHEN 201 THEN '【DESK】' || NAME

ELSE '【OTHER】' || NAME

END) test

FROM GOODS g;

出力結果

Oracle, SQL, データベース, ブログ

Oracle：年、月、日、時、分、秒以下を切り捨てて取得する方法

2022年7月6日管理者コメントする

OracleではTRUC関数を使用することで年、月、日、時、分、秒以下を切り捨てて取得することが可能となります。

使用例

サンプルテーブル「BIRTHDAY」

クエリー（SQL）

BIRTHDAYテーブルのUPDATE_DATEに対してTRUNC関数を使用した例となります。第2引数へformatを指定することで指定した単位で切り捨てた値を取得することが出来ます。

SELECT
TRUNC(b.UPDATE_DATE, 'YYYY'),
TRUNC(b.UPDATE_DATE, 'MM'),
TRUNC(b.UPDATE_DATE, 'DD'),
TRUNC(b.UPDATE_DATE, 'HH'),
TRUNC(b.UPDATE_DATE, 'MI')
FROM BIRTHDAY b;

SELECT

TRUNC(b.UPDATE_DATE, 'YYYY'),

TRUNC(b.UPDATE_DATE, 'MM'),

TRUNC(b.UPDATE_DATE, 'DD'),

TRUNC(b.UPDATE_DATE, 'HH'),

TRUNC(b.UPDATE_DATE, 'MI')

FROM BIRTHDAY b;

formatの種類

format 説明

YYYY 年まで取得して月日時分秒を切り捨て

MM 年月まで取得して日時分秒を切り捨て

DD 年月日まで取得して時分秒を切り捨て

HH 年月日時まで取得して分秒を切り捨て

MI 年月日時分まで取得して秒を切り捨て

format	説明
YYYY	年まで取得して月日時分秒を切り捨て
MM	年月まで取得して日時分秒を切り捨て
DD	年月日まで取得して時分秒を切り捨て
HH	年月日時まで取得して分秒を切り捨て
MI	年月日時分まで取得して秒を切り捨て

出力結果

Oracle, SQL, データベース, ブログ

SQL：システム日付と誕生日を元に年齢、誕生日までの月数、日数を表示する方法

2022年7月4日管理者コメントする

SQLでシステム日付、誕生日を使用して年齢、システム日付～誕生日までの月数、日数を取得するSQLをメモしておきます。

以下はOracleでの実行例となっています。

サンプルテーブル

「BIRTHDAY」テーブル

【Oracle】システム日付と誕生日から年齢、誕生日までの月数、日数を表示する例

クエリー（SQL）例

SELECT 
TO_CHAR(sysdate,'YYYY-MM-DD') "本日の日付",
TO_CHAR(b.BIRTHDAY,'YYYY-MM-DD') "誕生日",
EXTRACT(YEAR FROM sysdate) - EXTRACT(YEAR FROM b.BIRTHDAY) "年齢",
TRUNC(MONTHS_BETWEEN(sysdate,b.BIRTHDAY)) "誕生日までの月数",
TRUNC(sysdate - b.BIRTHDAY) "誕生日までの日数"
FROM dual, BIRTHDAY b;

SELECT

TO_CHAR(sysdate,'YYYY-MM-DD') "本日の日付",

TO_CHAR(b.BIRTHDAY,'YYYY-MM-DD') "誕生日",

EXTRACT(YEAR FROM sysdate) - EXTRACT(YEAR FROM b.BIRTHDAY) "年齢",

TRUNC(MONTHS_BETWEEN(sysdate,b.BIRTHDAY)) "誕生日までの月数",

TRUNC(sysdate - b.BIRTHDAY) "誕生日までの日数"

FROM dual, BIRTHDAY b;

実行結果

SQL, データベース, ブログ

SQL：UPDATE と JOIN を使用してレコードを更新する方法

2022年7月3日管理者コメントする

UPDATEで他のテーブルと結合（JOIN）した結果に対してのみ更新を実施したい場合、SQL ServerやMySQL、PostgreSQLではUPDATE文内に直接JOIN句を記載可能ですが、Oracleでは副問合せで結合することでJOINした結果に対して更新することが可能となります。

サンプルテーブル

「GOODS」テーブル

「GOODS_TYPE」テーブル

【Oracle】UPDATE文でJOINを使用して更新する例

クエリー（SQL）例

以下の例ではGOODS_TYPEテーブルに存在するGOODS_CODEと一致するレコードのみGOODSテーブルのPRICE項目を更新しています。

Oracle PL/SQL

UPDATE GOODS SET PRICE = 5000 WHERE GOODS_CODE = ANY (SELECT g.GOODS_CODE FROM GOODS g INNER JOIN GOODS_TYPE gt ON gt.GOODS_CODE = g.GOODS_CODE)

1
2
3
4

UPDATE GOODS
SET PRICE = 5000
WHERE GOODS_CODE = ANY
(SELECT g.GOODS_CODE FROM GOODS g INNER JOIN GOODS_TYPE gt ON gt.GOODS_CODE = g.GOODS_CODE)

実行結果

以下の様にGOODS_CODEが一致しているレコードのみ更新されていることが確認できます。

【SQL Server、MySQL、PostgreSQL】UPDATE文でJOINを使用して更新する例

クエリー（SQL）例

SQL Serverなどでは直接UPDATE文内にJOINを指定可能なので以下のような記載でもエラーにはならず更新されます。

Oracle PL/SQL

UPDATE g SET r.PRICE = 5000; FROM GOODS g INNER JOIN GOODS_TYPE gt ON gt.GOODS_CODE = g.GOODS_CODE

1
2
3
4

UPDATE g
SET r.PRICE = 5000;
FROM GOODS g
INNER JOIN GOODS_TYPE gt ON gt.GOODS_CODE = g.GOODS_CODE

SQL, データベース, ブログ

SQL：合計や平均を算出する方法

2022年7月3日管理者コメントする

SQLで合計を求めるにはSUM関数、平均を求めるにはAVG関数を使用します。

DBMS毎の使用可否

関数＼DBMS	MySQL	PostgreSQL	SQL Server	Oracle
SUM	○	○	○	○
AVG	○	○	○	○

サンプルテーブル

「GOODS」テーブル

合計を求める実行例

SUM関数を使用したクエリー（SQL）例

以下の例ではGOODSテーブルのPRICE項目の合計値を求めています。

Oracle PL/SQL

SELECT SUM(PRICE) FROM GOODS;

1
2

SELECT SUM(PRICE)
FROM GOODS;

実行結果

平均を求める例

AVG関数を使用したクエリー（SQL）例

以下の例ではGOODSテーブルのPRICE項目の平均値を求めています。

Oracle PL/SQL

SELECT AVG(PRICE) FROM GOODS;

1
2

SELECT AVG(PRICE)
FROM GOODS;

実行結果

SQL, データベース, ブログ

SQL：副問合せの結果をALL句で比較して取得する方法

2022年7月2日管理者コメントする

ALL句を使用することでWHERE句内に指定した副問合せの結果を比較してデータを抽出する事が可能となります。

サンプルテーブル

「GOODS」テーブル
「GOODS_TYPE」テーブル

ALL句を利用したクエリー（SQL）例

以下の例ではまず副問合せでGOODS_CODEテーブルからGOODS_CODEが101と301のデータを取得し、ALL句でGOODSテーブルのGOODS_CODEが101と301以外のデータを取得しています。

ALL句を使用して副問合せの結果と比較して抽出するSQL
Oracle PL/SQL

SELECT * FROM GOODS WHERE GOODS_CODE <> ALL ( SELECT GOODS_CODE FROM GOODS_TYPE WHERE GOODS_CODE in('101','301') );

1
2
3
4
5
6
7

SELECT *
FROM GOODS
WHERE GOODS_CODE <> ALL (
SELECT GOODS_CODE
FROM GOODS_TYPE
WHERE GOODS_CODE in('101','301')
);

実行結果

SQL, データベース, ブログ

SQL：文字列や数値項目へ埋め込み文字を挿入して桁数を揃える方法

2022年6月29日管理者コメントする

SQLでゼロ埋めスペース埋めして桁数を揃えたい場合にはLPAD関数やRPAD関数を使用することで取得可能となります。DBMSによっては使用出来ないものもあります。

関数＼DBMS	MySQL	PostgreSQL	SQL Server	Oracle
LPAD	○	○	✕	○
RPAD	○	○	✕	○

書式は「LPAD(列名,バイト数,’埋め込み文字’)」、「RPAD(列名,バイト数,’埋め込み文字’)」で指定します。第三引数の埋め込み文字を省略すると半角スペースが埋め込まれます。

サンプルではOracleでの使用例となります。

サンプルテーブル

「GOODS」テーブル

LPAD関数の使用例1

項目「GOODS_CODE」を10桁でゼロ埋めするSQL（クエリー）

Oracle PL/SQL

SELECT LPAD(g.GOODS_CODE , 10, '0') FROM USER1.GOODS g;

1

SELECT LPAD(g.GOODS_CODE , 10, '0') FROM USER1.GOODS g;
実行結果

LPAD関数の使用例2

項目「GOODS_CODE」を10桁で全角「＊」埋めするSQL（クエリー）

Oracle PL/SQL

SELECT LPAD(g.NAME , 10, '＊') FROM USER1.GOODS g;

1

SELECT LPAD(g.NAME , 10, '＊') FROM USER1.GOODS g;
実行結果
以下のように埋め込み文字へ全角文字を指定すると1文字=2バイト扱いで埋め込まれます。

RPAD関数の使用例1

項目「GOODS_CODE」を10桁でゼロ埋めするSQL（クエリー）

Oracle PL/SQL

SELECT RPAD(g.GOODS_CODE , 10, '0') FROM USER1.GOODS g;

1

SELECT RPAD(g.GOODS_CODE , 10, '0') FROM USER1.GOODS g;
実行結果

RPAD関数の使用例2

項目「GOODS_CODE」を10桁で全角「＊」埋めするSQL（クエリー）

Oracle PL/SQL

SELECT RPAD(g.NAME , 10, '＊') FROM USER1.GOODS g;

1

SELECT RPAD(g.NAME , 10, '＊') FROM USER1.GOODS g;
実行結果
RPADでも以下のように埋め込み文字へ全角文字を指定すると1文字=2バイト扱いで埋め込まれます。

Oracle, SQL, データベース, ブログ

SQL：WHERE句内で「(+)」を指定する意味

2022年6月28日管理者コメントする

SQLを見てるとたまにWHERE句内で「(+)」と記載されているのを見かけることがあります。

この「(+)」はOracle独自で記載が可能となる外部結合演算子といって、「g.GOODS_CODE = gt.GOODS_CODE(+)」のように指定するとLEFT OUTER JOINと同じ結果を取得することが出来ます。

サンプルテーブル

「GOODS」テーブル
「GOODS_TYPE」テーブル

外部結合演算子を利用したクエリー（SQL）例

SELECT g.name, gt.type_name
FROM USER1.GOODS g, USER1.GOODS_TYPE gt
WHERE g.GOODS_CODE = gt.GOODS_CODE(+);

SELECT g.name, gt.type_name

FROM USER1.GOODS g, USER1.GOODS_TYPE gt

WHERE g.GOODS_CODE = gt.GOODS_CODE(+);

通常の左外部結合でのクエリー（SQL）例

SELECT g.name, gt.type_name
FROM USER1.GOODS g
LEFT OUTER JOIN USER1.GOODS_TYPE gt ON g.GOODS_CODE = gt.GOODS_CODE;

SELECT g.name, gt.type_name

FROM USER1.GOODS g

LEFT OUTER JOIN USER1.GOODS_TYPE gt ON g.GOODS_CODE = gt.GOODS_CODE;

実行結果

外部結合演算子、左外部結合どちらも同じ結果となります。

補足

「(＋)」演算子の歴史的背景
Oracle の古いバージョンからサポートされていた外部結合の書き方で、ANSI SQL 標準が普及する以前によく使われていました。ANSI 標準（LEFT JOIN / RIGHT JOIN / FULL OUTER JOIN）が登場して以降は、可読性・移植性の観点から推奨されないことが多いです。
制限・注意点
1. 可読性・保守性：g.GOODS_CODE = gt.GOODS_CODE(+) のような記述は、JOIN の方向やどのテーブルに外部結合をかけているかが分かりにくく、後でクエリを読む人が迷いやすいです。
2. 複雑な結合：複数テーブルを結合する場合、どこに (+) を付けるか、また付け方によって結果が変わるため、注意深く設計する必要があります。
3. NULL の扱い：外部結合によって NULL が返る行が含まれますが、条件節で他の条件と組み合わせると意図しない行の除外が起きることがあります（例えば WHERE 節で AND 条件として別の非 NULL 条件を加えると外部結合の意味が薄れる等）。

ANSI 標準との違い

項目	(+) 外部結合演算子	ANSI SQL 外部結合 (LEFT OUTER JOIN 等)
可読性	やや分かりにくい	比較的分かりやすい
移植性	Oracle に依存	多くの RDBMS でサポートされる
複雑な JOIN の組み立て	エラーや誤解の元になりやすい	ON 句で明示的に指定できるため制御しやすい
サポート状況	最新 Oracle でも非推奨の方向	推奨される方式

現代のベストプラクティス
- 新規開発では ANSI SQL の外部結合構文を使うことを推奨。可読性や他 DBMS への移植性が高いため。
- 既存システムで (+) を使っているなら、リファクタリングやドキュメント整備を行い、「なぜこの構文になっているか」「どのような影響があるか」を明らかにしておく。
- テストを用意して、クエリ変更後に結果が同じになることを検証する（特に NULL の扱いや結合の漏れが起きていないか）。
実際の書き換え例

旧構文を ANSI 標準に書き換えるときの例をもう一つ：

-- 旧構文

Oracle PL/SQL

SELECT g.name, gt.type_name FROM GOODS g, GOODS_TYPE gt WHERE g.GOODS_CODE = gt.GOODS_CODE(+) AND g.status = 'active';

1
2
3
4

SELECT g.name, gt.type_name
  FROM GOODS g, GOODS_TYPE gt
WHERE g.GOODS_CODE = gt.GOODS_CODE(+)
   AND g.status = 'active';

-- 書き換え後（ANSI 標準）

Oracle PL/SQL

SELECT g.name, gt.type_name FROM GOODS g LEFT JOIN GOODS_TYPE gt ON g.GOODS_CODE = gt.GOODS_CODE WHERE g.status = 'active';

1
2
3
4
5

SELECT g.name, gt.type_name
  FROM GOODS g
  LEFT JOIN GOODS_TYPE gt
    ON g.GOODS_CODE = gt.GOODS_CODE
WHERE g.status = 'active';

このように、結合条件を ON に書き出すことで構造が明瞭になります。
互換性・移行のヒント
- Oracle のバージョンによっては (+) 構文が将来削除される可能性があり、非推奨の警告が出ることもあります。
- 他の RDBMS（PostgreSQL、MySQL、SQL Serverなど）で同じSQLを使うことを想定するなら、ANSI JOINに統一しておくと移行がスムーズです。
- クエリのパフォーマンスにも注意。古い構文でも Oracle のオプティマイザが合理化する場合がありますが、可視性や保守性を取る方が長期的には有利。

Oracle, SQL, データベース, トラブル対応, ブログ

Oracle：ユーザー作成時に「ORA-65096」エラーが出た場合の原因と対応方法

2022年6月25日管理者コメントする

Oracleのインストール後にSQL*Plusなどでユーザー作成しようとした際、「ORA-65096」エラーが発生した場合の原因と対応方法についてメモしておきます。

「ORA-65096：共通ユーザーまたはロール名が無効です」の原因

ルートコンテナにローカルユーザーを作成しようとした場合に発生するエラーとなります。
ルートコンテナには共有ユーザー(common user) と呼ばれる特殊なユーザーしか作成することはできません。
Oracle 11gまでと違いOracle 12c以降からは一つのインスタンスには一つのコンテナ・データベース(CDB)と、プラガブル・データベース(PDB)と呼ばれる子DBが存在しています。sysなどのユーザーでログイン直後はコンテナ・データベース(CDB)に接続されている状態となっているため、そのままローカルユーザーを作成しようとしてもエラーが発生してしまうということになります。

「ORA-65096：共通ユーザーまたはロール名が無効です」の対処方法

原因が分かってしまえば対応はシンプルです。接続先がコンテナ・データベース(CDB)であるのがまずいのであればプラガブル・データベース(PDB)に変更してしまえばいいだけです。

まずは「show con_name;」で現在接続されているデータベースを確認します。
次に「select name, open_mode from v$pdbs;」でPDBの名前と現在のOPEN_MODEを確認します。
PDBの名前が「ORCLPDB」というのがわかったのでデータベースの接続先を「ORCLPDB」へ変更します。
もう一度「show con_name;」を実行して接続先が変更されていることを確認します。
接続先がPDBへ変更されたのでもう一度ユーザー作成を実行すると正常に実行されます。

🔍補足：ORA-65096エラーの仕組みと注意点

ORA-65096: invalid common user or role name は、マルチテナント構成の Oracle Database（12c以降） において、
ルートコンテナ（CDB$ROOT）上でローカルユーザーを作成しようとした場合に発生するエラーです。
以下のポイントを押さえておくと、再発を防ぎやすくなります。

観点	内容
エラーの本質	共通ユーザーとローカルユーザーの区別を誤ったことによる構文エラー
共通ユーザー名の規則	`C##` または `c##` のプレフィックスが必須（`COMMON_USER_PREFIX`パラメータで変更可）
発生条件	`CDB$ROOT` に接続したままユーザーを作成／命名規則を満たさない場合
解決策	`ALTER SESSION SET CONTAINER = <PDB名>` でPDBに切り替えてから `CREATE USER` を実行する
参考	`SHOW CON_NAME;` で現在の接続先（コンテナ）を確認可能

✅ 具体例：安全なユーザー作成手順

-- 現在のコンテナを確認
SHOW CON_NAME;

-- PDBに切り替え
ALTER SESSION SET CONTAINER = ORCLPDB;

-- PDB内でユーザー作成
CREATE USER myuser IDENTIFIED BY mypassword;
GRANT CONNECT, RESOURCE TO myuser;

-- 現在のコンテナを確認

SHOW CON_NAME;

-- PDBに切り替え

ALTER SESSION SET CONTAINER = ORCLPDB;

-- PDB内でユーザー作成

CREATE USER myuser IDENTIFIED BY mypassword;

GRANT CONNECT, RESOURCE TO myuser;

もしルートコンテナ側で共通ユーザーを作成したい場合は、以下のようにします。

💡補足メモ

SHOW PDBS; で現在のPDB一覧を確認可能。OPEN_MODE が READ WRITE でなければユーザー作成はできません。
バージョン19c以降では、CDB構成がデフォルトのため、PDB接続の意識が必須 です。
TNS接続文字列（SERVICE_NAME）が CDB を指していると、意図せずルート側に接続してしまうことがあります。

SQL, データベース, ブログ

SQL：TRIM関数を使って任意の文字や文字列を削除する方法

2022年6月21日管理者コメントする

trimは通常、文字列の前後の半角スペースを削除して取得する場合に使用する場合が多いと思いますが、引数へLEADING、TRAILING、BOTHを指定することで任意の文字や文字列を削除して取得することが出来ます。

サンプルテーブル

「goods」テーブル

TRIMに引数「LEADING」を指定した場合

TRIMに引数「LEADING」を指定することで文字列の先頭にある任意の文字や文字列を削除することが出来ます。

クエリー（SQL）例

PgSQL

SELECT TRIM(LEADING '商品' From name) FROM goods ORDER BY id;

1

SELECT TRIM(LEADING '商品' From name) FROM goods ORDER BY id;
実行結果

TRIMに引数「TRAILING」を指定した場合

TRIMに引数「TRAILING」を指定することで文字列の末尾にある任意の文字や文字列を削除することが出来ます。

クエリー（SQL）例

PgSQL

SELECT TRIM(TRAILING 'A' From name) FROM goods ORDER BY id;

1

SELECT TRIM(TRAILING 'A' From name) FROM goods ORDER BY id;
実行結果

TRIMに引数「BOTH」を指定した場合

TRIMに引数「BOTH」を指定することで文字列の先頭と末尾にある任意の文字や文字列を削除することが出来ます。

クエリー（SQL）例

PgSQL

SELECT TRIM(BOTH 'A' From name) FROM goods ORDER BY id;

1

SELECT TRIM(BOTH 'A' From name) FROM goods ORDER BY id;
実行結果

使用例

サンプルテーブル

クエリー（SQL）

出力結果

関連記事

使用例

サンプルテーブル「BIRTHDAY」

クエリー（SQL）

出力結果

関連記事

サンプルテーブル

「BIRTHDAY」テーブル

【Oracle】システム日付と誕生日から年齢、誕生日までの月数、日数を表示する例

クエリー（SQL）例

実行結果

関連記事

サンプルテーブル

「GOODS」テーブル

「GOODS_TYPE」テーブル

【Oracle】UPDATE文でJOINを使用して更新する例

クエリー（SQL）例

実行結果

【SQL Server、MySQL、PostgreSQL】UPDATE文でJOINを使用して更新する例

クエリー（SQL）例

関連記事

DBMS毎の使用可否

サンプルテーブル

「GOODS」テーブル

合計を求める実行例

SUM関数を使用したクエリー（SQL）例

実行結果

平均を求める例

AVG関数を使用したクエリー（SQL）例

実行結果

関連記事

サンプルテーブル

ALL句を利用したクエリー（SQL）例

実行結果

関連記事

サンプルテーブル

LPAD関数の使用例1

LPAD関数の使用例2

RPAD関数の使用例1

RPAD関数の使用例2

関連記事

サンプルテーブル

外部結合演算子を利用したクエリー（SQL）例

通常の左外部結合でのクエリー（SQL）例

実行結果

補足

関連記事

「ORA-65096：共通ユーザーまたはロール名が無効です」の原因

「ORA-65096：共通ユーザーまたはロール名が無効です」の対処方法

🔍補足：ORA-65096エラーの仕組みと注意点

✅ 具体例：安全なユーザー作成手順

💡補足メモ

関連記事

サンプルテーブル

TRIMに引数「LEADING」を指定した場合

TRIMに引数「TRAILING」を指定した場合

TRIMに引数「BOTH」を指定した場合

関連記事

「駑馬十駕」を信念に IT系情報を中心に調べた事をコツコツ綴っています。

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事