前言
了解存储过程函数,用其提高SQL性能。
一、存储过程
一、存储过程和函数
含义)封存存储过程的细节,预处理好的批SQL语句。
优点)
1)提高代码的重用性
2)简化操作
3)不用重复编译
4)减少和数据库连接次数,一个过程一个连接,即多SQL只有一个连接。
创建语法)
delimiter 结束标记
create procedure 存储过程名(参数列表)
BEGIN(如果里面SQL只有一句,则可省略)
系列SQL;
END 结束标记
1)参数
参数模式 参数名 参数类
IN stuName VARCHAR(20)
参数模式)IN(需调用方传入值) OUT(返回值) INOUT(既输入又输出)
调用语法)
CALL 存储过程名(实参列表);
2)案例
例1)一次性插入多条记录
DELIMITER $
CREATE PROCEDURE insertPR ( ) BEGIN
INSERT INTO admin ( id, `name` )
VALUES
( NULL, 'a' ),
( NULL, 'b' ),
( NULL, 'c' ),
( NULL, 'd' );
END $
CALL insertPR ( ) $
例2)根据女生名,查询对应男生信息
CREATE PROCEDURE selectPR ( IN beautyName VARCHAR ( 20 ) ) BEGIN
SELECT
a.*
FROM
beauty a
LEFT JOIN boys b ON a.boyfriend_id = b.id
WHERE
beautyName = a.NAME;
end
END $
CALL selectPR ( '宋茜' );
例3)用户是否登录成功
CREATE PROCEDURE login ( IN username VARCHAR ( 10 ), IN PASSWORD VARCHAR ( 10 ) ) BEGIN
DECLARE
result INT; #声明
SELECT
count( 1 ) INTO result #赋值
FROM
admin a
WHERE
a.username = username
AND a.PASSWORD = b.PASSWORD;
SELECT
IF
( result = 0, 'failed', 'success' ) ; #使用
END $
CALL login ( 'lls', '123' ) $
例4)带out的
DELIMITER $
CREATE PROCEDURE login ( IN username VARCHAR ( 10 ), IN PASSWORD VARCHAR ( 10 ), OUT result INT ) BEGIN
SELECT
count( 1 ) INTO result #赋值
FROM
admin a
WHERE
a.username = username
AND a.PASSWORD = PASSWORD;
END $
CALL login ( 'lls', '123', @result ) $;
SELECT @result $
例5)带inout 翻倍
CREATE PROCEDURE extend ( INOUT a INT, INOUT b INT ) BEGIN
SET a = a * 2;# select a*2,b*2 into a,b;
SET b = b * 2;
END $
SET @val1 = 2 $
SET @val2 = 3 $ CALL extend ( @val1, @val2 ) $
SELECT @val1 + @val2 $
例6)传入女生,返回女生 and 男生(如果男生为null,则返回 and null)
CREATE PROCEDURE test ( IN beautyName, OUT str VARCHAR ( 50 ) ) BEGIN
SELECT
CONCAT( beautyName, ' and ', IFNULL( boyName, 'NULL' ) )
FROM
beauty a
LEFT JOIN boys b ON a.boyfriend_id = b.id
WHERE
beautyName = a.NAME;
END $
CALL test ( '小昭', @str ) $
SELECT @str $
二、删除存储过程
语法)drop procedure 存储过程名;
注:只能一个一个的删除
三、查看存储过程
语法)show create procedure 存储过程名;
二、函数
一、函数
区别于存储过程)
1)函数有且仅有一个返回值
2)存储过程适合做批量插入和更新,函数适合处理数据并返回结果。
语法)
delimiter $
create function 函数名(参数列表) returns 返回类型
begin(函数体只有一句话时可省略begin end)
函数体
end $
select 函数名(参数列表) $
例1)无参有返回,返回公司员工个数
USE employees;
delimiter $
CREATE FUNCTION myf1 ( ) RETURNS INT BEGIN
DECLARE
c INT DEFAULT 0;
SELECT
count( 1 ) INTO c
FROM
employees;
RETURN c;
END $
SELECTmyf1 ( ) $
例2)有参有返回,根据员工名,返回它的工资
CREATE FUNCTION myf2 ( empName VARCHAR ( 20 ) ) RETURNS DOUBLE BEGIN
SET @sla = 0;#声明
SELECT
salary INTO @sla #赋值
FROM
employees
WHERE
empName = employees.NAME;
RETURN @sal;
END $
SELECT myf2 ( 'king' ) $
二、查看函数
show create function 函数名;
show create function myf3;
三、删除函数
drop function 函数名
drop function myf3;
总结
1)如何创建储存过程
2)存储过程的删除和查看
3)函数
参考文献
[1] MySQL 尚硅谷