Oracle类型的创建及使用

深碍√TFBOYSˉ_ 2022-08-24 01:58 176阅读 0赞

构造基础表  
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 上调试通过。