storedproc_master.inc

来自「视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.」· INC 代码 · 共 2,828 行 · 第 1/5 页

              Ensure that all clauses that should be supported are supported
              SHOW CREATE PROC;
--source include/show_msg80.inc

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

CREATE PROCEDURE sp1 (f1 char(20) )
    SELECT * from t1 where f2 = f1;

--replace_column 5 modified 6 created
show CREATE PROCEDURE sp1;

# cleanup
DROP PROCEDURE sp1;

# ------------------------------------------------------------------------------
let $message= Testcase 4.1.4:
              ---------------
show create function;
--source include/show_msg80.inc

--disable_warnings
DROP FUNCTION IF EXISTS fn1;
--enable_warnings

CREATE FUNCTION fn1 (s char(20)) returns char(50)
   return concat('hello, ', s, '!');

--replace_column 5 modified 6 created
show CREATE FUNCTION fn1;

# cleanup
DROP FUNCTION fn1;

# ------------------------------------------------------------------------------
let $message= Testcase 4.1.5:
              ---------------
SHOW PROCEDURE status;
--source include/show_msg80.inc


CREATE PROCEDURE sp5()
   SELECT * from t1;

--replace_column 5 modified 6 created
SHOW PROCEDURE status like 'sp5';

# cleanup
DROP PROCEDURE sp5;

# ------------------------------------------------------------------------------
let $message= Testcase 4.1.6:
              ---------------
show function status;
--source include/show_msg80.inc


delimiter //;
CREATE FUNCTION fn5(a int) returns int
BEGIN
   set @b = 0.9 * a;
   return @b;
END//
delimiter ;//

--replace_column 5 modified 6 created
SHOW FUNCTION STATUS LIKE 'fn5';

# cleanup
DROP FUNCTION fn5;

# ------------------------------------------------------------------------------
let $message= Testcase 4.1.7:
              ---------------
CALL procedure;
--source include/show_msg80.inc

--disable_warnings
DROP PROCEDURE IF EXISTS sp7a;
DROP PROCEDURE IF EXISTS sp7b;
DROP PROCEDURE IF EXISTS sp7c;
--enable_warnings

CREATE PROCEDURE sp7a(a char(20))
    SELECT * from t1 where t1.f2 = a;

CALL sp7a( 'xyz' );

CREATE PROCEDURE sp7b (a char (20), out b char(20))
    SELECT f1 into b from t1 where t1.f2= a;

CALL sp7b('xyz', @out_param);
SELECT @out_param;

delimiter //;
CREATE PROCEDURE sp7c (a char (20), out b char(20), inout c int)
BEGIN
SELECT f1 into b from t1 where t1.f2=a;
   update t1 set t1.f2=999 where t1.f4=c;
SELECT f2 into c from t1 where t1.f2=999;
END//
delimiter ;//

set @c=1;
CALL sp7c('xyz', @out_param, @c);
SELECT @out_param;
SELECT @c;

# cleanup
DROP PROCEDURE sp7a;
DROP PROCEDURE sp7b;
DROP PROCEDURE sp7c;


# ------------------------------------------------------------------------------
let $message= Testcase 4.1.8:
              ---------------
calling function;
--source include/show_msg80.inc


CREATE FUNCTION fn8(a char(20)) returns char(50)
return concat('hello, ', a, '!');
SELECT fn8('world');

# cleanup
DROP FUNCTION fn8;

# ------------------------------------------------------------------------------
let $message= Testcase 4.1.9:
              ---------------
drop procedure;
--source include/show_msg80.inc


--replace_column 13 created 14 modified
SELECT * from mysql.proc where specific_name='sp9';

--disable_warnings
DROP PROCEDURE IF EXISTS sp9;
--enable_warnings

--replace_column 13 created 14 modified
SELECT * from mysql.proc where specific_name='sp9';

CREATE PROCEDURE sp9()SELECT * from t1;

--replace_column 13 created 14 modified
SELECT * from mysql.proc where specific_name='sp9';

DROP PROCEDURE sp9;

--replace_column 13 created 14 modified
SELECT * from mysql.proc where specific_name='sp9';

CREATE PROCEDURE sp9()SELECT * from t1;

--replace_column 13 created 14 modified
SELECT * from mysql.proc where specific_name='sp9';

DROP PROCEDURE IF EXISTS sp9;

--replace_column 13 created 14 modified
SELECT * from mysql.proc where specific_name='sp9';



# ------------------------------------------------------------------------------
let $message= Testcase 4.1.10:
              ----------------
DROP FUNCTION;
--source include/show_msg80.inc

--replace_column 13 created 14 modified
SELECT * from mysql.proc where specific_name='fn10' and type='function';

--disable_warnings
DROP FUNCTION IF EXISTS fn10;
--enable_warnings

--replace_column 13 created 14 modified
SELECT * from mysql.proc where specific_name='fn10' and type='function';


CREATE FUNCTION fn10() returns int return 100;

--replace_column 13 created 14 modified
SELECT * from mysql.proc where specific_name='fn10' and type='function';

DROP FUNCTION fn10;

--replace_column 13 created 14 modified
SELECT * from mysql.proc where specific_name='fn10' and type='function';

CREATE FUNCTION fn10() returns int return 100;

