storedproc_master.inc

来自「这个文件是windows mysql源码」· INC 代码 · 共 2,828 行 · 第 1/5 页

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(mediumint zerofill f1 ) returns mediumint zerofill
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(mediumint unsigned zerofill f1 ) returns mediumint unsigned zerofill
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(int f1 ) returns int
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(int unsigned f1 ) returns int unsigned
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(int zerofill f1 ) returns int zerofill
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(int unsigned zerofill f1 ) returns int unsigned zerofill
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(bigint f1 ) returns bigint
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(bigint unsigned f1 ) returns bigint unsigned
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(bigint zerofill f1 ) returns bigint zerofill
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(bigint unsigned zerofill f1 ) returns bigint unsigned zerofill
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(decimal f1 ) returns decimal
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(decimal unsigned f1 ) returns decimal unsigned
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(decimal zerofill f1 ) returns decimal zerofill
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(decimal unsigned zerofill f1 ) returns decimal unsigned zerofill
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(numeric f1 ) returns numeric
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(numeric unsigned f1 ) returns numeric unsigned
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(numeric zerofill f1 ) returns numeric zerofill
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(numeric unsigned zerofill f1 ) returns numeric unsigned zerofill
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(real f1 ) returns real
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(real unsigned f1 ) returns real unsigned
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(real zerofill f1 ) returns real zerofill
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(real unsigned zerofill f1 ) returns real unsigned zerofill
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(float f1 ) returns float
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(float unsigned f1 ) returns float unsigned
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(float zerofill f1 ) returns float zerofill
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(float unsigned zerofill f1 ) returns float unsigned zerofill
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(date f1 ) returns date
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(time f1 ) returns time
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(datetime f1 ) returns datetime
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(timestamp f1 ) returns timestamp
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(year f1 ) returns year
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(year(3) f1 ) returns year(3)
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(year(4) f1 ) returns year(4)
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(enum("1enum", "2enum") f1 ) returns enum("1enum", "2enum")
    return f1;

DROP FUNCTION IF EXISTS fn1;
--error 1064
CREATE FUNCTION fn1(set("1set", "2set") f1 ) returns set("1set", "2set")
    return f1;


# ------------------------------------------------------------------------------
let $message= Testcase 4.1.16:
              ----------------
Ensure that a reference to a non-existent stored procedure is rejected with an
appropriate error message;
--source include/show_msg80.inc

DROP PROCEDURE IF EXISTS sp16;

--error 1305
CALL sp16( 'xyz' );

CREATE DATABASE db1;
USE db1;

delimiter //;
CREATE PROCEDURE sp16()
BEGIN
    set @var1 = 1;
    SELECT @var1;
END//
delimiter ;//

--error 1305
CALL db_storedproc.sp16();

# cleanup
USE db_storedproc;
DROP PROCEDURE db1.sp16;
DROP DATABASE db1;


# ------------------------------------------------------------------------------
let $message= Testcase 4.1.17:
              ----------------
Ensure that it is possible to drop, create and CALL/execute a procedure and a
function with the same name, even in the same database;
--source include/show_msg80.inc

USE db_storedproc;
DROP FUNCTION IF EXISTS sp1;
DROP PROCEDURE IF EXISTS sp1;

delimiter //;
CREATE PROCEDURE sp1 ()
BEGIN
    declare x enum( 'db1', 'test' ) default 'test';
    SELECT x;
END//
delimiter ;//

CALL sp1();


CREATE FUNCTION sp1 (y char) returns char return y;
  SELECT sp1( 'a' );

DROP DATABASE IF EXISTS db1;
CREATE DATABASE db1;
USE db1;
CALL db_storedproc.sp1( );
SELECT db_storedproc.sp1( 'a' );

DROP FUNCTION db_storedproc.sp1;
USE db_storedproc;

--error 1305
SELECT sp1('a');

DROP PROCEDURE sp1;

--error 1305
CALL sp1();

--error 1305
SELECT sp1('a');

# cleanup
USE db_storedproc;
DROP DATABASE db1;


# ------------------------------------------------------------------------------
let $message= Testcase 4.1.18:
              ----------------
Ensure that it is possible to alter a procedure and
a function with the same name, in the same database;
--source include/show_msg80.inc


USE db_storedproc;
DROP PROCEDURE IF EXISTS sp1;
DROP FUNCTION IF EXISTS sp1;
set @x=null; set @y=null;

