mirror/mariadb
1
0
Fork 0
mirror of https://github.com/MariaDB/server.git synced 2025-04-21 06:35:31 +02:00
Code Issues Projects Releases Packages Wiki Activity Actions 1
mariadb/mysql-test/suite/compat/oracle/t/sp-package.test
Alexander Barkov aed9c656a9 MDEV-32101 CREATE PACKAGE [BODY] for sql_mode=DEFAULT 
This patch adds PACKAGE support with SQL/PSM dialect for sql_mode=DEFAULT:

- CREATE PACKAGE
- DROP PACKAGE
- CREATE PACKAGE BODY
- DROP PACKAGE BODY
- Package function and procedure invocation from outside of the package:
    -- using two step identifiers
    SELECT pkg.f1();
    CALL pkg.p1()

    -- using three step identifiers
    SELECT db.pkg.f1();
    CALL db.pkg.p1();

This is a non-standard MariaDB extension.

However, later this code can be used to implement
the SQL Standard and DB2 dialects of CREATE MODULE.
2023-12-18 13:34:55 +04:00

3111 lines
58 KiB
Text
Raw Permalink Blame History

--source include/default_charset.inc
SET sql_mode=ORACLE;
--enable_prepare_warnings
--disable_ps2_protocol
--echo #
--echo # Creating a body of a non-existing package
--echo #
DELIMITER $$;
--error ER_SP_DOES_NOT_EXIST
CREATE PACKAGE BODY test2 AS
FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
END;
$$
DELIMITER ;$$
--echo #
--echo # Dropping a non-existing package
--echo #
--error ER_SP_DOES_NOT_EXIST
DROP PACKAGE test2;
DROP PACKAGE IF EXISTS test2;
--error ER_SP_DOES_NOT_EXIST
DROP PACKAGE BODY test2;
--echo #
--echo # Bad combinations of OR REPLACE and IF EXISTS
--echo #
DELIMITER $$;
--error ER_WRONG_USAGE
CREATE OR REPLACE PACKAGE IF NOT EXISTS pkg AS
PROCEDURE p1;
END;
$$
DELIMITER ;$$
DELIMITER $$;
--error ER_WRONG_USAGE
CREATE OR REPLACE PACKAGE BODY IF NOT EXISTS pkg AS
PROCEDURE p1 AS BEGIN NULL; END;
END;
$$
DELIMITER ;$$
--echo #
--echo # PACKAGE and PS
--echo #
PREPARE stmt FROM 'CREATE PACKAGE test2 AS FUNCTION f1 RETURN INT; END test2';
DELIMITER $$;
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
END;
$$
DELIMITER ;$$
PREPARE stmt FROM 'CREATE PACKAGE BODY test2 AS'
' FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;'
'END test2';
DROP PACKAGE test2;
--echo #
--echo # Package and READ ONLY transactions
--echo #
SET SESSION TRANSACTION READ ONLY;
DELIMITER $$;
--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
PROCEDURE p1;
END
$$
DELIMITER ;$$
SET SESSION TRANSACTION READ WRITE;
DELIMITER $$;
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
FUNCTION f2 RETURN INT;
END;
$$
SET SESSION TRANSACTION READ ONLY
$$
--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
CREATE PACKAGE BODY test2 AS
FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
FUNCTION f2 RETURN INT AS BEGIN RETURN f1(); END;
PROCEDURE p1 AS
BEGIN
SELECT f2();
END;
END;
$$
DELIMITER ;$$
SET SESSION TRANSACTION READ WRITE;
DROP PACKAGE test2;
SET SESSION TRANSACTION READ ONLY;
--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
DROP PACKAGE test2;
--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
DROP PACKAGE BODY test2;
SET SESSION TRANSACTION READ WRITE;
--echo #
--echo # Syntax error inside a CREATE PACKAGE, inside a routine definition
--echo #
DELIMITER $$;
--error ER_PARSE_ERROR
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
FUNCTION f2 RETURN INT;
FUNCTION f3;
FUNCTION f4 RETURN INT;
END
$$
DELIMITER ;$$
--echo #
--echo # Syntax error inside a CREATE PACKAGE, outside of a routine definition
--echo #
# The definition "FUNCTION f3 RETURN INT AS BEGIN RETURN 10; END;"
# is valid in CREATE PACKAGE BODY, but not in CREATE PACKAGE.
# Syntax error happens after parsing "FUNCTION f3 RETURN INT".
DELIMITER $$;
--error ER_PARSE_ERROR
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
FUNCTION f2 RETURN INT;
FUNCTION f3 RETURN INT AS BEGIN RETURN 10; END;
FUNCTION f4 RETURN INT;
END
$$
DELIMITER ;$$
--echo #
--echo # Syntax error inside a CREATE PACKAGE BODY, inside a routine definition
--echo #
DELIMITER $$;
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
FUNCTION f2 RETURN INT;
END;
$$
--error ER_PARSE_ERROR
CREATE PACKAGE BODY test2 AS
FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
FUNCTION f2 RETURN INT SA BEGIN RETURN 10; END; -- Notice "SA" vs "AS"
END
$$
DELIMITER ;$$
DROP PACKAGE test2;
--echo #
--echo # Syntax error inside a CREATE PACKAGE BODY, outside a routine definition
--echo #
DELIMITER $$;
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
FUNCTION f2 RETURN INT;
END;
$$
--error ER_PARSE_ERROR
CREATE PACKAGE BODY test2 AS
FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
SOME SYNTAX ERROR;
FUNCTION f2 RETURN INT AS BEGIN RETURN 10; END;
END
$$
DELIMITER ;$$
DROP PACKAGE test2;
--echo #
--echo # Syntax error inside a CREATE PACKAGE BODY executable section
--echo #
DELIMITER $$;
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
END;
$$
--error ER_PARSE_ERROR
CREATE PACKAGE BODY test2 AS
FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
BEGIN
SOME SYNTAX ERROR;
END
$$
DELIMITER ;$$
DROP PACKAGE test2;
--echo #
--echo # CREATE PROCEDURE inside a package PROCEDURE is not allowed
--echo #
DELIMITER $$;
CREATE PACKAGE test2 AS
PROCEDURE p1;
END;
$$
--error ER_SP_NO_RECURSIVE_CREATE
CREATE PACKAGE BODY test2 AS
PROCEDURE p1 AS
BEGIN
CREATE PROCEDURE p1 AS BEGIN NULL; END;
END;
END;
$$
DELIMITER ;$$
DROP PACKAGE test2;
--echo #
--echo # CREATE PACKAGE inside a package PROCEDURE is not allowed
--echo #
DELIMITER $$;
CREATE PACKAGE test2 AS
PROCEDURE p1;
END;
$$
--error ER_SP_NO_RECURSIVE_CREATE
CREATE PACKAGE BODY test2 AS
PROCEDURE p1 AS
BEGIN
CREATE PACKAGE p1 AS PROCEDURE p1; END;
END;
END;
$$
DELIMITER ;$$
DROP PACKAGE test2;
--echo #
--echo # CREATE PROCEDURE inside a package executable section is not allowed
--echo #
DELIMITER $$;
CREATE PACKAGE test2 AS
PROCEDURE p1;
END;
$$
--error ER_SP_NO_RECURSIVE_CREATE
CREATE PACKAGE BODY test2 AS
PROCEDURE p1 AS BEGIN NULL; END;
BEGIN
CREATE PROCEDURE p1 AS BEGIN NULL; END;
END;
$$
DELIMITER ;$$
DROP PACKAGE test2;
--echo #
--echo # CREATE FUNCTION inside a package executable section is not allowed
--echo #
DELIMITER $$;
CREATE PACKAGE test2 AS
PROCEDURE p1;
END;
$$
--error ER_SP_NO_RECURSIVE_CREATE
CREATE PACKAGE BODY test2 AS
PROCEDURE p1 AS BEGIN NULL; END;
BEGIN
CREATE FUNCTION f1 RETURN INT AS BEGIN RETURN 0; END;
END;
$$
DELIMITER ;$$
DROP PACKAGE test2;
--echo #
--echo # CREATE PACKAGE inside a package executable section is not allowed
--echo #
DELIMITER $$;
CREATE PACKAGE test2 AS
PROCEDURE p1;
END;
$$
--error ER_SP_NO_RECURSIVE_CREATE
CREATE PACKAGE BODY test2 AS
PROCEDURE p1 AS BEGIN NULL; END;
BEGIN
CREATE PACKAGE p1 AS PROCEDURE p1; END;
END;
$$
DELIMITER ;$$
DROP PACKAGE test2;
--echo #
--echo # Broken CREATE PACKAGE at CREATE PACKAGE BODY time
--echo #
DELIMITER $$;
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
END;
$$
DELIMITER ;$$
UPDATE mysql.proc SET `body`='garbage'
WHERE db='test' AND name='test2' AND type='PACKAGE';
DELIMITER $$;
--error ER_SP_PROC_TABLE_CORRUPT
CREATE PACKAGE BODY test2 AS
FUNCTION f1 RETURN INT
AS BEGIN
RETURN f2();
END;
END;
$$
DELIMITER ;$$
show warnings;
DROP PACKAGE test2;
--echo #
--echo # Broken CREATE PACKAGE at a package function call time
--echo #
DELIMITER $$;
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
END;
$$
DELIMITER ;$$
DELIMITER $$;
CREATE PACKAGE BODY test2 AS
FUNCTION f1 RETURN INT
AS BEGIN
RETURN f2();
END;
END;
$$
DELIMITER ;$$
--error ER_SP_DOES_NOT_EXIST
SELECT test2.f1();
UPDATE mysql.proc SET `body`='garbage'
WHERE db='test' AND name='test2' AND type='PACKAGE';
--source sp-cache-invalidate.inc
--error ER_SP_PROC_TABLE_CORRUPT
SELECT test2.f1();
show warnings;
--error ER_SP_PROC_TABLE_CORRUPT
SELECT test2.f1();
show warnings;
--error ER_SP_PROC_TABLE_CORRUPT
SELECT test2.f1();
show warnings;
DROP PACKAGE test2;
--echo #
--echo # Broken CREATE PACKAGE at a package procedure call time
--echo #
DELIMITER $$;
CREATE PACKAGE test2 AS
PROCEDURE p1;
END;
$$
DELIMITER ;$$
DELIMITER $$;
CREATE PACKAGE BODY test2 AS
PROCEDURE p1
AS BEGIN
CALL p2;
END;
END;
$$
DELIMITER ;$$
--error ER_SP_DOES_NOT_EXIST
CALL test2.f1();
UPDATE mysql.proc SET `body`='garbage'
WHERE db='test' AND name='test2' AND type='PACKAGE';
--source sp-cache-invalidate.inc
--error ER_SP_PROC_TABLE_CORRUPT
CALL test2.p1();
show warnings;
--error ER_SP_PROC_TABLE_CORRUPT
CALL test2.p1();
show warnings;
--error ER_SP_PROC_TABLE_CORRUPT
CALL test2.p1();
show warnings;
DROP PACKAGE test2;
--echo #
--echo # Bad routine names
--echo #
DELIMITER $$;
--error ER_TOO_LONG_IDENT
CREATE PACKAGE p1 AS
PROCEDURE pppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppppp1;
END;
$$
DELIMITER ;$$
DELIMITER $$;
--error ER_TOO_LONG_IDENT
CREATE PACKAGE p1 AS
FUNCTION fffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff1
RETURN INT;
END;
$$
DELIMITER ;$$
DELIMITER $$;
--error ER_SP_WRONG_NAME
CREATE PACKAGE p1 AS
PROCEDURE "p1 ";
END;
$$
DELIMITER ;$$
DELIMITER $$;
--error ER_SP_WRONG_NAME
CREATE PACKAGE p1 AS
FUNCTION "f1 " RETURN INT;
END;
$$
DELIMITER ;$$
DELIMITER $$;
--error ER_SP_WRONG_NAME
CREATE PACKAGE p1 AS
PROCEDURE "p1.p1";
END;
$$
DELIMITER ;$$
DELIMITER $$;
--error ER_SP_WRONG_NAME
CREATE PACKAGE p1 AS
FUNCTION "f1.f1" RETURN INT;
END;
$$
DELIMITER ;$$
--echo #
--echo # Duplicate PROCEDURE in CREATE PACKAGE
--echo #
DELIMITER $$;
--error ER_SP_ALREADY_EXISTS,
CREATE PACKAGE test2 AS
PROCEDURE p1;
PROCEDURE p1;
END;
$$
DELIMITER ;$$
DELIMITER $$;
--error ER_SP_ALREADY_EXISTS,
CREATE PACKAGE test2 AS
PROCEDURE p1;
PROCEDURE P1;
END;
$$
DELIMITER ;$$
--echo #
--echo # Duplicate FUNCTION in CREATE PACKAGE
--echo #
DELIMITER $$;
--error ER_SP_ALREADY_EXISTS,
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
FUNCTION f1 RETURN INT;
END;
$$
DELIMITER ;$$
DELIMITER $$;
--error ER_SP_ALREADY_EXISTS,
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
FUNCTION F1 RETURN INT;
END;
$$
DELIMITER ;$$
--echo #
--echo # Duplicate PROCEDURE in CREATE PACKAGE BODY
--echo #
DELIMITER $$;
CREATE PACKAGE test2 AS
PROCEDURE p1;
END;
$$
--error ER_SP_ALREADY_EXISTS
CREATE PACKAGE BODY test2 AS
PROCEDURE p1 AS BEGIN NULL; END;
PROCEDURE p1 AS BEGIN NULL; END;
END;
$$
--error ER_SP_ALREADY_EXISTS
CREATE PACKAGE BODY test2 AS
PROCEDURE p1 AS BEGIN NULL; END;
PROCEDURE P1 AS BEGIN NULL; END;
END;
$$
DELIMITER ;$$
DROP PACKAGE test2;
--echo #
--echo # Duplicate FUNCTION in CREATE PACKAGE BODY
--echo #
DELIMITER $$;
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
END;
$$
--error ER_SP_ALREADY_EXISTS
CREATE PACKAGE BODY test2 AS
FUNCTION f1 RETURN INT AS BEGIN RETURN 0; END;
FUNCTION f1 RETURN INT AS BEGIN RETURN 0; END;
END;
$$
--error ER_SP_ALREADY_EXISTS
CREATE PACKAGE BODY test2 AS
FUNCTION f1 RETURN INT AS BEGIN RETURN 0; END;
FUNCTION F1 RETURN INT AS BEGIN RETURN 0; END;
END;
$$
DELIMITER ;$$
DROP PACKAGE test2;
--echo #
--echo # Routines declared in CREATE PACKAGE missing in CREATE PACKAGE BODY
--echo #
DELIMITER $$;
CREATE PACKAGE test2 AS
PROCEDURE p1;
END;
$$
--error ER_PACKAGE_ROUTINE_IN_SPEC_NOT_DEFINED_IN_BODY
CREATE PACKAGE BODY test2 AS
PROCEDURE p2 AS BEGIN NULL; END;
END;
$$
DELIMITER ;$$
DROP PACKAGE test2;
DELIMITER $$;
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
END;
$$
--error ER_PACKAGE_ROUTINE_IN_SPEC_NOT_DEFINED_IN_BODY
CREATE PACKAGE BODY test2 AS
FUNCTION f2 RETURN INT AS BEGIN RETURN 10; END;
END;
$$
DELIMITER ;$$
DROP PACKAGE test2;
DELIMITER $$;
CREATE PACKAGE test2 AS
PROCEDURE p1;
END;
$$
--error ER_PACKAGE_ROUTINE_IN_SPEC_NOT_DEFINED_IN_BODY
CREATE PACKAGE BODY test2 AS
FUNCTION p1 RETURN INT AS BEGIN RETURN 10; END;
END;
$$
DELIMITER ;$$
DROP PACKAGE test2;
DELIMITER $$;
CREATE PACKAGE test2 AS
PROCEDURE p1;
END;
$$
--error ER_PACKAGE_ROUTINE_IN_SPEC_NOT_DEFINED_IN_BODY
CREATE PACKAGE BODY test2 AS
PROCEDURE p1(a INT) AS BEGIN NULL; END; -- Notice different prototype
END;
$$
DELIMITER ;$$
DROP PACKAGE test2;
--echo #
--echo # Forward declarations in CREATE PACKAGE BODY with missing implementations
--echo #
DELIMITER $$;
CREATE PACKAGE test2 AS
PROCEDURE p1;
END;
$$
--error ER_PACKAGE_ROUTINE_FORWARD_DECLARATION_NOT_DEFINED
CREATE PACKAGE BODY test2 AS
PROCEDURE p1 AS BEGIN NULL; END;
PROCEDURE p2;
END;
$$
--error ER_PACKAGE_ROUTINE_FORWARD_DECLARATION_NOT_DEFINED
CREATE PACKAGE BODY test2 AS
FUNCTION f1 RETURN INT;
PROCEDURE p1 AS BEGIN NULL; END;
END;
$$
DELIMITER ;$$
DROP PACKAGE test2;
--echo #
--echo # Creating a new package
--echo #
DELIMITER $$;
CREATE PACKAGE test2 COMMENT 'package-test2-comment' AS
FUNCTION f1 RETURN INT DETERMINISTIC;
FUNCTION f2(a INT) RETURN INT;
FUNCTION concat RETURN INT;
PROCEDURE p1;
PROCEDURE p2(a INT);
END
$$
DELIMITER ;$$
--vertical_results
--replace_column 13 # 14 #
SELECT * FROM mysql.proc WHERE db='test' AND name='test2';
--replace_column 24 # 25 #
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test' AND ROUTINE_NAME='test2';
--horizontal_results
DELIMITER $$;
CREATE PACKAGE IF NOT EXISTS test2 AS
FUNCTION f1 RETURN INT;
END test2
$$
DELIMITER ;$$
DELIMITER $$;
CREATE PACKAGE BODY test2 COMMENT 'package-body-test2-comment' AS
FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
FUNCTION f2(a INT) RETURN INT AS BEGIN RETURN f1()+a; END;
FUNCTION concat RETURN INT AS BEGIN RETURN 1; END;
PROCEDURE p1 AS
BEGIN
SELECT f2(0);
END;
PROCEDURE p2(a INT) AS
BEGIN
SELECT f2(a);
END;
END;
$$
DELIMITER ;$$
# This should do nothing and return a warning
DELIMITER $$;
CREATE PACKAGE BODY IF NOT EXISTS test2 AS
FUNCTION f1 RETURN INT AS BEGIN RETURN 20; END;
FUNCTION f2(a INT) RETURN INT AS BEGIN RETURN f1()+a; END;
FUNCTION concat RETURN INT AS BEGIN RETURN 1; END;
PROCEDURE p1 AS
BEGIN
SELECT f2(0);
END;
PROCEDURE p2(a INT) AS
BEGIN
SELECT f2(a);
END;
END;
$$
DELIMITER ;$$
#
# The next query issues a warning about "concat" name collision,
# raised during compilation of the package body.
# However, "mtr --ps" does not produce the warning.
# It's not a package specific issue. The same difference exists for
# standalone functions. So just suppress warning for now.
#
--disable_warnings
SELECT test2.f1();
--enable_warnings
SELECT test2.f2(1);
CALL test2.p1();
CALL test2.p2(1);
--vertical_results
--replace_column 13 # 14 #
SELECT * FROM mysql.proc WHERE db='test' AND name LIKE 'test2.%';
--replace_column 24 # 25 #
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test' AND ROUTINE_NAME='test2';
--replace_column 24 # 25 #
SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test' AND ROUTINE_NAME LIKE 'test2.%';
--replace_column 4 'root@localhost' 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
SHOW PACKAGE STATUS;
--replace_column 4 'root@localhost' 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
SHOW PACKAGE BODY STATUS;
SHOW CREATE PACKAGE test2;
SHOW CREATE PACKAGE BODY test2;
--horizontal_results
DROP PACKAGE BODY test2;
--error ER_SP_DOES_NOT_EXIST
SELECT test2.f1();
--error ER_SP_DOES_NOT_EXIST
SELECT test2.f2();
--error ER_SP_DOES_NOT_EXIST
CALL test2.p1();
DROP PACKAGE BODY IF EXISTS test2;
--error ER_SP_DOES_NOT_EXIST
DROP PACKAGE BODY test2;
DROP PACKAGE test2;
--echo #
--echo # Creating a new package in a remote database
--echo #
CREATE DATABASE test2;
DELIMITER $$;
CREATE PACKAGE test2.test2 COMMENT 'package-test2-comment' AS
FUNCTION f1 RETURN INT;
PROCEDURE p1;
END
$$
DELIMITER ;$$
DELIMITER $$;
CREATE PACKAGE BODY test2.test2 COMMENT 'package-body-test2-comment' AS
FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
PROCEDURE p1 AS BEGIN SELECT f1(); END;
END;
$$
DELIMITER ;$$
--vertical_results
--replace_column 4 'root@localhost' 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
SHOW PACKAGE STATUS;
--replace_column 4 'root@localhost' 5 '0000-00-00 00:00:00' 6 '0000-00-00 00:00:00'
SHOW PACKAGE BODY STATUS;
--horizontal_results
USE test2;
SELECT test2.f1();
CALL test2.p1();
USE test;
DROP PACKAGE BODY test2.test2;
DROP PACKAGE test2.test2;
DROP DATABASE test2;
--echo #
--echo # Only public routines are available outside
--echo #
DELIMITER $$;
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY test2 AS
-- Public routines
FUNCTION f1 RETURN TEXT AS
BEGIN
RETURN 'This is test2.f1';
END;
PROCEDURE p1 AS
BEGIN
SELECT 'This is test2.p1';
END;
-- Private routines
FUNCTION f2 RETURN TEXT AS
BEGIN
RETURN 'This is test2.f2';
END;
PROCEDURE p2 AS
BEGIN
SELECT 'This is test2.p2';
END;
END;
$$
DELIMITER ;$$
SELECT test2.f1();
CALL test2.p1();
--error ER_SP_DOES_NOT_EXIST
SELECT test2.f2();
--error ER_SP_DOES_NOT_EXIST
CALL test2.p2();
DROP PACKAGE test2;
--echo #
--echo # PACKAGE BODY with forward declarations
--echo #
DELIMITER $$;
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY test2 AS
-- Forward declarations
FUNCTION f2private RETURN TEXT;
PROCEDURE p2private;
-- Public routines
FUNCTION f1 RETURN TEXT AS
BEGIN
RETURN f2private();
END;
PROCEDURE p1 AS
BEGIN
CALL p2private;
END;
-- Definitions for the forward declarations
FUNCTION f2private RETURN TEXT AS
BEGIN
RETURN 'This is f2private';
END;
PROCEDURE p2private AS
BEGIN
SELECT 'This is p2private';
END;
END;
$$
DELIMITER ;$$
SELECT test2.f1();
CALL test2.p1();
DROP PACKAGE test2;
--echo #
--echo # Calling private routines with forward declarations,
--echo # using qualified notation, e.g. "CALL pkg.proc"
--echo #
DELIMITER $$;
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY test2 AS
-- Forward declarations
FUNCTION f2private RETURN TEXT;
PROCEDURE p2private;
-- Public routines
FUNCTION f1 RETURN TEXT AS
BEGIN
RETURN test2.f2private();
END;
PROCEDURE p1 AS
BEGIN
CALL test2.p2private;
END;
-- Definitions for the forward declarations
FUNCTION f2private RETURN TEXT AS
BEGIN
RETURN 'This is f2private';
END;
PROCEDURE p2private AS
BEGIN
SELECT 'This is p2private' AS msg;
END;
END;
$$
DELIMITER ;$$
SELECT test2.f1();
CALL test2.p1();
DROP PACKAGE test2;
--echo #
--echo # Calling private routines, using qualified notation, e.g. "pkg.proc"
--echo #
DELIMITER $$;
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY test2 AS
-- Private routines
FUNCTION f2private RETURN TEXT AS
BEGIN
RETURN 'This is f2private';
END;
PROCEDURE p2private AS
BEGIN
SELECT 'This is p2private' AS msg;
END;
-- Public routines
FUNCTION f1 RETURN TEXT AS
BEGIN
RETURN test2.f2private();
END;
PROCEDURE p1 AS
BEGIN
CALL test2.p2private;
END;
END;
$$
DELIMITER ;$$
SELECT test2.f1();
CALL test2.p1();
DROP PACKAGE test2;
--echo #
--echo # Calling private routines from the package initialization section,
--echo # using qualified notation, e.g. "pkg.proc"
--echo #
DELIMITER $$;
CREATE PACKAGE test2 AS
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY test2 AS
-- Private routines
FUNCTION f2private RETURN TEXT AS
BEGIN
RETURN 'This is f2private';
END;
PROCEDURE p2private AS
BEGIN
SELECT 'This is p2private' AS msg;
END;
-- Public routines
PROCEDURE p1 AS
BEGIN
SELECT 'This is p1' AS msg;
END;
BEGIN
SELECT test2.f2private();
CALL test2.p2private();
END;
$$
DELIMITER ;$$
CALL test2.p1();
DROP PACKAGE test2;
--echo #
--echo # Testing OR REPLACE
--echo #
DELIMITER $$;
CREATE OR REPLACE PACKAGE pkg AS
FUNCTION f0 RETURN INT;
END;
$$
CREATE OR REPLACE PACKAGE pkg AS
FUNCTION f1 RETURN INT;
END;
$$
DELIMITER ;$$
SELECT name, type, `body` FROM mysql.proc WHERE name LIKE 'pkg%' ORDER BY type;
DELIMITER $$;
CREATE OR REPLACE PACKAGE BODY pkg AS
FUNCTION f1 RETURN INT AS BEGIN RETURN 10; END;
END;
$$
DELIMITER ;$$
SELECT name, type, `body` FROM mysql.proc WHERE name LIKE 'pkg%' ORDER BY type;
SELECT pkg.f1();
DELIMITER $$;
CREATE OR REPLACE PACKAGE BODY pkg AS
FUNCTION f1 RETURN INT AS BEGIN RETURN 20; END;
END;
$$
DELIMITER ;$$
SELECT name, type, `body` FROM mysql.proc WHERE name LIKE 'pkg%' ORDER BY type;
SELECT pkg.f1();
DELIMITER $$;
CREATE OR REPLACE PACKAGE pkg AS
FUNCTION f1 RETURN BIGINT;
END;
$$
DELIMITER ;$$
SELECT name, type, `body` FROM mysql.proc WHERE name LIKE 'pkg%' ORDER BY type;
--error ER_SP_DOES_NOT_EXIST
SELECT pkg.f1();
DELIMITER $$;
CREATE OR REPLACE PACKAGE BODY pkg AS
FUNCTION f1 RETURN INT AS BEGIN RETURN 30; END;
END;
$$
DELIMITER ;$$
SELECT name, type, `body` FROM mysql.proc WHERE name LIKE 'pkg%' ORDER BY type;
SELECT pkg.f1();
DROP PACKAGE pkg;
--echo #
--echo # Package routines accessing tables
--echo #
CREATE TABLE t1 (a INT);
DELIMITER $$;
CREATE PACKAGE test2 AS
PROCEDURE p1(a INT);
END;
$$
CREATE PACKAGE BODY test2 AS
PROCEDURE p1(a INT) AS
BEGIN
INSERT INTO t1 VALUES (10);
END;
END;
$$
DELIMITER ;$$
CALL test2.p1(10);
SELECT * FROM t1;
DROP PACKAGE test2;
DROP TABLE t1;
--echo #
--echo # CREATE PACKAGE: Optional package name after the "END" keyword
--echo #
DELIMITER $$;
--error ER_END_IDENTIFIER_DOES_NOT_MATCH
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
PROCEDURE p1;
END test2.test2
$$
DELIMITER ;$$
DELIMITER $$;
--error ER_END_IDENTIFIER_DOES_NOT_MATCH
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
PROCEDURE p1;
END test3
$$
DELIMITER ;$$
DELIMITER $$;
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
PROCEDURE p1;
END test2
$$
DELIMITER ;$$
DROP PACKAGE test2;
--echo #
--echo # MDEV-12089 sql_mode=ORACLE: Understand optional routine name after the END keyword
--echo #
DELIMITER $$;
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN INT;
PROCEDURE p1;
END test2;
$$
DELIMITER ;$$
DELIMITER $$;
--error ER_PARSE_ERROR
CREATE PACKAGE BODY test2 AS
FUNCTION f1 RETURN INT AS
BEGIN
RETURN 10;
END f1.f1;
END test2;
$$
DELIMITER ;$$
DELIMITER $$;
--error ER_END_IDENTIFIER_DOES_NOT_MATCH
CREATE PACKAGE BODY test2 AS
FUNCTION f1 RETURN INT AS
BEGIN
RETURN 10;
END f2;
END test2;
$$
DELIMITER ;$$
DELIMITER $$;
--error ER_PARSE_ERROR
CREATE PACKAGE BODY test2 AS
PROCEDURE p1 AS
BEGIN
NULL;
END p1.p1;
END test2;
$$
DELIMITER ;$$
DELIMITER $$;
--error ER_END_IDENTIFIER_DOES_NOT_MATCH
CREATE PACKAGE BODY test2 AS
PROCEDURE p1 AS
BEGIN
NULL;
END p2;
END test2;
$$
DELIMITER ;$$
DELIMITER $$;
CREATE PACKAGE BODY test2 AS
FUNCTION f1 RETURN INT AS
BEGIN
RETURN 10;
END f1;
PROCEDURE p1 AS
BEGIN
NULL;
END p1;
END test2;
$$
DELIMITER ;$$
DROP PACKAGE test2;
--echo #
--echo # Package and package routine name and end name are case insensitive
--echo #
DELIMITER $$;
CREATE PACKAGE test2 AS
FUNCTION f1 RETURN TEXT;
PROCEDURE p1;
END TEST2;
$$
DELIMITER ;$$
DELIMITER $$;
CREATE PACKAGE BODY test2 AS
FUNCTION f1 RETURN TEXT AS
BEGIN
RETURN 'This is f1';
END F1;
PROCEDURE P1 AS
BEGIN
SELECT 'This is p1' AS msg;
END p1;
END TEST2;
$$
DELIMITER ;$$
SELECT TEST2.F1();
SELECT test2.f1();
CALL TEST2.p1();
CALL test2.P1();
DROP PACKAGE BODY TEST2;
DROP PACKAGE TEST2;
--echo #
--echo # Testing various qualified/non-qualified db/package SP call chains
--echo #
DELIMITER $$;
CREATE FUNCTION f3() RETURN TEXT AS
BEGIN
SET @track= @track || ' ' || 'test.f3()';
RETURN '';
END;
$$
CREATE PROCEDURE p3() AS
BEGIN
SET @track= @track || ' ' || 'test.p3()';
END;
$$
CREATE FUNCTION ff2(task TEXT) RETURN TEXT AS
step TEXT := REGEXP_SUBSTR(task,'^[^ ]*');
tail TEXT := REGEXP_REPLACE(task,'^[^ ]*[ ]*(.*)','\\1');
rc TEXT;
BEGIN
SET @track= @track || ' ' || 'test.ff2()';
CASE step
WHEN '' THEN NULL;
WHEN 'p3' THEN CALL p3();
WHEN 'f3' THEN rc:= f3();
WHEN 'pack.p2' THEN CALL pack.p2(tail);
WHEN 'pack.f2' THEN rc:= pack.f2(tail);
WHEN 'pack.p3' THEN CALL pack.p3();
WHEN 'pack.f3' THEN rc:= pack.f3();
WHEN 'test.p3' THEN CALL test.p3();
WHEN 'test.f3' THEN rc:= test.f3();
WHEN 'test.pp2' THEN CALL test.pp2(tail);
WHEN 'test.ff2' THEN rc:= test.ff2(tail);
ELSE SET @track= @track || ' ' || step || ' [unknown step]';
END CASE;
RETURN '';
END;
$$
CREATE PROCEDURE pp2(task TEXT) AS
step TEXT := REGEXP_SUBSTR(task,'^[^ ]*');
tail TEXT := REGEXP_REPLACE(task,'^[^ ]*[ ]*(.*)','\\1');
rc TEXT;
BEGIN
SET @track= @track || ' ' || 'test.pp2()';
CASE step
WHEN '' THEN NULL;
WHEN 'p3' THEN CALL p3();
WHEN 'f3' THEN rc:= f3();
WHEN 'pack.p2' THEN CALL pack.p2(tail);
WHEN 'pack.f2' THEN rc:= pack.f2(tail);
WHEN 'pack.p3' THEN CALL pack.p3();
WHEN 'pack.f3' THEN rc:= pack.f3();
WHEN 'test.p3' THEN CALL test.p3();
WHEN 'test.f3' THEN rc:= test.f3();
WHEN 'test.pp2' THEN CALL test.pp2(tail);
WHEN 'test.ff2' THEN rc:= test.ff2(tail);
ELSE SET @track= @track || ' ' || step || ' [unknown step]';
END CASE;
END;
$$
CREATE PACKAGE pack AS
PROCEDURE p1(task TEXT);
PROCEDURE p2(task TEXT);
FUNCTION f1(task TEXT) RETURN TEXT;
FUNCTION f2(step2 TEXT) RETURN TEXT;
FUNCTION f3 RETURN TEXT;
PROCEDURE p3;
END;
$$
CREATE PACKAGE BODY pack AS
PROCEDURE p1(task TEXT) AS
step TEXT := REGEXP_SUBSTR(task,'^[^ ]*');
tail TEXT := REGEXP_REPLACE(task,'^[^ ]*[ ]*(.*)','\\1');
rc TEXT;
BEGIN
SET @track= 'test.pack.p1()';
CASE step
WHEN '' THEN NULL;
WHEN 'p2' THEN CALL p2(tail);
WHEN 'f2' THEN rc:= f2(tail);
WHEN 'p3' THEN CALL p3();
WHEN 'f3' THEN rc:= f3();
WHEN 'px' THEN CALL px();
WHEN 'fx' THEN rc:= fx();
WHEN 'pp2' THEN CALL pp2(tail);
WHEN 'ff2' THEN rc:= ff2(tail);
WHEN 'pack.p2' THEN CALL pack.p2(tail);
WHEN 'pack.f2' THEN rc:= pack.f2(tail);
WHEN 'pack.p3' THEN CALL pack.p3();
WHEN 'pack.f3' THEN rc:= pack.f3();
WHEN 'pack.px' THEN CALL pack.px();
WHEN 'pack.fx' THEN rc:= pack.fx();
WHEN 'test.p3' THEN CALL test.p3();
WHEN 'test.f3' THEN rc:= test.f3();
WHEN 'test.pp2' THEN CALL test.pp2(tail);
WHEN 'test.ff2' THEN rc:= test.ff2(tail);
ELSE SET @track= @track || ' ' || step || ' [unknown step]';
END CASE;
SELECT @track;
END;
FUNCTION f1(task TEXT) RETURN TEXT AS
step TEXT := REGEXP_SUBSTR(task,'^[^ ]*');
tail TEXT := REGEXP_REPLACE(task,'^[^ ]*[ ]*(.*)','\\1');
rc TEXT;
BEGIN
SET @track= 'test.pack.f1()';
CASE step
WHEN '' THEN NULL;
WHEN 'p2' THEN CALL p2(tail);
WHEN 'f2' THEN rc:= f2(tail);
WHEN 'p3' THEN CALL p3();
WHEN 'f3' THEN rc:= f3();
WHEN 'px' THEN CALL px();
WHEN 'fx' THEN rc:= fx();
WHEN 'pp2' THEN CALL pp2(tail);
WHEN 'ff2' THEN rc:= ff2(tail);
WHEN 'pack.p2' THEN CALL pack.p2(tail);
WHEN 'pack.f2' THEN rc:= pack.f2(tail);
WHEN 'pack.p3' THEN CALL pack.p3();
WHEN 'pack.f3' THEN rc:= pack.f3();
WHEN 'pack.px' THEN CALL pack.px();
WHEN 'pack.fx' THEN rc:= pack.fx();
WHEN 'test.p3' THEN CALL test.p3();
WHEN 'test.f3' THEN rc:= test.f3();
WHEN 'test.pp2' THEN CALL test.pp2(tail);
WHEN 'test.ff2' THEN rc:= test.ff2(tail);
ELSE SET @track= @track || ' ' || step || ' [unknown step]';
END CASE;
SIGNAL SQLSTATE '01000' SET MESSAGE_TEXT=@track;
RETURN '';
END;
PROCEDURE p2(task TEXT) AS
step TEXT := REGEXP_SUBSTR(task,'^[^ ]*');
tail TEXT := REGEXP_REPLACE(task,'^[^ ]*[ ]*(.*)','\\1');
rc TEXT;
BEGIN
SET @track= @track || ' ' || 'test.pack.p2()';
CASE step
WHEN '' THEN NULL;
WHEN 'p2' THEN CALL p2(tail);
WHEN 'f2' THEN rc:= f2(tail);
WHEN 'p3' THEN CALL p3();
WHEN 'f3' THEN rc:= f3();
WHEN 'px' THEN CALL px();
WHEN 'fx' THEN rc:= fx();
WHEN 'pp2' THEN CALL pp2(tail);
WHEN 'ff2' THEN rc:= ff2(tail);
WHEN 'pack.p2' THEN CALL pack.p2(tail);
WHEN 'pack.f2' THEN rc:= pack.f2(tail);
WHEN 'pack.p3' THEN CALL pack.p3();
WHEN 'pack.f3' THEN rc:= pack.f3();
WHEN 'pack.px' THEN CALL pack.px();
WHEN 'pack.fx' THEN rc:= pack.fx();
WHEN 'test.p3' THEN CALL test.p3();
WHEN 'test.f3' THEN rc:= test.f3();
WHEN 'test.pp2' THEN CALL test.pp2(tail);
WHEN 'test.ff2' THEN rc:= test.ff2(tail);
ELSE SET @track= @track || ' ' || step || ' [unknown step]';
END CASE;
END;
FUNCTION f2(task TEXT) RETURN TEXT AS
step TEXT := REGEXP_SUBSTR(task,'^[^ ]*');
tail TEXT := REGEXP_REPLACE(task,'^[^ ]*[ ]*(.*)','\\1');
rc TEXT;
BEGIN
SET @track= @track || ' ' || 'test.pack.f2()';
CASE step
WHEN '' THEN NULL;
WHEN 'p2' THEN CALL p2(tail);
WHEN 'f2' THEN rc:= f2(tail);
WHEN 'p3' THEN CALL p3();
WHEN 'f3' THEN rc:= f3();
WHEN 'px' THEN CALL px();
WHEN 'fx' THEN rc:= fx();
WHEN 'pp2' THEN CALL pp2(tail);
WHEN 'ff2' THEN rc:= ff2(tail);
WHEN 'pack.p2' THEN CALL pack.p2(tail);
WHEN 'pack.f2' THEN rc:= pack.f2(tail);
WHEN 'pack.p3' THEN CALL pack.p3();
WHEN 'pack.f3' THEN rc:= pack.f3();
WHEN 'pack.px' THEN CALL pack.px();
WHEN 'pack.fx' THEN rc:= pack.fx();
WHEN 'test.p3' THEN CALL test.p3();
WHEN 'test.f3' THEN rc:= test.f3();
WHEN 'test.pp2' THEN CALL test.pp2(tail);
WHEN 'test.ff2' THEN rc:= test.ff2(tail);
ELSE SET @track= @track || ' ' || step || ' [unknown step]';
END CASE;
RETURN '';
END;
PROCEDURE p3 AS
BEGIN
SET @track= @track || ' ' || 'test.pack.p3()';
END;
FUNCTION f3 RETURN TEXT AS
BEGIN
SET @track= @track || ' ' || 'test.pack.f3()';
RETURN '';
END;
END pack;
$$
DELIMITER ;$$
SET max_sp_recursion_depth=10;
--echo # pack.routine -> *
CALL pack.p1('p2');
CALL pack.p1('f2');
--error ER_SP_DOES_NOT_EXIST
CALL pack.p1('px');
--error ER_SP_DOES_NOT_EXIST
CALL pack.p1('fx');
CALL pack.p1('pp2');
CALL pack.p1('ff2');
CALL pack.p1('pack.p2');
CALL pack.p1('pack.f2');
--error ER_SP_DOES_NOT_EXIST
CALL pack.p1('pack.px');
--error ER_SP_DOES_NOT_EXIST
CALL pack.p1('pack.fx');
CALL pack.p1('test.pp2');
CALL pack.p1('test.ff2');
DO pack.f1('p2');
DO pack.f1('f2');
--error ER_SP_DOES_NOT_EXIST
DO pack.p1('px');
--error ER_SP_DOES_NOT_EXIST
DO pack.p1('fx');
DO pack.f1('pp2');
DO pack.f1('ff2');
DO pack.f1('pack.p2');
DO pack.f1('pack.f2');
--error ER_SP_DOES_NOT_EXIST
SELECT pack.f1('pack.px');
--error ER_SP_DOES_NOT_EXIST
SELECT pack.f1('pack.fx');
DO pack.f1('test.pp2');
DO pack.f1('test.ff2');
--echo #
--echo # Qualified_package_routine -> Non_qualified_package_routine
--echo #
--echo # pack.routine -> [pack.]routine -> pack.routine
CALL pack.p1('p2 pack.p3');
CALL pack.p1('p2 pack.f3');
CALL pack.p1('f2 pack.p3');
CALL pack.p1('f2 pack.f3');
DO pack.f1('p2 pack.p3');
DO pack.f1('p2 pack.f3');
DO pack.f1('f2 pack.p3');
DO pack.f1('f2 pack.f3');
--echo # pack.routine -> [pack.]routine -> [pack]routine
CALL pack.p1('p2 p3');
CALL pack.p1('p2 f3');
CALL pack.p1('f2 p3');
CALL pack.p1('f2 f3');
DO pack.f1('p2 p3');
DO pack.f1('p2 f3');
DO pack.f1('f2 p3');
DO pack.f1('f2 f3');
--echo # pack.routine -> [pack.]routine -> test.routine
CALL pack.p1('p2 test.p3');
CALL pack.p1('p2 test.f3');
CALL pack.p1('f2 test.p3');
CALL pack.p1('f2 test.f3');
DO pack.f1('p2 test.p3');
DO pack.f1('p2 test.f3');
DO pack.f1('f2 test.p3');
DO pack.f1('f2 test.f3');
--echo # pack.routine -> [pack.]routine -> [test.]routine
CALL pack.p1('p2 pp2');
CALL pack.p1('p2 ff2');
CALL pack.p1('f2 pp2');
CALL pack.p1('f2 ff2');
DO pack.f1('p2 pp2');
DO pack.f1('p2 ff2');
DO pack.f1('f2 pp2');
DO pack.f1('f2 ff2');
--echo #
--echo # Qualified_package_routine -> Non_qualified_database_routine
--echo #
--echo # pack.routine -> [test.]routine -> pack.routine
CALL pack.p1('pp2 pack.p3');
CALL pack.p1('pp2 pack.f3');
CALL pack.p1('ff2 pack.p3');
CALL pack.p1('ff2 pack.f3');
DO pack.f1('pp2 pack.p3');
DO pack.f1('pp2 pack.f3');
DO pack.f1('ff2 pack.p3');
DO pack.f1('ff2 pack.f3');
--echo # pack.routine -> [test.]routine -> test.routine
CALL pack.p1('pp2 test.p3');
CALL pack.p1('pp2 test.f3');
CALL pack.p1('ff2 test.p3');
CALL pack.p1('ff2 test.f3');
DO pack.f1('pp2 test.p3');
DO pack.f1('pp2 test.f3');
DO pack.f1('ff2 test.p3');
DO pack.f1('ff2 test.f3');
--echo # pack.routine -> [test.]routine -> [test.]routine
CALL pack.p1('pp2 p3');
CALL pack.p1('pp2 f3');
CALL pack.p1('ff2 p3');
CALL pack.p1('ff2 f3');
DO pack.f1('pp2 p3');
DO pack.f1('pp2 f3');
DO pack.f1('ff2 p3');
DO pack.f1('ff2 f3');
--echo #
--echo # Qualified_package_routine -> Qualified_package_routine
--echo #
--echo # pack.routine -> pack.routine -> pack.routine
CALL pack.p1('pack.p2 pack.p3');
CALL pack.p1('pack.p2 pack.f3');
CALL pack.p1('pack.f2 pack.p3');
CALL pack.p1('pack.f2 pack.f3');
DO pack.f1('pack.p2 pack.p3');
DO pack.f1('pack.p2 pack.f3');
DO pack.f1('pack.f2 pack.p3');
DO pack.f1('pack.f2 pack.f3');
--echo # pack.routine -> pack.routine -> [pack.]routine
CALL pack.p1('pack.p2 p3');
CALL pack.p1('pack.p2 f3');
CALL pack.p1('pack.f2 p3');
CALL pack.p1('pack.f2 f3');
DO pack.f1('pack.p2 p3');
DO pack.f1('pack.p2 f3');
DO pack.f1('pack.f2 p3');
DO pack.f1('pack.f2 f3');
--echo # pack.routine -> pack.routine -> test.routine
CALL pack.p1('pack.p2 test.p3');
CALL pack.p1('pack.p2 test.f3');
CALL pack.p1('pack.f2 test.p3');
CALL pack.p1('pack.f2 test.f3');
DO pack.f1('pack.p2 test.p3');
DO pack.f1('pack.p2 test.f3');
DO pack.f1('pack.f2 test.p3');
DO pack.f1('pack.f2 test.f3');
--echo # pack.routine -> pack.routine -> [test.]routine
CALL pack.p1('pack.p2 pp2');
CALL pack.p1('pack.p2 ff2');
CALL pack.p1('pack.f2 pp2');
CALL pack.p1('pack.f2 ff2');
DO pack.f1('pack.p2 pp2');
DO pack.f1('pack.p2 ff2');
DO pack.f1('pack.f2 pp2');
DO pack.f1('pack.f2 ff2');
--echo #
--echo # Qualified_package_routine -> Qualified_database_routine
--echo #
--echo pack.routine -> test.routine -> pack.routine
CALL pack.p1('test.pp2 pack.p3');
CALL pack.p1('test.pp2 pack.f3');
CALL pack.p1('test.ff2 pack.p3');
CALL pack.p1('test.ff2 pack.f3');
DO pack.f1('test.pp2 pack.p3');
DO pack.f1('test.pp2 pack.f3');
DO pack.f1('test.ff2 pack.p3');
DO pack.f1('test.ff2 pack.f3');
--echo pack.routine -> test.routine -> test.routine
CALL pack.p1('test.pp2 test.p3');
CALL pack.p1('test.pp2 test.f3');
CALL pack.p1('test.ff2 test.p3');
CALL pack.p1('test.ff2 test.f3');
DO pack.f1('test.pp2 test.p3');
DO pack.f1('test.pp2 test.f3');
DO pack.f1('test.ff2 test.p3');
DO pack.f1('test.ff2 test.f3');
--echo pack.routine -> test.routine -> [test.]routine
CALL pack.p1('test.pp2 p3');
CALL pack.p1('test.pp2 f3');
CALL pack.p1('test.ff2 p3');
CALL pack.p1('test.ff2 f3');
DO pack.f1('test.pp2 p3');
DO pack.f1('test.pp2 f3');
DO pack.f1('test.ff2 p3');
DO pack.f1('test.ff2 f3');
--echo # Longer chains
CALL pack.p1('p2 f2 p2 test.pp2 test.ff2 pack.p3');
CALL pack.p1('p2 test.pp2 pack.p2 pack.f2 test.ff2 pack.p3');
DROP PACKAGE pack;
DROP FUNCTION f3;
DROP PROCEDURE p3;
DROP FUNCTION ff2;
DROP PROCEDURE pp2;
--echo #
--echo # Calling a standalone function from a non-current database,
--echo # which calls a package routine from the same non-current database.
--echo #
DELIMITER $$;
CREATE PROCEDURE p1 AS
BEGIN
CALL pkg1.p1;
END;
$$
CREATE PACKAGE pkg1 AS
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY pkg1 AS
PROCEDURE p1 AS
BEGIN
SELECT database();
END;
END;
$$
DELIMITER ;$$
# Current database
CALL p1;
CREATE DATABASE test2;
USE test2;
# Non-current database
CALL test.p1;
DROP DATABASE test2;
# No current database at all
CALL test.p1;
USE test;
DROP PACKAGE pkg1;
DROP PROCEDURE p1;
--echo #
--echo # Creating a package with a different DEFINER
--echo #
CREATE USER xxx@localhost;
DELIMITER $$;
CREATE DEFINER=xxx@localhost PACKAGE p1 AS
PROCEDURE p1;
END;
$$
CREATE DEFINER=xxx@localhost PACKAGE BODY p1 AS
PROCEDURE p1 AS
BEGIN
NULL;
END;
END;
$$
DELIMITER ;$$
SELECT definer, name, security_type, type FROM mysql.proc WHERE name LIKE 'p1%' ORDER BY definer, name, type;
DROP PACKAGE p1;
DROP USER xxx@localhost;
--echo #
--echo # Creating a package with a different DEFINER, with SQL SECURITY INVOKER
--echo #
CREATE USER xxx@localhost;
DELIMITER $$;
CREATE DEFINER=xxx@localhost PACKAGE p1 SQL SECURITY INVOKER AS
PROCEDURE p1;
END;
$$
CREATE DEFINER=xxx@localhost PACKAGE BODY p1 SQL SECURITY INVOKER AS
PROCEDURE p1 AS
BEGIN
NULL;
END;
END;
$$
DELIMITER ;$$
SELECT definer, name, security_type, type FROM mysql.proc WHERE name LIKE 'p1%' ORDER BY definer, name, type;
DROP PACKAGE p1;
DROP USER xxx@localhost;
--echo #
--echo # A package with an initialization section
--echo #
DELIMITER $$;
CREATE PACKAGE p1 AS
PROCEDURE p1;
FUNCTION f1 RETURN INT;
END;
$$
CREATE PACKAGE BODY p1 AS
PROCEDURE p1 AS BEGIN SET @a=@a+1; SELECT @a; END;
FUNCTION f1 RETURN INT AS BEGIN SET @a=@a+1; RETURN @a; END;
BEGIN
SET @a:=10;
END;
$$
DELIMITER ;$$
CALL p1.p1();
CALL p1.p1();
SELECT p1.f1();
SELECT p1.f1();
--source sp-cache-invalidate.inc
SELECT p1.f1();
CALL p1.p1();
SELECT p1.f1();
CALL p1.p1();
DROP PACKAGE p1;
--echo #
--echo # A package with an initialization section calling
--echo # routines from the same package, and standalone routines.
--echo #
DELIMITER $$;
CREATE PROCEDURE init20 AS
BEGIN
SET @msg= @msg || '[init20]';
END;
$$
CREATE PACKAGE p1 AS
PROCEDURE init1;
PROCEDURE init2;
FUNCTION init3 RETURN INT;
PROCEDURE p1;
FUNCTION f1 RETURN TEXT;
END;
$$
CREATE PACKAGE BODY p1 AS
PROCEDURE init1 AS
BEGIN
SET @msg= @msg || '[p1.init1]';
END;
PROCEDURE init2 AS
BEGIN
SET @msg= @msg || '[p1.init2]';
END;
FUNCTION init3 RETURN INT AS
BEGIN
SET @msg= @msg || '[p1.init3]';
RETURN 0;
END;
PROCEDURE p1 AS
BEGIN
SET @msg= @msg || '[p1.p1]';
SELECT @msg;
END;
FUNCTION f1 RETURN TEXT AS
BEGIN
SET @msg= @msg || '[p1.f1]';
RETURN @msg;
END;
BEGIN
SET @msg= '';
init1();
init2();
DO init3();
init20();
END;
$$
DELIMITER ;$$
CALL p1.p1();
CALL p1.p1();
SELECT p1.f1();
SELECT p1.f1();
--source sp-cache-invalidate.inc
SELECT p1.f1();
CALL p1.p1();
SELECT p1.f1();
CALL p1.p1();
DROP PACKAGE p1;
DROP PROCEDURE init20;
--echo #
--echo # EXECUTE IMMEDIATE in the package initialization section
--echo #
SET @a=1000;
CREATE TABLE t1 AS SELECT 10 AS a;
DELIMITER $$;
CREATE PACKAGE p1 AS
PROCEDURE p1;
FUNCTION f1 RETURN INT;
END;
$$
CREATE PACKAGE BODY p1 AS
PROCEDURE p1 AS BEGIN SET @a=@a+1; SELECT @a; END;
FUNCTION f1 RETURN INT AS BEGIN SET @a=@a+1; RETURN @a; END;
BEGIN
EXECUTE IMMEDIATE 'SELECT MAX(a) FROM t1 INTO @a';
END;
$$
DELIMITER ;$$
CALL p1.p1();
CALL p1.p1();
SELECT p1.f1();
SELECT p1.f1();
--source sp-cache-invalidate.inc
--error ER_STMT_NOT_ALLOWED_IN_SF_OR_TRG
SELECT p1.f1();
DROP PACKAGE p1;
DROP TABLE t1;
--echo #
--echo # A package with an initialization section, loading table data into a user variable
--echo #
SET @a=1000;
CREATE TABLE t1 AS SELECT 10 AS a;
DELIMITER $$;
CREATE PACKAGE p1 AS
PROCEDURE p1;
FUNCTION f1 RETURN INT;
END;
$$
CREATE PACKAGE BODY p1 AS
PROCEDURE p1 AS BEGIN SET @a=@a+1; SELECT @a; END;
FUNCTION f1 RETURN INT AS BEGIN SET @a=@a+1; RETURN @a; END;
BEGIN
SELECT MAX(a) FROM t1 INTO @a;
END;
$$
DELIMITER ;$$
CALL p1.p1();
CALL p1.p1();
SELECT p1.f1();
SELECT p1.f1();
--source sp-cache-invalidate.inc
SELECT p1.f1();
DROP PACKAGE p1;
DROP TABLE t1;
--echo #
--echo # A package with an initialization section producing an error
--echo #
DELIMITER $$;
CREATE PACKAGE p1 AS
PROCEDURE p1;
FUNCTION f1 RETURN TEXT;
END;
$$
CREATE PACKAGE BODY p1 AS
PROCEDURE p1 AS BEGIN SELECT 'This is p1' AS msg; END;
FUNCTION f1 RETURN TEXT AS BEGIN RETURN 'This is f1'; END;
BEGIN
SELECT 1 FROM t1 INTO @a;
END;
$$
DELIMITER ;$$
--error ER_NO_SUCH_TABLE
CALL p1.p1();
--error ER_NO_SUCH_TABLE
SELECT p1.f1();
--source sp-cache-invalidate.inc
--error ER_NO_SUCH_TABLE
SELECT p1.f1();
--error ER_NO_SUCH_TABLE
CALL p1.p1();
--error ER_NO_SUCH_TABLE
SELECT p1.f1();
CREATE TABLE t1 (a INT) AS SELECT 1;
CALL p1.p1();
--source sp-cache-invalidate.inc
SELECT p1.f1();
--source sp-cache-invalidate.inc
CALL p1.p1();
DROP TABLE t1;
DROP PACKAGE p1;
--echo #
--echo # A package with SF-unsafe statements in the initialization section
--echo #
DELIMITER $$;
CREATE PACKAGE p1 AS
PROCEDURE p1;
FUNCTION f1 RETURN TEXT;
END;
$$
CREATE PACKAGE BODY p1 AS
PROCEDURE p1 AS BEGIN SELECT 'This is p1' AS msg; END;
FUNCTION f1 RETURN TEXT AS BEGIN RETURN 'This is f1'; END;
BEGIN
CREATE TABLE IF NOT EXISTS t1 (a INT);
DROP TABLE IF EXISTS t1;
END;
$$
DELIMITER ;$$
CALL p1.p1();
SELECT p1.f1();
--source sp-cache-invalidate.inc
--error ER_COMMIT_NOT_ALLOWED_IN_SF_OR_TRG
SELECT p1.f1();
CALL p1.p1();
SELECT p1.f1();
DROP PACKAGE p1;
--echo #
--echo # MDEV-13139 Package-wide variables in CREATE PACKAGE
--echo #
DELIMITER $$;
CREATE PACKAGE p1 AS
PROCEDURE p1;
FUNCTION f1 RETURN INT;
END;
$$
--error ER_SP_DUP_VAR
CREATE PACKAGE BODY p1 AS
a INT;
a INT;
PROCEDURE p1 AS
BEGIN
CREATE VIEW v1 AS SELECT a;
END;
END;
$$
--error ER_PARSE_ERROR
CREATE PACKAGE BODY p1 AS
a INT;
PROCEDURE p1 AS
BEGIN
NULL;
END;
b INT; -- Variables cannot go after routine definitions
END;
$$
--error ER_VIEW_SELECT_VARIABLE
CREATE PACKAGE BODY p1 AS
a INT;
PROCEDURE p1 AS
BEGIN
CREATE VIEW v1 AS SELECT a;
END;
END;
$$
CREATE PACKAGE BODY p1 AS
a INT:=NULL;
PROCEDURE p1 AS
BEGIN
SELECT a;
a:=COALESCE(a,0)+100;
SET a=a+1;
END;
FUNCTION f1 RETURN INT AS
BEGIN
RETURN a;
END;
END;
$$
DELIMITER ;$$
CALL p1.p1;
CALL p1.p1;
CALL p1.p1;
SELECT p1.f1();
DROP PACKAGE p1;
--echo #
--echo # One package variable with a default value
--echo #
DELIMITER $$;
CREATE PACKAGE p1 AS
PROCEDURE p1;
FUNCTION f1 RETURN INT;
END;
$$
CREATE PACKAGE BODY p1 AS
a INT:=10;
PROCEDURE p1 AS BEGIN a:=a+1; SELECT a; END;
FUNCTION f1 RETURN INT AS BEGIN a:=a+1; RETURN a; END;
END;
$$
DELIMITER ;$$
CALL p1.p1();
CALL p1.p1();
SELECT p1.f1();
SELECT p1.f1();
--source sp-cache-invalidate.inc
SELECT p1.f1();
CALL p1.p1();
SELECT p1.f1();
CALL p1.p1();
DROP PACKAGE p1;
DELIMITER $$;
CREATE PACKAGE p1 AS
PROCEDURE p1;
FUNCTION f1 RETURN INT;
END;
$$
CREATE PACKAGE BODY p1 AS
a ROW (a INT, b TEXT):=ROW(10,'bbb');
PROCEDURE p1 AS
BEGIN
a.a:= a.a+1;
a.b:= a.b || 'B';
SELECT a.a, a.b;
END;
FUNCTION f1 RETURN INT AS BEGIN a.a:= a.a+1; RETURN a.a; END;
END;
$$
DELIMITER ;$$
CALL p1.p1();
CALL p1.p1();
SELECT p1.f1();
SELECT p1.f1();
--source sp-cache-invalidate.inc
SELECT p1.f1();
CALL p1.p1();
SELECT p1.f1();
CALL p1.p1();
DROP PACKAGE p1;
CREATE TABLE t1 (a INT);
DELIMITER $$;
CREATE PACKAGE p1 AS
PROCEDURE p1;
FUNCTION f1 RETURN INT;
END;
$$
CREATE PACKAGE BODY p1 AS
a t1.a%TYPE:=10;
PROCEDURE p1 AS BEGIN a:=a+1; SELECT a; END;
FUNCTION f1 RETURN INT AS BEGIN a:=a+1; RETURN a; END;
END;
$$
DELIMITER ;$$
CALL p1.p1();
CALL p1.p1();
SELECT p1.f1();
SELECT p1.f1();
--source sp-cache-invalidate.inc
SELECT p1.f1();
CALL p1.p1();
SELECT p1.f1();
CALL p1.p1();
DROP PACKAGE p1;
DROP TABLE t1;
CREATE TABLE t1 (a INT, b TEXT);
DELIMITER $$;
CREATE PACKAGE p1 AS
PROCEDURE p1;
FUNCTION f1 RETURN INT;
END;
$$
CREATE PACKAGE BODY p1 AS
a t1%ROWTYPE:=ROW(10,'bbb');
PROCEDURE p1 AS
BEGIN
a.a:= a.a+1;
a.b:= a.b || 'B';
SELECT a.a, a.b;
END;
FUNCTION f1 RETURN INT AS BEGIN a.a:= a.a+1; RETURN a.a; END;
END;
$$
DELIMITER ;$$
CALL p1.p1();
CALL p1.p1();
SELECT p1.f1();
SELECT p1.f1();
--source sp-cache-invalidate.inc
SELECT p1.f1();
CALL p1.p1();
SELECT p1.f1();
CALL p1.p1();
DROP PACKAGE p1;
DROP TABLE t1;
--echo #
--echo # One package variable, set in the package initialization section
--echo #
DELIMITER $$;
CREATE PACKAGE p1 AS
PROCEDURE p1;
FUNCTION f1 RETURN INT;
END;
$$
CREATE PACKAGE BODY p1 AS
a INT;
PROCEDURE p1 AS BEGIN a:=a+1; SELECT a; END;
FUNCTION f1 RETURN INT AS BEGIN a:=a+1; RETURN a; END;
BEGIN
a:=10;
END;
$$
DELIMITER ;$$
CALL p1.p1();
CALL p1.p1();
SELECT p1.f1();
SELECT p1.f1();
--source sp-cache-invalidate.inc
SELECT p1.f1();
CALL p1.p1();
SELECT p1.f1();
CALL p1.p1();
DROP PACKAGE p1;
--echo #
--echo # A package with an initialization section,
--echo # loading table data into a package variable
--echo #
CREATE TABLE t1 AS SELECT 10 AS a;
DELIMITER $$;
CREATE PACKAGE p1 AS
PROCEDURE p1;
FUNCTION f1 RETURN INT;
END;
$$
CREATE PACKAGE BODY p1 AS
a INT;
PROCEDURE p1 AS BEGIN SET a=a+1; SELECT a; END;
FUNCTION f1 RETURN INT AS BEGIN SET a=a+1; RETURN a; END;
BEGIN
a:=(SELECT MAX(t1.a) FROM t1);
END;
$$
DELIMITER ;$$
CALL p1.p1();
CALL p1.p1();
SELECT p1.f1();
SELECT p1.f1();
--source sp-cache-invalidate.inc
SELECT p1.f1();
DROP PACKAGE p1;
DROP TABLE t1;
--echo #
--echo # Package variables and XPath
--echo #
DELIMITER $$;
CREATE PACKAGE p1 AS
FUNCTION f1 RETURN TEXT;
END;
$$
CREATE PACKAGE BODY p1 AS
i INT:=0;
xml TEXT:= '<a><b>b1</b><b>b2</b><b>b3</b></a>';
FUNCTION f1 RETURN TEXT AS
BEGIN
SET i=i+1;
RETURN ExtractValue(xml, '/a/b[$i]');
END;
END;
$$
DELIMITER ;$$
SELECT p1.f1();
SELECT p1.f1();
SELECT p1.f1();
DROP PACKAGE p1;
--echo #
--echo # Package variables as OUT routine parameter
--echo #
DELIMITER $$;
CREATE PACKAGE p1 AS
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY p1 AS
a INT;
b INT;
c INT:=10;
PROCEDURE p2(a OUT INT) AS
BEGIN
a:=c;
c:=c+1;
END;
PROCEDURE p1 AS
BEGIN
CALL p2(b);
SELECT a,b;
END;
BEGIN
CALL p2(a);
END;
$$
DELIMITER ;$$
CALL p1.p1;
DROP PACKAGE p1;
DELIMITER $$;
CREATE PACKAGE p1 AS
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY p1 AS
a ROW(a INT, b TEXT);
b ROW(a INT, b TEXT);
c ROW(a INT, b TEXT):=ROW(1,'b');
PROCEDURE p2(x OUT ROW(a INT,b TEXT)) AS
BEGIN
x:=c;
x.a:=c.a+100;
x.b:=c.b||'X';
c.a:=c.a+1;
c.b:=c.b||'B';
END;
PROCEDURE p1 AS
BEGIN
CALL p2(b);
SELECT a.a,a.b,b.a,b.b;
END;
BEGIN
CALL p2(a);
END;
$$
DELIMITER ;$$
CALL p1.p1;
DROP PACKAGE p1;
CREATE TABLE t1 (a INT,b TEXT);
DELIMITER $$;
CREATE PACKAGE p1 AS
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY p1 AS
a t1%ROWTYPE;
b t1%ROWTYPE;
c t1%ROWTYPE:=ROW(1,'b');
PROCEDURE p2(x OUT t1%ROWTYPE) AS
BEGIN
x:=c;
x.a:=c.a+100;
x.b:=c.b||'X';
c.a:=c.a+1;
c.b:=c.b||'B';
END;
PROCEDURE p1 AS
BEGIN
CALL p2(b);
SELECT a.a,a.b,b.a,b.b;
END;
BEGIN
CALL p2(a);
END;
$$
DELIMITER ;$$
CALL p1.p1;
DROP PACKAGE p1;
DROP TABLE t1;
--echo #
--echo # Package variable fields as OUT routine parameters
--echo #
CREATE TABLE t1 (a INT,b TEXT);
DELIMITER $$;
CREATE PACKAGE p1 AS
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY p1 AS
a t1%ROWTYPE;
x t1%ROWTYPE:=ROW(10,'b');
PROCEDURE p2(a OUT INT,b OUT TEXT) AS
BEGIN
a:=x.a;
b:=x.b;
x.a:=x.a+1;
x.b:=x.b||'B';
END;
PROCEDURE p1 AS
BEGIN
CALL p2(a.a, a.b);
SELECT a.a,a.b;
END;
BEGIN
CALL p2(a.a, a.b);
SELECT a.a, a.b;
END;
$$
DELIMITER ;$$
CALL p1.p1;
DROP PACKAGE p1;
DROP TABLE t1;
--echo #
--echo # Package variables as SELECT INTO targets
--echo #
DELIMITER $$;
CREATE PACKAGE p1 AS
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY p1 AS
a INT;
b INT;
PROCEDURE p1 AS
BEGIN
SELECT 2 INTO b;
SELECT a,b;
END;
BEGIN
SELECT 1 INTO a;
END;
$$
DELIMITER ;$$
CALL p1.p1;
DROP PACKAGE p1;
CREATE TABLE t1 (a INT, b TEXT);
INSERT INTO t1 VALUES (10,'b');
DELIMITER $$;
CREATE PACKAGE p1 AS
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY p1 AS
a t1%ROWTYPE;
b t1%ROWTYPE;
PROCEDURE p1 AS
BEGIN
SELECT * FROM t1 INTO a;
SELECT a.a,a.b;
END;
BEGIN
SELECT * FROM t1 INTO b;
SELECT b.a, b.b;
END;
$$
DELIMITER ;$$
CALL p1.p1;
DROP PACKAGE p1;
DROP TABLE t1;
--echo #
--echo # Package variable fields as SELECT INTO targets
--echo #
DELIMITER $$;
CREATE PACKAGE p1 AS
PROCEDURE p1;
END;
$$
CREATE PACKAGE BODY p1 AS
a ROW(a INT, b TEXT);
b ROW(a INT, b TEXT);
PROCEDURE p1 AS
BEGIN
SELECT 20,'x2' INTO b.a,b.b;
SELECT a.a,a.b,b.a,b.b;
END;
BEGIN
SELECT 10,'x1' INTO a.a,a.b;
END;
$$
DELIMITER ;$$
CALL p1.p1;
DROP PACKAGE p1;
--echo #
--echo # Recursive package procedure calls
--echo # Makes sure that the non-top sp_head instances created by
--echo # sp_clone_and_link_routine() correctly reproduce the package context:
--echo # package variables, package routines.
--echo #
DELIMITER $$;
CREATE PACKAGE p1 AS
PROCEDURE p1(c INT);
END p1;
$$
CREATE PACKAGE BODY p1 AS
pv1 INT:=10;
FUNCTION f1 RETURN INT AS BEGIN RETURN pv1+100; END;
PROCEDURE p1(c INT) AS
BEGIN
SELECT c, pv1, f1();
IF c>0 THEN
pv1:=pv1+1;
CALL p1(c-1);
END IF;
END;
END;
$$
DELIMITER ;$$
SET max_sp_recursion_depth=5;
CALL p1.p1(5);
SET max_sp_recursion_depth=0;
CALL p1.p1(0);
--error ER_SP_RECURSION_LIMIT
CALL p1.p1(1);
DROP PACKAGE p1;
--echo #
--echo # Non-reserved keywords as package body variable names
--echo #
DELIMITER $$;
CREATE PACKAGE p1 AS
PROCEDURE p1;
END p1;
$$
CREATE PACKAGE BODY p1 AS
ascii INT:=10;
action INT:=20;
PROCEDURE p1 AS
BEGIN
SELECT ascii, action;
END;
BEGIN
ascii := ascii + 1;
action := action + 1;
END;
$$
DELIMITER ;$$
CALL p1.p1;
DROP PACKAGE p1;
--echo #
--echo # Package routines calling routines of another package
--echo #
DELIMITER $$;
CREATE PACKAGE p1 AS
PROCEDURE p1;
FUNCTION f1 RETURN TEXT;
END;
$$
CREATE PACKAGE p2 AS
PROCEDURE p1;
FUNCTION f1 RETURN TEXT;
END;
$$
CREATE PACKAGE BODY p1 AS
PROCEDURE p1 AS
BEGIN
SELECT 'This is p1.p1' AS msg;
END;
FUNCTION f1 RETURN TEXT AS
BEGIN
RETURN 'This is p1.f1';
END;
END;
$$
CREATE PACKAGE BODY p2 AS
PROCEDURE p1 AS
BEGIN
CALL p1.p1;
END;
FUNCTION f1 RETURN TEXT AS
BEGIN
RETURN p1.f1();
END;
END;
$$
DELIMITER ;$$
CALL p1.p1;
CALL p2.p1;
SELECT p1.f1(), p2.f1();
DROP PACKAGE p2;
DROP PACKAGE p1;
--echo #
--echo # Package names with dot characters
--echo #
DELIMITER $$;
CREATE PACKAGE "p1.p1" AS
PROCEDURE p1;
FUNCTION f1 RETURN TEXT;
END;
$$
CREATE PACKAGE BODY "p1.p1" AS
PROCEDURE p1 AS
BEGIN
SELECT 'This is p1' AS msg;
END;
FUNCTION f1 RETURN TEXT AS
BEGIN
RETURN 'This is f1';
END;
END;
$$
DELIMITER ;$$
CALL "p1.p1"."p1";
SELECT "p1.p1"."f1"();
DROP PACKAGE "p1.p1";
--echo #
--echo # MDEV-15070 Crash when doing a CREATE VIEW inside a package routine
--echo #
SET sql_mode=ORACLE;
DELIMITER $$;
CREATE OR REPLACE PACKAGE pkg1 AS
PROCEDURE p00();
END;
$$
CREATE OR REPLACE PACKAGE BODY pkg1 AS
PROCEDURE p01() AS
BEGIN
SELECT 'This is p01' AS msg;
END;
PROCEDURE p00() AS
BEGIN
CREATE OR REPLACE VIEW v1 AS SELECT 1;
DROP VIEW v1;
CALL p01();
END;
END;
$$
DELIMITER ;$$
CALL pkg1.p00;
DROP PACKAGE pkg1;
CREATE OR REPLACE TABLE t1 (a INT);
CREATE OR REPLACE TRIGGER tr1 BEFORE INSERT ON t1 FOR EACH ROW SET NEW.a=1;
DELIMITER $$;
CREATE OR REPLACE PACKAGE pkg1 AS
PROCEDURE p00();
END;
$$
CREATE OR REPLACE PACKAGE BODY pkg1 AS
PROCEDURE p01() AS
BEGIN
SELECT 'This is p01' AS msg;
END;
PROCEDURE p00() AS
BEGIN
DROP TRIGGER tr1;
CALL p01();
END;
END;
$$
DELIMITER ;$$
CALL pkg1.p00;
DROP PACKAGE pkg1;
DROP TABLE t1;
--echo #
--echo # MDEV-17387 MariaDB Server giving wrong error while executing select query from procedure
--echo #
CREATE TABLE t1 (
CTR varchar(2) NOT NULL,
COR varchar(3) NOT NULL,
DATE datetime NOT NULL,
CHAN varchar(4) NOT NULL,
CNO varchar(20) NOT NULL,
JOBN varchar(18) NOT NULL,
C1 varchar(30) DEFAULT NULL,
C2 varchar(30) DEFAULT NULL,
TIME datetime DEFAULT NULL,
AMT decimal(12,2) DEFAULT NULL,
DT datetime NOT NULL,
pk int(11) NOT NULL,
PRIMARY KEY (pk),
KEY Indx1 (JOBN)
);
DELIMITER $$;
CREATE PACKAGE xyz IS
PROCEDURE xyz123(ctr IN VARCHAR2,Jn IN VARCHAR2,R OUT VARCHAR2);
END;
$$
CREATE OR REPLACE PACKAGE BODY xyz IS
PROCEDURE xyz123(
ctr IN VARCHAR2,
Jn IN VARCHAR2,
R OUT VARCHAR2)
AS
lS NUMBER(10) :=0;
CURSOR cBPD IS
SELECT CTR, COR, DATE, CHAN, CNO, C1, C2, TIME, AMT
FROM t1 WHERE JOBN=Jn;
BEGIN
FOR lbpd IN cBPD
LOOP
lS:=lS+1;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
BEGIN
SELECT SQLERRM;
END;
END;
END $$
DELIMITER ;$$
CALL xyz.xyz123(17,18,@R);
DROP PACKAGE xyz;
DROP TABLE t1;
--disable_prepare_warnings
--echo #
--echo # MDEV-28166 sql_mode=ORACLE: fully qualified package function calls do not work: db.pkg.func()
--echo #
--error ER_WRONG_DB_NAME
SELECT `db `.pkg.func();
--error ER_SP_WRONG_NAME
SELECT db.`pkg `.func();
--error ER_SP_WRONG_NAME
SELECT db.pkg.`func `();
CREATE DATABASE db1;
USE db1;
DELIMITER $$;
CREATE PACKAGE pkg1 AS
FUNCTION f1 RETURN TEXT;
FUNCTION f2_db1_pkg1_f1 RETURN TEXT;
FUNCTION f2_pkg1_f1 RETURN TEXT;
FUNCTION f2_f1 RETURN TEXT;
END;
$$
CREATE PACKAGE BODY pkg1
AS
FUNCTION f1 RETURN TEXT IS
BEGIN
RETURN 'This is db1.pkg1.f1';
END;
FUNCTION f2_db1_pkg1_f1 RETURN TEXT IS
BEGIN
RETURN db1.pkg1.f1();
END;
FUNCTION f2_pkg1_f1 RETURN TEXT IS
BEGIN
RETURN pkg1.f1();
END;
FUNCTION f2_f1 RETURN TEXT IS
BEGIN
RETURN f1();
END;
END;
$$
DELIMITER ;$$
USE db1;
SELECT pkg1.f2_db1_pkg1_f1();
SELECT pkg1.f2_pkg1_f1();
SELECT pkg1.f2_f1();
SELECT db1.pkg1.f2_db1_pkg1_f1();
SELECT db1.pkg1.f2_pkg1_f1();
SELECT db1.pkg1.f2_f1();
USE test;
SELECT db1.pkg1.f2_db1_pkg1_f1();
SELECT db1.pkg1.f2_pkg1_f1();
SELECT db1.pkg1.f2_f1();
DROP DATABASE db1;
#
# Testing db.pkg.func() in the package initialization section
#
CREATE DATABASE db1;
CREATE DATABASE db2;
DELIMITER $$;
CREATE PACKAGE db1.pkg1 AS
FUNCTION f1 RETURN TEXT;
END;
$$
CREATE PACKAGE BODY db1.pkg1 AS
FUNCTION f1 RETURN TEXT AS
BEGIN
RETURN 'This is db1.pkg1.f1';
END;
END;
$$
DELIMITER ;$$
DELIMITER $$;
CREATE PACKAGE db2.pkg1 AS
FUNCTION f1 RETURN TEXT;
FUNCTION var1 RETURN TEXT;
FUNCTION var2 RETURN TEXT;
END;
$$
CREATE PACKAGE BODY db2.pkg1 AS
m_var1 TEXT;
m_var2 TEXT;
FUNCTION f1 RETURN TEXT AS
BEGIN
RETURN 'This is db2.pkg1.f1';
END;
FUNCTION var1 RETURN TEXT AS
BEGIN
RETURN m_var1;
END;
FUNCTION var2 RETURN TEXT AS
BEGIN
RETURN m_var2;
END;
BEGIN
m_var1:= db1.pkg1.f1();
m_var2:= db2.pkg1.f1();
END;
$$
DELIMITER ;$$
SELECT db2.pkg1.var1(), db2.pkg1.var2();
DROP DATABASE db1;
DROP DATABASE db2;
#
# Make sure fully qualified package function call does not support AS syntax:
# SELECT db.pkg.func(10 AS a);
#
DELIMITER $$;
CREATE PACKAGE pkg1 AS
FUNCTION f1(a TEXT) RETURN TEXT;
END;
$$
CREATE PACKAGE BODY pkg1 AS
FUNCTION f1(a TEXT) RETURN TEXT AS
BEGIN
RETURN a;
END;
END;
$$
DELIMITER ;$$
SELECT test.pkg1.f1('xxx');
--error ER_PARSE_ERROR
SELECT test.pkg1.f1('xxx' AS a);
DROP PACKAGE pkg1;
--echo #
--echo # MDEV-19328 sql_mode=ORACLE: Package function in VIEW
--echo #
SET sql_mode=ORACLE;
DELIMITER $$;
CREATE PACKAGE test1 AS
FUNCTION f_test RETURN number;
END test1;
$$
CREATE PACKAGE BODY test1
AS
FUNCTION f_test RETURN NUMBER IS
BEGIN
RETURN 1;
END;
END test1;
$$
DELIMITER ;$$
#
# A VIEW created with sql_mode=ORACLE, calling a package routine
#
SET sql_mode=ORACLE;
CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test1.f_test();
SELECT * FROM v_test;
--vertical_results
SHOW CREATE VIEW v_test;
--horizontal_results
#
# It also works with sql_mode=DEFALT
#
SET sql_mode=DEFAULT;
SELECT * FROM v_test;
--vertical_results
SHOW CREATE VIEW v_test;
--horizontal_results
DROP VIEW v_test;
#
# A VIEW created with sql_mode=DEFAULT, calling a package routine
#
SET sql_mode=DEFAULT;
CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test1.f_test();
SELECT * FROM v_test;
--vertical_results
SHOW CREATE VIEW v_test;
#
# It also works with sql_mode=ORACLE
#
--horizontal_results
SET sql_mode=ORACLE;
SELECT * FROM v_test;
--vertical_results
SHOW CREATE VIEW v_test;
--horizontal_results
DROP VIEW v_test;
SET sql_mode=ORACLE;
CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test.test1.f_test();
SELECT * FROM v_test;
--vertical_results
SHOW CREATE VIEW v_test;
--horizontal_results
SET sql_mode=DEFAULT;
SELECT * FROM v_test;
--vertical_results
SHOW CREATE VIEW v_test;
--horizontal_results
DROP VIEW v_test;
SET sql_mode=DEFAULT;
CREATE VIEW v_test AS SELECT 1 AS c1 FROM DUAL WHERE 1=test.test1.f_test();
SELECT * FROM v_test;
--vertical_results
SHOW CREATE VIEW v_test;
--horizontal_results
SET sql_mode=ORACLE;
SELECT * FROM v_test;
--vertical_results
SHOW CREATE VIEW v_test;
--horizontal_results
DROP VIEW v_test;
SET sql_mode=ORACLE;
DROP PACKAGE test1;
--echo #
--echo # MDEV-19804 sql_mode=ORACLE: call procedure in packages
--echo #
--error ER_WRONG_DB_NAME
CALL `db1 `.pkg.p;
--error ER_SP_WRONG_NAME
CALL db1.`pkg `.p;
--error ER_SP_WRONG_NAME
CALL db1.pkg.`p `;
SET sql_mode=ORACLE;
DELIMITER $$;
CREATE PACKAGE pkg1 as
PROCEDURE p1();
END;
$$
CREATE PACKAGE BODY pkg1 as
PROCEDURE p1() as
BEGIN
SELECT 'test-function' AS c1;
END;
END;
$$
DELIMITER ;$$
CALL pkg1.p1;
CALL test.pkg1.p1;
# In sql_mode=DEFAULT we support fully qualified package function names
# (this is needed for VIEWs). Let's make sure we also support fully
# qualified package procedure names, for symmetry
SET sql_mode=DEFAULT;
CALL test.pkg1.p1;
SET sql_mode=ORACLE;
DELIMITER $$;
BEGIN
CALL pkg1.p1;
CALL test.pkg1.p1;
END
$$
DELIMITER ;$$
DELIMITER $$;
BEGIN
pkg1.p1;
test.pkg1.p1;
END
$$
DELIMITER ;$$
DROP PACKAGE pkg1;
#
# Testing packages in different databases calling each other
# in routines and in the initialization section.
#
CREATE DATABASE db1;
DELIMITER $$;
CREATE PACKAGE db1.pkg1 AS
PROCEDURE p1(a OUT TEXT);
END;
$$
CREATE PACKAGE BODY db1.pkg1 AS
PROCEDURE p1(a OUT TEXT) AS
BEGIN
a:= 'This is db1.pkg1.p1';
END;
END;
$$
DELIMITER ;$$
CREATE DATABASE db2;
DELIMITER $$;
CREATE PACKAGE db2.pkg1 AS
FUNCTION var1 RETURN TEXT;
PROCEDURE p1(a OUT TEXT);
PROCEDURE p2_db1_pkg1_p1;
END;
$$
CREATE PACKAGE BODY db2.pkg1 AS
m_var1 TEXT;
FUNCTION var1 RETURN TEXT AS
BEGIN
RETURN m_var1;
END;
PROCEDURE p1(a OUT TEXT) AS
BEGIN
a:= 'This is db2.pkg1.p1';
END;
PROCEDURE p2_db1_pkg1_p1 AS
a TEXT;
BEGIN
db1.pkg1.p1(a);
SELECT a;
END;
BEGIN
db1.pkg1.p1(m_var1);
END;
$$
DELIMITER ;$$
SELECT db2.pkg1.var1();
CALL db2.pkg1.p2_db1_pkg1_p1;
--enable_ps2_protocol
DROP DATABASE db1;
DROP DATABASE db2;
--echo #
--echo # MDEV-29370 Functions in packages are slow and seems to ignore deterministic
--echo #
SET SQL_MODE=ORACLE;
CREATE TABLE t1 (c1 CHAR(1));
DELIMITER //;
CREATE FUNCTION f1_deterministic()
RETURN CHAR(1)
DETERMINISTIC
IS
BEGIN
RETURN 'X';
END;
//
CREATE FUNCTION f2_not_deterministic()
RETURN CHAR(1)
IS
BEGIN
RETURN 'X';
END;
//
CREATE PACKAGE pkg1
IS
PROCEDURE t1_populate(numrows INTEGER);
FUNCTION f3_deterministic() RETURN CHAR(1) DETERMINISTIC;
FUNCTION f4_not_deterministic() RETURN CHAR(1);
END;
//
CREATE PACKAGE BODY pkg1
IS
PROCEDURE t1_populate(numrounds INTEGER)
IS
i INTEGER;
BEGIN
INSERT INTO t1 VALUES('Y');
FOR i IN 1..numrounds LOOP
INSERT INTO t1 SELECT * FROM t1;
END LOOP;
END;
FUNCTION f3_deterministic() RETURN CHAR(1) DETERMINISTIC COMMENT 'xxx'
IS
BEGIN
RETURN 'X';
END;
FUNCTION f4_not_deterministic() RETURN CHAR(1)
IS
BEGIN
RETURN 'X';
END;
END;
//
DELIMITER ;//
CALL pkg1.t1_populate(3);
EXPLAIN EXTENDED SELECT 'Deterministic function', COUNT(*) FROM t1 WHERE c1 = f1_deterministic();
EXPLAIN EXTENDED SELECT 'Non-deterministic function', COUNT(*) FROM t1 WHERE c1 = f2_not_deterministic();
EXPLAIN EXTENDED SELECT 'Deterministic package function', COUNT(*) FROM t1 WHERE c1 = pkg1.f3_deterministic();
EXPLAIN EXTENDED SELECT 'Non-deterministic package function', COUNT(*) FROM t1 WHERE c1 = pkg1.f4_not_deterministic();
DROP TABLE t1;
DROP FUNCTION f1_deterministic;
DROP FUNCTION f2_not_deterministic;
DROP PACKAGE pkg1;
Reference in a new issue View git blame Copy permalink