Oracle 存储过程

Question

    创建名为PROC_SHOW_EMP的存储过程，以部门编号为参数，查询并返回该部门平均工资，以及该部门中比该部门平均工资高的员工信息。
CREATE OR REPLACE PROCEDURE
     PROC_SHOW_EMP
     (dept_id employees.employee_id% TYPE,
     avgSal OUT employees.salary% TYPE,
     TYPE EMPTYPE IS TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER,
     empInfo OUT EMPTYPE)
AS
BEGIN
    SELECT AVG(salary)
    INTO avgSal
    FROM employees
    WHERE department_id = dept_id;
    SELECT *
    INTO empInfo
    FROM employees
    WHERE salary > avgSal;
END;
初学Oracle，创建存储过程出现警告，没成功，该怎么写？还有就是为什么我的Oracle不支持ROWTYPE和BINARY_INTEGER,只有ROW和BINARY？

Answer 1

参数里面，要么是in，out或 in out，不能出现定义语句。。另外，你如果要返回比该部门平均工资高的员工信息，建议使用游标。或者 使用 函数 + 管道 的方法

Answer 2

是的，我用游标和包实现了，但是不知道该如何调用，老是出现：'CUR_EMP'不是过程或尚未定义。
包定义如下：
CREATE OR REPLACE PACKAGE PKG_EMP
AS
   TYPE EMPTYPE IS REF CURSOR;
   PROCEDURE PROC_SHOW_EMP
        (dept_id EMPLOYEES.department_id%TYPE,
        AvgSal OUT EMPLOYEES.salary%TYPE,
        CUR_EMP OUT PKG_EMP.EMPTYPE);
END; 
/  --语句分开执行
CREATE OR REPLACE PACKAGE BODY PKG_EMP
AS
    PROCEDURE PROC_SHOW_EMP
        (dept_id EMPLOYEES.department_id%TYPE,
        AvgSal OUT EMPLOYEES.salary%TYPE,
        CUR_EMP OUT PKG_EMP.EMPTYPE)
    AS 
    BEGIN
        SELECT AVG(salary)
        INTO AvgSal
        FROM EMPLOYEES 
        WHERE department_id = dept_id;
        
        OPEN CUR_EMP FOR
        SELECT *
        FROM EMPLOYEES  
        WHERE department_id = dept_id
        AND salary > AvgSal;
    END; 
END;调用语句如下：
DECLARE
   AvgSal INT;
   CUR_EMP PKG_EMP.EMPTYPE;
BEGIN
   PKG_EMP.PROC_SHOW_EMP(1000, AvgSal, CUR_EMP);
   
   DBMS_OUTPUT.PUT_LINE(AvgSal);
   
   FOR CUR IN CUR_EMP
   LOOP
      DBMS_OUTPUT.PUT_LINE(CUR.employee_id || chr(9) || CUR_employee_name);
   END LOOP;
END;

Answer 3

上面的引用错了，请你看一下。还有就是如何得到触发器中表更新后的值，如COUNT(*), AVG(salary)等等，这些值都是表更新前的值，也就是说如果表中原本有2条记录，现在插入了一条，但是COUNT(*)还是2，怎么解决？
触发器定义如下：
CREATE OR REPLACE TRIGGER TRG_EMP_DEPT_STAT 
AFTER INSERT OR DELETE OR UPDATE OF salary
ON EMPLOYEES 
FOR EACH ROW 
DECLARE 
    CURSOR CUR_EMPCNT IS
    SELECT department_name, COUNT(*) empCnt
    FROM EMPLOYEES, DEPARTMENTS
    WHERE EMPLOYEES.department_id = DEPARTMENTS.department_id
    GROUP BY DEPARTMENTS.department_id, department_name;
    
    CURSOR CUR_EMPSAL IS
    SELECT department_name, ROUND(AVG(salary),2) AvgSal
    FROM EMPLOYEES, DEPARTMENTS
    WHERE EMPLOYEES.department_id = DEPARTMENTS.department_id
    GROUP BY DEPARTMENTS.department_id, department_name;
