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

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

  •  0
  • senek  · 技术社区  · 8 月前

    我想使用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 回复  |  直到 8 月前
        1
  •  2
  •   Koen Lostrie    8 月前

    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;
    
        2
  •  1
  •   MT0    8 月前

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

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

    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