代码之家 › 专栏 › 技术社区 › senek

如何在PL/SQL中将选择结果(列)放入数组中

plsql oracle-database sql

senek · 技术社区 · 1 年前

我想使用select运算符选择一个包含日期(年份)的列,然后将其放入数组变量中,这样我就可以在循环中迭代它的值。

以下是完整的代码:

CREATE TABLE employees (
   employee_id NUMBER PRIMARY KEY,
   first_name VARCHAR2(50),
   last_name VARCHAR2(50),
   otdel VARCHAR2(20),
   date_empl DATE,
   salary NUMBER(10, 2),
   bonus NUMBER(10, 2),
   nalog NUMBER(10, 2)
);

INSERT INTO employees (employee_id, otdel, date_empl, first_name, last_name, salary)
VALUES (101, 'IT', to_date('2015-01-14', 'yyyy/mm/dd'), 'John', 'Doe', 5000),
        (102, 'TOP Management', to_date('2007-06-30', 'yyyy/mm/dd'), 'Jane', 'Smith', 6000),
        (103, 'IT', to_date('2010-02-23', 'yyyy/mm/dd'), 'Alice', 'Johnson', 5500),
        (104, 'Sales', to_date('2017-08-24', 'yyyy/mm/dd'), 'Sean', 'Connery', 3500),
        (105, 'Sales', to_date('2019-03-30', 'yyyy/mm/dd'), 'Marv', 'Smith', 3000);

COMMIT;


DECLARE
  TYPE SelRes IS VARRAY(10) OF NUMBER(4,0);
  years SelRes;
  
  xxx NUMBER(4,0);
BEGIN
  SELECT EXTRACT (YEAR FROM empl.date_empl) INTO years FROM employees empl;
  
  FOR y in years
  LOOP
    SELECT COUNT(*) INTO xxx FROM employees empl WHERE EXTRACT (YEAR FROM empl.date_empl) = y;
    IF xxx>0 THEN
      DBMS_OUTPUT.PUT_LINE(y || ': ' || xxx || ' empl(-s) hired');
    END IF;
  END LOOP;
  
END;

在这种情况下,错误发生在第一次选择时:

Output:

  SELECT EXTRACT (YEAR FROM empl.date_empl) INTO years FROM employees empl;
                                                 *
ERROR at line 7:
ORA-06550: line 7, column 50:
PLS-00642: local collection types not allowed in SQL statements
ORA-06550: line 9, column 12:
PLS-00862: The type of the iterand is not compatible with an iteration control.
ORA-06550: line 9, column 3:
PL/SQL: Statement ignored

这个想法如下:按年计算今年雇佣的员工人数。同时,我不想获取从MIN_YEAR到MAX_YEAR的所有年份,只想获取数组中的年份。

是否有可能以某种方式在PL/SQL中实现这一点?

2 回复 | 直到 1 年前

Koen Lostrie 1 年前

pl/sql块有3个问题:

SELECT INTO 仅当查询结果返回单行时才适用。使用 BULK COLLECT INTO 对于多个值
要遍历数组的元素,请使用 FOR i IN 1 .. l_array.COUNT
引用数组元素的语法。y是迭代器,它将具有元素(1,2,…)的位置值。位置y的值用语法引用 l_array(y)

DECLARE
  TYPE SelRes IS VARRAY(10) OF NUMBER(4,0);
  years SelRes;
  
  xxx NUMBER(4,0);
BEGIN
-- BULK COLLECT INTO for multiple values.
  SELECT EXTRACT (YEAR FROM empl.date_empl) BULK COLLECT INTO years FROM employees empl;
  
-- Syntax for iterating through elements of a array:
  FOR y in 1 .. years.COUNT
  LOOP
    SELECT COUNT(*) INTO xxx FROM employees empl WHERE EXTRACT (YEAR FROM empl.date_empl) = years(y);
    IF xxx>0 THEN
      DBMS_OUTPUT.PUT_LINE(years(y) || ': ' || xxx || ' empl(-s) hired');
    END IF;
  END LOOP;
  
END;

但是,我不会使用VARRAY数据类型,因为它有固定数量的元素。请改用嵌套表。此外,在局部变量前加前缀以避免名称冲突——如果变量与对象名称同名,则可能会得到意外结果。

DECLARE
  TYPE selrestyp IS TABLE OF NUMBER;
  l_years selrestyp;
  
  l_xxx NUMBER(4,0);
BEGIN
  SELECT EXTRACT (YEAR FROM empl.date_empl) BULK COLLECT INTO l_years FROM employees empl;
  
  FOR y in 1 .. l_years.COUNT
  LOOP
    SELECT COUNT(*) INTO l_xxx FROM employees empl WHERE EXTRACT (YEAR FROM empl.date_empl) = l_years(y);
    IF l_xxx>0 THEN
      DBMS_OUTPUT.PUT_LINE(l_years(y) || ': ' || l_xxx || ' empl(-s) hired');
    END IF;
  END LOOP;
  
END;

请注意,不需要使用数组,您只需使用“游标FOR LOOP”即可。我添加了“DISTINCT”条款以避免重复年份:

DECLARE
  l_xxx NUMBER(4,0);
BEGIN
  
  FOR y in (SELECT DISTINCT EXTRACT (YEAR FROM empl.date_empl) as year FROM employees empl)
  LOOP
    SELECT COUNT(*) INTO l_xxx FROM employees empl WHERE EXTRACT (YEAR FROM empl.date_empl) = y.year;
    IF l_xxx>0 THEN
      DBMS_OUTPUT.PUT_LINE(y.year || ': ' || l_xxx || ' empl(-s) hired');
    END IF;
  END LOOP;
  
END;

MT0 1 年前

你可以使用一个集合,然后循环该集合,但不要这样做。

相反,将这些查询合并为一个查询:

BEGIN
  FOR r IN (
    SELECT EXTRACT(YEAR FROM empl.date_empl) AS year,
           COUNT(*) AS num_hired
    FROM   employees empl
    GROUP BY EXTRACT(YEAR FROM empl.date_empl)
    -- HAVING COUNT(*) > 0 -- This line is unnecessary as the count will never be 0
    ORDER BY year
  )
  LOOP
    DBMS_OUTPUT.PUT_LINE(r.year || ': ' || r.num_hired || ' empl(-s) hired');
  END LOOP;
END;
/

哪些输出:

2007: 1 empl(-s) hired
2010: 1 empl(-s) hired
2015: 1 empl(-s) hired
2017: 1 empl(-s) hired
2019: 1 empl(-s) hired

fiddle