--replace_column 13 created 14 modified
SELECT * from mysql.proc where specific_name='fn10' and type='function';

DROP FUNCTION IF EXISTS fn10;

--replace_column 13 created 14 modified
SELECT * from mysql.proc where specific_name='fn10' and type='function';


# ------------------------------------------------------------------------------
let $message= Testcase 4.1.11:
              ----------------
alter proc;
--source include/show_msg80.inc


create user 'user_1'@'localhost';
grant execute on db_storedproc.* to 'user_1'@'localhost';
flush privileges;
drop table IF EXISTS mysql.t1;
create table mysql.t1( f1 char );
DROP PROCEDURE IF EXISTS sp11;
CREATE PROCEDURE sp11() insert into mysql.t1 values('a');
--replace_column 13 created 14 modified
SELECT security_type from mysql.proc where specific_name='sp11';

--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
connect (u_1, localhost, user_1, , db_storedproc);
--source suite/funcs_1/include/show_connection.inc

CALL sp11();

connection default;
--source suite/funcs_1/include/show_connection.inc

alter procedure sp11 sql security invoker;
--replace_column 13 created 14 modified
SELECT security_type from mysql.proc where specific_name='sp11';

--replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
connection u_1;
--source suite/funcs_1/include/show_connection.inc

--error 1142
CALL sp11();

commit work;
disconnect u_1;
connection default;
--source suite/funcs_1/include/show_connection.inc

alter procedure sp11 sql security definer;
--replace_column 13 created 14 modified
SELECT security_type from mysql.proc where specific_name='sp11';
CALL sp11();

# cleanup
DROP USER 'user_1'@'localhost';
DROP PROCEDURE sp11;
drop table mysql.t1;


# ------------------------------------------------------------------------------
let $message= Testcase 4.1.12:
              ----------------
alter function;
--source include/show_msg80.inc


CREATE FUNCTION fn12() returns int
 return 100;
SELECT security_type from mysql.proc where specific_name='fn12';
--replace_column 13 created 14 modified
SELECT fn12();

alter function fn12 sql security invoker;
SELECT security_type from mysql.proc where specific_name='fn12';
--replace_column 13 created 14 modified
SELECT fn12();

alter function fn12 sql security definer;
SELECT security_type from mysql.proc where specific_name='fn12';
--replace_column 13 created 14 modified
SELECT fn12();

# cleanup
DROP FUNCTION fn12;


# ------------------------------------------------------------------------------
let $message= Testcase 4.1.13:
              ----------------
alter proc;
--source include/show_msg80.inc


DROP PROCEDURE IF EXISTS sp11;
CREATE PROCEDURE sp11()
   SELECT * from t1;

SELECT comment from mysql.proc where specific_name='sp11';
--replace_column 13 created 14 modified
alter procedure sp11 comment 'this is simple';
SELECT comment from mysql.proc where specific_name='sp11';
--replace_column 13 created 14 modified

# cleanup
DROP PROCEDURE sp11;


# ------------------------------------------------------------------------------
let $message= Testcase 4.1.14:
              ----------------
alter function;
--source include/show_msg80.inc

DROP FUNCTION IF EXISTS fn12;
CREATE FUNCTION fn12() returns int
    return 100;
  SELECT comment from mysql.proc where specific_name='fn12';
--replace_column 13 created 14 modified

  alter function fn12 comment 'this is simple';
  SELECT comment from mysql.proc where specific_name='fn12';
--replace_column 13 created 14 modified

# cleanup
DROP FUNCTION fn12;


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


--error 1357
CREATE PROCEDURE sp1()
DROP PROCEDURE sp1;

--error 1064
CREATE PROCEDURE !_sp1( f1 char(20) )
SELECT * from t1 where f2 = f1;

#FIXME: this was what I found in this file ... :
#CREATE PROCEDURE !_sp1( f1 char(20) )
#    SELECT * from t1 where f2 = f1; ^a^kcd '/usr/mysql/mysql-test/t'
#/t'ql/mysql-test
#t]# t@localhost t

#FIXME check this is OK:--error 1064
CREATE PROCEDURE function()
   SELECT * from t1 where f2=f1;
DROP PROCEDURE function;

--error 1064
CREATE PROCEDURE add()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE all()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE alter()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE analyze()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE and()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE as()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE asc()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE asensitive()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE before()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE between()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE bigint()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE binary()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE blob()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE both()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE by()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE CALL()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE cascade()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE case()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE change()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE char()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE character()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE check()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE collate()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE column()
   SELECT * from t1 where f2=f1;

#FIXME check this is OK:--error 1064
CREATE PROCEDURE columns()
   SELECT * from t1 where f2=f1;
DROP PROCEDURE columns;

--error 1064
CREATE PROCEDURE condition()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE connection()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE constraint()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE continue()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE convert()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE create()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE cross()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE current_date()
  SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE current_time()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE current_timestamp()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE current_user()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE cursor()
  SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE database()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE databases()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE day_hour()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE day_microsecond()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE day_minute()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE day_second()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE dec()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE decimal()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE declare()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE default()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE delayed()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE delete()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE desc()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE describe()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE deterministic()
   SELECT * from t1 where f2=f1;

--error 1064
CREATE PROCEDURE distinct()