sp-vars.result

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

DROP PROCEDURE IF EXISTS p1;
CREATE PROCEDURE p1()
BEGIN
DECLARE v1 BIT;
DECLARE v2 BIT(1);
DECLARE v3 BIT(3) DEFAULT b'101';
DECLARE v4 BIT(64) DEFAULT 0x5555555555555555;
DECLARE v5 BIT(3);
DECLARE v6 BIT(64);
DECLARE v7 BIT(8) DEFAULT 128;
DECLARE v8 BIT(8) DEFAULT '128';
DECLARE v9 BIT(8) DEFAULT ' 128';
DECLARE v10 BIT(8) DEFAULT 'x 128';
SET v1 = v4;
SET v2 = 0;
SET v5 = v4; # check overflow
SET v6 = v3; # check padding
SELECT HEX(v1);
SELECT HEX(v2);
SELECT HEX(v3);
SELECT HEX(v4);
SELECT HEX(v5);
SELECT HEX(v6);
SELECT HEX(v7);
SELECT HEX(v8);
SELECT HEX(v9);
SELECT HEX(v10);
END|
CALL p1();
HEX(v1)
1
HEX(v2)
0
HEX(v3)
5
HEX(v4)
5555555555555555
HEX(v5)
7
HEX(v6)
5
HEX(v7)
80
HEX(v8)
FF
HEX(v9)
FF
HEX(v10)
FF
Warnings:
Warning	1264	Out of range value adjusted for column 'v8' at row 1
Warning	1264	Out of range value adjusted for column 'v9' at row 1
Warning	1264	Out of range value adjusted for column 'v10' at row 1
Warning	1264	Out of range value adjusted for column 'v1' at row 1
Warning	1264	Out of range value adjusted for column 'v5' at row 1
DROP PROCEDURE p1;

---------------------------------------------------------------
CASE expression tests.
---------------------------------------------------------------

