Variables and Cursors

[Pages:4]60-415 Practice lab Solution? Variables and Cursors

1. You will need to download and run the scripts called create_db_OA.sql and populate_db_OA.sql (OA schema) from the wesite web2.uwindsor.ca/courses/cs/rituch/415/resources.html

Create a PL/SQL block that determines the top n salaries of the employees.

a. Execute the script given below to create a new table, top_salaries, for storing the salaries of the employees.

DROP TABLE top_salaries; CREATE TABLE top_salaries(salary NUMBER(8,2));

b. Accept a number n from the user where n represents the number of top n earners from the employees table. For example, to view the top five salaries, enter 5.

Note: Use the DEFINE command to define a variable p_num to provide the value for n. Pass the value to the PL/SQL block through an iSQL*Plus substitution variable.

c.

In the declarative section, declare two variables: num of type NUMBER to accept

the substitution variable p_num, sal of type employees.salary. Declare a cursor, emp_cursor,

that retrieves the salaries of employees in descending order. Remember that the salaries

should not be duplicated.

d. In the executable section, open the loop and fetch top n salaries and insert them into top_salaries table. You can use a simple loop to operate on the data. Also, try and use %ROWCOUNT and %FOUND attributes for the exit condition.

e. After inserting into the top_salaries table, display the rows with a SELECT statement. The output shown represents the five highest salaries in the employees table.

Solution : SET VERIFY OFF; DELETE FROM top_salaries; DEFINE p_num = 5 DECLARE

num NUMBER(3) := &p_num; sal employees.salary%TYPE;

CURSOR emp_cursor IS SELECT distinct salary FROM employees ORDER BY salary DESC;

BEGIN OPEN emp_cursor; FETCH emp_cursor INTO sal; WHILE emp_cursor%ROWCOUNT this will create all tables in the guest database and all the constraints, sequences etc. required for creating and populating the tables. Then download guest_inserts.zip to populate the database. )

Write a Pl/SQL program that fetches a cursor that has a data from the Student table into a %ROWTYPE. Only select students with a STUDENT_ID less than 110. The columns are the student_id, last_name, first_name and a count of the number of classes each student is enrolled in (using the ENROLLMENT table) . Fetch the cursor with a loop and then output all the columns . The output should be similar to as given below :

105 Angel Moskowitz is enrolled in 1 class 109 Larry Walter is enrolled in 2 classes 106 Judith Olvsade is enrolled in 2 classes 102 Fred Crocitto is enrolled in 2 classes 103 J. Landry is enrolled in 1 class 104 Laetia Enison is enrolled in 1 class 107 Catherine Mierzwa is enrolled in 1 class 108 Judy Sethi is enrolled in 1 class

Solution : declare

message varchar2(20);

cursor c_student_enroll IS select s.student_id, last_name, first_name, COUNT(*) enroll from student s, enrollment e where s.student_id = e.student_id and s.student_id < 110 group by s.student_id, last_name, first_name;

student_enroll_record c_student_enroll%ROWTYPE;

begin open c_student_enroll; loop fetch c_student_enroll into student_enroll_record; exit when c_student_enroll%NOTFOUND; message := case student_enroll_record.enroll when 1 then 'class' when null then 'no classes' else 'classes' end ; dbms_output.put_line(student_enroll_record.student_id ||' ' || student_enroll_record.first_name ||' ,, ||student_enroll_record.last_name ||'is enrolled in ' ||student_enroll_record.enroll ||message); end loop; close c_student_enroll;

end; /

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download