mirror of
https://github.com/MariaDB/server.git
synced 2025-02-04 21:02:17 +01:00
290 lines
5.5 KiB
Text
290 lines
5.5 KiB
Text
SET sql_mode=ORACLE;
|
|
#
|
|
# MDEV-10598 sql_mode=ORACLE: Variable declarations can go after cursor declarations
|
|
#
|
|
#
|
|
# Variable after cursor declaration
|
|
#
|
|
CREATE TABLE t1 (a INT);
|
|
insert into t1 values (1);
|
|
insert into t1 values (2);
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
CURSOR c IS SELECT a FROM t1;
|
|
var1 varchar(10);
|
|
BEGIN
|
|
OPEN c;
|
|
fetch c into var1;
|
|
SELECT c%ROWCOUNT,var1;
|
|
close c;
|
|
END;
|
|
$$
|
|
CALL p1;
|
|
c%ROWCOUNT var1
|
|
1 1
|
|
DROP PROCEDURE p1;
|
|
drop table t1;
|
|
#
|
|
# Variable after condition declaration
|
|
#
|
|
CREATE TABLE t1 (col1 INT);
|
|
insert into t1 values (1);
|
|
create unique index t1_col1 on t1 (col1);
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
dup_key CONDITION FOR SQLSTATE '23000';
|
|
var1 varchar(40);
|
|
CONTINUE HANDLER FOR dup_key
|
|
BEGIN
|
|
var1:='duplicate key in index';
|
|
END;
|
|
BEGIN
|
|
var1:='';
|
|
insert into t1 values (1);
|
|
select var1;
|
|
END;
|
|
$$
|
|
CALL p1;
|
|
var1
|
|
duplicate key in index
|
|
DROP PROCEDURE p1;
|
|
drop table t1;
|
|
#
|
|
# Condition after cursor declaration
|
|
#
|
|
CREATE TABLE t1 (col1 INT);
|
|
insert into t1 values (1);
|
|
create unique index t1_col1 on t1 (col1);
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
var1 varchar(40);
|
|
var2 integer;
|
|
CURSOR c IS SELECT col1 FROM t1;
|
|
dup_key CONDITION FOR SQLSTATE '23000';
|
|
CONTINUE HANDLER FOR dup_key
|
|
BEGIN
|
|
var1:='duplicate key in index';
|
|
END;
|
|
BEGIN
|
|
var1:='';
|
|
insert into t1 values (1);
|
|
SELECT var1;
|
|
END;
|
|
$$
|
|
CALL p1;
|
|
var1
|
|
duplicate key in index
|
|
DROP PROCEDURE p1;
|
|
drop table t1;
|
|
#
|
|
# Cursor after handler declaration
|
|
#
|
|
CREATE TABLE t1 (col1 INT);
|
|
insert into t1 values (1);
|
|
create unique index t1_col1 on t1 (col1);
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
var1 varchar(40);
|
|
var2 integer;
|
|
dup_key CONDITION FOR SQLSTATE '23000';
|
|
CONTINUE HANDLER FOR dup_key
|
|
BEGIN
|
|
var1:='duplicate key in index';
|
|
END;
|
|
CURSOR c IS SELECT col1 FROM t1;
|
|
BEGIN
|
|
var1:='';
|
|
insert into t1 values (1);
|
|
SELECT var1;
|
|
END;
|
|
$$
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CURSOR c IS SELECT col1 FROM t1;
|
|
BEGIN
|
|
var1:='';
|
|
insert into t1 values (1);
|
|
S...' at line 10
|
|
drop table t1;
|
|
#
|
|
# Condition after handler declaration
|
|
#
|
|
CREATE TABLE t1 (col1 INT);
|
|
insert into t1 values (1);
|
|
create unique index t1_col1 on t1 (col1);
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
var1 varchar(40);
|
|
var2 integer;
|
|
dup_key CONDITION FOR SQLSTATE '23000';
|
|
CURSOR c IS SELECT col1 FROM t1;
|
|
CONTINUE HANDLER FOR dup_key
|
|
BEGIN
|
|
var1:='duplicate key in index';
|
|
END;
|
|
divide_by_zero CONDITION FOR SQLSTATE '22012';
|
|
BEGIN
|
|
var1:='';
|
|
insert into t1 values (1);
|
|
SELECT var1;
|
|
END;
|
|
$$
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'divide_by_zero CONDITION FOR SQLSTATE '22012';
|
|
BEGIN
|
|
var1:='';
|
|
insert into t1...' at line 11
|
|
drop table t1;
|
|
#
|
|
# Variable after handler declaration
|
|
#
|
|
CREATE TABLE t1 (col1 INT);
|
|
insert into t1 values (1);
|
|
create unique index t1_col1 on t1 (col1);
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
var1 varchar(40);
|
|
var2 integer;
|
|
dup_key CONDITION FOR SQLSTATE '23000';
|
|
CURSOR c IS SELECT col1 FROM t1;
|
|
CONTINUE HANDLER FOR dup_key
|
|
BEGIN
|
|
var1:='duplicate key in index';
|
|
END;
|
|
divide_by_zero CONDITION FOR SQLSTATE '22012';
|
|
BEGIN
|
|
var1:='';
|
|
insert into t1 values (1);
|
|
SELECT var1;
|
|
END;
|
|
$$
|
|
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'divide_by_zero CONDITION FOR SQLSTATE '22012';
|
|
BEGIN
|
|
var1:='';
|
|
insert into t1...' at line 11
|
|
drop table t1;
|
|
#
|
|
# Variable after cursor (inner block)
|
|
#
|
|
CREATE TABLE t1 (col1 INT);
|
|
insert into t1 values (1);
|
|
insert into t1 values (2);
|
|
create unique index t1_col1 on t1 (col1);
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
CURSOR c IS SELECT col1 FROM t1;
|
|
var1 varchar(40);
|
|
BEGIN
|
|
OPEN c;
|
|
begin
|
|
declare
|
|
CURSOR c IS SELECT col1 FROM t1 where col1=2;
|
|
var2 integer;
|
|
dup_key CONDITION FOR SQLSTATE '23000';
|
|
CONTINUE HANDLER FOR dup_key
|
|
BEGIN
|
|
var1:='duplicate key in index';
|
|
END;
|
|
begin
|
|
OPEN c;
|
|
fetch c into var1;
|
|
SELECT 'inner cursor',var1;
|
|
insert into t1 values (2);
|
|
close c;
|
|
end;
|
|
end;
|
|
SELECT var1;
|
|
fetch c into var1;
|
|
SELECT c%ROWCOUNT,var1;
|
|
begin
|
|
insert into t1 values (2);
|
|
exception when 1062 then
|
|
begin
|
|
SELECT 'dup key caugth';
|
|
end;
|
|
end;
|
|
close c;
|
|
END;
|
|
$$
|
|
CALL p1;
|
|
inner cursor var1
|
|
inner cursor 2
|
|
var1
|
|
duplicate key in index
|
|
c%ROWCOUNT var1
|
|
1 1
|
|
dup key caugth
|
|
dup key caugth
|
|
DROP PROCEDURE p1;
|
|
drop table t1;
|
|
#
|
|
# Cursor declaration and row type declaration in same block
|
|
#
|
|
CREATE TABLE t1 (a INT, b VARCHAR(10));
|
|
insert into t1 values(1,'a');
|
|
CREATE PROCEDURE p1()
|
|
AS
|
|
CURSOR cur1 IS SELECT a FROM t1;
|
|
rec1 cur1%ROWTYPE;
|
|
BEGIN
|
|
rec1.a:= 10;
|
|
END;
|
|
$$
|
|
call p1;
|
|
DROP PROCEDURE p1;
|
|
drop table t1;
|
|
#
|
|
# Recursive cursor and cursor%ROWTYPE declarations in the same block
|
|
#
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
a INT:=10;
|
|
b VARCHAR(10):='b0';
|
|
c DOUBLE:=0.1;
|
|
CURSOR cur1 IS SELECT a, b, c;
|
|
rec1 cur1%ROWTYPE;
|
|
CURSOR cur2 IS SELECT rec1.a + 1 "a", rec1.b||'0' AS b, rec1.c AS c;
|
|
rec2 cur2%ROWTYPE;
|
|
BEGIN
|
|
OPEN cur1;
|
|
FETCH cur1 INTO rec1;
|
|
CLOSE cur1;
|
|
SELECT rec1.a;
|
|
OPEN cur2;
|
|
FETCH cur2 INTO rec2;
|
|
CLOSE cur2;
|
|
SELECT rec2.a;
|
|
CREATE TABLE t2 AS SELECT rec2.a AS a, rec2.b AS b, rec2.c AS c;
|
|
SHOW CREATE TABLE t2;
|
|
DROP TABLE t2;
|
|
END;
|
|
$$
|
|
CALL p1();
|
|
rec1.a
|
|
10
|
|
rec2.a
|
|
11
|
|
Table Create Table
|
|
t2 CREATE TABLE "t2" (
|
|
"a" bigint(20) DEFAULT NULL,
|
|
"b" varchar(11) DEFAULT NULL,
|
|
"c" double DEFAULT NULL
|
|
)
|
|
DROP PROCEDURE p1;
|
|
#
|
|
# MDEV-12916 Wrong column data type for an INT field of a cursor-anchored ROW variable
|
|
#
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
a INT DEFAULT 10;
|
|
CURSOR cur1 IS SELECT a;
|
|
rec1 cur1%ROWTYPE;
|
|
BEGIN
|
|
CREATE TABLE t1 AS SELECT rec1.a;
|
|
SHOW CREATE TABLE t1;
|
|
DROP TABLE t1;
|
|
END;
|
|
$$
|
|
CALL p1();
|
|
Table Create Table
|
|
t1 CREATE TABLE "t1" (
|
|
"rec1.a" int(11) DEFAULT NULL
|
|
)
|
|
DROP PROCEDURE p1;
|