Debian Codegraph @

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

raw
1 #
2 # SET-UP - Creation of various non-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
10 CREATE DATABASE stored_programs;
11 USE stored_programs;
12
13 CREATE TABLE t1(
14 i INT NOT NULL,
15 j INT
16 );
17
18 CREATE TABLE t2(
19 name CHAR(16) NOT NULL DEFAULT '',
20 id INT NOT NULL
21 );
22
23 CREATE TABLE t3(
24 d DATE,
25 n INT,
26 f DOUBLE,
27 s VARCHAR(32)
28 );
29
30 CREATE TABLE t4(
31 `k` int(10) unsigned NOT NULL AUTO_INCREMENT,
32 `word` varchar(100) NOT NULL,
33 `mean` varchar(300) NOT NULL,
34 PRIMARY KEY (`k`)
35 );
36
37
38 --echo ############################
39 --echo # Creating Stored Programs #
40 --echo ############################
41
42 --echo # Stored Routine ( Procedure & Function )
43
44 DELIMITER |;
45 CREATE PROCEDURE SampleProc1(x1 INT, x2 INT, y INT)
46 BEGIN
47 INSERT INTO t1 VALUES (x1, y);
48 INSERT INTO t1 VALUES (x2, y);
49 END|
50
51 CREATE PROCEDURE SampleProc2(x CHAR(16), y INT)
52 BEGIN
53 DECLARE z1, z2 INT;
54 SET z1 = y;
55 SET z2 = z1+2;
56 INSERT INTO t2 VALUES (x, z2);
57 END|
58
59 CREATE PROCEDURE SampleProc3()
60 BEGIN
61 DECLARE ld DATE;
62 DECLARE li INT;
63 DECLARE lf DOUBLE;
64 DECLARE ls VARCHAR(32);
65
66 SET ld = NULL, li = NULL, lf = NULL, ls = NULL;
67 INSERT INTO t3 VALUES (ld, li, lf, ls);
68
69 INSERT INTO t3 (n, f, s) VALUES ((ld IS NULL), 1, "ld is null"),
70 ((li IS NULL), 1, "li is null"),
71 ((li = 0), NULL, "li = 0"),
72 ((lf IS NULL), 1, "lf is null"),
73 ((lf = 0), NULL, "lf = 0"),
74 ((ls IS NULL), 1, "ls is null");
75 END|
76
77
78 CREATE PROCEDURE SampleProc4()
79 BEGIN
80 DECLARE x INT;
81 SET x = 1;
82 WHILE x <= 2 DO
83 INSERT INTO t4(word, mean) VALUES('a','a mean');
84 SET x = x + 1;
85 END WHILE;
86 END|
87
88 CREATE FUNCTION append(s1 CHAR(8), s2 CHAR(8)) RETURNS CHAR(16)
89 RETURN concat(s1, s2)|
90
91 CREATE FUNCTION wt_avg(n1 INT, n2 INT, n3 INT, n4 INT)
92 RETURNS INT
93 DETERMINISTIC
94 BEGIN
95 DECLARE avg INT;
96 SET avg = (n1+n2+n3*2+n4*4)/8;
97 RETURN avg;
98 END|
99
100 CREATE FUNCTION fac(n INT UNSIGNED) RETURNS BIGINT UNSIGNED
101 BEGIN
102 DECLARE f BIGINT UNSIGNED DEFAULT 1;
103 WHILE n > 1 DO
104 SET f = f * n;
105 SET n = n - 1;
106 END WHILE;
107 RETURN f;
108 END|
109
110 --echo # Triggers
111
112 # INSERT triggers
113 CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW
114 BEGIN
115 if isnull(new.j) then
116 SET new.j:= new.i * 10;
117 END if;
118 END|
119
120 CREATE TRIGGER trg2 AFTER INSERT ON t2 FOR EACH ROW
121 BEGIN
122 UPDATE t1 SET i=new.id+i ;
123 END|
124
125 # UPDATE trigger
126 CREATE TRIGGER trg3 AFTER UPDATE ON t2 FOR EACH ROW
127 SET @change:= @change + new.id - old.id|
128
129 # DELETE triggers
130 CREATE TRIGGER trg4 BEFORE DELETE ON t1 FOR EACH ROW
131 SET @del:= @del + 1|
132
133 CREATE TRIGGER trg5 AFTER DELETE ON t1 FOR EACH ROW
134 SET @del:= @del + 8 + old.j|
135
136 DELIMITER ;|