innodb_storedproc.result

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

Warning	1329	No data - zero rows fetched, selected, or processed
SELECT @out_param;
@out_param
NULL
SELECT @c;
@c
1
DROP PROCEDURE sp7a;
DROP PROCEDURE sp7b;
DROP PROCEDURE sp7c;

Testcase 4.1.8:
---------------
calling function
--------------------------------------------------------------------------------
CREATE FUNCTION fn8(a char(20)) returns char(50)
return concat('hello, ', a, '!');
SELECT fn8('world');
fn8('world')
hello, world!
DROP FUNCTION fn8;

Testcase 4.1.9:
---------------
drop procedure
--------------------------------------------------------------------------------
SELECT * from mysql.proc where specific_name='sp9';
db	name	type	specific_name	language	sql_data_access	is_deterministic	security_type	param_list	returns	body	definer	created	modified	sql_mode	comment
DROP PROCEDURE IF EXISTS sp9;
SELECT * from mysql.proc where specific_name='sp9';
db	name	type	specific_name	language	sql_data_access	is_deterministic	security_type	param_list	returns	body	definer	created	modified	sql_mode	comment
CREATE PROCEDURE sp9()SELECT * from t1;
SELECT * from mysql.proc where specific_name='sp9';
db	name	type	specific_name	language	sql_data_access	is_deterministic	security_type	param_list	returns	body	definer	created	modified	sql_mode	comment
db_storedproc	sp9	PROCEDURE	sp9	SQL	CONTAINS_SQL	NO	DEFINER			SELECT * from t1	root@localhost	created	modified		
DROP PROCEDURE sp9;
SELECT * from mysql.proc where specific_name='sp9';
db	name	type	specific_name	language	sql_data_access	is_deterministic	security_type	param_list	returns	body	definer	created	modified	sql_mode	comment
CREATE PROCEDURE sp9()SELECT * from t1;
SELECT * from mysql.proc where specific_name='sp9';
db	name	type	specific_name	language	sql_data_access	is_deterministic	security_type	param_list	returns	body	definer	created	modified	sql_mode	comment
db_storedproc	sp9	PROCEDURE	sp9	SQL	CONTAINS_SQL	NO	DEFINER			SELECT * from t1	root@localhost	created	modified		
DROP PROCEDURE IF EXISTS sp9;
SELECT * from mysql.proc where specific_name='sp9';
db	name	type	specific_name	language	sql_data_access	is_deterministic	security_type	param_list	returns	body	definer	created	modified	sql_mode	comment

Testcase 4.1.10:
----------------
DROP FUNCTION
--------------------------------------------------------------------------------
SELECT * from mysql.proc where specific_name='fn10' and type='function';
db	name	type	specific_name	language	sql_data_access	is_deterministic	security_type	param_list	returns	body	definer	created	modified	sql_mode	comment
DROP FUNCTION IF EXISTS fn10;
SELECT * from mysql.proc where specific_name='fn10' and type='function';
db	name	type	specific_name	language	sql_data_access	is_deterministic	security_type	param_list	returns	body	definer	created	modified	sql_mode	comment
CREATE FUNCTION fn10() returns int return 100;
SELECT * from mysql.proc where specific_name='fn10' and type='function';
db	name	type	specific_name	language	sql_data_access	is_deterministic	security_type	param_list	returns	body	definer	created	modified	sql_mode	comment
db_storedproc	fn10	FUNCTION	fn10	SQL	CONTAINS_SQL	NO	DEFINER		int(11)	return 100	root@localhost	created	modified		
DROP FUNCTION fn10;
SELECT * from mysql.proc where specific_name='fn10' and type='function';
db	name	type	specific_name	language	sql_data_access	is_deterministic	security_type	param_list	returns	body	definer	created	modified	sql_mode	comment
CREATE FUNCTION fn10() returns int return 100;
SELECT * from mysql.proc where specific_name='fn10' and type='function';
db	name	type	specific_name	language	sql_data_access	is_deterministic	security_type	param_list	returns	body	definer	created	modified	sql_mode	comment
db_storedproc	fn10	FUNCTION	fn10	SQL	CONTAINS_SQL	NO	DEFINER		int(11)	return 100	root@localhost	created	modified		
DROP FUNCTION IF EXISTS fn10;
SELECT * from mysql.proc where specific_name='fn10' and type='function';
db	name	type	specific_name	language	sql_data_access	is_deterministic	security_type	param_list	returns	body	definer	created	modified	sql_mode	comment

Testcase 4.1.11:
----------------
alter proc
--------------------------------------------------------------------------------
create user 'user_1'@'localhost';
grant execute on db_storedproc.* to 'user_1'@'localhost';
flush privileges;
drop table IF EXISTS mysql.t1;
Warnings:
Note	1051	Unknown table 't1'
create table mysql.t1( f1 char );
DROP PROCEDURE IF EXISTS sp11;
Warnings:
Note	1305	PROCEDURE sp11 does not exist
CREATE PROCEDURE sp11() insert into mysql.t1 values('a');
SELECT security_type from mysql.proc where specific_name='sp11';
security_type
DEFINER
connect(localhost,user_1,,db_storedproc,MYSQL_PORT,MYSQL_SOCK);
	
