PROCEDURE EXAMPLE

CREATE OR REPLACE PROCEDURE DEPT_NAME_INSERT(
                p_empno     IN EMP.EMPNO%TYPE,
                p_ename     IN EMP.ENAME%TYPE,
                p_job       IN EMP.JOB%TYPE,
                p_mgr       IN EMP.MGR%TYPE,
                p_hiredate  IN EMP.HIREDATE%TYPE,
                p_sal       IN EMP.SAL%TYPE,
                p_comm      IN EMP.COMM%TYPE,
                p_dname     IN DEPT.DNAME%TYPE)
       
IS
P_COUNT NUMBER;
l_dept number;
BEGIN
  SELECT COUNT(*) INTO P_COUNT FROM DEPT WHERE DNAME =P_DNAME;
IF P_COUNT>0
  THEN
  begin
  SELECT DEPTNO into l_dept FROM DEPT WHERE DNAME=P_DNAME ;
   INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
   VALUES(P_EMPNO,P_ENAME,P_JOB,P_MGR,P_HIREDATE,P_SAL,P_COMM,l_dept);
   end;
ELSE
begin
SELECT MAX(DEPTNO) into l_dept FROM DEPT;
 INSERT INTO DEPT VALUES(l_dept+10,p_dname,'hyderabad');

 INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
   VALUES(P_EMPNO,P_ENAME,P_JOB,P_MGR,P_HIREDATE,P_SAL,P_COMM,l_dept+10);
   end;
END IF;
END;

COMMIT;

END DEPT_NAME_INSERT;