创建oracle type类型,Oracle类型的创建及使用

构造基础表

create table ascii_enum(ascii_no number, charactor varchar2(255));

构造测试数据

insert into ascii_enum

select ascii('a') + rownum - 1, chr(ascii('a') + rownum -  1)

from dba_objects o

where rownum <= 26

union all

select ascii('A') + rownum - 1, chr(ascii('A') + rownum -  1)

from dba_objects o

where rownum <= 26

union all

select ascii('9') + rownum - 1, chr(ascii('0') + rownum -  1)

from dba_objects o

where rownum <= 10;

commit;

select * from ascii_enum

select ascii('''') from dual

创建类型

create or replace type ascii_key IS TABLE OF NUMBER;

注意:

在创建类型的时候可能会出现："ora-01031: 权限不足" 的情况，

只要赋予create type的权限即可。

grant create type to dvbcetus_sup

如何使用该类型

使用方式1：

declare

v_ascii_key ascii_key := ascii_key(97,100,78,79,39,60,61);

v_ascii_char varchar2(255) := '';

cursor c_ascii is

select column_value from table(v_ascii_key);

begin

for vc_ascii in c_ascii loop

begin

select ae.charactor into  v_ascii_char

from ascii_enum ae

where ae.ascii_no = vc_ascii.column_value;

exception when no_data_found then

v_ascii_char := '(Not in this table: ascii_enum: 【' || chr(vc_ascii.column_value) || '】)';

end;

dbms_output.put_line('Ascii:Charactor ->' || vc_ascii.column_value || ':' || v_ascii_char);

end loop;

end ;

使用方式2：

declare

v_ascii_key ascii_key := ascii_key(97,100,78,79,39,60,61);

v_cnt integer := 0;

begin

select count(*) into  v_cnt  from ascii_enum ae

where ae.ascii_no in(select column_value from table(v_ascii_key));

dbms_output.put_line('Total count: ' || v_cnt);

end ;

使用方式3：

这种方式其实就是第一种方式的另一种实现方式而已，本质上是一样的

declare

v_ascii_key ascii_key := ascii_key(97,100,78,79,39,60,61);

v_ascii_char varchar2(255) := '';

v_cnt integer := 0;

begin

for vc_ascii in (select column_value from table(v_ascii_key)) loop

begin

select ae.charactor into  v_ascii_char

from ascii_enum ae

where ae.ascii_no = vc_ascii.column_value;

exception when no_data_found then

v_ascii_char := '(Not in this table: ascii_enum: 【' || chr(vc_ascii.column_value) || '】)';

end;

dbms_output.put_line('Ascii:Charactor ->' || vc_ascii.column_value || ':' || v_ascii_char);

end loop;

end ;

另外，有这样一种情况，我不想创建一个新的类型，只想在声明的时候定义该类型

就可以在后面使用，假设想实现如下需求：

declare

v_ascii_enum2 ascii_enum2 := ascii_enum2(97,100,78,79,39,60,61);

v_ascii_char varchar2(255) := '';

v_cnt integer := 0;

cursor c_ascii is

select column_value from table(v_ascii_enum2);

begin

for vc_ascii in c_ascii loop

begin

select ae.charactor into  v_ascii_char

from ascii_enum ae

where ae.ascii_no = vc_ascii.column_value;

exception when no_data_found then

v_ascii_char := '(Not in this table: ascii_enum: 【' || chr(vc_ascii.column_value) || '】)';

end;

dbms_output.put_line('Ascii:Charactor ->' || vc_ascii.column_value || ':' || v_ascii_char);

end loop;

end;

出现如下错误：PLS-00642 local collection types not allowed in SQL statements

网上的一些解释，大意如果在sql级使用嵌套表或varray数组，则所定义的类型必须是schema级的。

Cause: A locally-defined (that is not schema level) collection type was used in a SQL statement. The type must be defined in a schema to be accepted in a SQL statement.

Action: Define the collection type in your schema, not inside a PL/SQL subprogram.

当创建该类型后问题即解决：

create or replace type ascii_enum2 is table of number;

实际就是前面说的那种情况。

上面只是讨论了type的一个方面，这个方面有一个优点：

我在变量声明的时候，就定义改类型的初始值。后面SQL语句

中要用到所有这些值的，我只要往这个声明的变量中添加初始值

即可。无需修改每处涉及这些值的地方。

如以下这个过程，对于其中的SQL1和SQL2，如果直接使用

in(97,100,78,79,39,60,61)和not in(97,100,78,79,39,60,61)，

declare

v_cnt integer := 0;

begin

--SQL 1

select count(*) into  v_cnt  from ascii_enum ae

where ae.ascii_no in( 97,100,78,79,39,60,61);

dbms_output.put_line('Total key in count -> ' || v_cnt);

--SQL 2

select count(*) into  v_cnt  from ascii_enum ae

where ae.ascii_no not in(97,100,78,79,39,60,61);

dbms_output.put_line('Total key not in count -> ' || v_cnt);

end ;

而且如果这种情况出现多次，则会使代码的可维护性和可读性降低；

而如果用一个的变量来替代，就不会有上面的问题了：

declare

v_ascii_key ascii_key := ascii_key(97,100,78,79,39,60,61);

v_cnt integer := 0;

begin

--SQL 1      select count(*) into  v_cnt  from ascii_enum ae

where ae.ascii_no in(select column_value from table(v_ascii_key));

dbms_output.put_line('Total key in count -> ' || v_cnt);

--SQL 2

select count(*) into  v_cnt  from ascii_enum ae

where ae.ascii_no not in(select column_value from table(v_ascii_key));

dbms_output.put_line('Total key not in count -> ' || v_cnt);

end ;

20080618补充：

在下面语句的insert 中，为插入下面几个值，执行如下语句：

SQL> declare

2      v_ascii_key ascii_key := ascii_key(42,40,38);

3  begin

4      insert into ascii_enum

5      select column_value, chr(column_value) from table(v_ascii_key);

6      commit;

7  end;

8  /

出现如下报错信息：

ORA-22905: cannot access rows from a non-nested table item

ORA-06512: at line 5

但是我如果一定要用这种方法实现呢？

答案是加一个cast转换，如下语句实现：

SQL>

SQL> declare

2      v_ascii_key ascii_key := ascii_key(42,40,38);

3  begin

4      insert into ascii_enum

5      select column_value, chr(column_value) from table(cast(v_ascii_key as ascii_key));

6      commit;

7  end;

8  /

PL/SQL procedure successfully completed

语句正常执行。

上面的方法是从asktom上找来的，发现此中方法还不错，：)。

最后总结一下：

这里总共使用几项关键的技术：

1、使用table函数，这个函数是将pl/sql语句返回的结果集做为一个table来处理，因此转换后可以直接在select * from 中使用，还有另外一个使用的方式如：

select *from table(dbms_xplan.display)--查看explain plan的结果。

2、使用数组，数组在C/C++/JAVA程序中是非常常见的一种数据格式，在这里使用类似一维数组的类型，即：create or replace type type_abc is table of number;这种类型可以在初始化的时候赋予初始值，如：

v_abc type_abc := typc_abc(1,2,3,4,5);

这样就表示这个数组共有5个元素，如果元素个数比较多一下子数不清或者我在程序中想要以一种自动的方式获得元素的个数及内容呢？

那就可以用v_abc.count来获取元素的个数

及v_abc(n)来获取对应的第n个元素(类似于C++的容器的使用)：

declare

v_ascii_key ascii_key := ascii_key(42,40,381,2,3,4,6,8);

begin

dbms_output.put_line(v_ascii_key.count);

for i in 1..v_ascii_key.count loop

dbms_output.put_line(v_ascii_key(i));

end loop;

end;

3、其他的用到的还有异常检测(Exception when..then)、cursor使用、类型创建等内容，以后再逐个写写...

注意：以上代码都在Oracle9i Enterprise Edition Release 9.2.0.4.0 上调试通过。