is_routines_embedded.result

这个文件是windows mysql源码

SHOW TABLES FROM information_schema LIKE 'ROUTINES';
Tables_in_information_schema (ROUTINES)
ROUTINES
#######################################################################
# Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
#######################################################################
DROP VIEW      IF EXISTS test.v1;
DROP PROCEDURE IF EXISTS test.p1;
DROP FUNCTION  IF EXISTS test.f1;
CREATE VIEW test.v1 AS     SELECT * FROM information_schema.ROUTINES;
CREATE PROCEDURE test.p1() SELECT * FROM information_schema.ROUTINES;
CREATE FUNCTION test.f1() returns BIGINT
BEGIN
DECLARE counter BIGINT DEFAULT NULL;
SELECT COUNT(*) INTO counter FROM information_schema.ROUTINES;
RETURN counter;
END//
# Attention: The printing of the next result sets is disabled.
SELECT * FROM information_schema.ROUTINES;
SELECT * FROM test.v1;
CALL test.p1;
SELECT test.f1();
DROP VIEW test.v1;
DROP PROCEDURE test.p1;
DROP FUNCTION test.f1;
#########################################################################
# Testcase 3.2.8.1: INFORMATION_SCHEMA.ROUTINES layout
#########################################################################
DESCRIBE          information_schema.ROUTINES;
Field	Type	Null	Key	Default	Extra
SPECIFIC_NAME	varchar(64)	NO			
ROUTINE_CATALOG	varchar(512)	YES		NULL	
ROUTINE_SCHEMA	varchar(64)	NO			
ROUTINE_NAME	varchar(64)	NO			
ROUTINE_TYPE	varchar(9)	NO			
DTD_IDENTIFIER	varchar(64)	YES		NULL	
ROUTINE_BODY	varchar(8)	NO			
ROUTINE_DEFINITION	longtext	YES		NULL	
EXTERNAL_NAME	varchar(64)	YES		NULL	
EXTERNAL_LANGUAGE	varchar(64)	YES		NULL	
PARAMETER_STYLE	varchar(8)	NO			
IS_DETERMINISTIC	varchar(3)	NO			
SQL_DATA_ACCESS	varchar(64)	NO			
SQL_PATH	varchar(64)	YES		NULL	
SECURITY_TYPE	varchar(7)	NO			
CREATED	datetime	NO		0000-00-00 00:00:00	
LAST_ALTERED	datetime	NO		0000-00-00 00:00:00	
SQL_MODE	longtext	NO		NULL	
ROUTINE_COMMENT	varchar(64)	NO			
DEFINER	varchar(77)	NO			
SHOW CREATE TABLE information_schema.ROUTINES;
Table	Create Table
ROUTINES	CREATE TEMPORARY TABLE `ROUTINES` (
  `SPECIFIC_NAME` varchar(64) NOT NULL default '',
  `ROUTINE_CATALOG` varchar(512) default NULL,
  `ROUTINE_SCHEMA` varchar(64) NOT NULL default '',
  `ROUTINE_NAME` varchar(64) NOT NULL default '',
  `ROUTINE_TYPE` varchar(9) NOT NULL default '',
  `DTD_IDENTIFIER` varchar(64) default NULL,
  `ROUTINE_BODY` varchar(8) NOT NULL default '',
  `ROUTINE_DEFINITION` longtext,
  `EXTERNAL_NAME` varchar(64) default NULL,
  `EXTERNAL_LANGUAGE` varchar(64) default NULL,
  `PARAMETER_STYLE` varchar(8) NOT NULL default '',
  `IS_DETERMINISTIC` varchar(3) NOT NULL default '',
  `SQL_DATA_ACCESS` varchar(64) NOT NULL default '',
  `SQL_PATH` varchar(64) default NULL,
  `SECURITY_TYPE` varchar(7) NOT NULL default '',
  `CREATED` datetime NOT NULL default '0000-00-00 00:00:00',
  `LAST_ALTERED` datetime NOT NULL default '0000-00-00 00:00:00',
  `SQL_MODE` longtext NOT NULL,
  `ROUTINE_COMMENT` varchar(64) NOT NULL default '',
  `DEFINER` varchar(77) NOT NULL default ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8
SHOW COLUMNS FROM information_schema.ROUTINES;
Field	Type	Null	Key	Default	Extra
SPECIFIC_NAME	varchar(64)	NO			
ROUTINE_CATALOG	varchar(512)	YES		NULL	
ROUTINE_SCHEMA	varchar(64)	NO			
ROUTINE_NAME	varchar(64)	NO			
ROUTINE_TYPE	varchar(9)	NO			
DTD_IDENTIFIER	varchar(64)	YES		NULL	
ROUTINE_BODY	varchar(8)	NO			
ROUTINE_DEFINITION	longtext	YES		NULL	
EXTERNAL_NAME	varchar(64)	YES		NULL	
EXTERNAL_LANGUAGE	varchar(64)	YES		NULL	
PARAMETER_STYLE	varchar(8)	NO			
IS_DETERMINISTIC	varchar(3)	NO			
SQL_DATA_ACCESS	varchar(64)	NO			
SQL_PATH	varchar(64)	YES		NULL	
SECURITY_TYPE	varchar(7)	NO			
CREATED	datetime	NO		0000-00-00 00:00:00	
LAST_ALTERED	datetime	NO		0000-00-00 00:00:00	
SQL_MODE	longtext	NO		NULL	
ROUTINE_COMMENT	varchar(64)	NO			
DEFINER	varchar(77)	NO			
USE test;
DROP PROCEDURE IF EXISTS sp_for_routines;
DROP FUNCTION  IF EXISTS function_for_routines;
CREATE PROCEDURE sp_for_routines()      SELECT 'db_datadict';
CREATE FUNCTION function_for_routines() RETURNS INT RETURN 0;
SELECT specific_name,routine_catalog,routine_schema,routine_name,routine_type,
routine_body,external_name,external_language,parameter_style,sql_path
FROM information_schema.routines
WHERE routine_catalog   IS NOT NULL OR external_name   IS NOT NULL
OR external_language IS NOT NULL OR sql_path        IS NOT NULL
OR routine_body      <> 'SQL'    OR parameter_style <> 'SQL'
   OR specific_name     <> routine_name;
specific_name	routine_catalog	routine_schema	routine_name	routine_type	routine_body	external_name	external_language	parameter_style	sql_path
DROP PROCEDURE sp_for_routines;
DROP FUNCTION  function_for_routines;
################################################################################
# Testcase 3.2.8.2 + 3.2.8.3: INFORMATION_SCHEMA.ROUTINES accessible information
################################################################################
DROP DATABASE IF EXISTS db_datadict;
DROP DATABASE IF EXISTS db_datadict_2;
CREATE DATABASE db_datadict;
USE db_datadict;
CREATE TABLE res_6_408002_1(f1 CHAR(3), f2 TEXT(25), f3 DATE, f4 INT)
ENGINE = <other_engine_type>;
INSERT INTO res_6_408002_1(f1, f2, f3, f4)
VALUES('abc', 'xyz', '1989-11-09', 0815);
DROP PROCEDURE IF EXISTS sp_6_408002_1;
CREATE PROCEDURE sp_6_408002_1()
BEGIN
SELECT * FROM db_datadict.res_6_408002_1;
END//
CREATE DATABASE db_datadict_2;
USE db_datadict_2;
CREATE TABLE res_6_408002_2(f1 CHAR(3), f2 TEXT(25), f3 DATE, f4 INT)
ENGINE = <other_engine_type>;
INSERT INTO res_6_408002_2(f1, f2, f3, f4)
VALUES('abc', 'xyz', '1990-10-03', 4711);
DROP PROCEDURE IF EXISTS sp_6_408002_2;
CREATE PROCEDURE sp_6_408002_2()
BEGIN
SELECT * FROM db_datadict_2.res_6_408002_2;
END//
DROP   USER 'testuser1'@'localhost';
CREATE USER 'testuser1'@'localhost';
DROP   USER 'testuser2'@'localhost';
CREATE USER 'testuser2'@'localhost';
DROP   USER 'testuser3'@'localhost';
CREATE USER 'testuser3'@'localhost';
GRANT SELECT  ON db_datadict_2.* TO 'testuser1'@'localhost';
GRANT EXECUTE ON db_datadict_2.* TO 'testuser1'@'localhost';
GRANT EXECUTE ON db_datadict.*   TO 'testuser1'@'localhost';
GRANT SELECT  ON db_datadict.*   TO 'testuser2'@'localhost';
GRANT EXECUTE ON PROCEDURE db_datadict_2.sp_6_408002_2
TO 'testuser2'@'localhost';
GRANT EXECUTE ON db_datadict_2.* TO 'testuser2'@'localhost';
FLUSH PRIVILEGES;
# Establish connection testuser1 (user=testuser1)
SELECT * FROM information_schema.routines;
SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	ROUTINE_TYPE	DTD_IDENTIFIER	ROUTINE_BODY	ROUTINE_DEFINITION	EXTERNAL_NAME	EXTERNAL_LANGUAGE	PARAMETER_STYLE	IS_DETERMINISTIC	SQL_DATA_ACCESS	SQL_PATH	SECURITY_TYPE	CREATED	LAST_ALTERED	SQL_MODE	ROUTINE_COMMENT	DEFINER
sp_6_408002_1	NULL	db_datadict	sp_6_408002_1	PROCEDURE	NULL	SQL	BEGIN
SELECT * FROM db_datadict.res_6_408002_1;
END	NULL	NULL	SQL	NO	CONTAINS SQL	NULL	DEFINER	YYYY-MM-DD hh:mm:ss	YYYY-MM-DD hh:mm:ss			root@localhost
sp_6_408002_2	NULL	db_datadict_2	sp_6_408002_2	PROCEDURE	NULL	SQL	BEGIN
SELECT * FROM db_datadict_2.res_6_408002_2;
END	NULL	NULL	SQL	NO	CONTAINS SQL	NULL	DEFINER	YYYY-MM-DD hh:mm:ss	YYYY-MM-DD hh:mm:ss			root@localhost
# Establish connection testuser2 (user=testuser2)
SELECT * FROM information_schema.routines;
SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	ROUTINE_TYPE	DTD_IDENTIFIER	ROUTINE_BODY	ROUTINE_DEFINITION	EXTERNAL_NAME	EXTERNAL_LANGUAGE	PARAMETER_STYLE	IS_DETERMINISTIC	SQL_DATA_ACCESS	SQL_PATH	SECURITY_TYPE	CREATED	LAST_ALTERED	SQL_MODE	ROUTINE_COMMENT	DEFINER
sp_6_408002_1	NULL	db_datadict	sp_6_408002_1	PROCEDURE	NULL	SQL	BEGIN
SELECT * FROM db_datadict.res_6_408002_1;
END	NULL	NULL	SQL	NO	CONTAINS SQL	NULL	DEFINER	YYYY-MM-DD hh:mm:ss	YYYY-MM-DD hh:mm:ss			root@localhost
sp_6_408002_2	NULL	db_datadict_2	sp_6_408002_2	PROCEDURE	NULL	SQL	BEGIN
SELECT * FROM db_datadict_2.res_6_408002_2;
END	NULL	NULL	SQL	NO	CONTAINS SQL	NULL	DEFINER	YYYY-MM-DD hh:mm:ss	YYYY-MM-DD hh:mm:ss			root@localhost
# Establish connection testuser3 (user=testuser3)
SELECT * FROM information_schema.routines;
SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	ROUTINE_TYPE	DTD_IDENTIFIER	ROUTINE_BODY	ROUTINE_DEFINITION	EXTERNAL_NAME	EXTERNAL_LANGUAGE	PARAMETER_STYLE	IS_DETERMINISTIC	SQL_DATA_ACCESS	SQL_PATH	SECURITY_TYPE	CREATED	LAST_ALTERED	SQL_MODE	ROUTINE_COMMENT	DEFINER
sp_6_408002_1	NULL	db_datadict	sp_6_408002_1	PROCEDURE	NULL	SQL	BEGIN
SELECT * FROM db_datadict.res_6_408002_1;
END	NULL	NULL	SQL	NO	CONTAINS SQL	NULL	DEFINER	YYYY-MM-DD hh:mm:ss	YYYY-MM-DD hh:mm:ss			root@localhost
sp_6_408002_2	NULL	db_datadict_2	sp_6_408002_2	PROCEDURE	NULL	SQL	BEGIN
SELECT * FROM db_datadict_2.res_6_408002_2;
END	NULL	NULL	SQL	NO	CONTAINS SQL	NULL	DEFINER	YYYY-MM-DD hh:mm:ss	YYYY-MM-DD hh:mm:ss			root@localhost
# Switch to connection default and close connections testuser1,testuser2,testuser3
DROP USER 'testuser1'@'localhost';
DROP USER 'testuser2'@'localhost';
DROP USER 'testuser3'@'localhost';
USE test;
DROP DATABASE db_datadict;
DROP DATABASE db_datadict_2;
#########################################################################
# 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.ROUTINES modifications
#########################################################################
DROP DATABASE IF EXISTS db_datadict;
CREATE DATABASE db_datadict;
SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict';
SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	ROUTINE_TYPE	DTD_IDENTIFIER	ROUTINE_BODY	ROUTINE_DEFINITION	EXTERNAL_NAME	EXTERNAL_LANGUAGE	PARAMETER_STYLE	IS_DETERMINISTIC	SQL_DATA_ACCESS	SQL_PATH	SECURITY_TYPE	CREATED	LAST_ALTERED	SQL_MODE	ROUTINE_COMMENT	DEFINER
USE db_datadict;
CREATE PROCEDURE sp_for_routines()      SELECT 'db_datadict';
CREATE FUNCTION function_for_routines() RETURNS INT RETURN 0;
SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict'
ORDER BY routine_name;
SPECIFIC_NAME	function_for_routines
ROUTINE_CATALOG	NULL
ROUTINE_SCHEMA	db_datadict
ROUTINE_NAME	function_for_routines
ROUTINE_TYPE	FUNCTION
DTD_IDENTIFIER	int(11)
ROUTINE_BODY	SQL
ROUTINE_DEFINITION	RETURN 0
EXTERNAL_NAME	NULL
EXTERNAL_LANGUAGE	NULL
PARAMETER_STYLE	SQL
IS_DETERMINISTIC	NO
SQL_DATA_ACCESS	CONTAINS SQL
SQL_PATH	NULL
SECURITY_TYPE	DEFINER
CREATED	<created>
LAST_ALTERED	<modified>
SQL_MODE	
ROUTINE_COMMENT	
DEFINER	root@localhost
SPECIFIC_NAME	sp_for_routines
ROUTINE_CATALOG	NULL
ROUTINE_SCHEMA	db_datadict
ROUTINE_NAME	sp_for_routines
ROUTINE_TYPE	PROCEDURE
DTD_IDENTIFIER	NULL
ROUTINE_BODY	SQL
ROUTINE_DEFINITION	SELECT 'db_datadict'
EXTERNAL_NAME	NULL
EXTERNAL_LANGUAGE	NULL
PARAMETER_STYLE	SQL
IS_DETERMINISTIC	NO
SQL_DATA_ACCESS	CONTAINS SQL
SQL_PATH	NULL
SECURITY_TYPE	DEFINER
CREATED	<created>
LAST_ALTERED	<modified>
SQL_MODE	
ROUTINE_COMMENT	
DEFINER	root@localhost
ALTER PROCEDURE sp_for_routines SQL SECURITY INVOKER;
ALTER FUNCTION function_for_routines COMMENT 'updated comments';
SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict'
ORDER BY routine_name;
SPECIFIC_NAME	function_for_routines
ROUTINE_CATALOG	NULL
ROUTINE_SCHEMA	db_datadict
ROUTINE_NAME	function_for_routines
ROUTINE_TYPE	FUNCTION
DTD_IDENTIFIER	int(11)
ROUTINE_BODY	SQL
ROUTINE_DEFINITION	RETURN 0
EXTERNAL_NAME	NULL
EXTERNAL_LANGUAGE	NULL
PARAMETER_STYLE	SQL
IS_DETERMINISTIC	NO
SQL_DATA_ACCESS	CONTAINS SQL
SQL_PATH	NULL
SECURITY_TYPE	DEFINER
CREATED	<created>
LAST_ALTERED	<modified>
SQL_MODE	
ROUTINE_COMMENT	updated comments
DEFINER	root@localhost
SPECIFIC_NAME	sp_for_routines
ROUTINE_CATALOG	NULL
ROUTINE_SCHEMA	db_datadict
ROUTINE_NAME	sp_for_routines
ROUTINE_TYPE	PROCEDURE
DTD_IDENTIFIER	NULL
ROUTINE_BODY	SQL
ROUTINE_DEFINITION	SELECT 'db_datadict'
EXTERNAL_NAME	NULL
EXTERNAL_LANGUAGE	NULL
PARAMETER_STYLE	SQL
IS_DETERMINISTIC	NO
SQL_DATA_ACCESS	CONTAINS SQL
SQL_PATH	NULL
SECURITY_TYPE	INVOKER
CREATED	<created>
LAST_ALTERED	<modified>
SQL_MODE	
ROUTINE_COMMENT	
DEFINER	root@localhost
DROP PROCEDURE sp_for_routines;
DROP FUNCTION function_for_routines;
SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict';
SPECIFIC_NAME	ROUTINE_CATALOG	ROUTINE_SCHEMA	ROUTINE_NAME	ROUTINE_TYPE	DTD_IDENTIFIER	ROUTINE_BODY	ROUTINE_DEFINITION	EXTERNAL_NAME	EXTERNAL_LANGUAGE	PARAMETER_STYLE	IS_DETERMINISTIC	SQL_DATA_ACCESS	SQL_PATH	SECURITY_TYPE	CREATED	LAST_ALTERED	SQL_MODE	ROUTINE_COMMENT	DEFINER
CREATE PROCEDURE sp_for_routines()      SELECT 'db_datadict';
CREATE FUNCTION function_for_routines() RETURNS INT RETURN 0;
SELECT * FROM information_schema.routines WHERE routine_schema = 'db_datadict'
ORDER BY routine_name;
SPECIFIC_NAME	function_for_routines
ROUTINE_CATALOG	NULL
ROUTINE_SCHEMA	db_datadict
ROUTINE_NAME	function_for_routines
ROUTINE_TYPE	FUNCTION
DTD_IDENTIFIER	int(11)
ROUTINE_BODY	SQL
ROUTINE_DEFINITION	RETURN 0
EXTERNAL_NAME	NULL
EXTERNAL_LANGUAGE	NULL
PARAMETER_STYLE	SQL
IS_DETERMINISTIC	NO
SQL_DATA_ACCESS	CONTAINS SQL
SQL_PATH	NULL
SECURITY_TYPE	DEFINER
CREATED	<created>
LAST_ALTERED	<modified>
SQL_MODE	
ROUTINE_COMMENT	
DEFINER	root@localhost
SPECIFIC_NAME	sp_for_routines