「SQL」タグアーカイブ

Oracle「ORA-06502: PL/SQL 数値または値エラー」エラーが出た時の解決方法

2025年9月28日管理者コメントする

Oracle データベースを利用していると、**「ORA-06502: PL/SQL: 数値または値エラー」**というエラーに遭遇することがあります。これは比較的よく見られるエラーの一つで、主に「データ型の不一致」や「文字列長の超過」が原因です。この記事では、このエラーの代表的な原因と解決方法を解説します。

ORA-06502 エラーの意味

エラーメッセージ全文は以下のようになります。

このエラーは、PL/SQL 実行時に「値が期待されるデータ型に収まらない」場合に発生します。例えば以下のケースです。

数値型の変数に、文字列を代入しようとした場合
VARCHAR2 の長さ制限を超える文字列を代入した場合
型変換関数（TO_NUMBER, TO_DATE など）が失敗した場合

よくある原因と解決方法

1. 文字列長の超過

解決方法: 変数の長さを見直す、あるいは SUBSTR を利用して長さを調整する。

2. 数値変換エラー

解決方法: 入力値が数値かどうかを事前にチェックする。正規表現を利用するのも有効です。

3. 不正な日付変換

原因: 存在しない日付を変換しようとした。
解決方法: 入力フォーマットをチェックし、妥当な値のみ渡す。

4. 数値桁数のオーバーフロー

解決方法: NUMBER の定義を見直す、または値を丸める。

トラブルシューティングのポイント

エラー発生時の 変数定義 を確認する
DBMS_OUTPUT.PUT_LINE で 代入しようとしている値 を出力する
データベースの カラム定義と変数定義の不一致 を確認する
外部入力（CSV など）を扱う場合は 入力データの妥当性チェック を行う

まとめ

「ORA-06502」エラーは、ほとんどの場合 データ型の不一致 や 値の範囲超過 が原因です。
再発防止のためには以下が重要です。

変数やカラムの定義を余裕を持たせて設計する
入力値チェックを徹底する
デバッグ時に DBMS_OUTPUT を活用して値を追跡する

これらを意識することで、エラーを効率的に解消できるはずです。

SQL, ブログ

SQL便利技：PIVOTとUNPIVOTで自由自在に表を変換する方法

2025年9月23日管理者コメントする

SQLを使ってデータを扱うとき、表の形を「横持ち」や「縦持ち」に変換したい場面は多々あります。
例えば、月ごとの売上を列ごとに並べたい、あるいはアンケート結果を1列にまとめたいなど。

こうした「表の回転」に便利なのが PIVOT と UNPIVOT です。
本記事では、それぞれの使い方と、主要なDBMSごとの違いを整理します。

PIVOTとは？

PIVOTは 縦持ちデータを横持ちに変換する 機能です。
例：月ごとの売上を集計して列化する。

サンプルデータ

商品月売上

A 1月 100

A 2月 150

B 1月 200

B 2月 180

商品	月	売上
A	1月	100
A	2月	150
B	1月	200
B	2月	180

PIVOTのイメージ

商品	1月売上	2月売上
A	100	150
B	200	180

UNPIVOTとは？

UNPIVOTは 横持ちデータを縦持ちに変換する 機能です。
例：上記の「商品×月売上表」を再び「商品・月・売上」の縦持ちに戻す。

各DBMSでの書き方比較

1. SQL Server

SQL Serverはネイティブで PIVOT / UNPIVOT をサポート。

-- PIVOT
SELECT 商品, [1月] AS 売上1月, [2月] AS 売上2月
FROM (
  SELECT 商品, 月, 売上
  FROM Sales
) AS src
PIVOT (
  SUM(売上) FOR 月 IN ([1月], [2月])
) AS pvt;

-- PIVOT

SELECT 商品, [1月] AS 売上1月, [2月] AS 売上2月

FROM (

SELECT 商品, 月, 売上

FROM Sales

) AS src

PIVOT (

SUM(売上) FOR 月 IN ([1月], [2月])

) AS pvt;

-- UNPIVOT
SELECT 商品, 月, 売上
FROM SalesPivot
UNPIVOT (
  売上 FOR 月 IN ([1月売上], [2月売上])
) AS unpvt;