delimiter //;
CREATE PROCEDURE sp1()
BEGIN
    set @x= 1;
    SELECT @x;
END//
delimiter ;//

CREATE FUNCTION sp1 () returns int return 2.2;
CALL db_storedproc.sp1();
SELECT db_storedproc.sp1();

DROP DATABASE IF EXISTS db1;
CREATE DATABASE db1;
USE db1;
alter procedure db_storedproc.sp1 sql security invoker;
SELECT name, type, security_type from mysql.proc where db LIKE 'db_storedproc%' and specific_name='sp1';

alter function db_storedproc.sp1 sql security invoker;
SELECT name, type, security_type from mysql.proc where db LIKE 'db_storedproc%' and specific_name='sp1';

CALL db_storedproc.sp1();

SELECT db_storedproc.sp1();

USE db_storedproc;
alter procedure sp1 sql security definer;
CALL db_storedproc.sp1();

SELECT db_storedproc.sp1();

alter function sp1 sql security definer;

SELECT name, type, security_type from mysql.proc where db LIKE 'db_storedproc%' and specific_name='sp1';
CALL db_storedproc.sp1();

SELECT db_storedproc.sp1();

# cleanup
USE db_storedproc;
DROP DATABASE db1;
DROP PROCEDURE db_storedproc.sp1;
DROP FUNCTION db_storedproc.sp1;

# ------------------------------------------------------------------------------
let $message= Testcase 4.1.19:
              ----------------
verify altering procedure and function with the same name, does not affect
properties of a procedure and a function with the same name in the different
database.;
--source include/show_msg80.inc


--disable_warnings
DROP DATABASE IF EXISTS db_storedproc_3122;
--enable_warnings

CREATE DATABASE db_storedproc_3122;
USE db_storedproc;
set @x=null;
set @y=null;
DROP PROCEDURE IF EXISTS sp1;
DROP FUNCTION IF EXISTS sp1;
DROP PROCEDURE IF EXISTS db_storedproc_3122.sp1;
DROP FUNCTION IF EXISTS db_storedproc_3122.sp1;
delimiter //;
CREATE PROCEDURE sp1()
BEGIN
    set @x= 1;
    SELECT @x;
END//
delimiter ;//

# FIXME ps-protocol vs. normal difference when returning float instead of double
CREATE FUNCTION db_storedproc_3122.sp1() returns double return 2.2;
CALL sp1();
  SELECT db_storedproc_3122.sp1();
  USE db_storedproc_3122;

delimiter //;
CREATE PROCEDURE sp1 ()
BEGIN
    set @x= 3;
    SELECT @x;
END//
delimiter ;//

CREATE FUNCTION db_storedproc.sp1() returns double return 4.4;
CALL sp1();
SELECT db_storedproc.sp1();

alter procedure db_storedproc_3122.sp1 sql security invoker;
alter function sp1 sql security invoker;

SELECT db, name, type, security_type from mysql.proc where db LIKE 'db_storedproc%' and specific_name='sp1';

CALL db_storedproc.sp1();

SELECT db_storedproc.sp1();
CALL db_storedproc_3122.sp1();
SELECT db_storedproc_3122.sp1();

# clean up
USE db_storedproc;
DROP DATABASE db_storedproc_3122;
DROP FUNCTION db_storedproc.sp1;
DROP PROCEDURE db_storedproc.sp1;


# ------------------------------------------------------------------------------
let $message= Testcase 4.1.20:
              ----------------
Ensure that it is possible to alter the comment of a procedure
and a function with the same name, even in the same database;
--source include/show_msg80.inc


USE db_storedproc;
set @x=null;

--disable_warnings
DROP PROCEDURE IF EXISTS sp1;
DROP FUNCTION IF EXISTS sp1;
--enable_warnings

CREATE PROCEDURE sp1 () set @x= 1;
CREATE FUNCTION sp1 () returns int return 2;

DROP DATABASE IF EXISTS db_storedproc_3122;
CREATE DATABASE db_storedproc_3122;
USE db_storedproc_3122;

CREATE PROCEDURE sp1 () set @x= 3;
CREATE FUNCTION sp1 () returns int return 4;

alter procedure sp1 sql security invoker comment 'this is a procedure';
alter function sp1 sql security invoker comment 'this is a function';

alter procedure sp1 sql security definer;
alter function sp1 sql security definer;
--replace_column 5 modified 6 created
show CREATE PROCEDURE sp1;
--replace_column 5 modified 6 created
show CREATE FUNCTION sp1;

