How to initialize a matrix in plsql

问题:

I've tried to Map a table with associative arrays , but i can't figure out how to initialize it

here is an example :

TYPE RecType IS RECORD
 (
  value1   NUMBER,
  value2   NUMBER,
  value3   NUMBER
 );
 TYPE TblType IS TABLE OF RecType INDEX BY PLS_INTEGER;
 TYPE TblOfTblType IS TABLE OF TblType INDEX BY PLS_INTEGER;
 matrix   TblOfTblType;

Now when i tried to initialize the matrix like this :

FOR i IN matrix.FIRST .. matrix.LAST LOOP
  FOR j IN matrix (i).FIRST .. matrix (i).LAST LOOP
       matrix(i)(j) := NULL;  
  END LOOP;
END LOOP;

It doesn't work !I also tried

matrix := TblOfTblType()();

it shows the following error :

PLS-00363 expression 'Matrix' cannot be used as an assignment target .

回答1:

You do not need to use an associative array - a collection will work.

DECLARE
  TYPE RecType IS RECORD
  (
    value1   NUMBER,
    value2   NUMBER,
    value3   NUMBER
  );
  TYPE TblType IS TABLE OF RecType;
  TYPE TblOfTblType IS TABLE OF TblType;
  matrix   TblOfTblType := TblOfTblType();
BEGIN
  matrix.EXTEND(3);
  FOR i IN 1 .. matrix.COUNT LOOP
    matrix(i) := TblType();
    matrix(i).EXTEND(4);
    FOR j IN 1 .. matrix(i).COUNT LOOP
      matrix(i)(j).value1 := i;
      matrix(i)(j).value2 := j;
      matrix(i)(j).value3 := DBMS_RANDOM.VALUE;
    END LOOP;
  END LOOP;

  FOR i IN 1 .. matrix.COUNT LOOP
    FOR j IN 1 .. matrix(i).COUNT LOOP
      DBMS_OUTPUT.PUT( '[' || matrix(i)(j).value1
                    || ',' || matrix(i)(j).value2
                    || ',' || matrix(i)(j).value3 || ']' || CHR(11) );
    END LOOP;
    DBMS_OUTPUT.NEW_LINE;
  END LOOP;
END;
/

回答2:

Although I agree with @MTO's approach, the error from your loop approach is because you are trying to refer to FIRST and LAST on an empty table, and they both evaluate to null at that point. You are effectively trying to do:

FOR i IN null .. null LOOP

which gets the same ORA-06502: PL/SQL: numeric or value error as your original FOR loop.

You haven't defined anywhere what the dimensions of the matrix should be. You need to do that somewhere to be able to 'initialise' it, e.g. with fixed values to match MTO's and your type declarations:

DECLARE
  TYPE RecType IS RECORD
  (
    value1   NUMBER,
    value2   NUMBER,
    value3   NUMBER
  );
  TYPE TblType IS TABLE OF RecType INDEX BY PLS_INTEGER;
  TYPE TblOfTblType IS TABLE OF TblType INDEX BY PLS_INTEGER;
  matrix   TblOfTblType;
BEGIN
  FOR i IN 1 .. 3 LOOP
    FOR j IN 1 .. 4 LOOP
      matrix(i)(j) := null;
    END LOOP;
  END LOOP;
END;
/

PL/SQL procedure successfully completed.

To populate with non-null values you woudl need a separate record variable that you populate and then assign tot he matrix position:

  FOR i IN 1 .. 3 LOOP
    FOR j IN 1 .. 4 LOOP
      rec.value1 := i;
      rec.value2 := j;
      rec.value3 := DBMS_RANDOM.VALUE;
      matrix(i)(j) := rec;
    END LOOP;
  END LOOP;

or you could target specific matrix elements without a loop if you're getting those form somewhere else. And you can print it out the same way MTO showed too, or with FIRST and LAST, which are now valid:

  FOR i IN matrix.FIRST .. matrix.LAST LOOP
    FOR j IN matrix(i).FIRST .. matrix(i).LAST LOOP
      DBMS_OUTPUT.PUT( '[' || matrix(i)(j).value1
                    || ',' || matrix(i)(j).value2
                    || ',' || matrix(i)(j).value3 || ']' || CHR(11) );
    END LOOP;
    DBMS_OUTPUT.NEW_LINE;
  END LOOP;

回答3:

CREATE OR REPLACE TYPE RecType AS OBJECT (
  value1   NUMBER,
  value2   NUMBER,
  value3   NUMBER
 );
/   

CREATE OR REPLACE TYPE TblType IS TABLE OF RecType;
/

CREATE OR REPLACE TYPE TblOfTblType IS TABLE OF TblType;
/

DECLARE
     matrix   TblOfTblType;   
       rec1   RecType;
       rec2   TblType; 
BEGIN
    rec1 := RecType(1,2,3);
    rec2 := TblType(rec1);   
    matrix := TblOfTblType(rec2);
    dbms_output.put_line(matrix(1)(1).value2);
END;