Oracle explicit cursor and implicit cursor

Oracle 有隐式游标和显式游标之分.

对于那些仅仅返回一行记录的SQL语句, 本身又没有显式地使用Open语句, 其实就是隐式游标.
Implicit cursors

For SQL queries returning single row PL/SQL declares implicit cursors. Implicit cursors are simple SELECT statements and are written in the BEGIN block (executable section) of the PL/SQL. Implicit cursors are easy to code, and they retrieve exactly one row. PL/SQL implicitly declares cursors for all DML statements. The most commonly raised exceptions here are NO_DATA_FOUND or TOO_MANY_ROWS.
Syntax:

SELECT ename, sal INTO ena, esa FROM EMP WHERE EMPNO = 7844;

Note: Ename and sal are columns of the table EMP and ena and esa are the variables
used to store ename and sal fetched by the query.

显式游标, 其实显式游标也可以分为显式打开的游标, 和隐式打开的游标, 对于前者, 应该在最后关闭游标.

这是一个显式打开的显式游标

DECLARE
  CURSOR trip_cursor IS
     SELECT bt_id_pk, bt_hotel_id
     FROM business_trips;
  trip_record trip_cursor%ROWTYPE

BEGIN
  OPEN trip_cursor;
  LOOP
     FETCH trip_cursor INTO trip_record;
     EXIT WHEN trip_cursor%NOTFOUND;
     INSERT INTO copy_of_business_trips (bt_id_pk, bt_hotel_id)
     VALUES (trip_record.bt_id_pk, trip_record.bt_hotel_id);
  END LOOP;
  CLOSE job_cursor;
END;



--这个是隐式打开的显式游标, 在使用之后, 你不必手动地关闭这个游标
--Retrieve all trips from the t_business_trips table using a cursor
and print only those with a duration of one day:

SET SERVEROUTPUT ON
DECLARE
  CURSOR trip_cursor IS
     SELECT bt_id_pk, bt_duration
     FROM business_trips;
BEGIN
  FOR trip_record IN trip_cursor LOOP
     -- implicit open/fetch occur
     IF trip_record.bt_duration = 1 THEN
       DBMS_OUTPUT_LINE ('Trip Number ' || trip_record.bt_id_pk
                       || ' is a one day trip');
     END IF;
  END LOOP; -- IMPLICIT CLOSE OCCURS
END;

参考1
http://www.dba-oracle.com/plsql/t_plsql_cursors.htm
http://www.exforsys.com/tutorials/oracle-9i/oracle-cursors.html

invoke the cursor valued stored function/procedure

这节主要讨论, 如何在PL/SQL中怎样调用另一个存储函数或存储过程呢?
如果一个函数返回的是scalar值, 那么调用这个自定义的函数和调用内建的函数是完全一样的.

如果你的自定义的函数返回的是一个游标, 调用这个函数本身并不复杂, 但是怎样循环这个游标却比较诡异. 如下面的例子, FUN_GET_JOB_INFO返回的游标不是直接来源于个表, 而是动态生成的一个recordset, 你不能将这个游标函数看作一个普通表一样,放在from从句中, 只能像普通游标一样使用它, 比如用Loop语句来遍历这个游标. 因为调用这个游标函数, 获得的其实就是一个游标.

Fetch这个cursor的row时候, 不能将它直接将整个row放到你自定义的row 类型变量中, 这会引起convert错误. 你应当将这个row分解成若干个字段, 将row放到这些字段中.
比如
FETCH CURSOR1 INTO V_JOB_CODE, V_JOB_NAME;

--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_TEST4 is

type type_result_table is ref cursor ;

function FUN_GET_JOB_INFO RETURN type_result_table  ;
function FUN_GET_ONE_JOB(v_arg varchar2, v_Arg_Is_JOB_Code in char)  return varchar2;

end  PKG_TEST4;
/
create or replace package body PKG_TEST4 is

/*
Purpose: To fetch ALL JOB code or name
*/
function FUN_GET_JOB_INFO RETURN  type_result_table
IS
  result_table type_result_table ;
  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) ;

  return(result_table);
END ;


/*
Purpose: To fetch on specific JOB code or name
*/
function FUN_GET_ONE_JOB(v_arg varchar2, v_Arg_Is_JOB_Code in char)
return varchar2 is
result     varchar2(50);
cursor1    type_result_table;
V_JOB_CODE VARCHAR2(20);
V_JOB_NAME VARCHAR2(20);
V_JOB_ROW  TYPE_JOB_ROW;
begin
result  := '';
--call the cursor valued function
cursor1 := FUN_GET_JOB_INFO;
LOOP
  --Fetch the record into 2 variables
  FETCH CURSOR1 INTO V_JOB_CODE, V_JOB_NAME;

  --the following code is wrong, because it will fail to convert the row type.
     --FETCH CURSOR1 INTO V_JOB_ROW;
    
  if (v_Arg_Is_JOB_Code = 'Y') or (v_Arg_Is_JOB_Code = 'y') then
    if (V_JOB_CODE = v_arg) then
      result := V_JOB_name;
    end if;
  else
    if (V_JOB_name = v_arg) then
      result := V_JOB_code;
    end if;
  end if;
  EXIT WHEN CURSOR1%NOTFOUND;

END LOOP;
return(Result);
end;

end PKG_TEST4;
/

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中保持.