MIS 4386 PL SQL Opportunity 3

1. Create a PL/SQL block that uses the EMP table associated with PL/SQL Opportunity No. 2 (S22-emptable.sql) to display what the revised salary of each employee would be if the president's salary were reduced by 20 percent and all other employees received a 10 percent increase in their salary.

Comments on Exercise 1

• This will be the first PL/SQL block that you write that will require the use of a cursor (which will need to be declared, opened, used in a fetch statement and closed) along with a loop and an explicit cursor attribute. In addition, you will also need to use an IF statement.
• Use the following dbms_output.put_line command to display the name and revised salary each employee.

dbms_output.put_line (enamehold || ' ' || new_salary);

• Your DBMS_OUTPUT.PUT_LINE command will be used to display character literals and PL/SQL variables. The concatenation operator (||) will be of frequent use in your DBMS_OUTPUT.PUT_LINE commands. By the way, the character literal ' (i.e., a single quote, followed by a space, followed by a single quote) causes a space to be displayed.
• The output produced by your dbms_output.put_line command should be:

SMITH 880
ALLEN 1760
WARD 1375
JONES 3272.5
MARTIN 1375
BLAKE 3135
CLARK 2695
SCOTT 3300
KING 4000
TURNER 1650
ADAMS 1210
JAMES 1045
FORD 3300
MILLER 1430

• The output from a dbms_output. put_line command cannot be formatted nicely. Other than using a fixed font let's not worry about the appearance.

2. This exercise involves the example that appears on page 16 of the PL/SQL Primer. Attached to this opportunity is the file S22-example4extra.sql.

declare
emp_name emp.ename%type;
dept_num emp.deptno%type;
count1 number(5) := 0;
cursor emp_cursor is
  select ename, deptno from emp;
begin
  open emp_cursor;
  loop
    fetch emp_cursor into emp_name, dept_num;
    count1 := count1 + 1;
    if emp_cursor%rowcount > 10 then exit;
    end if;
  end loop;
  close emp_cursor;
  dbms_output.put_line ('count1 = '||count1);
end;
/

Modify this PL/SQL block so that the following output is displayed via the use of dbms_output.put_line commands.

Number of employees 14
Total salary 29025
Average salary 2073

This will require (a) the addition of the definition of some PL/SQL variables in the DECLARE section, (b) replacing the explicit cursor attribute %rowcount with %notfound, and (c) the calculation of the total salary and average salary of all employees.

3. This is optional and for extra credit should you wish to do it. Modify the PL/SQL block in Exercise 2 so that the use of an explicit cursor is replaced by the use of a Cursor FOR Loop.

4. The purpose of this exercise is for you to "play computer" (in other words I would prefer you not type it in and then run it) with the following PL/SQL program which is a modified version of the PL/SQL Program found on page 32 of the PL/SQL Primer handout. Your job is to indicate the content of the RATIO table after the execution of the PL/SQL program along with the output generated by the DBMS_OUTPUT.PUT_LINE command. Please round the calculated ratios to the nearest two decimal places.

SQL> select * from result_table;

 SAMPLE_ID          X          Y
---------- ---------- ----------
       130         70         87
       131         77        194
       132         73          0
       133         81         98
       134          0         56
       135         54         68
       136         71          0
       137         65         32

8 rows selected.

SQL> select * from ratio;

no rows selected

SQL> start e:mis4386fall21plsqlstuffex6brevs21forclass
SQL> DECLARE
  2    SAMP_NUM   NUMBER;
  3    SAMPLE_NUM         NUMBER;
  4    NUMERATOR          NUMBER;
  5    DENOMINATOR        NUMBER;
  6    THE_RATIO          NUMBER;
  7    LOWER_LIMIT        CONSTANT NUMBER := 0.72;
  8    counter number(5) := 0;
  9    CURSOR C1 IS
 10         SELECT SAMPLE_ID, X, Y FROM RESULT_TABLE;
 11  BEGIN
 12    OPEN C1;
 13    LOOP
 14        FETCH C1 INTO SAMP_NUM, NUMERATOR, DENOMINATOR;
 15              EXIT WHEN C1%NOTFOUND;
 16        BEGIN
 17               counter := counter + 1;
 18        THE_RATIO := NUMERATOR/DENOMINATOR;
 19              INSERT INTO RATIO VALUES (SAMP_NUM, THE_RATIO);
 20  
 21        EXCEPTION
 22          WHEN ZERO_DIVIDE THEN
 23              DBMS_OUTPUT.PUT_LINE ('Attempted to divide by Zero for samp num '||samp_num);
 24          WHEN OTHERS THEN
 25              ROLLBACK;
 26        END;
 27  
 28    END LOOP;
 29  CLOSE C1;
 30  COMMIT;
 31  dbms_output.put_line ('Have handled all ' || counter ||' rows in RESULT_TABLE');
 32  END;
 33  /

