在PL / SQL中将游标数据提取到数组中

I have a problem at one exercise: I have a table with an ussualy column, and another column which is another table, like this:

我在一个练习中遇到了问题：我有一个带有ussualy列的表，另一个列是另一个表，如下所示：

   CREATE OR REPLACE TYPE list_firstnames AS TABLE OF VARCHAR2(10); 

then I create the following table:

然后我创建下表：

   CREATE TABLE persons (last_name VARCHAR2(10), first_name list_firstnames)
   NESTED TABLE first_name STORE AS lista; 

I insert:

我插入：

INSERT INTO persons VALUES('Stewart', list_firstnames('John', 'Jack'));
INSERT INTO persons VALUES('Bauer', list_firstnames('Helen', 'Audrey'));
INSERT INTO persons VALUES('Obrian', list_firstnames('Mike', 'Logan'));

I want to make a cursor to take all last and first names from persons, and then I want to put all of them in an array. After this, I want to count the first names which contains the 'n' letter.

我想制作一个光标来获取人的所有姓氏和名字，然后我想把它们全部放在一个数组中。在此之后，我想计算包含'n'字母的名字。

First of all, I want to know how I can put all the information from the cursor in an array. I try this:

首先，我想知道如何将光标中的所有信息放入数组中。我试试这个：

DECLARE 
      CURSOR firstnames_students IS
      select last_name,COLUMN_VALUE as "FIRSTNAME" from persons p,         TABLE(p.last_name) p2;
      v_lastname VARCHAR(50);
      v_firstname VARCHAR(50);
      v_I NUMBER := 1;
      v_count NUMBER := 0;
      v_contor INTEGER := 0;
      TYPE MyTab IS TABLE OF persons%ROWTYPE INDEX BY VARCHAR2(20);
      std MyTab;
 BEGIN
     OPEN firstnames_students;
     LOOP
        FETCH firstnames_students INTO v_lastname, v_firstname;
        EXIT WHEN firstnames_students%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_lastname || ' ' || v_firstname);
     END LOOP;
        --select * INTO std FROM firstnames_students; 
     CLOSE firstnames_students;
  END; 

1 个解决方案

#1

SET SERVEROUTPUT ON;
DECLARE 
  CURSOR students IS
    SELECT * FROM persons;

  v_row   PERSONS%ROWTYPE;
  v_names SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
  v_count INT := 0;
 BEGIN
  OPEN students;
  LOOP
    FETCH students INTO v_row;
    EXIT WHEN students%NOTFOUND;
    v_names.EXTEND;
    v_names(v_names.COUNT) := v_row.last_name;
    FOR i IN 1 .. v_row.first_name.COUNT LOOP
      v_names.EXTEND;
      v_names(v_names.COUNT) := v_row.first_name(i);
    END LOOP;
  END LOOP;
  CLOSE students;

  FOR i IN 1 .. v_names.COUNT LOOP
    IF INSTR( v_names(i), 'n' ) > 0 THEN
      v_count := v_count + 1;
      DBMS_OUTPUT.PUT_LINE( v_names(i) );
    END IF;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE( v_count );
END;

Output:

输出：

John
Helen
Obrian
Logan
4