「データベース」カテゴリーアーカイブ

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')
);

実行結果

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

Oracle：DESCRIBEコマンドでテーブル（表）構造を確認する方法

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

SQL PlusやSQL Developerに用意されている「DESCRIBE」コマンドを使用することでテーブル構造を確認することが出来ます。

DESCRIBEコマンドはDESCと省略することも出来ます。

SQL PlusでのDESCRIBEコマンド実行例

「DESCRIBE テーブル名」でテーブル構造が確認できます。

SQL PlusでのDESCコマンド実行例

DESCでもDESCRIBEと同じ結果を取得できます。

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

Oracle：接続時に「ORA-01109：データベースがオープンされていません。」エラーが出た場合の原因と対応方法

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

DBクライアントソフトなどでOracle DBへ接続時に「ORA-01109：データベースがオープンされていません。」が表示された場合の原因と対応方法についてメモしておきます。

「ORA-01109：データベースがオープンされていません。」の原因

文字通り接続しようとしているDBがオープンされていないのが原因となります。今回接続先を「ORCLPDB」に指定していましたがそこがオープンされていないようです。
念のためSQL Plusを起動して「show pdbs」コマンドでプラガブル・データベース(PDB)のオープン状態を確認するとやはりオープンされていませんでした。

「ORA-01109：データベースがオープンされていません。」の対処方法

SQL Plusで「alter pluggable database ORCLPDB open;」コマンドを実行し、ORCLPDBをオープンします。
「show pdbs」コマンドでプラガブル・データベース(PDB)のオープン状態を確認します。
これでORCLPDBのオープンだけなら完了していますが、このままPCを再起動するとまた再びクローズ状態に戻ってしまいます。ですので最後に現在オープンされている全てのPDBを自動起動（オープン）するように「alter pluggable database all save state;」コマンドで保存します。

補足：よくある注意点と確認ポイント

上記の対応だけで解決しない場合や、環境によっては以下のようなケースもあるので参考にしてください。

PDB が自動で開かない場合

ALTER PLUGGABLE DATABASE ALL SAVE STATE; を実行しても、再起動後に PDB が自動的にオープンされないことがあります。
その際は以下を確認してみてください。

PDB の AUTO_STARTUP が有効になっているか
初期化パラメータ ENABLE_PLUGGABLE_DATABASE が設定されているか
OS 側の起動スクリプトに PDB オープン処理が含まれているか

権限不足でエラーになる場合

PDB を開こうとしたときに ORA-01031: insufficient privileges が出る場合は、権限不足の可能性があります。
SYSDBA 権限を持つユーザーで接続して実行する必要があります。

PDB の状態確認

今の状態を確認したいときは、次のコマンドを使うと便利です。

```sql
SELECT name, open_mode FROM v$pdbs;
-- または
SELECT name, status FROM dba_pdbs;

```sql

SELECT name, open_mode FROM v$pdbs;

-- または

SELECT name, status FROM dba_pdbs;

READ WRITE であればオープン済み、MOUNTED ならまだ開いていない状態です。

ログのチェック

もし原因が分からない場合は、アラートログ（alert log）や trace ファイルを確認してみてください。
「ファイルが読み込めない」「整合性が取れていない」といったエラーが記録されていることもあります。

冗長構成を利用している場合

Data Guard や RAC のような構成では、PDB のオープン状態が自動で引き継がれないことがあります。
フェイルオーバー後に PDB が閉じたままになるケースもあるので注意が必要です。

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, データベース, ブログ

SQL：大文字 ⇒ 小文字、小文字 ⇒ 大文字へ変換する方法

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

SQLで大文字⇒小文字に変換する場合はLCASE関数 or LOWER関数、小文字⇒大文字へ変換するにはUCASE関数 or UPPER関数を使用します。DBMS毎で使用出来る関数が異なるので整理しておきます。

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

関数＼DBMS	MySQL	PostgreSQL	SQL Server	Oracle
UCASE ※小文字 ⇒ 大文字	○	✕	✕	✕
UPPER ※小文字 ⇒ 大文字	✕	○	○	○
LCASE ※大文字 ⇒ 小文字	○	✕	✕	✕
LOWER ※大文字 ⇒ 小文字	✕	○	○	○

サンプルテーブル

