| 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 ;| |