-
Notifications
You must be signed in to change notification settings - Fork 1
/
Asg_6.txt
168 lines (142 loc) · 5.28 KB
/
Asg_6.txt
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
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
AARTI RATHI
My website - https://shinchancode.github.io/3d-react-portfolio/
Problem Statement :
Write and execute PL/SQL stored procedure and function to perform a suitable task on the database.
-----------------------------------------------------------------------------------------------------------------
mysql> use mydb;
Database changed
mysql> select * from employee;
+-------+--------------+-----------+------------+------------+--------+------+------+
| empid | ename | contactno | DOB | DOJ | salary | Pid | did |
+-------+--------------+-----------+------------+------------+--------+------+------+
| 1256 | Aarti Rathi | 77852 | 2000-10-17 | 2017-10-14 | 17025 | 2 | 5 |
| 1257 | Aditya Rathi | 96890 | 0000-00-00 | 2014-03-27 | 21005 | 4 | 1 |
| 1271 | Binod | 78523 | 1995-08-10 | 2018-07-11 | 25418 | 3 | 4 |
| 1222 | Anushka | 77453 | 1998-04-01 | 2019-12-12 | 27521 | 1 | 5 |
| 6214 | NULL | NULL | NULL | NULL | 10456 | NULL | NULL |
+-------+--------------+-----------+------------+------------+--------+------+------+
5 rows in set (6.15 sec)
mysql> delete from employee
-> where empid = 6214;
Query OK, 1 row affected (1.40 sec)
mysql> select * from employee;
+-------+--------------+-----------+------------+------------+--------+------+------+
| empid | ename | contactno | DOB | DOJ | salary | Pid | did |
+-------+--------------+-----------+------------+------------+--------+------+------+
| 1256 | Aarti Rathi | 77852 | 2000-10-17 | 2017-10-14 | 17025 | 2 | 5 |
| 1257 | Aditya Rathi | 96890 | 0000-00-00 | 2014-03-27 | 21005 | 4 | 1 |
| 1271 | Binod | 78523 | 1995-08-10 | 2018-07-11 | 25418 | 3 | 4 |
| 1222 | Anushka | 77453 | 1998-04-01 | 2019-12-12 | 27521 | 1 | 5 |
+-------+--------------+-----------+------------+------------+--------+------+------+
4 rows in set (0.00 sec)
mysql> delimiter $$
mysql> select * from employee;
-> $$
+-------+--------------+-----------+------------+------------+--------+------+------+
| empid | ename | contactno | DOB | DOJ | salary | Pid | did |
+-------+--------------+-----------+------------+------------+--------+------+------+
| 1256 | Aarti Rathi | 77852 | 2000-10-17 | 2017-10-14 | 17025 | 2 | 5 |
| 1257 | Aditya Rathi | 96890 | 0000-00-00 | 2014-03-27 | 21005 | 4 | 1 |
| 1271 | Binod | 78523 | 1995-08-10 | 2018-07-11 | 25418 | 3 | 4 |
| 1222 | Anushka | 77453 | 1998-04-01 | 2019-12-12 | 27521 | 1 | 5 |
+-------+--------------+-----------+------------+------------+--------+------+------+
4 rows in set (0.00 sec)
mysql> drop procedure if exists helloworld$$
Query OK, 0 rows affected, 1 warning (0.49 sec)
mysql> create procedure helloworld()
-> begin
-> select "Hello World";
-> end$$
Query OK, 0 rows affected (0.36 sec)
mysql> call helloworld()$$
+-------------+
| Hello World |
+-------------+
| Hello World |
+-------------+
1 row in set (0.13 sec)
Query OK, 0 rows affected (0.14 sec)
mysql> create procedure funsqrt(num1 int)
-> begin
-> declare result int;
-> set result = sqrt(num1);
-> select result;
-> end$$
Query OK, 0 rows affected (0.51 sec)
mysql> call funsqrt(4)$$
+--------+
| result |
+--------+
| 2 |
+--------+
1 row in set (0.06 sec)
Query OK, 0 rows affected (0.06 sec)
mysql> create procedure oddeven(num1 int)
-> begin
-> if mod(num1,2)=0 then
-> select "Even number";
-> else
-> select "Odd number";
-> end if;
-> end$$
Query OK, 0 rows affected (0.21 sec)
mysql> call oddeven(4)$$
+-------------+
| Even number |
+-------------+
| Even number |
+-------------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> call oddeven(15)$$
+------------+
| Odd number |
+------------+
| Odd number |
+------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> create function funsqrt(num1 int)
-> returns int
-> deterministic
-> begin
-> declare result int;
-> set result = sqrt(num1);
-> return result;
-> end$$
Query OK, 0 rows affected (0.25 sec)
mysql> select funsqrt(49)$$
+-------------+
| funsqrt(49) |
+-------------+
| 7 |
+-------------+
1 row in set (0.09 sec)
mysql> create procedure classemp(eid int)
-> begin
-> declare sal int;
-> select salary into sal from employee where empid=eid;
-> if sal<25000 then
-> select "class X";
-> end if;
-> end$$
Query OK, 0 rows affected (0.27 sec)
mysql> call classemp(1)$$
Query OK, 0 rows affected (0.21 sec)
mysql> call classemp(4)$$
Query OK, 0 rows affected (0.00 sec)
mysql> create function classemp(eid int)
-> returns varchar(20)
-> deterministic
-> begin
-> declare sal int;
-> declare class varchar(20);
-> select salary into sal from employee where empid=eid;
-> if sal<25000 then
-> set class = "class X";
-> end if;
-> return class;
-> end$$
Query OK, 0 rows affected (0.15 sec)
mysql> call classemp(2)$$
Query OK, 0 rows affected (0.00 sec)