# clean up
USE db_storedproc;
DROP DATABASE db_storedproc_3122;
DROP FUNCTION db_storedproc.sp1;
DROP PROCEDURE db_storedproc.sp1;


# ------------------------------------------------------------------------------
let $message= Testcase 4.1.21:
              ----------------
Ensure that it is not possible to create two procedures with same name
in same database;
--source include/show_msg80.inc

USE db_storedproc;
set @x=null;
set @y=null;

--disable_warnings
DROP DATABASE IF EXISTS db1;
--enable_warnings

CREATE DATABASE db1;
DROP PROCEDURE IF EXISTS sp1;
CREATE PROCEDURE sp1 () set @x=1;

--error 1304
CREATE PROCEDURE sp1 () set @x=2;

CALL sp1();
SELECT @x;
USE db1;
--error 1304
CREATE PROCEDURE db_storedproc.sp1 () set @x=3;
CALL db_storedproc.sp1();
  SELECT @x;

DROP PROCEDURE IF EXISTS db_storedproc.sp1;
CREATE PROCEDURE db_storedproc.sp1 () set @x=1;
--error 1304
CREATE PROCEDURE db_storedproc.sp1 () set @x=2;
CALL db_storedproc.sp1();
SELECT @x;

# clean up
USE db_storedproc;
DROP DATABASE db1;
DROP PROCEDURE db_storedproc.sp1;



# ------------------------------------------------------------------------------
let $message= Testcase 4.1.22:
              ----------------
Ensure that it is not possible to create two functions with same name in the
same database;
--source include/show_msg80.inc

USE db_storedproc;
DROP DATABASE IF EXISTS db1;
CREATE DATABASE db1;
DROP FUNCTION IF EXISTS fn1;
CREATE FUNCTION fn1 () returns int return 1;
--error 1304
CREATE FUNCTION fn1 () returns int return 2;
SELECT fn1();
USE db1;
--error 1304
CREATE FUNCTION db_storedproc.fn1 () returns int return 3;
SELECT db_storedproc.fn1();
DROP FUNCTION IF EXISTS db_storedproc.fn1;
CREATE FUNCTION db_storedproc.fn1 () returns int return 1;
--error 1304
CREATE FUNCTION db_storedproc.fn1 () returns int return 2;
SELECT db_storedproc.fn1();

# clean up
USE db_storedproc;
DROP DATABASE db1;
DROP FUNCTION db_storedproc.fn1;


# ------------------------------------------------------------------------------
let $message= Testcase 4.1.23:
              ----------------
Ensure that it is possible to create two or more procedures with the same name,
providing each resides in different databases;
--source include/show_msg80.inc


USE db_storedproc;
set @x=null;
set @y=null;
DROP PROCEDURE IF EXISTS sp1;
CREATE PROCEDURE sp1 () set @x= 1;
DROP DATABASE IF EXISTS test3124;
CREATE DATABASE test3124;
USE test3124;
CREATE PROCEDURE sp1 () set @y= 2;
CALL sp1();
SELECT @x, @y;
USE db_storedproc;
CALL sp1();
SELECT @x, @y;

# clean up
USE db_storedproc;
DROP DATABASE test3124;
DROP PROCEDURE db_storedproc.sp1;


# ------------------------------------------------------------------------------
let $message= Testcase 4.1.24:
              ----------------
Ensure that it is possible to create two or more functions with the same name,
providing each resides in different databases.;
--source include/show_msg80.inc


USE db_storedproc;
DROP FUNCTION IF EXISTS f1;
CREATE FUNCTION f1 () returns int return 1;
DROP DATABASE IF EXISTS test3125;
CREATE DATABASE test3125;
USE test3125; CREATE FUNCTION f1 () returns int return 2;
SELECT f1();
USE db_storedproc;
SELECT f1();

# clean up
USE db_storedproc;
DROP DATABASE test3125;
DROP FUNCTION db_storedproc.f1;


# ------------------------------------------------------------------------------
let $message= Testcase 4.1.25:
              ----------------
Ensure that any invalid function name is never accepted, and that an appropriate
error message is returned when the name is rejected. (invalid func name);
--source include/show_msg80.inc

delimiter //;
--error 1064
CREATE FUNCTION !_fn1( f1 char(20) ) returns int
BEGIN
   SELECT * from t1 where f2 = f1;
   return