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>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-671197/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-671197/