Oracle：在包中使用数组datav类型

yqkkidmi 于 2023-03-01 发布在 Oracle

关注(0)|答案(2)|浏览(144)

我有一个包，其中包含一个过程，我需要在其中查询一个ID号列表，它是varchar2，我将不得不多次查询这个列表，我不希望重做查询，理想情况下，我希望在我的包中创建一个函数，它将返回ID号列表，这样我就可以将ID号数组加载到一个变量中，然后在我的整个过程中，把这个变量当作一个表，我一直在疯狂地搜索，但这似乎是不可能的，有什么方法可以做到吗？
注意：我不能在模式级别创建新类型，而且它也不允许我在本地集合类型上这样做。
而且，我更愿意不使用动态SQL;我的过程中的主查询非常庞大，我不想处理这么大的字符串。
我想做这样的事情：

id_number_list array_type := my_function();

select *
from my_table mt
left join table(id_number_list) idl on mt.id_number = idl.column_value;

编辑：谢谢你的帮助! MTO的答案适用于选择语句连接，就像我上面描述的那样。但是，我还需要从列表中删除id_number所在的表。这给了我一个"invalid data type"错误。如何解释这个错误？数据类型应该总是相同的：varchar2（10）。
这里我在包级别创建类型：

type string_list is table of varchar2(10);

然后创建一个函数，返回ID号列表（出于我们的目的，"action"总是c_action_refresh，因此if语句为真）：

function get_modified_ids(scope in smallint, action in smallint) return string_list is
    
  modified_ids string_list := string_list();
  last_refreshed date;
  
  begin
   
  last_refreshed := get_last_refreshed_date(scope,action);
  
  if action = c_action_refresh then
   
   select id_number
   bulk collect into modified_ids
   from(
  
          select id_number
          from adv.hr_giving cg
          join adv.pbi_dates d
            on d.DATE_FULL = trunc(cg.processed_date)
         where d.RELATIVE_DATE >= last_refreshed
           and d.RELATIVE_DATE <= trunc(CURRENT_DATE)
           and cg.fiscal_year >= adv.current_fiscal_year - 6
        
        union
        
        select gi.gift_donor_id as id_number
          from adv.gift gi
         where gi.date_added >= last_refreshed
            or gi.date_modified >= last_refreshed
        
        union
        
        select p.pledge_donor_id as id_number
          from adv.pledge_rev p
         where p.date_added >= last_refreshed
            or p.date_modified >= last_refreshed
        
        union
        
        select a.id_number
          from adv.affiliation a
         where a.date_added >= last_refreshed
            or a.date_modified >= last_refreshed
    );
    
  end if;
  
  return(modified_ids);
  
  end get_modified_ids;

然后，在我的过程中，我通过调用函数初始化一个变量：

modified_ids string_list := get_modified_ids(scope,action);

然后我尝试在delete语句中使用该列表：

delete from advrpt.pbi_gvg_profile_ag p
  where p.id_number in
        (select column_value from table(modified_ids));

这将产生错误ORA-00902：无效的数据类型。id_number的类型是varchar2（10）。同样，它在select语句的连接中工作正常。
为什么我会收到这个错误？

oracle

来源：https://stackoverflow.com/questions/75503171/oracle-using-an-array-datav-type-in-package

2条答案

按热度按时间

3npbholx1#

不要在包中使用变量（因为只有一个变量，如果您的过程在短时间内连续调用两次，那么第二组值将覆盖第一组值，如果在处理第一次调用的中途发生这种情况，则可能会导致问题）。
而是创建用户定义的集合类型：

CREATE TYPE number_list IS TABLE OF NUMBER;

并将集合作为参数传递给过程：

CREATE PROCEDURE your_procedure (
  i_numbers IN  number_list,
  o_cursor1 OUT SYS_REFCURSOR,
  o_cursor2 OUT SYS_REFCURSOR
)
IS
BEGIN
  OPEN o_cursor1 FOR
    SELECT *
    FROM   your_table
    WHERE  id MEMBER OF i_numbers;

  OPEN o_cursor2 FOR
    SELECT y.*
    FROM   your_table y
           INNER JOIN TABLE(i_numbers) n
           ON (y.id = n.COLUMN_VALUE);
END;
/

然后使用调用它，例如：

DECLARE
  v_cur1  SYS_REFCURSOR;
  v_cur2  SYS_REFCURSOR;
  v_id    your_table.id%TYPE;
  v_value your_table.value%TYPE;
BEGIN
  your_procedure(number_list(1,5,13), v_cur1, v_cur2);
  LOOP
    FETCH v_cur1 INTO v_id, v_value;
    EXIT WHEN v_cur1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_id || ', ' || v_value);
  END LOOP;
END;
/

或者创建类型作为包的一部分：

CREATE PACKAGE your_package AS
  TYPE number_list IS TABLE OF NUMBER;

  PROCEDURE your_procedure (
    i_numbers IN  number_list,
    o_cursor1 OUT SYS_REFCURSOR,
    o_cursor2 OUT SYS_REFCURSOR
  );
END;
/

然后创建程序包主体：

CREATE PACKAGE BODY your_package AS
  PROCEDURE your_procedure (
    i_numbers IN  number_list,
    o_cursor1 OUT SYS_REFCURSOR,
    o_cursor2 OUT SYS_REFCURSOR
  )
  IS
  BEGIN
    OPEN o_cursor1 FOR
      SELECT *
      FROM   your_table
      WHERE  id IN (SELECT COLUMN_VALUE FROM TABLE(i_numbers));

    OPEN o_cursor2 FOR
      SELECT y.*
      FROM   your_table y
             INNER JOIN TABLE(i_numbers) n
             ON (y.id = n.COLUMN_VALUE);
  END;
END;
/

注：MEMBER OF运算符仅适用于SQL范围中定义的集合，而不适用于PL/SQL范围中本地定义的集合。*

然后使用调用它，例如：

DECLARE
  v_cur1  SYS_REFCURSOR;
  v_cur2  SYS_REFCURSOR;
  v_id    your_table.id%TYPE;
  v_value your_table.value%TYPE;
BEGIN
  your_package.your_procedure(your_package.number_list(1,5,13), v_cur1, v_cur2);
  LOOP
    FETCH v_cur1 INTO v_id, v_value;
    EXIT WHEN v_cur1%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_id || ', ' || v_value);
  END LOOP;
END;
/

fiddle

赞(0）回复(0）举报 2023-03-01

lyfkaqu12#

在我看来，你似乎拥有了一切。如果没有，这里有一个例子。
功能：

SQL> create or replace function f_test
  2    return sys.odcinumberlist
  3  is
  4  begin
  5    return sys.odcinumberlist(10, 20);
  6  end;
  7  /

Function created.

如何使用它？

SQL> set serveroutput on
SQL> declare
  2    id_number_list  sys.odcinumberlist := f_test;
  3  begin
  4    for cur_r in (select e.deptno, e.ename
  5                  from emp e join table(id_number_list) idl on idl.column_value = e.deptno
  6                  order by 1, 2
  7                 )
  8    loop
  9      dbms_output.put_line(cur_r.deptno ||' '|| cur_r.ename);
 10    end loop;
 11  end;
 12  /
10 CLARK
10 KING
10 MILLER
20 ADAMS
20 FORD
20 JONES
20 SCOTT
20 SMITH

PL/SQL procedure successfully completed.

SQL>

赞(0）回复(0）举报 2023-03-01

我来回答

Oracle：在包中使用数组datav类型

2条答案

相关问题

热门标签

最新问答