-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathSQL08-Stored_Procedures
83 lines (63 loc) · 3.35 KB
/
SQL08-Stored_Procedures
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
/* STORED PROCEDURES */
CREATE DATABASE Courses;
USE Courses;
CREATE TABLE Course(
idCourse INTEGER PRIMARY KEY auto_increment,
nameCourse VARCHAR (30) NOT NULL,
hoursCourse INTEGER (2) NOT NULL,
priceCourse FLOAT (10,2) NOT NULL,
modelCourse ENUM('PRESENCIAL', 'ONLINE', 'HYBRID')
);
-- INPUT PROCEDURE --
delimiter #;
status
CREATE PROCEDURE CourseInput(
nameCourse VARCHAR (30),
hoursCourse INTEGER (2),
priceCourse FLOAT (10,2),
modelCourse ENUM('PRESENCIAL', 'ONLINE', 'HYBRID'))
BEGIN
INSERT INTO Course VALUES(
NULL, nameCourse, hoursCourse, priceCourse, modelCourse);
END #
delimiter ;#
status
-- USING DELIMITER --
CALL CourseInput('Basic BI', 30, 499.99, 'PRESENCIAL');
CALL CourseInput('Intermediate BI', 60, 799.99, 'PRESENCIAL');
CALL CourseInput('Advanced BI', 120, 1999.99, 'PRESENCIAL');
CALL CourseInput('SQL', 40, 200.99, 'PRESENCIAL');
Select * from Course;
+----------+-----------------+-------------+-------------+-------------+
| idCourse | nameCourse | hoursCourse | priceCourse | modelCourse |
+----------+-----------------+-------------+-------------+-------------+
| 1 | Basic BI | 30 | 499.99 | PRESENCIAL |
| 2 | Intermediate BI | 60 | 799.99 | PRESENCIAL |
| 3 | Advanced BI | 120 | 1999.99 | PRESENCIAL |
| 4 | SQL | 40 | 200.99 | PRESENCIAL |
+----------+-----------------+-------------+-------------+-------------+
-- CREATING ANOTHER PROCEDURE --
delimiter #;
CREATE PROCEDURE CourseSelect ()
BEGIN
select * from Course;
END #
delimiter ;#
-- RESULTS --
show PROCEDURE status
WHERE db = 'Courses';
+---------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+---------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| courses | CourseInput | PROCEDURE | root@localhost | 2022-01-10 21:35:47 | 2022-01-10 21:35:47 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |
| courses | CourseSelect | PROCEDURE | root@localhost | 2022-01-12 11:12:56 | 2022-01-12 11:12:56 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |
+---------+--------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
Call CourseSelect;
+----------+-----------------+-------------+-------------+-------------+
| idCourse | nameCourse | hoursCourse | priceCourse | modelCourse |
+----------+-----------------+-------------+-------------+-------------+
| 1 | Basic BI | 30 | 499.99 | PRESENCIAL |
| 2 | Intermediate BI | 60 | 799.99 | PRESENCIAL |
| 3 | Advanced BI | 120 | 1999.99 | PRESENCIAL |
| 4 | SQL | 40 | 200.99 | PRESENCIAL |
+----------+-----------------+-------------+-------------+-------------+