Monday, 15 February 2016

ORACLE 11G

PL/SQL PROGRAMS:

Q)CREATE A PROCEDURE TO INSERT AN EMPLOYEE DEATAILS BY PROVIDING ALL COLUMNS EXCEPT DEPTNO AND EMPNO IN EMP TABLE AND BY PROVING DEPTNAME IN DEPT TABLE?
A)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;

ORACLE 11G

PL/SQL PROGRAMS:
Q)WRITE A PROGRAM TO DISPLAY WHETHER A>B OR B>A BY GIVING INPUT TWO    NUMBERS A AND B?

A)DECLARE
   a       NUMBER := &a;
   b       NUMBER := &b;
BEGIN
   IF (a > b)
   THEN
      DBMS_OUTPUT.put_line ('A IS GREATER THAN B');
   ELSE
      DBMS_OUTPUT.put_line ('B IS GREATER THAN A');
   END IF;
END;
*************************************************************************************
 Q)CREATE A PROCEDURE TO INSERT AN EMPLOYEE DETAILS INTO EXISTING TABLE ?
 A) CREATE OR REPLACE PROCEDURE xx_insert_employee (
   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_deptno                         IN       emp.deptno%TYPE
)
IS
   employee_already_exist             EXCEPTION;
L_COUNT NUMBER;
BEGIN
SELECT COUNT(*) INTO L_COUNT FROM emp
WHERE EMPNO = P_EMPNO;
IF L_COUNT>0
   THEN
      RAISE employee_already_exist;
   END IF;
   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
               ,p_deptno
               );

   COMMIT;

EXCEPTION
   WHEN employee_already_exist
   THEN
      DBMS_OUTPUT.put_line ('Employee number already exist in EMP table.');
END xx_insert_employee;

***********************************************************************************