user_1@localhost	db_storedproc
CALL sp11();
	
root@localhost	db_storedproc
alter procedure sp11 sql security invoker;
SELECT security_type from mysql.proc where specific_name='sp11';
security_type
INVOKER
	
user_1@localhost	db_storedproc
CALL sp11();
ERROR 42000: INSERT command denied to user 'user_1'@'localhost' for table 't1'
commit work;
	
root@localhost	db_storedproc
alter procedure sp11 sql security definer;
SELECT security_type from mysql.proc where specific_name='sp11';
security_type
DEFINER
CALL sp11();
DROP USER 'user_1'@'localhost';
DROP PROCEDURE sp11;
drop table mysql.t1;

Testcase 4.1.12:
----------------
alter function
--------------------------------------------------------------------------------
CREATE FUNCTION fn12() returns int
return 100;
SELECT security_type from mysql.proc where specific_name='fn12';
security_type
DEFINER
SELECT fn12();
fn12()
100
alter function fn12 sql security invoker;
SELECT security_type from mysql.proc where specific_name='fn12';
security_type
INVOKER
SELECT fn12();
fn12()
100
alter function fn12 sql security definer;
SELECT security_type from mysql.proc where specific_name='fn12';
security_type
DEFINER
SELECT fn12();
fn12()
100
DROP FUNCTION fn12;

Testcase 4.1.13:
----------------
alter proc
--------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp11;
Warnings:
Note	1305	PROCEDURE sp11 does not exist
CREATE PROCEDURE sp11()
SELECT * from t1;
SELECT comment from mysql.proc where specific_name='sp11';
comment

alter procedure sp11 comment 'this is simple';
SELECT comment from mysql.proc where specific_name='sp11';
comment
this is simple
DROP PROCEDURE sp11;

Testcase 4.1.14:
----------------
alter function
--------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS fn12;
Warnings:
Note	1305	FUNCTION fn12 does not exist
CREATE FUNCTION fn12() returns int
return 100;
SELECT comment from mysql.proc where specific_name='fn12';
comment

alter function fn12 comment 'this is simple';
SELECT comment from mysql.proc where specific_name='fn12';
comment
this is simple
DROP FUNCTION fn12;

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
--------------------------------------------------------------------------------
CREATE PROCEDURE sp1()
DROP PROCEDURE sp1;
ERROR HY000: Can't drop or alter a PROCEDURE from within another stored routine
CREATE PROCEDURE !_sp1( f1 char(20) )
SELECT * from t1 where f2 = f1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '!_sp1( f1 char(20) )
SELECT * from t1 where f2 = f1' at line 1
CREATE PROCEDURE function()
SELECT * from t1 where f2=f1;
DROP PROCEDURE function;
CREATE PROCEDURE add()
SELECT * from t1 where f2=f1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'add()
SELECT * from t1 where f2=f1' at line 1
CREATE PROCEDURE all()
SELECT * from t1 where f2=f1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'all()
SELECT * from t1 where f2=f1' at line 1
CREATE PROCEDURE alter()
SELECT * from t1 where f2=f1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'alter()
SELECT * from t1 where f2=f1' at line 1
CREATE PROCEDURE analyze()
SELECT * from t1 where f2=f1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'analyze()
SELECT * from t1 where f2=f1' at line 1
CREATE PROCEDURE and()
SELECT * from t1 where f2=f1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'and()
SELECT * from t1 where f2=f1' at line 1
CREATE PROCEDURE as()
SELECT * from t1 where f2=f1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'as()
SELECT * from t1 where f2=f1' at line 1
CREATE PROCEDURE asc()
SELECT * from t1 where f2=f1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'asc()
SELECT * from t1 where f2=f1' at line 1
CREATE PROCEDURE asensitive()
SELECT * from t1 where f2=f1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'asensitive()
SELECT * from t1 where f2=f1' at line 1
CREATE PROCEDURE before()
SELECT * from t1 where f2=f1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'before()
SELECT * from t1 where f2=f1' at line 1
CREATE PROCEDURE between()
SELECT * from t1 where f2=f1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'between()
SELECT * from t1 where f2=f1' at line 1
CREATE PROCEDURE bigint()
SELECT * from t1 where f2=f1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'bigint()
SELECT * from t1 where f2=f1' at line 1
CREATE PROCEDURE binary()
SELECT * from t1 where f2=f1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'binary()
SELECT * from t1 where f2=f1' at line 1
CREATE PROCEDURE blob()
SELECT * from t1 where f2=f1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'blob()
SELECT * from t1 where f2=f1' at line 1
CREATE PROCEDURE both()
SELECT * from t1 where f2=f1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'both()
SELECT * from t1 where f2=f1' at line 1
CREATE PROCEDURE by()
SELECT * from t1 where f2=f1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'by()
SELECT * from t1 where f2=f1' at line 1
CREATE PROCEDURE CALL()
SELECT * from t1 where f2=f1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CALL()
SELECT * from t1 where f2=f1' at line 1
CREATE PROCEDURE cascade()
SELECT * from t1 where f2=f1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'cascade()
SELECT * from t1 where f2=f1' at line 1
CREATE PROCEDURE case()
SELECT * from t1 where f2=f1;
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'case()
SELECT * from t1 where f2=f1' at line 1