Oracle Pl/Sql - Questions and Answers 1

Oracle PL/SQL - Questions and Answers

 1. Write a program that displays the Netsal(Sal+comm) of an employee entered at        run time from EMP  table.(Use NVL Function).

DECLARE
NETSAL NUMBER;
BEGIN
SELECT NVL(SAL+COMM, SAL) INTO NETSAL FROM EMP WHERE   EMPNO =                            &EMPNO1;
DBMS_OUTPUT.PUT_LINE(NETSAL);
END;

2. Write a program that will prompt for a EMPNO,ENAME,SAL. If EMPNO does      not exists in EMP table then INSERT the EMPNO,ENAME,SAL into the EMP        table else UPDATE ENAME,SAL for that EMPNO.

 DECLARE
   EMPNO1 NUMBER;
   ENAME1 VARCHAR2(50);
   SAL1 NUMBER;
   EMPNO_VAL BOOLEAN;
   NUM NUMBER;
 BEGIN
   EMPNO1 := &EMPNO;
   ENAME1 := '&ENAME';
   SAL1 := &SAL;
   SELECT EMPNO INTO NUM FROM EMP WHERE 
        EMPNO = EMPNO1;
   IF NUM IS NOT NULL THEN
     UPDATE EMP SET ENAME=ENAME1, SAL=SAL1 
       WHERE EMPNO=EMPNO1;
   END IF;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
        INSERT INTO EMP (EMPNO, ENAME, SAL) VALUES (EMPNO1, ENAME1, SAL1); 
        DBMS_OUTPUT.PUT_LINE('SUCCESS');
   END;
  /

      

 3. Write a program that will prompt for number and display whether the number       is ODD or EVEN number.

    DECLARE
    NUM NUMBER;
    BEGIN
    NUM := #
    IF MOD(NUM,2) = 0 THEN
    DBMS_OUTPUT.PUT_LINE('EVEN');
    ELSE
    DBMS_OUTPUT.PUT_LINE('ODD');
    END IF;
    END;
    /

    

 4. Write program that prompts for EMPNO and update his salary based on the             below specification:

    If the employee belongs to DEPTNO 10 then update his salary by 10%.
    If the employee belongs to DEPTNO 20 then update his salary by 15%.
    If the employee belongs to other DEPTNO then update his salary with SALARY+COMM.

    DECLARE
    EMPNO1 NUMBER;
    DEPTNUM NUMBER;
    SALA NUMBER;
    COM NUMBER;
    BEGIN
    EMPNO1 := &EMPNUM;
    SELECT DEPTNO,SAL,COMM INTO DEPTNUM, SALA, COM FROM EMP WHERE EMPNO = EMPNO1;
    IF DEPTNUM = 10 THEN
    SALA := SALA + SALA * 10/100;
    UPDATE EMP SET SAL = SALA WHERE EMPNO = EMPNO1;
    ELSIF DEPTNUM = 20 THEN
    SALA := SALA + SALA * 15/100;
    UPDATE EMP SET SAL = SALA WHERE EMPNO = EMPNO1;
    ELSE
    IF COM IS NULL THEN
    UPDATE EMP SET SAL = SALA WHERE EMPNO = EMPNO1;
    ELSE
    SALA := SALA + COM;
    UPDATE EMP SET SAL = SALA WHERE EMPNO=EMPNO1;
    END IF;
    END IF;
    END;
   /

 

 

 

 5. In sql Prompt create a table MYTABLE1 with RESULT as a column of number      datatype. Write a program that will insert record in the mytable from 1,2,3… to     10 values. Do not insert record 6 and 8.

    DECLARE
    BEGIN
    FOR I IN 1..10 LOOP
    IF I=6 OR I=8 THEN
    CONTINUE;
    END IF;
    INSERT INTO MYTABLE1 VALUES(I);
    END LOOP;
    END;
  /

 6. Write a program that will display the entire row by passing the empno. 

      DECLARE
       EMPLOYEE EMP%ROWTYPE;
       BEGIN
       SELECT * INTO EMPLOYEE FROM EMP WHERE EMPNO=&EMPNUM;
        DBMS_OUTPUT.PUT_LINE(EMPLOYEE.ENAME||', '||EMPLOYEE.EMPNO||',                                                                              '||EMPLOYEE.SAL||', '||EMPLOYEE.DEPTNO);
       END;
       /

  

 7. Write a implicit cursor that will prompt for a JOB and delete the employees  working in that Job. Store the number of records deleted in a session variable.  

  DECLARE
  ROWS_AFFECTED NUMBER;
  JOB1 VARCHAR2(20);
  BEGIN
  JOB1 := '&JOB';
  DELETE FROM EMP WHERE JOB=JOB1;
  ROWS_AFFECTED := SQL%ROWCOUNT;
  IF ROWS_AFFECTED > 0 THEN
  DBMS_OUTPUT.PUT_LINE(ROWS_AFFECTED ||' RECORDS ARE MODIFIED                                                                                          SUCCESSFULLY');
  ELSE
  DBMS_OUTPUT.PUT_LINE('NO RECORDS FOUND');
  END IF;
  END;
  /