PL/SQL procedure successfully completed.

SQL> select * from ratio;

 SAMPLE_ID      RATIO

S22-emptable.sql

set echo on
set feedback on
drop table dept cascade constraints;
drop table emp cascade constraints;
drop table temp2 cascade constraints;
drop table temp2a cascade constraints;
create table dept (deptno number(2) primary key, dname varchar2(14), loc varchar2(13));
create table emp (empno number(4) primary key, ename varchar2(10) not null, job varchar2(10),
mgr number(4), hiredate date, sal number(7,2), comm number(7,2),
deptno number(2) not null references dept (deptno));
create table temp2 (num_col1 number, num_col2 number, char_col varchar2(25));
create table temp2a (num_col1 number, num_col2 number, char_col varchar2(25));
insert into dept values (10, 'ACCOUNTING', 'NEW YORK');
insert into dept values (20, 'RESEARCH', 'DALLAS');
insert into dept values (30, 'SALES', 'CHICAGO');
insert into dept values (40, 'OPERATIONS', 'BOSTON');
insert into emp values (7369, 'SMITH', 'CLERK', 7902, '17-dec-80', 800, null, 20);
insert into emp values (7499, 'ALLEN', 'SALESMAN', 7698, '20-feb-81', 1600, 300, 30);
insert into emp values (7521, 'WARD', 'SALESMAN', 7698, '22-feb-81', 1250, 500, 30);
insert into emp values (7566, 'JONES', 'MANAGER', 7839, '02-apr-81', 2975, null, 20);
insert into emp values (7654, 'MARTIN', 'SALESMAN', 7698, '28-sep-81', 1250, 1400, 30);
insert into emp values (7698, 'BLAKE', 'MANAGER', 7839, '01-may-81', 2850, null, 30);
insert into emp values (7782, 'CLARK', 'MANAGER', 7839, '09-jun-81', 2450, null, 10);
insert into emp values (7788, 'SCOTT', 'ANALYST', 7566, '19-apr-87', 3000, null, 20);
insert into emp values (7839, 'KING', 'PRESIDENT', null, '17-nov-81', 5000, null, 10);
insert into emp values (7844, 'TURNER', 'SALESMAN', 7698, '08-sep-81', 1500, 0, 30);
insert into emp values (7876, 'ADAMS', 'CLERK', 7788, '23-may-87', 1100, null, 20);
insert into emp values (7900, 'JAMES', 'CLERK', 7698, '03-dec-81', 950, null, 30);
insert into emp values (7902, 'FORD', 'ANALYST', 7566, '03-dec-81', 3000, null, 20);
insert into emp values (7934, 'MILLER', 'CLERK', 7782, '23-jan-82', 1300, null, 10);
commit;
set linesize 130
set pagesize 100
desc dept
select * from dept;
desc emp
select * from emp;
desc temp2
select * from temp2;
desc temp2a
select * from temp2a;

S22-example4extra.sql

declare
emp_name emp.ename%type;
dept_num emp.deptno%type;
count1 number(5) := 0;
cursor emp_cursor is
select ename, deptno from emp;
begin
open emp_cursor;
loop
fetch emp_cursor into emp_name, dept_num;
count1 := count1 + 1;
if emp_cursor%rowcount > 10 then exit;
end if;
end loop;
close emp_cursor;
dbms_output.put_line ('count1 = '||count1);
end;
/