在mysql高版本中存储过程非常的受用了,我们今天来了解一下mysql存储过程的一些简单用法也就是基础知识哦,具体如下。
MySQL中,创建存储过程的基本形式如下:
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
其中,sp_name参数是存储过程的名称;proc_parameter表示存储过程的参数列表; characteristic参数指定存储过程的特性;routine_body参数是SQL代码的内容,可以用BEGIN…END来标志SQL代码的开始和结束。
proc_parameter中的每个参数由3部分组成。这3部分分别是输入输出类型、参数名称和参数类型。其形式如下:
[ IN | OUT | INOUT ] param_name type
其中,IN表示输入参数;OUT表示输出参数; INOUT表示既可以是输入,也可以是输出; param_name参数是存储过程的参数名称;type参数指定存储过程的参数类型,该类型可以是MySQL数据库的任意数据类型。
characteristic参数有多个取值。其取值说明如下:
LANGUAGE SQL:说明routine_body部分是由SQL语言的语句组成,这也是数据库系统默认的语言。
[NOT] DETERMINISTIC:指明存储过程的执行结果是否是确定的。DETERMINISTIC表示结果是确定的。每次执行存储过程时,相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是非确定的,相同的输入可能得到不同的输出。默认情况下,结果是非确定的。
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }:指明子程序使用SQL语句的限制。CONTAINS SQL表示子程序包含SQL语句,但不包含读或写数据的语句;NO SQL表示子程序中不包含SQL语句;READS SQL DATA表示子程序中包含读数据的语句;MODIFIES SQL DATA表示子程序中包含写数据的语句。默认情况下,系统会指定为CONTAINS SQL。
SQL SECURITY { DEFINER | INVOKER }:指明谁有权限来执行。DEFINER表示只有定义者自己才能够执行;INVOKER表示调用者可以执行。默认情况下,系统指定的权限是DEFINER。
COMMENT 'string':注释信息。
技巧:创建存储过程时,系统默认指定CONTAINS SQL,表示存储过程中使用了SQL语句。但是,如果存储过程中没有使用SQL语句,最好设置为NO SQL。而且,存储过程中最好在COMMENT部分对存储过程进行简单的注释,以便以后在阅读存储过程的代码时更加方便。
存储过程的好处:
业务逻辑被“隐藏”在数据库里面,从而避免敏感的数据被透露
能简化应用程序代码
跨平台性
如果您必须一次执行几个语句,一个存储过程比客户端程序逐条地执行语句要快的多,因为不需要任何的来往返数据
CREATE
[DEFINER = {user | CURRENT_USER}]
PROCEDURE (PARAMETER(S)…)
[characteristic(s)…] routine_body
The syntax for creating a function is:
CREATE
[DEFINER = {user | CURRENT_USER }]
FUNCTION sp_name ([parameter])
RETURNS type
[characteristic(s)…] routine_body
CREATE 创建
DEFINER 定义者,表示存储过程或者函数的所有者,如果省略,讲使用默认所有者(当前用户)
RPOCEDURE 表示创建一个名字为name的存储过程
FUNCTION 表示创建一个名称为name的函数
【IN | OUT | INOUT】 type
IN 指参数是一个输入变量,它仅向过程提供一个值
OUT 指参数是只是用来存储返回值
INOUT 指参数可用于输入变量和返回值
parameter name(参数名称)是Type的类型名
RETURNS 表示返回数据类型
DETERMINISTIC 确定性
NOT DETERMINISTIC 非确定性
实例一、
CREATE PROCEDURE user_avg(OUT average NUMERIC(5,2))
BEGIN
SELECT AVG(age) INTO average FROM users
END;
执行该存储过程:
CALL user_avg(@a);
SELECT @a;
实例二、
数据库存在的admin1的时候返回1
数据库不存在的admin11的时候返回0
例子
将下面的语句复制粘贴可以一次性执行完,我已经测试过,没有问题!
MySql存储过程简单实例:
/********************* 创建表 *****************************/
delimiter //
DROP TABLE if exists test //
CREATE TABLE test(
id int(11) NULL
) //
/********************** 最简单的一个存储过程 **********************/
drop procedure if exists sp//
CREATE PROCEDURE sp() select 1 //
call sp()//
/********************* 带输入参数的存储过程 *******************/
drop procedure if exists sp1 //
create procedure sp1(in p int)
comment 'insert into a int value'
begin
/* 定义一个整形变量 */
declare v1 int;
/* 将输入参数的值赋给变量 */
set v1 = p;
/* 执行插入操作 */
insert into test(id) values(v1);
end
//
/* 调用这个存储过程 */
call sp1(1)//
/* 去数据库查看调用之后的结果 */
select * from test//
/****************** 带输出参数的存储过程 ************************/
drop procedure if exists sp2 //
create procedure sp2(out p int)
/*这里的DETERMINISTIC子句表示输入和输出的值都是确定的,不会再改变.我一同事说目前mysql并没有实现该功能,因此加不加都是NOT DETERMINISTIC的*/
DETERMINISTIC
begin
select max(id) into p from test;
end
//
/* 调用该存储过程,注意:输出参数必须是一个带@符号的变量 */
call sp2(@pv)//
/* 查询刚刚在存储过程中使用到的变量 */
select @pv//
/******************** 带输入和输出参数的存储过程 ***********************/
drop procedure if exists sp3 //
create procedure sp3(in p1 int , out p2 int)
begin
if p1 = 1 then
/* 用@符号加变量名的方式定义一个变量,与declare类似 */
set @v = 10;
else
set @v = 20;
end if;
/* 语句体内可以执行多条sql,但必须以分号分隔 */
insert into test(id) values(@v);
select max(id) into p2 from test;
end
//
/* 调用该存储过程,注意:输入参数是一个值,而输出参数则必须是一个带@符号的变量 */
call sp3(1,@ret)//
select @ret//
/***************** 既做输入又做输出参数的存储过程 ***************************************/
drop procedure if exists sp4 //
create procedure sp4(inout p4 int)
begin
if p4 = 4 then
set @pg = 400;
else
set @pg = 500;
end if;
select @pg;
end//
call sp4(@pp)//
/* 这里需要先设置一个已赋值的变量,然后再作为参数传入 */
set @pp = 4//
call sp4(@pp)//
/********************************************************/