-- UNPIVOT

SELECT 商品, 月, 売上

FROM SalesPivot

UNPIVOT (

売上 FOR 月 IN ([1月売上], [2月売上])

) AS unpvt;

2. Oracle

Oracleは PIVOT / UNPIVOT が標準で利用可能。

3. PostgreSQL

PostgreSQLはPIVOT句を持たないため、crosstab関数（tablefunc拡張） を使う。

4. MySQL

MySQLには PIVOT 句はなく、CASE式 + GROUP BY を使う。

-- PIVOT
SELECT
  商品,
  SUM(CASE WHEN 月 = '1月' THEN 売上 END) AS 売上1月,
  SUM(CASE WHEN 月 = '2月' THEN 売上 END) AS 売上2月
FROM Sales
GROUP BY 商品;

-- PIVOT

SELECT

商品,

SUM(CASE WHEN 月 = '1月' THEN 売上 END) AS 売上1月,

SUM(CASE WHEN 月 = '2月' THEN 売上 END) AS 売上2月

FROM Sales

GROUP BY 商品;

UNPIVOTも標準構文がないので、PostgreSQL同様 UNION ALL を用いる。

-- UNPIVOT (UNION ALL)
SELECT 商品, '1月' AS 月, 売上1月 AS 売上 FROM sales_pivot
UNION ALL
SELECT 商品, '2月', 売上2月 FROM sales_pivot;

-- UNPIVOT (UNION ALL)

SELECT 商品, '1月' AS 月, 売上1月 AS 売上 FROM sales_pivot

UNION ALL

SELECT 商品, '2月', 売上2月 FROM sales_pivot;

DBMS比較表

DBMS PIVOT対応 UNPIVOT対応代替手段

SQL Server ネイティブネイティブそのまま使用可

Oracle ネイティブネイティブそのまま使用可

PostgreSQL なしなし crosstab関数 / UNION ALL

MySQL なしなし CASE式 + GROUP BY / UNION ALL

DBMS	PIVOT対応	UNPIVOT対応	代替手段
SQL Server	ネイティブ	ネイティブ	そのまま使用可
Oracle	ネイティブ	ネイティブ	そのまま使用可
PostgreSQL	なし	なし	crosstab関数 / UNION ALL
MySQL	なし	なし	CASE式 + GROUP BY / UNION ALL

まとめ

SQL Server / Oracle → PIVOT/UNPIVOTがシンプルに使える。
PostgreSQL / MySQL → 標準ではなく、関数やCASE式で工夫が必要。

「集計を横に展開したい」あるいは「フラットに戻したい」とき、
DBMSに応じた方法を覚えておくと、データ整形がぐっと楽になります。

SQL, ブログ

DENSE_RANKとRANKの違いを使い分けるランキング便利技

2025年9月20日管理者コメントする

SQLでデータに順位を付けたいとき、よく使われるのが RANK と DENSE_RANK です。
どちらもウィンドウ関数として利用でき、同点がある場合にどう順位を振るかが異なります。

「売上ランキングを作りたい」「部門ごとのTOP3を出したい」といった実務シーンでは、両者の違いを理解していないと期待通りの結果にならないことがあります。

本記事では、RANKとDENSE_RANKの基本的な違い を解説したうえで、実務での使い分け方、さらに DBMSごとのサポート状況 までわかりやすく紹介します。

RANKとは？

RANK は、同点がある場合に同じ順位を付けますが、その分次の順位が飛びます。

例：テストの点数ランキング

SELECT
  name,
  score,
  RANK() OVER (ORDER BY score DESC) AS rank
FROM
  students;

SELECT

name,

score,

RANK() OVER (ORDER BY score DESC) AS rank

FROM

students;

名前	点数	RANK
Aさん	100	1
Bさん	95	2
Cさん	95	2
Dさん	90	4

👉 2位が2人いるため、次の順位は「4位」となります。

DENSE_RANKとは？

DENSE_RANK は、同点の場合も同じ順位を付けますが、次の順位は飛ばさずに連続します。

SELECT
  name,
  score,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM
  students;

SELECT

name,

score,

DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank

FROM

students;