PRAGMA AUTONOMOUS_TRANSACTION;  --可能加上后得不到更新值,不加又会报错
BEGIN
    IF(UPDATING) THEN
       IF(:NEW.salary != :OLD.salary) THEN
          DBMS_OUTPUT.PUT_LINE('部门名称' || ' ' || '平均工资');
          FOR CUR IN CUR_EMPSAL
          LOOP
             DBMS_OUTPUT.PUT_LINE(CUR.department_name || chr(9) || chr(9) || CUR.AvgSal);
          END LOOP;
        END IF;
    ELSE
       DBMS_OUTPUT.PUT_LINE('部门名称' || ' ' || '部门人数');
       FOR CUR IN CUR_EMPCNT
       LOOP
          DBMS_OUTPUT.PUT_LINE(CUR.department_name || chr(9) || chr(9) || CUR.empCnt);
       END LOOP;
    END IF;
END;

Answer 4

上面的引用错了，请你看一下。还有就是如何得到触发器中表更新后的值，如COUNT(*), AVG(salary)等等，这些值都是表更新前的值，也就是说如果表中原本有2条记录，现在插入了一条，但是COUNT(*)还是2，怎么解决？
触发器定义如下：
CREATE OR REPLACE TRIGGER TRG_EMP_DEPT_STAT 
AFTER INSERT OR DELETE OR UPDATE OF salary
ON EMPLOYEES 
FOR EACH ROW 
DECLARE 
    CURSOR CUR_EMPCNT IS
    SELECT department_name, COUNT(*) empCnt
    FROM EMPLOYEES, DEPARTMENTS
    WHERE EMPLOYEES.department_id = DEPARTMENTS.department_id
    GROUP BY DEPARTMENTS.department_id, department_name;
    
    CURSOR CUR_EMPSAL IS
    SELECT department_name, ROUND(AVG(salary),2) AvgSal
    FROM EMPLOYEES, DEPARTMENTS
    WHERE EMPLOYEES.department_id = DEPARTMENTS.department_id
    GROUP BY DEPARTMENTS.department_id, department_name;
PRAGMA AUTONOMOUS_TRANSACTION;  --可能加上后得不到更新值,不加又会报错
BEGIN
    IF(UPDATING) THEN
       IF(:NEW.salary != :OLD.salary) THEN
          DBMS_OUTPUT.PUT_LINE('部门名称' || ' ' || '平均工资');
          FOR CUR IN CUR_EMPSAL
          LOOP
             DBMS_OUTPUT.PUT_LINE(CUR.department_name || chr(9) || chr(9) || CUR.AvgSal);
          END LOOP;
        END IF;
    ELSE
       DBMS_OUTPUT.PUT_LINE('部门名称' || ' ' || '部门人数');
       FOR CUR IN CUR_EMPCNT
       LOOP
          DBMS_OUTPUT.PUT_LINE(CUR.department_name || chr(9) || chr(9) || CUR.empCnt);
       END LOOP;
    END IF;
END;

忘发题目了。如下：
    为EMPLOYEES表创建触发器TRG_EMP_DEPT_STAT,当执行插入或删除操作时，统计操作后各个部门员工人数；当执行更新工资操作时，统计更新后各个部门员工平均工资。

Answer 5

是的，我用游标和包实现了，但是不知道该如何调用，老是出现：'CUR_EMP'不是过程或尚未定义。
包定义如下：
CREATE OR REPLACE PACKAGE PKG_EMP
AS
   TYPE EMPTYPE IS REF CURSOR;
   PROCEDURE PROC_SHOW_EMP
        (dept_id EMPLOYEES.department_id%TYPE,
        AvgSal OUT EMPLOYEES.salary%TYPE,
        CUR_EMP OUT PKG_EMP.EMPTYPE);
