如何循环通过嵌套表在ORACLE为Oracle窗体存储过程基于块？

Question

我正在开发Oracle Forms Builder 10g。在我的应用程序中，我有一个嵌套的表类型，其中包含一堆varchar2。它是类型my\u type\u 1是varchar2（255）的表。我在数据库中创建了相同的类型。

现在，我正在创建一个基于存储过程的表单。我想传递一个变量的my_type_1在身体是这样的：

procedure my_proc (my_var_in_out IN OUT some_type, my_var_test IN my_type_1) is

cursor my_cursor(id varchar2(255)) is
select name from emp where emp_id = id;

idx number := 1;

begin

for I in my_cursor(my_var_test )  loop <<< this is where I'm stuck. Can I pass it like that ?
   my_var_in_out (idx) := I;
   idx  := idx  +1;
end loop;
end;

Answer 1

您可以像下面这样循环：

创建的类型

  CREATE OR REPLACE TYPE my_type_1 IS TABLE OF VARCHAR2 (1000);
    /


    CREATE TABLE emp
    (
       fname    VARCHAR2 (100),
       emp_id   VARCHAR2 (10)
    );

SQL> select * from emp;

FNAME                                                                                                EMP_ID
---------------------------------------------------------------------------------------------------- ----------
XXX                                                                                                  1
YYY                                                                                                  2

程序

CREATE OR REPLACE PROCEDURE my_proc (my_var_test IN OUT my_type_1)
IS
   CURSOR my_cursor (my_var_test my_type_1)
   IS
      SELECT fname
        FROM emp
       WHERE emp_id MEMBER OF my_var_test; --<--This is how you implement in clause while using a collection

   v_var   my_type_1 := my_type_1 ();
BEGIN
   OPEN my_cursor (my_var_test);

   FETCH my_cursor BULK COLLECT INTO v_var;

   CLOSE my_cursor;

   FOR rec IN 1 .. v_var.COUNT
   LOOP         
      my_var_test (rec) := v_var (rec);         
   END LOOP;
END;

执行：

DECLARE
   var   my_type_1 := my_type_1 ();
BEGIN
   var.EXTEND(2);

   var (1) := '1';
   var (2) := '2';

   my_proc (MY_VAR_TEST => var);


   FOR i IN 1 .. var.COUNT
   LOOP
      DBMS_OUTPUT.put_line (var (i));
   END LOOP;
END;

输出：

SQL> /
XXX
YYY

PL/SQL procedure successfully completed.