名前	点数	DENSE_RANK
Aさん	100	1
Bさん	95	2
Cさん	95	2
Dさん	90	3

👉 2位が2人いても、次の順位は「3位」となります。

実務での便利な活用例

1. 売上ランキングを作る

売上データから各商品の順位を求めたいときは DENSE_RANK が便利です。

SELECT
  product_id,
  SUM(sales_amount) AS total_sales,
  DENSE_RANK() OVER (
    ORDER BY SUM(sales_amount) DESC
  ) AS sales_rank
FROM
  sales
GROUP BY
  product_id;

SELECT

product_id,

SUM(sales_amount) AS total_sales,

DENSE_RANK() OVER (

ORDER BY SUM(sales_amount) DESC

) AS sales_rank

FROM

sales

GROUP BY

product_id;

👉 同順位の商品があっても「順位が飛ばない」ので、一覧が見やすくなります。

2. 部門別ランキングを作る

「部門ごとにランキングを出したい」場合は、PARTITION BY を組み合わせます。

SELECT
  department,
  employee_name,
  sales_amount,
  RANK() OVER (
    PARTITION BY department
    ORDER BY sales_amount DESC
  ) AS rank_in_dept
FROM
  employee_sales;

SELECT

department,

employee_name,

sales_amount,

RANK() OVER (

PARTITION BY department

ORDER BY sales_amount DESC

) AS rank_in_dept

FROM

employee_sales;

👉 部門ごとに順位がリセットされ、それぞれの中でランキングが作成されます。

3. TOP Nの商品を抽出する

「売上TOP3の商品を取得したい」といった場合は注意が必要です。

WITH ranked AS (
  SELECT
    product_id,
    SUM(sales_amount) AS total_sales,
    RANK() OVER (
      ORDER BY SUM(sales_amount) DESC
    ) AS rank
  FROM
    sales
  GROUP BY
    product_id
)
SELECT
  *
FROM
  ranked
WHERE
  rank <= 3;  -- ここを任意のNに変更

WITH ranked AS (

SELECT

product_id,

SUM(sales_amount) AS total_sales,

RANK() OVER (

ORDER BY SUM(sales_amount) DESC

) AS rank

FROM

sales

GROUP BY

product_id

)

SELECT

FROM

ranked

WHERE

rank <= 3; -- ここを任意のNに変更

👉 RANK を使うと、3位が同点の場合に4件以上取得されることがあります。

DENSE_RANK なら「必ず3位まで」に限定できるため安全です。

DBMSごとの違い

Oracle Database

RANK / DENSE_RANK ともに早期からサポート
標準SQLに準拠し、安定して利用可能

PostgreSQL

バージョン8.4以降でサポート
標準SQL準拠のため、OracleやSQL Serverとほぼ同じ書き方で利用できる

MySQL

MySQL 8.0 以降で利用可能
それ以前（5.x系など）では未対応で、ユーザー変数を使った代替実装が必要

SQL Server (Microsoft)

2005以降でサポート
標準SQLと同じ感覚で利用可能

👉 まとめると：

Oracle / PostgreSQL / SQL Server → そのまま使える
MySQL 8.0以降 → 標準対応
MySQL 5.x以前 → 対応なし（代替実装が必要）

まとめ

RANK → 同順位があると次の順位が飛ぶ（例：1,2,2,4）
DENSE_RANK → 順位が連続（例：1,2,2,3）
実務での使い分け：
- 売上ランキング → DENSE_RANK
- 部門別の表彰や順位 → RANK
- TOP N抽出 → DENSE_RANK が安全
DBMSによってサポート状況が異なるため、特にMySQLはバージョンを確認することが重要

SQLでのランキング処理はシーンによって適切に関数を選ぶのがコツです。

SQL, ブログ

正規表現(REGEXP)でSQLがもっと楽になる！実践パターン集

2025年9月19日管理者コメントする

SQLの検索でよく使われる LIKE 句は便利ですが、複雑な条件指定には限界があります。
そこで強力な武器となるのが 正規表現（REGEXP）。
この記事では、基本的な使い方からよく使うパターン、さらに「SQLで利用できる正規表現の一覧」をまとめました。

1. REGEXPの基本構文

SQLでは REGEXP を用いて文字列検索を行います。

