Debian Codegraph @

mysql-test/suite/compat/oracle/r/sp-package-innodb.result

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