Ex-13.md

PL/SQL Cursors

Q1) Write a PL/SQL Block, to update salaries of all the employees who work in deptno 20 by 15%. If none of the employee’s salary are updated display a message 'None of the salaries were updated'. Otherwise display the total number of employee who got salary updated.

CREATE TABLE emp1 (
  empno NUMBER(4) PRIMARY KEY,
  ename VARCHAR2(20),
  job VARCHAR2(20),
  sal NUMBER(7,2)
);

INSERT INTO emp1 VALUES (7369, 'SMITH', 'CLERK', 800);
INSERT INTO emp1 VALUES (7499, 'ALLEN', 'SALESMAN', 1600);
INSERT INTO emp1 VALUES (7521, 'WARD', 'SALESMAN', 1250);
INSERT INTO emp1 VALUES (7566, 'JONES', 'MANAGER', 2975);
INSERT INTO emp1 VALUES (7654, 'MARTIN', 'SALESMAN', 1250);
INSERT INTO emp1 VALUES (7698, 'BLAKE', 'MANAGER', 2850);
INSERT INTO emp1 VALUES (7782, 'CLARK', 'MANAGER', 2450);
INSERT INTO emp1 VALUES (7788, 'SCOTT', 'ANALYST', 3000);
INSERT INTO emp1 VALUES (7839, 'KING', 'PRESIDENT', 5000);
INSERT INTO emp1 VALUES (7844, 'TURNER', 'ANALYST', 3000);
INSERT INTO emp1 VALUES (7876, 'ADAMS', 'CLERK', 5000);
INSERT INTO emp1 VALUES (7900, 'JAMES', 'CLERK', 950);
INSERT INTO emp1 VALUES (7902, 'FORD', 'ANALYST', 3000);
INSERT INTO emp1 VALUES (7934, 'MILLER', 'CLERK', 6000);

Declare
     num number(5);
     Begin
    update emp1 set sal = sal + sal*0.15 where deptno=20;
     if SQL%NOTFOUND then
     dbms_output.put_line('none of the salaries were updated');
     elsif SQL%FOUND then
    num := SQL%ROWCOUNT;
     dbms_output.put_line('salaries for ' || num || 'employees are updated');
  end if;
   End;
 12  /

Q2 Create a table emp_grade with columns empno & grade. Write PL/SQL block to insert values into the table emp_grade by processing emp table with the following constraints. If sal <= 1400 then grade is ’C’ Else if sal between 1401 and 2000 then the grade is ‘B’ Else the grade is ‘A’.

create table emp_grade(empno number, grade char(2)); 
Declare	
	Emp_rec emp1%rowtype;
	Cursor c is select * into emp_grade from emp1;
Begin
	Open c;
	If c%ISOPEN then Loop
		Fetch c into emp_rec;
	If c%notfound then 
	Exit; 
	End if; 
	If emp_rec.sal <= 1400 then
		Insert into emp_grade values(emp_rec.empno,'C'); 
	Elsif emp_rec.sal between 1401 and 2000 then
		Insert into emp_grade values(em_rec.empno,'B'); 
	Else
		Insert into emp_grade values(em_rec.empno,'A'); 
	End if;
	End loop;
	Else
End if;
End;