存储过程和存储函数

1.常见的几种存储过程:

(1).无参数无返回值：

#1. 创建存储过程insert_user(),实现传入用户名和密码，插入到admin表中
DELIMITER //
CREATE PROCEDURE insert_user(IN id1 INT,IN pwd1 VARCHAR(25))
BEGIN 
    INSERT INTO ADMIN (id,pwd)
    VALUES (id1,pwd1);
END //
DELIMITER;

格式:

DELIMITER //

CREATE PROCEDURE 函数名()

BEGIN

　　函数的有关定义;

END //

DELIMITER ;

知识点补充：DELIMITER //的作用是让语句的终止符变为'//'而不是默认的';',因为函数定义的最后要使用';',而此时函数又未结束，所以需重新定义一个结束符 。

　　　　　　DELIMITER;的意义是情况DELIMITER //的操作

 

(2).无参数有返回值

#举例2：创建存储过程avg_employee_salary()，返回所有员工的平均工资

DELIMITER //

CREATE PROCEDURE avg_employee_salary()
BEGIN 
    SELECT AVG(salary) FROM employees;
    
END //

如果有返回值,则函数定义中一定有一个'select'关键词,其后跟的内容就是返回值的内容

 

无参函数的调用：

#调用
CALL avg_employee_salary();

使用关键词'call'来进行调用 

 

(3).有参数:

参数共有两种类型:输入型变量(IN)和输出型变量(OUT)

输入性变量:

#举例5：创建存储过程show_someone_salary()，查看“emps”表的某个员工的薪资，
#并用IN参数empname输入员工姓名。

DELIMITER //

CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGIN
    SELECT salary FROM employees
    WHERE last_name = empname;
END //

DELIMITER ;

输出型变量:

DELIMITER //

CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN
    SELECT MIN(salary) INTO ms
    FROM employees;
END //

DELIMITER ;

 

参数变量的声明格式:

变量类型(IN/OUT)  变量名  变量的数据类型(INT/VARCHAR(25)等)

#调用方式1
CALL show_someone_salary('Abel');
#调用方式2
SET @empname := 'Abel';
CALL show_someone_salary(@empname);

有参函数的调用：

1.如果是常量则直接引入参数

2.如果是变量名则需在变量名前加上'@'

 

2.常见的几种储存函数：

(1).无参有返回：

#无参有返回
#1. 创建函数get_count(),返回公司的员工个数
DELIMITER //
CREATE FUNCTION get_count()
RETURNS INT #此处一定要写为returns,不是return!
    READS SQL DATA#写明一个函数安全类型，防止函数定义其报错
BEGIN
    RETURN (SELECT COUNT(*) FROM employees);
END //
DELIMITER;

(2).有参有返回：

#有参有返回
#2. 创建函数ename_salary(),根据员工姓名，返回它的工资
DELIMITER //
CREATE FUNCTION ename_salary(emp_name VARCHAR(25))#此处不要写in表示输入变量(默认是输入变量)
RETURNS DOUBLE 
       READS SQL DATA
BEGIN
    RETURN (SELECT salary FROM employees WHERE last_name = emp_name);
END //
DELIMITER;

SELECT ename_salary('Abel');

函数定义的一般格式：

DELIMITER //

CREATE FUNCION 函数名 (参数名 参数数据类型)

RETURNS DOUBLE

　　　　　READS SQL DATA

BEGIN

　　　　　RETURN (SELECT ...);

END //

DELIMITER;

 

为了保证函数创建一定能够成功，可以加入该语句：

#创建函数前执行此语句，保证函数的创建会成功
SET GLOBAL log_bin_trust_function_creators = 1;

三个注意点:

1.声明返回值的类型时,写RETURNS而不是RETURN

2.存储函数的所有参数都默认未输入变量,所以无需声明IN，所有的参数声明形式均为:  变量名  变量数据类型

3.函数定义时一定要声明一个安全类型，防止其编译时发生报错

 

存储函数的调用形式：

(1).常量为参数

SELECT ename_salary('Abel');

(2).变量为参数

SET @emp_id := 102;
SELECT email_by_id(@emp_id);

其参数调用原理与存储过程基本相同，涉及到的关键词为'select'