存储过程
输入参数 IN
DROP PROCEDURE IF EXISTS test_in;
DELIMITER //
CREATE PROCEDURE test_in (IN a INTEGER)
BEGIN
DECLARE v VARCHAR(20); #declare 局部变量声明方式 @符全局变量声明
if a=1 THEN
SET v='111';
ELSE
SET v='000';
END IF;
INSERT INTO t_procedure (mark) VALUE (v);
END;
//
# 调用存储过程
call test_in(1);
输出参数OUT
#此时INOUT(即可为输入参数也可为输出参数) 输出结果也一样
DROP PROCEDURE IF EXISTS test_out;
DELIMITER //
CREATE PROCEDURE test_out (OUT a INTEGER)
BEGIN
select COUNT(1) INTO a from t where id<10;
END;
//
# 调用存储过程
call test_out(@b);
select @b; # 9
IN、OUT参数组合使用
DELIMITER // #声明结束符// 避免和存储过程中分号冲突
CREATE PROCEDURE proc_count (IN id INT, OUT t_count INT)
READS SQL DATA
BEGIN
SELECT count(1) INTO t_count FROM t_procedure where id>0;
END
// # 结束
call proc_count(4, @a);
select @a; #2
while循环
DROP PROCEDURE IF EXISTS pro10;
create procedure pro10()
BEGIN
declare i int;
set i=1;
while i<12 DO
select * from test where id<i;
set i=i+1;
end while;
end;
call pro10();
查看存储过程详情示例
SHOW PROCEDURE STATUS LIKE 'test_in';
存储函数
DROP FUNCTION IF EXISTS func_test;
CREATE FUNCTION func_test(param_id INT)
RETURNS INT(10)
BEGIN
RETURN(select count(1) from t where id < param_id);
END;
##调用存储函数
select func_test(100); ## 99
查看存储函数详情示例
SHOW FUNCTION STATUS LIKE 'func_test';
分析sql语句
查看具体sql耗时
select * from t where a BETWEEN 0 AND 1000 and b BETWEEN 1000 AND 50000;
show PROFILES;
show PROFILE for query 34;
status | duration |
---|---|
starting | ---- |
checking permissions | 6E-6 |
Opening tables | 1.9E-5 |
init | 2.5E-5 |
System lock | 1E-5 |
optimizing | 1.2E-5 |
statistics | 8.2E-5 |
preparing | 1.4E-5 |
executing | 3E-6 |
Sending data | 0.000658 |
end | 4E-6 |
query end | 3E-6 |
closing tables | 7E-6 |
freeing items | 2.7E-5 |
cleaning up | 1E-5 |