|
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_OU