memory_storedproc_08.result

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

return i4;
END
EXTERNAL_NAME	NULL
EXTERNAL_LANGUAGE	NULL
PARAMETER_STYLE	SQL
IS_DETERMINISTIC	NO
SQL_DATA_ACCESS	MODIFIES SQL DATA
SQL_PATH	NULL
SECURITY_TYPE	DEFINER
CREATED	<modified>
LAST_ALTERED	<created>
SQL_MODE	
ROUTINE_COMMENT	FN changed to DEFINER
DEFINER	root@localhost
SPECIFIC_NAME	sp_1
ROUTINE_CATALOG	NULL
ROUTINE_SCHEMA	db_storedproc
ROUTINE_NAME	sp_1
ROUTINE_TYPE	PROCEDURE
DTD_IDENTIFIER	NULL
ROUTINE_BODY	SQL
ROUTINE_DEFINITION	BEGIN
set @x=i1;
END
EXTERNAL_NAME	NULL
EXTERNAL_LANGUAGE	NULL
PARAMETER_STYLE	SQL
IS_DETERMINISTIC	NO
SQL_DATA_ACCESS	CONTAINS SQL
SQL_PATH	NULL
SECURITY_TYPE	INVOKER
CREATED	<modified>
LAST_ALTERED	<created>
SQL_MODE	
ROUTINE_COMMENT	new comment, SP changed to INVOKER
DEFINER	root@localhost
SPECIFIC_NAME	sp_2
ROUTINE_CATALOG	NULL
ROUTINE_SCHEMA	db_storedproc
ROUTINE_NAME	sp_2
ROUTINE_TYPE	PROCEDURE
DTD_IDENTIFIER	NULL
ROUTINE_BODY	SQL
ROUTINE_DEFINITION	BEGIN
set @x=i1;
END
EXTERNAL_NAME	NULL
EXTERNAL_LANGUAGE	NULL
PARAMETER_STYLE	SQL
IS_DETERMINISTIC	NO
SQL_DATA_ACCESS	READS SQL DATA
SQL_PATH	NULL
SECURITY_TYPE	DEFINER
CREATED	<modified>
LAST_ALTERED	<created>
SQL_MODE	
ROUTINE_COMMENT	SP changed to DEFINER
DEFINER	root@localhost
SHOW CREATE FUNCTION  fn_1;
Function	fn_1
sql_mode	
Create Function	CREATE DEFINER=`root`@`localhost` FUNCTION `fn_1`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4)
    NO SQL
    SQL SECURITY INVOKER
    COMMENT 'new comment, FN changed to INVOKER'
BEGIN
set @x=i1;
set @y=@x;
return i4;
END
SHOW CREATE FUNCTION  fn_2;
Function	fn_2
sql_mode	
Create Function	CREATE DEFINER=`root`@`localhost` FUNCTION `fn_2`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4)
    MODIFIES SQL DATA
    COMMENT 'FN changed to DEFINER'
BEGIN
set @x=i1;
set @y=@x;
return i4;
END
SHOW CREATE PROCEDURE sp_1;
Procedure	sp_1
sql_mode	
Create Procedure	CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_1`(i1 int)
    SQL SECURITY INVOKER
    COMMENT 'new comment, SP changed to INVOKER'
BEGIN
set @x=i1;
END
SHOW CREATE PROCEDURE sp_2;
Procedure	sp_2
sql_mode	
Create Procedure	CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_2`(i1 int)
    READS SQL DATA
    COMMENT 'SP changed to DEFINER'
BEGIN
set @x=i1;
END
SHOW FUNCTION STATUS LIKE 'fn_%';
Db	db_storedproc
Name	fn_1
Type	FUNCTION
Definer	root@localhost
Modified	<modified>
Created	<created>
Security_type	INVOKER
Comment	new comment, FN changed to INVOKER
Db	db_storedproc
Name	fn_2
Type	FUNCTION
Definer	root@localhost
Modified	<modified>
Created	<created>
Security_type	DEFINER
Comment	FN changed to DEFINER
SHOW PROCEDURE STATUS LIKE 'sp_%';
Db	db_storedproc
Name	sp_1
Type	PROCEDURE
Definer	root@localhost
Modified	<modified>
Created	<created>
Security_type	INVOKER
Comment	new comment, SP changed to INVOKER
Db	db_storedproc
Name	sp_2
Type	PROCEDURE
Definer	root@localhost
Modified	<modified>
Created	<created>
Security_type	DEFINER
Comment	SP changed to DEFINER

... change back to default and check result:
--------------------------------------------
ALTER FUNCTION  fn_2 CONTAINS SQL;