2. 使用できる正規表現の一覧（MySQL準拠）

SQLで使える代表的な正規表現を整理しました。
※DBエンジンにより若干差異あり（MySQL、PostgreSQL、Oracleなど）

パターン	意味	使用例
^	行頭にマッチ	^A → Aで始まる
$	行末にマッチ	Z$ → Zで終わる
.	任意の1文字	c.t → cat, cot, cut
[...]	文字クラス	[0-9] → 数字1文字
[^...]	否定の文字クラス	[^0-9] → 数字以外
*	0回以上の繰り返し	a* → \\"	a	aaa"
+	1回以上の繰り返し	a+ → a, aa
?	0回または1回	colou?r → color, colour
{n}	n回の繰り返し	[0-9]{4} → 4桁の数字
{n,}	n回以上の繰り返し	[0-9]{2,} → 2桁以上の数字
{n,m}	n〜m回の繰り返し	[A-Z]{2,5} → 2〜5文字の大文字
\|	OR条件	cat\|dog → cat または dog
()	グループ化	(abc)+ → abc, abcabc
[:digit:]	数字	[[:digit:]] → 0〜9
[:alpha:]	英字	[[:alpha:]] → A〜Z, a〜z
[:alnum:]	英数字	[[:alnum:]] → 英数字
[:space:]	空白文字	[[:space:]] → 空白, 改行, タブ
[:upper:]	大文字	[[:upper:]] → 大文字
[:lower:]	小文字	[[:lower:]] → 小文字

3. よく使う実践パターン

(1) 先頭・末尾の一致

(2) 日付フォーマット判定

(3) メールアドレス判定

(4) 商品コードの書式検証

(5) 拡張子フィルタ

4. REGEXPのメリットと注意点

メリット

複雑な条件をシンプルに表現できる
SQLの可読性が向上
データ品質チェックに有効

注意点

DBごとに正規表現エンジンが異なる（MySQL、PostgreSQL、Oracleで互換性に注意）
パフォーマンス低下の可能性があるため、大量データ処理時はインデックス設計と併用が望ましい

SQLでのREGEXPサポート比較（DBMSごと）

DBMS	REGEXPサポート	演算子/関数例	備考
MySQL	◎	REGEXP, REGEXP_REPLACE	8.0以降はICUベース
PostgreSQL	◎	~, ~, !~, !~	高度な正規表現OK
Oracle	◎	REGEXP_LIKE, REGEXP_SUBSTR	POSIX互換
SQL Server	△	（CLR関数経由）	ネイティブ未対応
SQLite	△	REGEXP（要自作関数）	デフォルト非対応
BigQuery	◎	REGEXP_CONTAINS など	クラウドSQL
Snowflake	◎	RLIKE, REGEXP	ほぼMySQL互換

まとめ

REGEXPを使えばSQLの検索が格段に柔軟になります。
一覧表を参考に、ログ解析やメール判定、コード検証などに応用してみてください。

「LIKEでは表現できない…」と思ったら、REGEXPの出番です！

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

PL/SQL：BULK COLLECTとFORALLを使った効率的な大量データ処理

2025年9月17日管理者コメントする

Oracle PL/SQLを使って大量データを処理する際、1行ずつループして処理を行うとパフォーマンスが低下します。
このようなケースで活躍するのが BULK COLLECT と FORALL です。

これらを活用することで、SQLとPL/SQL間のコンテキスト切り替えを最小限に抑え、大量データを効率的に処理できます。

BULK COLLECTとは？

BULK COLLECTは、複数行のデータを一括でコレクション（配列型変数）に格納する仕組みです。

基本構文

使用例

DECLARE
  TYPE t_emp IS TABLE OF employees%ROWTYPE;
  l_emp t_emp;
BEGIN
  SELECT * 
  BULK COLLECT INTO l_emp
  FROM employees
  WHERE department_id = 10;

  DBMS_OUTPUT.PUT_LINE('取得件数: ' || l_emp.COUNT);
END;

DECLARE

TYPE t_emp IS TABLE OF employees%ROWTYPE;

l_emp t_emp;

BEGIN

SELECT *

BULK COLLECT INTO l_emp

FROM employees

