如何不借助变量直接向Oracle存储过程传入集合类型参数？

你想要直接用值列表调用带包内关联数组参数的存储过程，直接用{1=>'xxx'}这种语法是行不通的——因为你定义的t是包私有关联数组类型，Oracle PL/SQL不支持直接用字面量构造这类类型的实例，而且这种类型的作用域仅限test包内部。

不过可以通过以下两种方案实现类似需求：

方案一：重载存储过程，接受更易传入的参数类型 #

在原包中新增一个重载的testprc过程，接受逗号分隔的字符串（或其他易构造的类型），内部自动转换为t类型后调用原过程：

修改后的包规范： #

Create or replace package test
As 
  Type t is table of varchar(400) index by binary_integer;
  -- 原过程
  Procedure testprc(p_client_id in number,t_no in number,t1 in t,t2 in t);
  -- 重载版本，接受逗号分隔的电话、邮箱字符串
  Procedure testprc(p_client_id in number, p_phone_list in varchar2, p_email_list in varchar2);
End;
/

修改后的包体： #

Create or replace package body test
As
  -- 原过程实现不变
  Procedure testprc(p_client_id in number,t_no in number,t1 in t, t2 in t)
  is
  Begin
    for i in 1 ..t_no loop
      Insert into client(Client_id,Client_phone,client_email) 
      values (p_client_id,t1(i),t2(i));
    End loop;
  End;

  -- 重载版本实现：拆分字符串并转换为关联数组
  Procedure testprc(p_client_id in number, p_phone_list in varchar2, p_email_list in varchar2)
  is
    l_t1 t;
    l_t2 t;
    l_phones sys.odcivarchar2list := sys.odcivarchar2list();
    l_emails sys.odcivarchar2list := sys.odcivarchar2list();
    l_delimiter varchar2(1) := ',';
    l_start number := 1;
    l_end number;
  begin
    -- 拆分电话字符串为临时集合
    loop
      l_end := instr(p_phone_list, l_delimiter, l_start);
      if l_end = 0 then
        l_phones.extend;
        l_phones(l_phones.count) := substr(p_phone_list, l_start);
        exit;
      end if;
      l_phones.extend;
      l_phones(l_phones.count) := substr(p_phone_list, l_start, l_end - l_start);
      l_start := l_end + 1;
    end loop;

    -- 拆分邮箱字符串为临时集合
    l_start := 1;
    loop
      l_end := instr(p_email_list, l_delimiter, l_start);
      if l_end = 0 then
        l_emails.extend;
        l_emails(l_emails.count) := substr(p_email_list, l_start);
        exit;
      end if;
      l_emails.extend;
      l_emails(l_emails.count) := substr(p_email_list, l_start, l_end - l_start);
      l_start := l_end + 1;
    end loop;

    -- 转换为自定义关联数组
    for i in 1 .. l_phones.count loop
      l_t1(i) := l_phones(i);
      l_t2(i) := l_emails(i);
    end loop;

    -- 调用原过程
    testprc(p_client_id, l_phones.count, l_t1, l_t2);
  End;
End;
/

调用方式： #

现在可以直接传入字符串列表，无需声明变量：

-- 单个值
exec test.testprc(13, '22737371', 'test@abc.com');

-- 多个值（逗号分隔）
exec test.testprc(13, '22737371,22737372', 'test@abc.com,test2@abc.com');

方案二：用匿名块简化变量声明与调用 #

如果不想修改原包，可以在匿名块内快速声明并赋值关联数组，写法可以很紧凑：

declare
  v_t1 test.t;
  v_t2 test.t;
begin
  v_t1(1) := '22737371';
  v_t2(1) := 'test@abc.com';
  test.testprc(13, 1, v_t1, v_t2);
end;
/

内容的提问来源于stack exchange，提问作者Surbhi Sharma