Oracle存储过程的创建怎么破

Question

有员工表和部门表
创建存储过程， 员工新进一个部门时，部门表对应部门人数加1，员工调换部门时，原先部门人数减1，现在部门人数加1

Answer 1

CREATE OR REPLACE PROCEDURE ap.update_data (var_emp       IN     VARCHAR2,
                                            var_new_dpt     IN     VARCHAR2,
                                            var_omsg         OUT VARCHAR2)
AS
   l_exit      EXCEPTION;
   l_count     INT;
   l_old_dpt   VARCHAR2 (50);
----table : emp_table   dpt _able
BEGIN
   SELECT COUNT (*)
     INTO l_count
     FROM emp_table
    WHERE empname = var_emp;

   IF l_count <= 0
   THEN
      var_o_msg := 'emp not exists';
      RAISE l_exit;
   END IF;

   SELECT dptid
     INTO l_old_dpt
     FROM emp_table
    WHERE empname = var_emp;

   UPDATE dpt_table
      SET dptnumber = dptnumber + 1
    WHERE (dptid = var_new_dpt or dptname =var_new_dpt);--看你传入参数是部门id还是名称

   UPDATE dpt_table
      SET dptnumber = dptnumber - 1
    WHERE dptid = l_old_dpt;

   var_omsg := 'information updated ';
   COMMIT;
   RETURN;
EXCEPTION
   WHEN l_exit
   THEN
      ROLLBACK;
      RETURN;
   WHEN OTHERS
   THEN
      ROLLBACK;
END;

随手写的，用触发器其实更好一点，没必要写什么存储过程。