存储过程和函数
存储过程
一、创建语法
CREATE PROCEDURE 存储过程名( 参数列表)
BEGIN
存储过程体(一组合法的SQL 语句)
END
1,注意:
二、调用语法
CALL 存储过程名( 实参列表) ;
SELECT * FROM admin;
DELIMITER $
CREATE PROCEDURE myp1( )
BEGIN
INSERT INTO admin( username, ` password` )
VALUES ( 'john1' , '0000' ) , ( 'lily' , '0000' ) , ( 'rose' , '0000' ) , ( 'jack' , '0000' ) , ( 'tom' , '0000' ) ;
END $
CALL myp1( ) $
select * from admin$
CREATE PROCEDURE myp2( IN beautyName VARCHAR ( 20 ) )
BEGIN
SELECT bo. *
FROM boys bo
RIGHT JOIN beauty b ON bo. id = b. boyfriend_id
WHERE b. name= beautyName;
END $
CALL myp2( '柳岩' ) $
CREATE PROCEDURE myp4( IN username VARCHAR ( 20 ) , IN PASSWORD VARCHAR ( 20 ) )
BEGIN
DECLARE result INT DEFAULT 0 ;
SELECT COUNT ( * ) INTO result
FROM admin
WHERE admin. username = username
AND admin. password = PASSWORD;
SELECT IF ( result> 0 , '成功' , '失败' ) ;
END $
CALL myp3( '张飞' , '8888' ) $
CREATE PROCEDURE myp6( IN beautyName VARCHAR ( 20 ) , OUT boyName VARCHAR ( 20 ) )
BEGIN
SELECT bo. boyname INTO boyname
FROM boys bo
RIGHT JOIN
beauty b ON b. boyfriend_id = bo. id
WHERE b. name= beautyName ;
END $
CREATE PROCEDURE myp7( IN beautyName VARCHAR ( 20 ) , OUT boyName VARCHAR ( 20 ) , OUT usercp INT )
BEGIN
SELECT boys. boyname , boys. usercp INTO boyname, usercp
FROM boys
RIGHT JOIN
beauty b ON b. boyfriend_id = boys. id
WHERE b. name= beautyName ;
END $
CALL myp7( '小昭' , @name , @cp ) $
SELECT @name , @cp$
CREATE PROCEDURE myp8( INOUT a INT , INOUT b INT )
BEGIN
SET a= a* 2 ;
SET b= b* 2 ;
END $
SET @m = 10 $
SET @n = 20 $
CALL myp8( @m , @n ) $
SELECT @m , @n$
三、删除存储过程
DROP PROCEDURE p1;
DROP PROCEDURE p2, p3;
四、查看存储过程的信息
DESC myp2; ×
SHOW CREATE PROCEDURE myp2;
存储过程案例讲解
一、创建存储过程实现传入用户名和密码,插入到admin表中
CREATE PROCEDURE test_pro1( IN username VARCHAR ( 20 ) , IN loginPwd VARCHAR ( 20 ) )
BEGIN
INSERT INTO admin( admin. username, PASSWORD)
VALUES ( username, loginpwd) ;
END $
二、创建存储过程实现传入女神编号,返回女神名称和女神电话
CREATE PROCEDURE test_pro2( IN id INT , OUT NAME VARCHAR ( 20 ) , OUT phone VARCHAR ( 20 ) )
BEGIN
SELECT b. name , b. phone INTO NAME, phone
FROM beauty b
WHERE b. id = id;
END $
三、创建存储存储过程或函数实现传入两个女神生日,返回大小
CREATE PROCEDURE test_pro3( IN birth1 DATETIME , IN birth2 DATETIME , OUT result INT )
BEGIN
SELECT DATEDIFF( birth1, birth2) INTO result;
END $
四、创建存储过程或函数实现传入一个日期,格式化成xx年xx月xx日并返回
CREATE PROCEDURE test_pro4( IN mydate DATETIME , OUT strDate VARCHAR ( 50 ) )
BEGIN
SELECT DATE_FORMAT( mydate, '%y年%m月%d日' ) INTO strDate;
END $
CALL test_pro4( NOW ( ) , @str ) $
SELECT @str $
五、创建存储过程或函数实现传入女神名称,返回:女神 and 男神 格式的字符串
如 传入 :小昭
返回: 小昭 AND 张无忌
DROP PROCEDURE test_pro5 $
CREATE PROCEDURE test_pro5( IN beautyName VARCHAR ( 20 ) , OUT str VARCHAR ( 50 ) )
BEGIN
SELECT CONCAT( beautyName, ' and ' , IFNULL( boyName, 'null' ) ) INTO str
FROM boys bo
RIGHT JOIN beauty b ON b. boyfriend_id = bo. id
WHERE b. name= beautyName;
SET str=
END $
CALL test_pro5( '柳岩' , @str ) $
SELECT @str $
六、创建存储过程或函数,根据传入的条目数和起始索引,查询beauty表的记录
DROP PROCEDURE test_pro6$
CREATE PROCEDURE test_pro6( IN startIndex INT , IN size INT )
BEGIN
SELECT * FROM beauty LIMIT startIndex, size;
END $
CALL test_pro6( 3 , 5 ) $
函数
一、创建语法
CREATE FUNCTION 函数名( 参数列表) RETURNS 返回类型
BEGIN
函数体
END
二、调用语法
SELECT 函数名( 参数列表)
CREATE FUNCTION myf1( ) RETURNS INT
BEGIN
DECLARE c INT DEFAULT 0 ;
SELECT COUNT ( * ) INTO c
FROM employees;
RETURN c;
END $
SELECT myf1( ) $
CREATE FUNCTION myf2( empName VARCHAR ( 20 ) ) RETURNS DOUBLE
BEGIN
SET @sal = 0 ;
SELECT salary INTO @sal
FROM employees
WHERE last_name = empName;
RETURN @sal ;
END $
SELECT myf2( 'k_ing' ) $
CREATE FUNCTION myf3( deptName VARCHAR ( 20 ) ) RETURNS DOUBLE
BEGIN
DECLARE sal DOUBLE ;
SELECT AVG ( salary) INTO sal
FROM employees e
JOIN departments d ON e. department_id = d. department_id
WHERE d. department_name= deptName;
RETURN sal;
END $
SELECT myf3( 'IT' ) $
三、查看函数
SHOW CREATE FUNCTION myf3;
四、删除函数
DROP FUNCTION myf3;
案例
CREATE FUNCTION test_fun1( num1 FLOAT , num2 FLOAT ) RETURNS FLOAT
BEGIN
DECLARE SUM FLOAT DEFAULT 0 ;
SET SUM= num1+ num2;
RETURN SUM;
END $
SELECT test_fun1( 1 , 2 ) $