PL/SQLの2次元配列の作り方について

Question

PL/SQL で、テーブルの型に合わせた２次元配列を作りたいのですが、

普通、多次元配列の作り方は、

TYPE tElement IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
TYPE tSquare IS TABLE OF tElement INDEX BY BINARY_INTEGER;
 
vSquare	tSquare;
vSquare(0)(0) := 0;
vSquare(0)(1) := 1;
...

出典https://www.shift-the-oracle.com/plsql/collection/multi-dimension-array.html

のように書きますが、ここで、データ型を、あるテーブルAと同じにしたいのです。

やってみたことは、1行目を書き換えて、

TYPE tElement IS TABLE OF MY_TABLE_A%ROWTYPE INDEX BY BINARY_INTEGER;
TYPE tSquare IS TABLE OF tElement INDEX BY BINARY_INTEGER;
 
vSquare	tSquare;
vSquare(0)(0) := 1000;
vSquare(0)(1) := 'ABC';
...

CREATE TABLE MY_TABLE_A (
         num        NUMBER(5) NOT NULL,
         name       VARCHAR2(15) NOT NULL,
         score1     NUMBER(10) NOT NULL,
         score2     NUMBER(10) NOT NULL,
         score3     NUMBER(10) NOT NULL,
         score4     NUMBER(10) NOT NULL,
         score5     NUMBER(10) NOT NULL,
         score6     NUMBER(10) NOT NULL,
         score7     NUMBER(10) NOT NULL,
         score8     NUMBER(10) NOT NULL,
         score9     NUMBER(10) NOT NULL
);

としてみたことです。
しかし、どこでおかしくなっているのかわかりませんが、vSquareのどこかのデータを取得しようとすると、「型が違います」というエラーで中断されます。

たとえば、

IF vSquare(0)(10) = 0 THEN /* ここで型エラー　TO_CHAR(0)でも同様 */
    /* 処理 */
END IF; 

のようなことをすると、たちまち落ちます。配列の要素が空だったからではないかと思い、初期値をいれておいてもダメでした。

ところで、最初のサンプルのコード元は、配列の要素を

 /* 抜粋 */
		FOR i in 1 .. 9 LOOP
			FOR j in 1..9 LOOP
				P_ARRAY(i)(j) := i * j;
			END LOOP;
		END LOOP;

と、1から指定して、0から指定していません、計算の都合上からでしょうか。それともPL/SQLの仕様でしょうか。

Comments

[Orlofsky]

PL/SQLです。大文字小文字はきちんと区別して書けた方が良いです。

[Orlofsky]

URLは https://teratail.com/help/question-tips#questionTips3-7 の [リンク] で [リンク先の概要](http...) に修正してください。

[teefpc]

見出しとリンクを修正しました

[Orlofsky]

... MY_TABLE_A%ROWTYPE

質問に MY_TABLE_A の CREATE TABLE を追記してください。

[Orlofsky]

宣言や実行文の終わりは ; です。抜けているところがあります。

[teefpc]

テーブル定義を追加しました。

[teefpc]

コードを修正しました。

Answer 1

vSquare(0)(0) が MY_TABLE_A%ROWTYPE なので

SQL> DECLARE
  2  TYPE tElement   IS TABLE OF MY_TABLE_A%ROWTYPE INDEX BY BINARY_INTEGER;
  3  TYPE tSquare    IS TABLE OF tElement INDEX BY BINARY_INTEGER;
  4  vSquare     tSquare;
  5  BEGIN
  6      vSquare(0)(0) := 1000;
  7      vSquare(0)(1) := 'ABC';
  8  END;
  9  /
vSquare(0)(0) := 1000;
                 *
行6でエラーが発生しました。:
ORA-06550: 行6、列18:
PLS-00382: 式の型が正しくありません。
ORA-06550: 行6、列1:
PL/SQL: Statement ignored
ORA-06550: 行7、列18:
PLS-00382: 式の型が正しくありません。
ORA-06550: 行7、列1:
PL/SQL: Statement ignored 

となります。

SQL> DECLARE
  2  TYPE tElement   IS TABLE OF MY_TABLE_A%ROWTYPE INDEX BY BINARY_INTEGER;
  3  TYPE tSquare    IS TABLE OF tElement INDEX BY BINARY_INTEGER;
  4  vSquare     tSquare;
  5  BEGIN
  6      vSquare(0)(0).NUM   := 1000;
  7      vSquare(0)(1).NAME  := 'ABC';
  8  END;
  9  /

PL/SQLプロシージャが正常に完了しました。

こうなっていれば実行できます。

Comments

[teefpc]

ありがとうございました。

Answer 2

PL/SQLに関する質問に対する回答

PL/SQLでは無名ブロックでの宣言は

DECLARE -- 宣言するものがあれば
    ここで宣言を記述 ;
BEGIN
    ここで実行文を記述 ;
EXCEPTION -- 例外があれば記述
    ....
END ;
/

ストアドでの宣言は

CREATE ...
IS
    ここで宣言を記述 ;
BEGIN
以下略

PL/SQLの入門書を1冊買ってきて勉強されては？
わたしは有償で出向いて教えることもあります。その方が早くまともなコードを書けるようになるのでトータルでは安上がりです。

Comments

[teefpc]

ありがとうございます。ということは、配列の使い方は合っているということですね。

[Orlofsky]

いいえ、PL/SQLでは配列の添字は 1 からで 0 はありません。

[Orlofsky]

テーブルの用途がわかりませんが、

SQLをシンプルにパフォーマンス良く実行できるように、データベースのテーブルは
データベースの正規化 https://oss-db.jp/measures/dojo_info_04.shtml
に沿って設計します。通常、第3正規化まで行います。第1正規化で繰り返し(今回は配列)を排除します。
通常、第3正規化まで行います。

[teefpc]

質問中のテーブルは処理の流れの中で一時的に作られるワークテーブルです。或るトランザクションテーブルのデータをピボットテーブルのように集計しながら縦横を入れ替えて作り変えます。そのためPL/SQL内で、出力するワークテーブル用のバッファを作る必要がありました。

[Orlofsky]

https://www.shift-the-oracle.com/sql/aggregate-functions/listagg.html が使える案件かも？

[teefpc]

ありがとうございました。こちらも参考にさせていただきます！