異種 DB 移行（SQL Server ⇒ Oracleなど）において問題になりやすいストアドプロシージャーの移行（プログラム修正）について、ChatGPT に任せられないかと思い、簡単なケースで試してみたので結果を記しておきます。
1. はじめに
長い期間システムを運用していると、コストや処理パフォーマンス、機能、クラウド移行などの理由から利用している DBMS 製品・サービスをバージョンアップではなく他の製品・サービスに変更するということが少なからずあります。
こういうのを私は異種 DB 移行（厳密には RDBMS 以外も含みますが、ここでは RDBMS に限ります）と呼んでいますが、
- データ移行
- プログラム修正（外部アプリとストアド両方）
- 元新比較テスト
- 処理パフォーマンス確認
- 運用の見直し（監視、バックアップ、セキュリティ対策など）
- コスト見積
などなど、中々に大変なタスクを実行していく必要があります。
その中でも、ストアドプロシージャーに関しては RDBMS 独自の言語で記述されていることも多く、ストアドの移行先 DB 向けの変換は難易度や手間の点で課題になりやすいです。（外部アプリケーションに関しても問題になりやすいのですが、方言があるとは言え SQL が中心であることや RDBMS の違いを多少吸収するライブラリもあるので、ストアドよりかは多少マシかと）
今回はそのストアドの変換というタスクを ChatGPT に任せられないかと思い立ち、まずは基本的で簡単な SQL Server における T-SQL のストアドを Oracle の PL/SQL にコード変換してみることを試みてみました。
以下、但し書きです。
- 私の興味対象は DWH におけるデータパイプラインとしてのストアドなので、試すコードもそちら寄りです。
- 今回は SQL Server と Oracle の SQL の変換というより、T-SQL と PL/SQL の手続き型言語としての要素の変換を中心に見ていきます。
- 簡単な動作確認は行っていますが、厳密な単体テストをしているわけではないので、うまく変換できていない部分を見落としているかもしれません。
2. 環境
動作確認は以下の DBMS で行いました。
- 変換元：SQL Sever 2022 (on Oracle Linux 8.8)
- 変換先：Oracle Database 23c Free (on Oracle Linux 8.8)
あと、以下のような簡単な売上テーブルを変換対象のストアドでは利用しています。
create table sales (
sales_id integer,
customer_id integer not null,
customer_name varchar(100) not null,
product_id integer not null,
product_name varchar(100) not null,
sales_date date not null,
sales_amount integer not null,
constraint pk_sales primary key(sales_id)
);
insert into sales values
(1, 101, 'Adachi Ichiro', 201, 'PS5', '2023-01-10', 45000),
(2, 101, 'Adachi Ichiro', 202, 'XBox', '2023-01-10', 35000),
(3, 102, 'Baba Jiro', 201, 'PS5', '2023-02-01', 46000),
(4, 103, 'Chiyoda Saburo', 202, 'XBox', '2023-02-28', 34000),
(5, 104, 'Daimon Shiro', 203, 'Wii', '2023-02-28', 18000),
(6, 105, 'Endo Goro', 203, 'Wii', '2023-03-01', 18500);
ChatGPT に関してはバージョン August 3, 2023 の GPT-4 モデルを利用しています。また ChatGPT にはシンプルに以下の命令の後に T-SQL のコードをコード引用ブロック付きで渡しています。
Tranlate next SQL Server T-SQL function( or proceudure) to Oracle PL/SQLfunction( or proceudure).
3. コード変換結果
3-1. スカラー関数
まずは、引数で指定した日付の売上テーブルの件数を返す簡単なスカラー関数を試してみます。
-- 01_スカラー関数(T-SQL)
create function count_sales(
@sales_date date
)
returns integer
as
begin
declare @cnt integer;
select @cnt = count(*) from sales where sales_date = @sales_date;
return @cnt;
end;
これを ChatGPT に変換させると以下になりました。
-- 01_スカラー関数(PL/SQL)
CREATE OR REPLACE FUNCTION count_sales(p_sales_date DATE)
RETURN NUMBER
IS
v_cnt NUMBER;
BEGIN
SELECT COUNT(*)
INTO v_cnt
FROM sales
WHERE sales_date = p_sales_date;
RETURN v_cnt;
EXCEPTION
WHEN OTHERS THEN
RETURN 0; -- Return 0 or handle the exception as needed
END count_sales;
/
create function を create or replace function に直してくれたり、例外ブロックを勝手につけたりしていますが（後者は本当は良くないと思うのですが）、概ね想定通り動作するコードになっています。
１つポイントを挙げるとすれば、T-SQL では変数は @ を頭に付けることになっているため、変数名とカラム名との名前の重複が許されるのですが、PL/SQL では接頭辞はないため重複が許されません。この問題を解決するために、パラメータ変数には p_、ローカル変数には v_ という接頭辞をつけていますね（これは PL/SQL のよくあるコーディングルールと言えばそうなのですが）。
3-2. 複数 DML の順次実行
次に、売上テーブルから商品ごと、日ごとの集計テーブルのデータを順に作成するストアドプロシージャーを試してみます。集計処理だけではなく、処理件数を処理ログテーブル（proc_log）に格納しています。
-- 02_複数DMLの順次実行(T-SQL)
create procedure summary_sales
as
begin
insert into sales_summary_by_product
select
product_name,
sum(sales_amount)
from
sales
group by
product_name
;
insert into proc_log values (
CURRENT_TIMESTAMP,
OBJECT_NAME(@@PROCID),
'sales_summary_by_product',
@@ROWCOUNT
);
insert into sales_summary_by_sales_date
select
sales_date,
sum(sales_amount)
from
sales
group by
sales_date
;
insert into proc_log values (
CURRENT_TIMESTAMP,
OBJECT_NAME(@@PROCID),
'sales_summary_by_sales_date',
@@ROWCOUNT
);
end;
これを ChatGPT に変換させると以下になりました。
-- 02_複数DMLの順次実行(PL/SQL)
CREATE OR REPLACE PROCEDURE summary_sales IS
v_rows_inserted NUMBER;
BEGIN
INSERT INTO sales_summary_by_product
SELECT
product_name,
SUM(sales_amount)
FROM
sales
GROUP BY
product_name;
-- Get number of rows inserted
v_rows_inserted := SQL%ROWCOUNT;
INSERT INTO proc_log (timestamp_column, proc_name, table_name, row_count)
VALUES (
SYSTIMESTAMP,
'summary_sales', -- This is a hardcoded procedure name, based on your example
'sales_summary_by_product',
v_rows_inserted
);
INSERT INTO sales_summary_by_sales_date
SELECT
sales_date,
SUM(sales_amount)
FROM
sales
GROUP BY
sales_date;
-- Get number of rows inserted
v_rows_inserted := SQL%ROWCOUNT;
INSERT INTO proc_log (timestamp_column, proc_name, table_name, row_count)
VALUES (
SYSTIMESTAMP,
'summary_sales', -- This is a hardcoded procedure name, based on your example
'sales_summary_by_sales_date',
v_rows_inserted
);
COMMIT; -- If you want to commit changes in the procedure
EXCEPTION
WHEN OTHERS THEN
ROLLBACK; -- If you want to rollback in case of any error
RAISE; -- Reraise the caught exception
END summary_sales;
/
例外ブロックや COMMMIT が勝手についていますが、概ね想定通りに動きます。（例外ブロックや COMMIT はプロンプトで含めないように指示しておけば、問題なく除去できることは確認しています）
元の T-SQL コードでは @@PROCID や @ROWCOUNT といったシステム変数を利用していますが、一応それを考慮してコードを変換できています。@@PROCID に関しては該当するPL/SQLの仕組みがないのでハードコードで対応してくれています。
あと、proc_log テーブルへの INSERT 文について元の T-SQL のコードは横着して列指定をしなかったのですが、変換後の PL/SQL コードでは推定された列指定がついています（ChatGPT の回答でもその点は指摘されます）。実は推定された列名と実際の列名は違うのですが、これは元のコードが悪いですね（移行においては元のコードが悪いことが原因で変換がうまう行かないケースはよくあるので、無視できる問題ではないのですが）
3-3. IF 文
次に、引数で渡した売上レコードついて、既に同じ ID が登録済みなら更新、未登録なら挿入をするという条件分岐（IF 文）を含む処理を試してみます。
create procedure upsert_sales (
@sales_id integer,
@customer_id integer,
@customer_name varchar(100),
@product_id integer,
@product_name varchar(100),
@sales_date date,
@sales_ammount integer
)
as
begin
declare @cnt integer;
select @cnt = count(*) from sales where sales_id = @sales_id;
if @cnt > 0
update sales
set
customer_id = @customer_id,
customer_name = @customer_name,
product_id = @product_id,
product_name = @product_name,
sales_date = @sales_date,
sales_amount = @sales_ammount
where
sales_id = @sales_id
;
else
insert into sales values (
@sales_id,
@customer_id,
@customer_name,
@product_id,
@product_name,
@sales_date,
@sales_ammount
);
end;
これを ChatGPT に変換させると以下になりました。
CREATE OR REPLACE PROCEDURE upsert_sales (
p_sales_id NUMBER,
p_customer_id NUMBER,
p_customer_name VARCHAR2(100),
p_product_id NUMBER,
p_product_name VARCHAR2(100),
p_sales_date DATE,
p_sales_amount NUMBER
) AS
BEGIN
IF EXISTS (SELECT 1 FROM sales WHERE sales_id = p_sales_id) THEN
UPDATE sales
SET
customer_id = p_customer_id,
customer_name = p_customer_name,
product_id = p_product_id,
product_name = p_product_name,
sales_date = p_sales_date,
sales_amount = p_sales_amount
WHERE
sales_id = p_sales_id;
ELSE
INSERT INTO sales (
sales_id,
customer_id,
customer_name,
product_id,
product_name,
sales_date,
sales_amount
)
VALUES (
p_sales_id,
p_customer_id,
p_customer_name,
p_product_id,
p_product_name,
p_sales_date,
p_sales_amount
);
END IF;
END upsert_sales;
/
残念ながら、生成された PL/SQL コードは以下の２点でコンパイルが通りません。
- パラメーター変数
p_customer_nameとp_product_nameの宣言で長さ指定がされている。（PL/SQL では仮パラメーターの宣言において数字型に対する桁数・精度や文字列型に対する長さの指定は不可） - IF 文で利用できないEXISTSを利用している。（この構文 T-SQL では使えるんですね）
１番目の問題に関しては、以下のような指示を追加ですると解消します。あと、この指示でなぜか２番目も同時に解消しました。
Regenerate the code, recognising that in PL/SQL the specification of the number of digits/precision of a number and the length of a string in function and procedure parameter declarations is invalid.
3-4. カーソルを用いたループ
オプトアウトの要求があった顧客の ID をカーソルで取得し、ループで１顧客ずつ売上テーブルから削除する処理を試してみます。（本当はループ使うのは非効率なんですが、そこは機能確認ということでご容赦ください）
-- 04_カーソルループ(T-SQL)
create procedure delete_opt_out_sales
as
begin
declare @user_id integer;
declare opt_out_cursor cursor for select customer_id from opt_out_request;
open opt_out_cursor;
fetch opt_out_cursor into @user_id;
while @@fetch_status = 0
begin
delete sales where customer_id = @user_id;
fetch opt_out_cursor into @user_id;
end
close opt_out_cursor;
deallocate opt_out_cursor;
end;
これを ChatGPT に変換させると以下になりました。
-- 04_カーソルループ(PL/SQL)
CREATE OR REPLACE PROCEDURE delete_opt_out_sales AS
v_user_id NUMBER;
CURSOR opt_out_cursor IS
SELECT customer_id FROM opt_out_request;
BEGIN
OPEN opt_out_cursor;
LOOP
FETCH opt_out_cursor INTO v_user_id;
EXIT WHEN opt_out_cursor%NOTFOUND;
DELETE FROM sales WHERE customer_id = v_user_id;
END LOOP;
CLOSE opt_out_cursor;
END delete_opt_out_sales;
/
こちらは問題なく動きます。
T-SQL はカーソル利用の文法が若干イケていないのですが、ストレートに変換してくれていますね。できれば、カーソル FOR ループを使ってシンプルに書き直して欲しいところではあるのですが。
3-5. 例外処理
次は売上テーブルに引数で渡されたレコードを登録するのですが、その際に一意制約と NOT NULL 制約に関係する例外が発生した場合、エラーメッセージを出力する処理を試します。
-- 05_例外処理(T-SQL)
create procedure insert_sales (
@sales_id integer,
@customer_id integer,
@customer_name varchar(100),
@product_id integer,
@product_name varchar(100),
@sales_date date,
@sales_ammount integer
)
as
begin
begin try
begin transaction
insert into sales values (
@sales_id,
@customer_id,
@customer_name,
@product_id,
@product_name,
@sales_date,
@sales_ammount
);
commit transaction;
end try
begin catch
rollback transaction;
if error_number() = 2627
begin
print N'一意制約違反です'
end
else if error_number() = 515
begin
print N'NOT NULL制約違反です'
end
else
begin
print N'その他のエラーです'
end
end catch;
end;
これを ChatGPT に変換させると以下になりました。
-- 05_例外処理(PL/SQL)
CREATE OR REPLACE PROCEDURE insert_sales (
p_sales_id INTEGER,
p_customer_id INTEGER,
p_customer_name VARCHAR2(100),
p_product_id INTEGER,
p_product_name VARCHAR2(100),
p_sales_date DATE,
p_sales_ammount INTEGER
) IS
-- Declare exception for unique constraint violation
e_unique_violation EXCEPTION;
PRAGMA EXCEPTION_INIT(e_unique_violation, -1); -- ORA-00001
-- Declare exception for NOT NULL constraint violation
e_notnull_violation EXCEPTION;
PRAGMA EXCEPTION_INIT(e_notnull_violation, -1400); -- ORA-01400
BEGIN
-- Start the transaction
SAVEPOINT start_transaction;
INSERT INTO sales VALUES (
p_sales_id,
p_customer_id,
p_customer_name,
p_product_id,
p_product_name,
p_sales_date,
p_sales_ammount
);
EXCEPTION
WHEN e_unique_violation THEN
ROLLBACK TO start_transaction;
DBMS_OUTPUT.PUT_LINE('一意制約違反です');
WHEN e_notnull_violation THEN
ROLLBACK TO start_transaction;
DBMS_OUTPUT.PUT_LINE('NOT NULL制約違反です');
WHEN OTHERS THEN
ROLLBACK TO start_transaction;
DBMS_OUTPUT.PUT_LINE('その他のエラーです');
END insert_sales;
/
3-3. と同様に仮パラメータの宣言で文字列の長さを指定しているのでこのままではコンパイルは通りませんが、それは追加命令で解決できます。
SQL Server の 2627, 515 エラーを Oracle の ORA-00001, ORA-01400 エラーに変換できているのは結構驚きですね。あと、rollback の位置も調整できているようですね。
3-6. 別ストアドの呼び出し
最後に、3-5. で作成したストアドを呼び出すストアドを試してみます。
-- 06_他のストアドの呼び出し(T-SQL)
create procedure check_and_insert_sales (
@sales_id integer,
@customer_id integer,
@customer_name varchar(100),
@product_id integer,
@product_name varchar(100),
@sales_date date,
@sales_ammount integer
)
as
begin
declare @cnt integer
select @cnt = count(*) from sales where sales_id = @sales_id;
if @cnt > 0
begin
print 'sales_idが重複します'
return;
end
execute dbo.insert_sales
@sales_id,
@customer_id,
@customer_name,
@product_id,
@product_name,
@sales_date,
@sales_ammount
;
end
これを ChatGPT に変換させると以下になりました。
-- 06_他のストアドの呼び出し(PL/SQL)
CREATE OR REPLACE PROCEDURE check_and_insert_sales (
p_sales_id INTEGER,
p_customer_id INTEGER,
p_customer_name VARCHAR2(100),
p_product_id INTEGER,
p_product_name VARCHAR2(100),
p_sales_date DATE,
p_sales_ammount INTEGER
) AS
v_cnt INTEGER;
BEGIN
-- Check for duplicate sales_id
SELECT COUNT(*)
INTO v_cnt
FROM sales
WHERE sales_id = p_sales_id;
IF v_cnt > 0 THEN
DBMS_OUTPUT.PUT_LINE('sales_idが重複します');
RETURN;
END IF;
-- Call the insert_sales procedure (assuming it exists in the same schema)
insert_sales(
p_sales_id,
p_customer_id,
p_customer_name,
p_product_id,
p_product_name,
p_sales_date,
p_sales_ammount
);
EXCEPTION
WHEN OTHERS THEN
RAISE;
END check_and_insert_sales;
/
仮パラメーターの宣言で長さ指定が残っているのは先と同じですが、そこを対応すれば問題なく動作します。
4. まとめ
簡単な T-SQL ストアドを PL/SQL に変換するタスクを ChatGPT にやらせてみましたが、思ったよりは動作するコードを生成してくれました。ただ、今回分かった範囲では以下のようなプロンプトは足しておいた方が良いようです。
- 仮パラメーターの宣言で桁数・精度や長さの指定はしない
- 列指定のない INSERT 文に関しては列名を勝手に推測して列指定しない
- 元のコードに含まれていない例外処理やトランザクション処理は勝手に追加しない
ただ、これらを自然言語で指示しておけば回避できるというのは ChatGPT のメリットですね。実際に変換ツールを作るとなると構文解析した上でそのようなルールを実装しないといけないので。
ここまで、ちょっと期待させる内容を記載していますが、実プロジェクトで利用しようとすると、
- ChatGPT への指示の都度、若干コードスタイルや変換方法が異なる。
- 長いストアドを変換したい場合、トークン数上限に抵触する。
という課題があり、実適用はまだ難しいかなと思っています。特に後者について、ストアドって大体がスパゲティー化していて数千行とか場合によっては１万行超えるコードとかもザラにあったりするので、変換できるように小さい単位に分割・リファクタリングするなどの事前準備をする必要があるかなと思っています。あと、トークン上限に収まったとしても、そここそ長いストアドのコードは正しく変換できるかは確認が必要かなと思います。
5. おまけ
ここまで T-SQL から PL/SQL への変換を扱ってきましたが、実は私が本当にやりたかった事は T-SQL から Snowflake ストアドへの変換です。さすがに Snowflake ストアドはそれほど学習できていないだろうからということでまず PL/SQL を選びましたが。
3-1. と 3-2. の T-SQL を Snowflake 用に変換してもらうと、以下になります。答え合わせは改めて。
- 3-1. スカラー関数
CREATE OR REPLACE FUNCTION count_sales(sales_date DATE)
RETURNS INTEGER
AS
'
SELECT COUNT(*) FROM sales WHERE sales_date = $1
';
- 3-2. DMLの順次実行
CREATE OR REPLACE PROCEDURE summary_sales()
RETURNS STRING
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS
$$
try {
// Insert into sales_summary_by_product
var sql_command1 = "INSERT INTO sales_summary_by_product " +
"SELECT product_name, SUM(sales_amount) " +
"FROM sales " +
"GROUP BY product_name";
var statement1 = snowflake.createStatement({sqlText: sql_command1});
statement1.execute();
var rowCount1 = statement1.getRowCount();
// Log to proc_log
var sql_log1 = "INSERT INTO proc_log " +
"(CURRENT_TIMESTAMP(), 'summary_sales', 'sales_summary_by_product', ?)";
var statement_log1 = snowflake.createStatement({sqlText: sql_log1, binds: [rowCount1]});
statement_log1.execute();
// Insert into sales_summary_by_sales_date
var sql_command2 = "INSERT INTO sales_summary_by_sales_date " +
"SELECT sales_date, SUM(sales_amount) " +
"FROM sales " +
"GROUP BY sales_date";
var statement2 = snowflake.createStatement({sqlText: sql_command2});
statement2.execute();
var rowCount2 = statement2.getRowCount();
// Log to proc_log
var sql_log2 = "INSERT INTO proc_log " +
"(CURRENT_TIMESTAMP(), 'summary_sales', 'sales_summary_by_sales_date', ?)";
var statement_log2 = snowflake.createStatement({sqlText: sql_log2, binds: [rowCount2]});
statement_log2.execute();
return "Success";
} catch(err) {
return "Failed: " + err;
}
$$;
【以下、2023/9/25 追記】
上の T-SQL ストアドを Snowflake ストアドに変換したものは残念ながらコンパイルが通りませんでした。主に問題点は３点あります。
- パラメーターの参照方法
- 3-1. のケースでパラメーターを
$1で参照していますが、これは Unix シェルでの方法であり、Snowflake では使えません。 - Snowflake において
$1は「列の1番目」を指します。そのため、今回はsales.sales_idと同義となり、where 句で異なるデータ型を比較（date 型と integer 型）しているためコンパイルエラーになります。 - 今回は偶々データ型が食い違っていたのでコンパイルエラーになりましたが、もし一致していたらコンパイルは通るけど結果がおかしいストアドになっていたはずです。
- 3-1. のケースでパラメーターを
- INSERT 文に VALUES 句がない
- 3-2. における
proc_logテーブルへの１行挿入で values 句がないためコンパイルエラーになります。
- 3-2. における
- DML 文の処理件数取得
- DML 文の処理件数を
getRowCount()で取得しようとしていますが、これは SELECT 文の結果件数を返す API です。 - DML 文に対して
getRowCount()を実行すると 1 が返ってきます。これは、Snowflake では DML 文の結果セットは処理件数を含む１行だからです。 - DML 文の処理件数を取得するためには、
getNumRowsAffected()を利用する必要があります。
- DML 文の処理件数を
この簡単なストアドでこれだけ問題があると、Snowflake ストアドへの変換はかなり無理っぽいですね。T-SQL や PL/SQL と比べるとやはり学習データ量の差なのでしょうか。