mirror of
https://github.com/MariaDB/server.git
synced 2025-08-17 07:51:36 +02:00
115 lines
2 KiB
Text
115 lines
2 KiB
Text
#
|
|
# MDEV-36053 CURSOR declarations in PACKAGE BODY
|
|
#
|
|
CREATE OR REPLACE PACKAGE pkg
|
|
FUNCTION f1() RETURNS INT;
|
|
END;
|
|
/
|
|
CREATE PACKAGE BODY pkg
|
|
DECLARE cur CURSOR FOR SELECT a FROM t1;
|
|
FUNCTION f1() RETURNS TEXT
|
|
BEGIN
|
|
DECLARE vc TEXT DEFAULT 0;
|
|
OPEN cur;
|
|
FETCH cur INTO vc;
|
|
CLOSE cur;
|
|
RETURN vc;
|
|
END;
|
|
END;
|
|
/
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (10);
|
|
SELECT pkg.f1();
|
|
pkg.f1()
|
|
10
|
|
SELECT pkg.f1();
|
|
pkg.f1()
|
|
10
|
|
ALTER TABLE t1 MODIFY a TEXT;
|
|
UPDATE t1 SET a=CONCAT('a',a);
|
|
SELECT pkg.f1();
|
|
pkg.f1()
|
|
a10
|
|
SELECT pkg.f1();
|
|
pkg.f1()
|
|
a10
|
|
SELECT pkg.f1();
|
|
pkg.f1()
|
|
a10
|
|
DROP TABLE t1;
|
|
DROP PACKAGE pkg;
|
|
CREATE PACKAGE pkg
|
|
PROCEDURE p1();
|
|
END;
|
|
/
|
|
CREATE PACKAGE BODY pkg
|
|
DECLARE pv0 INT DEFAULT 10;
|
|
DECLARE cur CURSOR FOR SELECT a, pv0 FROM t1;
|
|
PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE vc0 TEXT DEFAULT '';
|
|
DECLARE vc1 TEXT DEFAULT '';
|
|
OPEN cur;
|
|
FETCH cur INTO vc0, vc1;
|
|
CLOSE cur;
|
|
SELECT vc0, vc1;
|
|
END;
|
|
END;
|
|
/
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (10);
|
|
CALL pkg.p1();
|
|
vc0 vc1
|
|
10 10
|
|
CALL pkg.p1();
|
|
vc0 vc1
|
|
10 10
|
|
ALTER TABLE t1 MODIFY a TEXT;
|
|
UPDATE t1 SET a=CONCAT('a',a);
|
|
CALL pkg.p1();
|
|
vc0 vc1
|
|
a10 10
|
|
CALL pkg.p1();
|
|
vc0 vc1
|
|
a10 10
|
|
DROP TABLE t1;
|
|
DROP PACKAGE pkg;
|
|
CREATE PACKAGE pkg
|
|
PROCEDURE p1();
|
|
END;
|
|
/
|
|
CREATE PACKAGE BODY pkg
|
|
DECLARE vp0 VARCHAR(10) DEFAULT 'vp0';
|
|
DECLARE c0 CURSOR FOR SELECT a, vp0 FROM t1;
|
|
PROCEDURE p1()
|
|
BEGIN
|
|
DECLARE vc0 ROW TYPE OF c0;
|
|
CREATE TABLE t2 AS SELECT vc0.a AS a, vp0 AS b;
|
|
SHOW CREATE TABLE t2;
|
|
SELECT * FROM t2;
|
|
DROP TABLE t2;
|
|
END;
|
|
END;
|
|
/
|
|
CREATE TABLE t1 (a INT);
|
|
INSERT INTO t1 VALUES (10);
|
|
CALL pkg.p1();
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` int(11) DEFAULT NULL,
|
|
`b` varchar(10) DEFAULT NULL
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
a b
|
|
NULL vp0
|
|
ALTER TABLE t1 MODIFY a TEXT;
|
|
UPDATE t1 SET a=CONCAT('a',a);
|
|
CALL pkg.p1();
|
|
Table Create Table
|
|
t2 CREATE TABLE `t2` (
|
|
`a` text DEFAULT NULL,
|
|
`b` varchar(10) DEFAULT NULL
|
|
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
|
|
a b
|
|
NULL vp0
|
|
DROP TABLE t1;
|
|
DROP PACKAGE pkg;
|