PL/SQL problem

Q # 1: Create a PL/SQL block that determines the top employees with respect to their salaries. In a loop get the names and salaries of the top 5 people with respect to salary in the EMP table. Store the name and salaries in TOP_EMP table. Assume that no two employees have the same salary.



create table top_dogs(name char(20), salary varchar(8));
DECLARE
v_num NUMBER (3) := 5;
v_ename emp.ename%TYPE;
v_sal emp.sal%TYPE;
CURSOR emp_cursor is
SELECT ename, sal
FROM emp
WHERE sal IS NOT NULL;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO v_ename, v_sal;
WHILE emp_cursor %ROWCOUNT <= v_num
INSERT INTO top_EMP(name, salary)
VALUES (v_ename, v_sal);
FETCH emp_cursor INTO v_ename, v_sal;
END LOOP;
CLOSE emp_cursor;
COMMIT;
END;
/
Save
SQL> DELETE FROM TOP_EMP;
SQL>START top.sql
SQL >SELECT FROM top_EMP;


Q # 2: Write a PL/SQL block that updates the salary of an employee in EMP table. The user supplies employee number and new salary. If the user enters an employee number that does not exist, no rows will be updated in the employee table. Raise an exception and print a message that an invalid employee number was entered.

ACCEPT p_num -
PROMPT 'Please enter the number'
ACCEPT p_sal -
PROMPT 'Please enter the salary'
DECLARE
Vrec emp1%found;
v_empno NUMBER(3) :=&p_num;
v_sal NUMBER(10) :=&p_sal;
v_empno emp1.empno%type;
v_sal emp1.sal%TYPE;
CURSOR emp1_cursor is
SELECT empno,ename, sal
FROM emp1
WHERE sal IS NOT NULL
BEGIN
OPEN emp1_cursor;
FETCH emp1_cursor INTO v_empno, v_sal;
if emp1.empno%found then
update emp1 set sal = V_sal;
else
dbms_output.put_line('Employee record modified successfully');
end if;
CLOSE emp1_cursor;
COMMIT;
END;

can anyone tell me that what's the error in programs given above...cause they are not running..

Replies

  • Anoop Kumar
    Anoop Kumar
    Prob 1: you forgot to open LOOP and order by clause.

    DECLARE
     
      v_num  NUMBER(3) := 5;
      v_ename emp.ename%TYPE;
      v_sal  emp.sal%TYPE;
      CURSOR emp_cursor is
        SELECT ename, sal FROM emp WHERE sal IS NOT NULL  order by sal desc  ;
     
    BEGIN
      delete from top_EMP;
      OPEN emp_cursor;
      FETCH emp_cursor
        INTO v_ename, v_sal;
      WHILE emp_cursor %ROWCOUNT <= v_num
        Loop
        INSERT INTO top_EMP(name, salary)
                    VALUES(v_ename, v_sal) ;
      FETCH emp_cursor INTO v_ename, v_sal ;
     
      END LOOP ;
        CLOSE emp_cursor;
        --COMMIT;
     
      END;
    

    Problem 2:

    --  select t.ename,t.sal,t.id from EMP t
    declare
     
      v_empno varchar2(10) :=&p_num;
      v_sal NUMBER(10) :=&p_sal;
     
     
      Emp_missing Exception;
      CURSOR emp_cursor is
          SELECT id FROM emp WHERE id=v_empno and sal IS NOT NULL;
    BEGIN
        OPEN emp_cursor;
        FETCH emp_cursor INTO v_empno;
      if emp_cursor%notfound then
            raise Emp_missing;
      else
      update emp set sal = V_sal where id=v_empno;
      end if;
            dbms_output.put_line('Employee record modified successfully' || 'EmpID: ' || v_empno  || '  Salary:' || V_sal );
     
        CLOSE emp_cursor;
      -- COMMIT;
     
     
      exception
     
    when Emp_missing then
        dbms_output.put_line(' No such employ exists with    employ code  '||v_empno);
    end;

You are reading an archived discussion.

Related Posts

hi, when dealing with arrays we often come across mechanism that seems to implement call by reference, of course, during function calls. Is it not the violation of the fact...
- How about introducing "Best Answer" option ? The CEan who asks the question has right to vote for "Best Answer" to whoever's answer he/she finds "most-helpful". If CEan who...
why we use superposition principle in derivation of schoridinger wave equation?????????????
is the statement regarding wave function correct or not --------justifyy ur answer- "periodic variations of wave function gives rise to the deBroglie waves or matter waves associated with the moving...
I'm looking for an opinion on setting up a home network to connect computers and networking enabled devices. If the locations of the gadgets are fixed - would you recommend...