WHERE department_id = 10;

DBMS_OUTPUT.PUT_LINE('取得件数: ' || l_emp.COUNT);

END;

✅ 通常のSELECT INTOでは1行しか取得できませんが、BULK COLLECTを使うと複数行をまとめて変数に格納できます。

FORALLとは？

FORALLは、コレクションに格納されたデータを使って一括処理（INSERT/UPDATE/DELETE）を行う構文です。

基本構文

使用例

DECLARE
  TYPE t_empno IS TABLE OF employees.employee_id%TYPE;
  l_empno t_empno;
BEGIN
  -- 部署ID 20の社員IDをまとめて取得
  SELECT employee_id
  BULK COLLECT INTO l_empno
  FROM employees
  WHERE department_id = 20;

  -- 一括で給与を10%アップ
  FORALL i IN l_empno.FIRST .. l_empno.LAST
    UPDATE employees
    SET salary = salary * 1.1
    WHERE employee_id = l_empno(i);

  COMMIT;
END;

DECLARE

TYPE t_empno IS TABLE OF employees.employee_id%TYPE;

l_empno t_empno;

BEGIN

-- 部署ID 20の社員IDをまとめて取得

SELECT employee_id

BULK COLLECT INTO l_empno

FROM employees

WHERE department_id = 20;

-- 一括で給与を10%アップ

FORALL i IN l_empno.FIRST .. l_empno.LAST

UPDATE employees

SET salary = salary * 1.1

WHERE employee_id = l_empno(i);

COMMIT;

END;

✅ FORループで1件ずつUPDATEするよりも大幅に高速化できます。

BULK COLLECTとFORALLを組み合わせる

実務では、BULK COLLECTで一括取得 → FORALLで一括更新／削除といった流れがよく使われます。

処理フロー例

BULK COLLECTで対象データを配列に取得
配列の内容をFORALLで一括更新
コミット

このように組み合わせることで、バッチ処理や大量データ更新におけるパフォーマンスを劇的に改善できます。

パフォーマンス比較

従来のループ処理
SQLとPL/SQL間で行き来が多くなり、数万件以上の処理では遅くなる
BULK COLLECT + FORALL
コンテキストスイッチが最小化され、処理速度が数倍〜数十倍向上するケースもある

注意点

BULK COLLECTで一度に大量データを取得するとメモリ不足の可能性あり
→ LIMIT句を組み合わせて分割取得が推奨
FORALLはDML専用（SELECTでは使えない）
例外処理はSAVE EXCEPTIONSを付けて制御することも可能

まとめ

BULK COLLECT → 複数行を一括取得
FORALL → 複数行を一括処理
大量データ処理では必須テクニック
メモリ管理や例外処理に注意しつつ使うと、バッチ処理の効率が大幅に改善

💡 実際のプロジェクトでは「数十万件以上のデータ更新」で特に効果が出やすいため、PL/SQLチューニングの定番として覚えておきましょう。

SQL, ブログ

SQL：サブクエリの使い方を徹底解説！実例で学ぶネストされたSELECT文

2025年9月15日管理者コメントする

はじめに

SQLを学んでいると「サブクエリ（副問い合わせ）」という言葉を耳にすることが多いでしょう。
サブクエリは、SELECT文の中にさらにSELECT文をネスト（入れ子構造）して使う機能です。
複雑な条件指定や集計処理をシンプルに書けるため、業務システムやデータ分析で頻繁に活用されます。

この記事では、サブクエリの基本から実践的な使い方まで、実例を交えて徹底解説します。

サブクエリとは？

**サブクエリ（Subquery）**とは、SQL文の中に埋め込まれるSELECT文のことです。
通常のSQL文の一部として利用され、主に次のような用途があります。

WHERE句での条件指定
FROM句での仮想テーブル生成
SELECT句での派生列計算

サブクエリの基本構文

サブクエリの基本的な形は以下の通りです。

用途によって、スカラサブクエリ、行サブクエリ、テーブルサブクエリと呼ばれることもあります。

例1：WHERE句でのサブクエリ

もっともよく使われるのが WHERE句での利用 です。
例えば「平均給与より高い社員を取得する」場合は次のように書けます。