DROP PROCEDURE IF EXISTS p1;
Warnings:
Note	1305	PROCEDURE p1 does not exist
DROP PROCEDURE IF EXISTS p2;
Warnings:
Note	1305	PROCEDURE p2 does not exist
DROP TABLE IF EXISTS t1;
Warnings:
Note	1051	Unknown table 't1'
CREATE TABLE t1(log_msg VARCHAR(1024));
CREATE PROCEDURE p1(arg VARCHAR(255))
BEGIN
INSERT INTO t1 VALUES('p1: step1');
CASE arg * 10
WHEN 10 * 10 THEN
INSERT INTO t1 VALUES('p1: case1: on 10');
WHEN 10 * 10 + 10 * 10 THEN
BEGIN
CASE arg / 10
WHEN 1 THEN
INSERT INTO t1 VALUES('p1: case1: case2: on 1');
WHEN 2 THEN
BEGIN
DECLARE i TINYINT DEFAULT 10;
WHILE i > 0 DO
INSERT INTO t1 VALUES(CONCAT('p1: case1: case2: loop: i: ', i));
CASE MOD(i, 2)
WHEN 0 THEN
INSERT INTO t1 VALUES('p1: case1: case2: loop: i is even');
WHEN 1 THEN
INSERT INTO t1 VALUES('p1: case1: case2: loop: i is odd');
ELSE
INSERT INTO t1 VALUES('p1: case1: case2: loop: ERROR');
END CASE;
SET i = i - 1;
END WHILE;
END;
ELSE
INSERT INTO t1 VALUES('p1: case1: case2: ERROR');
END CASE;
CASE arg
WHEN 10 THEN
INSERT INTO t1 VALUES('p1: case1: case3: on 10');
WHEN 20 THEN
INSERT INTO t1 VALUES('p1: case1: case3: on 20');
ELSE
INSERT INTO t1 VALUES('p1: case1: case3: ERROR');
END CASE;
END;
ELSE
INSERT INTO t1 VALUES('p1: case1: ERROR');
END CASE;
CASE arg * 10
WHEN 10 * 10 THEN
INSERT INTO t1 VALUES('p1: case4: on 10');
WHEN 10 * 10 + 10 * 10 THEN
BEGIN
CASE arg / 10
WHEN 1 THEN
INSERT INTO t1 VALUES('p1: case4: case5: on 1');
WHEN 2 THEN
BEGIN
DECLARE i TINYINT DEFAULT 10;
WHILE i > 0 DO
INSERT INTO t1 VALUES(CONCAT('p1: case4: case5: loop: i: ', i));
CASE MOD(i, 2)
WHEN 0 THEN
INSERT INTO t1 VALUES('p1: case4: case5: loop: i is even');
WHEN 1 THEN
INSERT INTO t1 VALUES('p1: case4: case5: loop: i is odd');
ELSE
INSERT INTO t1 VALUES('p1: case4: case5: loop: ERROR');
END CASE;
SET i = i - 1;
END WHILE;
END;
ELSE
INSERT INTO t1 VALUES('p1: case4: case5: ERROR');
END CASE;
CASE arg
WHEN 10 THEN
INSERT INTO t1 VALUES('p1: case4: case6: on 10');
WHEN 20 THEN
INSERT INTO t1 VALUES('p1: case4: case6: on 20');
ELSE
INSERT INTO t1 VALUES('p1: case4: case6: ERROR');
END CASE;
END;
ELSE
INSERT INTO t1 VALUES('p1: case4: ERROR');
END CASE;
END|
CREATE PROCEDURE p2()
BEGIN
DECLARE i TINYINT DEFAULT 3;
WHILE i > 0 DO
IF MOD(i, 2) = 0 THEN
SET @_test_session_var = 10;
ELSE
SET @_test_session_var = 'test';
END IF;
CASE @_test_session_var
WHEN 10 THEN
INSERT INTO t1 VALUES('p2: case: numerical type');
WHEN 'test' THEN
INSERT INTO t1 VALUES('p2: case: string type');
ELSE
INSERT INTO t1 VALUES('p2: case: ERROR');
END CASE;
SET i = i - 1;
END WHILE;
END|
CALL p1(10);
CALL p1(20);
CALL p2();
SELECT * FROM t1;
log_msg
p1: step1
p1: case1: on 10
p1: case4: on 10
p1: step1
p1: case1: case2: loop: i: 10
p1: case1: case2: loop: i is even
p1: case1: case2: loop: i: 9
p1: case1: case2: loop: i is odd
p1: case1: case2: loop: i: 8
p1: case1: case2: loop: i is even
p1: case1: case2: loop: i: 7
p1: case1: case2: loop: i is odd
p1: case1: case2: loop: i: 6
p1: case1: case2: loop: i is even
p1: case1: case2: loop: i: 5
p1: case1: case2: loop: i is odd
p1: case1: case2: loop: i: 4
p1: case1: case2: loop: i is even
p1: case1: case2: loop: i: 3
p1: case1: case2: loop: i is odd
p1: case1: case2: loop: i: 2
p1: case1: case2: loop: i is even
p1: case1: case2: loop: i: 1
p1: case1: case2: loop: i is odd
p1: case1: case3: on 20
p1: case4: case5: loop: i: 10
p1: case4: case5: loop: i is even
p1: case4: case5: loop: i: 9
p1: case4: case5: loop: i is odd
p1: case4: case5: loop: i: 8
p1: case4: case5: loop: i is even
p1: case4: case5: loop: i: 7
p1: case4: case5: loop: i is odd
p1: case4: case5: loop: i: 6
p1: case4: case5: loop: i is even
p1: case4: case5: loop: i: 5
p1: case4: case5: loop: i is odd
p1: case4: case5: loop: i: 4
p1: case4: case5: loop: i is even
p1: case4: case5: loop: i: 3
p1: case4: case5: loop: i is odd
p1: case4: case5: loop: i: 2
p1: case4: case5: loop: i is even
p1: case4: case5: loop: i: 1
p1: case4: case5: loop: i is odd
p1: case4: case6: on 20
p2: case: string type
p2: case: numerical type
p2: case: string type
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP TABLE t1;

---------------------------------------------------------------
BUG#14161
---------------------------------------------------------------

DROP TABLE IF EXISTS t1;
DROP PROCEDURE IF EXISTS p1;
CREATE TABLE t1(col BIGINT UNSIGNED);
INSERT INTO t1 VALUE(18446744073709551614);
CREATE PROCEDURE p1(IN arg BIGINT UNSIGNED)
BEGIN
SELECT arg;
SELECT * FROM t1;
SELECT * FROM t1 WHERE col = arg;
END|
CALL p1(18446744073709551614);
arg
18446744073709551614
col
18446744073709551614
col
18446744073709551614
DROP TABLE t1;
DROP PROCEDURE p1;

---------------------------------------------------------------
BUG#13705
---------------------------------------------------------------

