SPOOL file-name SET ECHO ON SET FEEDBACK ON SET LINESIZE 100 SET PAGESIZE 200 SET SERVEROUTPUT ON

Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
icon
Related questions
Question

Hey experts, please help me out on this :( xx 

Sample database:

For all the implemented tasks, your report or output must include a listing of all PL/SQL 
statements processed. To achieve that put the following SQL*Plus commands in all 
your scripts:
SPOOL file-name
SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 100
SET PAGESIZE 200
SET SERVEROUTPUT ON
at the beginning of SQL script and
SPOOL OFF
at the end of SQL script.

1551328
1590017
1756133
1770752
1778023
10-12-1993
23-07-1993
Total order amount:
Order key
73764
142119
207687
361286
455911
459968
469189
543553
547431
579013
Customer key: 36343, Customer #000036343
Total number of orders made: 33
755399
814246
852194
867748
871808
887045
888673
1014406
1054657
1074018
1089380
1175392
1242469
1287396
1341957
1345955
15-12-1993
24-01-1996
27-08-1994
1380164
1527617
1633895
1669157
1680610
1716192
1778052
Order date
29-07-1998
06-04-1995
01-10-1997
07-07-1993
27-12-1996
24-05-1996
05-04-1994
19-05-1996
24-02-1997
21-04-1993
30-01-1996
04-01-1998
19-07-1993
23-06-1993
25-12-1995
24-01-1995
09-07-1994
15-05-1992
24-09-1997
07-02-1994
10-11-1994
19-12-1997
30-06-1998
12-06-1995
07-01-1998
20-09-1992
15-12-1993
01-09-1997
18-05-1994
10-03-1997
06-04-1998
30-03-1998
19-04-1998
Total order amount:
$105,257.07
$96,769.73
$290,800.68
$329,544.51
$137,299.46
$4,885,169.38
Order amount
$251,350.54
$85,705.43
$121,832.00
$162,899.61
$89,021.33
$122,759.72
$68,410.10
$7,160.87
$95,629.70
$171,255.98
$260,444.95
$273,316.49
$210,217.06
$32,561.87
$216,665.55
$105,670.34
$85,809.02
$251,312.53
$3,552.68
$64,887.99
$80,492.99
$99,484.16
$159,819.75
$170,112.85
$223,858.72
$139,626.48
$224,382.21
$228,696.56
$140,485.51
$204,629.57
$46,715.12
$237,051.15
$249,389.92
$4,885,208.75
It is up to you to decide if you want to handle exception in your procedure.
Transcribed Image Text:1551328 1590017 1756133 1770752 1778023 10-12-1993 23-07-1993 Total order amount: Order key 73764 142119 207687 361286 455911 459968 469189 543553 547431 579013 Customer key: 36343, Customer #000036343 Total number of orders made: 33 755399 814246 852194 867748 871808 887045 888673 1014406 1054657 1074018 1089380 1175392 1242469 1287396 1341957 1345955 15-12-1993 24-01-1996 27-08-1994 1380164 1527617 1633895 1669157 1680610 1716192 1778052 Order date 29-07-1998 06-04-1995 01-10-1997 07-07-1993 27-12-1996 24-05-1996 05-04-1994 19-05-1996 24-02-1997 21-04-1993 30-01-1996 04-01-1998 19-07-1993 23-06-1993 25-12-1995 24-01-1995 09-07-1994 15-05-1992 24-09-1997 07-02-1994 10-11-1994 19-12-1997 30-06-1998 12-06-1995 07-01-1998 20-09-1992 15-12-1993 01-09-1997 18-05-1994 10-03-1997 06-04-1998 30-03-1998 19-04-1998 Total order amount: $105,257.07 $96,769.73 $290,800.68 $329,544.51 $137,299.46 $4,885,169.38 Order amount $251,350.54 $85,705.43 $121,832.00 $162,899.61 $89,021.33 $122,759.72 $68,410.10 $7,160.87 $95,629.70 $171,255.98 $260,444.95 $273,316.49 $210,217.06 $32,561.87 $216,665.55 $105,670.34 $85,809.02 $251,312.53 $3,552.68 $64,887.99 $80,492.99 $99,484.16 $159,819.75 $170,112.85 $223,858.72 $139,626.48 $224,382.21 $228,696.56 $140,485.51 $204,629.57 $46,715.12 $237,051.15 $249,389.92 $4,885,208.75 It is up to you to decide if you want to handle exception in your procedure.
Stored PL/SQL procedure
a) Implement a stored PL/SQL procedure TotalNumOfOrdersMade that lists
information about the customer and the details of the orders made by the
customers. The procedure takes as parameter a nation name and computes
the total number of orders made by all the customers from that nation. The
information to be displayed include the customer key, customer name, total
number of orders the customer made, and a list of the order key, order
date, and order amount. An example of a segment of the output for the list
of customers and the customers' order details is as follow:
Enter value for nationname: China
old 62:
nationName := upper ('&nationName');
new 62: nationName: upper ('China');
Customer key: 6745, Customer #000006745
Total number of orders made: 33
Order key
57092
127745
231142
249600
251712
268322
275591
303364
332932
335363
402593
422881
447428
450119
477027
493731
518695
528000
640161
650247
688134
839463
905700
981699
991591
1177796
1187936
1510466
Order date
02-05-1993
03-03-1993
01-08-1994
18-12-1994
11-04-1997
01-08-1996
20-01-1998
05-08-1997
14-12-1992
04-10-1992
27-05-1994
16-08-1997
02-08-1997
14-01-1994
26-01-1997
14-09-1997
27-08-1995
17-05-1997
14-07-1994
06-06-1998
05-10-1996
29-11-1993
10-01-1992
02-07-1993
11-04-1993
20-07-1997
24-03-1995
12-04-1994
Order amount
$115,109.85
$37,455.13
$143,399.55
$95,936.61
$92,074.43
$232,737.36
$127,975.27
$171,623.70
$120,153.30
$119,202.53
$81,549.75
$83,703.62
$154,502.67
$198,730.51
$125,974.62
$230,883.10
$150, 625.98
$117,289.68
$153,853.47
$55,327.56
$56,012.56
$267,659.99
$354,849.22
$174,903.36
$66,109.85
$244,462.49
$2,622.27
$150,769.50
Transcribed Image Text:Stored PL/SQL procedure a) Implement a stored PL/SQL procedure TotalNumOfOrdersMade that lists information about the customer and the details of the orders made by the customers. The procedure takes as parameter a nation name and computes the total number of orders made by all the customers from that nation. The information to be displayed include the customer key, customer name, total number of orders the customer made, and a list of the order key, order date, and order amount. An example of a segment of the output for the list of customers and the customers' order details is as follow: Enter value for nationname: China old 62: nationName := upper ('&nationName'); new 62: nationName: upper ('China'); Customer key: 6745, Customer #000006745 Total number of orders made: 33 Order key 57092 127745 231142 249600 251712 268322 275591 303364 332932 335363 402593 422881 447428 450119 477027 493731 518695 528000 640161 650247 688134 839463 905700 981699 991591 1177796 1187936 1510466 Order date 02-05-1993 03-03-1993 01-08-1994 18-12-1994 11-04-1997 01-08-1996 20-01-1998 05-08-1997 14-12-1992 04-10-1992 27-05-1994 16-08-1997 02-08-1997 14-01-1994 26-01-1997 14-09-1997 27-08-1995 17-05-1997 14-07-1994 06-06-1998 05-10-1996 29-11-1993 10-01-1992 02-07-1993 11-04-1993 20-07-1997 24-03-1995 12-04-1994 Order amount $115,109.85 $37,455.13 $143,399.55 $95,936.61 $92,074.43 $232,737.36 $127,975.27 $171,623.70 $120,153.30 $119,202.53 $81,549.75 $83,703.62 $154,502.67 $198,730.51 $125,974.62 $230,883.10 $150, 625.98 $117,289.68 $153,853.47 $55,327.56 $56,012.56 $267,659.99 $354,849.22 $174,903.36 $66,109.85 $244,462.49 $2,622.27 $150,769.50
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 2 steps with 2 images

Blurred answer
Knowledge Booster
Files and Directory
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Similar questions
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education