阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MySQL存储过程详细讲解及完整实例下载

184次阅读
没有评论

共计 5155 个字符,预计需要花费 13 分钟才能阅读完成。

一、存储过程概念
1. 存储过程(Stored Procedure)是一组为了完成特定功能的 SQL 语句集。经编译后存储在数据库中。
2. 存储过程是数据库中的一个重要对象,用户通过指定存储过程的名字并给出参数(如果该存储过 程带有参数)来执行它。
3. 存储过程是由流控制和 SQL 语句书写的过程,这个过程经编译和优化后存储在数据库服务器中。
4. 存储过程可由应用程序通过一个调用来执行,而且允许用户声明变量。
5. 同时,存储过程可以接收和输出参数、返回执行存储过程的状态值,也可以嵌套调用。
二、存储过程优点
1. 增强了 SQL 语句的功能和灵活性
2 不需要反复建立一系列处理步骤,保证了数据的完整性
3. 降低了网络的通信量,客户端调用存储过程只需要传存储过程名和相关参数即可,与传输 SQL 语 句相比自然数据量少了很多
4. 增强了使用的安全性,通过存储过程可以使没有权限的用户在控制之下间接地存取数据库,从而 保证数据的安全。
5. 可以实现集中控制,当规则发生改变时,只需要修改存储过程就可以。。、
三、存储过程缺点
1. 调试不是很方便。
2. 可能没有创建存储过程的权利。
3. 重新编译问题。
4. 移植性问题。

四、变量

1. 用户变量:以”@”开始,形式为”@变量名。”用户变量跟 MySQL 客户端是绑定的,设置的变量,只对当前用户使用的客户端生效.
2. 全局变量:定义时,以如下两种形式出现,set GLOBAL 变量名 或者 set @@global. 变量名。show global variables; 对所有客户端生效。只有 super 权限才可以设置全局变量。
3. 会话变量:只对连接的客户端有效。一旦客户端失去连接,变量失效。show session variables;
4. 局部变量:作用范围在 begin 到 end 语句块之间。

4.1 在该语句块里设置的变量 declare 语句专门用于定义局部变量。declare numeric number(8,2)【MySQL 的数据类型,如:int,float, date, varchar(length)】default 9.95;

4.2 变量赋值:SET 变量名 = 表达式值 [,variable_name= expression …],set numeric=1.2 或者 SELECT 2.3 into @x;

五、mysql 存储程序

1. 基本语法:create procedure  过程名 ([过程参数[,…]])[特性 …] 过程体; 先看基本例子

第一种:

delimiter ;;
create procedure proc_on_insert()
begin
end
;;
delimiter

第二种:

delimiter //
create procedure proc_on_insert()
begin
end
//
delimiter ;;

注意:

1). 这里需要注意的是 delimiter // 和 delimiter ;; 两句,delimiter 是分割符的意思,因为 MySQL 默认以 ”;” 为分隔符,如果我们没有声明分割符,那么编译器会把存储过程当成 SQL 语句进行处理,则存储过程的编译过程会报错,所以要事先用 delimiter 关键字申明当前段分隔符,这样 MySQL 才会将 ”;” 当做存储过程中的代码。
2). 存储过程根据需要可能会有输入、输出、输入输出参数,这里有一个输出参数 s,类型是 int 型,如果有多个参数用 ”,” 分割开。
3). 过程体的开始与结束使用 begin 与 emd 进行标识。

2.. 调用存储过程基本语法:call sp_name()

3. 参数:MySQL 存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT, 形式如:

create procedure([[in |out |inout] 参数名 数据类形 …])
in 输入参数: 表示该参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值
out 输出参数: 该值可在存储过程内部被改变,并可返回
inout 输入输出参数: 调用时指定,并且可被改变和返回

3.1in 参数例子:

drop procedure if exists prc_on_in;
delimiter ;;
create procedure prc_on_in(in num int)
begin
declare number int ;
set number=num;
select number;
end
;;
delimiter ;;
set @num=1;
call prc_on_in(@num);

3.2out 参数创建例子

drop procedure if exists prc_on_out;
delimiter ;;
create procedure prc_on_out(out out_num int)
begin
select out_num;
set out_num=78;
select out_num;
end
;;
delimiter ;;
set @number=6;
call prc_on_out(@number);

3.3inout 参数创建例子

drop procedure if exists prc_on_inout;
delimiter ;;
create procedure prc_on_inout(inout p_inout int)
begin
select p_inout;
set p_inout=100;
select p_inout;
end
;;
delimiter ;;
set @p_out=90;
call prc_on_inout(@p_out);

3.4 存储过程中的 IF 语句(if then elseif then else end if)

