![Page 1: Ten Minute Design 5 Stored Procedures 1carme.cs.trinity.edu/.../Ten-Minute-Design-05-Stored-Procedures-1.pdf · 2] Call DisplayNGenre(15); 3] If NoToDisplay = 15 This Stored Procedure](https://reader034.vdocuments.us/reader034/viewer/2022042111/5e8bea9e0fc5ee1b7d04adf9/html5/thumbnails/1.jpg)
1
Dr. Tom HicksComputer Science Department
Ten Minute Design 5 Stored Procedures 1
Elementary School Library
Software EngineeringCSCI-3321
![Page 2: Ten Minute Design 5 Stored Procedures 1carme.cs.trinity.edu/.../Ten-Minute-Design-05-Stored-Procedures-1.pdf · 2] Call DisplayNGenre(15); 3] If NoToDisplay = 15 This Stored Procedure](https://reader034.vdocuments.us/reader034/viewer/2022042111/5e8bea9e0fc5ee1b7d04adf9/html5/thumbnails/2.jpg)
2
Grab Documents
![Page 3: Ten Minute Design 5 Stored Procedures 1carme.cs.trinity.edu/.../Ten-Minute-Design-05-Stored-Procedures-1.pdf · 2] Call DisplayNGenre(15); 3] If NoToDisplay = 15 This Stored Procedure](https://reader034.vdocuments.us/reader034/viewer/2022042111/5e8bea9e0fc5ee1b7d04adf9/html5/thumbnails/3.jpg)
3
Grab The Database
• Copy ESL5.sql from The Schedule PageOR From Mars
![Page 4: Ten Minute Design 5 Stored Procedures 1carme.cs.trinity.edu/.../Ten-Minute-Design-05-Stored-Procedures-1.pdf · 2] Call DisplayNGenre(15); 3] If NoToDisplay = 15 This Stored Procedure](https://reader034.vdocuments.us/reader034/viewer/2022042111/5e8bea9e0fc5ee1b7d04adf9/html5/thumbnails/4.jpg)
4
Import Database
ESL5
![Page 5: Ten Minute Design 5 Stored Procedures 1carme.cs.trinity.edu/.../Ten-Minute-Design-05-Stored-Procedures-1.pdf · 2] Call DisplayNGenre(15); 3] If NoToDisplay = 15 This Stored Procedure](https://reader034.vdocuments.us/reader034/viewer/2022042111/5e8bea9e0fc5ee1b7d04adf9/html5/thumbnails/5.jpg)
5
Import ESL5.sql
1 - Load MySQL Workbench - Import ESL5.sql Call The Database ELS5TH Using Your Initials.
![Page 6: Ten Minute Design 5 Stored Procedures 1carme.cs.trinity.edu/.../Ten-Minute-Design-05-Stored-Procedures-1.pdf · 2] Call DisplayNGenre(15); 3] If NoToDisplay = 15 This Stored Procedure](https://reader034.vdocuments.us/reader034/viewer/2022042111/5e8bea9e0fc5ee1b7d04adf9/html5/thumbnails/6.jpg)
6
Note That Table User Has No Records
1 - Table User Layout Matches Our CD Design.
![Page 7: Ten Minute Design 5 Stored Procedures 1carme.cs.trinity.edu/.../Ten-Minute-Design-05-Stored-Procedures-1.pdf · 2] Call DisplayNGenre(15); 3] If NoToDisplay = 15 This Stored Procedure](https://reader034.vdocuments.us/reader034/viewer/2022042111/5e8bea9e0fc5ee1b7d04adf9/html5/thumbnails/7.jpg)
7
We Need 100,000 User Records For A Final Application
Check-Out
![Page 8: Ten Minute Design 5 Stored Procedures 1carme.cs.trinity.edu/.../Ten-Minute-Design-05-Stored-Procedures-1.pdf · 2] Call DisplayNGenre(15); 3] If NoToDisplay = 15 This Stored Procedure](https://reader034.vdocuments.us/reader034/viewer/2022042111/5e8bea9e0fc5ee1b7d04adf9/html5/thumbnails/8.jpg)
8
Yuk
1] Execute 100,000 Queries One At A Time
INSERT INTO User (ID, First, MI, Last, UserTypeID, Email,FullName, Zipcode, FullName, Deleted, Dated)
VALUES ( …
![Page 9: Ten Minute Design 5 Stored Procedures 1carme.cs.trinity.edu/.../Ten-Minute-Design-05-Stored-Procedures-1.pdf · 2] Call DisplayNGenre(15); 3] If NoToDisplay = 15 This Stored Procedure](https://reader034.vdocuments.us/reader034/viewer/2022042111/5e8bea9e0fc5ee1b7d04adf9/html5/thumbnails/9.jpg)
9
Stored Program
DisplayGenre()
![Page 10: Ten Minute Design 5 Stored Procedures 1carme.cs.trinity.edu/.../Ten-Minute-Design-05-Stored-Procedures-1.pdf · 2] Call DisplayNGenre(15); 3] If NoToDisplay = 15 This Stored Procedure](https://reader034.vdocuments.us/reader034/viewer/2022042111/5e8bea9e0fc5ee1b7d04adf9/html5/thumbnails/10.jpg)
10
Create Stored Program DisplayGenre
1 - Right-Mouse Click On Stored Procedure Select Create
2 - Name The Procedure 'DisplayGenre'
![Page 11: Ten Minute Design 5 Stored Procedures 1carme.cs.trinity.edu/.../Ten-Minute-Design-05-Stored-Procedures-1.pdf · 2] Call DisplayNGenre(15); 3] If NoToDisplay = 15 This Stored Procedure](https://reader034.vdocuments.us/reader034/viewer/2022042111/5e8bea9e0fc5ee1b7d04adf9/html5/thumbnails/11.jpg)
11
Add The Code To Display All Of Genre In Alpha Order
1] Push Apply Button
2] Note The Wrapper That Workbench Puts Around Our Procedure.
![Page 12: Ten Minute Design 5 Stored Procedures 1carme.cs.trinity.edu/.../Ten-Minute-Design-05-Stored-Procedures-1.pdf · 2] Call DisplayNGenre(15); 3] If NoToDisplay = 15 This Stored Procedure](https://reader034.vdocuments.us/reader034/viewer/2022042111/5e8bea9e0fc5ee1b7d04adf9/html5/thumbnails/12.jpg)
12
Execute The Procedure
2] You Can Pass Arguments To TheProcedure.
![Page 13: Ten Minute Design 5 Stored Procedures 1carme.cs.trinity.edu/.../Ten-Minute-Design-05-Stored-Procedures-1.pdf · 2] Call DisplayNGenre(15); 3] If NoToDisplay = 15 This Stored Procedure](https://reader034.vdocuments.us/reader034/viewer/2022042111/5e8bea9e0fc5ee1b7d04adf9/html5/thumbnails/13.jpg)
13
Command Line Execution
1] You Can Do All Of ThisFrom The CommandLine
![Page 14: Ten Minute Design 5 Stored Procedures 1carme.cs.trinity.edu/.../Ten-Minute-Design-05-Stored-Procedures-1.pdf · 2] Call DisplayNGenre(15); 3] If NoToDisplay = 15 This Stored Procedure](https://reader034.vdocuments.us/reader034/viewer/2022042111/5e8bea9e0fc5ee1b7d04adf9/html5/thumbnails/14.jpg)
14
Stored Program
DisplayNGenre(15)
![Page 15: Ten Minute Design 5 Stored Procedures 1carme.cs.trinity.edu/.../Ten-Minute-Design-05-Stored-Procedures-1.pdf · 2] Call DisplayNGenre(15); 3] If NoToDisplay = 15 This Stored Procedure](https://reader034.vdocuments.us/reader034/viewer/2022042111/5e8bea9e0fc5ee1b7d04adf9/html5/thumbnails/15.jpg)
15
Add The Code To Display N Genre In Alpha Order1] Let's Make Sure We Can Pass Information Into A Stored
Procedure.
2] Call DisplayNGenre(15);
3] If NoToDisplay = 15 This Stored Procedure Is To Display All Of The Information About The First 15
Undeleted Genre In Alpha Order.
WRITE THE CODE!
![Page 16: Ten Minute Design 5 Stored Procedures 1carme.cs.trinity.edu/.../Ten-Minute-Design-05-Stored-Procedures-1.pdf · 2] Call DisplayNGenre(15); 3] If NoToDisplay = 15 This Stored Procedure](https://reader034.vdocuments.us/reader034/viewer/2022042111/5e8bea9e0fc5ee1b7d04adf9/html5/thumbnails/16.jpg)
16
What New Thing Do We Need To Know How To Do?1] MySQL Equivalent Of
Form Loop
2] Call DisplayNGenre(4);
![Page 17: Ten Minute Design 5 Stored Procedures 1carme.cs.trinity.edu/.../Ten-Minute-Design-05-Stored-Procedures-1.pdf · 2] Call DisplayNGenre(15); 3] If NoToDisplay = 15 This Stored Procedure](https://reader034.vdocuments.us/reader034/viewer/2022042111/5e8bea9e0fc5ee1b7d04adf9/html5/thumbnails/17.jpg)
17
Stored Program
CreateUsers(15)
![Page 18: Ten Minute Design 5 Stored Procedures 1carme.cs.trinity.edu/.../Ten-Minute-Design-05-Stored-Procedures-1.pdf · 2] Call DisplayNGenre(15); 3] If NoToDisplay = 15 This Stored Procedure](https://reader034.vdocuments.us/reader034/viewer/2022042111/5e8bea9e0fc5ee1b7d04adf9/html5/thumbnails/18.jpg)
18
Create Procedure CreateUsers Pass NoUsers
1] Set The IDs = 1, 2, 3, … NoUsers
3] Call CreateUsers (10);
2] Lots Of Solutions
![Page 19: Ten Minute Design 5 Stored Procedures 1carme.cs.trinity.edu/.../Ten-Minute-Design-05-Stored-Procedures-1.pdf · 2] Call DisplayNGenre(15); 3] If NoToDisplay = 15 This Stored Procedure](https://reader034.vdocuments.us/reader034/viewer/2022042111/5e8bea9e0fc5ee1b7d04adf9/html5/thumbnails/19.jpg)
19
Run Procedure CreateUsers Pass NoUsers
1] Call CreateUsers (10);
2] We Will Handle First, MI, Last, etc Next Time. We Will Create Our DataFile One Field At A Time.WHAT DO YOU THINK OF THIS?
![Page 20: Ten Minute Design 5 Stored Procedures 1carme.cs.trinity.edu/.../Ten-Minute-Design-05-Stored-Procedures-1.pdf · 2] Call DisplayNGenre(15); 3] If NoToDisplay = 15 This Stored Procedure](https://reader034.vdocuments.us/reader034/viewer/2022042111/5e8bea9e0fc5ee1b7d04adf9/html5/thumbnails/20.jpg)
20
Run Procedure CreateUsers Again
1] Call CreateUsers (10);
2] WHAT DO YOU THINK OF THIS?
![Page 21: Ten Minute Design 5 Stored Procedures 1carme.cs.trinity.edu/.../Ten-Minute-Design-05-Stored-Procedures-1.pdf · 2] Call DisplayNGenre(15); 3] If NoToDisplay = 15 This Stored Procedure](https://reader034.vdocuments.us/reader034/viewer/2022042111/5e8bea9e0fc5ee1b7d04adf9/html5/thumbnails/21.jpg)
21
How Can We Avoid The Duplicates?1] Automate Delete From User
3] Call CreateUsers (10);
2] Lots Of Solutions
![Page 22: Ten Minute Design 5 Stored Procedures 1carme.cs.trinity.edu/.../Ten-Minute-Design-05-Stored-Procedures-1.pdf · 2] Call DisplayNGenre(15); 3] If NoToDisplay = 15 This Stored Procedure](https://reader034.vdocuments.us/reader034/viewer/2022042111/5e8bea9e0fc5ee1b7d04adf9/html5/thumbnails/22.jpg)
22
Backup Your DatabaseESL5TH
INCLUDE YOUR STORED PROCEDURES!
![Page 23: Ten Minute Design 5 Stored Procedures 1carme.cs.trinity.edu/.../Ten-Minute-Design-05-Stored-Procedures-1.pdf · 2] Call DisplayNGenre(15); 3] If NoToDisplay = 15 This Stored Procedure](https://reader034.vdocuments.us/reader034/viewer/2022042111/5e8bea9e0fc5ee1b7d04adf9/html5/thumbnails/23.jpg)
23
Workbench Server Data Export
![Page 24: Ten Minute Design 5 Stored Procedures 1carme.cs.trinity.edu/.../Ten-Minute-Design-05-Stored-Procedures-1.pdf · 2] Call DisplayNGenre(15); 3] If NoToDisplay = 15 This Stored Procedure](https://reader034.vdocuments.us/reader034/viewer/2022042111/5e8bea9e0fc5ee1b7d04adf9/html5/thumbnails/24.jpg)
24