存储过程详解
存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象。
存储过程是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
存储过程思想上很简单,就是数据库 SQL 语言层面的代码封装与重用。
一、存储过程的创建及调用
CREATE
[DEFINER = { user | CURRENT_USER }]
不写则默认为DEFINER=`DB_user`@`%`
PROCEDURE sp_name 自定义名字([proc_parameter[,...]]参数)
[characteristic ...]特性 routine_body sql语言主体
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 }
设置安全性:
DEFINER 只有定义者才能调用,默认为DEFINER;
INVOKER 拥有权限的使用者才能调用。
routine_body:
Valid SQL routine statement
[begin_label:] BEGIN
[statement_list]
……
END [end_label]
示例:
DELIMITER //
CREATE PROCEDURE sp_simon_test(IN season varchar(50))
BEGIN
SELECT * FROM goods_detail where 季节= season limit 10;
END //
DELIMITER;
调用存储过程
CALL proc_name ([parameter[,...]])
示例:
call sp_simon_test("夏")
二、存储过程的参数
MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT
create procedure 过程名字(IN | OUT | INOUT 参数名 参数类型)
IN 输入参数:表示调用者向过程传入值(传入值可以是字面量或变量)
OUT 输出参数:不接收输入的变量,表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
INOUT 输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)
in输入参数:
mysql> delimiter $$
mysql> create procedure in_param(in p_in int)
-> begin
-> select p_in;
-> set p_in=2;
-> select P_in;
-> end$$
mysql> delimiter ;
mysql> set @p_in=1;
mysql> call in_param(@p_in);
+------+
| p_in |
+------+
| 1 |
+------+
+------+
| P_in |
+------+
| 2 |
+------+
mysql> select @p_in;
+-------+
| @p_in |
+-------+
| 1 |
+-------+
out 输出参数:
mysql> delimiter //
mysql> create procedure out_param(out p_out int)
-> begin
-> select p_out;
-> set p_out=2;
-> select p_out;
-> end
-> //
mysql> delimiter ;
mysql> set @p_out=1;
mysql> call out_param(@p_out);
+-------+
| p_out |
+-------+
| NULL |
+-------+
#因为out是向调用者输出参数,不接收输入的参数,所以存储过程里的p_out为null
+-------+
| p_out |
+-------+
| 2 |
+-------+
mysql> select @p_out;
+--------+
| @p_out |
+--------+
| 2 |
+--------+
#调用了out_param存储过程,输出参数,改变了p_out变量的值
INOUT输入输出参数:
mysql> delimiter $$
mysql> create procedure inout_param(inout p_inout int)
-> begin
-> select p_inout;
-> set p_inout=2;
-> select p_inout;
-> end
-> $$
mysql> delimiter ;
mysql> set @p_inout=1;
mysql> call inout_param(@p_inout);
+---------+
| p_inout |
+---------+
| 1 |
+---------+
+---------+
| p_inout |
+---------+
| 2 |
+---------+
mysql> select @p_inout;
+----------+
| @p_inout |
+----------+
| 2 |
+----------+
#调用了inout_param存储过程,接受了输入的参数,也输出参数,改变了变量
注:
1、没有参数时,调用时也要加() 即: call sp_name()
2、确保参数的名字不等于列的名字,否则在过程体中,参数名被当做列名来处理。
三、查看已创建的存储过程及其内容
查看所有存储过程
show procedure status;
查看存储过程内容
show create procedure 存储过程名字;
四、删除及修改存储过程
删除
drop procedure if exits sp_name
修改
ALTER PROCEDURE sp_name
SQL SECURITY INVOKER;
COMMENT"simon"