SQL存储过程和函数

SQL存储过程：

由来：在具体应用中，一个完整的操作会包含多条SQL语句，在执行过程中需要根据前面SQL语句的执行结果有选择的执行后面的SQL语句。因此，mysql提供了数据库对象存储过程和函数。

定义：存储过程就是作为可执行对象存放在数据库中的一个或多个SQL命令。和函数的区别在于，函数有返回值，存储过程没有。

优点：

1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

2.当对数据库进行复杂操作时，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

3.存储过程可以重复使用,可减少数据库开发人员的工作量。

4.安全性高,可设定只有某些用户才具有对指定存储过程的使用权

创建存储过程：

在MySQL中创建存储过程通过SQL语句CREATE PROCEDURE来实现，其语法形式如下：

CREATE PROCEDURE procedure_name([procedure_paramter[,…]])

[characteristic…] routine_body

实例：

DELIMITER $$     //设置分隔符
CREATE PROCEDURE proce_employee_sal ()
COMMENT'查询所有雇员的工资'
BEGIN
    SELECT sal
    FROM t_employee;
END$$
DELIMITER ;    //恢复分隔符

调用存储过程：

用 call调用存储过程

创建函数：

在MySQL中创建函数通过SQL语句CREATE Function来实现，其语法形式如下：

CREATE FUNCTION function _name([function_paramter[,…]])

[characteristic…] routine_body

例子：根据雇员编号查询薪资

DELIMITER $$
CREATE FUNCTION func_employee_sal (empno INT(11))   //传入值
    RETURNS DOUBLE(10,2)     //返回值
COMMENT'查询某个雇员的工资'
BEGIN
    RETURN (SELECT sal
        FROM t_employee
        WHERE t_employee.empno=empno);
END$$
DELIMITER ;

调用函数：

call/select func_employee_sal(7035);

是否和创建触发器的过程很类似呢？

存储过程中的变量：

1．声明变量

在MySQL中定义变量通过关键字DECLARE来实现，其语法形式如下：

DECLARE var_name[,…] type [DEFAULT value]

2．赋值变量

在MySQL中为变量赋值通过关键字SET来实现，其语法形式如下：

SET var_name=expr[,…]

当为变量赋值时，除了上述语法外，还可以通过关键字“SELECT……INTO”语句来实现，其语法形式如下：

SELECT field_name[,…] INTO var_name[,…]

  FROM table_name

  WHERE condition

存储过程中的游标：

sql的查询语句可以返回多条记录结果，如果想要遍历这些查询结果，就可以使用游标来实现。相当于C++里的指针。

1．声明游标

DECLARE cursor_name CURSOR FOR select_statement;

2．打开游标

OPEN cursor_name

3．使用游标

FETCH cursor_name INTO var_name [,var_name] …

4．关闭游标

CLOSE cursor_name

例子：统计工资大于999的雇员人数

DROP PROCEDURE IF EXISTS emplayee_count;
DELIMITER $
#创建存储过程
CREATE PROCEDURE emplayee_count (OUT NUM INTEGER)　　　　　　//out表示输出，num为输出的变量
BEGIN
#声明变量
DECLARE   emplayee_sal INTEGER;DECLARE   flag INTEGER;
#声明游标
DECLARE cursor_emplayee 
    CURSOR FOR SELECT sal FROM t_employee;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = 1;
#设置结束标志
SET flag=0;SET NUM=0;
#打开游标
OPEN cursor_emplayee;
#遍历游标指向的结果集
FETCH cursor_emplayee INTO emplayee_sal;
WHILE flag<>1 DO
IF emplayee_sal >999 THEN
SET num=num+1;
END IF;
FETCH cursor_emplayee INTO emplayee_sal;   //下一条记录
END WHILE;
#关闭游标
CLOSE cursor_emplayee;
END$
DELIMITER ;

查看存储过程和函数：

• 通过SHOW PROCEDURE STATUS语句查看储存过程状态信息
• 
• 通过SHOW FUNCTION STATUS语句查看函数状态信息
• 通过SHOW CREATE PROCEDURE语句查看储存过程定义信息
• 
• 通过SHOW CREATE FUNCTION语句查看函数定义信息

修改存储过程：

在MySQL数据库管理系统中修改存储过程通过SQL语“ALTER PROCEDURE”来实现，其语法形式如下：

ALTER PROCEDURE procedure_name

[characteristic…]

修改函数：

在MySQL数据库管理系统中修改函数通过SQL语“ALTER FUNCTION”来实现，其语法形式如下：

ALTER FUNCTION function_name

[characteristic…]

删除存储过程和函数：

DROP PROCEDURE proce_name;

DROP FUNCTION func_name;

原文链接: https://www.cnblogs.com/LUO77/p/5880485.html

欢迎关注

微信关注下方公众号，第一时间获取干货硬货；公众号内回复【pdf】免费获取数百本计算机经典书籍