サブクエリ (SELECT AVG(給与) FROM 社員) で平均給与を取得
メインクエリで給与がそれを上回る社員を抽出

例2：IN句とサブクエリ

複数の値を条件にする場合は IN句 を利用します。

サブクエリで特定の日に注文された商品IDを取得
メインクエリでその商品情報を表示

例3：FROM句でのサブクエリ（派生テーブル）

FROM句でサブクエリを使えば、仮想テーブルを作成して結合や集計が可能です。

サブクエリで部署ごとの平均給与を計算
メインクエリで平均給与が30万円を超える部署を抽出

例4：SELECT句でのサブクエリ

SELECT句にサブクエリを埋め込むことで、計算列を動的に追加できます。

社員ごとに部署名をサブクエリで取得
JOINを使わずにシンプルに表記可能（ただしパフォーマンス注意）

サブクエリを使うときの注意点

パフォーマンスに注意
ネストが深すぎると処理速度が落ちる場合があります。JOINやCTE（共通テーブル式）で置き換えを検討しましょう。
返却される値の型に注意
単一値を期待しているのに複数行が返るとエラーになります。
読みやすさを意識
サブクエリは便利ですが、複雑になると可読性が低下します。適切にインデントを整えるのが重要です。

DBMS別：サブクエリ対応表

DBMS	WHERE句でのサブクエリ	FROM句でのサブクエリ	SELECT句でのサブクエリ	相関サブクエリ	備考
Oracle	○ 完全対応	○ インラインビュー	○ 利用可	○ 高性能	大規模業務で多用
MySQL	○ (v4.1以降対応)	○ 利用可	○ 利用可	△ パフォーマンス注意	古いバージョンでは非対応
PostgreSQL	○ 標準準拠	○ 利用可	○ 利用可	○ 高性能	複雑な分析処理に強い
SQL Server	○ 完全対応	○ 利用可	○ 利用可	○ ただし過剰利用注意	実行計画が膨らむことあり

まとめ

サブクエリはSQL文の中でネストされたSELECT文
WHERE、FROM、SELECTなど多くの場面で利用可能
集計や複雑な条件指定をシンプルに書ける
パフォーマンスと可読性に注意が必要

サブクエリをマスターすることで、SQLの表現力が大幅に広がります。
まずはシンプルなWHERE句から練習し、徐々に複雑なケースに挑戦してみましょう！

SQL, ブログ

Oracle：DECODE関数とCASE式の違いを徹底解説

2025年9月13日管理者コメントする

Oracle SQLを学んでいると、「DECODE関数」と「CASE式」の使い分けで迷う方は多いのではないでしょうか。
どちらも条件分岐を行うために利用できますが、機能や表現力には明確な違いがあります。

本記事では、DECODEとCASEの特徴、違い、実務での使い分けポイントをわかりやすく解説します。

1. DECODE関数とは？

DECODE は Oracle独自の関数 で、簡易的な条件分岐を行うために利用されます。
基本構文は次の通りです。

指定した式の値と「検索値」が一致すれば、その「置換値」を返す
一致しなければ最後のデフォルト値を返す（省略可能）

例：部署IDに応じて部署名を返す

2. CASE式とは？

CASE は SQL標準 でサポートされる条件分岐の構文です。
Oracleだけでなく、他のデータベース（MySQL、PostgreSQLなど）でも使えます。

構文（シンプルCASE）

構文（検索CASE）

例：部署IDに応じて部署名を返す

3. DECODEとCASEの比較

項目	DECODE	CASE
標準SQL	Oracle独自機能	SQL標準でサポート
構文	関数形式	式形式
条件	「等しい場合」のみ判定可能	!ERROR! C4 -> Formula Error: Unexpected ,
可読性	ネストが増えると読みにくい	複雑な条件もわかりやすく記述可能
移植性	Oracleに依存	他DBでも利用可能
推奨度	古いコードに多い	現在はこちらが主流

4. 実務での使い分けポイント

既存システムのSQLでDECODEが多用されている → 互換性を保つためそのまま使用するケースあり
新規開発や複雑な条件分岐 → 可読性・移植性を考えて CASE式を推奨
DB移行を見据える場合 → CASE式を選択しておくと移植がスムーズ