「goods」テーブル

UPPER関数（小文字 ⇒ 大文字へ変換）の使用例

クエリー（SQL）例

PgSQL

SELECT UPPER(name) FROM goods ORDER BY id;

1

SELECT UPPER(name) FROM goods ORDER BY id;
実行結果

LOWER関数（大文字 ⇒ 小文字へ変換）の使用例

クエリー（SQL）例

PgSQL

SELECT LOWER(name) FROM goods ORDER BY id;

1

SELECT LOWER(name) FROM goods ORDER BY id;
実行結果

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

SQLで一部の文字列を取得する方法

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

SQLで一部の文字列を取得するにはSUBSTR関数やLEFT関数、RIGHT関数を使用することで可能となります。DBMS毎に利用できる関数に違いがありOracleではLEFT関数、RIGHT関数は使用出来ません。

関数＼DBMS	MySQL	PostgreSQL	SQL Server	Oracle
SUBSTR or SUBSTRING	○	○	○	○
LEFT	○	○	○	✕
RIGHT	○	○	○	✕

サンプルテーブル

「goods」テーブル

SUBSTR関数の使用例

クエリー（SQL）例
SUBSTR関数は「SUBSTR(文字列, 開始位置, (文字数))」で指定します。第三引数を省略すると第二引数へ指定した文字以降全て取得します。
SUBSTRはSUBSTRINGでも同様の動作となります。

SELECT 
SUBSTR(name,2),   -- 先頭から見て2文字目以降を取得
SUBSTR(name,2,1), -- 先頭から見て2文字目～1文字を取得
SUBSTR(name,2,3)  -- 先頭から見て2文字目～3文字を取得
FROM goods WHERE name = 'ABCDE';

SELECT

SUBSTR(name,2), -- 先頭から見て2文字目以降を取得

SUBSTR(name,2,1), -- 先頭から見て2文字目～1文字を取得

SUBSTR(name,2,3) -- 先頭から見て2文字目～3文字を取得

FROM goods WHERE name = 'ABCDE';

実行結果

LEFT関数の使用例

クエリー（SQL）例
LEFT 関数は「LEFT(文字列, 文字数)」で指定します。

LEFT関数の使用例
PgSQL

SELECT LEFT(name,2) 　 -- 左から2文字を取得 FROM goods WHERE name = 'ABCDE';

1
2
3

SELECT
LEFT(name,2) 　 -- 左から2文字を取得
FROM goods WHERE name = 'ABCDE';
実行結果

RIGHT関数の使用例

クエリー（SQL）例
LEFT 関数は「RIGHT(文字列, 文字数)」で指定します。

RIGHT関数の使用例
PgSQL

SELECT RIGHT(name,2)　 -- 右から2文字を取得 FROM goods WHERE name = 'ABCDE';

1
2
3

SELECT
RIGHT(name,2)　 -- 右から2文字を取得
FROM goods WHERE name = 'ABCDE';
実行結果

DBMS毎の使用可否

サンプルテーブル

「GOODS」テーブル

合計を求める実行例

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

実行結果

平均を求める例

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

実行結果

関連記事

サンプルテーブル

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

実行結果

関連記事

SQL PlusでのDESCRIBEコマンド実行例

SQL PlusでのDESCコマンド実行例

関連記事

「ORA-01109：データベースがオープンされていません。」の原因

「ORA-01109：データベースがオープンされていません。」の対処方法

補足：よくある注意点と確認ポイント

PDB が自動で開かない場合

権限不足でエラーになる場合

PDB の状態確認

ログのチェック

冗長構成を利用している場合

関連するエラーメッセージ

関連記事

サンプルテーブル

LPAD関数の使用例1

LPAD関数の使用例2

RPAD関数の使用例1

RPAD関数の使用例2

関連記事

サンプルテーブル

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

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

実行結果

補足

関連記事

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

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

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

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

💡補足メモ

関連記事

サンプルテーブル

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

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

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

関連記事

サンプルテーブル

UPPER関数（小文字 ⇒ 大文字へ変換）の使用例

LOWER関数（大文字 ⇒ 小文字へ変換）の使用例

関連記事

サンプルテーブル

SUBSTR関数の使用例

LEFT関数の使用例

RIGHT関数の使用例

関連記事

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

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事