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..
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 KumarProb 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...