is_routines_embedded.result

这个文件是windows mysql源码

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
use test;
DROP 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
#########################################################################
# 3.2.8.4: INFORMATION_SCHEMA.ROUTINES routine body too big for
#          ROUTINE_DEFINITION column
#########################################################################
DROP DATABASE IF EXISTS db_datadict;
CREATE DATABASE db_datadict;
USE db_datadict;
CREATE TABLE db_datadict.res_6_408004_1
(f1 LONGTEXT , f2 MEDIUMINT , f3 LONGBLOB , f4 REAL , f5 YEAR)
ENGINE = <other_engine_type>;
INSERT INTO db_datadict.res_6_408004_1
VALUES ('abc', 98765 , 99999999 , 98765, 10);
CREATE TABLE db_datadict.res_6_408004_2
(f1 LONGTEXT , f2 MEDIUMINT , f3 LONGBLOB , f4 REAL , f5 YEAR)
ENGINE = <other_engine_type>;
INSERT INTO db_datadict.res_6_408004_2
VALUES ('abc', 98765 , 99999999 , 98765, 10);
# Checking the max. possible length of (currently) 4 GByte is not
# in this environment here.
CREATE PROCEDURE sp_6_408004 ()
BEGIN
DECLARE done INTEGER DEFAULt 0;
DECLARE variable_number_1 LONGTEXT;
DECLARE variable_number_2 MEDIUMINT;
DECLARE variable_number_3 LONGBLOB;
DECLARE variable_number_4 REAL;
DECLARE variable_number_5 YEAR;
DECLARE cursor_number_1 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
DECLARE cursor_number_2 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
DECLARE cursor_number_3 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
DECLARE cursor_number_4 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
DECLARE cursor_number_5 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
BEGIN
OPEN cursor_number_1;
WHILE done <> 1 DO
FETCH cursor_number_1
INTO variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5;
IF done <> 0 THEN
INSERT INTO res_6_408004_2
VALUES (variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5);
END IF;
END WHILE;
BEGIN
BEGIN
SET done = 0;
OPEN cursor_number_2;
WHILE done <> 1 DO
FETCH cursor_number_2
INTO variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5;
IF done <> 0 THEN
INSERT INTO res_6_408004_2
VALUES(variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5);
END IF;
END WHILE;
END;
SET done = 0;
OPEN cursor_number_3;
WHILE done <> 1 DO
FETCH cursor_number_3
INTO variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5;
IF done <> 0 THEN
INSERT INTO res_6_408004_2
VALUES(variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5);
END IF;
END WHILE;
END;
END;
BEGIN
SET done = 0;
OPEN cursor_number_4;
WHILE done <> 1 DO
FETCH cursor_number_4
INTO variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5;
IF done <> 0 THEN
INSERT INTO res_6_408004_2
VALUES (variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5);
END IF;
END WHILE;
END;
BEGIN
SET @a='test row';
SELECT @a;
SELECT @a;
SELECT @a;
END;
BEGIN
SET done = 0;
OPEN cursor_number_5;
WHILE done <> 1 DO
FETCH cursor_number_5
INTO variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5;
IF done <> 0 THEN
INSERT INTO res_6_408004_2
VALUES (variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5);
END IF;
END WHILE;
END;
BEGIN
SET @a='test row';
SELECT @a;
SELECT @a;
SELECT @a;
END;
END//
CALL db_datadict.sp_6_408004 ();
@a
test row
@a
test row
@a
test row
@a
test row
@a
test row
@a
test row
SELECT * FROM db_datadict.res_6_408004_2;
f1	f2	f3	f4	f5
abc	98765	99999999	98765	2010
abc	98765	99999999	98765	2010
abc	98765	99999999	98765	2010
abc	98765	99999999	98765	2010
abc	98765	99999999	98765	2010
abc	98765	99999999	98765	2010
SELECT *, LENGTH(routine_definition) FROM information_schema.routines
WHERE routine_schema = 'db_datadict';
SPECIFIC_NAME	sp_6_408004
ROUTINE_CATALOG	NULL
ROUTINE_SCHEMA	db_datadict
ROUTINE_NAME	sp_6_408004
ROUTINE_TYPE	PROCEDURE
DTD_IDENTIFIER	NULL
ROUTINE_BODY	SQL
ROUTINE_DEFINITION	BEGIN
DECLARE done INTEGER DEFAULt 0;
DECLARE variable_number_1 LONGTEXT;
DECLARE variable_number_2 MEDIUMINT;
DECLARE variable_number_3 LONGBLOB;
DECLARE variable_number_4 REAL;
DECLARE variable_number_5 YEAR;
DECLARE cursor_number_1 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
DECLARE cursor_number_2 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
DECLARE cursor_number_3 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
DECLARE cursor_number_4 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
DECLARE cursor_number_5 CURSOR FOR SELECT * FROM res_6_408004_1 LIMIT 0, 10;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
BEGIN
OPEN cursor_number_1;
WHILE done <> 1 DO
FETCH cursor_number_1
INTO variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5;
IF done <> 0 THEN
INSERT INTO res_6_408004_2
VALUES (variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5);
END IF;
END WHILE;
BEGIN
BEGIN
SET done = 0;
OPEN cursor_number_2;
WHILE done <> 1 DO
FETCH cursor_number_2
INTO variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5;
IF done <> 0 THEN
INSERT INTO res_6_408004_2
VALUES(variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5);
END IF;
END WHILE;
END;
SET done = 0;
OPEN cursor_number_3;
WHILE done <> 1 DO
FETCH cursor_number_3
INTO variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5;
IF done <> 0 THEN
INSERT INTO res_6_408004_2
VALUES(variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5);
END IF;
END WHILE;
END;
END;
BEGIN
SET done = 0;
OPEN cursor_number_4;
WHILE done <> 1 DO
FETCH cursor_number_4
INTO variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5;
IF done <> 0 THEN
INSERT INTO res_6_408004_2
VALUES (variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5);
END IF;
END WHILE;
END;
BEGIN
SET @a='test row';
SELECT @a;
SELECT @a;
SELECT @a;
END;
BEGIN
SET done = 0;
OPEN cursor_number_5;
WHILE done <> 1 DO
FETCH cursor_number_5
INTO variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5;
IF done <> 0 THEN
INSERT INTO res_6_408004_2
VALUES (variable_number_1, variable_number_2, variable_number_3,
variable_number_4, variable_number_5);
END IF;
END WHILE;
END;
BEGIN
SET @a='test row';
SELECT @a;
SELECT @a;
SELECT @a;
END;
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	YYYY-MM-DD hh:mm:ss
LAST_ALTERED	YYYY-MM-DD hh:mm:ss
SQL_MODE	
ROUTINE_COMMENT	
DEFINER	root@localhost
LENGTH(routine_definition)	2549
DROP DATABASE db_datadict;
########################################################################
# Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
#           DDL on INFORMATION_SCHEMA table are not supported
########################################################################
DROP DATABASE IF EXISTS db_datadict;
CREATE DATABASE db_datadict;
USE db_datadict;
CREATE PROCEDURE sp_for_routines() SELECT 'db_datadict';
USE test;
INSERT INTO information_schema.routines (routine_name, routine_type )
VALUES ('p2', 'procedure');
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
UPDATE information_schema.routines SET routine_name = 'p2'
WHERE routine_body = 'sql';
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DELETE FROM information_schema.routines ;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
TRUNCATE information_schema.routines ;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
CREATE INDEX i7 ON information_schema.routines (routine_name);
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.routines  ADD f1 INT;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.routines  DISCARD TABLESPACE;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP TABLE information_schema.routines ;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.routines RENAME db_datadict.routines;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.routines RENAME information_schema.xroutines;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP DATABASE db_datadict;