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