END; 
/  --语句分开执行
CREATE OR REPLACE PACKAGE BODY PKG_EMP
AS
    PROCEDURE PROC_SHOW_EMP
        (dept_id EMPLOYEES.department_id%TYPE,
        AvgSal OUT EMPLOYEES.salary%TYPE,
        CUR_EMP OUT PKG_EMP.EMPTYPE)
    AS 
    BEGIN
        SELECT AVG(salary)
        INTO AvgSal
        FROM EMPLOYEES 
        WHERE department_id = dept_id;
        
        OPEN CUR_EMP FOR
        SELECT *
        FROM EMPLOYEES  
        WHERE department_id = dept_id
        AND salary > AvgSal;
    END; 
END;调用语句如下：
DECLARE
   AvgSal INT;
   CUR_EMP PKG_EMP.EMPTYPE;
BEGIN
   PKG_EMP.PROC_SHOW_EMP(1000, AvgSal, CUR_EMP);
   
   DBMS_OUTPUT.PUT_LINE(AvgSal);
   
   FOR CUR IN CUR_EMP
   LOOP
      DBMS_OUTPUT.PUT_LINE(CUR.employee_id || chr(9) || CUR_employee_name);
   END LOOP;
END;
我知道了，原因在于PROC_SHOW_EMP过程已经打开了游标，for语句可以自动打开游标，执行for语句时就会出错

Answer 6

是的，我用游标和包实现了，但是不知道该如何调用，老是出现：'CUR_EMP'不是过程或尚未定义。
包定义如下：
CREATE OR REPLACE PACKAGE PKG_EMP
AS
   TYPE EMPTYPE IS REF CURSOR;
   PROCEDURE PROC_SHOW_EMP
        (dept_id EMPLOYEES.department_id%TYPE,
        AvgSal OUT EMPLOYEES.salary%TYPE,
        CUR_EMP OUT PKG_EMP.EMPTYPE);
END; 
/  --语句分开执行
CREATE OR REPLACE PACKAGE BODY PKG_EMP
AS
    PROCEDURE PROC_SHOW_EMP
        (dept_id EMPLOYEES.department_id%TYPE,
        AvgSal OUT EMPLOYEES.salary%TYPE,
        CUR_EMP OUT PKG_EMP.EMPTYPE)
    AS 
    BEGIN
        SELECT AVG(salary)
        INTO AvgSal
        FROM EMPLOYEES 
        WHERE department_id = dept_id;
        
        OPEN CUR_EMP FOR
        SELECT *
        FROM EMPLOYEES  
        WHERE department_id = dept_id
        AND salary > AvgSal;
    END; 
END;调用语句如下：
DECLARE
   AvgSal INT;
   CUR_EMP PKG_EMP.EMPTYPE;
BEGIN
   PKG_EMP.PROC_SHOW_EMP(1000, AvgSal, CUR_EMP);
   
   DBMS_OUTPUT.PUT_LINE(AvgSal);
   
   FOR CUR IN CUR_EMP
   LOOP
      DBMS_OUTPUT.PUT_LINE(CUR.employee_id || chr(9) || CUR_employee_name);
   END LOOP;
END;1.如果返回游标的话，就不需要再定义type 了，系统有现成的
PROCEDURE PROC_SHOW_EMP(dept_id EMPLOYEES.department_id%TYPE,
                        AvgSal  OUT EMPLOYEES.salary%TYPE,
                        CUR_EMP OUT sys_refcursor);2.返回的是个游标了，你就不能在用for....in 了 ，应该用open...fetch的方式

Answer 7

呵呵、这个问题 我一直找不到解决思路，以前也想用触发器来着，发现根本实现不了看其他人会不
其实我也想到了建一张临时表，然后用:OLD和:.NEW对临时表进行插入，删除，更新等操作，最后在临时表上进行统计。

Answer 8

呵呵、这个问题 我一直找不到解决思路，以前也想用触发器来着，发现根本实现不了看其他人会不
其实我也想到了建一张临时表，然后用:OLD和:.NEW对临时表进行插入，删除，更新等操作，最后在临时表上进行统计。这个方法可以作为没办法的时候用
不知道还有更好的方法没

Answer 9

ORACLE中是不能返回数据集的，如果要返回只能用游标，ＳＱＬ中可以返回数据集的。

Answer 10

这不是一个SQL就能搞定的事情么？