【MySQL必知必会】使用存储过程

存储过程

存储过程简单来说就是为以后的使用而保存的一条或多条MySQL语句集合。这里可以理解为在实际项目中，可能会遇到不同逻辑的MySQL，这个时候需要把这些语句集合起来，相当于是一个文件。

为什么使用存储过程

通过把处理封装在容易使用的单元中，简化复杂的操作。
由于不要求反复建立一系列处理步骤，这保证了数据的完整性。如果所有开发人员和应用程序都使用同一存储过程，则所使用的代码都是相同的。这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。
简化对变动的管理。如果表名、列名或业务逻辑有变化，只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。
提高性能。因为使用存储过程比使用比使用单独的SQL语句要快。
存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用他们来编写功能更强更灵活的代码。

存储过程的优缺点

存储过程会将写好的SQL语句预编译过，所以在运行的时候会很快
存储过程可以接受参数，输出参数。可以向程序返回错误原因
存储过程可以包含程序流、逻辑以及数据库的查询。同时可以实体封装和隐藏了数据逻辑
存储过程可以控制权限，比如一个表不允许用户直接访问，但允许用户访问和修改字段，那就可以通过存储过程来实现

使用存储过程

执行存储过程的关键词CALL

CALL productpricing(@pricelow,
                    @pricehigh,
                    @priceaverage);

其中执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格。

创建存储过程

存储过程的结束符为;，但是在默认情况下MySQL会将分号识别为语句分隔符，所以会出现问题倒是无法创建存储过程，所以需要临时修改结束符。
因为终端(命令行程序)在解释MySQL语句的时候会把本身在存储过程中的成分;误认为是分隔符，所以会导致语法错误。解决办法是：
可以用delimiter命令来重新定义，创建成功后再重新回复到;。

mysql> delimiter // #修改存储过程结束符

mysql> CREATE PROCEDURE dorepeat(p1 INT)
    -> BEGIN
    ->   SET @x = 0;
    ->   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL dorepeat(1000);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x;
+------+
| @x   |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)

此存储过程名为dorepeat，用CREATE PROCEDURE salaryaverage()定义，括号内可传参数，传的参数名为p1的INT，在此过程中将@x做1的累加，知道大于p1为止。所以获取@x值为1001

CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

proc_parameter:
    [ IN | OUT | INOUT ] param_name type

characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }

routine_body:
　　Valid SQL routine statement

[begin_label:] BEGIN
　　[statement_list]

END [end_label]

括号内的参数：
proc_parameter:

in：向过程里传参
out：过程向外传参值
inout：in and out

建立智能存储过程

以一个实际场景为例：

create        -- 存储过程头部区域开始
or replace    --可选表示如果数据库中已经存在一条相同名称的存储过程就把它替换掉
procedure
article_create --存储过程名称 procedure_name
(
id int, title varchar2, body text
) -- parameter_declaration 声明参数（注意不需要写长度），存储过程头部区域结束
as
--声明区域，不需要声明变量可以不写
begin -- PL/SQL标准执行语句
--执行区域
  insert into artivles values(id, title, body);
end;

例2- 比较完整的智能存储过程

-- Name:ordertotal
-- Parmeters:onumber = order number
--           taxable = 0 if not taxable
--           ototal = order total variable
CREATE PROCEDURE ordretotal(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
)COMMENT 'obtain order total,optiomally adding tax'
BEGIN
	-- dexlare variable for total
	DECLARE total DECIMAL(8,2);
	-- delcare tax percentage
	DECLARE taxtate INT DEFAULT 6;
	-- get the ordertotal
	SELECT Sum(item_price*quantity)
	FROM orderitems
	WHERE order_num = onumber INTO total;
	-- is this taxable?
	IF taxable THEN
		-- yes,so add taxrate to the total
		SELECT total+(total/100*taxrate) INTO total;
	END IF;
	-- and finally,save to out variable
	SELECT total INTO ototal;
END;

头部 -- Name:ordertotal等为注释
在ordretotal()中传入参数，IN表示向过程里传参,OUT表示过程向外传参也就是返回
COMMENT – 描述
在执行过程中 DECLARE表示定义局部变量, 后面为变量名和数据类型
最后就是IF语句的基本用法，IF…THEN … END IF;

检查存储过程

SHOW CREATE PROCEDURE ordertotal; – 显示用来创建一个存储过程的CREATE语句

SHOW PROCEDURE STATUS; – 获得包括何时、由谁创建等详细信息的存储过程列表

loannes's blog