mirror of
https://github.com/MariaDB/server.git
synced 2025-04-20 14:15:30 +02:00
3074beaad6
Changing the way how a cursor is opened to fetch its structure only, e.g. for a cursor FOR loop record variable. The old methods with setting thd->lex->limit_rows_examined to an Item_uint(0) was not reliable and could push these messages into diagnostics area: The query examined at least 1 rows, which exceeds LIMIT ROWS EXAMINED (0) The new method should be more reliable, as it completely prevents the call of do_select() in JOIN::exec_inner() during the cursor structure discovery, so the execution of the cursor SELECT query returns immediately after the preparation step (when the result row structure becomes known), without even entering the code that fetches the result rows.
989 lines
18 KiB
Text
989 lines
18 KiB
Text
SET sql_mode=ORACLE;
|
|
|
|
--echo #
|
|
--echo # MDEV-10582 sql_mode=ORACLE: explicit cursor attributes %ISOPEN, %ROWCOUNT, %FOUND, %NOTFOUND
|
|
--echo #
|
|
|
|
|
|
--echo #
|
|
--echo # Cursor attributes outside of an SP context
|
|
--echo #
|
|
|
|
--error ER_SP_CURSOR_MISMATCH
|
|
SELECT c%ISOPEN;
|
|
--error ER_SP_CURSOR_MISMATCH
|
|
SELECT c%FOUND;
|
|
--error ER_SP_CURSOR_MISMATCH
|
|
SELECT c%NOTFOUND;
|
|
--error ER_SP_CURSOR_MISMATCH
|
|
SELECT c%ROWCOUNT;
|
|
|
|
|
|
--echo #
|
|
--echo # Undefinite cursor attributes
|
|
--echo #
|
|
|
|
DELIMITER $$;
|
|
--error ER_SP_CURSOR_MISMATCH
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
BEGIN
|
|
SELECT c%ISOPEN;
|
|
END;
|
|
$$
|
|
--error ER_SP_CURSOR_MISMATCH
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
BEGIN
|
|
SELECT c%ROWCOUNT;
|
|
END;
|
|
$$
|
|
--error ER_SP_CURSOR_MISMATCH
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
BEGIN
|
|
SELECT c%FOUND;
|
|
END;
|
|
$$
|
|
--error ER_SP_CURSOR_MISMATCH
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
BEGIN
|
|
SELECT c%NOTFOUND;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
|
|
|
|
--echo #
|
|
--echo # Not opened cursor attributes %FOUND, %NOTFOUND, %ROWCOUNT
|
|
--echo #
|
|
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
CURSOR c IS SELECT 1 AS c FROM DUAL;
|
|
BEGIN
|
|
SELECT c%ROWCOUNT;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
--error ER_SP_CURSOR_NOT_OPEN
|
|
CALL p1;
|
|
DROP PROCEDURE p1;
|
|
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
CURSOR c IS SELECT 1 AS c FROM DUAL;
|
|
BEGIN
|
|
SELECT c%FOUND;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
--error ER_SP_CURSOR_NOT_OPEN
|
|
CALL p1;
|
|
DROP PROCEDURE p1;
|
|
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
CURSOR c IS SELECT 1 AS c FROM DUAL;
|
|
BEGIN
|
|
SELECT c%NOTFOUND;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
--error ER_SP_CURSOR_NOT_OPEN
|
|
CALL p1;
|
|
DROP PROCEDURE p1;
|
|
|
|
|
|
--echo #
|
|
--echo # Not opened cursor attributes %FOUND, %NOTFOUND, %ROWCOUNT with INVALID_CURSOR exception
|
|
--echo #
|
|
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
CURSOR c IS SELECT 1 AS c FROM DUAL;
|
|
BEGIN
|
|
SELECT c%ROWCOUNT;
|
|
EXCEPTION
|
|
WHEN INVALID_CURSOR THEN SELECT 'INVALID_CURSOR caught' AS msg;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1;
|
|
DROP PROCEDURE p1;
|
|
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
CURSOR c IS SELECT 1 AS c FROM DUAL;
|
|
BEGIN
|
|
SELECT c%FOUND;
|
|
EXCEPTION
|
|
WHEN INVALID_CURSOR THEN SELECT 'INVALID_CURSOR caught' AS msg;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1;
|
|
DROP PROCEDURE p1;
|
|
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
CURSOR c IS SELECT 1 AS c FROM DUAL;
|
|
BEGIN
|
|
SELECT c%NOTFOUND;
|
|
EXCEPTION
|
|
WHEN INVALID_CURSOR THEN SELECT 'INVALID_CURSOR caught' AS msg;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1;
|
|
DROP PROCEDURE p1;
|
|
|
|
|
|
--echo #
|
|
--echo # print()
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
CURSOR c IS SELECT * FROM t1 ORDER BY a;
|
|
BEGIN
|
|
EXPLAIN EXTENDED SELECT c%ISOPEN, c%ROWCOUNT, c%FOUND, c%NOTFOUND;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1();
|
|
DROP PROCEDURE p1;
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo #
|
|
--echo # Declared data type of the attributes
|
|
--echo #
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (10);
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
CURSOR c IS SELECT * FROM t1 ORDER BY a;
|
|
BEGIN
|
|
OPEN c;
|
|
CREATE TABLE t2 AS SELECT c%ISOPEN, c%ROWCOUNT, c%FOUND, c%NOTFOUND;
|
|
SHOW CREATE TABLE t2;
|
|
DROP TABLE t2;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1();
|
|
DROP PROCEDURE p1;
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo #
|
|
--echo # Core functionality
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (10);
|
|
INSERT INTO t1 VALUES (20);
|
|
INSERT INTO t1 VALUES (30);
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
a INT:=0;
|
|
CURSOR c IS SELECT * FROM t1 ORDER BY a;
|
|
BEGIN
|
|
SELECT a, c%ISOPEN;
|
|
OPEN c;
|
|
/*
|
|
After OPEN and before FETCH:
|
|
- %ROWCOUNT returns 0
|
|
- %FOUND and %NOTFOUND return NULL
|
|
*/
|
|
SELECT a, c%ISOPEN, c%ROWCOUNT, c%FOUND, c%NOTFOUND;
|
|
FETCH c INTO a;
|
|
SELECT a, c%ISOPEN, c%ROWCOUNT, c%FOUND, c%NOTFOUND;
|
|
FETCH c INTO a;
|
|
SELECT a, c%ISOPEN, c%ROWCOUNT, c%FOUND, c%NOTFOUND;
|
|
FETCH c INTO a;
|
|
SELECT a, c%ISOPEN, c%ROWCOUNT, c%FOUND, c%NOTFOUND;
|
|
FETCH c INTO a;
|
|
SELECT a, c%ISOPEN, c%ROWCOUNT, c%FOUND, c%NOTFOUND;
|
|
CLOSE c;
|
|
SELECT a, c%ISOPEN;
|
|
/*
|
|
After reopen and before FETCH:
|
|
- %ROWCOUNT returns 0
|
|
- %FOUND and %NOTFOUND return NULL
|
|
*/
|
|
OPEN c;
|
|
SELECT a, c%ISOPEN, c%ROWCOUNT, c%FOUND, c%NOTFOUND;
|
|
FETCH c INTO a;
|
|
SELECT a, c%ISOPEN, c%ROWCOUNT, c%FOUND, c%NOTFOUND;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1();
|
|
DROP PROCEDURE p1;
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo #
|
|
--echo # %NOTFOUND as a loop exit condition
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (10);
|
|
INSERT INTO t1 VALUES (20);
|
|
INSERT INTO t1 VALUES (30);
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
a INT:=0;
|
|
CURSOR c IS SELECT * FROM t1 ORDER BY a;
|
|
BEGIN
|
|
OPEN c;
|
|
LOOP
|
|
FETCH c INTO a;
|
|
EXIT WHEN c%NOTFOUND;
|
|
SELECT a;
|
|
END LOOP;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1();
|
|
DROP PROCEDURE p1;
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo #
|
|
--echo # %FOUND as a loop exit condition
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (10);
|
|
INSERT INTO t1 VALUES (20);
|
|
INSERT INTO t1 VALUES (30);
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
a INT:=0;
|
|
CURSOR c IS SELECT * FROM t1 ORDER BY a;
|
|
BEGIN
|
|
OPEN c;
|
|
LOOP
|
|
FETCH c INTO a;
|
|
EXIT WHEN NOT c%FOUND;
|
|
SELECT a;
|
|
END LOOP;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1();
|
|
DROP PROCEDURE p1;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # End of MDEV-10582 sql_mode=ORACLE: explicit cursor attributes %ISOPEN, %ROWCOUNT, %FOUND, %NOTFOUND
|
|
--echo #
|
|
|
|
--echo #
|
|
--echo # MDEV-10597 Cursors with parameters
|
|
--echo #
|
|
|
|
--echo #
|
|
--echo # OPEN with a wrong number of parameters
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT, b VARCHAR(10));
|
|
DELIMITER $$;
|
|
--error ER_WRONG_PARAMCOUNT_TO_CURSOR
|
|
CREATE PROCEDURE p1(a_a INT,a_b VARCHAR)
|
|
AS
|
|
v_a INT;
|
|
v_b VARCHAR(10);
|
|
CURSOR c (p_a INT, p_b VARCHAR) IS SELECT * FROM t1 WHERE a=p_a;
|
|
BEGIN
|
|
OPEN c(a_a);
|
|
LOOP
|
|
FETCH c INTO v_a, v_b;
|
|
EXIT WHEN c%NOTFOUND;
|
|
DBMS_OUTPUT.PUT_LINE('Fetched a record a='||TO_CHAR(v_a)||' b='||v_b);
|
|
END LOOP;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo #
|
|
--echo # Cursor parameters are not visible outside of the cursor
|
|
--echo #
|
|
|
|
DELIMITER $$;
|
|
--error ER_UNKNOWN_SYSTEM_VARIABLE
|
|
CREATE PROCEDURE p1(a_a INT)
|
|
AS
|
|
v_a INT;
|
|
CURSOR c (p_a INT) IS SELECT a FROM t1 WHERE a=p_a;
|
|
BEGIN
|
|
OPEN c(a_a);
|
|
p_a:=10;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
|
|
DELIMITER $$;
|
|
--error ER_UNKNOWN_SYSTEM_VARIABLE
|
|
CREATE PROCEDURE p1(a_a INT)
|
|
AS
|
|
v_a INT;
|
|
CURSOR c (p_a INT) IS SELECT a FROM t1 WHERE a=p_a;
|
|
BEGIN
|
|
p_a:=10;
|
|
OPEN c(a_a);
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
|
|
|
|
--echo #
|
|
--echo # Cursor parameter shadowing a local variable
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (1);
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1(a INT)
|
|
AS
|
|
v_a INT:=NULL;
|
|
p_a INT:=NULL;
|
|
CURSOR c (p_a VARCHAR2) IS SELECT a FROM t1 WHERE p_a IS NOT NULL;
|
|
BEGIN
|
|
OPEN c(a);
|
|
FETCH c INTO v_a;
|
|
IF c%NOTFOUND THEN
|
|
BEGIN
|
|
SELECT 'No records found' AS msg;
|
|
RETURN;
|
|
END;
|
|
END IF;
|
|
CLOSE c;
|
|
SELECT 'Fetched a record a='||v_a AS msg;
|
|
INSERT INTO t1 VALUES (v_a);
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1(1);
|
|
SELECT * FROM t1;
|
|
CALL p1(NULL);
|
|
SELECT * FROM t1;
|
|
DROP PROCEDURE p1;
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo #
|
|
--echo # Parameters in SELECT list
|
|
--echo #
|
|
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1(a_a INT, a_b VARCHAR)
|
|
AS
|
|
v_a INT;
|
|
v_b VARCHAR(10);
|
|
CURSOR c (p_a INT, p_b VARCHAR) IS SELECT p_a,p_b FROM DUAL;
|
|
BEGIN
|
|
FOR i IN 0..1
|
|
LOOP
|
|
OPEN c(a_a + i,a_b);
|
|
LOOP
|
|
FETCH c INTO v_a, v_b;
|
|
EXIT WHEN c%NOTFOUND;
|
|
SELECT 'Fetched a record a=' || v_a || ' b=' || v_b AS msg;
|
|
END LOOP;
|
|
CLOSE c;
|
|
END LOOP;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1(1,'b1');
|
|
DROP PROCEDURE p1;
|
|
|
|
|
|
--echo #
|
|
--echo # Parameters in SELECT list + UNION
|
|
--echo #
|
|
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1(a_a INT, a_b VARCHAR)
|
|
AS
|
|
v_a INT;
|
|
v_b VARCHAR(10);
|
|
CURSOR c (p_a INT, p_b VARCHAR) IS
|
|
SELECT p_a,p_b FROM DUAL
|
|
UNION ALL
|
|
SELECT p_a+1,p_b||'b' FROM DUAL;
|
|
BEGIN
|
|
OPEN c(a_a,a_b);
|
|
LOOP
|
|
FETCH c INTO v_a, v_b;
|
|
EXIT WHEN c%NOTFOUND;
|
|
SELECT 'Fetched a record a=' || v_a || ' b=' || v_b AS msg;
|
|
END LOOP;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1(1,'b1');
|
|
DROP PROCEDURE p1;
|
|
|
|
|
|
--echo #
|
|
--echo # Parameters in SELECT list + type conversion + warnings
|
|
--echo #
|
|
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1(a_a VARCHAR)
|
|
AS
|
|
v_a INT;
|
|
CURSOR c (p_a INT) IS SELECT p_a FROM DUAL;
|
|
BEGIN
|
|
OPEN c(a_a);
|
|
LOOP
|
|
FETCH c INTO v_a;
|
|
EXIT WHEN c%NOTFOUND;
|
|
SELECT 'Fetched a record a=' || v_a AS msg;
|
|
END LOOP;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1('1b');
|
|
CALL p1('b1');
|
|
DROP PROCEDURE p1;
|
|
|
|
|
|
--echo #
|
|
--echo # One parameter in SELECT list + subselect
|
|
--echo #
|
|
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1(a_a VARCHAR)
|
|
AS
|
|
v_a VARCHAR(10);
|
|
CURSOR c (p_a VARCHAR) IS
|
|
SELECT p_a FROM DUAL UNION SELECT REVERSE(p_a) FROM DUAL;
|
|
BEGIN
|
|
OPEN c((SELECT a_a));
|
|
LOOP
|
|
FETCH c INTO v_a;
|
|
EXIT WHEN c%NOTFOUND;
|
|
SELECT v_a;
|
|
END LOOP;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1('ab');
|
|
DROP PROCEDURE p1;
|
|
|
|
|
|
--echo #
|
|
--echo # Two parameters in SELECT list + subselect
|
|
--echo #
|
|
|
|
SET sql_mode=ORACLE;
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1()
|
|
AS
|
|
v_a VARCHAR(10);
|
|
v_b VARCHAR(20);
|
|
CURSOR c (p_a VARCHAR, p_b VARCHAR) IS
|
|
SELECT p_a, p_b FROM DUAL
|
|
UNION
|
|
SELECT p_b, p_a FROM DUAL;
|
|
BEGIN
|
|
OPEN c((SELECT 'aaa'),(SELECT 'bbb'));
|
|
LOOP
|
|
FETCH c INTO v_a, v_b;
|
|
EXIT WHEN c%NOTFOUND;
|
|
SELECT v_a, v_b;
|
|
END LOOP;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1();
|
|
DROP PROCEDURE p1;
|
|
|
|
|
|
--echo #
|
|
--echo # Two parameters in SELECT list + two parameters in WHERE + subselects
|
|
--echo #
|
|
|
|
SET sql_mode=ORACLE;
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1(a_a VARCHAR, a_b VARCHAR)
|
|
AS
|
|
v_a VARCHAR(10);
|
|
v_b VARCHAR(20);
|
|
CURSOR c (value_a VARCHAR, value_b VARCHAR,
|
|
pattern_a VARCHAR, pattern_b VARCHAR) IS
|
|
SELECT value_a, value_b FROM DUAL WHERE value_a LIKE pattern_a
|
|
UNION
|
|
SELECT value_b, value_a FROM DUAL WHERE value_b LIKE pattern_b;
|
|
BEGIN
|
|
OPEN c((SELECT 'aaa'),(SELECT 'bbb'),(SELECT a_a),(SELECT a_b));
|
|
LOOP
|
|
FETCH c INTO v_a, v_b;
|
|
EXIT WHEN c%NOTFOUND;
|
|
SELECT v_a, v_b;
|
|
END LOOP;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1('%','%');
|
|
CALL p1('aaa','xxx');
|
|
CALL p1('xxx','bbb');
|
|
CALL p1('xxx','xxx');
|
|
DROP PROCEDURE p1;
|
|
|
|
|
|
--echo #
|
|
--echo # Parameters in SELECT list + stored function
|
|
--echo #
|
|
|
|
DELIMITER $$;
|
|
CREATE FUNCTION f1 (a VARCHAR) RETURN VARCHAR
|
|
AS
|
|
BEGIN
|
|
RETURN a || 'y';
|
|
END;
|
|
$$
|
|
CREATE PROCEDURE p1(a_a VARCHAR)
|
|
AS
|
|
v_a VARCHAR(10);
|
|
v_b VARCHAR(10);
|
|
CURSOR c (p_sel_a VARCHAR, p_cmp_a VARCHAR) IS
|
|
SELECT p_sel_a, p_cmp_a FROM DUAL;
|
|
BEGIN
|
|
OPEN c(f1(a_a), f1(a_a));
|
|
LOOP
|
|
FETCH c INTO v_a, v_b;
|
|
EXIT WHEN c%NOTFOUND;
|
|
SELECT v_a;
|
|
END LOOP;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1('x');
|
|
# A complex expression
|
|
CALL p1(f1(COALESCE(NULL, f1('x'))));
|
|
DROP PROCEDURE p1;
|
|
DROP FUNCTION f1;
|
|
|
|
|
|
--echo #
|
|
--echo # One parameter in WHERE clause
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT, b VARCHAR(10));
|
|
CREATE TABLE t2 (a INT, b VARCHAR(10));
|
|
INSERT INTO t1 VALUES (1,'11');
|
|
INSERT INTO t1 VALUES (1,'12');
|
|
INSERT INTO t1 VALUES (2,'21');
|
|
INSERT INTO t1 VALUES (2,'22');
|
|
INSERT INTO t1 VALUES (3,'31');
|
|
INSERT INTO t1 VALUES (3,'32');
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1(a_a INT)
|
|
AS
|
|
v_a INT;
|
|
v_b VARCHAR(10);
|
|
CURSOR c (p_a INT) IS SELECT a,b FROM t1 WHERE a=p_a;
|
|
BEGIN
|
|
OPEN c(a_a);
|
|
LOOP
|
|
FETCH c INTO v_a, v_b;
|
|
EXIT WHEN c%NOTFOUND;
|
|
INSERT INTO t2 VALUES (v_a,v_b);
|
|
END LOOP;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1(1);
|
|
SELECT * FROM t2;
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|
|
DROP PROCEDURE p1;
|
|
|
|
|
|
--echo #
|
|
--echo # Two parameters in WHERE clause
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT, b VARCHAR(10));
|
|
CREATE TABLE t2 (a INT, b VARCHAR(10));
|
|
INSERT INTO t1 VALUES (1,'11');
|
|
INSERT INTO t1 VALUES (1,'12');
|
|
INSERT INTO t1 VALUES (2,'21');
|
|
INSERT INTO t1 VALUES (2,'22');
|
|
INSERT INTO t1 VALUES (3,'31');
|
|
INSERT INTO t1 VALUES (3,'32');
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1(a_a INT, a_b VARCHAR)
|
|
AS
|
|
v_a INT;
|
|
v_b VARCHAR(10);
|
|
CURSOR c (p_a INT, p_b VARCHAR) IS SELECT a,b FROM t1 WHERE a=p_a AND b=p_b;
|
|
BEGIN
|
|
OPEN c(a_a, a_b);
|
|
LOOP
|
|
FETCH c INTO v_a, v_b;
|
|
EXIT WHEN c%NOTFOUND;
|
|
INSERT INTO t2 VALUES (v_a,v_b);
|
|
END LOOP;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1(1,'11');
|
|
SELECT * FROM t2;
|
|
DROP TABLE t1;
|
|
DROP TABLE t2;
|
|
DROP PROCEDURE p1;
|
|
|
|
--echo #
|
|
--echo # Parameters in WHERE and HAVING clauses
|
|
--echo #
|
|
CREATE TABLE t1 (name VARCHAR(10), value INT);
|
|
INSERT INTO t1 VALUES ('but',1);
|
|
INSERT INTO t1 VALUES ('but',1);
|
|
INSERT INTO t1 VALUES ('but',1);
|
|
INSERT INTO t1 VALUES ('bin',1);
|
|
INSERT INTO t1 VALUES ('bin',1);
|
|
INSERT INTO t1 VALUES ('bot',1);
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1 (arg_name_limit VARCHAR, arg_total_limit INT)
|
|
AS
|
|
v_name VARCHAR(10);
|
|
v_total INT;
|
|
-- +0 is needed to work around the bug MDEV-11081
|
|
CURSOR c(p_v INT) IS
|
|
SELECT name, SUM(value + p_v) + 0 AS total FROM t1
|
|
WHERE name LIKE arg_name_limit
|
|
GROUP BY name HAVING total>=arg_total_limit;
|
|
BEGIN
|
|
FOR i IN 0..1
|
|
LOOP
|
|
OPEN c(i);
|
|
LOOP
|
|
FETCH c INTO v_name, v_total;
|
|
EXIT WHEN c%NOTFOUND;
|
|
SELECT v_name, v_total;
|
|
END LOOP;
|
|
CLOSE c;
|
|
END LOOP;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1('%', 2);
|
|
CALL p1('b_t', 0);
|
|
DROP PROCEDURE p1;
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo #
|
|
--echo # One parameter in LIMIT clause
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT, b VARCHAR(10));
|
|
INSERT INTO t1 VALUES (1,'b1');
|
|
INSERT INTO t1 VALUES (2,'b2');
|
|
INSERT INTO t1 VALUES (3,'b3');
|
|
INSERT INTO t1 VALUES (4,'b4');
|
|
INSERT INTO t1 VALUES (5,'b5');
|
|
INSERT INTO t1 VALUES (6,'b6');
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1(a_a INT)
|
|
AS
|
|
v_a INT;
|
|
v_b VARCHAR(10);
|
|
CURSOR c (p_a INT) IS SELECT a,b FROM t1 ORDER BY a LIMIT p_a;
|
|
BEGIN
|
|
CREATE TABLE t2 (a INT, b VARCHAR(10));
|
|
OPEN c(a_a);
|
|
LOOP
|
|
FETCH c INTO v_a, v_b;
|
|
EXIT WHEN c%NOTFOUND;
|
|
INSERT INTO t2 VALUES (v_a,v_b);
|
|
END LOOP;
|
|
CLOSE c;
|
|
SELECT * FROM t2;
|
|
DROP TABLE t2;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1(1);
|
|
CALL p1(3);
|
|
CALL p1(6);
|
|
DROP TABLE t1;
|
|
DROP PROCEDURE p1;
|
|
|
|
|
|
--echo #
|
|
--echo # End of MDEV-10597 Cursors with parameters
|
|
--echo #
|
|
|
|
--echo #
|
|
--echo # MDEV-12209 sql_mode=ORACLE: Syntax error in a OPEN cursor with parameters makes the server crash
|
|
--echo #
|
|
CREATE TABLE t1 (a INT, b VARCHAR(10));
|
|
INSERT INTO t1 VALUES (1,'A');
|
|
DELIMITER $$;
|
|
--error ER_PARSE_ERROR
|
|
CREATE PROCEDURE p1(a INT,b VARCHAR)
|
|
AS
|
|
CURSOR c (p_a INT, p_b VARCHAR) IS SELECT * FROM t1 WHERE a=p_a;
|
|
BEGIN
|
|
OPEN c(a+, b);
|
|
LOOP
|
|
FETCH c INTO a, b;
|
|
EXIT WHEN c%NOTFOUND;
|
|
SELECT a, b;
|
|
END LOOP;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo #
|
|
--echo # MDEV-10577 sql_mode=ORACLE: %TYPE in variable declarations
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT, b VARCHAR(10),c DATETIME(3));
|
|
INSERT INTO t1 VALUES (1,'b1','2001-01-01 10:20:30.123');
|
|
INSERT INTO t1 VALUES (2,'b2','2001-01-02 10:20:30.123');
|
|
CREATE TABLE t2 LIKE t1;
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1()
|
|
AS
|
|
v_a t1.a%TYPE;
|
|
v_b t1.b%TYPE;
|
|
v_c t1.c%TYPE;
|
|
CURSOR c IS SELECT a,b,c FROM t1;
|
|
BEGIN
|
|
OPEN c;
|
|
LOOP
|
|
FETCH c INTO v_a, v_b, v_c;
|
|
EXIT WHEN c%NOTFOUND;
|
|
INSERT INTO t2 (a,b,c) VALUES (v_a, v_b, v_c);
|
|
END LOOP;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1();
|
|
SELECT * FROM t2;
|
|
DROP TABLE t2;
|
|
DROP PROCEDURE p1;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-12007 Allow ROW variables as a cursor FETCH target
|
|
--echo #
|
|
|
|
CREATE TABLE t1 (a INT, b VARCHAR(32));
|
|
INSERT INTO t1 VALUES (10,'b10');
|
|
INSERT INTO t1 VALUES (20,'b20');
|
|
INSERT INTO t1 VALUES (30,'b30');
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1 AS
|
|
rec ROW(a INT, b VARCHAR(32));
|
|
CURSOR c IS SELECT a,b FROM t1;
|
|
BEGIN
|
|
OPEN c;
|
|
LOOP
|
|
FETCH c INTO rec;
|
|
EXIT WHEN c%NOTFOUND;
|
|
SELECT ('rec=(' || rec.a ||','|| rec.b||')') AS c;
|
|
END LOOP;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1();
|
|
DROP PROCEDURE p1;
|
|
DROP TABLE t1;
|
|
|
|
|
|
--echo #
|
|
--echo # MDEV-12441 Variables declared after cursors with parameters lose values
|
|
--echo #
|
|
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1() AS
|
|
x0 INT:=100;
|
|
CURSOR cur(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
|
|
x1 INT:=101;
|
|
BEGIN
|
|
OPEN cur(10,11);
|
|
CLOSE cur;
|
|
SELECT x0, x1;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1();
|
|
DROP PROCEDURE p1;
|
|
|
|
|
|
CREATE TABLE t1 (a INT);
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1() AS
|
|
x0 INT:=100;
|
|
CURSOR cur(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
|
|
x1 t1.a%TYPE:=101;
|
|
BEGIN
|
|
OPEN cur(10,11);
|
|
CLOSE cur;
|
|
SELECT x0, x1;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1();
|
|
DROP PROCEDURE p1;
|
|
DROP TABLE t1;
|
|
|
|
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1() AS
|
|
x0 INT:=100;
|
|
CURSOR cur(cp1 INT, cp2 INT) IS SELECT cp1+cp2;
|
|
x1 ROW(a INT,b INT):=ROW(101,102);
|
|
BEGIN
|
|
OPEN cur(10,11);
|
|
CLOSE cur;
|
|
SELECT x0, x1.a, x1.b;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1();
|
|
DROP PROCEDURE p1;
|
|
|
|
|
|
CREATE TABLE t1 (a INT, b VARCHAR(10));
|
|
INSERT INTO t1 VALUES (10,'Tbl-t1.b0');
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1() AS
|
|
x0 INT:=100;
|
|
CURSOR cur(cp1 INT, cp2 INT) IS SELECT a,b FROM t1;
|
|
x1 t1%ROWTYPE:=ROW(101,'Var-x1.b0');
|
|
BEGIN
|
|
SELECT x0, x1.a, x1.b;
|
|
OPEN cur(10,11);
|
|
FETCH cur INTO x1;
|
|
CLOSE cur;
|
|
SELECT x0, x1.a, x1.b;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1();
|
|
DROP PROCEDURE p1;
|
|
DROP TABLE t1;
|
|
|
|
|
|
CREATE TABLE t1 (a INT, b VARCHAR(10));
|
|
INSERT INTO t1 VALUES (10,'Tbl-t1.b0');
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1() AS
|
|
x0 INT:=100;
|
|
CURSOR cur(cp1 INT, cp2 INT) IS SELECT a,b FROM t1;
|
|
x1 cur%ROWTYPE:=ROW(101,'Var-x1.b0');
|
|
BEGIN
|
|
SELECT x0, x1.a, x1.b;
|
|
OPEN cur(10,11);
|
|
FETCH cur INTO x1;
|
|
CLOSE cur;
|
|
SELECT x0, x1.a, x1.b;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
CALL p1();
|
|
DROP PROCEDURE p1;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-12854 Synchronize CREATE..SELECT data type and result set metadata data type for INT functions
|
|
--echo #
|
|
|
|
--enable_metadata
|
|
--disable_ps_protocol
|
|
DELIMITER $$;
|
|
DECLARE
|
|
CURSOR c IS SELECT 1 AS c FROM DUAL;
|
|
BEGIN
|
|
OPEN c;
|
|
SELECT
|
|
c%ISOPEN,
|
|
c%NOTFOUND,
|
|
c%FOUND,
|
|
c%ROWCOUNT;
|
|
CLOSE c;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
--enable_ps_protocol
|
|
--disable_metadata
|
|
|
|
|
|
--echo #
|
|
--echo # MDEV-17387 MariaDB Server giving wrong error while executing select query from procedure
|
|
--echo #
|
|
|
|
CREATE TABLE t1
|
|
(
|
|
JOBN varchar(18) NOT NULL,
|
|
pk int(11) NOT NULL,
|
|
PRIMARY KEY (pk),
|
|
KEY (JOBN)
|
|
);
|
|
|
|
DELIMITER $$;
|
|
CREATE PROCEDURE p1
|
|
AS
|
|
lS NUMBER(10) :=0;
|
|
CURSOR cBPD IS SELECT * FROM t1 WHERE JOBN='x';
|
|
BEGIN
|
|
FOR lbpd IN cBPD LOOP
|
|
lS:=lS+1;
|
|
END LOOP;
|
|
EXCEPTION
|
|
WHEN OTHERS THEN
|
|
BEGIN
|
|
SELECT SQLERRM;
|
|
END;
|
|
END;
|
|
$$
|
|
DELIMITER ;$$
|
|
|
|
CALL p1();
|
|
DROP PROCEDURE p1;
|
|
DROP TABLE t1;
|