「SQL」カテゴリーアーカイブ

SQL関連のカテゴリ

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

実行結果

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

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

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

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

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

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

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

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

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

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

使用例

サンプルテーブル「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）例

実行結果

関連記事

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

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

関連記事

サンプルテーブル

LPAD関数の使用例1

LPAD関数の使用例2

RPAD関数の使用例1

RPAD関数の使用例2

関連記事

サンプルテーブル

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

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

実行結果

関連記事

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

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

関連記事

サンプルテーブル

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

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

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

関連記事

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

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事