|
Oracle PL/SQL |
1.
Write a procedure that has the
user enter two numbers and then calculates the sum, difference, product and
quotient of the two numbers. Create the procedure and execute it. Show the
output from the procedure.
--
--Write a procedure that has the user enter two numbers
--and then calculates the sum, difference, product and quotient of the
--two numbers. Create the procedure and execute it. Show the output
--from the procedure.
spool a:\h3q4res
set serveroutput on
--
CREATE OR REPLACE PROCEDURE math (n1 NUMBER, n2 NUMBER) AS
total NUMBER;
diff NUMBER;
prod NUMBER;
quot NUMBER;
BEGIN
total := n1 + n2;
diff := n1 - n2;
prod := n1 * n2;
quot := n1 / n2;
DBMS_OUTPUT.PUT_LINE('N1 N2 SUM DIFFERENCE PRODUCT QUOTIENT');
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
DBMS_OUTPUT.PUT_LINE(RPAD(ROUND(n1,2),6)||RPAD(ROUND(n2,2),6)
||RPAD(ROUND(total,2),8)||RPAD(ROUND(diff,2),14)
||RPAD(ROUND(prod,2),12)||RPAD(ROUND(quot,2),12));
END;
/
--
--execute the procedure
exec math(4.67,98.2)
exec math(48,101.3)
--
--Output
--
Procedure created.
N1 N2 SUM DIFFERENCE PRODUCT QUOTIENT
--------------------------------------------------
4.67 98.2 102.87 -93.53 458.59 .05
PL/SQL procedure successfully completed.
N1 N2 SUM DIFFERENCE PRODUCT QUOTIENT
--------------------------------------------------
48 101.3 149.3 -53.3 4862.4 .47
PL/SQL procedure successfully completed.
SQL> spool off
2. Create the baseball table from the student folder
and insert the data.
a.
Write a procedure to find and
output the name of the player who has hit the most home runs and the number of
home runs he has hit.
b.
Write a procedure to find and
output the average number of hits for the players in the table.
c.
Write a procedure to show the
current age in years for the players in the table who are now alive.
d.
Run and execute the procedures
and show the output.
--
--Create the baseball table from the student folder and insert the data.
-- a. Write a procedure to find and output the name of the
-- player who has hit the most home runs and the number of
-- home runs he has hit.
-- b. Write a procedure to find and output the average number of
-- hits for the players in the table.
-- c. Write a procedure to show the current age in years for the
-- players in the table who are now alive.
-- d. Run and execute the procedures and show the output.
--
spool a:\h3q5
set serveroutput on
--
--procedure on home runs
--
create or replace procedure homers as
type tbb is table of baseball%rowtype
index by binary_integer;
vbb tbb;
vage number;
i number := 1;
cursor cbb is select * from baseball
where home_runs = (select max(home_runs) from baseball);
begin
dbms_output.enable(10000);
open cbb;
fetch cbb into vbb(i);
while cbb%found loop
dbms_output.put_line(vbb(i).first_name||' '||vbb(i).last_name||' has hit'
||vbb(i).home_runs||', the most of any player.');
fetch cbb into vbb(i);
end loop;
end;
/
--
--procedure on hits
--
CREATE OR REPLACE PROCEDURE avghits AS
vAvghits baseball.hits%TYPE;
BEGIN
SELECT AVG(hits) INTO vAvghits FROM baseball;
vAvghits := ROUND(vAvghits,0);
DBMS_OUTPUT.PUT_LINE('The average number of hits is '||vAvghits);
END;
/
--
--procedure to get names of those with above average hits
--
CREATE OR REPLACE PROCEDURE plavghits AS
vHits baseball.hits%TYPE;
vFN baseball.first_name%TYPE;
vLN baseball.last_name%TYPE;
CURSOR cbbave IS SELECT first_name, last_name, hits FROM baseball
WHERE hits >= (SELECT AVG(hits) FROM baseball)
ORDER BY last_name;
BEGIN
OPEN cbbave;
FETCH cbbave into vFN, vLN, vHits;
DBMS_OUTPUT.PUT_LINE('The players with above average hits are:');
WHILE cbbave%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(vFN||' '||vLN,20)||LPAD(vHits,8));
FETCH cbbave into vFN, vLN, vHits;
END LOOP;
END;
/
--create a procedure to get ages
--in this case I'm using one procedure, but it could be divided into two
--
create or replace procedure bb as
age number;
vplayer baseball%rowtype;
cursor cplayer is select * from baseball;
begin
dbms_output.enable(10000);
open cplayer;
fetch cplayer into vplayer;
while cplayer%found loop
if vplayer.dod is NULL then
age := round((months_between(sysdate, vplayer.dob))/12,2);
dbms_output.put_line(vplayer.first_name||' '||vplayer.last_name||
' is '||age||' years old.');
else
age := round((months_between(vplayer.dod, vplayer.dob))/12,2);
dbms_output.put_line(vplayer.first_name||' '||vplayer.last_name||
' died on '||vplayer.dod||'at the age of '||age||' years.');
end if;
fetch cplayer into vplayer;
end loop;
close cplayer;
end;
/
exec homers
exec bb
--
--Output of homers
--
Procedure created.
Hank Aaron has hit 755, the most of any player.
PL/SQL procedure successfully completed.
Procedure created.
--
--Output of avghits and plavghits
--
Procedure created.
The average number of hits is 2388
PL/SQL procedure successfully completed.
The players with above average hits are:
Hank Aaron 3771
Cap Anson 3418
Luke Appling 2749
Ernie Banks 2583
Roberto Clemente 3000
Ty Cobb 4189
Jimmie Foxx 2646
Lou Gehrig 2721
Goose Goslin 2735
Rogers Hornsby 2930
Reggie Jackson 2548
Nap Lajoie 3242
Rabbit Maranville 2605
Willie Mays 3283
Stuffy McInnis 2405
Paul Molitor 3014
Stan Musial 3630
Cal Ripken 2549
Frank Robinson 2943
Brooks Robinson 2848
Babe Ruth 2873
Tris Speaker 3514
Pie Traynor 2416
Honus Wagner 3415
Paul Waner 3152
Lloyd Waner 2459
Ted Williams 2654
PL/SQL procedure successfully completed.
--
--Output of bb
--
Procedure created.
Ty Cobb died on 17-JUL-61 at the age of 74.58 years.
Hank Aaron is 65.79 years old.
Stan Musial is 79 years old.
Tris Speaker died on 08-DEC-58 at the age of 70.68 years.
Cap Anson died on 14-APR-22 at the age of 70.01 years.
Honus Wagner died on 06-DEC-55 at the age of 81.78 years.
Willie Mays is 68.54 years old.
Nap Lajoie died on 07-FEB-59 at the age of 84.42 years.
Paul Waner died on 29-AUG-65 at the age of 62.37 years.
Paul Molitor is 43.25 years old.
Roberto Clemente died on 31-DEC-72 at the age of 38.37 years.
Frank Robinson is 64.22 years old.
Rogers Hornsby died on 05-JAN-63 at the age of 66.69 years.
Babe Ruth died on 16-AUG-48 at the age of 53.53 years.
Brooks Robinson is 62.51 years old.
Luke Appling died on 03-JAN-91 at the age of 83.75 years.
Goose Goslin died on 15-MAY-71 at the age of 70.58 years.
Lou Gehrig died on 02-JUN-41 at the age of 37.95 years.
Ted Williams is 81.22 years old.
Jimmie Foxx died on 21-JUL-67 at the age of 59.75 years.
Rabbit Maranville died on 05-JAN-54 at the age of 62.15 years.
Ernie Banks is 68.81 years old.
Cal Ripken is 39.24 years old.
Reggie Jackson is 53.51 years old.
Lloyd Waner died on 22-JUL-82 at the age of 76.35 years.
Pie Traynor died on 16-MAR-72 at the age of 72.35 years.
Stuffy McInnis died on 16-FEB-60 at the age of 69.41 years.
Joe Torre is 59.34 years old.
Eddie Mathews is 68.1 years old.
Kiki Cuyler died on 11-FEB-50 at the age of 51.45 years.
Joe DiMaggio died on 08-MAR-99 at the age of 84.29 years.
Willie McCovey is 61.86 years old.
Pee Wee Reese died on 14-AUG-99 at the age of 81.06 years.
Don Mattingly is 38.58 years old.
Yogi Berra is 74.52 years old.
Duke Snider is 73.17 years old.
Mark Grace is 35.4 years old.
Gil Hodges died on 02-APR-72 at the age of 47.99 years.
Joe Adcock died on 03-MAY-99 at the age of 71.51 years.
Ted Kluszewski died on 29-MAR-88 at the age of 63.55 years.
Bobby Thomson is 76.07 years old.
Hank Greenberg died on 04-SEP-86 at the age of 75.67 years.
Jackie Robinson died on 24-OCT-72 at the age of 53.73 years.
Mark McGwire is 36.14 years old.
Hack Wilson died on 23-NOV-48 at the age of 48.58 years.
Sammy Sosa is 31.02 years old.
Roy Campanella died on 26-JUN-93 at the age of 71.6 years.
Al Spangler is 66.37 years old.
Dutch Zwilling died on 27-MAR-78 at the age of 89.4 years.
Warren Spahn is 78.58 years old.
PL/SQL procedure successfully completed.
SQL> spool off
3. Create the planet table and insert the data.
a.
Write a procedure that allows
the user to input the name of a planet and then calculates the circumference of
the planets orbit (where circumference = 2 * p *
radius, p = 3.14159 and radius is the distance of the planet
from the sun.)
b.
Run and execute the procedure
and show the output.
--
-- Create the planet table and insert the data.
--a. Write a procedure that allows the user to input the name of a planet
-- and then calculates the circumference of the planets orbit (where
-- circumference = 2 * p * radius, p = 3.14159 and radius is the distance
-- of the planet from the sun.)
--b. Run and execute the procedure and show the output.
--
--use the file planet.txt to create and populate the table
--
spool a:\h3q6res
set serveroutput on
--
CREATE OR REPLACE PROCEDURE planets AS
TYPE tNums IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
vPlanet planet%ROWTYPE;
vSpeed tNums;
vCircum tNums;
vCounter NUMBER := 1;
PI CONSTANT NUMBER := 3.14159;
CURSOR cPlanet is SELECT * FROM planet;
BEGIN
OPEN cPlanet;
FETCH cPlanet INTO vPlanet;
DBMS_OUTPUT.ENABLE(10000);
DBMS_OUTPUT.PUT_LINE('Planet Orbit(km) Speed(km/hr)');
DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------');
WHILE cPlanet%FOUND LOOP
IF vPlanet.Orbperiod_days IS NULL OR vPlanet.Distance_km IS NULL THEN
NULL;
ELSE
vCircum(vCounter) := vPlanet.Distance_km * PI * 2;
vSpeed(vCounter) := vCircum(vCounter)/(vPlanet.Orbperiod_days * 24);
DBMS_OUTPUT.PUT_LINE(RPAD(vPlanet.Name,10)||' '
||LPAD(ROUND(vCircum(vCounter),0),16)||' '||LPAD(ROUND(vSpeed(vCounter),0),25));
END IF;
vCounter := vCounter + 1;
FETCH cPlanet INTO vPlanet;
END LOOP;
CLOSE cPlanet;
END;
/
--
--execute the procedure
exec planets
--
--Output
--
Procedure created.
Planet Orbit(km) Speed(km/hr)
-----------------------------------------------------
Mercury 363858954 172340
Venus 679840076 126064
Earth 939963728 107225
Mars 1432188049 86865
Jupiter 4890387489 47030
Saturn 8981177492 34780
Uranus 18038946948 24495
Neptune 28301327674 19592
Pluto 37155710594 17050
PL/SQL procedure successfully completed.
SQL> spool off
4.
Write a procedure that
calculates the maximum or minimum of three numbers based on the user’s input of
three numbers and request for the maximum or minimum.
--
--Write a procedure that calculates the maximum or minimum of three
--numbers based on the user’s input of three numbers and request for the
--maximum or minimum.
--
spool a:\h4q1
set serveroutput on
--
CREATE OR REPLACE PROCEDURE minmax (req CHAR, n1 NUMBER, n2 NUMBER, n3 NUMBER) AS
BEGIN
IF UPPER(req) = 'MIN' OR UPPER(req) = 'MINIMUM' THEN
IF n1 < n2 AND n1 <= n3 THEN
DBMS_OUTPUT.PUT_LINE('You have entered '||n1||' '||n2||' '||n3);
DBMS_OUTPUT.PUT_LINE('The smallest number is '||n1);
ELSIF n1 < n2 AND n3 < n1 THEN
DBMS_OUTPUT.PUT_LINE('You have entered '||n1||' '||n2||' '||n3);
DBMS_OUTPUT.PUT_LINE('The smallest number is '||n3);
ELSIF n2 < n1 AND n2 <= n3 THEN
DBMS_OUTPUT.PUT_LINE('You have entered '||n1||' '||n2||' '||n3);
DBMS_OUTPUT.PUT_LINE('The smallest number is '||n2);
ELSIF n2 < n1 AND n3 < n2 THEN
DBMS_OUTPUT.PUT_LINE('You have entered '||n1||' '||n2||' '||n3);
DBMS_OUTPUT.PUT_LINE('The smallest number is '||n3);
ELSIF n1 <= n2 AND n1 < n3 THEN
DBMS_OUTPUT.PUT_LINE('You have entered '||n1||' '||n2||' '||n3);
DBMS_OUTPUT.PUT_LINE('The smallest number is '||n1);
ELSIF n3 < n1 AND n1 <= n2 THEN
DBMS_OUTPUT.PUT_LINE('You have entered '||n1||' '||n2||' '||n3);
DBMS_OUTPUT.PUT_LINE('The smallest number is '||n3);
ELSE
DBMS_OUTPUT.PUT_LINE('You have entered '||n1||' '||n2||' '||n3);
DBMS_OUTPUT.PUT_LINE('The numbers are all equal and their value is '||n1);
END IF;
ELSIF UPPER(req)= 'MAX' OR UPPER(req) = 'MAXIMUM' THEN
IF n1 > n2 AND n1 >= n3 THEN
DBMS_OUTPUT.PUT_LINE('You have entered '||n1||' '||n2||' '||n3);
DBMS_OUTPUT.PUT_LINE('The largest number is '||n1);
ELSIF n1 > n2 AND n3 > n1 THEN
DBMS_OUTPUT.PUT_LINE('You have entered '||n1||' '||n2||' '||n3);
DBMS_OUTPUT.PUT_LINE('The largest number is '||n3);
ELSIF n2 > n1 AND n2 >= n3 THEN
DBMS_OUTPUT.PUT_LINE('You have entered '||n1||' '||n2||' '||n3);
DBMS_OUTPUT.PUT_LINE('The largest number is '||n2);
ELSIF n2 > n1 AND n3 > n2 THEN
DBMS_OUTPUT.PUT_LINE('You have entered '||n1||' '||n2||' '||n3);
DBMS_OUTPUT.PUT_LINE('The largest number is '||n3);
ELSIF n1 >= n2 AND n1 > n3 THEN
DBMS_OUTPUT.PUT_LINE('You have entered '||n1||' '||n2||' '||n3);
DBMS_OUTPUT.PUT_LINE('The largest number is '||n1);
ELSIF n3 > n1 AND n1 >= n2 THEN
DBMS_OUTPUT.PUT_LINE('You have entered '||n1||' '||n2||' '||n3);
DBMS_OUTPUT.PUT_LINE('The largest number is '||n3);
ELSE
DBMS_OUTPUT.PUT_LINE('You have entered '||n1||' '||n2||' '||n3);
DBMS_OUTPUT.PUT_LINE('The numbers are all equal and their value is '||n1);
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE('You have entered invalid data.
Please enter max or min and three numbers.');
END IF;
END;
/
exec minmax('min',43,43,43)
exec minmax('min',43,43,54)
exec minmax('min',43,43,35)
exec minmax('min',54,43,43)
exec minmax('min',35,43,43)
exec minmax('min',43,54,43)
exec minmax('min',43,35,43)
exec minmax('min',35,43,54)
exec minmax('min',35,54,43)
exec minmax('min',43,35,54)
exec minmax('min',54,35,43)
exec minmax('min',54,43,35)
exec minmax('min',43,54,35)
exec minmax('max',43,43,43)
exec minmax('max',43,43,54)
exec minmax('max',43,43,35)
exec minmax('max',54,43,43)
exec minmax('max',35,43,43)
exec minmax('max',43,54,43)
exec minmax('max',43,35,43)
exec minmax('max',35,43,54)
exec minmax('max',35,54,43)
exec minmax('max',43,35,54)
exec minmax('max',54,35,43)
exec minmax('max',54,43,35)
exec minmax('max',43,54,35)
--
--Output
--
Procedure created.
You have entered 43 43 43
The numbers are all equal and their value is 43
PL/SQL procedure successfully completed.
You have entered 43 43 54
The smallest number is 43
PL/SQL procedure successfully completed.
You have entered 43 43 35
The smallest number is 35
PL/SQL procedure successfully completed.
You have entered 54 43 43
The smallest number is 43
PL/SQL procedure successfully completed.
You have entered 35 43 43
The smallest number is 35
PL/SQL procedure successfully completed.
You have entered 43 54 43
The smallest number is 43
PL/SQL procedure successfully completed.
You have entered 43 35 43
The smallest number is 35
PL/SQL procedure successfully completed.
You have entered 35 43 54
The smallest number is 35
PL/SQL procedure successfully completed.
You have entered 35 54 43
The smallest number is 35
PL/SQL procedure successfully completed.
You have entered 43 35 54
The smallest number is 35
PL/SQL procedure successfully completed.
You have entered 54 35 43
The smallest number is 35
PL/SQL procedure successfully completed.
You have entered 54 43 35
The smallest number is 35
PL/SQL procedure successfully completed.
You have entered 43 54 35
The smallest number is 35
PL/SQL procedure successfully completed.
You have entered 43 43 43
The numbers are all equal and their value is 43
PL/SQL procedure successfully completed.
You have entered 43 43 54
The largest number is 54
PL/SQL procedure successfully completed.
You have entered 43 43 35
The largest number is 43
PL/SQL procedure successfully completed.
You have entered 54 43 43
The largest number is 54
PL/SQL procedure successfully completed.
You have entered 35 43 43
The largest number is 43
PL/SQL procedure successfully completed.
You have entered 43 54 43
The largest number is 54
PL/SQL procedure successfully completed.
You have entered 43 35 43
The largest number is 43
PL/SQL procedure successfully completed.
You have entered 35 43 54
The largest number is 54
PL/SQL procedure successfully completed.
You have entered 35 54 43
The largest number is 54
PL/SQL procedure successfully completed.
You have entered 43 35 54
The largest number is 54
PL/SQL procedure successfully completed.
You have entered 54 35 43
The largest number is 54
PL/SQL procedure successfully completed.
You have entered 54 43 35
The largest number is 54
PL/SQL procedure successfully completed.
You have entered 43 54 35
The largest number is 54
PL/SQL
procedure successfully completed.
SQL> spool off
5.
Write a procedure to define a
table/array to hold the numbers between one and 20. Output the even numbers,
one per line.
--
--Write a procedure to define a table/array to hold the numbers between
--one and 20. Output the even numbers, one per line.
--
spool a:\h4q2
set serveroutput on
--
CREATE OR REPLACE PROCEDURE evens AS
TYPE tnums IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
vnums tnums;
vcount NUMBER := 1;
BEGIN
DBMS_OUTPUT.PUT_LINE('The even numbers between one and 20 are:');
WHILE vcount <= 20 LOOP
vnums(vcount) := vcount;
IF MOD(vnums(vcount),2) = 0 THEN
DBMS_OUTPUT.PUT_LINE(vnums(vcount));
END IF;
vcount := vcount + 1;
END LOOP;
END;
/
exec evens
--
--Output
--
Procedure created.
The even numbers between one and 20 are:
2
4
6
8
10
12
14
16
18
20
PL/SQL procedure successfully completed.
6. Create a table called yourlastnameemp. The table should have
five columns – firstname (varchar2), lastname (varchar2), hours (number), rate
(number(6,2), tax (number).
a.
Write a procedure to insert
data into the table, where the data is inserted when the procedure is executed.
Insert five records into the table.
b.
Write a procedure to calculate
the salary for each record in the table where salary is the hours times the
rate minus the tax.
c.
Write a procedure to calculate
the total hours, taxes and salary for all the records in the table.
--
--Create a table called yourlastnameemp. The table should have five columns
--firstname (varchar2), lastname (varchar2), hours (number), rate (number(6,2),
--tax (number).
--a. Write a procedure to insert data into the table, where the data is
-- inserted when the procedure is executed. Insert five records into the table.
--b. Write a procedure to calculate the salary for each record in the table
-- where salary is the hours times the rate minus the tax.
--c. Write a procedure to calculate the total hours, taxes and salary for all
-- the records in the table.
--
spool a:\h4q3
set serveroutput on
--
--create table
--
DROP TABLE riddioughemp;
CREATE TABLE riddioughemp (firstname VARCHAR2(15), lastname VARCHAR2(20),
hours NUMBER, rate NUMBER(6,2), tax NUMBER);
--
--procedure to enter data
--
CREATE OR REPLACE PROCEDURE enter (fn VARCHAR2, ln VARCHAR2,
hrs NUMBER, rt NUMBER, tx NUMBER) AS
BEGIN
INSERT INTO riddioughemp VALUES(fn,ln,hrs,rt,tx);
END;
/
--
--insert five rows into the table
--
exec enter('Karen','Huston',35,32.35,95)
exec enter('Jack','Kristy',38,37.35,97)
exec enter('Margaret','Burbidge',42,41.65,103)
exec enter('Stephen','Hawking',40,39.35,101)
exec enter('Subramanyan','Chandrasekhar',45,42.95,105)
--
--create procedure to calculate salary for each record
--
CREATE OR REPLACE PROCEDURE calc AS
vsalary NUMBER;
vemp riddioughemp%ROWTYPE;
CURSOR cremp IS SELECT * FROM riddioughemp ORDER BY hours;
BEGIN
DBMS_OUTPUT.PUT_LINE('Employee Hours Rate Tax Salary');
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------------------------');
OPEN cremp;
FETCH cremp INTO vemp;
WHILE cremp%FOUND LOOP
vsalary := vemp.hours * vemp.rate - vemp.tax;
DBMS_OUTPUT.PUT_LINE(RPAD(vemp.firstname||' '||vemp.lastname,30)||LPAD(vemp.hours,5)
||LPAD(vemp.rate,11)||LPAD(vemp.tax,8)||' $'||LPAD(ROUND(vsalary,2),6));
FETCH cremp INTO vemp;
END LOOP;
END;
/
--
--execute the procedure
--
exec calc
--
--create procedure to procedure summary
--
CREATE OR REPLACE PROCEDURE analysis AS
vSumHr NUMBER;
vSumTx NUMBER;
vSumSl NUMBER;
BEGIN
SELECT SUM(hours), SUM(tax), SUM(hours * rate - tax) INTO vSumHr, vSumTx, vSumSl from riddioughemp;
DBMS_OUTPUT.PUT_LINE('The total hours for all employees is '||vSumHr);
DBMS_OUTPUT.PUT_LINE('The total taxes for all employees is '||vSumTx);
DBMS_OUTPUT.PUT_LINE('The total salary for all employees is $'||ROUND(vSumSl,2));
END;
/
--
--execute the procedure
--
exec analysis
--
--Output
--
Table dropped.
Table created.
Procedure created.
PL/SQL procedure successfully completed.
Procedure created.
Employee Hours Rate Tax Salary
--------------------------------------------------------------------
Karen Huston 35 32.35 95 $1037.2
Jack Kristy 38 37.35 97 $1322.3
Stephen Hawking 40 39.35 101 $ 1473
Margaret Burbidge 42 41.65 103 $1646.3
Subramanyan Chandrasekhar 45 42.95 105 $1827.7
PL/SQL procedure successfully completed.
Procedure created.
The total hours for all employees is 200
The total taxes for all employees is 501
The total salary for all employees is $7306.6
PL/SQL procedure successfully completed.
SQL> spool off
7. Create a student table called yourlastnamestudent. The table should have five columns – firstname
(varchar2), lastname (varchar2), score1 (number), score2 (number), score3
(number).
a.
Insert ten records into the
table.
b.
Write a procedure to display
all the information on each student and the average score for each student.
c.
Write a procedure to display
the highest average of the averages and to display the names of the student(s)
with the highest average.
d.
Update the scores by adding
ten to each and then update the scores by setting any that are over 100 to 100.
e.
Display all the information
with the average and display all the students with the highest average.
--
--Create a student table called yourlastnamestudent. The table should have five
--columns - firstname (varchar2), lastname (varchar2), score1 (number),
--score2 (number), score3 (number).
--a. Insert ten records into the table.
--b. Write a procedure to display all the information on each student and the
-- average score for each student.
--c. Write a procedure to display the highest average of the averages and to
-- display the names of the student(s) with the highest average.
--d. Update the scores by adding ten to each and then update the scores by
-- setting any that are over 100 to 100.
--e. Display all the information with the average and display all the students
-- with the highest average.
--
--(Below is an example of how this could be placed in a package.)
--
spool a:\h4q4
setserveroutput on
--
--create table
--
DROP TABLE crrstudent;
CREATE TABLE crrstudent (firstname VARCHAR2(15), lastname VARCHAR2(20),
score1 NUMBER, score2 NUMBER, score3 NUMBER);
--
--procedure to enter data
--
CREATE OR REPLACE PROCEDURE senter (fn VARCHAR2, ln VARCHAR2,
s1 NUMBER, s2 NUMBER, s3 NUMBER) AS
BEGIN
INSERT INTO crrstudent VALUES(fn,ln,s1,s2,s3);
END;
/
--
--insert ten rows into the table
--
exec senter('Karen','Huston',85,87,95)
exec senter('Jack','Kristy',83,81,97)
exec senter('Margaret','Burbidge',100,95,94)
exec senter('Stephen','Hawking',98,97,95)
exec senter('Subramanyan','Chandrasekhar',98,100,95)
exec senter('Anwar','Sadat',87,76,95)
exec senter('Michael','Riddiough',75,86,81)
exec senter('Aaron','Sayad',88,85,87)
exec senter('Amelia','Detweiler',98,99,99)
exec senter('Connie','Weisbroth',98,80,85)
--
--create procedure to calculate scores for each record
--
CREATE OR REPLACE PROCEDURE stinfo AS
vAve NUMBER;
vstudent crrstudent%ROWTYPE;
CURSOR cstud IS SELECT * FROM crrstudent ORDER BY lastname;
BEGIN
DBMS_OUTPUT.PUT_LINE('Name Score1 Score2 Score3 Average');
DBMS_OUTPUT.PUT_LINE('---------------------------------------------------------------');
OPEN cstud;
FETCH cstud INTO vstudent;
WHILE cstud%FOUND LOOP
vAve := (vstudent.score1 + vstudent.score2 + vstudent.score3) / 3;
DBMS_OUTPUT.PUT_LINE(RPAD(vstudent.firstname||' '||
vstudent.lastname,30)||LPAD(vstudent.score1,5)||LPAD(vstudent.score2,8)
||LPAD(vstudent.score3,8)||LPAD(ROUND(vAve,0),8));
FETCH cstud INTO vstudent;
END LOOP;
END;
/
--
--execute the procedure
--
exec stinfo
--
--create procedure to calculate the highest average
--and the students with the highest average
--
CREATE OR REPLACE PROCEDURE stave AS
TYPE tAve IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
vAve tAve;
counter NUMBER := 1;
vstudent crrstudent%ROWTYPE;
CURSOR cst IS SELECT * FROM crrstudent
WHERE ((score1 + score2 + score3) / 3) =
(SELECT MAX((score1 + score2 + score3) / 3) FROM crrstudent)
ORDER BY lastname;
BEGIN
SELECT MAX((score1 + score2 + score3) / 3) INTO vAve(0) FROM crrstudent;
vAve(0) := ROUND(vAve(0),0);
DBMS_OUTPUT.PUT_LINE('The highest average is '||vAve(0)||
' and the students with the highest average are listed below.');
DBMS_OUTPUT.PUT_LINE('Name Score1 Score2 Score3');
DBMS_OUTPUT.PUT_LINE('-----------------------------------------------------');
OPEN cst;
FETCH cst INTO vstudent;
WHILE cst%FOUND LOOP
vAve(counter) := (vstudent.score1 + vstudent.score2 + vstudent.score3) / 3;
DBMS_OUTPUT.PUT_LINE(RPAD(vstudent.firstname||' '||
vstudent.lastname,30)||LPAD(vstudent.score1,5)
||LPAD(vstudent.score2,8)||LPAD(vstudent.score3,8));
counter := counter + 1;
FETCH cst INTO vstudent;
END LOOP;
END;
/
--
--execute the procedure
--
exec stave
--
--
--create a procedure to update the table
--
CREATE OR REPLACE PROCEDURE stupdate AS
BEGIN
UPDATE crrstudent SET score1 = score1 + 10;
UPDATE crrstudent SET score2 = score2 + 10;
UPDATE crrstudent SET score3 = score3 + 10;
UPDATE crrstudent SET score1 = 100 WHERE score1 > 100;
UPDATE crrstudent SET score2 = 100 WHERE score2 > 100;
UPDATE crrstudent SET score3 = 100 WHERE score3 > 100;
END;
/
--execute the update procedure and then execute the average procedure again
--
exec stupdate
exec stave
--
--Output
--
Table dropped.
Table created.
Procedure created.
PL/SQL procedure successfully completed.
Procedure created.
Name Score1 Score2 Score3 Average
---------------------------------------------------------------
Margaret Burbidge 100 95 94 96
Subramanyan Chandrasekhar 98 100 95 98
Amelia Detweiler 98 99 99 99
Stephen Hawking 98 97 95 97
Karen Huston 85 87 95 89
Jack Kristy 83 81 97 87
Michael Riddiough 75 86 81 81
Anwar Sadat 87 76 95 86
Aaron Sayad 88 85 87 87
Connie Weisbroth 98 80 85 88
PL/SQL procedure successfully completed.
Procedure created.
The highest average is 99 and the students with the highest average are listed below.
Name Score1 Score2 Score3
-----------------------------------------------------
Amelia Detweiler 98 99 99
PL/SQL procedure successfully completed.
Procedure created.
PL/SQL procedure successfully completed.
The highest average is 100 and the students with the highest average are listed below.
Name Score1 Score2 Score3
-----------------------------------------------------
Margaret Burbidge 100 100 100
Subramanyan Chandrasekhar 100 100 100
Amelia Detweiler 100 100 100
Stephen Hawking 100 100 100
PL/SQL procedure successfully completed.
SQL> spool off
--
--A program to create the student table
--
spool a:\proj5res
set serveroutput on
DROP TABLE crrstudent;
CREATE TABLE crrstudent(Last_Name VARCHAR(10), First_Name VARCHAR(10),
Score1 NUMBER, Score2 NUMBER, Score3 NUMBER);
--
--Write a package that will allow you to update and report on student scores.
--The package will include a procedure to add data to the student table,
--a procedure to display the scores and the average score for each person,
--a procedure to calculate the highest average and display who has it and
--a procedure to update the scores.
--
--Each of the procedures below could be developed as individual procedures
--rather than as part of the package.
--
--First we write the header to define the components of the package.
--
CREATE OR REPLACE PACKAGE crrpackage AS
PROCEDURE studentins (lastname VARCHAR2, firstname VARCHAR2, score1 NUMBER,
score2 NUMBER, score3 NUMBER);
PROCEDURE studentave;
PROCEDURE studentgrp;
PROCEDURE studentup;
PROCEDURE studentrep (rept CHAR);
END;
/
--
--Next we write the body of the package which includes the specific procedures.
--
CREATE OR REPLACE PACKAGE BODY crrpackage AS
TYPE tStudent IS TABLE OF VARCHAR2(30)
INDEX BY BINARY_INTEGER;
vStudent crrstudent%ROWTYPE;
vAverage NUMBER;
vMiMax VARCHAR2(8);
vMaxScore NUMBER;
vMinScore NUMBER;
vStuHiLo tStudent;
vCounter NUMBER := 1;
CURSOR cStudent IS SELECT * FROM crrstudent ORDER BY Last_Name;
--
--The first procedure inserts new data into the table.
--
PROCEDURE studentins (lastname VARCHAR2, firstname VARCHAR2, score1 NUMBER,
score2 NUMBER, score3 NUMBER) IS
BEGIN
INSERT INTO crrstudent VALUES(lastname, firstname, score1, score2, score3);
END studentins;
--
--The second procedure calculates the average score for each student.
--
PROCEDURE studentave IS
BEGIN
OPEN cStudent;
FETCH cStudent INTO vStudent;
studentrep('a');
WHILE cStudent%FOUND LOOP
vAverage := (vStudent.score1 + vStudent.score2 + vStudent.score3) / 3;
studentrep('b');
FETCH cStudent INTO vStudent;
END LOOP;
CLOSE cStudent;
END studentave;
--
--The third procedure calculates the highest and
--lowest of the student averages.
--
PROCEDURE studentgrp IS
BEGIN
vCounter := 1;
OPEN cStudent;
FETCH cStudent INTO vStudent;
SELECT MAX((Score1+Score2+Score3)/3) INTO vMaxScore FROM crrstudent;
vMiMax := 'highest';
WHILE cStudent%FOUND LOOP
vAverage := (vStudent.score1 + vStudent.score2 + vStudent.score3) / 3;
IF vAverage = vMaxScore THEN
vStuHiLo(vCounter) := vStudent.First_Name||' '||vStudent.Last_Name;
studentrep('c');
vCounter := vCounter + 1;
END IF;
FETCH cStudent INTO vStudent;
END LOOP;
CLOSE cStudent;
vCounter := 1;
OPEN cStudent;
FETCH cStudent INTO vStudent;
SELECT MIN((Score1+Score2+Score3)/3) INTO vMinScore FROM crrstudent;
vMiMax := 'lowest';
WHILE cStudent%FOUND LOOP
vAverage := (vStudent.score1 + vStudent.score2 + vStudent.score3) / 3;
IF vAverage = vMinScore THEN
vStuHiLo(vCounter) := vStudent.First_Name||' '||vStudent.Last_Name;
studentrep('c');
vCounter := vCounter + 1;
END IF;
FETCH cStudent INTO vStudent;
END LOOP;
CLOSE cStudent;
END studentgrp;
--
--The fourth procedure updates the student grades.
--
PROCEDURE studentup IS
BEGIN
DBMS_OUTPUT.ENABLE(10000);
UPDATE crrstudent SET score1 = score1+10,
score2 = score2+10,
score3 = score3 + 10;
UPDATE crrstudent SET score1 = 100 WHERE score1 > 100;
UPDATE crrstudent SET score2 = 100 WHERE score2 > 100;
UPDATE crrstudent SET score3 = 100 WHERE score3 > 100;
END studentup;
--
--The last procedure does reports for previous procedures.
--
PROCEDURE studentrep (rept CHAR) IS
BEGIN
DBMS_OUTPUT.ENABLE(10000);
IF rept = 'a' THEN
DBMS_OUTPUT.PUT_LINE('Name Score 1 Score 2 Score 3 Average');
DBMS_OUTPUT.PUT_LINE('---- ------- ------- ------- -------');
ELSIF rept = 'b' THEN
DBMS_OUTPUT.PUT_LINE(RPAD((vStudent.First_name||' '||vStudent.Last_name),22)||
LPAD(vStudent.score1,5)||LPAD(vStudent.score2,12)||LPAD(vStudent.score3,12)||
LPAD(ROUND(vAverage,2),12));
ELSE
DBMS_OUTPUT.PUT_LINE('The'||vMiMax||' average is '||ROUND(vAverage,2)||
' and a student with that score is '||vStuHiLo(vCounter));
END IF;
END studentrep;
END crrpackage;
/
--
--Execute the segments of the package.
--Insert students into the table.
--
exec crrpackage.studentins('Riddiough','Chris',97,95,98)
exec crrpackage.studentins('Smith','Alex',87,52,65)
--Add additional students ...
--
--Calculate the averages and print the reports.
--
exec crrpackage.studentave
exec crrpackage.studentgrp
--
--Update the table, recalculate the averages and print the reports.
--
exec crrpackage.studentup
exec crrpackage.studentave
exec crrpackage.studentgrp
--
--Output of crrpackage execution.
Table dropped.
Table created.
SQL> @ a:\class.txt
Package created.
Package body created.
SQL> @ a:\clex.txt
PL/SQL procedure successfully completed.
Name Score 1 Score 2 Score 3 Average
---- ------- ------- ------- -------
Amelia Detweiler 86 90 53 76.33
BJ Detweiler 75 74 98 82.33
Jane Jonre 76 80 85 80.33
John Nedrow 90 84 97 90.33
Boots Nedrow 89 85 90 88
Elvis Nedrow 55 89 78 74
Judith Nedrow 99 64 77 80
Chris Riddiough 97 95 98 96.67
Kibbe Riddiough 94 90 86 90
Michael Riddiough 98 92 95 95
Tabouli Riddiough 91 95 93 93
Ruth Riddiough 98 64 86 82.67
Aaron Sayad 93 83 85 87
Judith Sayad 96 93 97 95.33
Miriam Sayad 98 89 97 94.67
Alex Smith 87 52 65 68
Jon Smith 84 86 80 83.33
Connie Weisbroth 90 84 91 88.33
Jeff Weisbroth 99 94 92 95
Ben Weisbroth 99 98 86 94.33
PL/SQL procedure successfully completed.
The highest average is 96.67 and a student with that score is Chris Riddiough
The lowest average is 68 and a student with that score is Alex Smith
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Name Score 1 Score 2 Score 3 Average
---- ------- ------- ------- -------
Amelia Detweiler 96 100 63 86.33
BJ Detweiler 85 84 100 89.67
Jane Jonre 86 90 95 90.33
John Nedrow 100 94 100 98
Boots Nedrow 99 95 100 98
Elvis Nedrow 65 99 88 84
Judith Nedrow 100 74 87 87
Chris Riddiough 100 100 100 100
Kibbe Riddiough 100 100 96 98.67
Michael Riddiough 100 100 100 100
Tabouli Riddiough 100 100 100 100
Ruth Riddiough 100 74 96 90
Aaron Sayad 100 93 95 96
Judith Sayad 100 100 100 100
Miriam Sayad 100 99 100 99.67
Alex Smith 97 62 75 78
Jon Smith 94 96 90 93.33
Connie Weisbroth 100 94 100 98
Jeff Weisbroth 100 100 100 100
Ben Weisbroth 100 100 96 98.67
PL/SQL procedure successfully completed.
The highest average is 100 and a student with that score is Chris Riddiough
The highest average is 100 and a student with that score is Michael Riddiough
The highest average is 100 and a student with that score is Tabouli Riddiough
The highest average is 100 and a student with that score is Judith Sayad
The highest average is 100 and a student with that score is Jeff Weisbroth
The lowest average is 78 and a student with that score is Alex Smith
PL/SQL procedure successfully completed.
SQL> spool off
8. Create the world tables from the student folder and
insert the data.
a.
Write a procedure to find and
output the names of the countries that have higher than average internet usage.
b. Run and execute the procedures and show the output.
--
-- Create the world tables from the student folder and insert the data.
-- a. Write a procedure to find and output the names of the countries that
-- have higher than average internet usage.
-- b. Run and execute the procedures and show the output.
--
spool a:\h4q5
set serveroutput on
--
CREATE OR REPLACE PROCEDURE intuse AS
vAve NUMBER;
vCountry world_pop.country%TYPE;
vInternet world_el.internet_users%TYPE;
CURSOR cWorld IS SELECT p.country, e.internet_users
FROM world_pop p, world_el e WHERE p.hdi_rank = e.hdi_rank
AND e.internet_users > (SELECT AVG(internet_users) FROM world_el)
ORDER BY e.internet_users;
BEGIN
SELECT AVG(internet_users) INTO vAve FROM world_el;
DBMS_OUTPUT.PUT_LINE('Average World Internet Usage is '||ROUND(vAve,2)||' per 1000 people.');
DBMS_OUTPUT.PUT_LINE('Country Internet Users');
DBMS_OUTPUT.PUT_LINE('----------------------------------------------');
OPEN cWorld;
FETCH cWorld INTO vCountry, vInternet;
WHILE cWorld%FOUND LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(vCountry,20)||LPAD(ROUND(vInternet,0),8));
FETCH cWorld INTO vCountry, vInternet;
END LOOP;
END;
/
--
--execute the procedure
--
exec intuse
--
--
--Output
--
Procedure created.
Average World Internet Usage is 6.62 per 1000 people.
Country Internet Users
----------------------------------------
Chile 7
Japan 7
Greece 8
France 9
Portugal 9
Bahamas 10
Belgium 10
Hungary 11
South Africa 11
Ireland 11
Luxembourg 16
Germany 18
Austria 19
Czech Republic 21
Antigua and Barbuda 23
United Kingdom 26
Estonia 27
Slovenia 29
Singapore 30
Switzerland 36
USA 38
Denmark 38
Netherlands 39
Canada 41
Hong Kong, China 49
New Zealand 50
Sweden 51
Israel 54
Australia 55
Norway 64
Iceland 112
Finland 139
PL/SQL procedure successfully completed.
SQL> spool off
9. Develop
a procedure that will generate customer reports for Eddie’s Equipment Rental.
Eddie’s rents out the following equipment for either a full-day or a half-day:
|
Eqipment ID |
Equipment |
Half-Day Price |
Full-Day Price |
|
1 |
Rug cleaner |
$16 |
$24 |
|
2 |
Lawn mower |
$12 |
$18 |
|
3 |
Paint sprayer |
$20 |
$30 |
The customer bill will also include a $30 deposit. The program should display a receipt indicating that it is from Eddie’s, the equipment, time of rental , price, deposit and total amount tendered. It should also insert into a table the date and time of the rental.
--Version 1 - Package
--
--Develop a program to generate a customer bill for Eddie's Equipment Rental.
--Program should request customer select a piece of equipment and indicate
--amount of time for rental. Customer bill includes a $30 deposit. Program
--should include the option for entering more than one request.
--
--Create three tables - one for equipment, one for customers and one for orders.
--Create two sequences for the customer and order tables.
--Enter data into the equipment table. Format SQL report on
--equipment and display.
--
spool a:\eddieout
set serveroutput on
DROP TABLE crreddie;
CREATE TABLE crreddie (itemno NUMBER, equip VARCHAR2(15), half_day_cost NUMBER(5,2),
full_day_cost NUMBER(5,2));
DROP SEQUENCE credcust;
CREATE SEQUENCE credcust START WITH 1000;
DROP TABLE crcustomer;
CREATE TABLE crcustomer (custid NUMBER, cust_fname VARCHAR2(10), cust_lname
VARCHAR2(15));
DROP SEQUENCE credorder;
CREATE SEQUENCE credorder START WITH 10000;
DROP TABLE credlog;
CREATE TABLE credlog (orderid NUMBER, custid NUMBER, itemno NUMBER, charge
NUMBER(5,2), orderdate DATE);
INSERT INTO crreddie VALUES (1,'Rug cleaner',16.00,24.00);
INSERT INTO crreddie VALUES (2,'Lawn mower',12.00,18.00);
INSERT INTO crreddie VALUES (3,'Paint sprayer',20.00,30.00);
SET PAGESIZE 15
TTITLE 'Eddie''s Equipment Rental' LEFT
COLUMN itemno HEADING ' |Item|Number'
COLUMN equip HEADING 'Equipment'
COLUMN half_day_cost Heading 'Rental Cost for|Half Day' format $999.99
COLUMN full_day_cost Heading 'Rental Cost for|Full Day' format $999.99
BTITLE '$30 Deposit Required' LEFT
SELECT * FROM crreddie;
TTITLE '---'
BTITLE '---'
--
--
--Part 2: Develop a program to generate a customer bill for
--Eddie's Equipment Rental.
--Program should request customer select a piece of equipment and indicate
--amount of time for rental. Customer bill includes a $30 deposit. Program
--should include the option for entering more than one request.
--
CREATE OR REPLACE PACKAGE crredp AS
PROCEDURE preddie;
PROCEDURE calceddie(pItemNo NUMBER, pTime CHAR);
PROCEDURE inseddie(pItemno NUMBER, pTime CHAR, pFN VARCHAR2 DEFAULT NULL, pLN VARCHAR2 DEFAULT NULL);
PROCEDURE main(pItemno NUMBER, pTime CHAR, pFN VARCHAR2 DEFAULT NULL, pLN VARCHAR2 DEFAULT NULL);
END;
/
--
CREATE OR REPLACE PACKAGE BODY crredp AS
vEddie crreddie%ROWTYPE;
vCost NUMBER;
vDeposit NUMBER := 30;
--
PROCEDURE preddie IS
BEGIN
DBMS_OUTPUT.ENABLE(10000);
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE('** Welcome to Eddie''s Equipment Rental ***** Welcome to Eddie''s Equipment Rental **');
DBMS_OUTPUT.PUT_LINE('** **');
DBMS_OUTPUT.PUT_LINE('** EEEEEEEEEE DDDDDD DDDDDD IIIIII EEEEEEEEEE ||| SSSSSSS **');
DBMS_OUTPUT.PUT_LINE('** EE DD DD DD DD II EE ||| SSSSSSSS **');
DBMS_OUTPUT.PUT_LINE('** EE DD DD DD DD II EE || SSS **');
DBMS_OUTPUT.PUT_LINE('** EE DD DD DD DD II EE || SSS **');
DBMS_OUTPUT.PUT_LINE('** EEEEEE DD DD DD DD II EEEEEE SSSSSS **');
DBMS_OUTPUT.PUT_LINE('** EEEEEE DD DD DD DD II EEEEEE SSSSSS **');
DBMS_OUTPUT.PUT_LINE('** EE DD DD DD DD II EE SSS **');
DBMS_OUTPUT.PUT_LINE('** EE DD DD DD DD II EE SSS **');
DBMS_OUTPUT.PUT_LINE('** EE DD DD DD DD II EE SSSSSSSS **');
DBMS_OUTPUT.PUT_LINE('** EEEEEEEEEE DDDDDD DDDDDD IIIIII EEEEEEEEEE SSSSSSS **');
DBMS_OUTPUT.PUT_LINE('** **');
DBMS_OUTPUT.PUT_LINE('** Welcome to Eddie''s Equipment Rental ***** Welcome to Eddie''s Equipment Rental **');
DBMS_OUTPUT.PUT_LINE('** **');
DBMS_OUTPUT.PUT_LINE('** **');
DBMS_OUTPUT.PUT_LINE('*************************************************************************************');
DBMS_OUTPUT.PUT_LINE('** **');
DBMS_OUTPUT.PUT_LINE('** Please select an item and a rental time. **');
DBMS_OUTPUT.PUT_LINE('** Choose 1 for Rug cleaner, 2 for Lawn mower or 3 for Paint sprayer. **');
DBMS_OUTPUT.PUT_LINE('** Then choose ''H'' for half-day rental or ''F'' for full-day rental. **');
DBMS_OUTPUT.PUT_LINE('** Enter the customer''s first and last names if available. **');
DBMS_OUTPUT.PUT_LINE('** Please use the following format: **');
DBMS_OUTPUT.PUT_LINE('** exec crredp.main(Item, ''Rental Time'', ''First Name'', ''Last Name'') **');
DBMS_OUTPUT.PUT_LINE('** Be sure to include the single quotes around rental time and name. **');
DBMS_OUTPUT.PUT_LINE('** **');
DBMS_OUTPUT.PUT_LINE('*************************************************************************************');
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.NEW_LINE;
END preddie;
--
PROCEDURE calceddie(pItemNo NUMBER, pTime CHAR) IS
eError EXCEPTION;
vDura VARCHAR2(20);
BEGIN
SELECT * INTO vEddie FROM crreddie
WHERE itemno = pItemNo;
IF UPPER(pTime) = 'F' THEN
vCost := vEddie.full_day_cost;
vDura := 'Full Day Rental';
ELSIF UPPER(pTime) = 'H' THEN
vCost := vEddie.half_day_cost;
vDura := 'Half Day Rental';
ELSE
RAISE eError;
END IF;
DBMS_OUTPUT.PUT_LINE('***********************************************************************************');
DBMS_OUTPUT.PUT_LINE('** **');
DBMS_OUTPUT.PUT_LINE('** Receipt from Eddie''s Equipment **');
DBMS_OUTPUT.PUT_LINE('** **');
DBMS_OUTPUT.PUT_LINE('** Deposit $'||RPAD(vDeposit,6)||' **');
DBMS_OUTPUT.PUT_LINE('** '||RPAD(vEddie.equip,20)||' $'||RPAD(vCost,6)||' ('||vDura||') **');
DBMS_OUTPUT.PUT_LINE('** ---------------------------------------------- **');
DBMS_OUTPUT.PUT_LINE('** Total $'||RPAD((vDeposit + vCost),6)||' **');
DBMS_OUTPUT.PUT_LINE('** **');
DBMS_OUTPUT.PUT_LINE('** Thank you for your patronage. **');
DBMS_OUTPUT.PUT_LINE('** **');
DBMS_OUTPUT.PUT_LINE('***********************************************************************************');
DBMS_OUTPUT.NEW_LINE;
preddie;
EXCEPTION
WHEN eError THEN
DBMS_OUTPUT.PUT_LINE('***********************************************************************************');
DBMS_OUTPUT.PUT_LINE('** **');
DBMS_OUTPUT.PUT_LINE('** You have entered an incorrect code. **');
DBMS_OUTPUT.PUT_LINE('** Please select an item and a rental time. **');
DBMS_OUTPUT.PUT_LINE('** Choose 1 for Rug cleaner, 2 for Lawn mower or 3 for Paint sprayer. **');
DBMS_OUTPUT.PUT_LINE('** Then choose H for half-day rental or F for full-day rental. **');
DBMS_OUTPUT.PUT_LINE('** **');
DBMS_OUTPUT.PUT_LINE('***********************************************************************************');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('***********************************************************************************');
DBMS_OUTPUT.PUT_LINE('** **');
DBMS_OUTPUT.PUT_LINE('** You have entered an incorrect code. **');
DBMS_OUTPUT.PUT_LINE('** Please select an item and a rental time. **');
DBMS_OUTPUT.PUT_LINE('** Choose 1 for Rug cleaner, 2 for Lawn mower or 3 for Paint sprayer. **');
DBMS_OUTPUT.PUT_LINE('** Then choose H for half-day rental or F for full-day rental. **');
DBMS_OUTPUT.PUT_LINE('** **');
DBMS_OUTPUT.PUT_LINE('***********************************************************************************');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('You have another error.');
END calceddie;
--
PROCEDURE inseddie(pItemno NUMBER, pTime CHAR, pFN VARCHAR2 DEFAULT NULL, pLN VARCHAR2 DEFAULT NULL) IS
vCustID NUMBER;
BEGIN
IF (pItemno = 1 OR pItemno = 2 OR pItemno = 3) AND (UPPER(pTime) = 'H' OR UPPER(pTime) = 'F') THEN
INSERT INTO crcustomer VALUES(credcust.nextval,pFN,pLN);
INSERT INTO credlog VALUES(credorder.nextval,credcust.currval,pItemno,(vCost + vDeposit),sysdate);
END IF;
END inseddie;
--
PROCEDURE main(pItemno NUMBER, pTime CHAR, pFN VARCHAR2 DEFAULT NULL, pLN VARCHAR2 DEFAULT NULL) IS
BEGIN
crredp.calceddie(pItemno,pTime);
crredp.inseddie(pItemno,pTime,pFN,pLN);
END main;
END;
/
--
--
--execute the eddie procedures
--
exec crredp.preddie
exec crredp.main(1, 'F','Chris','Riddiough')
exec crredp.main(2, 'h','Aaron','Sayad')
exec crredp.main(2, 'f','Michael','Riddiough')
exec crredp.main(3, 'F','Judith','Nedrow')
exec crredp.main(4, 'H')
exec crredp.main(1, 'U')
SET PAGESIZE 15
TTITLE 'Eddie''s Equipment Rental' LEFT
COLUMN itemno HEADING ' |Item|Number'
COLUMN equip HEADING 'Equipment'
COLUMN half_day_cost Heading 'Rental Cost for|Half Day' format $999.99
COLUMN full_day_cost Heading 'Rental Cost for|Full Day' format $999.99
BTITLE '$30 Deposit Required' LEFT
SELECT * FROM crreddie;
TTITLE 'Eddie''s Equipment Rental - Customer List' LEFT
COLUMN itemno HEADING ' |Item|Number'
COLUMN equip HEADING 'Equipment'
COLUMN name HEADING 'Customer Name'
COLUMN custid HEADING 'Customer ID'
BTITLE '*****'
SELECT custid, cust_fname||' '||cust_lname name FROM crcustomer;
TTITLE 'Eddie''s Equipment Rental - Order List' LEFT
COLUMN itemno HEADING ' |Item|Number'
COLUMN equip HEADING 'Equipment'
COLUMN charge HEADING 'Income' format $999.99
COLUMN orderdate HEADING 'Order Date' format a10
COLUMN orderid HEADING 'Order ID'
BTITLE '-----'
SELECT * from credlog;
SELECT l.orderid,l.charge,l.orderdate,c.custid,c.cust_fname||' '||c.cust_lname name,e.equip
FROM credlog l, crcustomer c, crreddie e
WHERE e.itemno = l.itemno AND c.custid = l.custid;
--
--Output Version 1 - Package
Table dropped.
Table created.
Sequence dropped.
Sequence created.
Table dropped.
Table created.
Sequence dropped.
Sequence created.
Table dropped.
Table created.
1 row created.
1 row created.
1 row created.
Eddie's Equipment Rental
Item Rental Cost for Rental Cost for
Number Equipment Half Day Full Day
--------- --------------- --------------- ---------------
1 Rug cleaner $16.00 $24.00
2 Lawn mower $12.00 $18.00
3 Paint sprayer $20.00 $30.00
$30 Deposit Required
SQL> @ a:\eddie2.txt
Package created.
Package body created.
SQL> @ a:\eddieex.txt
**Welcome to Eddie's Equipment Rental ***** Welcome to Eddie's Equipment Rental**
** **
** EEEEEEEEEE DDDDDD DDDDDD IIIIII EEEEEEEEEE ||| SSSSSSS **
** EE DD DD DD DD II EE ||| SSSSSSSS **
** EE DD DD DD DD II EE || SSS **
** EE DD DD DD DD II EE || SSS **
** EEEEEE DD DD DD DD II EEEEEE SSSSSS **
** EEEEEE DD DD DD DD II EEEEEE SSSSSS **
** EE DD DD DD DD II EE SSS **
** EE DD DD DD DD II EE SSS **
** EE DD DD DD DD II EE SSSSSSSS **
** EEEEEEEEEE DDDDDD DDDDDD IIIIII EEEEEEEEEE SSSSSSS **
** **
**Welcome to Eddie's Equipment Rental ***** Welcome to Eddie's Equipment Rental**
** **
** **
*********************************************************************************
** **
** Please select an item and a rental time. **
** Choose 1 for Rug cleaner, 2 for Lawn mower or 3 for Paint sprayer. **
** Then choose 'H' for half-day rental or 'F' for full-day rental. **
** Enter the customer's first and last names if available. **
** Please use the following format: **
** exec crredp.main(Item, 'Rental Time', 'First Name', 'Last Name') **
** Be sure to include the single quotes around rental time and name. **
** **
*********************************************************************************
PL/SQL procedure successfully completed.
*********************************************************************************
** **
** Receipt from Eddie's Equipment **
** **
** Deposit $30 **
** Rug cleaner $24 (Full Day Rental) **
** ---------------------------------------------- **
** Total $54 **
** **
** Thank you for your patronage. **
** **
*********************************************************************************
Eddie's Equipment Rental - Customer List
Customer ID Customer Name
-------------------------------------
1000 Chris Riddiough
1001 Aaron Sayad
1002 Michael Riddiough
1003 Judith Nedrow
*****
Eddie's Equipment Rental - Order List
Item
Order ID Customer ID Number Income Order Date
-------------------- --------- -------- ----------
10000 1000 1 $54.00 28-JUN-99
10001 1001 2 $42.00 28-JUN-99
10002 1002 2 $48.00 28-JUN-99
10003 1003 3 $60.00 28-JUN-99
-----
Eddie's Equipment Rental - Order List
Order ID Income Order Date Customer ID Customer Name Equipment
----------------- ---------- ----------- -------------------------- ---------------
10000 $54.00 28-JUN-99 1000 Chris Riddiough Rug cleaner
10001 $42.00 28-JUN-99 1001 Aaron Sayad Lawn mower
10002 $48.00 28-JUN-99 1002 Michael Riddiough Lawn mower
10003 $60.00 28-JUN-99 1003 Judith Nedrow Paint sprayer
-----
SQL> spool off
--
--Version 2 - Buffer/Procedure
--
--Develop a program to generate a customer bill for Eddie's Equipment Rental.
--Program should request customer select a piece of equipment and indicate
--amount of time for rental. Customer bill includes a $30 deposit. Program
--should include the option for entering more than one request.
--
spool a:\eddieres
set serveroutput on
CREATE OR REPLACE PROCEDURE calcrr(vItemNo NUMBER, vTime CHAR) AS
vItem crreddie.equip%TYPE;
BEGIN
IF UPPER(vTime) = 'H' THEN
SELECT equip, half_day_cost INTO vItem, vCost
FROM crreddie
WHERE itemno = vItemno;
DBMS_OUTPUT.PUT_LINE('** '||RPAD(vItem,12)||' $'||vCost||' (Half Day Rental) **');
DBMS_OUTPUT.PUT_LINE('** ------------------------------- **');
ELSIF UPPER(vTime) = 'F' THEN
SELECT equip, full_day_cost INTO vItem, vCost
FROM crreddie
WHERE itemno = vItemno;
DBMS_OUTPUT.PUT_LINE('**'||RPAD(vItem,12)||' $'||vCost||' (Full Day Rental) **');
DBMS_OUTPUT.PUT_LINE('** ------------------------------- **');
ELSE
RAISE eCode;
END IF;
vTotal := vCost + 30;
DBMS_OUTPUT.PUT_LINE('** Total $'||vTotal||' **');
DBMS_OUTPUT.PUT_LINE('** **');
DBMS_OUTPUT.PUT_LINE('** Thank you for shopping at Eddie''s **');
DBMS_OUTPUT.PUT_LINE('************************************************');
DBMS_OUTPUT.PUT_LINE('***********************************************************************************');
DBMS_OUTPUT.PUT_LINE('** **');
DBMS_OUTPUT.PUT_LINE('** If you wish to enter another customer, enter ''y'' **');
DBMS_OUTPUT.PUT_LINE('** Then select an item and a rental time. **');
DBMS_OUTPUT.PUT_LINE('** Choose 1 for Rug cleaner, 2 for Lawn mower or 3 for Paint sprayer. **');
DBMS_OUTPUT.PUT_LINE('** Then choose ''H'' for half-day rental or''F'' for full-day rental. **');
DBMS_OUTPUT.PUT_LINE('** **');
DBMS_OUTPUT.PUT_LINE('***********************************************************************************');
EXCEPTION
WHEN eCode THEN
DBMS_OUTPUT.PUT_LINE('***********************************************************************************');
DBMS_OUTPUT.PUT_LINE('** **');
DBMS_OUTPUT.PUT_LINE('** You have entered an incorrect code. **');
DBMS_OUTPUT.PUT_LINE('** Please select an item and a rental time. **');
DBMS_OUTPUT.PUT_LINE('** Choose 1 for Rug cleaner, 2 for Lawn mower or 3 for Paint sprayer. **');
DBMS_OUTPUT.PUT_LINE('** Then choose H for half-day rental or F for full-day rental. **');
DBMS_OUTPUT.PUT_LINE('** **');
DBMS_OUTPUT.PUT_LINE('***********************************************************************************');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('** You have another error. **');
END;
/
exec calcrr('&vItemNo', '&vTime')
exec calcrr('&vItemNo', '&vTime')
exec calcrr('&vItemNo', '&vTime')
exec calcrr('&vItemNo', '&vTime')
exec calcrr('&vItemNo', '&vTime')
--
--Output
- Version 2 - Procedure/Buffer
--
Procedure created.
Enter value for vitemno: 1
Enter value for vtime: h
************************************************
** **
** Receipt from Eddie's Equipment **************
** **
** Deposit $30 **
** Rug cleaner $16 (Half Day Rental) **
** ------------------------------- **
** Total $46 **
** **
** Thank you for shopping at Eddie's **
************************************************
***********************************************************************************
** **
** If you wish to enter another customer, enter 'y' **
** Then select an item and a rental time. **
** Choose 1 for Rug cleaner, 2 for Lawn mower or 3 for Paint sprayer. **
** Then choose 'H' for half-day rental or 'F' for full-day rental. **
** **
***********************************************************************************
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SQL> spool off
10.
Set up the planet and moon tables from the student
folder. Create a package that will do the following:
a.
Print out a list of planets in order of distance from the
sun along with their moons.
b.
Print out the distance they travel in their orbits, using
the formula c=2pr where c is the
circumference of the orbit and r is the radius of the orbit (distance from the
sun)
c.
Print out the distance the moons travel around their
planets using the above formula.
d.
Print out the density of the planets and their moons
based on the formula d=m/(4pr3/3)
where d is density, m is the mass of the object and r is its radius.
--
--Set up the planet and moon tables from the student folder. Create a package
-- that will do the following:
-- a. Print out a list of planets in order of distance from the sun
-- along with their moons.
-- b. Print out the distance they travel in their orbits, using the
-- formula c=2pir where c is the circumference of the orbit and r
-- is the radius of the orbit (distance from the sun)
-- c. Print out the distance the moons travel around their planets
-- using the above formula.
-- d. Print out the density of the planets and their moons based
-- on the formula d=m/(4pir3/3) where d is density, m is the mass
-- of the object and r is its radius.
--
spool a:\h5q2
set serveroutput on
--
CREATE OR REPLACE PACKAGE solar IS
PROCEDURE solorder;
PROCEDURE pcirc;
PROCEDURE mcirc;
PROCEDURE density;
END;
/
--
--create package body
--
CREATE OR REPLACE PACKAGE BODY solar IS
vPlanets planet%ROWTYPE;
vMoons moon%ROWTYPE;
CURSOR cPlanets IS SELECT * FROM planet ORDER BY distance_km;
CURSOR cMoons IS SELECT * FROM moon ORDER BY id, distance_km;
--
--create procedure solorder
--
PROCEDURE solorder IS
BEGIN
DBMS_OUTPUT.ENABLE(10000);
DBMS_OUTPUT.PUT_LINE('The planets, in order from the sun, and their moons are: ');
OPEN cPlanets;
FETCH cPlanets INTO vPlanets;
WHILE cPlanets%FOUND LOOP
DBMS_OUTPUT.PUT_LINE('-- Planet: '||vPlanets.name);
OPEN cMoons;
LOOP
FETCH cMoons INTO vMoons;
EXIT WHEN cMoons%NOTFOUND;
IF vMoons.id = vPlanets.id THEN
DBMS_OUTPUT.PUT_LINE('-- Moon: '||vMoons.name);
END IF;
END LOOP;
CLOSE cMoons;
FETCH cPlanets INTO vPlanets;
END LOOP;
close cPlanets;
END solorder;
--
--create procedure pcirc
--
PROCEDURE pcirc IS
vcircum NUMBER;
pi CONSTANT NUMBER := 3.14159;
BEGIN
DBMS_OUTPUT.PUT_LINE('The planets travel the following distances in kilometers: ');
DBMS_OUTPUT.PUT_LINE('Name Circumference of Orbit');
DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------');
OPEN cPlanets;
FETCH cPlanets INTO vPlanets;
WHILE cPlanets%FOUND LOOP
vcircum := ROUND(2 * pi * vPlanets.distance_km,2);
DBMS_OUTPUT.PUT_LINE(RPAD(vPlanets.name,15)||LPAD(vcircum,43));
FETCH cPlanets INTO vPlanets;
END LOOP;
close cPlanets;
END pcirc;
--
--create procedure mcirc
--
PROCEDUREmcirc IS
vcircum NUMBER;
pi CONSTANT NUMBER := 3.14159;
BEGIN
DBMS_OUTPUT.ENABLE(10000);
DBMS_OUTPUT.PUT_LINE('The moons travel the following distances in kilometers: ');
DBMS_OUTPUT.PUT_LINE('Name Circumference of Orbit');
DBMS_OUTPUT.PUT_LINE('----------------------------------------------------------');
OPEN cMoons;
FETCH cMoons INTO vMoons;
WHILE cMoons%FOUND LOOP
vcircum := ROUND(2 * pi * vMoons.distance_km,2);
DBMS_OUTPUT.PUT_LINE(RPAD(vMoons.name,15)||LPAD(vcircum,43));
FETCH cMoons INTO vMoons;
END LOOP;
close cMoons;
END mcirc;
--
--create procedure density
--
PROCEDURE density IS
vdensity NUMBER;
pi CONSTANT NUMBER := 3.14159;
BEGIN
DBMS_OUTPUT.ENABLE(10000);
DBMS_OUTPUT.PUT_LINE('The desnsities of the planets and their moons are: ');
OPEN cPlanets;
FETCH cPlanets INTO vPlanets;
WHILE cPlanets%FOUND LOOP
vdensity := ROUND(vplanets.mass_kg / ((4 * pi * (vplanets.radius_km ** 3))/3),2);
DBMS_OUTPUT.PUT_LINE('-- Planet: '||vPlanets.name||' Density: '||vdensity);
OPEN cMoons;
LOOP
FETCH cMoons INTO vMoons;
EXIT WHEN cMoons%NOTFOUND;
IF vMoons.id = vPlanets.id THEN
vdensity := ROUND(vmoons.mass_kg / ((4 * pi * (vmoons.radius_km ** 3))/3),2);
DBMS_OUTPUT.PUT_LINE('-- Moon: '||vMoons.name||' Density: '||vdensity);
END IF;
END LOOP;
CLOSE cMoons;
FETCH cPlanets INTO vPlanets;
END LOOP;
close cPlanets;
END density;
END solar;
/
--
--execute the procedures
--
exec solar.solorder
exec solar.pcirc
exec solar.mcirc
exec solar.density
--
--
--Output
--
Package created.
Package body created.
The planets, in order from the sun, and their moons are:
-- Planet: Mercury
-- Planet: Venus
-- Planet: Earth
-- Moon: Moon
-- Planet: Mars
-- Moon: Phobos
-- Moon: Deimos
-- Planet: Jupiter
-- Moon: Metis
-- Moon: Adrastea
-- Moon: Amalthea
-- Moon: Thebe
-- Moon: Io
-- Moon: Europa
-- Moon: Ganymede
-- Moon: Callisto
-- Moon: Leda
-- Moon: Himalia
-- Moon: Lysithea
-- Moon: Elara
-- Moon: Ananke
-- Moon: Carme
-- Moon: Pasiphae
-- Moon: Sinope
-- Planet: Saturn
-- Moon: Pan
-- Moon: Atlas
-- Moon: Prometheus
-- Moon: Pandora
-- Moon: Epimetheus
-- Moon: Janus
-- Moon: Mimas
-- Moon: Enceladus
-- Moon: Tethys
-- Moon: Telesto
-- Moon: Calypso
-- Moon: Dione
-- Moon: Helene
-- Moon: Rhea
-- Moon: Titan
-- Moon: Hyperion
-- Moon: Iapetus
-- Moon: Phoebe
-- Planet: Uranus
-- Moon: Cordelia
-- Moon: Ophelia
-- Moon: Bianca
-- Moon: Cressida
-- Moon: Desdemona
-- Moon: Juliet
-- Moon: Portia
-- Moon: Rosalind
-- Moon: Belinda
-- Moon: Puck
-- Moon: Miranda
-- Moon: Ariel
-- Moon: Umbriel
-- Moon: Titania
-- Moon: Oberon
-- Moon: Caliban
-- Moon: Sycorax
-- Planet: Neptune
-- Moon: Naiad
-- Moon: Thalassa
-- Moon: Despina
-- Moon: Galatea
-- Moon: Larissa
-- Moon: Proteus
-- Moon: Triton
-- Moon: Nereid
-- Planet: Pluto
-- Moon: Charon
PL/SQL procedure successfully completed.
The planets travel the following distances in kilometers:
Name Circumference of Orbit
----------------------------------------------------------
Mercury 363858953.8
Venus 679840076
Earth 939963728
Mars 1432188049.2
Jupiter 4890387489.4
Saturn 8981177492
Uranus 18038946948.2
Neptune 28301327674
Pluto 37155710593.6
PL/SQL procedure successfully completed.
The moons travel the following distances in kilometers:
Name Circumference of Orbit
----------------------------------------------------------
Moon 2412741.12
Phobos 56548.62
Deimos 144513.14
Charon 125663.6
Metis 804247.04
Adrastea 810530.22
Amalthea 1137255.58
Thebe 1394865.96
Io 2651501.96
Europa 4216013.78
Ganymede 6723002.6
Callisto 11831227.94
Leda 69705598.92
Himalia 72130906.4
Lysithea 73638869.6
Elara 73745683.66
Ananke 133203416
Carme 141999868
Pasiphae 147654730
Sinope 148911366
Pan 841946.12
Atlas 867078.84
Prometheus 873362.02
Pandora 892211.56
Epimetheus 948760.18
Janus 948760.18
Mimas 1168671.48
Enceladus 1495396.84
Tethys 1853538.1
Telesto 1853538.1
Calypso 1853538.1
Dione 2368758.86
Helene 2368758.86
Rhea 3311235.86
Titan 7678045.96
Hyperion 9305389.58
Iapetus 22374403.98
Phoebe 81379747.36
Cordelia 314159
Ophelia 339291.72
Bianca 370707.62
Cressida 389557.16
Desdemona 395840.34
Juliet 402123.52
Portia 414689.88
Rosalind 439822.6
Belinda 471238.5
Puck 540353.48
Miranda 816813.4
Ariel 1200087.38
Umbriel 1671325.88
Titania 2739466.48
Oberon 3663093.94
Caliban 45238896
Sycorax 76654796
Naiad 301592.64
Thalassa 314159
Despina 333008.54
Galatea 389557.16
Larissa 464955.32
Proteus 741415.24
Triton 2230528.9
Nereid 34639171.34
PL/SQL procedure successfully completed.
The desnsities of the planets and their moons are:
-- Planet: Mercury Density: 5423207461100.61
-- Planet: Venus Density: 5244981499937.27
-- Planet: Earth Density: 5493290217278.86
-- Moon: Moon Density: 3342333487677.45
-- Planet: Mars Density: 3909850439575.18
-- Moon: Phobos Density: 1937124159200.53
-- Moon: Deimos Density: 1989438469055.48
-- Planet: Jupiter Density: 1241346483801.08
-- Moon: Metis Density: 2852854764625.56
-- Moon: Adrastea Density: 4559792971075.16
-- Moon: Amalthea Density: 2060854602033.43
-- Moon: Thebe Density: 1483961942837.86
-- Moon: Io Density: 3530476938972.24
-- Moon: Europa Density: 2989578553001.77
-- Moon: Ganymede Density: 1933419796407.29
-- Moon: Callisto Density: 1858121894061.26
-- Moon: Leda Density: 2648439961930.11
-- Moon: Himalia Density: 2837401566344.85
-- Moon: Lysithea Density: 3180645453613.39
-- Moon: Elara Density: 3380508143583.85
-- Moon: Ananke Density: 2702099538414.91
-- Moon: Carme Density: 2852854764625.56
-- Moon: Pasiphae Density: 2918267501488.1
-- Moon: Sinope Density: 3180645453613.39
-- Planet: Saturn Density: 619440724839.92
-- Moon: Pan Density:
-- Moon: Atlas Density:
-- Moon: Prometheus Density: 662219593956.99
-- Moon: Pandora Density: 708902596683.2
-- Moon: Epimetheus Density: 720607865870.96
-- Moon: Janus Density: 670512576081.48
-- Moon: Mimas Density: 1136014294186.22
-- Moon: Enceladus Density: 1128850881270.67
-- Moon: Tethys Density: 997411872420.19
-- Moon: Telesto Density:
-- Moon: Calypso Density:
-- Moon: Dione Density: 1427371350565.95
-- Moon: Helene Density:
-- Moon: Rhea Density: 1236641058523.69
-- Moon: Titan Density: 1887616764952.93
-- Moon: Hyperion Density: 1445030159723.25
-- Moon: Iapetus Density: 1025499727080.26
-- Moon: Phoebe Density: 717453392296.49
-- Planet: Uranus Density: 1241080376525.09
-- Moon: Cordelia Density:
-- Moon: Ophelia Density:
-- Moon: Bianca Density:
-- Moon: Cressida Density:
-- Moon: Desdemona Density:
-- Moon: Juliet Density:
-- Moon: Portia Density:
-- Moon: Rosalind Density:
-- Moon: Belinda Density:
-- Moon: Puck Density:
-- Moon: Miranda Density: 1196909084340.01
-- Moon: Ariel Density: 1643301035277.6
-- Moon: Umbriel Density: 1395179290155.06
-- Moon: Titania Density: 1715755711587.57
-- Moon: Oberon Density: 1630513064006.3
-- Moon: Caliban Density: 0
-- Moon: Sycorax Density: 0
-- Planet: Neptune Density: 1603039868104.94
-- Moon: Naiad Density:
-- Moon: Thalassa Density:
-- Moon: Despina Density:
-- Moon: Galatea Density:
-- Moon: Larissa Density:
-- Moon: Proteus Density:
-- Moon: Triton Density: 2072320044148.04
-- Moon: Nereid Density:
-- Planet: Pluto Density: 2062690576163.99
-- Moon: Charon Density: 2254096529917.23
PL/SQL procedure successfully completed.
SQL> spool off
11.
Use or create the baseball table. Write a procedure that
will calculate the age at death of people in the baseball table. Include an
exception section that will let you know if the person is not yet deceased.
--
--Use or create the baseball table. Write a procedure that will calculate the
--age at death of people in the baseball table. Include an exception section
--that will let --you know if the person is not yet deceased.
--
spool a:\h5q3
set serveroutput on
--
CREATE OR REPLACE PROCEDURE BBAGE AS
vBB baseball%ROWTYPE;
vAge NUMBER;
eNotdead EXCEPTION;
CURSOR cBB IS SELECT * FROM baseball ORDER BY dob;
BEGIN
DBMS_OUTPUT.ENABLE(10000);
DBMS_OUTPUT.PUT_LINE('Lives of Famous Baseball Players');
OPEN cBB;
FETCH cBB INTO vBB;
WHILE cBB%FOUND LOOP
IF vBB.dod IS NULL THEN
RAISE eNotdead;
END IF;
vAge := MONTHS_BETWEEN(vBB.dod,vBB.dob)/12;
DBMS_OUTPUT.PUT_LINE(vBB.first_name||' '||vBB.last_name||' was '
||ROUND(vAge,0)||' years old when he died in '||vBB.dod);
FETCH cBB INTO vBB;
END LOOP;
close cBB;
EXCEPTION
WHEN eNotdead THEN
DBMS_OUTPUT.PUT_LINE('Some of the players are not dead yet.');
CLOSE cBB;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('There is another error.');
END;
/
--
--execute the procedures
--
exec bbage
--
--Output
--
Procedure created.
Lives of Famous Baseball Players
Cap Anson was 70 years old when he died in 14-APR-22
Honus Wagner was 82 years old when he died in 06-DEC-55
Nap Lajoie was 84 years old when he died in 07-FEB-59
Ty Cobb was 75 years old when he died in 17-JUL-61
Tris Speaker was 71 years old when he died in 08-DEC-58
Dutch Zwilling was 89 years old when he died in 27-MAR-78
Stuffy McInnis was 69 years old when he died in 16-FEB-60
Rabbit Maranville was 62 years old when he died in 05-JAN-54
Babe Ruth was 54 years old when he died in 16-AUG-48
Rogers Hornsby was 67 years old when he died in 05-JAN-63
Kiki Cuyler was 51 years old when he died in 11-FEB-50
Pie Traynor was 72 years old when he died in 16-MAR-72
Hack Wilson was 49 years old when he died in 23-NOV-48
Goose Goslin was 71 years old when he died in 15-MAY-71
Paul Waner was 62 years old when he died in 29-AUG-65
Lou Gehrig was 38 years old when he died in 02-JUN-41
Lloyd Waner was 76 years old when he died in 22-JUL-82
Luke Appling was 84 years old when he died in 03-JAN-91
Jimmie Foxx was 60 years old when he died in 21-JUL-67
Hank Greenberg was 76 years old when he died in 04-SEP-86
Joe DiMaggio was 84 years old when he died in 08-MAR-99
Pee Wee Reese was 81 years old when he died in 14-AUG-99
Some of the players are not dead yet.
PL/SQL procedure successfully completed.
SQL> spool off;
12. Use or
create the world tables.
a.
Write a function that creates an information systems
development index (isdi) based on the following formula: isdi= ((175-hdi)/100)
+ 3 * internet users+ 2 * computers)/6. Round your results to two decimal
places.
b.
Write another function that creates an electronic index
(edi) based on the following: edi:= (((175-hdi)/100)+ radio + 2 * TV +
phone_lines + 2 * Fax + 3 * cell + isdi(hdi))/10. Round to two decimal places.
--
--Use or create the world tables.
-- a. Write a function that creates an information systems development
-- index (isdi) based on the following formula:
-- isdi= ((175-hdi)/100) + 3 * internet users+ 2 * computers)/6.
-- Round your results to two decimal places.
-- b. Write another function that creates an electronic index (edi)
-- based on the following:
-- edi:= (((175-hdi)/100)+ radio + 2 * TV + phone_lines +
-- 2 * Fax + 3 * cell + isdi(hdi))/10. Round to two decimal places.
--
spool a:\h5q4
set serveroutput on
--
--create isdi function
--
CREATE OR REPLACE FUNCTION isdi(phdi NUMBER) RETURN NUMBER AS
vhdi world_el.hdi_rank%TYPE;
vint world_el.internet_users%TYPE;
vcom world_el.computers%TYPE;
visdi NUMBER;
BEGIN
SELECT hdi_rank, internet_users, computers into vhdi, vint, vcom
FROM world_el WHERE hdi_rank = phdi;
visdi := ROUND(((175 - vhdi)/100 + 3 * vint + 2 * vcom)/ 6, 2);
RETURN visdi;
END;
/
--
--create edi function
--
CREATE OR REPLACE FUNCTION edi(phdi NUMBER) RETURN NUMBER AS
vwel world_el%ROWTYPE;
vedi NUMBER;
BEGIN
SELECT * into vwel FROM world_el WHERE hdi_rank = phdi;
vedi := ROUND((((175-vwel.hdi_rank)/100)+ vwel.radio + 2 * vwel.TV + vwel.phone_line +
2 * vwel.Fax + 3 * vwel.cell_phone + isdi(vwel.hdi_rank))/10, 2);
RETURN vedi;
END;
/
--
--create a procedure that calls these functions and outputs the results
--
CREATE OR REPLACE PROCEDURE electronic AS
vIsdi NUMBER;
vEdi NUMBER;
vHdi world_pop.hdi_rank%TYPE;
vCountry world_pop.country%TYPE;
CURSOR cEl IS SELECT hdi_rank, country FROM world_pop ORDER BY hdi_rank;
BEGIN
DBMS_OUTPUT.ENABLE(20000);
DBMS_OUTPUT.PUT_LINE('Country HDI Rank ISDI Rank EDI Rank');
DBMS_OUTPUT.PUT_LINE('------------------------------------------------------------------');
OPEN cEl;
FETCH cEl INTO vHdi, vCountry;
WHILE cEl%FOUND LOOP
vIsdi := isdi(vHdi);
vEdi := edi(vHdi);
DBMS_OUTPUT.PUT_LINE(RPAD(vCountry,30)||LPAD(vHdi,4)
||LPAD(vIsdi,7)||LPAD(vEdi,7));
FETCH cEl INTO vHdi, vCountry;
END LOOP;
END;
/
--execute the procedure
--
exec electronic
--
--Output
--
Function created.
Function created.
Procedure created.
Country HDI Rank ISDI Rank EDI Rank
------------------------------------------------------------------
Canada 1 85.07 328.59
France 2 49.37 279.88
Norway 3 123.34 328.48
USA 4 128.62 478.62
Iceland 5 124.64 271.99
Finland 6 130.47 337.76
Netherlands 7 86.52 270.46
Japan 8 54.73 307.07
New Zealand 9 99.56 294.89
Sweden 10 89.96 329.48
Spain 11 29.38 178.39
Belgium 12 51.31 229.81
Austria 13 50.96 234.56
United Kingdom 14 75.14 359.17
Australia 15 119.92 365.3
Switzerland 16 134.02 276.85
Ireland 17 54.2 196.63
Denmark 18 109.17 339.43
Germany 19 64.38 274.08
Greece 20 15.21 190.22
Italy 21 30.76 236.19
Israel 22 60.29 178.49
Cyprus 23 15.95 126.83
Barbados 24 19.46 190.7
Hong Kong, China 25 67.82 246.95
Luxembourg 26 8.2 263.63
Malta 27 28.28 201.67
Singapore 28 75.57 218.79
Antigua and Barbuda 29 11.59 127.01
Korea, Rep. of 30 39.59 223.15
Chile 31 16.38 109.93
Bahamas 32 5.09 120.75
Portugal 33 24.95 140.2
Costa Rica 34 2.39 88.69
Brunei Darussalam 35 1.68 212.71
Argentina 36 9.12 157.29
Slovenia 37 30.43 152.89
Uruguay 38 8.82 146.95
Czech Republic 39 28.61 174.49
Trinidad and Tobago 40 7.36 132.61
Dominica 41 2.87 117.09
Slovakia 42 16.49 125.18
Bahrain 43 17.84 187.86
Fiji 44 .27 89.03
Panama 45 .52 80.2
Venezuela 46 6.02 99.02
Hungary 47 18.67 182.29
United Arab Emirates 48 16.9 128.17
Mexico 49 9.67 77.47
Saint Kitts and Nevis 50 .21 102.07
Grenada 51 .21 118.36
Poland 52 12.95 144.12
Colombia 53 5.96 107.34
Kuwait 54 19.98 171.63
Saint Vincent 55 .85 131.76
Seychelles 56 .2 110.83
Qatar 57 1.1 171.17
Saint Lucia 58 1.55 157.2
Thailand 59 5.09 76.28
Malaysia 60 13.62 122.42
Mauritius 61 10.81 96.06
Brazil 62 5.12 105.99
Belize 63 9.45 108.28
Libyan Arab Jamahiriya 64 .19 56.71
Suriname 65 .78 123.09
Lebanon 66 4.64 160.5
Bulgaria 67 7.94 151.48
Belarus 68 .18 101.01
Turkey 69 4.73 88.55
Saudi Arabia 70 .23 92.92
Oman 71 4.42 79.74
Russian Federation 72 6.81 127.8
Ecuador 73 1.68 70.89
Romania 74 2.28 75.01
DPR of Korea 75 .17 41.33
Croatia 76 9.64 104.32
Estonia 77 15.91 168.64
Islamic Rep of Iran 78 .16 57.4
Lithuania 79 2.32 139.98
Macedonia, FYR 80 .36 68.86
Syrian Arab Republic 81 .18 50.72
Algeria 82 1.17 42.46
Tunisia 83 2.44 58.02
Jamaica 84 .45 122.06
Cuba 85 .15 78.47
Peru 86 2.28 51.99
Jordan 87 2.92 70.03
Dominican Republic 88 .25 43.66
South Africa 89 14.54 66.66
Sri Lanka 90 .51 35.91
Paraguay 91 .14 51.24
Latvia 92 2.79 192.02
Kazakhstan 93 .19 105.42
Western Samoa 94 .14 56.2
Maldives 95 4.22 28.83
Indonesia 96 1.43 46.64
Botswana 97 .13 22.5
Philippines 98 3.14 44.57
Armenia 99 .38 64.26
Guyana 100 .13 63.63
Mongolia 101 .12 28.78
Ukraine 102 2.2 148.69
Turkmenistan 103 .12 58.67
Uzbekistan 104 .12 52.41
Albania 105 .17 39.74
China 106 .83 72.34
Namibia 107 .16 25.62
Georgia 108 .11 109.49
Kyrgyzstan 109 .11 66.73
Azerbaijan 110 .11 53.2
Guatemala 111 1.05 35.24
Egypt 112 .26 61.16
Moldova, Rep. of 113 .82 143.14
El Salvador 114 .1 100.2
Swaziland 115 .1 37.67
Bolivia 116 .15 112.65
Cape Verde 117 .1 24.31
Tajikistan 118 .1 56.23
Honduras 119 .09 59.84
Gabon 120 1.6 36.9
Sâo Tomé and Principe 121 .09 29.33
Viet Nam 122 .23 44.46
Solomon Islands 123 .19 17.84
Vanuatu 124 .09 35.02
Morocco 125 .69 56.38
Nicaragua 126 .23 64.73
Iraq 127 .08 40.56
Congo 128 .08 15.87
Papua New Guinea 129 .08 41.96
Zimbabwe 130 1.13 15.86
Myanmar 131 .07 24.52
Cameroon 132 .07 30.76
Ghana 133 .46 26.85
Lesotho 134 .07 6.11
Equatorial Guinea 135 .07 61.63
PDR Lao 136 .07 14.89
Kenya 137 .29 14.22
Pakistan 138 .46 15.67
India 139 .45 21.7
Cambodia 140 .06 13.34
Comoros 141 .06 15.64
Nigeria 142 .06 27.74
DR of the Congo 143 .05 18.21
Togo 144 .07 24.94
Benin 145 .05 24.44
Zambia 146 .1 23.62
Bangladesh 147 .05 6.39
Côte d Ivoire 148 .05 27.94
Mauritania 149 .04 27.07
Tanzania, U. Rep. of 150 .04 31.17
Yemen 151 .04 59.16
Nepal 152 .04 4.61
Madagascar 153 .04 24.27
Central African Republic 154 .04 8.77
Bhutan 155 .03 2.62
Angola 156 .03 14.24
Sudan 157 .03 44.53
Senegal 158 2.43 20.64
Haiti 159 .03 7.16
Uganda 160 .2 17.22
Malawi 161 .02 26
Djibouti 162 .12 23.97
Chad 163 .02 25.3
Guinea Bissau 164 .02 5.19
Gambia 165 .02 18.72
Mozambique 166 .02 4.75
Guinea 167 .06 19.82
Eritrea 168 .01 11.53
Ethiopia 169 .01 20.36
Burundi 170 .01 8.48
Mali 171 .01 7.18
Burkina Faso 172 .01 4.29
Niger 173 0 11.56
Sierra Leone 174 0 28.62
PL/SQL procedure successfully completed.
SQL> spool off
Back to Oracle Page | Back to Chris Riddiough's Home Page