在 PLSQL 中返回两个列表

Question

我正在尝试将 3 个 VARRAYS/COLLECTIONS 的列表返回到我的应用程序。但我遇到了麻烦，认为我要么错误地实施了解决方案，

create or replace
PROCEDURE "GENERATE_PEOPLE" 
(
  -- In this example pi_string will be "This.is.a.test"
  pi_string         IN  VARCHAR2 ,
  po_firstnames     OUT VARRAY , 
  po_lastnames      OUT VARRAY ,
  po_descriptions   OUT VARRAY ,
  po_error_code     OUT VARCHAR2 ,
  po_error_message  OUT VARCHAR2 
)
IS
  CURSOR people_cursor IS SELECT firstname, lastname, description FROM people;   
BEGIN 

  FOR person_rec IN people_cursor
    LOOP
      -- This is where I am trying to return 3 collections of po_firstnames, po_lastnames, po_descriptions
      -- The print statements below print out exactly what it is I am trying to return!
      dbms_output.put_line('Firstname: '   || person_rec.firstname);
      dbms_output.put_line('Lastname: '    || person_rec.lastname); 
      dbms_output.put_line('Description: ' || pi_string || person_rec.description);

      -- This is where the values would be added to the list/array/collection
      po_firstnames(num?)   := person_rec.firstname;
      po_lastnames(num?)    := person_rec.lastname;
      po_descriptions(num?) := pi_string || person_rec.description;      
    END LOOP;
  RETURN;
END;

非常感谢任何帮助

Oracle 10g

我这样称呼它：

DECLARE 
  TYPE po_firstnames AS vc2_array;
  TYPE po_lastnames AS vc2_array;
  TYPE po_descriptions AS vc2_array;
  po_error_code VARCHAR2(50);
  po_error_message VARCHAR2(50);
BEGIN
  GENERATE_PEOPLE
  (
    'This.is.a.test' , 
    po_firstnames    ,
    po_lastnames     ,
    po_descriptions  ,
    po_error_code    ,
    po_error_message   
   );  
END;

Answer 1

VARRAY 不能直接用作参数或变量的类型。相反，您需要创建一个像这样的 VARRAY 类型（例如）：

create type vc2_array as varray(100) of varchar2(4000);

那么：

create or replace
PROCEDURE "GENERATE_PEOPLE" 
(
  -- In this example pi_string will be "This.is.a.test"
  pi_string         IN  VARCHAR2 ,
  po_firstnames     OUT vc2_array , 
  po_lastnames      OUT vc2_array ,
  po_descriptions   OUT vc2_array ,
  po_error_code     OUT VARCHAR2 ,
  po_error_message  OUT VARCHAR2 
)

通常我会使用 TABLE 而不是 VARRAY，因为使用 TABLE 您不必指定最大元素数：

create type vc2_array as table of varchar2(4000);

然后可以像这样分配值this 在你的循环中：

  num := num+1; -- num must be declared above and initialised to 0
  po_firstnames(num)   := person_rec.firstname;
  po_lastnames(num)    := person_rec.lastname;
  po_descriptions(num) := pi_string || person_rec.description;

但是这样做会更有效：

create or replace
PROCEDURE "GENERATE_PEOPLE" 
(
  -- In this example pi_string will be "This.is.a.test"
  pi_string         IN  VARCHAR2 ,
  po_firstnames     OUT vc2_array , 
  po_lastnames      OUT vc2_array ,
  po_descriptions   OUT vc2_array ,
  po_error_code     OUT VARCHAR2 ,
  po_error_message  OUT VARCHAR2 
)
IS
BEGIN
  SELECT firstname, lastname, pi_string||description
  BULK COLLECT INTO po_firstnames, po_lastnames, po_descriptions
  FROM people;
END;