summaryrefslogtreecommitdiffstats
path: root/mysql-test/suite/perfschema/include/program_setup.inc
diff options
context:
space:
mode:
Diffstat (limited to 'mysql-test/suite/perfschema/include/program_setup.inc')
-rw-r--r--mysql-test/suite/perfschema/include/program_setup.inc136
1 files changed, 136 insertions, 0 deletions
diff --git a/mysql-test/suite/perfschema/include/program_setup.inc b/mysql-test/suite/perfschema/include/program_setup.inc
new file mode 100644
index 00000000..a4e7f245
--- /dev/null
+++ b/mysql-test/suite/perfschema/include/program_setup.inc
@@ -0,0 +1,136 @@
+#
+# SET-UP - Creation of various non-nested stored programs
+#
+
+--source include/no_protocol.inc
+--source include/have_innodb.inc
+
+--echo # SET-UP
+
+CREATE DATABASE stored_programs;
+USE stored_programs;
+
+CREATE TABLE t1(
+ i INT NOT NULL,
+ j INT
+) engine=innodb;
+
+CREATE TABLE t2(
+ name CHAR(16) NOT NULL DEFAULT '',
+ id INT NOT NULL
+) engine=innodb;
+
+CREATE TABLE t3(
+ d DATE,
+ n INT,
+ f DOUBLE,
+ s VARCHAR(32)
+);
+
+CREATE TABLE t4(
+ `k` int(10) unsigned NOT NULL AUTO_INCREMENT,
+ `word` varchar(100) NOT NULL,
+ `mean` varchar(300) NOT NULL,
+ PRIMARY KEY (`k`)
+);
+
+
+--echo ############################
+--echo # Creating Stored Programs #
+--echo ############################
+
+--echo # Stored Routine ( Procedure & Function )
+
+DELIMITER |;
+CREATE PROCEDURE SampleProc1(x1 INT, x2 INT, y INT)
+BEGIN
+ INSERT INTO t1 VALUES (x1, y);
+ INSERT INTO t1 VALUES (x2, y);
+END|
+
+CREATE PROCEDURE SampleProc2(x CHAR(16), y INT)
+BEGIN
+ DECLARE z1, z2 INT;
+ SET z1 = y;
+ SET z2 = z1+2;
+ INSERT INTO t2 VALUES (x, z2);
+END|
+
+CREATE PROCEDURE SampleProc3()
+BEGIN
+ DECLARE ld DATE;
+ DECLARE li INT;
+ DECLARE lf DOUBLE;
+ DECLARE ls VARCHAR(32);
+
+ SET ld = NULL, li = NULL, lf = NULL, ls = NULL;
+ INSERT INTO t3 VALUES (ld, li, lf, ls);
+
+ INSERT INTO t3 (n, f, s) VALUES ((ld IS NULL), 1, "ld is null"),
+ ((li IS NULL), 1, "li is null"),
+ ((li = 0), NULL, "li = 0"),
+ ((lf IS NULL), 1, "lf is null"),
+ ((lf = 0), NULL, "lf = 0"),
+ ((ls IS NULL), 1, "ls is null");
+END|
+
+
+CREATE PROCEDURE SampleProc4()
+BEGIN
+ DECLARE x INT;
+ SET x = 1;
+ WHILE x <= 2 DO
+ INSERT INTO t4(word, mean) VALUES('a','a mean');
+ SET x = x + 1;
+ END WHILE;
+END|
+
+CREATE FUNCTION append(s1 CHAR(8), s2 CHAR(8)) RETURNS CHAR(16)
+ RETURN concat(s1, s2)|
+
+CREATE FUNCTION wt_avg(n1 INT, n2 INT, n3 INT, n4 INT)
+ RETURNS INT
+ DETERMINISTIC
+ BEGIN
+ DECLARE avg INT;
+ SET avg = (n1+n2+n3*2+n4*4)/8;
+ RETURN avg;
+ END|
+
+CREATE FUNCTION fac(n INT UNSIGNED) RETURNS BIGINT UNSIGNED
+BEGIN
+ DECLARE f BIGINT UNSIGNED DEFAULT 1;
+ WHILE n > 1 DO
+ SET f = f * n;
+ SET n = n - 1;
+ END WHILE;
+ RETURN f;
+END|
+
+--echo # Triggers
+
+# INSERT triggers
+CREATE TRIGGER trg1 BEFORE INSERT ON t1 FOR EACH ROW
+BEGIN
+ if isnull(new.j) then
+ SET new.j:= new.i * 10;
+ END if;
+END|
+
+CREATE TRIGGER trg2 AFTER INSERT ON t2 FOR EACH ROW
+BEGIN
+ UPDATE t1 SET i=new.id+i ;
+END|
+
+# UPDATE trigger
+CREATE TRIGGER trg3 AFTER UPDATE ON t2 FOR EACH ROW
+ SET @change:= @change + new.id - old.id|
+
+# DELETE triggers
+CREATE TRIGGER trg4 BEFORE DELETE ON t1 FOR EACH ROW
+ SET @del:= @del + 1|
+
+CREATE TRIGGER trg5 AFTER DELETE ON t1 FOR EACH ROW
+ SET @del:= @del + 8 + old.j|
+
+DELIMITER ;|