DROP PROCEDURE IF EXISTS p1;
CREATE PROCEDURE p1(x VARCHAR(10), y CHAR(3)) READS SQL DATA
BEGIN
SELECT x, y;
END|
CALL p1('alpha', 'abc');
x	y
alpha	abc
CALL p1('alpha', 'abcdef');
x	y
alpha	abc
Warnings:
Warning	1265	Data truncated for column 'y' at row 1
DROP PROCEDURE p1;

---------------------------------------------------------------
BUG#13675
---------------------------------------------------------------

DROP PROCEDURE IF EXISTS p1;
DROP TABLE IF EXISTS t1;
CREATE PROCEDURE p1(x DATETIME)
BEGIN
CREATE TABLE t1 SELECT x;
SHOW CREATE TABLE t1;
DROP TABLE t1;
END|
CALL p1(NOW());
Table	Create Table
t1	CREATE TABLE "t1" (
  "x" datetime default NULL
)
CALL p1('test');
Table	Create Table
t1	CREATE TABLE "t1" (
  "x" datetime default NULL
)
Warnings:
Warning	1264	Out of range value adjusted for column 'x' at row 1
DROP PROCEDURE p1;

---------------------------------------------------------------
BUG#12976
---------------------------------------------------------------

DROP TABLE IF EXISTS t1;
DROP PROCEDURE IF EXISTS p1;
DROP PROCEDURE IF EXISTS p2;
CREATE TABLE t1(b BIT(1));
INSERT INTO t1(b) VALUES(b'0'), (b'1');
CREATE PROCEDURE p1()
BEGIN
SELECT HEX(b),
b = 0,
b = FALSE,
b IS FALSE,
b = 1,
b = TRUE,
b IS TRUE
FROM t1;
END|
CREATE PROCEDURE p2()
BEGIN
DECLARE vb BIT(1);
SELECT b INTO vb FROM t1 WHERE b = 0;
SELECT HEX(vb),
vb = 0,
vb = FALSE,
vb IS FALSE,
vb = 1,
vb = TRUE,
vb IS TRUE;
SELECT b INTO vb FROM t1 WHERE b = 1;
SELECT HEX(vb),
vb = 0,
vb = FALSE,
vb IS FALSE,
vb = 1,
vb = TRUE,
vb IS TRUE;
END|
call p1();
HEX(b)	b = 0	b = FALSE	b IS FALSE	b = 1	b = TRUE	b IS TRUE

0	1	1	1	0	0	0
1	0	0	0	1	1	1
call p2();
HEX(vb)	vb = 0	vb = FALSE	vb IS FALSE	vb = 1	vb = TRUE	vb IS TRUE
0	1	1	1	0	0	0
HEX(vb)	vb = 0	vb = FALSE	vb IS FALSE	vb = 1	vb = TRUE	vb IS TRUE
1	0	0	0	1	1	1
DROP TABLE t1;
DROP PROCEDURE p1;
DROP PROCEDURE p2;
DROP TABLE IF EXISTS table_12976_a;
DROP TABLE IF EXISTS table_12976_b;
DROP PROCEDURE IF EXISTS proc_12976_a;
DROP PROCEDURE IF EXISTS proc_12976_b;
CREATE TABLE table_12976_a (val bit(1));
CREATE TABLE table_12976_b(
appname varchar(15),
emailperm bit not null default 1,
phoneperm bit not null default 0);
insert into table_12976_b values ('A', b'1', b'1'), ('B', b'0', b'0');
CREATE PROCEDURE proc_12976_a()
BEGIN
declare localvar bit(1);
SELECT val INTO localvar FROM table_12976_a;
SELECT coalesce(localvar, 1)+1, coalesce(val, 1)+1 FROM table_12976_a;
END||
CREATE PROCEDURE proc_12976_b(
name varchar(15),
out ep bit,
out msg varchar(10))
BEGIN
SELECT emailperm into ep FROM table_12976_b where (appname = name);
IF ep is true THEN
SET msg = 'True';
ELSE
SET msg = 'False';
END IF;
END||
INSERT table_12976_a VALUES (0);
call proc_12976_a();
coalesce(localvar, 1)+1	coalesce(val, 1)+1
1	1
UPDATE table_12976_a set val=1;
call proc_12976_a();
coalesce(localvar, 1)+1	coalesce(val, 1)+1
2	2
call proc_12976_b('A', @ep, @msg);
select @ep, @msg;
@ep	@msg
1	True
call proc_12976_b('B', @ep, @msg);
select @ep, @msg;
@ep	@msg
0	False
DROP TABLE table_12976_a;
DROP TABLE table_12976_b;
DROP PROCEDURE proc_12976_a;
DROP PROCEDURE proc_12976_b;