第七章 存储过程

1 存储过程简介

        存储过程是存储在数据库服务器中的一组SQL语句，通过在查询中调用一个指定的名来执行，很像封装了一组命令的函数，调用此函数名时就会执行这些命令

 

2 存储过程的优点

(1).增强SQL语言的功能和灵活性：存储过程可以用控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

(2).标准组件式编程：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。

(3).较快的执行速度：如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。

(4).减少网络流量：针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并降低了网络负载。

(5).作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

 

3  创建存储过程语法

语法
CREATE PROCEDURE  过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] 过程体
DELIMITER //
  CREATE PROCEDURE myproc(OUT s int)
    BEGIN
      SELECT COUNT(*) INTO s FROM students;
    END
    //
DELIMITER ;

分隔符
MySQL默认以";"为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错，所以要事先用“DELIMITER //”声明当前段分隔符，让编译器把两个"//"之间的内容当做存储过程的代码，不会执行这些代码；“DELIMITER ;”的意为把分隔符还原。

参数
存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用","分割开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT:
IN参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
OUT:该值可在存储过程内部被改变，并可返回
INOUT:调用时指定，并且可被改变和返回

过程体
过程体的开始与结束使用BEGIN与END进行标识。 

4 基本操作

-- 存储过程 

/**
创建存储过程，并不代表执行存储过程
*/

delimiter //
create
   
    procedure test()
    begin
	insert into a values (null,'11');
	insert into a values (null,'33');
	insert into a values (null,'99');
	insert into a values (null,'11');
    end//
    
delimiter ;

-- 执行调用存储过程
call test();


-- 有输入参数的存储过程
/*
test2(参数。。)   in或者out,inout  参数名字 参数的类型
添加多个参数用,隔开  参数类型可以一样，但是名字要不一样。
*/
delimiter //
create
   
    procedure test2(in number2 varchar(22),number1 varchar(22))
    begin
	insert into a values (null,number1);
    end//
    
delimiter ;

call test2('张三','李四');


-- 有输出参数的存储过程
delimiter //
create
   
    procedure test3(out number int)
    begin
	select count(*)into number from a;
    end//
    
delimiter ;

-- 调用该存储过程    输出参数number的值，保存在aa中
call test3(@aa);

-- 查看aa的值
select @aa;



-- 有输出和输入参数的存储过程
delimiter //
create
   
    procedure test4(message varchar(22),out number int)
    begin
	select count(*)into number from a;
	insert into a values(null,message);
    end//
    
delimiter ;

-- 调用存储过程
call test4('admin',@number);

select @number;



/*****************控制流************/
-- 变量
/*
变量 : 会变的程度
变：变化
量：程度

1 定义
2 赋值

*/

-- if
/**
number 1 在a 表中a_name 就为张三
number 2 在a 表中a_name 就为李四
number 3 在a 表中a_name 就为王武
nubmer为其他数字，就添加aa
*/
delimiter //
create
   
    procedure test5(number int )
    begin
	if number=1   then 
	    insert into a values (null,'张三');
	elseif number=2   then 
	    insert into a values (null,'李四');
	elseif number=3   then 
	    insert into a values (null,'王武');   
	else  
	    insert into a values (null,'aa');	
	end if;	
    end//
delimiter ;

-- 调用存储过程
call test5(44);


--  变量  1 declare  message  varchar(22); 
delimiter //
create
    procedure test6(number int )
    begin
	
	/*声明变量   保存值
	*/
	declare  message  varchar(22); 
	if number=1  then 
		/**赋值*/
	    set message ='张三';
	elseif number=2   then 
	    set message ='李四';
	elseif number=3   then 
	     set message ='王武';
	else  
	   set message ='aa';	
	end if;	
	insert into a values (null,message);	
    end//
delimiter ;


call test6(3);



--  变量  2set @message ='张三';
delimiter //
create
    procedure test7(number int )
    begin

	if number=1  then 
		/**赋值*/
	    set @message ='张三';
	elseif number=2 then 
	    set @message ='李四';
	elseif number=3 then 
	     set @message ='王武';
	else  
	   set @message ='aa';	
	end if;	
	insert into a values (44,@message);	
    end//
delimiter ;

-- 调用
call test7(1);



-- case语句  
delimiter //
create
    procedure test8(number int )
    begin
	case  
	when number =1 then  
	    set @message ='张三';
	    
	when  2 then  
	    set @message ='李四';
	when  3 then  
	    set @message ='王武';
	else  
	    set @message ='aa';
	end case;
	
	insert into a values (44,@message);	
    end//
delimiter ;

call test8(33);


--  循环  LOOP_LABLE
delimiter //
create
    procedure test9( )
    begin
      declare v int default 1;	
     LOOP_LABLE:loop 
      set v=v+1;
      /*打印*/
      select concat('v=',v);
      if v >10 then 
      leave LOOP_LABLE;  
      end if;  
     end loop;		
    end//
delimiter ;

call test9();