memory_storedproc.result

来自「视频监控网络部分的协议ddns,的模块的实现代码,请大家大胆指正.」· RESULT 代码 · 共 1,325 行 · 第 1/5 页

CALL sp1( 'abc' );
f1
abc
SHOW PROCEDURE status like 'sp1';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment
db_storedproc	sp1	PROCEDURE	root@localhost	modified	created	INVOKER	
DROP PROCEDURE IF EXISTS sp1;
CREATE PROCEDURE sp1( f1 text ) comment 'this is simple' SELECT f1;
CALL sp1( 'abc' );
f1
abc
SHOW PROCEDURE status like 'sp1';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment
db_storedproc	sp1	PROCEDURE	root@localhost	modified	created	DEFINER	this is simple
DROP PROCEDURE sp1_thisisaveryverylongname234872934_thisisaveryverylongname234872934;
ERROR 42000: PROCEDURE db_storedproc.sp1_thisisaveryverylongname234872934_thisisaveryverylongname234872934 does not exist
DROP PROCEDURE sp1_thisisaveryverylongname234872934_thisisaveryverylongnameabcde;
ERROR 42000: PROCEDURE db_storedproc.sp1_thisisaveryverylongname234872934_thisisaveryverylongnameabcde does not exist
DROP PROCEDURE sp1;

Testcase 4.1.2:
---------------
Ensure that all clauses that should be supported are supported
CREATE FUNCTION
--------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS fn1;
CREATE FUNCTION fn1 (s char(20)) returns char(50)
return concat('hello, ', s, '!');
SELECT fn1('world');
fn1('world')
hello, world!
DROP FUNCTION IF EXISTS fn1;
CREATE FUNCTION fn1( f1 mediumtext ) returns mediumtext   language sql deterministic sql security definer comment 'this is simple'
  BEGIN
set @v1 = 'hello';
set f1 = concat( @v1, f1 );
return f1;
END//
SELECT fn1( ' world');
fn1( ' world')
hello world
SHOW FUNCTION STATUS LIKE 'fn1';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment
db_storedproc	fn1	FUNCTION	root@localhost	modified	created	DEFINER	this is simple
DROP FUNCTION IF EXISTS fn1;
CREATE FUNCTION fn1( f1 smallint ) returns smallint language sql not deterministic sql security invoker comment 'this is simple'
BEGIN
set f1 = 1 + f1;
return f1;
END//
SELECT fn1( 126 );
fn1( 126 )
127
SHOW FUNCTION STATUS LIKE 'fn1';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment
db_storedproc	fn1	FUNCTION	root@localhost	modified	created	INVOKER	this is simple
DROP FUNCTION IF EXISTS fn1;
CREATE FUNCTION fn1( f1 decimal(63, 31) ) returns decimal(63, 31) language sql not deterministic sql security invoker comment 'this is simple'
BEGIN
set f1 = 1000000 + f1;
return f1;
END//
ERROR 42000: Too big scale 31 specified for column ''. Maximum is 30.
SELECT fn1( 1.3326e+8 );
ERROR 42000: FUNCTION db_storedproc.fn1 does not exist
CREATE FUNCTION fn1( f1 decimal(63, 30) ) returns decimal(63, 30) language sql not deterministic sql security invoker comment 'this is simple'
BEGIN
set f1 = 1000000 + f1;
return f1;
END//
SELECT fn1( 1.3326e+8 );
fn1( 1.3326e+8 )
134260000.000000000000000000000000000000
SHOW FUNCTION STATUS LIKE 'fn1';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment
db_storedproc	fn1	FUNCTION	root@localhost	modified	created	INVOKER	this is simple
DROP FUNCTION IF EXISTS fn1;
CREATE FUNCTION fn1( f1 enum("value1", "value1") ) returns decimal(63, 30)  language sql not deterministic sql security invoker comment 'this is simple'
  BEGIN
return f1;
END//
Warnings:
Note	1291	Column '' has duplicated value 'value1' in ENUM
SELECT fn1( "value1" );
fn1( "value1" )
1.000000000000000000000000000000
Warnings:
Note	1291	Column '' has duplicated value 'value1' in ENUM
SHOW FUNCTION STATUS LIKE 'fn1';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment
db_storedproc	fn1	FUNCTION	root@localhost	modified	created	INVOKER	this is simple
DROP FUNCTION IF EXISTS fn1;
CREATE FUNCTION fn1( f1 set("value1", "value1") ) returns decimal(63, 30) language sql not deterministic sql security invoker comment 'this is simple'
  BEGIN
