本文介绍Oracle中自定义类型的应用方法，包括类型创建、使用表函数处理集合数据及异常处理等。通过实例展示了如何利用自定义类型简化SQL操作并提高代码可维护性。
构造基础表
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 上调试通过。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
