JDBC公共操作方法(六):存储过程

存储过程

?

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;/