Monday, 15 February 2016

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;

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

No comments:

Post a Comment