... now check what is stored:
-----------------------------
SELECT * FROM information_schema.routines;
SPECIFIC_NAME	fn_1
ROUTINE_CATALOG	NULL
ROUTINE_SCHEMA	db_storedproc
ROUTINE_NAME	fn_1
ROUTINE_TYPE	FUNCTION
DTD_IDENTIFIER	year(4)
ROUTINE_BODY	SQL
ROUTINE_DEFINITION	BEGIN
set @x=i1;
set @y=@x;
return i4;
END
EXTERNAL_NAME	NULL
EXTERNAL_LANGUAGE	NULL
PARAMETER_STYLE	SQL
IS_DETERMINISTIC	NO
SQL_DATA_ACCESS	NO SQL
SQL_PATH	NULL
SECURITY_TYPE	INVOKER
CREATED	<modified>
LAST_ALTERED	<created>
SQL_MODE	
ROUTINE_COMMENT	new comment, FN changed to INVOKER
DEFINER	root@localhost
SPECIFIC_NAME	fn_2
ROUTINE_CATALOG	NULL
ROUTINE_SCHEMA	db_storedproc
ROUTINE_NAME	fn_2
ROUTINE_TYPE	FUNCTION
DTD_IDENTIFIER	year(4)
ROUTINE_BODY	SQL
ROUTINE_DEFINITION	BEGIN
set @x=i1;
set @y=@x;
return i4;
END
EXTERNAL_NAME	NULL
EXTERNAL_LANGUAGE	NULL
PARAMETER_STYLE	SQL
IS_DETERMINISTIC	NO
SQL_DATA_ACCESS	CONTAINS SQL
SQL_PATH	NULL
SECURITY_TYPE	DEFINER
CREATED	<modified>
LAST_ALTERED	<created>
SQL_MODE	
ROUTINE_COMMENT	FN changed to DEFINER
DEFINER	root@localhost
SPECIFIC_NAME	sp_1
ROUTINE_CATALOG	NULL
ROUTINE_SCHEMA	db_storedproc
ROUTINE_NAME	sp_1
ROUTINE_TYPE	PROCEDURE
DTD_IDENTIFIER	NULL
ROUTINE_BODY	SQL
ROUTINE_DEFINITION	BEGIN
set @x=i1;
END
EXTERNAL_NAME	NULL
EXTERNAL_LANGUAGE	NULL
PARAMETER_STYLE	SQL
IS_DETERMINISTIC	NO
SQL_DATA_ACCESS	CONTAINS SQL
SQL_PATH	NULL
SECURITY_TYPE	INVOKER
CREATED	<modified>
LAST_ALTERED	<created>
SQL_MODE	
ROUTINE_COMMENT	new comment, SP changed to INVOKER
DEFINER	root@localhost
SPECIFIC_NAME	sp_2
ROUTINE_CATALOG	NULL
ROUTINE_SCHEMA	db_storedproc
ROUTINE_NAME	sp_2
ROUTINE_TYPE	PROCEDURE
DTD_IDENTIFIER	NULL
ROUTINE_BODY	SQL
ROUTINE_DEFINITION	BEGIN
set @x=i1;
END
EXTERNAL_NAME	NULL
EXTERNAL_LANGUAGE	NULL
PARAMETER_STYLE	SQL
IS_DETERMINISTIC	NO
SQL_DATA_ACCESS	READS SQL DATA
SQL_PATH	NULL
SECURITY_TYPE	DEFINER
CREATED	<modified>
LAST_ALTERED	<created>
SQL_MODE	
ROUTINE_COMMENT	SP changed to DEFINER
DEFINER	root@localhost
SHOW CREATE FUNCTION  fn_1;
Function	fn_1
sql_mode	
Create Function	CREATE DEFINER=`root`@`localhost` FUNCTION `fn_1`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4)
    NO SQL
    SQL SECURITY INVOKER
    COMMENT 'new comment, FN changed to INVOKER'
BEGIN
set @x=i1;
set @y=@x;
return i4;
END
SHOW CREATE FUNCTION  fn_2;
Function	fn_2
sql_mode	
Create Function	CREATE DEFINER=`root`@`localhost` FUNCTION `fn_2`(i1 longtext, i2 mediumint , i3 longblob, i4 year, i5 real) RETURNS year(4)
    COMMENT 'FN changed to DEFINER'
BEGIN
set @x=i1;
set @y=@x;
return i4;
END
SHOW CREATE PROCEDURE sp_1;
Procedure	sp_1
sql_mode	
Create Procedure	CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_1`(i1 int)
    SQL SECURITY INVOKER
    COMMENT 'new comment, SP changed to INVOKER'
BEGIN
set @x=i1;
END
SHOW CREATE PROCEDURE sp_2;
Procedure	sp_2
sql_mode	
Create Procedure	CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_2`(i1 int)
    READS SQL DATA
    COMMENT 'SP changed to DEFINER'
BEGIN
set @x=i1;
END
SHOW FUNCTION STATUS LIKE 'fn_%';
Db	db_storedproc
Name	fn_1
Type	FUNCTION
Definer	root@localhost
Modified	<modified>
Created	<created>
Security_type	INVOKER
Comment	new comment, FN changed to INVOKER
Db	db_storedproc
Name	fn_2
Type	FUNCTION
Definer	root@localhost
Modified	<modified>
Created	<created>
Security_type	DEFINER
Comment	FN changed to DEFINER
SHOW PROCEDURE STATUS LIKE 'sp_%';
Db	db_storedproc
Name	sp_1
Type	PROCEDURE
Definer	root@localhost
Modified	<modified>
Created	<created>
Security_type	INVOKER
Comment	new comment, SP changed to INVOKER
Db	db_storedproc
Name	sp_2
Type	PROCEDURE
Definer	root@localhost
Modified	<modified>
Created	<created>
Security_type	DEFINER
Comment	SP changed to DEFINER

... cleanup
-----------
DROP FUNCTION fn_1;
DROP FUNCTION fn_2;
DROP PROCEDURE sp_1;

--source suite/funcs_1/storedproc/cleanup_sp_tb.inc
--------------------------------------------------------------------------------
DROP DATABASE IF EXISTS db_storedproc;
DROP DATABASE IF EXISTS db_storedproc_1;

.                               +++ END OF SCRIPT +++
--------------------------------------------------------------------------------