drop procedure if exists p_else;
create procedure p_else(in id int)
begin
    if (id > 0) then
        select '> 0' as id;
    elseif (id = 0) then
        select '= 0' as id;
    else
        select '< 0' as id;
    end if;
end;
set @p=-10;
call p_else(@p);

3.5 存储过程中的 case when then

drop procedure if exists p_case;
delimiter ;;
create procedure p_case(id int  
)  
begin  
    case id  
    when 1 then     
    select 'one' as trans;  
    when 2 then  
    select 'two' as trans;  
    when 3 then   
    select 'three' as trans;  
    else  
    select 'no trans' as trans;  
    end case;  
end;  
;;
delimiter ;;
set @id=1;
call p_case(@id);

3.6 存储过程中的 while do … end while 语句

drop procedure if exists p_while_do;  
create procedure p_while_do()  
begin  
    declare i int;  
        set i = 1;  
        while i <= 10 do  
            select concat('index : ', i) ;  
            set i = i + 1;  
        end while;  
end;  
call p_while_do(); 

3.7 存储过程中的 repeat … until end repeat 语句

drop procedure if exists p_repeat;
delimiter ;;
create procedure p_repeat(in parameter int)
BEGIN
     declare var int;  
     set var = parameter; 
     REPEAT
     set var = var - 1; 
     set parameter = parameter -2; 
     UNTIL var<0
     end REPEAT;
     select parameter;
END
;;
delimiter ;; 
set @parameter=1;
call p_repeat(@parameter);

这个 REPEAT 循环的功能和前面 WHILE 循环一样,区别在于它的执行后检查是否满足循环条件(until i>=5),而 WHILE 则是执行前检查(while i<5 do)。
不过要注意 until i>= 5 后面不要加分号,如果加分号,就是提示语法错误。

3.8 存储过程中的 loop ··· end loop 语句

drop procedure if exists p_loop;
delimiter;;
create procedure p_loop(in parameter int)
BEGIN
     declare var int;  
     set var = parameter; 
     LOOP_LABLE:loop
     set var = var - 1; 
     set parameter = parameter -2; 
     if var<0 THEN
   LEAVE LOOP_LABLE;
     END IF;
     end LOOP;
     select parameter;
END
;;
delimiter;;
set @parameter=4;
call p_loop(@parameter);

使用 LOOP 编写同样的循环控制语句要比使用 while 和 repeat 编写的要复杂一些:在循环内部加入了 IF……END IF 语句,在 IF 语句中又加入了 LEAVE 语句,LEAVE 语句的意思是离开循环,LEAVE 的格式是:LEAVE 循环标号。

4. 游标的使用 : 定义游标,打开游标 , 使用游标 , 关闭游标例子

drop table if exists  person;
CREATE TABLE `person` (`id` int(11) NOT NULL DEFAULT '0',
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into person(age) value(1);
drop procedure if exists prc_test1;
delimiter ;;
create definer = root@localhost procedure prc_test1()
BEGIN
    declare var int;   
     /** 跳出循环标识 **/
   declare done INT DEFAULT FALSE;
     /** 声明游标 **/
   declare cur cursor for select age from person;
   /** 循环结束设置跳出标识 **/
   declare continue handler for not FOUND set done = true;
   /** 打开游标 **/
   open cur;
     LOOP_LABLE:loop
        FETCH cur INTO var;
        select var;
     if done THEN
   LEAVE LOOP_LABLE;
     END IF;
     end LOOP;
     /** 关闭游标 **/
   CLOSE cur;
END;
;;
delimiter ;;
call prc_test1();

5.MySQL 存储过程的查询

5.1. 查看某个数据库下面的存储过程

select name from mysql.proc where db=’数据库名’;
或者
select routine_name frominformation_schema.routines where routine_schema=’ 数据库名 ’;
或者
show procedure status where db=’ 数据库名 ’;

5.2. 查看存储过程的详细

show create procedure 数据库. 存储过程名;

6、MySQL 存储过程的修改

ALTER PROCEDURE: 更改用 CREATE PROCEDURE 建立的预先指定的存储过程,其不会影响相关存储过程或存储功能。

7. 删除存储过程
drop procedure sp_name // 注释函数名

mysql 存储函数实例可以到 Linux 公社资源站下载:

—————————————— 分割线 ——————————————

免费下载地址在 http://linux.linuxidc.com/

用户名与密码都是www.linuxidc.com

具体下载目录在 /2017 年资料 / 8 月 /15 日 /MySQL 存储过程详细讲解及完整实例下载 /

下载方法见 http://www.linuxidc.com/Linux/2013-07/87684.htm

—————————————— 分割线 ——————————————

本文永久更新链接地址:http://www.linuxidc.com/Linux/2017-08/146249.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计5155字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中