分别用loop循环、while循环、for循环以及显式游标统计并输出各个部门的人数以及平均工资;
时间: 2023-04-25 13:01:59 浏览: 129
使用loop循环、while循环、for循环以及显式游标可以统计并输出各个部门的人数以及平均工资。具体实现方法如下:
1. 使用loop循环:
DECLARE
v_deptno NUMBER := 10;
v_count NUMBER;
v_total_salary NUMBER;
v_avg_salary NUMBER;
BEGIN
LOOP
SELECT COUNT(*), AVG(salary)
INTO v_count, v_avg_salary
FROM employees
WHERE department_id = v_deptno;
DBMS_OUTPUT.PUT_LINE('Department ' || v_deptno || ': ' || v_count || ' employees, average salary: ' || v_avg_salary);
v_deptno := v_deptno + 10;
EXIT WHEN v_deptno > 100;
END LOOP;
END;
2. 使用while循环:
DECLARE
v_deptno NUMBER := 10;
v_count NUMBER;
v_total_salary NUMBER;
v_avg_salary NUMBER;
BEGIN
WHILE v_deptno <= 100 LOOP
SELECT COUNT(*), AVG(salary)
INTO v_count, v_avg_salary
FROM employees
WHERE department_id = v_deptno;
DBMS_OUTPUT.PUT_LINE('Department ' || v_deptno || ': ' || v_count || ' employees, average salary: ' || v_avg_salary);
v_deptno := v_deptno + 10;
END LOOP;
END;
3. 使用for循环:
DECLARE
v_count NUMBER;
v_total_salary NUMBER;
v_avg_salary NUMBER;
BEGIN
FOR i IN 10..100 BY 10 LOOP
SELECT COUNT(*), AVG(salary)
INTO v_count, v_avg_salary
FROM employees
WHERE department_id = i;
DBMS_OUTPUT.PUT_LINE('Department ' || i || ': ' || v_count || ' employees, average salary: ' || v_avg_salary);
END LOOP;
END;
4. 使用显式游标:
DECLARE
CURSOR c_emp IS
SELECT department_id, COUNT(*), AVG(salary)
FROM employees
GROUP BY department_id;
v_deptno NUMBER;
v_count NUMBER;
v_total_salary NUMBER;
v_avg_salary NUMBER;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO v_deptno, v_count, v_avg_salary;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Department ' || v_deptno || ': ' || v_count || ' employees, average salary: ' || v_avg_salary);
END LOOP;
CLOSE c_emp;
END;
以上四种方法都可以用来统计并输出各个部门的人数以及平均工资,具体使用哪种方法可以根据实际情况选择。