1delimiter |; 2 3# -------------------------------------------------------------------------- 4 5CREATE PROCEDURE sp_vars_check_dflt() 6BEGIN 7 DECLARE v1 TINYINT DEFAULT 1e200; 8 DECLARE v1u TINYINT UNSIGNED DEFAULT 1e200; 9 DECLARE v2 TINYINT DEFAULT -1e200; 10 DECLARE v2u TINYINT UNSIGNED DEFAULT -1e200; 11 DECLARE v3 TINYINT DEFAULT 300; 12 DECLARE v3u TINYINT UNSIGNED DEFAULT 300; 13 DECLARE v4 TINYINT DEFAULT -300; 14 DECLARE v4u TINYINT UNSIGNED DEFAULT -300; 15 16 DECLARE v5 TINYINT DEFAULT 10 * 10 * 10; 17 DECLARE v5u TINYINT UNSIGNED DEFAULT 10 * 10 * 10; 18 DECLARE v6 TINYINT DEFAULT -10 * 10 * 10; 19 DECLARE v6u TINYINT UNSIGNED DEFAULT -10 * 10 * 10; 20 21 DECLARE v7 TINYINT DEFAULT '10'; 22 DECLARE v8 TINYINT DEFAULT '10 '; 23 DECLARE v9 TINYINT DEFAULT ' 10 '; 24 DECLARE v10 TINYINT DEFAULT 'String 10 '; 25 DECLARE v11 TINYINT DEFAULT 'String10'; 26 DECLARE v12 TINYINT DEFAULT '10 String'; 27 DECLARE v13 TINYINT DEFAULT '10String'; 28 DECLARE v14 TINYINT DEFAULT concat('10', ' '); 29 DECLARE v15 TINYINT DEFAULT concat(' ', '10'); 30 DECLARE v16 TINYINT DEFAULT concat('Hello, ', 'world'); 31 32 DECLARE v17 DECIMAL(64, 2) DEFAULT 12; 33 DECLARE v18 DECIMAL(64, 2) DEFAULT 12.123; 34 DECLARE v19 DECIMAL(64, 2) DEFAULT 11 + 1; 35 DECLARE v20 DECIMAL(64, 2) DEFAULT 12 + 0.123; 36 37 SELECT v1, v1u, v2, v2u, v3, v3u, v4, v4u; 38 SELECT v5, v5u, v6, v6u; 39 SELECT v7, v8, v9, v10, v11, v12, v13, v14, v15, v16; 40 SELECT v17, v18, v19, v20; 41END| 42 43# -------------------------------------------------------------------------- 44 45CREATE PROCEDURE sp_vars_check_assignment() 46BEGIN 47 DECLARE i1, i2, i3, i4 TINYINT; 48 DECLARE u1, u2, u3, u4 TINYINT UNSIGNED; 49 DECLARE d1, d2, d3 DECIMAL(64, 2); 50 51 SET i1 = 1e200; 52 SET i2 = -1e200; 53 SET i3 = 300; 54 SET i4 = -300; 55 56 SELECT i1, i2, i3, i4; 57 58 SET i1 = 10 * 10 * 10; 59 SET i2 = -10 * 10 * 10; 60 SET i3 = sign(10 * 10) * 10 * 20; 61 SET i4 = sign(-10 * 10) * -10 * 20; 62 63 SELECT i1, i2, i3, i4; 64 65 SET u1 = 1e200; 66 SET u2 = -1e200; 67 SET u3 = 300; 68 SET u4 = -300; 69 70 SELECT u1, u2, u3, u4; 71 72 SET u1 = 10 * 10 * 10; 73 SET u2 = -10 * 10 * 10; 74 SET u3 = sign(10 * 10) * 10 * 20; 75 SET u4 = sign(-10 * 10) * -10 * 20; 76 77 SELECT u1, u2, u3, u4; 78 79 SET d1 = 1234; 80 SET d2 = 1234.12; 81 SET d3 = 1234.1234; 82 83 SELECT d1, d2, d3; 84 85 SET d1 = 12 * 100 + 34; 86 SET d2 = 12 * 100 + 34 + 0.12; 87 SET d3 = 12 * 100 + 34 + 0.1234; 88 89 SELECT d1, d2, d3; 90END| 91 92# -------------------------------------------------------------------------- 93 94CREATE FUNCTION sp_vars_check_ret1() RETURNS TINYINT 95BEGIN 96 RETURN 1e200; 97END| 98 99# -------------------------------------------------------------------------- 100 101CREATE FUNCTION sp_vars_check_ret2() RETURNS TINYINT 102BEGIN 103 RETURN 10 * 10 * 10; 104END| 105 106# -------------------------------------------------------------------------- 107 108CREATE FUNCTION sp_vars_check_ret3() RETURNS TINYINT 109BEGIN 110 RETURN 'Hello, world'; 111END| 112 113# -------------------------------------------------------------------------- 114 115CREATE FUNCTION sp_vars_check_ret4() RETURNS DECIMAL(64, 2) 116BEGIN 117 RETURN 12 * 10 + 34 + 0.1234; 118END| 119 120# -------------------------------------------------------------------------- 121 122CREATE FUNCTION sp_vars_div_zero() RETURNS INTEGER 123BEGIN 124 DECLARE div_zero INTEGER; 125 SELECT 1/0 INTO div_zero; 126 RETURN div_zero; 127END| 128 129# -------------------------------------------------------------------------- 130 131delimiter ;| 132