『PLSQL』使用PL/SQL索引表

 

在SQL Server的存储过程中, 可以非常方便地使用临时表#TempTable, 你可以随意地将数据插入到临时表, 可以将临时表的记录作为存储过程的结果集. #TempTable用过后, 它就干干净净地消失了, 包括表的structure.而Oracle的临时表是global temporary table, 这表明它的定义是persistent, 只是内容是临时的.那么在Oracle中如何才能做到像SQL Server一样的临时表呢? Oracle为我们提供了PL/SQL Table表变量, 其实就是索引表.

 CREATE OR REPLACE PACKAGE Pkg_Test IS  TYPE Type_Job_Row IS RECORD(    Job_Code VARCHAR(50),    Job_Name VARCHAR(200));  TYPE Type_Job_Table IS TABLE OF Type_Job_Row;  TYPE Type_Result_Table IS REF CURSOR;  PROCEDURE Sp_Get_Job_Info(Result_Table OUT Type_Result_Table);END Pkg_Test; 

 / 

 
CREATE OR REPLACE PACKAGE BODY Pkg_Test IS  PROCEDURE Sp_Get_Job_Info(Result_Table OUT Type_Result_Table) IS    Job_Table Type_Job_Table := Type_Job_Table();    Job_Row   Type_Job_Row;  BEGIN    Job_Row.Job_Code := 'A';    Job_Row.Job_Name := 'A12123';    Job_Table.EXTEND();    Job_Table(Job_Table.COUNT) := Job_Row;    Job_Row.Job_Code := 'B';    Job_Row.Job_Name := 'B12123';    Job_Table.EXTEND();    Job_Table(Job_Table.COUNT) := Job_Row;    Dbms_Output.Put_Line(Job_Table.COUNT);    --OUTPUT RESULT IS 2    --The following code can not compile succeeded    --OPEN result_table for    --   select * from table(JOB_ROWS) ;        END;  PROCEDURE Sp_Get_Job_Info_2(In_Job_Id    IN NUMBER,                              Result_Table OUT Type_Result_Table) IS    Job_Table Type_Job_Table := Type_Job_Table();    Job_Row   Type_Job_Row;    CURSOR Cursor_Jobs IS      SELECT * FROM Employee_Jobs WHERE Job_Id >= In_Job_Id;  BEGIN    FOR r_Job IN Cursor_Jobs    LOOP      Job_Row.Job_Code := r_Job.Job_Code;      Job_Row.Job_Name := r_Job.Job_Name;      Job_Table.EXTEND();      Job_Table(Job_Table.COUNT) := Job_Row;    END LOOP;    Dbms_Output.Put_Line(Job_Table.COUNT);    --OUTPUT RESULT IS 2    --The following code can not compile succeeded    --OPEN result_table for    --   select * from table(JOB_ROWS) ;        END;END Pkg_Test;/ 

在上面的例子中, 我们在package中声明了一个record类型(TYPE_JOB_ROW)和一个table类型(TYPE_JOB_TABLE), 在存储过程SP_GET_JOB_INFO中, 可以实例化这个table类型变量JOB_TABLE, 你可以向JOB_TABLE插入单个的记录. 如果使用Loop语句的话, 你可以批量地插入许多记录到这个表变量中, 就像存储过程SP_GET_JOB_INFO2.

这种在package中定义的table类型变量, 对于一般的DML操作是非常实用的, 但如果你要将table表变量的结果作为ref cursor类型的存储过程out参数暴露出来, 就不行了. 在上面的示例代码中, 如果将取消最后两句代码的注释, 你就发现这个存储过程不能被编译. 为什么呢? 详见 http://forums.oracle.com/forums/thread.jspa?threadID=623197

简单讲, 原因就是, 我们是在package中定义的TYPE_JOB_TABLE的table类型, 这个类型对于该package之外其实是不可见的, 而存储过程却要将这个表类型暴露到package之外, 所以Oracle会报错. 知道这个原因之后, 解决也很简单,  Record和Table的定义放在包之外, 就行了. 需要说明的是, 需要修改一下Record的定义格式(从Record类型改到Object类型).

 --create one object type outside of one  package
create or replace type TYPE_JOB_ROW as object
(
    JOB_CODE VARCHAR(50),
    JOB_NAME VARCHAR(200)
    );

--create one table type outside of one  package    
CREATE OR REPLACE TYPE TYPE_JOB_TABLE is TABLE OF TYPE_JOB_ROW;

  
create or replace package PKG_TEST2 is

type type_result_table is ref cursor ;

procedure SP_GET_JOB_INFO(result_table out type_result_table) ;
end PKG_TEST2;
/
create or replace package body PKG_TEST2 is

  procedure SP_GET_JOB_INFO(result_table out type_result_table)
  IS
  JOB_TABLE TYPE_JOB_TABLE:=TYPE_JOB_TABLE() ;
  JOB_ROW  TYPE_JOB_ROW ;
  BEGIN
    JOB_ROW:=TYPE_JOB_ROW(JOB_CODE => 'A',JOB_NAME => 'A123') ;
    JOB_ROW.JOB_NAME:='A12' ; --Change the job_name from A123->A12
    JOB_TABLE.extend();
    JOB_TABLE(JOB_TABLE.count) := JOB_ROW ;

    OPEN result_table FOR
      SELECT * FROM table(JOB_TABLE) ;
  END ;

end PKG_TEST2;