Question

oracle数据库：创建一存储过程，返回HR模式下某部门的总工资数

创建一存储过程，返回HR模式下某部门的总工资数

Answer 1

CREATE OR REPLACE PROCEDURE GET_SALARY(V_DEPARTMENT_ID IN NUMBER,
                                       V_SALARY        OUT NUMBER) AS
BEGIN
    SELECT NVL(SUM(SALARY), 0)
      INTO V_SALARY
      FROM EMPLOYEES
     WHERE DEPARTMENT_ID = V_DEPARTMENT_ID;
END;
/

测试过程

DECLARE
    V_DEPARTMENT_ID NUMBER;
    V_SALARY        NUMBER;
BEGIN
    V_DEPARTMENT_ID := 2;
    GET_SALARY(V_DEPARTMENT_ID, V_SALARY);
    DBMS_OUTPUT.PUT_LINE(V_SALARY);
END;
/