まとめ

DECODE関数：Oracle独自。簡単な条件分岐向け。古いSQLでよく見かける。
CASE式：SQL標準。複雑な条件も書けて、移植性・可読性に優れる。

👉 今後の開発では CASE式を優先的に利用 するのがおすすめです。

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

Oracle：COALESCE関数の使い方

2023年7月9日管理者コメントする

oracleの独自関数のCOALESCE（コアレス）関数の用途についてメモしておきます。

COALESCE関数とは

COALESCE関数は引数のリストから最初のNULL以外の値を返却するOracle独自関数となります。

NVL2関数との違いは必ずしも引数へ指定するデータ型を全て合わせる必要はないことです。

ただデータの一貫性を保ちたい場合は極力データ型は合わせた方が望ましいです。

サンプルテーブル

サンプルテーブル「CLIENT_ADDRESS」

COALESCE関数の使用例

SQL（クエリー）例

Oracle PL/SQL

SELECT COALESCE(START_DATE, END_DATE, sysdate) FROM CLIENT_ADDRESS;

1

SELECT COALESCE(START_DATE, END_DATE, sysdate) FROM CLIENT_ADDRESS;

上記例ではSTART_DATEがNULLであればEND_DATEの値を、END_DATEもNULLであればsysdateを返却します。
実行結果

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

Oracle：NVL関数とNVL2関数の違い

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

oracleの独自関数としてNVL関数やNVL2関数があります。

知ってると結構便利な関数なので、この２つの関数の違いについて整理しておきます。

NVL関数とは

NVL関数は第１引数がNULLなら第２引数の値（代替値）を返します。

もし第１引数の結果がNULLでなければ、そのまま第１引数の値を返します。

注意点として第１引数と第２引数へは同じデータ型を指定する必要があります。

NVL関数の使用例

サンプルテーブル「CLIENT_ADDRESS」
SQL（クエリー）例
以下の例ではEND_DATEがNULLでない場合はEND_DATEの値を、NULLの場合はsysdateを返却します。

Oracle PL/SQL

SELECT NVL(END_DATE, sysdate) FROM CLIENT_ADDRESS;

1

SELECT NVL(END_DATE, sysdate) FROM CLIENT_ADDRESS;
実行結果

NVL2関数とは

NVL2関数は第１引数がNULLの場合に、第２引数の値を返却し、第１引数がNULLの場合は第３引数の値を返却します。

注意点として第１引数、第２引数、第３引数に指定する値は全て同じデータ型を指定する必要があります。

NVL2関数の使用例

サンプルテーブル「CLIENT_ADDRESS」
SQL（クエリー）例
以下の例ではEND_DATEがNULLでない場合は第２引数のSTART_DATEの値を返却し、END_DATEがNULLの場合は第３引数のsysdateを返却します。

Oracle PL/SQL

SELECT NVL2(END_DATE, START_DATE, sysdate) FROM CLIENT_ADDRESS;

1

SELECT NVL2(END_DATE, START_DATE, sysdate) FROM CLIENT_ADDRESS;
実行結果

ストアドプロシージャ, データベース, ブログ

INSERT文を指定回数分ループして実行する方法

2023年6月12日管理者コメントする

性能試験などであるテーブルに大量データの作成が必要になった場合にINSERT文をループで処理できれば便利！という事で、SQLとロジックを組み合わせたストアドプロシージャでのサンプルプログラムとなります。

ストアドプロシージャ

DECLARE
    -- 変数の宣言
    counter NUMBER := 1;    
	loop_limit NUMBER := 10;
    goods_id NUMBER := 1;    
BEGIN
    WHILE counter <= loop_limit LOOP
    	-- 実行するSQL
		INSERT INTO USER1.GOODS (GOODS_ID,NAME,GOODS_CODE,PRICE) VALUES (goods_id,'パソコン',101,5000);
    	-- インクリメント
		counter := counter + 1; -- カウンタをインクリメント
        goods_id := goods_id + 1; -- GOODS_IDをインクリメント
    END LOOP;
  	-- 正常終了すれば自動的にCOMMITされるため明示的なCOMMITは不要。途中で失敗した場合は全件ロールバックされる
    -- DBクライアントソフトが自動コミットしない場合は明示的なCOMMITが必要
