PL/SQL problem

sad_engineer

sad_engineer

@sad-engineer-ntejaB • Oct 23, 2024
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..
Like 0 Replies 1

Replies

Welcome, guest

Join CrazyEngineers to reply, ask questions, and participate in conversations.

Sign up Log in

CrazyEngineers powered by Jatra Community Platform

  • Anoop Kumar

    Anoop Kumar

    @anoop-kumar-GDGRCn • Dec 1, 2012

    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;
    0