PL/SQL の条件分岐について

Question

###前提・実現したいこと
PL/SQL の条件分岐についての質問です。
やりたいのは DB1からDB2のデータ取得です。
DB1.学籍番号番号があって、学籍番号の末尾2桁が00の場合、
00を消して、DB2にセットする（00が無くなるまで、最後の3桁そのまま)
例10101000 の場合 末尾00の消し、101010をセット
1010000 → 101をセット
1000000 → 100をセット

###該当のソースコード
Select case when substr(ID,-2,2) = '00' and length(ID)>3 以下のロジックを教えて頂きたいです。

宜しくお願い致します。

Comments

[Panzer_vor]

丸投げと捉えられかねない内容ですので、
現段階で試したこと、検討していることがあれば追記するようお願い致します。

[UDKKI]

申し訳ございません 携帯環境ですので
Select
case
when substr(ID,-2,2) = '00' and length(ID)>3
以下のロジックを教えて頂きたいです。

[Panzer_vor]

> UDKKIさん
可能でしたら質問文の方にコードを追記して下さいね。

後、確認ですがPL/SQLということはデータベースはOracleで間違いないでしょうか？

[UDKKI]

ORACLEです
宜しくお願い致します。

[退会済みユーザー]

質問するときは携帯環境はやめた方がいいです。
携帯から質問すると確実に情報不足です。

Answer 1

やりたいのは DB1からDB2のデータ取得です。

DB1, DB2 って何ですか？テーブル？インスタンス？

とりあえず、
文字列を入力、結果も文字列とするFUNCTIONで、結果を3桁以上で後ろ2桁が'00'なら後ろ2桁を削る、って作業をLOOPで。

CREATE FUNCTION, LOOP,IF(今回の案件はEXIT WHENかな？) はPL/SQL言語リファレンスに載っています。
丸投げするのではなく、
自分でここまで作れたけどどこが間違っているのでしょうか？って成果を提示して、後からPL/SQLを学ぶ人たちの道しるべになる内容にしたいものです。

Answer 2

すでに解決済のようですが、
質問をする際は、自身の試したことを掲示することを心がけてください。

それが合っていようとずれていようと、
試行錯誤したとわかる質問であれば、
回答側も力になろうと言う心理が働きますし、
回答側もより無駄のない回答が掲示できます。
(今回のケースだと掲示がないままだとSUBSTR(ID, -2, 2)の箇所が回答側でもダブル可能性がありましたよね？)

さて以下は蛇足ですが、
Oracleでも確か正規表現をサポートしていた記憶があるので、
当方でしたらDB2に登録するIDの導出は下記のようなアプローチをとります。
0. DB1.IDのIDを先頭3桁(部分文字列1)と、残りの桁(部分文字列2)で分割(SUBSTRなどで可能)
0. 部分文字列2より文字末尾が「00」の繰り返しパターンに該当するものを空文字に置き換え
0. 部分文字列1と上記でリプレイス処理後の部分文字列を再結合

以下コードのイメージ(変数定義は割愛)

/* 場合によっては最初にIDの長さを見なければならないかも */
vStr1 := SUBSTR(A.ID, 1, 3);
vStr2 := SUBSTR(A.ID, 4);

vStr2 := REGEXP_REPLACE(vStr2, '(0{2})+$','');

vNewID := vStr1 || vStr2;

ただし王道はOrlofskyさんの掲示されている方法かなと思います。
後、興味があれば正規表現についても調べてみてくださいね。

Comments

[Orlofsky]

元筆問者は本当に解決できたのか不安もありますが、正規表現で処理できればLOOPもFUNCTIONも不要ですからシンプルで良いですね。

SQL> SELECT REGEXP_REPLACE('1010000', '(0{2})+$','') FROM DUAL ;

REGEXP
------
101

SQL> SELECT REGEXP_REPLACE('1000000', '(0{2})+$','') FROM DUAL ;

RE
--
1
と頭の3桁は残すって機能が足りません。

