plsql_varray_自定義type之測試小例_user_types

---varray類似於index by ,但元素個數有限,元素引用同index by

SQL> r
  1  declare
  2  type table_type is varray(4)  of number;
  3  table1 table_type;
  4  begin
  5  table1:=table_type(1,2,3);
  6  dbms_output.put_line('varary total number: '||table1.count);
  7  for i in 1..table1.count loop
  8  dbms_output.put_line(table1(i));
  9  end loop;
 10* end;
varary total number: 3
1
2
3

PL/SQL procedure successfully completed.

 

 

 

SQL> select owner,type_name,attributes,methods from dba_types where type_name like 'PL%';--用dba_types查plsql引用的資料型別

OWNER                          TYPE_NAME                      ATTRIBUTES    METHODS
------------------------------ ------------------------------ ---------- ----------
                               PL/SQL BINARY INTEGER                   0          0
                               PL/SQL BOOLEAN                          0          0
                               PL/SQL COLLECTION                       0          0
                               PL/SQL LONG                             0          0
                               PL/SQL LONG RAW                         0          0
                               PL/SQL NATURAL                          0          0
                               PL/SQL NATURALN                         0          0
                               PL/SQL PLS INTEGER                      0          0
                               PL/SQL POSITIVE                         0          0
                               PL/SQL POSITIVEN                        0          0
                               PL/SQL RECORD                           0          0

OWNER                          TYPE_NAME                      ATTRIBUTES    METHODS
------------------------------ ------------------------------ ---------- ----------
                               PL/SQL REF CURSOR                       0          0
                               PL/SQL ROWID                            0          0
                               PL/SQL STRING                           0          0

14 rows selected.
 

 

conn scott/system
create type zxy_type as object(x number,y number);--檢視user|dba_objects可查出此定義的資料型別，此後你在plsql或者在表中可引用此資料型別

SQL> r
  1  declare
  2  type new_type is varray(10) of zxy_type; --在plsql中引用以上定義的type zxy_type，請注意採用了varray（說明type可以巢狀定義引用,此處就是varray引用了另一個型別type zxy_type)
  3  zxy_type_1 new_type:=new_type();--初始化變數zxy_type_1(以它的型別例項化）
  4  new_child zxy_type;--定義zxy_type型別的變數
  5  value_child zxy_type;--定義另一個zxy_type型別的變數
  6  begin
  7  value_child:=zxy_type(6,6);--提供value_child一個值
  8  zxy_type_1:=new_type(zxy_type(2,3),zxy_type(4,5),zxy_type(7,8),value_child);--為變數（複合）提供一系列值,注意最後一個slot元素的值是6,6
  9  for i in 1..zxy_type_1.count loop  --採用count（count是特殊變數型別的元素個數）
 10  new_child:=zxy_type_1(i);--為變數型別用for loop進行迴圈提供值
 11  dbms_output.put_line('new_child''s x is '||new_child.x||' new_child''s y is '||new_child.y);--列印顯示new_child的值，真正定位用    變數名字.變數所引用資料型別的屬性名字
 12  end loop;
 13* end;
new_child's x is 2 new_child's y is 3
new_child's x is 4 new_child's y is 5
new_child's x is 7 new_child's y is 8
new_child's x is 6 new_child's y is 6

PL/SQL procedure successfully completed.

SQL>