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;
/