4493d40a-92aa-4162-9dfc-bfc4ec0996e6
/
polardbxengine
2
0
Fork 0
Code Issues Pull Requests Packages Projects Releases Wiki Activity
polardbxengine/mysql-test/r/information_schema_paramete...

583 lines
25 KiB
Plaintext
Raw Permalink Blame History

# ========== parameters.1 ==========
USE INFORMATION_SCHEMA;
SHOW CREATE TABLE INFORMATION_SCHEMA.PARAMETERS;
View Create View character_set_client collation_connection
PARAMETERS CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `PARAMETERS` AS select (`cat`.`name` collate utf8_tolower_ci) AS `SPECIFIC_CATALOG`,(`sch`.`name` collate utf8_tolower_ci) AS `SPECIFIC_SCHEMA`,`rtn`.`name` AS `SPECIFIC_NAME`,if((`rtn`.`type` = 'FUNCTION'),(`prm`.`ordinal_position` - 1),`prm`.`ordinal_position`) AS `ORDINAL_POSITION`,if(((`rtn`.`type` = 'FUNCTION') and (`prm`.`ordinal_position` = 1)),NULL,`prm`.`mode`) AS `PARAMETER_MODE`,if(((`rtn`.`type` = 'FUNCTION') and (`prm`.`ordinal_position` = 1)),NULL,`prm`.`name`) AS `PARAMETER_NAME`,substring_index(substring_index(`prm`.`data_type_utf8`,'(',1),' ',1) AS `DATA_TYPE`,internal_dd_char_length(`prm`.`data_type`,`prm`.`char_length`,`col`.`name`,0) AS `CHARACTER_MAXIMUM_LENGTH`,internal_dd_char_length(`prm`.`data_type`,`prm`.`char_length`,`col`.`name`,1) AS `CHARACTER_OCTET_LENGTH`,`prm`.`numeric_precision` AS `NUMERIC_PRECISION`,if((`prm`.`numeric_precision` is null),NULL,ifnull(`prm`.`numeric_scale`,0)) AS `NUMERIC_SCALE`,`prm`.`datetime_precision` AS `DATETIME_PRECISION`,(case `prm`.`data_type` when 'MYSQL_TYPE_STRING' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_VAR_STRING' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_VARCHAR' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_TINY_BLOB' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_MEDIUM_BLOB' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_BLOB' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_LONG_BLOB' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_ENUM' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_SET' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) else NULL end) AS `CHARACTER_SET_NAME`,(case `prm`.`data_type` when 'MYSQL_TYPE_STRING' then if((`cs`.`name` = 'binary'),NULL,`col`.`name`) when 'MYSQL_TYPE_VAR_STRING' then if((`cs`.`name` = 'binary'),NULL,`col`.`name`) when 'MYSQL_TYPE_VARCHAR' then if((`cs`.`name` = 'binary'),NULL,`col`.`name`) when 'MYSQL_TYPE_TINY_BLOB' then if((`cs`.`name` = 'binary'),NULL,`col`.`name`) when 'MYSQL_TYPE_MEDIUM_BLOB' then if((`cs`.`name` = 'binary'),NULL,`col`.`name`) when 'MYSQL_TYPE_BLOB' then if((`cs`.`name` = 'binary'),NULL,`col`.`name`) when 'MYSQL_TYPE_LONG_BLOB' then if((`cs`.`name` = 'binary'),NULL,`col`.`name`) when 'MYSQL_TYPE_ENUM' then if((`cs`.`name` = 'binary'),NULL,`col`.`name`) when 'MYSQL_TYPE_SET' then if((`cs`.`name` = 'binary'),NULL,`col`.`name`) else NULL end) AS `COLLATION_NAME`,`prm`.`data_type_utf8` AS `DTD_IDENTIFIER`,`rtn`.`type` AS `ROUTINE_TYPE` from (((((`mysql`.`parameters` `prm` join `mysql`.`routines` `rtn` on((`prm`.`routine_id` = `rtn`.`id`))) join `mysql`.`schemata` `sch` on((`rtn`.`schema_id` = `sch`.`id`))) join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) join `mysql`.`collations` `col` on((`prm`.`collation_id` = `col`.`id`))) join `mysql`.`character_sets` `cs` on((`col`.`character_set_id` = `cs`.`id`))) where (0 <> can_access_routine(`sch`.`name`,`rtn`.`name`,`rtn`.`type`,`rtn`.`definer`,false)) utf8 utf8_general_ci
SELECT * FROM information_schema.columns
WHERE table_schema = 'information_schema'
AND table_name = 'PARAMETERS'
ORDER BY ordinal_position;
TABLE_CATALOG def
TABLE_SCHEMA information_schema
TABLE_NAME PARAMETERS
COLUMN_NAME SPECIFIC_CATALOG
ORDINAL_POSITION 1
COLUMN_DEFAULT NULL
IS_NULLABLE YES
DATA_TYPE varchar
CHARACTER_MAXIMUM_LENGTH 64
CHARACTER_OCTET_LENGTH 192
NUMERIC_PRECISION NULL
NUMERIC_SCALE NULL
DATETIME_PRECISION NULL
CHARACTER_SET_NAME utf8
COLLATION_NAME utf8_bin
COLUMN_TYPE varchar(64)
COLUMN_KEY
EXTRA
PRIVILEGES select
COLUMN_COMMENT
GENERATION_EXPRESSION
SRS_ID NULL
TABLE_CATALOG def
TABLE_SCHEMA information_schema
TABLE_NAME PARAMETERS
COLUMN_NAME SPECIFIC_SCHEMA
ORDINAL_POSITION 2
COLUMN_DEFAULT NULL
IS_NULLABLE YES
DATA_TYPE varchar
CHARACTER_MAXIMUM_LENGTH 64
CHARACTER_OCTET_LENGTH 192
NUMERIC_PRECISION NULL
NUMERIC_SCALE NULL
DATETIME_PRECISION NULL
CHARACTER_SET_NAME utf8
COLLATION_NAME utf8_bin
COLUMN_TYPE varchar(64)
COLUMN_KEY
EXTRA
PRIVILEGES select
COLUMN_COMMENT
GENERATION_EXPRESSION
SRS_ID NULL
TABLE_CATALOG def
TABLE_SCHEMA information_schema
TABLE_NAME PARAMETERS
COLUMN_NAME SPECIFIC_NAME
ORDINAL_POSITION 3
COLUMN_DEFAULT NULL
IS_NULLABLE NO
DATA_TYPE varchar
CHARACTER_MAXIMUM_LENGTH 64
CHARACTER_OCTET_LENGTH 192
NUMERIC_PRECISION NULL
NUMERIC_SCALE NULL
DATETIME_PRECISION NULL
CHARACTER_SET_NAME utf8
COLLATION_NAME utf8_general_ci
COLUMN_TYPE varchar(64)
COLUMN_KEY
EXTRA
PRIVILEGES select
COLUMN_COMMENT
GENERATION_EXPRESSION
SRS_ID NULL
TABLE_CATALOG def
TABLE_SCHEMA information_schema
TABLE_NAME PARAMETERS
COLUMN_NAME ORDINAL_POSITION
ORDINAL_POSITION 4
COLUMN_DEFAULT 0
IS_NULLABLE NO
DATA_TYPE bigint
CHARACTER_MAXIMUM_LENGTH NULL
CHARACTER_OCTET_LENGTH NULL
NUMERIC_PRECISION 20
NUMERIC_SCALE 0
DATETIME_PRECISION NULL
CHARACTER_SET_NAME NULL
COLLATION_NAME NULL
COLUMN_TYPE bigint(11) unsigned
COLUMN_KEY
EXTRA
PRIVILEGES select
COLUMN_COMMENT
GENERATION_EXPRESSION
SRS_ID NULL
TABLE_CATALOG def
TABLE_SCHEMA information_schema
TABLE_NAME PARAMETERS
COLUMN_NAME PARAMETER_MODE
ORDINAL_POSITION 5
COLUMN_DEFAULT NULL
IS_NULLABLE YES
DATA_TYPE varchar
CHARACTER_MAXIMUM_LENGTH 5
CHARACTER_OCTET_LENGTH 15
NUMERIC_PRECISION NULL
NUMERIC_SCALE NULL
DATETIME_PRECISION NULL
CHARACTER_SET_NAME utf8
COLLATION_NAME utf8_bin
COLUMN_TYPE varchar(5)
COLUMN_KEY
EXTRA
PRIVILEGES select
COLUMN_COMMENT
GENERATION_EXPRESSION
SRS_ID NULL
TABLE_CATALOG def
TABLE_SCHEMA information_schema
TABLE_NAME PARAMETERS
COLUMN_NAME PARAMETER_NAME
ORDINAL_POSITION 6
COLUMN_DEFAULT NULL
IS_NULLABLE YES
DATA_TYPE varchar
CHARACTER_MAXIMUM_LENGTH 64
CHARACTER_OCTET_LENGTH 192
NUMERIC_PRECISION NULL
NUMERIC_SCALE NULL
DATETIME_PRECISION NULL
CHARACTER_SET_NAME utf8
COLLATION_NAME utf8_general_ci
COLUMN_TYPE varchar(64)
COLUMN_KEY
EXTRA
PRIVILEGES select
COLUMN_COMMENT
GENERATION_EXPRESSION
SRS_ID NULL
TABLE_CATALOG def
TABLE_SCHEMA information_schema
TABLE_NAME PARAMETERS
COLUMN_NAME DATA_TYPE
ORDINAL_POSITION 7
COLUMN_DEFAULT NULL
IS_NULLABLE YES
DATA_TYPE longtext
CHARACTER_MAXIMUM_LENGTH 4294967295
CHARACTER_OCTET_LENGTH 4294967295
NUMERIC_PRECISION NULL
NUMERIC_SCALE NULL
DATETIME_PRECISION NULL
CHARACTER_SET_NAME utf8
COLLATION_NAME utf8_bin
COLUMN_TYPE longtext
COLUMN_KEY
EXTRA
PRIVILEGES select
COLUMN_COMMENT
GENERATION_EXPRESSION
SRS_ID NULL
TABLE_CATALOG def
TABLE_SCHEMA information_schema
TABLE_NAME PARAMETERS
COLUMN_NAME CHARACTER_MAXIMUM_LENGTH
ORDINAL_POSITION 8
COLUMN_DEFAULT NULL
IS_NULLABLE YES
DATA_TYPE bigint
CHARACTER_MAXIMUM_LENGTH NULL
CHARACTER_OCTET_LENGTH NULL
NUMERIC_PRECISION 19
NUMERIC_SCALE 0
DATETIME_PRECISION NULL
CHARACTER_SET_NAME NULL
COLLATION_NAME NULL
COLUMN_TYPE bigint(21)
COLUMN_KEY
EXTRA
PRIVILEGES select
COLUMN_COMMENT
GENERATION_EXPRESSION
SRS_ID NULL
TABLE_CATALOG def
TABLE_SCHEMA information_schema
TABLE_NAME PARAMETERS
COLUMN_NAME CHARACTER_OCTET_LENGTH
ORDINAL_POSITION 9
COLUMN_DEFAULT NULL
IS_NULLABLE YES
DATA_TYPE bigint
CHARACTER_MAXIMUM_LENGTH NULL
CHARACTER_OCTET_LENGTH NULL
NUMERIC_PRECISION 19
NUMERIC_SCALE 0
DATETIME_PRECISION NULL
CHARACTER_SET_NAME NULL
COLLATION_NAME NULL
COLUMN_TYPE bigint(21)
COLUMN_KEY
EXTRA
PRIVILEGES select
COLUMN_COMMENT
GENERATION_EXPRESSION
SRS_ID NULL
TABLE_CATALOG def
TABLE_SCHEMA information_schema
TABLE_NAME PARAMETERS
COLUMN_NAME NUMERIC_PRECISION
ORDINAL_POSITION 10
COLUMN_DEFAULT NULL
IS_NULLABLE YES
DATA_TYPE int
CHARACTER_MAXIMUM_LENGTH NULL
CHARACTER_OCTET_LENGTH NULL
NUMERIC_PRECISION 10
NUMERIC_SCALE 0
DATETIME_PRECISION NULL
CHARACTER_SET_NAME NULL
COLLATION_NAME NULL
COLUMN_TYPE int(10) unsigned
COLUMN_KEY
EXTRA
PRIVILEGES select
COLUMN_COMMENT
GENERATION_EXPRESSION
SRS_ID NULL
TABLE_CATALOG def
TABLE_SCHEMA information_schema
TABLE_NAME PARAMETERS
COLUMN_NAME NUMERIC_SCALE
ORDINAL_POSITION 11
COLUMN_DEFAULT NULL
IS_NULLABLE YES
DATA_TYPE bigint
CHARACTER_MAXIMUM_LENGTH NULL
CHARACTER_OCTET_LENGTH NULL
NUMERIC_PRECISION 19
NUMERIC_SCALE 0
DATETIME_PRECISION NULL
CHARACTER_SET_NAME NULL
COLLATION_NAME NULL
COLUMN_TYPE bigint(10)
COLUMN_KEY
EXTRA
PRIVILEGES select
COLUMN_COMMENT
GENERATION_EXPRESSION
SRS_ID NULL
TABLE_CATALOG def
TABLE_SCHEMA information_schema
TABLE_NAME PARAMETERS
COLUMN_NAME DATETIME_PRECISION
ORDINAL_POSITION 12
COLUMN_DEFAULT NULL
IS_NULLABLE YES
DATA_TYPE int
CHARACTER_MAXIMUM_LENGTH NULL
CHARACTER_OCTET_LENGTH NULL
NUMERIC_PRECISION 10
NUMERIC_SCALE 0
DATETIME_PRECISION NULL
CHARACTER_SET_NAME NULL
COLLATION_NAME NULL
COLUMN_TYPE int(10) unsigned
COLUMN_KEY
EXTRA
PRIVILEGES select
COLUMN_COMMENT
GENERATION_EXPRESSION
SRS_ID NULL
TABLE_CATALOG def
TABLE_SCHEMA information_schema
TABLE_NAME PARAMETERS
COLUMN_NAME CHARACTER_SET_NAME
ORDINAL_POSITION 13
COLUMN_DEFAULT NULL
IS_NULLABLE YES
DATA_TYPE varchar
CHARACTER_MAXIMUM_LENGTH 64
CHARACTER_OCTET_LENGTH 192
NUMERIC_PRECISION NULL
NUMERIC_SCALE NULL
DATETIME_PRECISION NULL
CHARACTER_SET_NAME utf8
COLLATION_NAME utf8_general_ci
COLUMN_TYPE varchar(64)
COLUMN_KEY
EXTRA
PRIVILEGES select
COLUMN_COMMENT
GENERATION_EXPRESSION
SRS_ID NULL
TABLE_CATALOG def
TABLE_SCHEMA information_schema
TABLE_NAME PARAMETERS
COLUMN_NAME COLLATION_NAME
ORDINAL_POSITION 14
COLUMN_DEFAULT NULL
IS_NULLABLE YES
DATA_TYPE varchar
CHARACTER_MAXIMUM_LENGTH 64
CHARACTER_OCTET_LENGTH 192
NUMERIC_PRECISION NULL
NUMERIC_SCALE NULL
DATETIME_PRECISION NULL
CHARACTER_SET_NAME utf8
COLLATION_NAME utf8_general_ci
COLUMN_TYPE varchar(64)
COLUMN_KEY
EXTRA
PRIVILEGES select
COLUMN_COMMENT
GENERATION_EXPRESSION
SRS_ID NULL
TABLE_CATALOG def
TABLE_SCHEMA information_schema
TABLE_NAME PARAMETERS
COLUMN_NAME DTD_IDENTIFIER
ORDINAL_POSITION 15
COLUMN_DEFAULT NULL
IS_NULLABLE NO
DATA_TYPE mediumtext
CHARACTER_MAXIMUM_LENGTH 16777215
CHARACTER_OCTET_LENGTH 16777215
NUMERIC_PRECISION NULL
NUMERIC_SCALE NULL
DATETIME_PRECISION NULL
CHARACTER_SET_NAME utf8
COLLATION_NAME utf8_bin
COLUMN_TYPE mediumtext
COLUMN_KEY
EXTRA
PRIVILEGES select
COLUMN_COMMENT
GENERATION_EXPRESSION
SRS_ID NULL
TABLE_CATALOG def
TABLE_SCHEMA information_schema
TABLE_NAME PARAMETERS
COLUMN_NAME ROUTINE_TYPE
ORDINAL_POSITION 16
COLUMN_DEFAULT NULL
IS_NULLABLE NO
DATA_TYPE enum
CHARACTER_MAXIMUM_LENGTH 9
CHARACTER_OCTET_LENGTH 27
NUMERIC_PRECISION NULL
NUMERIC_SCALE NULL
DATETIME_PRECISION NULL
CHARACTER_SET_NAME utf8
COLLATION_NAME utf8_bin
COLUMN_TYPE enum('FUNCTION','PROCEDURE')
COLUMN_KEY
EXTRA
PRIVILEGES select
COLUMN_COMMENT
GENERATION_EXPRESSION
SRS_ID NULL
DESCRIBE INFORMATION_SCHEMA.PARAMETERS;
Field Type Null Key Default Extra
SPECIFIC_CATALOG varchar(64) YES NULL
SPECIFIC_SCHEMA varchar(64) YES NULL
SPECIFIC_NAME varchar(64) NO NULL
ORDINAL_POSITION bigint(11) unsigned NO 0
PARAMETER_MODE varchar(5) YES NULL
PARAMETER_NAME varchar(64) YES NULL
DATA_TYPE longtext YES NULL
CHARACTER_MAXIMUM_LENGTH bigint(21) YES NULL
CHARACTER_OCTET_LENGTH bigint(21) YES NULL
NUMERIC_PRECISION int(10) unsigned YES NULL
NUMERIC_SCALE bigint(10) YES NULL
DATETIME_PRECISION int(10) unsigned YES NULL
CHARACTER_SET_NAME varchar(64) YES NULL
COLLATION_NAME varchar(64) YES NULL
DTD_IDENTIFIER mediumtext NO NULL
ROUTINE_TYPE enum('FUNCTION','PROCEDURE') NO NULL
# ========== parameters.2 ==========
CREATE DATABASE i_s_parameters_test;
USE i_s_parameters_test;
CREATE FUNCTION test_func1 (s char(20) RETURNS CHAR(50)
RETURN CONCAT('Hello', ,s,'!');
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 'RETURNS CHAR(50)
RETURN CONCAT('Hello', ,s,'!')' at line 1
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func1';
SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE
# ========== parameters.3 ==========
DROP DATABASE IF EXISTS i_s_parameters_test;
CREATE DATABASE i_s_parameters_test;
USE i_s_parameters_test;
CREATE FUNCTION test_func1 (s char(20)) RETURNS CHAR(50)
RETURN CONCAT('Hello, ',s,'!');
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func1';
SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE
def i_s_parameters_test test_func1 0 NULL NULL char 50 200 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci char(50) FUNCTION
def i_s_parameters_test test_func1 1 IN s char 20 80 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci char(20) FUNCTION
DROP FUNCTION test_func1;
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func1';
SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE
# ========== parameters.4 ==========
DROP DATABASE IF EXISTS i_s_parameters_test;
CREATE DATABASE i_s_parameters_test;
USE i_s_parameters_test;
CREATE PROCEDURE testproc (OUT param1 INT)
BEGIN
SELECT 2+2 as param1;
END;
//
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'testproc';
SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE
def i_s_parameters_test testproc 1 OUT param1 int NULL NULL 10 0 NULL NULL NULL int(11) PROCEDURE
# ========== parameters.5 ==========
DROP DATABASE IF EXISTS i_s_parameters_test;
CREATE DATABASE i_s_parameters_test;
USE i_s_parameters_test;
CREATE PROCEDURE test_proc(INOUT P INT) SET @x=P*2;
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_proc';
SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE
def i_s_parameters_test test_proc 1 INOUT P int NULL NULL 10 0 NULL NULL NULL int(11) PROCEDURE
# ========== parameters.6 ==========
DROP DATABASE IF EXISTS i_s_parameters_test;
CREATE DATABASE i_s_parameters_test;
USE i_s_parameters_test;
CREATE PROCEDURE test_proc(OUT p VARCHAR(10)) SET P='test';
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_proc';
SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE
def i_s_parameters_test test_proc 1 OUT p varchar 10 40 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci varchar(10) PROCEDURE
# ========== parameters.7 ==========
DROP DATABASE IF EXISTS i_s_parameters_test;
CREATE DATABASE i_s_parameters_test;
USE i_s_parameters_test;
CREATE FUNCTION test_func1 (s char(20), t char(20)) RETURNS CHAR(40)
RETURN CONCAT(s,t);
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func1';
SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE
def i_s_parameters_test test_func1 0 NULL NULL char 40 160 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci char(40) FUNCTION
def i_s_parameters_test test_func1 1 IN s char 20 80 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci char(20) FUNCTION
def i_s_parameters_test test_func1 2 IN t char 20 80 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci char(20) FUNCTION
# ========== parameters.8 ==========
DROP DATABASE IF EXISTS i_s_parameters_test;
CREATE DATABASE i_s_parameters_test;
USE i_s_parameters_test;
CREATE FUNCTION test_func1 (s char(20)) RETURNS CHAR(50)
RETURN CONCAT('Hello, ',s,'!');
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func1';
SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE
def i_s_parameters_test test_func1 0 NULL NULL char 50 200 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci char(50) FUNCTION
def i_s_parameters_test test_func1 1 IN s char 20 80 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci char(20) FUNCTION
# ========== parameters.9 ==========
DROP DATABASE IF EXISTS i_s_parameters_test;
CREATE DATABASE i_s_parameters_test;
USE i_s_parameters_test;
CREATE FUNCTION test_func2 (s int) RETURNS INT RETURN s*2;
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func2';
SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE
def i_s_parameters_test test_func2 0 NULL NULL int NULL NULL 10 0 NULL NULL NULL int(11) FUNCTION
def i_s_parameters_test test_func2 1 IN s int NULL NULL 10 0 NULL NULL NULL int(11) FUNCTION
# ========== parameters.10 ==========
DROP DATABASE IF EXISTS i_s_parameters_test;
CREATE DATABASE i_s_parameters_test;
USE i_s_parameters_test;
CREATE FUNCTION test_func5 (s date) RETURNS TIMESTAMP
RETURN CURRENT_TIMESTAMP;
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func5';
SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE
def i_s_parameters_test test_func5 0 NULL NULL timestamp NULL NULL NULL NULL 0 NULL NULL timestamp FUNCTION
def i_s_parameters_test test_func5 1 IN s date NULL NULL NULL NULL NULL NULL NULL date FUNCTION
# ========== parameters.11 ==========
DROP DATABASE IF EXISTS i_s_parameters_test;
CREATE DATABASE i_s_parameters_test;
USE i_s_parameters_test;
CREATE FUNCTION test_func5 (s date) RETURNS TIMESTAMP
RETURN CURRENT_TIMESTAMP;
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func5';
SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE
def i_s_parameters_test test_func5 0 NULL NULL timestamp NULL NULL NULL NULL 0 NULL NULL timestamp FUNCTION
def i_s_parameters_test test_func5 1 IN s date NULL NULL NULL NULL NULL NULL NULL date FUNCTION
ALTER FUNCTION test_func5 COMMENT 'new comment added';
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func5';
SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE
def i_s_parameters_test test_func5 0 NULL NULL timestamp NULL NULL NULL NULL 0 NULL NULL timestamp FUNCTION
def i_s_parameters_test test_func5 1 IN s date NULL NULL NULL NULL NULL NULL NULL date FUNCTION
# ========== parameters.12 ==========
DROP DATABASE IF EXISTS i_s_parameters_test;
CREATE DATABASE i_s_parameters_test CHARACTER SET utf8;
Warnings:
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
USE i_s_parameters_test;
CREATE FUNCTION test_func5 (s CHAR(20)) RETURNS VARCHAR(30)
RETURN CONCAT('XYZ, ' ,s);
SELECT * FROM INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_SCHEMA = 'i_s_parameters_test' AND SPECIFIC_NAME = 'test_func5';
SPECIFIC_CATALOG SPECIFIC_SCHEMA SPECIFIC_NAME ORDINAL_POSITION PARAMETER_MODE PARAMETER_NAME DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME DTD_IDENTIFIER ROUTINE_TYPE
def i_s_parameters_test test_func5 0 NULL NULL varchar 30 90 NULL NULL NULL utf8 utf8_general_ci varchar(30) FUNCTION
def i_s_parameters_test test_func5 1 IN s char 20 60 NULL NULL NULL utf8 utf8_general_ci char(20) FUNCTION
# ========== parameters.13 ==========
DROP DATABASE IF EXISTS i_s_parameters_test;
CREATE DATABASE i_s_parameters_test;
USE i_s_parameters_test;
CREATE PROCEDURE test_proc (OUT p_p1 datetime,OUT p_p2 time,OUT p_p3 timestamp) BEGIN SELECT 1 ; END; \\
CREATE PROCEDURE test_proc0 (OUT p_p1 datetime(0),OUT p_p2 time(0),OUT p_p3 timestamp(0)) BEGIN SELECT 1 ; END; \\
CREATE PROCEDURE test_proc1 (OUT p_p1 datetime(1),OUT p_p2 time(1),OUT p_p3 timestamp(1)) BEGIN SELECT 1 ; END; \\
CREATE PROCEDURE test_proc2 (OUT p_p1 datetime(2),OUT p_p2 time(2),OUT p_p3 timestamp(2)) BEGIN SELECT 1 ; END; \\
CREATE PROCEDURE test_proc3 (OUT p_p1 datetime(3),OUT p_p2 time(3),OUT p_p3 timestamp(3)) BEGIN SELECT 1 ; END; \\
CREATE PROCEDURE test_proc4 (OUT p_p1 datetime(4),OUT p_p2 time(4),OUT p_p3 timestamp(4)) BEGIN SELECT 1 ; END; \\
CREATE PROCEDURE test_proc5 (OUT p_p1 datetime(5),OUT p_p2 time(5),OUT p_p3 timestamp(5)) BEGIN SELECT 1 ; END; \\
CREATE PROCEDURE test_proc6 (OUT p_p1 datetime(6),OUT p_p2 time(6),OUT p_p3 timestamp(6)) BEGIN SELECT 1 ; END; \\
CREATE FUNCTION test_func (f_p1 DATETIME, f_p2 TIMESTAMP) RETURNS TIME RETURN NULL;
CREATE FUNCTION test_func0 (f_p1 DATETIME(0), f_p2 TIMESTAMP(0)) RETURNS TIME(0) RETURN NULL;
CREATE FUNCTION test_func1 (f_p1 DATETIME(1), f_p2 TIMESTAMP(1)) RETURNS TIME(1) RETURN NULL;
CREATE FUNCTION test_func2 (f_p1 DATETIME(2), f_p2 TIMESTAMP(2)) RETURNS TIME(2) RETURN NULL;
CREATE FUNCTION test_func3 (f_p1 DATETIME(3), f_p2 TIMESTAMP(3)) RETURNS TIME(3) RETURN NULL;
CREATE FUNCTION test_func4 (f_p1 DATETIME(4), f_p2 TIMESTAMP(4)) RETURNS TIME(4) RETURN NULL;
CREATE FUNCTION test_func5 (f_p1 DATETIME(5), f_p2 TIMESTAMP(5)) RETURNS TIME(5) RETURN NULL;
CREATE FUNCTION test_func6 (f_p1 DATETIME(6), f_p2 TIMESTAMP(6)) RETURNS TIME(6) RETURN NULL;
SELECT SPECIFIC_SCHEMA, SPECIFIC_NAME, PARAMETER_NAME, DATA_TYPE,
DATETIME_PRECISION from INFORMATION_SCHEMA.PARAMETERS WHERE
SPECIFIC_SCHEMA='i_s_parameters_test' ORDER BY SPECIFIC_NAME,PARAMETER_NAME;
SPECIFIC_SCHEMA SPECIFIC_NAME PARAMETER_NAME DATA_TYPE DATETIME_PRECISION
i_s_parameters_test test_func NULL time 0
i_s_parameters_test test_func f_p1 datetime 0
i_s_parameters_test test_func f_p2 timestamp 0
i_s_parameters_test test_func0 NULL time 0
i_s_parameters_test test_func0 f_p1 datetime 0
i_s_parameters_test test_func0 f_p2 timestamp 0
i_s_parameters_test test_func1 NULL time 1
i_s_parameters_test test_func1 f_p1 datetime 1
i_s_parameters_test test_func1 f_p2 timestamp 1
i_s_parameters_test test_func2 NULL time 2
i_s_parameters_test test_func2 f_p1 datetime 2
i_s_parameters_test test_func2 f_p2 timestamp 2
i_s_parameters_test test_func3 NULL time 3
i_s_parameters_test test_func3 f_p1 datetime 3
i_s_parameters_test test_func3 f_p2 timestamp 3
i_s_parameters_test test_func4 NULL time 4
i_s_parameters_test test_func4 f_p1 datetime 4
i_s_parameters_test test_func4 f_p2 timestamp 4
i_s_parameters_test test_func5 NULL time 5
i_s_parameters_test test_func5 f_p1 datetime 5
i_s_parameters_test test_func5 f_p2 timestamp 5
i_s_parameters_test test_func6 NULL time 6
i_s_parameters_test test_func6 f_p1 datetime 6
i_s_parameters_test test_func6 f_p2 timestamp 6
i_s_parameters_test test_proc p_p1 datetime 0
i_s_parameters_test test_proc p_p2 time 0
i_s_parameters_test test_proc p_p3 timestamp 0
i_s_parameters_test test_proc0 p_p1 datetime 0
i_s_parameters_test test_proc0 p_p2 time 0
i_s_parameters_test test_proc0 p_p3 timestamp 0
i_s_parameters_test test_proc1 p_p1 datetime 1
i_s_parameters_test test_proc1 p_p2 time 1
i_s_parameters_test test_proc1 p_p3 timestamp 1
i_s_parameters_test test_proc2 p_p1 datetime 2
i_s_parameters_test test_proc2 p_p2 time 2
i_s_parameters_test test_proc2 p_p3 timestamp 2
i_s_parameters_test test_proc3 p_p1 datetime 3
i_s_parameters_test test_proc3 p_p2 time 3
i_s_parameters_test test_proc3 p_p3 timestamp 3
i_s_parameters_test test_proc4 p_p1 datetime 4
i_s_parameters_test test_proc4 p_p2 time 4
i_s_parameters_test test_proc4 p_p3 timestamp 4
i_s_parameters_test test_proc5 p_p1 datetime 5
i_s_parameters_test test_proc5 p_p2 time 5
i_s_parameters_test test_proc5 p_p3 timestamp 5
i_s_parameters_test test_proc6 p_p1 datetime 6
i_s_parameters_test test_proc6 p_p2 time 6
i_s_parameters_test test_proc6 p_p3 timestamp 6
DROP DATABASE i_s_parameters_test;
Reference in New Issue View Git Blame Copy Permalink