Using PL/SQL Table variable

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

create or replace package PKG_TEST2 is

TYPE TYPE_NESTED_JOB_ROW IS RECORD
(JOB_CODE VARCHAR(50),
JOB_NAME VARCHAR(200));

TYPE TYPE_NESTED_JOB_TABLE is TABLE OF TYPE_NESTED_JOB_ROW ;

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_NESTED_JOB_TABLE:=TYPE_NESTED_JOB_TABLE() ;
JOB_ROW  TYPE_NESTED_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_NESTED_JOB_TABLE:=TYPE_NESTED_JOB_TABLE() ;
JOB_ROW  TYPE_NESTED_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_TEST2;
/

在上面的例子中, 我们在package中声明了一个record类型(TYPE_NESTED_JOB_ROW)和一个table类型(TYPE_NESTED_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_NESTED_JOB_TABLE的table类型, 这个类型对于该package之外其实是不可见的, 而存储过程却要将这个nested的表类型暴露到package之外, 所以Oracle会报错. 知道这个原因之后, 解决也很简单, Nested Record和Nested Table的定义放在包之外, 就行了. 需要说明的是, 需要修改一下Nested 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_TEST3 is

type type_result_table is ref cursor ;

procedure SP_GET_JOB_INFO(result_table out type_result_table) ;
end PKG_TEST3;
/
create or replace package body PKG_TEST3 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_TEST3;
/

Oracle temp table

在SQL Server的存储过程中, 可以非常方便地使用临时表#TempTable, 你可以随意地将数据插入到临时表, 也可以将临时表的记录作为存储过程的结果集. #TempTable用过后, 它就干干净净地消失了, 包括表的structure.

而Oracle的临时表是global temporary table, 这表明它的定义是persistent, 只是内容是临时的. 除此之外, 临时表还有几个特点:

1. 临时表是不能加logging等选项, 所以它的redo log和I/O和普通表相比都非常小,

2. 不能为临时表指定tablespace, 临时表一定是在TEMPORARY tablespace中.

定义一般Heap表的格式通常是: CREATE TABLE Table_1(col_1 number) TABLESPACE USERS;

而定义临时表的格式通常是:

CREATE GLOBAL TEMPORARY TABLE Temp_Table_1 (a NUMBER) ON COMMIT

PRESERVE ROWS; --临时表的内容在整个Session中一直保持, 即使是一个transaction结束.

或者是:
CREATE GLOBAL TEMPORARY TABLE Temp_Table_1 (a NUMBER) ON COMMIT

DELETE ROWS; --临时表的内容仅仅在一个transaction中保持.