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 上除錯透過。