[Panzer_vor]

> Orlofskyさん
何かコードのハイライトが崩れてて見にくいかもしれませんが、
頭の3桁を残すという要件に備えて最初の3桁とそれ以外をSUBSTRで切り出してたりします。

多分これでいけるはずなんですが、動かしてはないので確証はなしです^^;

[Orlofsky]

おっと、
>vStr1 := SUBSTR(A.ID, 1, 3);
の部分が抜けていましたね。

SQL> SELECT SUBSTR(A.ID, 1, 3) || REGEXP_REPLACE(ID, '(0{2})+$', '') AS ANS
2 FROM(
3 SELECT '1010000' AS ID FROM DUAL UNION ALL
4 SELECT '1000000' AS ID FROM DUAL
5 ) A ;

ANS
-----------------------------------------------------------------------------
101101
1001

となり、ちょっと違います。

[Panzer_vor]

> Orlofskyさん
度々ご確認いただきありがとうございます。

ただ2つ目のREGEXP_REPLACEをかけている箇所ですが、
こいつはID列の4桁目以降を対象にしております(vStr2の変数を参照)。

ハイライトが見にくくなってるので、直したいところですね。
PL/SQLの場合を上手く表示する方法ってありましたっけ・・・

[Orlofsky]

結局、PL/SQLにしないと正常に動作しませんでした。

SQL> CREATE OR REPLACE FUNCTION FUNC1
2 (
3 I_ID VARCHAR2
4 )
5 RETURN VARCHAR2
6 IS
7 vStr1 VARCHAR2(3) := SUBSTR(I_ID, 1, 3) ;
8 vStr2 VARCHAR2(128) := SUBSTR(I_ID, 4) ;
9 BEGIN
10 RETURN(vStr1 || REGEXP_REPLACE(vStr2, '(0{2})+$', '')) ;
11 END FUNC1 ;
12 /

ファンクションが作成されました。

SQL> COLUMN ANS FORMAT A10
SQL> SELECT FUNC1('1010000') AS ANS FROM DUAL ;

ANS
----------
101

SQL> SELECT FUNC1('1000000') AS ANS FROM DUAL ;

ANS
----------
100

SQL>

正規表現の勉強になりました。ありがとうございます。

[コメント]はコードを見やすく表示できません。[回答]なら</>でコードをくくれば良いのですが。1年以上前からteratail 側に改善を要望しているのですが、遅々として進みませんね。どこか使いやすい掲示板があると良いのですが。

[Panzer_vor]

> Orlofskyさん
REGEXP_REPLACEの中にSUBSTR関数などをネストできない等の制約はなかったはずなので、
SQL単体でも表現できそうな気はします。

以下のSQLでもダメそうでしょうかね？
SELECT SUBSTR(A.ID, 1, 3) || REGEXP_REPLACE(SUBSTR(A.ID, 4), '(0{2})+$', '') AS ANS
FROM(
SELECT '1010000' AS ID FROM DUAL
UNION ALL
SELECT '1000000' AS ID FROM DUAL
) A

[Orlofsky]

SQL> SELECT SUBSTR(A.ID, 1, 3) || REGEXP_REPLACE(SUBSTR(ID, 4), '(0{2})+$', '') AS ANS
2 FROM(
3 SELECT '1010000' AS ID FROM DUAL UNION ALL
4 SELECT '1000000' AS ID FROM DUAL
5 ) A ;

ANS
----------
101
100

でうまくいきました。お手数かけてすみません。(_ _);

[Panzer_vor]

> Orlofskyさん
ご確認いただきありがとうございます。

無事動作したとのことで何よりです。
(デマ回答だとそのまま残しておく訳にはいかないので^^;)

正規表現は当方もそこまで深い知識はありませんが、
パターンマッチング系では威力を発揮するので、
頭の片隅に入れておくと良いかと思われます。

記載コード量が減るのは、
メリットとしては捨てがたいですしね。