「SQL」タグアーカイブ

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へ変更されたのでもう一度ユーザー作成を実行すると正常に実行されます。

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';
実行結果

サンプルテーブル

「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：共通ユーザーまたはロール名が無効です」の対処方法

関連記事

サンプルテーブル

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

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

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

関連記事

サンプルテーブル

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

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

関連記事

サンプルテーブル

SUBSTR関数の使用例

LEFT関数の使用例

RIGHT関数の使用例

関連記事

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

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事