mirror of
https://github.com/MariaDB/server.git
synced 2025-08-29 13:51:35 +02:00
117 lines
1.8 KiB
Text
117 lines
1.8 KiB
Text
SET sql_mode=ORACLE;
|
|
#
|
|
# MDEV-36053 CURSOR declarations in PACKAGE BODY
|
|
#
|
|
CREATE OR REPLACE PACKAGE pkg AS
|
|
FUNCTION f1 RETURN INT;
|
|
END;
|
|
/
|
|
CREATE PACKAGE BODY pkg AS
|
|
CURSOR cur IS SELECT a FROM t1;
|
|
FUNCTION f1 RETURN TEXT AS
|
|
vc TEXT := 0;
|
|
BEGIN
|
|
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 AS
|
|
PROCEDURE p1;
|
|
END;
|
|
/
|
|
CREATE PACKAGE BODY pkg AS
|
|
pv0 INT := 10;
|
|
CURSOR cur IS SELECT a, pv0 FROM t1;
|
|
PROCEDURE p1 AS
|
|
vc0 TEXT := '';
|
|
vc1 TEXT := '';
|
|
BEGIN
|
|
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;
|
|
SET sql_mode=ORACLE;
|
|
CREATE PACKAGE pkg AS
|
|
PROCEDURE p1;
|
|
END;
|
|
/
|
|
CREATE PACKAGE BODY pkg AS
|
|
vp0 VARCHAR(10) := 'vp0';
|
|
CURSOR c0 IS SELECT a, vp0 FROM t1;
|
|
PROCEDURE p1 AS
|
|
vc0 c0%ROWTYPE;
|
|
BEGIN
|
|
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
|
|
)
|
|
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
|
|
)
|
|
a b
|
|
NULL vp0
|
|
DROP TABLE t1;
|
|
DROP PACKAGE pkg;
|