Plsql Programs

Write an anonymous PL/SQL block that fetches and displays the data from employee table to the console.

 declare
 essn number:=&
 sal employee.salary% type;
 name employee.minit%type;
 begin
 select salary,minit into sal,name from employee
 where ssn=essn;
 DBMS_OUTPUT.PUT_LINE(sal);
 DBMS_OUTPUT.PUT_LINE(name);
exception
 when no_data_found then
 DBMS_OUTPUT.PUT_LINE('no data found');
 end;
 /
 

output:

 Enter value for amp: 102
old   2:  essn number:=&
new   2:  essn number:=102;
no data found

PL/SQL procedure successfully completed.

SQL> /
Enter value for amp: 333445555
old   2:  essn number:=&
new   2:  essn number:=333445555;
40000
T

PL/SQL procedure successfully completed.

Write a program that updates salaries of all employees with 10 % hike (use cursors).

declare
name employee.fname%type;
 salary employee.salary%type;
cursor c2 is select fname,salary from employee;
 BEGIN
 open c2;
 UPDATE EMPLOYEE SET SALARY=SALARY+(SALARY*0.1);
 loop
 fetch c2 into name,salary;
 exit when c2%NOTFOUND;
 DBMS_OUTPUT.PUT_LINE(name||'--->'||salary);
 end loop;
 END;




 14  /




output:


update
john--->30000
franklin--->40000
alicia--->25000
jennifer--->43000
ramesh--->38000
Joyce--->25000
Ahmad--->25000
james--->55000

PL/SQL procedure successfully completed.