Debian Codegraph @

mysql-test/suite/x/include/stored_procedures.inc

raw
1 --disable_result_log
2 --disable_query_log
3
4 --echo #
5 --echo ## Setup tests: tables and stored procedures
6 ## CURRENT CONTENT OF stored_procedure.inc##
7
8 eval CREATE SCHEMA IF NOT EXISTS $STORED_PROC_DB_NAME;
9 eval USE $STORED_PROC_DB_NAME;
10
11 DELIMITER //;
12
13 CREATE PROCEDURE `recreate_tables_for_sp`()
14 BEGIN
15 CREATE TABLE IF NOT EXISTS tab_1 (c1t1 INT UNSIGNED, c2t1 VARCHAR(10));
16 TRUNCATE TABLE tab_1;
17 INSERT INTO tab_1 VALUES (1, "ONE"), (2, "TWO"), (3, "THREE");
18
19 CREATE TABLE IF NOT EXISTS tab_2 (c1t2 DATETIME);
20 TRUNCATE TABLE tab_2;
21 INSERT INTO tab_2 VALUES ('2006-07-03'), ('2013-06-15'), ('2012-11-24'), ('1979-11-24');
22 END //
23
24 CALL `recreate_tables_for_sp`() //
25
26 CREATE PROCEDURE `empty`()
27 BEGIN
28 END //
29
30 CREATE PROCEDURE one_results_set()
31 BEGIN
32 SELECT * FROM tab_1;
33 END //
34
35 CREATE PROCEDURE two_resultsets()
36 BEGIN
37 SELECT c1t1 FROM tab_1 LIMIT 1;
38 SELECT c1t2 FROM tab_2 LIMIT 1;
39 END //
40
41
42 CREATE PROCEDURE three_results_sets()
43 BEGIN
44 SELECT * FROM tab_1;
45 SELECT * FROM tab_2;
46
47 CALL one_results_set();
48 END //
49
50 CREATE PROCEDURE out_param_no_select(IN val INT, OUT res INT)
51 BEGIN
52 SET res = 2 * val;
53 END //
54
55 CREATE PROCEDURE out_params_with_select(OUT min_val DATETIME, OUT max_val DATETIME)
56 BEGIN
57 SELECT MIN(c1t2) INTO min_val FROM tab_2;
58 SELECT MAX(c1t2) INTO max_val FROM tab_2;
59
60 SELECT * FROM tab_1;
61 END //
62
63 CREATE PROCEDURE update_no_select()
64 BEGIN
65
66 UPDATE tab_1 SET c1t1 = c1t1 + 1 WHERE c1t1 > 1;
67 END //
68
69 CREATE PROCEDURE update_and_select()
70 BEGIN
71
72 UPDATE tab_1 SET c1t1 = c1t1 + 2 WHERE c1t1 > 2;
73 SELECT * FROM tab_1;
74 END //
75
76 CREATE PROCEDURE update_and_out_param(OUT max_val INT)
77 BEGIN
78
79 UPDATE tab_1 SET c1t1 = c1t1 + 2 ORDER BY c1t1 LIMIT 1;
80 SELECT MAX(c1t1) into max_val FROM tab_1;
81 END //
82
83 CREATE PROCEDURE update_and_out_param_and_select(OUT max_val INT)
84 BEGIN
85 UPDATE tab_1 SET c1t1 = c1t1 * 2 ORDER BY c1t1 DESC LIMIT 1;
86 SELECT MAX(c1t1) into max_val FROM tab_1;
87 SELECT AVG(c1t1) FROM tab_1;
88 END //
89
90 CREATE PROCEDURE insert_no_select()
91 BEGIN
92 INSERT INTO tab_1 VALUES (11, "s11"), (12, "s12"), (13, "s13");
93 END //
94
95 CREATE PROCEDURE insert_and_select()
96 BEGIN
97 INSERT INTO tab_1 VALUES (111, "s111"), (112, "s112"), (13, "s113");
98 SELECT * FROM tab_1;
99 END //
100
101 CREATE PROCEDURE insert_and_out_param(OUT max_val INT)
102 BEGIN
103
104 INSERT INTO tab_1 VALUES(9999, "BIG ONE");
105 SELECT MAX(c1t1) into max_val FROM tab_1;
106 END //
107
108 CREATE PROCEDURE insert_and_out_param_and_select(OUT max_val INT)
109 BEGIN
110
111 INSERT INTO tab_1 VALUES(19999, "BIGER ONE");
112 SELECT MAX(c1t1) into max_val FROM tab_1;
113 SELECT MIN(c1t1) FROM tab_1;
114 END //
115
116 CREATE PROCEDURE error()
117 BEGIN
118 SELECT * FROM tab_not_existing;
119 END //
120
121 CREATE PROCEDURE select_and_error()
122 BEGIN
123 SELECT * FROM tab_1;
124 SELECT * FROM tab_not_existing;
125 END //
126
127 CREATE PROCEDURE out_param_and_error(OUT min_val INT)
128 BEGIN
129 SELECT MIN(c1t1) INTO min_val FROM tab_1;
130 SELECT * FROM tab_not_existing;
131 END //
132
133 CREATE PROCEDURE select_out_param_and_error(OUT min_val INT)
134 BEGIN
135 SELECT MIN(c1t1) INTO min_val FROM tab_1;
136 SELECT COUNT(c1t1) FROM tab_1;
137 SELECT * FROM tab_not_existing;
138 END //
139
140 CREATE PROCEDURE with_warning()
141 BEGIN
142 DECLARE tiny TINYINT;
143
144 SET tiny = 9000;
145 END //
146
147 CREATE PROCEDURE select_and_warning()
148 BEGIN
149 DECLARE tiny TINYINT;
150 SELECT * FROM tab_2;
151 SET tiny = 9000;
152 END //
153
154 CREATE PROCEDURE out_param_and_warning(OUT vavg FLOAT)
155 BEGIN
156 DECLARE tiny TINYINT;
157 SELECT AVG(c1t1) INTO vavg FROM tab_1;
158 SET tiny = 9000;
159 END //
160
161 CREATE PROCEDURE select_out_param_and_warning(OUT min_val INT)
162 BEGIN
163 DECLARE c CHAR(2);
164 SELECT MIN(c1t1) INTO min_val FROM tab_1;
165 SELECT COUNT(c1t1) FROM tab_1;
166 SET c = "TOO LONG";
167 END //
168
169 DELIMITER ;//
170
171 --enable_result_log
172 --enable_query_log