时间戳

首页 > 解决方案 > 总结列表 PLSQL 中的所有值

sql - 总结列表 PLSQL 中的所有值

问题描述

我想总结 INDEX BY TABLE 列表中的所有值。

在列表中有这样的值：

24000、4500、7890 等。

这是代码：

SET SERVEROUTPUT ON
SET VERIFY OFF

DECLARE
  emp_id NUMBER(5);
  emp_sal NUMBER(10);
  i NUMBER(10) := 100;
  counter NUMBER(10) := 0;
  counted NUMBER(10) := 0;

  TYPE employee IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  employeelist employee; 
  e_id NUMBER(10);
  
BEGIN
  LOOP
    SELECT employee_id, salary INTO emp_id, emp_sal FROM employees WHERE employee_id = i;
      employeelist(emp_id) := emp_sal;
      i := i + 1; 
    EXIT WHEN emp_id = 110;
  END LOOP;
  
  
  e_id := employeelist.FIRST; 
  LOOP 
  counted := counted + employeelist(e_id); --This is where I want to sum all the things. 
  counter := counter + 1;
  EXIT WHEN employeelist.count > counter;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(counted); --The result should be displayed here but the only thing that get displayed is the last value of the list
  


END;

标签： sqloracleplsql

解决方案

让我们假设您有一个关联的数组，并且您想要总结其中的值。而且，我们还假设关联数组的索引是稀疏的。

所以，如果我们有测试数据：

CREATE TABLE employees ( employee_id, salary ) AS
SELECT 42, 24000 FROM DUAL UNION ALL
SELECT 93,  4500 FROM DUAL UNION ALL
SELECT 17,  7890 FROM DUAL;

（注意：ID 没有1, 2, 3.）

然后我们用代码填充关联数组：

DECLARE
  TYPE employee IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  employeelist employee;
BEGIN
  FOR r_emp IN ( SELECT employee_id, salary FROM employees )
  LOOP
    employeelist(r_emp.employee_id) := r_emp.salary;
  END LOOP;
END;
/

为了执行求和，我们需要遍历关联数组。这不能在 SQL 中完成，因为关联数组是仅限 PL/SQL 的数据类型，因此您有两种选择：

  1. 将 PL/SQL 关联数组转换为 SQL 范围内的集合，并使用 SQL 语句对值求和；或者
  2. 遍历 PL/SQL 中的关联数组。

这是第二个选项，将使用关联数组的.FIRST和.NEXT(index)属性来迭代稀疏数组（而当数组稀疏时，使用计数器并递增 1 将失败）：

DECLARE
  TYPE employee IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
  employeelist employee;
  
  employee_id EMPLOYEES.EMPLOYEE_ID%TYPE;
  counted     NUMBER(10) := 0;
BEGIN
  FOR r_emp IN ( SELECT employee_id, salary FROM employees )
  LOOP
    employeelist(r_emp.employee_id) := r_emp.salary;
  END LOOP;
  
  employee_id := employeelist.FIRST;
  LOOP
    EXIT WHEN employee_id IS NULL;
    counted := counted + employeelist(employee_id);
    employee_id := employeelist.NEXT( employee_id );
  END LOOP;
  DBMS_OUTPUT.PUT_LINE( counted );
END;
/

哪个输出：

36390

db<>在这里摆弄

推荐阅读