如何使用PL/SQL数组或集合作为参数，然后按索引将它们联接在一起
我正在尝试创建一个非常简单的过程来创建一个或多个帐户.为了完成此操作，我希望对本过程进行的Call的实现尽可能简单.该过程背后的代码可以是复杂的也可以是简单的，但这并不是我所关心的全部，因为我首先是从将要调用create_account()Proc的用户的角度来解决这个问题的.
I am attempting to create a really simple procedure that creates an account or several accounts. In order to accomplish this I wanted the implementation of the Call that will be made to this Procedure to be as simple as possible. The Code behind the Procedure can be complex or simple but that's not entirely what I care about here as I'm approaching this first from the perspective of the User that will be making the Call to the create_account() Proc.
创建帐户的简单调用-正如您在我提供的代码中看到的那样，存在零声明...在进行调用时，我没有初始化任何内容.这对于最大限度地简化此过程的使用非常重要.
Simple Call to Create an Account - As you can see in the code I provided there are Zero Declarations... I'm not initializing anything when making the Call. This is important to maximize the level of simplicity regarding the usage of this Procedure.
BEGIN
create_account(p_entity_id => 550005
, p_sub_id => 100051
, v_contacts_fname => sys.odcivarchar2list('dan','bob')
, v_contacts_lname => sys.odcivarchar2list('anderson','bebop')
);
END;
基本上，我正在寻找一种最简单的方法，允许用户通过数组在此调用中键入一个由逗号分隔的简单值列表，然后将其解析以便执行插入帐户表中.
Basically, I'm looking for the easiest route to allow a user to just type into this call a simple list of comma separated values via an array which will then be parsed in order to perform an insert into an accounts table.
问题:由于我目前正在研究程序"的两个集合，因此如何按索引将条目绑定在一起?集合1的索引1应该绑定到集合2的索引1，依此类推.在create_account()过程中包含的PL/SQL中建立此连接的最佳方法是什么?
The issue: Since I'm currently taking into the Procedure two collections how do I tie together the entries by index..? Index 1 of Collection 1 should tie to Index 1 of Collection 2 and so on. What is the best approach to make this connection within the PL/SQL contained in the create_account() Procedure?
理想情况下，我希望能够执行与C#中类似的操作，即只需声明一个新的LIST()对象，而无需声明该对象并将其发送到Procedure或某些程序中.但是我无法直接将 RECORD 类型发送到Procedure中，这就是为什么我认为使用Oracle提供的sys.odcivarchar2list集合可以起作用的原因.这种方法的确允许我基于基本的逗号分隔值创建集合，但是它们仅是单列集合，因此对于这里我关心的两个参数，我必须使用两个集合.最终，我想按比例放大参数，以包括account_type，phone_numbers等...，但我首先需要发现基于索引将这些集合捆绑在一起的最佳方式.
Ideally I would have loved to be able to do something similar as can be done in C# which would be to just New Up a LIST() Object without the requirement of Declaring that object and to just send that into the Procedure or some sort of multi column array however I'm unable to just send a RECORD Type directly into the Procedure which is why I thought that using the sys.odcivarchar2list collections Oracle provides could work. This approach does allow me to create collections based on my basic comma separated values but they are ONLY single column collections and so I must use Two collections for each of the two parameters I care about here. Eventually I will like to scale up the parameters to include account_type, phone_numbers etc... but I first need to discover the BEST way to tie these collections together based on the index.
请参见下面我到目前为止已经开始的工作.完整的脚本/代码-非常基本，但是很重要.
See below the work I have begun so far. FULL Script/Code - Very basic but it gets the point across.
/* This code will run on it's own. Single PL/SQL Block. */
DECLARE
PROCEDURE create_account(p_entity_id NUMBER
, p_sub_id NUMBER
, v_contacts_fname sys.odcivarchar2list
, v_contacts_lname sys.odcivarchar2list
)
IS
BEGIN
dbms_output.put_line('Entity_id: ' || p_entity_id || ' - Sub_id: ' || p_sub_id);
FOR f IN (SELECT m.column_value fname FROM TABLE(v_contacts_fname) m)
LOOP
FOR l IN (SELECT m.column_value lname FROM TABLE(v_contacts_lname) m)
LOOP
dbms_output.put_line(l.lname ||', ' || f.fname);
END LOOP;
END LOOP;
END create_account;
BEGIN
create_account(p_entity_id => 550005
, p_sub_id => 100051
, v_contacts_fname => sys.odcivarchar2list('dan','bob')
, v_contacts_lname => sys.odcivarchar2list('anderson','bebop')
) ;
END;
输出
/* I just thru a dbms_output() within both loops for display but of course this is NOT */
/* how I want to tie together the entries of both collections to each other */
Entity_id: 550005 - Sub_id: 100051
anderson, dan
bebop, dan
anderson, bob
bebop, bob
正确的输出当然是:
anderson, dan
bebop, bob
您正在使用的集合类型是varray，因此已对其进行了索引；您可以这样做:
The collection type you are using is a varray, so it is indexed; you can do:
FOR i IN 1..v_contacts_fname.COUNT
LOOP
dbms_output.put_line(v_contacts_fname(i) ||', ' || v_contacts_lname(i));
END LOOP;
适应您的示例代码:
DECLARE
PROCEDURE create_account(p_entity_id NUMBER
, p_sub_id NUMBER
, v_contacts_fname sys.odcivarchar2list
, v_contacts_lname sys.odcivarchar2list
)
IS
BEGIN
dbms_output.put_line('Entity_id: ' || p_entity_id || ' - Sub_id: ' || p_sub_id);
FOR i IN 1..v_contacts_fname.COUNT
LOOP
dbms_output.put_line(v_contacts_fname(i) ||', ' || v_contacts_lname(i));
END LOOP;
END create_account;
BEGIN
create_account(p_entity_id => 550005
, p_sub_id => 100051
, v_contacts_fname => sys.odcivarchar2list('dan','bob')
, v_contacts_lname => sys.odcivarchar2list('anderson','bebop')
) ;
END;
/
现在得到
Entity_id: 550005 - Sub_id: 100051
dan, anderson
bob, bebop
PL/SQL procedure successfully completed.
您可以在开始时进行非常基本的检查，以验证两个数组中的count是否相同-如果不相同，则可能会引发异常.
You could do a very basic check at the start to verify that the count from both arrays is the same - if not then throw an exception perhaps.
如果您要传递单个集合参数，则该参数将需要是记录或对象类型的集合，这些声明或对象类型可以在模式级别或在程序包中声明，具体取决于您想使用内容的方式(并且，在某种程度上，您正在使用的Oracle版本).听起来您好像不想这样做.
If you want to pass a single collection argument then that argument would need to be a collection of record or object types, declared either at schema level or perhaps in a package, depending on how you want to use the contents (and, to some extent, the version of Oracle you are using). It sounds like you don't want to do that though.