return f1;
END//
Warnings:
Note	1291	Column '' has duplicated value 'value1' in SET
SELECT fn1( "value1, value1" );
fn1( "value1, value1" )
1.000000000000000000000000000000
Warnings:
Note	1291	Column '' has duplicated value 'value1' in SET
Warning	1265	Data truncated for column 'f1' at row 1
SHOW FUNCTION STATUS LIKE 'fn1';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment
db_storedproc	fn1	FUNCTION	root@localhost	modified	created	INVOKER	this is simple
DROP FUNCTION IF EXISTS fn1;
CREATE FUNCTION fn1( f1 smallint ) returns smallint language sql
BEGIN
set f1 = 1 + f1;
return f1;
END//
SELECT fn1( 126 );
fn1( 126 )
127
SHOW FUNCTION STATUS LIKE 'fn1';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment
db_storedproc	fn1	FUNCTION	root@localhost	modified	created	DEFINER	
DROP FUNCTION IF EXISTS fn1;
CREATE FUNCTION fn1( f1 smallint ) returns smallint deterministic
BEGIN
set f1 = 1 + f1;
return f1;
END//
SELECT fn1( 126 );
fn1( 126 )
127
SHOW FUNCTION STATUS LIKE 'fn1';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment
db_storedproc	fn1	FUNCTION	root@localhost	modified	created	DEFINER	
DROP FUNCTION IF EXISTS fn1;
CREATE FUNCTION fn1( f1 smallint ) returns smallint not deterministic
BEGIN
set f1 = 1 + f1;
return f1;
END//
SELECT fn1( 126 );
fn1( 126 )
127
SHOW FUNCTION STATUS LIKE 'fn1';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment
db_storedproc	fn1	FUNCTION	root@localhost	modified	created	DEFINER	
DROP FUNCTION IF EXISTS fn1;
CREATE FUNCTION fn1( f1 smallint ) returns smallint
sql security definer
BEGIN
set f1 = 1 + f1;
return f1;
END//
SELECT fn1( 126 );
fn1( 126 )
127
SHOW FUNCTION STATUS LIKE 'fn1';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment
db_storedproc	fn1	FUNCTION	root@localhost	modified	created	DEFINER	
DROP FUNCTION IF EXISTS fn1;
CREATE FUNCTION fn1( f1 smallint ) returns smallint
sql security invoker
BEGIN
set f1 = 1 + f1;
return f1;
END//
SELECT fn1( 126 );
fn1( 126 )
127
SHOW FUNCTION STATUS LIKE 'fn1';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment
db_storedproc	fn1	FUNCTION	root@localhost	modified	created	INVOKER	
DROP FUNCTION IF EXISTS fn1;
CREATE FUNCTION fn1( f1 smallint ) returns smallint
comment 'this is simple'
BEGIN
set f1 = 1 + f1;
return f1;
END//
SELECT fn1( 126 );
fn1( 126 )
127
SHOW FUNCTION STATUS LIKE 'fn1';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment
db_storedproc	fn1	FUNCTION	root@localhost	modified	created	DEFINER	this is simple
DROP FUNCTION fn1;

Testcase 4.1.3:
---------------
Ensure that all clauses that should be supported are supported
SHOW CREATE PROC
--------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp1;
CREATE PROCEDURE sp1 (f1 char(20) )
SELECT * from t1 where f2 = f1;
show CREATE PROCEDURE sp1;
Procedure	sql_mode	Create Procedure
sp1		CREATE DEFINER=`root`@`localhost` PROCEDURE `sp1`(f1 char(20) )
SELECT * from t1 where f2 = f1
DROP PROCEDURE sp1;

Testcase 4.1.4:
---------------
show create function
--------------------------------------------------------------------------------
DROP FUNCTION IF EXISTS fn1;
CREATE FUNCTION fn1 (s char(20)) returns char(50)
return concat('hello, ', s, '!');
show CREATE FUNCTION fn1;
Function	sql_mode	Create Function
fn1		CREATE DEFINER=`root`@`localhost` FUNCTION `fn1`(s char(20)) RETURNS char(50)
return concat('hello, ', s, '!')
DROP FUNCTION fn1;

Testcase 4.1.5:
---------------
SHOW PROCEDURE status
--------------------------------------------------------------------------------
CREATE PROCEDURE sp5()
SELECT * from t1;
SHOW PROCEDURE status like 'sp5';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment
db_storedproc	sp5	PROCEDURE	root@localhost	modified	created	DEFINER	
DROP PROCEDURE sp5;

Testcase 4.1.6:
---------------
show function status
--------------------------------------------------------------------------------
CREATE FUNCTION fn5(a int) returns int
BEGIN
set @b = 0.9 * a;
return @b;
END//
SHOW FUNCTION STATUS LIKE 'fn5';
Db	Name	Type	Definer	Modified	Created	Security_type	Comment
db_storedproc	fn5	FUNCTION	root@localhost	modified	created	DEFINER	
DROP FUNCTION fn5;

Testcase 4.1.7:
---------------
CALL procedure
--------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp7a;
DROP PROCEDURE IF EXISTS sp7b;
DROP PROCEDURE IF EXISTS sp7c;
CREATE PROCEDURE sp7a(a char(20))
SELECT * from t1 where t1.f2 = a;
CALL sp7a( 'xyz' );
f1	f2	f3	f4	f5	f6
CREATE PROCEDURE sp7b (a char (20), out b char(20))
SELECT f1 into b from t1 where t1.f2= a;
CALL sp7b('xyz', @out_param);
Warnings:
Warning	1329	No data - zero rows fetched, selected, or processed
SELECT @out_param;
@out_param
NULL
CREATE PROCEDURE sp7c (a char (20), out b char(20), inout c int)
BEGIN
SELECT f1 into b from t1 where t1.f2=a;
update t1 set t1.f2=999 where t1.f4=c;
SELECT f2 into c from t1 where t1.f2=999;
END//
set @c=1;
CALL sp7c('xyz', @out_param, @c);
Warnings:
Warning	1329	No data - zero rows fetched, selected, or processed