Debian Codegraph @

mysql-test/suite/perfschema/r/statement_program_non_nested.result

raw
1 TRUNCATE TABLE performance_schema.events_statements_summary_by_program;
2 TRUNCATE TABLE performance_schema.events_statements_history_long;
3 ################################################
4 # Quering PS statement summary and history_long#
5 ################################################
6 # SET-UP
7 CREATE DATABASE stored_programs;
8 USE stored_programs;
9 CREATE TABLE t1(
10 i INT NOT NULL,
11 j INT
12 ) engine=innodb;
13 CREATE TABLE t2(
14 name CHAR(16) NOT NULL DEFAULT '',
15 id INT NOT NULL
16 ) engine=innodb;
17 CREATE TABLE t3(
18 d DATE,
19 n INT,
20 f DOUBLE,
21 s VARCHAR(32)
22 );
23 CREATE TABLE t4(
24 `k` int(10) unsigned NOT NULL AUTO_INCREMENT,
25 `word` varchar(100) NOT NULL,
26 `mean` varchar(300) NOT NULL,
27 PRIMARY KEY (`k`)
28 );
29 ############################
30 # Creating Stored Programs #
31 ############################
32 # Stored Routine ( Procedure & Function )
33 CREATE PROCEDURE SampleProc1(x1 INT, x2 INT, y INT)
34 BEGIN
35 INSERT INTO t1 VALUES (x1, y);
36 INSERT INTO t1 VALUES (x2, y);
37 END|
38 CREATE PROCEDURE SampleProc2(x CHAR(16), y INT)
39 BEGIN
40 DECLARE z1, z2 INT;
41 SET z1 = y;
42 SET z2 = z1+2;
43 INSERT INTO t2 VALUES (x, z2);
44 END|
45 CREATE PROCEDURE SampleProc3()
46 BEGIN
47 DECLARE ld DATE;
48 DECLARE li INT;
49 DECLARE lf DOUBLE;
50 DECLARE ls VARCHAR(32);
51 SET ld = NULL, li = NULL, lf = NULL, ls = NULL;
52 INSERT INTO t3 VALUES (ld, li, lf, ls);
53 INSERT INTO t3 (n, f, s) VALUES ((ld IS NULL), 1, "ld is null"),
54 ((li IS NULL), 1, "li is null"),
55 ((li = 0), NULL, "li = 0"),
56 ((lf IS NULL), 1, "lf is null"),
57 ((lf = 0), NULL, "lf = 0"),
58 ((ls IS NULL), 1, "ls is null");
59 END|
60 CREATE PROCEDURE SampleProc4()
61 BEGIN
62 DECLARE x INT;
63 SET x = 1;
64 WHILE x <= 2 DO
65 INSERT INTO t4(word, mean) VALUES('a','a mean');
66 SET x = x + 1;
67 END WHILE;
68 END|
69 CREATE FUNCTION append(s1 CHAR(8), s2 CHAR(8)) RETURNS CHAR(16)
70 RETURN concat(s1, s2)|
71 CREATE FUNCTION wt_avg(n1 INT, n2 INT, n3 INT, n4 INT)
72 RETURNS INT
73 DETERMINISTIC
74 BEGIN
75 DECLARE avg INT;
76 SET avg = (n1+n2+n3*2+n4*4)/8;
77 RETURN avg;
78 END|
79 CREATE FUNCTION fac(n INT UNSIGNED) RETURNS BIGINT UNSIGNED
80 BEGIN
81 DECLARE f BIGINT UNSIGNED DEFAULT 1;
82 WHILE n > 1 DO
83 SET f = f * n;
84 SET n = n - 1;
85 END WHILE;
86 RETURN f;
87 END|
88 # Triggers
89 CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW
90 BEGIN
91 if isnull(new.j) then
92 SET new.j:= new.i * 10;
93 END if;
94 END|
95 CREATE TRIGGER trg2 AFTER INSERT ON t2 FOR EACH ROW
96 BEGIN
97 UPDATE t1 SET i=new.id+i ;
98 END|
99 CREATE TRIGGER trg3 AFTER UPDATE ON t2 FOR EACH ROW
100 SET @change:= @change + new.id - old.id|
101 CREATE TRIGGER trg4 BEFORE DELETE ON t1 FOR EACH ROW
102 SET @del:= @del + 1|
103 CREATE TRIGGER trg5 AFTER DELETE ON t1 FOR EACH ROW
104 SET @del:= @del + 8 + old.j|
105 #####################
106 # Executing queries #
107 #####################
108 INSERT INTO t1 VALUES (10,20);
109 CALL SampleProc1(30,40,50);
110 SET @a=1;
111 SELECT @a;
112 @a
113 1
114 CALL SampleProc2("Jwalamukhi",34);
115 SELECT @a;
116 @a
117 1
118 CALL SampleProc3();
119 CALL SampleProc4();
120 SET @change=1;
121 SELECT @change;
122 @change
123 1
124 UPDATE t2 SET id=22 WHERE name="Jwalamukhi";
125 SELECT @change;
126 @change
127 -13
128 SET @del=1;
129 SELECT @del;
130 @del
131 1
132 DELETE FROM t1 WHERE i=76;
133 SELECT @del;
134 @del
135 60
136 SELECT wt_avg(1, 12, 1990, 1121990);
137 wt_avg(1, 12, 1990, 1121990)
138 561494
139 SELECT fac(5);
140 fac(5)
141 120
142 SELECT append("Bolly", "wood");
143 append("Bolly", "wood")
144 Bollywood
145 # Event
146 SET GLOBAL event_scheduler=ON;
147 CREATE TABLE table_t(a INT);
148 CREATE EVENT e1 ON SCHEDULE EVERY 2 SECOND ON COMPLETION NOT PRESERVE DO
149 BEGIN
150 INSERT INTO table_t VALUES(1);
151 END|
152 SELECT * FROM table_t;
153 a
154 1
155 SET GLOBAL event_scheduler=OFF;
156 SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_STATEMENTS
157 FROM performance_schema.events_statements_summary_by_program
158 WHERE OBJECT_SCHEMA='stored_programs' ORDER BY OBJECT_NAME;
159 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME COUNT_STAR COUNT_STATEMENTS
160 FUNCTION stored_programs append 1 1
161 EVENT stored_programs e1 1 1
162 FUNCTION stored_programs fac 1 19
163 PROCEDURE stored_programs sampleproc1 1 2
164 PROCEDURE stored_programs sampleproc2 1 5
165 PROCEDURE stored_programs sampleproc3 1 10
166 PROCEDURE stored_programs sampleproc4 1 11
167 TRIGGER stored_programs trg1 3 3
168 TRIGGER stored_programs trg2 1 1
169 TRIGGER stored_programs trg3 1 1
170 TRIGGER stored_programs trg4 1 1
171 TRIGGER stored_programs trg5 1 1
172 FUNCTION stored_programs wt_avg 1 3
173 SELECT EVENT_NAME, SQL_TEXT, CURRENT_SCHEMA, OBJECT_TYPE, OBJECT_SCHEMA,
174 OBJECT_NAME, NESTING_EVENT_TYPE, NESTING_EVENT_LEVEL FROM
175 performance_schema.events_statements_history_long WHERE
176 CURRENT_SCHEMA='stored_programs' AND
177 (SQL_TEXT not like '%count(*) = %' OR SQL_TEXT IS NULL)
178 ORDER BY OBJECT_NAME, NESTING_EVENT_LEVEL, SQL_TEXT;
179 EVENT_NAME SQL_TEXT CURRENT_SCHEMA OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME NESTING_EVENT_TYPE NESTING_EVENT_LEVEL
180 statement/scheduler/event NULL stored_programs NULL NULL NULL NULL 0
181 statement/sql/call_procedure CALL SampleProc1(30,40,50) stored_programs NULL NULL NULL NULL 0
182 statement/sql/call_procedure CALL SampleProc2("Jwalamukhi",34) stored_programs NULL NULL NULL NULL 0
183 statement/sql/call_procedure CALL SampleProc3() stored_programs NULL NULL NULL NULL 0
184 statement/sql/call_procedure CALL SampleProc4() stored_programs NULL NULL NULL NULL 0
185 statement/sql/create_event CREATE EVENT e1 ON SCHEDULE EVERY 2 SECOND ON COMPLETION NOT PRESERVE DO
186 BEGIN
187 INSERT INTO table_t VALUES(1);
188 END stored_programs NULL NULL NULL NULL 0
189 statement/sql/create_function CREATE FUNCTION append(s1 CHAR(8), s2 CHAR(8)) RETURNS CHAR(16)
190 RETURN concat(s1, s2) stored_programs NULL NULL NULL NULL 0
191 statement/sql/create_function CREATE FUNCTION fac(n INT UNSIGNED) RETURNS BIGINT UNSIGNED
192 BEGIN
193 DECLARE f BIGINT UNSIGNED DEFAULT 1;
194 WHILE n > 1 DO
195 SET f = f * n;
196 SET n = n - 1;
197 END WHILE;
198 RETURN f;
199 END stored_programs NULL NULL NULL NULL 0
200 statement/sql/create_function CREATE FUNCTION wt_avg(n1 INT, n2 INT, n3 INT, n4 INT)
201 RETURNS INT
202 DETERMINISTIC
203 BEGIN
204 DECLARE avg INT;
205 SET avg = (n1+n2+n3*2+n4*4)/8;
206 RETURN avg;
207 END stored_programs NULL NULL NULL NULL 0
208 statement/sql/create_procedure CREATE PROCEDURE SampleProc1(x1 INT, x2 INT, y INT)
209 BEGIN
210 INSERT INTO t1 VALUES (x1, y);
211 INSERT INTO t1 VALUES (x2, y);
212 END stored_programs NULL NULL NULL NULL 0
213 statement/sql/create_procedure CREATE PROCEDURE SampleProc2(x CHAR(16), y INT)
214 BEGIN
215 DECLARE z1, z2 INT;
216 SET z1 = y;
217 SET z2 = z1+2;
218 INSERT INTO t2 VALUES (x, z2);
219 END stored_programs NULL NULL NULL NULL 0
220 statement/sql/create_procedure CREATE PROCEDURE SampleProc3()
221 BEGIN
222 DECLARE ld DATE;
223 DECLARE li INT;
224 DECLARE lf DOUBLE;
225 DECLARE ls VARCHAR(32);
226 SET ld = NULL, li = NULL, lf = NULL, ls = NULL;
227 INSERT INTO t3 VALUES (ld, li, lf, ls);
228 INSERT INTO t3 (n, f, s) VALUES ((ld IS NULL), 1, "ld is null"),
229 ((li IS NULL), 1, "li is null"),
230 ((li = 0), NULL, "li = 0"),
231 ((lf IS NULL), 1, "lf is null"),
232 ((lf = 0), NULL, "lf = 0"),
233 ((ls IS NULL), 1, "ls is null");
234 END stored_programs NULL NULL NULL NULL 0
235 statement/sql/create_procedure CREATE PROCEDURE SampleProc4()
236 BEGIN
237 DECLARE x INT;
238 SET x = 1;
239 WHILE x <= 2 DO
240 INSERT INTO t4(word, mean) VALUES('a','a mean');
241 SET x = x + 1;
242 END WHILE;
243 END stored_programs NULL NULL NULL NULL 0
244 statement/sql/create_table CREATE TABLE t1(
245 i INT NOT NULL,
246 j INT
247 ) engine=innodb stored_programs NULL NULL NULL NULL 0
248 statement/sql/create_table CREATE TABLE t2(
249 name CHAR(16) NOT NULL DEFAULT '',
250 id INT NOT NULL
251 ) engine=innodb stored_programs NULL NULL NULL NULL 0
252 statement/sql/create_table CREATE TABLE t3(
253 d DATE,
254 n INT,
255 f DOUBLE,
256 s VARCHAR(32)
257 ) stored_programs NULL NULL NULL NULL 0
258 statement/sql/create_table CREATE TABLE t4(
259 `k` int(10) unsigned NOT NULL AUTO_INCREMENT,
260 `word` varchar(100) NOT NULL,
261 `mean` varchar(300) NOT NULL,
262 PRIMARY KEY (`k`)
263 ) stored_programs NULL NULL NULL NULL 0
264 statement/sql/create_table CREATE TABLE table_t(a INT) stored_programs NULL NULL NULL NULL 0
265 statement/sql/create_trigger CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW
266 BEGIN
267 if isnull(new.j) then
268 SET new.j:= new.i * 10;
269 END if;
270 END stored_programs NULL NULL NULL NULL 0
271 statement/sql/create_trigger CREATE TRIGGER trg2 AFTER INSERT ON t2 FOR EACH ROW
272 BEGIN
273 UPDATE t1 SET i=new.id+i ;
274 END stored_programs NULL NULL NULL NULL 0
275 statement/sql/create_trigger CREATE TRIGGER trg3 AFTER UPDATE ON t2 FOR EACH ROW
276 SET @change:= @change + new.id - old.id stored_programs NULL NULL NULL NULL 0
277 statement/sql/create_trigger CREATE TRIGGER trg4 BEFORE DELETE ON t1 FOR EACH ROW
278 SET @del:= @del + 1 stored_programs NULL NULL NULL NULL 0
279 statement/sql/create_trigger CREATE TRIGGER trg5 AFTER DELETE ON t1 FOR EACH ROW
280 SET @del:= @del + 8 + old.j stored_programs NULL NULL NULL NULL 0
281 statement/sql/delete DELETE FROM t1 WHERE i=76 stored_programs NULL NULL NULL NULL 0
282 statement/sql/insert INSERT INTO t1 VALUES (10,20) stored_programs NULL NULL NULL NULL 0
283 statement/sql/select SELECT * FROM table_t stored_programs NULL NULL NULL NULL 0
284 statement/sql/select SELECT 5 * 10 stored_programs NULL NULL NULL NULL 0
285 statement/sql/select SELECT @a stored_programs NULL NULL NULL NULL 0
286 statement/sql/select SELECT @a stored_programs NULL NULL NULL NULL 0
287 statement/sql/select SELECT @change stored_programs NULL NULL NULL NULL 0
288 statement/sql/select SELECT @change stored_programs NULL NULL NULL NULL 0
289 statement/sql/select SELECT @del stored_programs NULL NULL NULL NULL 0
290 statement/sql/select SELECT @del stored_programs NULL NULL NULL NULL 0
291 statement/sql/select SELECT append("Bolly", "wood") stored_programs NULL NULL NULL NULL 0
292 statement/sql/select SELECT fac(5) stored_programs NULL NULL NULL NULL 0
293 statement/sql/select SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_STATEMENTS
294 FROM performance_schema.events_statements_summary_by_program
295 WHERE OBJECT_SCHEMA='stored_programs' ORDER BY OBJECT_NAME stored_programs NULL NULL NULL NULL 0
296 statement/sql/select SELECT wt_avg(1, 12, 1990, 1121990) stored_programs NULL NULL NULL NULL 0
297 statement/sql/set_option SET @a=1 stored_programs NULL NULL NULL NULL 0
298 statement/sql/set_option SET @change=1 stored_programs NULL NULL NULL NULL 0
299 statement/sql/set_option SET @del=1 stored_programs NULL NULL NULL NULL 0
300 statement/sql/set_option SET GLOBAL event_scheduler=OFF stored_programs NULL NULL NULL NULL 0
301 statement/sql/set_option SET GLOBAL event_scheduler=ON stored_programs NULL NULL NULL NULL 0
302 statement/sql/update UPDATE t2 SET id=22 WHERE name="Jwalamukhi" stored_programs NULL NULL NULL NULL 0
303 statement/sp/freturn NULL stored_programs FUNCTION stored_programs append STATEMENT 1
304 statement/sp/stmt INSERT INTO table_t VALUES(1) stored_programs EVENT stored_programs e1 STATEMENT 1
305 statement/sp/set NULL stored_programs FUNCTION stored_programs fac STATEMENT 1
306 statement/sp/jump_if_not NULL stored_programs FUNCTION stored_programs fac STATEMENT 1
307 statement/sp/set NULL stored_programs FUNCTION stored_programs fac STATEMENT 1
308 statement/sp/set NULL stored_programs FUNCTION stored_programs fac STATEMENT 1
309 statement/sp/jump NULL stored_programs FUNCTION stored_programs fac STATEMENT 1
310 statement/sp/jump_if_not NULL stored_programs FUNCTION stored_programs fac STATEMENT 1
311 statement/sp/set NULL stored_programs FUNCTION stored_programs fac STATEMENT 1
312 statement/sp/set NULL stored_programs FUNCTION stored_programs fac STATEMENT 1
313 statement/sp/jump NULL stored_programs FUNCTION stored_programs fac STATEMENT 1
314 statement/sp/jump_if_not NULL stored_programs FUNCTION stored_programs fac STATEMENT 1
315 statement/sp/set NULL stored_programs FUNCTION stored_programs fac STATEMENT 1
316 statement/sp/set NULL stored_programs FUNCTION stored_programs fac STATEMENT 1
317 statement/sp/jump NULL stored_programs FUNCTION stored_programs fac STATEMENT 1
318 statement/sp/jump_if_not NULL stored_programs FUNCTION stored_programs fac STATEMENT 1
319 statement/sp/set NULL stored_programs FUNCTION stored_programs fac STATEMENT 1
320 statement/sp/set NULL stored_programs FUNCTION stored_programs fac STATEMENT 1
321 statement/sp/jump NULL stored_programs FUNCTION stored_programs fac STATEMENT 1
322 statement/sp/jump_if_not NULL stored_programs FUNCTION stored_programs fac STATEMENT 1
323 statement/sp/freturn NULL stored_programs FUNCTION stored_programs fac STATEMENT 1
324 statement/sp/stmt INSERT INTO t1 VALUES (x1, y) stored_programs PROCEDURE stored_programs sampleproc1 STATEMENT 1
325 statement/sp/stmt INSERT INTO t1 VALUES (x2, y) stored_programs PROCEDURE stored_programs sampleproc1 STATEMENT 1
326 statement/sp/set NULL stored_programs PROCEDURE stored_programs sampleproc2 STATEMENT 1
327 statement/sp/set NULL stored_programs PROCEDURE stored_programs sampleproc2 STATEMENT 1
328 statement/sp/set NULL stored_programs PROCEDURE stored_programs sampleproc2 STATEMENT 1
329 statement/sp/set NULL stored_programs PROCEDURE stored_programs sampleproc2 STATEMENT 1
330 statement/sp/stmt INSERT INTO t2 VALUES (x, z2) stored_programs PROCEDURE stored_programs sampleproc2 STATEMENT 1
331 statement/sp/set NULL stored_programs PROCEDURE stored_programs sampleproc3 STATEMENT 1
332 statement/sp/set NULL stored_programs PROCEDURE stored_programs sampleproc3 STATEMENT 1
333 statement/sp/set NULL stored_programs PROCEDURE stored_programs sampleproc3 STATEMENT 1
334 statement/sp/set NULL stored_programs PROCEDURE stored_programs sampleproc3 STATEMENT 1
335 statement/sp/set NULL stored_programs PROCEDURE stored_programs sampleproc3 STATEMENT 1
336 statement/sp/set NULL stored_programs PROCEDURE stored_programs sampleproc3 STATEMENT 1
337 statement/sp/set NULL stored_programs PROCEDURE stored_programs sampleproc3 STATEMENT 1
338 statement/sp/set NULL stored_programs PROCEDURE stored_programs sampleproc3 STATEMENT 1
339 statement/sp/stmt INSERT INTO t3 (n, f, s) VALUES ((ld IS NULL), 1, "ld is null"),
340 ((li IS NULL), 1, "li is null"),
341 ((li = 0), NULL, "li = 0"),
342 ((lf IS NULL), 1, "lf is null"),
343 ((lf = 0), NULL, "lf = 0"),
344 ((ls IS NULL), 1, "ls is null") stored_programs PROCEDURE stored_programs sampleproc3 STATEMENT 1
345 statement/sp/stmt INSERT INTO t3 VALUES (ld, li, lf, ls) stored_programs PROCEDURE stored_programs sampleproc3 STATEMENT 1
346 statement/sp/set NULL stored_programs PROCEDURE stored_programs sampleproc4 STATEMENT 1
347 statement/sp/set NULL stored_programs PROCEDURE stored_programs sampleproc4 STATEMENT 1
348 statement/sp/jump_if_not NULL stored_programs PROCEDURE stored_programs sampleproc4 STATEMENT 1
349 statement/sp/set NULL stored_programs PROCEDURE stored_programs sampleproc4 STATEMENT 1
350 statement/sp/jump NULL stored_programs PROCEDURE stored_programs sampleproc4 STATEMENT 1
351 statement/sp/jump_if_not NULL stored_programs PROCEDURE stored_programs sampleproc4 STATEMENT 1
352 statement/sp/set NULL stored_programs PROCEDURE stored_programs sampleproc4 STATEMENT 1
353 statement/sp/jump NULL stored_programs PROCEDURE stored_programs sampleproc4 STATEMENT 1
354 statement/sp/jump_if_not NULL stored_programs PROCEDURE stored_programs sampleproc4 STATEMENT 1
355 statement/sp/stmt INSERT INTO t4(word, mean) VALUES('a','a mean') stored_programs PROCEDURE stored_programs sampleproc4 STATEMENT 1
356 statement/sp/stmt INSERT INTO t4(word, mean) VALUES('a','a mean') stored_programs PROCEDURE stored_programs sampleproc4 STATEMENT 1
357 statement/sp/jump_if_not NULL stored_programs TRIGGER stored_programs trg1 TRANSACTION 1
358 statement/sp/jump_if_not NULL stored_programs TRIGGER stored_programs trg1 TRANSACTION 2
359 statement/sp/jump_if_not NULL stored_programs TRIGGER stored_programs trg1 TRANSACTION 2
360 statement/sp/stmt UPDATE t1 SET i=new.id+i stored_programs TRIGGER stored_programs trg2 TRANSACTION 2
361 statement/sp/stmt SET @change:= @change + new.id - old.id stored_programs TRIGGER stored_programs trg3 TRANSACTION 1
362 statement/sp/stmt SET @del:= @del + 1 stored_programs TRIGGER stored_programs trg4 TRANSACTION 1
363 statement/sp/stmt SET @del:= @del + 8 + old.j stored_programs TRIGGER stored_programs trg5 TRANSACTION 1
364 statement/sp/set NULL stored_programs FUNCTION stored_programs wt_avg STATEMENT 1
365 statement/sp/set NULL stored_programs FUNCTION stored_programs wt_avg STATEMENT 1
366 statement/sp/freturn NULL stored_programs FUNCTION stored_programs wt_avg STATEMENT 1
367 # clean -up
368 TRUNCATE TABLE performance_schema.events_statements_summary_by_program;
369 TRUNCATE TABLE performance_schema.events_statements_history_long;
370 SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_STATEMENTS
371 FROM performance_schema.events_statements_summary_by_program
372 WHERE OBJECT_SCHEMA='stored_programs' ORDER BY OBJECT_NAME;
373 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME COUNT_STAR COUNT_STATEMENTS
374 FUNCTION stored_programs append 0 0
375 EVENT stored_programs e1 0 0
376 FUNCTION stored_programs fac 0 0
377 PROCEDURE stored_programs sampleproc1 0 0
378 PROCEDURE stored_programs sampleproc2 0 0
379 PROCEDURE stored_programs sampleproc3 0 0
380 PROCEDURE stored_programs sampleproc4 0 0
381 TRIGGER stored_programs trg1 0 0
382 TRIGGER stored_programs trg2 0 0
383 TRIGGER stored_programs trg3 0 0
384 TRIGGER stored_programs trg4 0 0
385 TRIGGER stored_programs trg5 0 0
386 FUNCTION stored_programs wt_avg 0 0
387 DROP PROCEDURE SampleProc1;
388 DROP PROCEDURE SampleProc2;
389 DROP PROCEDURE SampleProc3;
390 DROP PROCEDURE SampleProc4;
391 DROP FUNCTION wt_avg;
392 DROP FUNCTION fac;
393 DROP FUNCTION append;
394 DROP TRIGGER trg1;
395 DROP TRIGGER trg2;
396 DROP TRIGGER trg3;
397 DROP TRIGGER trg4;
398 DROP TRIGGER trg5;
399 DROP EVENT IF EXISTS e1;
400 DROP TABLE t1;
401 DROP TABLE t2;
402 DROP TABLE t3;
403 DROP TABLE t4;
404 DROP TABLE table_t;
405 DROP DATABASE stored_programs;
406 SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_STATEMENTS
407 FROM performance_schema.events_statements_summary_by_program
408 WHERE OBJECT_SCHEMA='stored_programs' ORDER BY OBJECT_NAME;
409 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME COUNT_STAR COUNT_STATEMENTS
410 # SET-UP
411 CREATE DATABASE stored_programs;
412 USE stored_programs;
413 CREATE TABLE t1(
414 i INT NOT NULL,
415 j INT
416 ) engine=innodb;
417 CREATE TABLE t2(
418 name CHAR(16) NOT NULL DEFAULT '',
419 id INT NOT NULL
420 ) engine=innodb;
421 CREATE TABLE t3(
422 d DATE,
423 n INT,
424 f DOUBLE,
425 s VARCHAR(32)
426 );
427 CREATE TABLE t4(
428 `k` int(10) unsigned NOT NULL AUTO_INCREMENT,
429 `word` varchar(100) NOT NULL,
430 `mean` varchar(300) NOT NULL,
431 PRIMARY KEY (`k`)
432 );
433 ############################
434 # Creating Stored Programs #
435 ############################
436 # Stored Routine ( Procedure & Function )
437 CREATE PROCEDURE SampleProc1(x1 INT, x2 INT, y INT)
438 BEGIN
439 INSERT INTO t1 VALUES (x1, y);
440 INSERT INTO t1 VALUES (x2, y);
441 END|
442 CREATE PROCEDURE SampleProc2(x CHAR(16), y INT)
443 BEGIN
444 DECLARE z1, z2 INT;
445 SET z1 = y;
446 SET z2 = z1+2;
447 INSERT INTO t2 VALUES (x, z2);
448 END|
449 CREATE PROCEDURE SampleProc3()
450 BEGIN
451 DECLARE ld DATE;
452 DECLARE li INT;
453 DECLARE lf DOUBLE;
454 DECLARE ls VARCHAR(32);
455 SET ld = NULL, li = NULL, lf = NULL, ls = NULL;
456 INSERT INTO t3 VALUES (ld, li, lf, ls);
457 INSERT INTO t3 (n, f, s) VALUES ((ld IS NULL), 1, "ld is null"),
458 ((li IS NULL), 1, "li is null"),
459 ((li = 0), NULL, "li = 0"),
460 ((lf IS NULL), 1, "lf is null"),
461 ((lf = 0), NULL, "lf = 0"),
462 ((ls IS NULL), 1, "ls is null");
463 END|
464 CREATE PROCEDURE SampleProc4()
465 BEGIN
466 DECLARE x INT;
467 SET x = 1;
468 WHILE x <= 2 DO
469 INSERT INTO t4(word, mean) VALUES('a','a mean');
470 SET x = x + 1;
471 END WHILE;
472 END|
473 CREATE FUNCTION append(s1 CHAR(8), s2 CHAR(8)) RETURNS CHAR(16)
474 RETURN concat(s1, s2)|
475 CREATE FUNCTION wt_avg(n1 INT, n2 INT, n3 INT, n4 INT)
476 RETURNS INT
477 DETERMINISTIC
478 BEGIN
479 DECLARE avg INT;
480 SET avg = (n1+n2+n3*2+n4*4)/8;
481 RETURN avg;
482 END|
483 CREATE FUNCTION fac(n INT UNSIGNED) RETURNS BIGINT UNSIGNED
484 BEGIN
485 DECLARE f BIGINT UNSIGNED DEFAULT 1;
486 WHILE n > 1 DO
487 SET f = f * n;
488 SET n = n - 1;
489 END WHILE;
490 RETURN f;
491 END|
492 # Triggers
493 CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW
494 BEGIN
495 if isnull(new.j) then
496 SET new.j:= new.i * 10;
497 END if;
498 END|
499 CREATE TRIGGER trg2 AFTER INSERT ON t2 FOR EACH ROW
500 BEGIN
501 UPDATE t1 SET i=new.id+i ;
502 END|
503 CREATE TRIGGER trg3 AFTER UPDATE ON t2 FOR EACH ROW
504 SET @change:= @change + new.id - old.id|
505 CREATE TRIGGER trg4 BEFORE DELETE ON t1 FOR EACH ROW
506 SET @del:= @del + 1|
507 CREATE TRIGGER trg5 AFTER DELETE ON t1 FOR EACH ROW
508 SET @del:= @del + 8 + old.j|
509 update performance_schema.setup_instruments set enabled='YES', timed='NO'
510 where name like "statement/sp/%" order by name;
511 TRUNCATE TABLE performance_schema.events_statements_summary_by_program;
512 TRUNCATE TABLE performance_schema.events_statements_history_long;
513 #####################
514 # Executing queries #
515 #####################
516 INSERT INTO t1 VALUES (10,20);
517 CALL SampleProc1(30,40,50);
518 SET @a=1;
519 SELECT @a;
520 @a
521 1
522 CALL SampleProc2("Jwalamukhi",34);
523 SELECT @a;
524 @a
525 1
526 CALL SampleProc3();
527 CALL SampleProc4();
528 SET @change=1;
529 SELECT @change;
530 @change
531 1
532 UPDATE t2 SET id=22 WHERE name="Jwalamukhi";
533 SELECT @change;
534 @change
535 -13
536 SET @del=1;
537 SELECT @del;
538 @del
539 1
540 DELETE FROM t1 WHERE i=76;
541 SELECT @del;
542 @del
543 60
544 SELECT wt_avg(1, 12, 1990, 1121990);
545 wt_avg(1, 12, 1990, 1121990)
546 561494
547 SELECT fac(5);
548 fac(5)
549 120
550 SELECT append("Bolly", "wood");
551 append("Bolly", "wood")
552 Bollywood
553 # Event
554 SET GLOBAL event_scheduler=ON;
555 CREATE TABLE table_t(a INT);
556 CREATE EVENT e1 ON SCHEDULE EVERY 2 SECOND ON COMPLETION NOT PRESERVE DO
557 BEGIN
558 INSERT INTO table_t VALUES(1);
559 END|
560 SELECT * FROM table_t;
561 a
562 1
563 SET GLOBAL event_scheduler=OFF;
564 SELECT EVENT_NAME, TIMER_START, TIMER_END, TIMER_WAIT FROM
565 performance_schema.events_statements_history_long WHERE
566 CURRENT_SCHEMA='stored_programs' AND EVENT_NAME like "statement/sp/%";
567 EVENT_NAME TIMER_START TIMER_END TIMER_WAIT
568 statement/sp/jump_if_not NULL NULL NULL
569 statement/sp/jump_if_not NULL NULL NULL
570 statement/sp/stmt NULL NULL NULL
571 statement/sp/jump_if_not NULL NULL NULL
572 statement/sp/stmt NULL NULL NULL
573 statement/sp/set NULL NULL NULL
574 statement/sp/set NULL NULL NULL
575 statement/sp/set NULL NULL NULL
576 statement/sp/set NULL NULL NULL
577 statement/sp/stmt NULL NULL NULL
578 statement/sp/stmt NULL NULL NULL
579 statement/sp/set NULL NULL NULL
580 statement/sp/set NULL NULL NULL
581 statement/sp/set NULL NULL NULL
582 statement/sp/set NULL NULL NULL
583 statement/sp/set NULL NULL NULL
584 statement/sp/set NULL NULL NULL
585 statement/sp/set NULL NULL NULL
586 statement/sp/set NULL NULL NULL
587 statement/sp/stmt NULL NULL NULL
588 statement/sp/stmt NULL NULL NULL
589 statement/sp/set NULL NULL NULL
590 statement/sp/set NULL NULL NULL
591 statement/sp/jump_if_not NULL NULL NULL
592 statement/sp/stmt NULL NULL NULL
593 statement/sp/set NULL NULL NULL
594 statement/sp/jump NULL NULL NULL
595 statement/sp/jump_if_not NULL NULL NULL
596 statement/sp/stmt NULL NULL NULL
597 statement/sp/set NULL NULL NULL
598 statement/sp/jump NULL NULL NULL
599 statement/sp/jump_if_not NULL NULL NULL
600 statement/sp/stmt NULL NULL NULL
601 statement/sp/stmt NULL NULL NULL
602 statement/sp/stmt NULL NULL NULL
603 statement/sp/set NULL NULL NULL
604 statement/sp/set NULL NULL NULL
605 statement/sp/freturn NULL NULL NULL
606 statement/sp/set NULL NULL NULL
607 statement/sp/jump_if_not NULL NULL NULL
608 statement/sp/set NULL NULL NULL
609 statement/sp/set NULL NULL NULL
610 statement/sp/jump NULL NULL NULL
611 statement/sp/jump_if_not NULL NULL NULL
612 statement/sp/set NULL NULL NULL
613 statement/sp/set NULL NULL NULL
614 statement/sp/jump NULL NULL NULL
615 statement/sp/jump_if_not NULL NULL NULL
616 statement/sp/set NULL NULL NULL
617 statement/sp/set NULL NULL NULL
618 statement/sp/jump NULL NULL NULL
619 statement/sp/jump_if_not NULL NULL NULL
620 statement/sp/set NULL NULL NULL
621 statement/sp/set NULL NULL NULL
622 statement/sp/jump NULL NULL NULL
623 statement/sp/jump_if_not NULL NULL NULL
624 statement/sp/freturn NULL NULL NULL
625 statement/sp/freturn NULL NULL NULL
626 statement/sp/stmt NULL NULL NULL
627 update performance_schema.setup_instruments set enabled='YES', timed='YES'
628 where name like "statement/sp/%" order by name;
629 TRUNCATE TABLE performance_schema.events_statements_summary_by_program;
630 TRUNCATE TABLE performance_schema.events_statements_history_long;
631 DROP PROCEDURE SampleProc1;
632 DROP PROCEDURE SampleProc2;
633 DROP PROCEDURE SampleProc3;
634 DROP PROCEDURE SampleProc4;
635 DROP FUNCTION wt_avg;
636 DROP FUNCTION fac;
637 DROP FUNCTION append;
638 DROP TRIGGER trg1;
639 DROP TRIGGER trg2;
640 DROP TRIGGER trg3;
641 DROP TRIGGER trg4;
642 DROP TRIGGER trg5;
643 DROP EVENT IF EXISTS e1;
644 DROP TABLE t1;
645 DROP TABLE t2;
646 DROP TABLE t3;
647 DROP TABLE t4;
648 DROP TABLE table_t;
649 DROP DATABASE stored_programs;
650 # set-up
651 CREATE DATABASE sp;
652 USE sp;
653 CREATE TABLE t1(
654 a INT,
655 b INT
656 );
657 # let the creation of the following stored programs fail
658 CREATE PROCEDURE fail1(IN a INT OUT x CHAR(16))
659 SET a=1;
660 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'OUT x CHAR(16))
661 SET a=1' at line 1
662 CREATE FUNCTION fail2(a INT , b INT) RETURNS INT
663 x=SELECT COUNT(*) FROM t;
664 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '=SELECT COUNT(*) FROM t' at line 2
665 CREATE EVENT fail3 SCHEDULE EVERY MICROSECOND DO
666 DROP TABLE t;
667 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SCHEDULE EVERY MICROSECOND DO
668 DROP TABLE t' at line 1
669 # the below query on PS table doesn't show any rows
670 # as the creation of stored programs failed
671 SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_STATEMENTS
672 FROM performance_schema.events_statements_summary_by_program
673 WHERE OBJECT_SCHEMA='sp';
674 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME COUNT_STAR COUNT_STATEMENTS
675 # create few stored programs
676 CREATE PROCEDURE p(x1 INT, x2 INT)
677 BEGIN
678 INSERT INTO t1 VALUES (x1, x2);
679 END|
680 CREATE FUNCTION f(y1 INT, y2 INT) RETURNS INT
681 RETURN y1+y2;
682 CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW
683 SET @neg=-1;
684 # execute the created stored programs such that they fail.
685 CALL p(7);
686 ERROR 42000: Incorrect number of arguments for PROCEDURE sp.p; expected 2, got 1
687 SELECT f("add",1,3);
688 ERROR 42000: Incorrect number of arguments for FUNCTION sp.f; expected 2, got 3
689 INSERT INTO t1;
690 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
691 # the below query on PS table doesn't expose any statistics as
692 # execution of the created stored porgrams failed.
693 SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, COUNT_STAR, COUNT_STATEMENTS
694 FROM performance_schema.events_statements_summary_by_program
695 WHERE OBJECT_SCHEMA='sp';
696 OBJECT_TYPE OBJECT_SCHEMA OBJECT_NAME COUNT_STAR COUNT_STATEMENTS
697 PROCEDURE sp p 0 0
698 FUNCTION sp f 0 0
699 #clean-up
700 DROP PROCEDURE p;
701 DROP FUNCTION f;
702 DROP TRIGGER trg;
703 DROP TABLE t1;
704 DROP DATABASE sp;