存储过程
CREATE OR REPLACE PROCEDURE P_ADD_EMP(ENO IN NUMBER,
EMPNAME IN VARCHAR2,
EMPJOB IN VARCHAR2,
EMPMGR IN NUMBER,
EMPHIREDATE IN DATE,
EMPSAL IN NUMBER,
EMPCOMM IN NUMBER,
EDEPTNO IN NUMBER,
RCODE OUT NUMBER) IS
V_CODE NUMBER(1) := -1;
V_SQL VARCHAR2(200);
BEGIN
IF ENO IS NULL THEN
RCODE := -1;
RETURN;
END IF;
-- 查找数据库中是否已存在要添加的empno
SELECT COUNT(EMPNO) INTO V_CODE FROM EMP WHERE EMPNO = ENO;
IF V_CODE > 0 THEN
--如果已经存在要添加的empno
RCODE := 1;
ELSE
-- 查找数据库中是否已存在deptno
SELECT COUNT(DEPTNO) INTO V_CODE FROM DEPT WHERE DEPTNO = EDEPTNO;
IF V_CODE > 0 THEN
-- 如果已经存在deptno,则允许添加emp
INSERT INTO EMP E
VALUES
(ENO,
EMPNAME,
EMPJOB,
EMPMGR,
EMPHIREDATE,
EMPSAL,
EMPCOMM,
EDEPTNO);
RCODE := 0;
COMMIT;
ELSE
RCODE := 2;
END IF;
END IF;
EXCEPTION
WHEN OTHERS THEN
RCODE := 3;
ROLLBACK;
END P_ADD_EMP;
/