END;

DECLARE

-- 変数の宣言

counter NUMBER := 1;

loop_limit NUMBER := 10;

goods_id NUMBER := 1;

BEGIN

WHILE counter <= loop_limit LOOP

-- 実行するSQL

INSERT INTO USER1.GOODS (GOODS_ID,NAME,GOODS_CODE,PRICE) VALUES (goods_id,'パソコン',101,5000);

-- インクリメント

counter := counter + 1; -- カウンタをインクリメント

goods_id := goods_id + 1; -- GOODS_IDをインクリメント

END LOOP;

-- 正常終了すれば自動的にCOMMITされるため明示的なCOMMITは不要。途中で失敗した場合は全件ロールバックされる

-- DBクライアントソフトが自動コミットしない場合は明示的なCOMMITが必要

END;

上記の例では、loop_limitに指定した件数分「GOODS」テーブルへレコードを追加するサンプルコードです。

上記の例では、loop_limitを10としていますが、必要に応じて任意の数値に変更できます。

DBeaverで上記のストアドプロシージャを実行する場合は「SQLスクリプトを実行する」を選択することで正常に実行されます。

ORA-06502 エラーの意味

よくある原因と解決方法

1. 文字列長の超過

2. 数値変換エラー

3. 不正な日付変換

4. 数値桁数のオーバーフロー

トラブルシューティングのポイント

まとめ

関連記事

PIVOTとは？

サンプルデータ 商品月売上 A1月100 A2月150 B1月200 B2月180

PIVOTのイメージ

商品1月売上2月売上 A100150 B200180

UNPIVOTとは？

各DBMSでの書き方比較

1. SQL Server

2. Oracle

3. PostgreSQL

4. MySQL

DBMS比較表

DBMSPIVOT対応UNPIVOT対応代替手段 SQL Serverネイティブネイティブそのまま使用可 Oracleネイティブネイティブそのまま使用可 PostgreSQLなしなしcrosstab関数 / UNION ALL MySQLなしなしCASE式 + GROUP BY / UNION ALL

まとめ

関連記事

RANKとは？

DENSE_RANKとは？

実務での便利な活用例

1. 売上ランキングを作る

2. 部門別ランキングを作る

3. TOP Nの商品を抽出する

DBMSごとの違い

Oracle Database

PostgreSQL

MySQL

SQL Server (Microsoft)

まとめ

関連記事

1. REGEXPの基本構文

2. 使用できる正規表現の一覧（MySQL準拠）

3. よく使う実践パターン

(1) 先頭・末尾の一致

(2) 日付フォーマット判定

(3) メールアドレス判定

(4) 商品コードの書式検証

(5) 拡張子フィルタ

4. REGEXPのメリットと注意点

メリット

注意点

SQLでのREGEXPサポート比較（DBMSごと）

まとめ

関連記事

BULK COLLECTとは？

基本構文

使用例

FORALLとは？

基本構文

使用例

BULK COLLECTとFORALLを組み合わせる

処理フロー例

パフォーマンス比較

注意点

まとめ

関連記事

はじめに

サブクエリとは？

サブクエリの基本構文

例1：WHERE句でのサブクエリ

例2：IN句とサブクエリ

例3：FROM句でのサブクエリ（派生テーブル）

例4：SELECT句でのサブクエリ

サブクエリを使うときの注意点

まとめ

関連記事

1. DECODE関数とは？

例：部署IDに応じて部署名を返す

2. CASE式とは？

構文（シンプルCASE）

構文（検索CASE）

例：部署IDに応じて部署名を返す

3. DECODEとCASEの比較

4. 実務での使い分けポイント

　関連記事

サンプルデータ

商品月売上

A 1月 100

A 2月 150

B 1月 200

B 2月 180

商品 1月売上 2月売上

A 100 150

B 200 180

DBMS PIVOT対応 UNPIVOT対応代替手段

SQL Server ネイティブネイティブそのまま使用可

Oracle ネイティブネイティブそのまま使用可

PostgreSQL なしなし crosstab関数 / UNION ALL

MySQL なしなし CASE式 + GROUP BY / UNION ALL

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事

　関連記事