Debian Codegraph @

mysql-test/suite/compat/oracle/t/sp-package-innodb.test

raw
1 -- source include/have_innodb.inc
2
3 SET default_storage_engine=InnoDB;
4
5 SET sql_mode=ORACLE;
6
7 CREATE TABLE t1 (a INT, routine TEXT);
8 SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES
9 WHERE TABLE_SCHEMA='test' AND TABLE_NAME='t1';
10 INSERT INTO t1 VALUES (10,'none');
11
12 --enable_prepare_warnings
13
14 DELIMITER $$;
15 CREATE PACKAGE pkg1 AS
16 PROCEDURE p1;
17 END;
18 $$
19 CREATE PACKAGE BODY pkg1 AS
20 a INT;
21 PROCEDURE p1 AS
22 BEGIN
23 a:=a+1;
24 INSERT INTO t1 VALUES (a,'p1');
25 END;
26 BEGIN
27 SELECT MAX(t1.a) FROM t1 INTO a;
28 a:=a+1;
29 INSERT INTO t1 VALUES (a,'pkg1 initialization');
30 END;
31 $$
32 DELIMITER ;$$
33 CALL pkg1.p1;
34 SELECT * FROM t1 ORDER BY a;
35 DELETE FROM t1;
36
37 --source sp-cache-invalidate.inc
38 START TRANSACTION;
39 CALL pkg1.p1;
40 SELECT * FROM t1 ORDER BY a;
41 ROLLBACK;
42 SELECT * FROM t1 ORDER BY a;
43 DELETE FROM t1;
44
45 --source sp-cache-invalidate.inc
46 INSERT INTO t1 VALUES (20,'none');
47 START TRANSACTION;
48 CALL pkg1.p1;
49 SELECT * FROM t1 ORDER BY a;
50 COMMIT;
51 SELECT * FROM t1 ORDER BY a;
52 DELETE FROM t1;
53
54 --source sp-cache-invalidate.inc
55 INSERT INTO t1 VALUES (20,'none');
56 START TRANSACTION;
57 CALL pkg1.p1;
58 SELECT * FROM t1 ORDER BY a;
59 ROLLBACK;
60 SELECT * FROM t1 ORDER BY a;
61 DELETE FROM t1;
62
63 --disable_prepare_warnings
64
65 DROP PACKAGE pkg1;
66 DROP TABLE t1;