时间戳

首页 > 解决方案 > PLSQL中数组中的不同值

arrays - PLSQL中数组中的不同值

问题描述

我想将表中存在的列中的不同值存储到数组中。我得到以下错误。我怎样才能做到这一点？

select count(distinct(Name)) into n from table;      

create or replace type array_type is varray(100) of varchar2(20); 

for i in 1 .. n loop
    name_array.extend;
    select distinct(Name) into name_array(i) from table order by name asc;
end loop;   

Error :
 ORA-01422: exact fetch returns more than requested number of rows

桌子 ：

在此处输入图像描述

标签： arraysplsqlassociative-array

解决方案

您可以SELECT DISTINCT value BULK COLLECT INTO获取不同的值并将它们存储到集合中。

尝试：

CREATE TABLE test_table (
  name VARCHAR2(20),
  city VARCHAR2(250)
);

INSERT INTO test_table (name, city) VALUES ('A', 'X');
INSERT INTO test_table (name, city) VALUES ('B', 'Y');
INSERT INTO test_table (name, city) VALUES ('A', 'Z');
INSERT INTO test_table (name, city) VALUES ('C', 'K');
INSERT INTO test_table (name, city) VALUES ('D', 'P');
INSERT INTO test_table (name, city) VALUES ('A', 'Q');
INSERT INTO test_table (name, city) VALUES ('D', 'R');
INSERT INTO test_table (name, city) VALUES ('C', 'S');

COMMIT;

CREATE OR REPLACE TYPE array_type IS VARRAY(100) OF VARCHAR2(20);

DECLARE
  v_name_array array_type := array_type();
BEGIN
  SELECT DISTINCT NAME
    BULK COLLECT
    INTO v_name_array
    FROM test_table
   ORDER BY NAME ASC;
  -- Show resulting values in collection
  FOR i IN 1 .. v_name_array.count
  LOOP
    dbms_output.put_line(v_name_array(i));
  END LOOP;
END;
/

推荐阅读