Debian Codegraph @

mysql-test/suite/compat/oracle/t/sp-expr.test

raw
1 # Testing expressions of different kinds in various parts of SP syntax
2
3 SET sql_mode=ORACLE;
4
5 --echo #
6 --echo # Start of 10.3 tests
7 --echo #
8
9 #
10 # Subselects in SP control structures
11 #
12
13 CREATE TABLE t1 (a INT);
14 INSERT INTO t1 VALUES (1);
15 DELIMITER $$;
16
17 BEGIN
18 CASE ((1) IN (SELECT a FROM t1)) WHEN 1 THEN SELECT 1;
19 ELSE SELECT NULL;
20 END CASE;
21 END;
22 $$
23 BEGIN
24 CASE (EXISTS (SELECT a FROM t1)) WHEN 1 THEN SELECT 1;
25 ELSE SELECT NULL;
26 END CASE;
27 END;
28 $$
29
30 BEGIN
31 IF ((1) IN (SELECT a FROM t1)) THEN SELECT 1;
32 ELSE SELECT NULL;
33 END IF;
34 END;
35 $$
36 BEGIN
37 IF (EXISTS (SELECT a FROM t1)) THEN SELECT 1;
38 ELSE SELECT NULL;
39 END IF;
40 END;
41 $$
42
43 BEGIN
44 WHILE ((1234) IN (SELECT * FROM t1)) LOOP
45 SELECT 1;
46 END LOOP;
47 END;
48 $$
49 BEGIN
50 WHILE (EXISTS (SELECT * FROM t1 WHERE a=1234)) LOOP
51 SELECT 1;
52 END LOOP;
53 END;
54 $$
55
56 BEGIN
57 REPEAT
58 SELECT 1;
59 UNTIL (1 IN (SELECT * FROM t1))
60 END REPEAT;
61 END;
62 $$
63 BEGIN
64 REPEAT
65 SELECT 1;
66 UNTIL EXISTS (SELECT * FROM t1 WHERE a=1)
67 END REPEAT;
68 END;
69 $$
70
71 BEGIN
72 FOR i IN 0..(1 IN (SELECT * FROM t1))
73 LOOP
74 SELECT i;
75 END LOOP;
76 END;
77 $$
78 BEGIN
79 FOR i IN 0..EXISTS (SELECT * FROM t1 WHERE a=1)
80 LOOP
81 SELECT i;
82 END LOOP;
83 END;
84 $$
85 DELIMITER ;$$
86 DROP TABLE t1;
87
88
89 #
90 # Subselects as SP variable default values
91 #
92
93 CREATE TABLE t1 (a INT);
94 INSERT INTO t1 VALUES (10);
95 DELIMITER $$;
96 DECLARE
97 a INT DEFAULT ((10) IN (SELECT * FROM t1));
98 BEGIN
99 SELECT a;
100 END;
101 $$
102 DECLARE
103 a INT DEFAULT EXISTS (SELECT * FROM t1);
104 BEGIN
105 SELECT a;
106 END;
107 $$
108 DELIMITER ;$$
109 DROP TABLE t1;
110
111
112 #
113 # Subselects SP function return values
114 #
115
116 CREATE TABLE t1 (a INT);
117 INSERT INTO t1 VALUES (1);
118 DELIMITER $$;
119 CREATE FUNCTION f1() RETURN INT AS
120 BEGIN
121 RETURN ((1) IN (SELECT * FROM t1));
122 END;
123 $$
124 CREATE FUNCTION f2() RETURN INT AS
125 BEGIN
126 RETURN EXISTS (SELECT * FROM t1 WHERE a=1);
127 END;
128 $$
129 DELIMITER ;$$
130 SELECT f1();
131 SELECT f2();
132 DROP FUNCTION f1;
133 DROP FUNCTION f2;
134 DROP TABLE t1;
135
136
137 #
138 # Subselects in CURSOR parameters
139 #
140
141 CREATE TABLE t1 (a INT);
142 INSERT INTO t1 VALUES (1),(2),(3);
143 DELIMITER $$;
144 DECLARE
145 va INT;
146 CURSOR cur(amin INT) IS SELECT a FROM t1 WHERE a>amin ORDER BY a;
147 BEGIN
148 OPEN cur(1 IN (SELECT * FROM t1));
149 FETCH cur INTO va;
150 SELECT va;
151 CLOSE cur;
152 END;
153 $$
154 DECLARE
155 va INT;
156 CURSOR cur(amin INT) IS SELECT a FROM t1 WHERE a>amin ORDER BY a;
157 BEGIN
158 OPEN cur(EXISTS (SELECT * FROM t1));
159 FETCH cur INTO va;
160 SELECT va;
161 CLOSE cur;
162 END;
163 $$
164 DELIMITER ;$$
165 DROP TABLE t1;