第七章存储过程

你的名字 2022-01-26 06:07 233阅读 0赞

## **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();