Debian Codegraph @

mysql-test/suite/perfschema/include/program_nested_setup.inc

raw
1 #
2 # SET-UP - Creation of various nested stored programs
3 #
4
5 # Test requires: sp-protocol/ps-protocol/view-protocol/cursor-protocol disabled
6 --source include/no_protocol.inc
7
8 --echo # SET-UP
9 CREATE DATABASE nested_sp;
10 USE nested_sp;
11
12 CREATE TABLE t1(
13 id CHAR(16) NOT NULL DEFAULT '',
14 data INT NOT NULL
15 );
16
17 CREATE TABLE t2(
18 n INT UNSIGNED NOT NULL,
19 f BIGINT UNSIGNED
20 );
21
22 --echo ############################
23 --echo # Creating Stored Programs #
24 --echo ############################
25 DELIMITER |;
26
27 # Nested Stored Procedure - 1
28 CREATE PROCEDURE c1(x INT)
29 CALL c2("c", x)|
30 CREATE PROCEDURE c2(s CHAR(16), x INT)
31 CALL c3(x, s)|
32 CREATE PROCEDURE c3(x INT, s CHAR(16))
33 CALL c4("level", x, s)|
34 CREATE PROCEDURE c4(l CHAR(8), x INT, s CHAR(16))
35 INSERT INTO t1 VALUES (concat(l,s), x)|
36
37 # Nested Stored Procedure - 2
38 CREATE PROCEDURE iotest(x1 CHAR(16), x2 CHAR(16), y INT)
39 BEGIN
40 CALL inc2(x2, y);
41 INSERT INTO t1 VALUES (x1, y);
42 END|
43 CREATE PROCEDURE inc2(x CHAR(16), y INT)
44 BEGIN
45 CALL inc(y);
46 INSERT INTO t1 VALUES (x, y);
47 END|
48 CREATE PROCEDURE inc(inout io INT)
49 SET io = io + 1|
50
51 # Nested Stored Function
52 CREATE FUNCTION mul(x INT, y INT) RETURNS INT
53 RETURN x*y|
54 CREATE FUNCTION inc(i INT) RETURNS INT
55 RETURN i+1|
56 CREATE FUNCTION fac(n INT UNSIGNED) RETURNS BIGINT UNSIGNED
57 BEGIN
58 DECLARE f BIGINT UNSIGNED DEFAULT 1;
59 WHILE n > 1 DO
60 SET f = f * n;
61 SET n = n - 1;
62 END WHILE;
63 RETURN f;
64 END|
65 CREATE FUNCTION fun(i INT, u INT UNSIGNED) RETURNS DOUBLE
66 RETURN mul(inc(i), fac(u))|
67 #
68 # A nested stored program
69 #
70 CREATE PROCEDURE ifac(n INT UNSIGNED)
71 BEGIN
72 DECLARE i BIGINT UNSIGNED DEFAULT 1;
73
74 IF n > 20 THEN
75 SET n = 20; # bigint overflow otherwise
76 END IF;
77 WHILE i <= n DO
78 BEGIN
79 INSERT INTO t2 VALUES (i, fac(i));
80 SET i = i + 1;
81 END;
82 END WHILE;
83 END|
84 CREATE TRIGGER trg AFTER INSERT ON t1 FOR EACH ROW
85 CALL ifac(10)|
86
87 DELIMITER ;|
88