MYSQL存储过程怎么写

MySQL存储过程

 

DELIMITER $$     
DROP   PROCEDURE   IF   EXISTS  HelloWorld$$   
CREATE   PROCEDURE  HelloWorld()   
BEGIN    
      SELECT  "Hello World!";   
END $$   
DELIMITER ;    

 

　　3，变量

　　DECLARE声明，SET赋值

可以在DECLARE变量时用DEFAULT设置默认值，不设则为NULL 

 

 

DECLARE  counter  INT   DEFAULT   0 ;   
SET  counter  =  counter + 1 ;   

 

 　 4，参数

IN为默认类型，值必须在调用时指定，值不能返回（值传递）

OUT值可以返回（指针传递）

INOUT值必须在调用时指定，值可以返回

 

CREATE   PROCEDURE  test(a  INT , OUT b  FLOAT , INOUT c  INT )   
CREATE   PROCEDURE  test(a  INT , OUT b  FLOAT , INOUT c  INT )

 

 　  5，条件判断

IF THEN、ELSEIF、ELSE、END IF

 

 代码

DELIMITER $$   
   
DROP   PROCEDURE   IF   EXISTS  discounted_price$$   
CREATE   PROCEDURE  discunted_price(normal_price NUMERIC( 8 ,  2 ), OUT discount_price NUMERIC( 8 ,  2 ))   
BEGIN    
      IF  (normal_price  >   500 )  THEN    
          SET  discount_price  =  normal_price  *  . 8 ;   
     ELSEIF (normal_price  >   100 )  THEN    
          SET  discount_price  =  normal_price  *  . 9 ;   
      ELSE    
          SET  discount_price  =  normal_price;   
      END   IF ;   
END $$   
   
DELIMITER ;   

6，循环

LOOP、END LOOP 

 

代码

DELIMITER $$    
DROP   PROCEDURE   IF   EXISTS  simple_loop$$    
CREATE   PROCEDURE  simple_loop(OUT counter  INT )   
BEGIN    
      SET  counter  =   0 ;   
     my_simple_loop:  LOOP    
          SET  counter  =  counter + 1 ;   
          IF  counter  =   10   THEN    
              LEAVE  my_simple_loop;   
          END   IF ;   
      END   LOOP  my_simple_loop;   
END $$     
DELIMITER ;   

 

WHILE DO、END WHILE

 

代码

DELIMITER $$   
   
DROP   PROCEDURE   IF   EXISTS  simple_while$$   
CREATE   PROCEDURE  simple_while(OUT counter  INT )   
BEGIN    
      SET  counter  =   0 ;   
      WHILE  counter  !=   10  DO   
          SET  counter  =  counter + 1 ;   
      END   WHILE ;   
END $$   
   
DELIMITER ;   

 

REPEAT、UNTILL

 

代码

DELIMITER $$   
   
DROP   PROCEDURE   IF   EXISTS  simple_repeat$$     
CREATE   PROCEDURE  simple_repeat(OUT counter  INT )   
BEGIN    
      SET  counter  =   0 ;   
     REPEAT   
          SET  counter  =  counter + 1 ;   
     UNTIL counter  =   10   END  REPEAT;   
END $$   
   
DELIMITER ;  

 

      7，异常处理

如果用cursor获取SELECT语句返回的所有结果集时应该定义NOT FOUND error handler来防止存储程序提前终结

如果SQL语句可能返回constraint violation等错误时应该创建一个handler来防止程序终结

8，数据库交互

INTO用于存储单行记录的查询结果

 

DECLARE  total_sales NUMERIC( 8 ,  2 );   
SELECT   SUM (sale_value)  INTO  total_sales  FROM  sales  WHERE  customer_id = in_customer_id;  

 

 　　CURSOR用于处理多行记录的查询结果

 

代码

DELIMITER $$  
  
DROP   PROCEDURE   IF  EXITS cursor_example$$  
CREATE   PROCEDURE  cursor_example()  
     READS SQL DATA  
BEGIN   
      DECLARE  l_employee_id  INT ;  
      DECLARE  l_salary NUMERIC( 8 , 2 );  
      DECLARE  l_department_id  INT ;  
      DECLARE  done  INT   DEFAULT   0 ;  
      DECLARE  cur1  CURSOR   FOR   SELECT  employee_id, salary, department_id  FROM  employees;  
      DECLARE   CONTINUE  HANDLER  FOR   NOT  FOUND  SET  done = 1 ;  
  
      OPEN  cur1;  
     emp_loop: LOOP  
          FETCH  cur1  INTO  l_employee_id, l_salary, l_department_id;  
          IF  done = 1   THEN   
             LEAVE emp_loop;  
          END   IF ;  
      END  LOOP emp_loop;  
      CLOSE  cur1;  
END $$  
DELIMITER ;  

 

unbounded SELECT语句用于存储过程返回结果集

 

代码

DELIMITER $$  

DROP   PROCEDURE   IF   EXISTS  sp_emps_in_dept$$  
CREATE   PROCEDURE  sp_emps_in_dept(in_employee_id  INT )  
BEGIN   
      SELECT  employee_id, surname, firstname, address1, address2, zipcode, date_of_birth  FROM  employees  WHERE  department_id = in_employee_id;  
END $$  
  
DELIMITER ;   

 

UPDATE、INSERT、DELETE、CREATE TABLE等非查询语句也可以嵌入存储过程里

 

代码

DELIMITER $$   
   
DROP   PROCEDURE   IF  EXITS sp_update_salary$$   
CREATE   PROCEDURE  sp_update_salary(in_employee_id  INT , in_new_salary NUMERIC( 8 , 2 ))   
BEGIN    
      IF  in_new_salary  <   5000   OR  in_new_salary  >   500000   THEN    
          SELECT  "Illegal salary: salary must be  between  $ 5000   and  $ 500 ,  000 ";   
      ELSE    
          UPDATE  employees  SET  salary = in_new_salary  WHERE  employee_id = in_employee_id;   
      END   IF ;  
END $$   
   
DELIMITER ;   

 

 　　9，使用CALL调用存储程序

 

代码

DELIMITER $$   
   
DROP   PROCEDURE   IF   EXISTS  call_example$$   
CREATE   PROCEDURE  call_example(employee_id  INT , employee_type  VARCHAR ( 20 ))   
     NO SQL   
BEGIN    
      DECLARE  l_bonus_amount NUMERIC( 8 , 2 );   
   
      IF  employee_type = ' MANAGER '   THEN    
         CALL calc_manager_bonus(employee_id, l_bonus_amount);   
      ELSE    
         CALL calc_minion_bonus(employee_id, l_bonus_amount);   
      END   IF ;   
     CALL grant_bonus(employee_id, l_bonus_amount);   
END $$   

DELIMITER ; 

 

 　　10，一个复杂的例子

 

代码

CREATE   PROCEDURE  putting_it_all_together(in_department_id  INT )   
     MODIFIES SQL DATA   
BEGIN    
      DECLARE  l_employee_id  INT ;   
      DECLARE  l_salary NUMERIC( 8 , 2 );   
      DECLARE  l_department_id  INT ;   
      DECLARE  l_new_salary NUMERIC( 8 , 2 );   
      DECLARE  done  INT   DEFAULT   0 ;   
   
      DECLARE  cur1  CURSOR   FOR    
          SELECT  employee_id, salary, department_id   
          FROM  employees   
          WHERE  department_id = in_department_id;   
   
      DECLARE   CONTINUE  HANDLER  FOR   NOT  FOUND  SET  done = 1 ;   
   
      CREATE   TEMPORARY   TABLE   IF   NOT   EXISTS  emp_raises   
         (employee_id  INT , department_id  INT , new_salary NUMERIC( 8 , 2 ));   
   
      OPEN  cur1;   
     emp_loop: LOOP   
          FETCH  cur1  INTO  l_employee_id, l_salary, l_department_id;   
          IF  done = 1   THEN      /*  No more rows  */    
             LEAVE emp_loop;   
          END   IF ;   
         CALL new_salary(1_employee_id, l_new_salary);  /*  Get new salary  */    
          IF  (l_new_salary  <>  l_salary)  THEN    /*  Salary changed  */    
              UPDATE  employees   
                  SET  salary = l_new_salary   
              WHERE  employee_id = l_employee_id;   
             /*  Keep track of changed salaries  */    
              INSERT   INTO  emp_raises(employee_id, department_id, new_salary)   
                  VALUES  (l_employee_id, l_department_id, l_new_salary);   
          END   IF :   
      END  LOOP emp_loop;   
      CLOSE  cur1;   
     /*  Print out the changed salaries  */    
      SELECT  employee_id, department_id, new_salary  from  emp_raises   
          ORDER   BY  employee_id;   
END ;  

 

  　 11，存储方法

存储方法与存储过程的区别

1，存储方法的参数列表只允许IN类型的参数，而且没必要也不允许指定IN关键字

2，存储方法返回一个单一的值，值的类型在存储方法的头部定义

3，存储方法可以在SQL语句内部调用

4，存储方法不能返回结果集

语法：

 

代码

CREATE    
      [ DEFINER = { user | CURRENT_USER } ]    
      PROCEDURE  sp_name ( [ proc_parameter[,... ] ])   
      [ characteristic ... ]  routine_body   
   
CREATE    
      [ DEFINER = { user | CURRENT_USER } ]    
      FUNCTION  sp_name ( [ func_parameter[,... ] ])   
      RETURNS  type   
      [ characteristic ... ]  routine_body   
       
proc_parameter:   
      [  IN | OUT | INOUT  ]  param_name type   
       
func_parameter:   
     param_name type   
   
type:   
      Any  valid MySQL data type   
   
characteristic:   
     LANGUAGE SQL   
    |   [ NOT ]  DETERMINISTIC   
    |  {  CONTAINS  SQL  |  NO SQL  |  READS SQL DATA  |  MODIFIES SQL DATA }   
    |  SQL SECURITY { DEFINER  |  INVOKER }   
    |  COMMENT  ' string '    
   
routine_body:   
     Valid SQL  procedure  statement   

 

 　  各参数说明见CREATE PROCEDURE and CREATE FUNCTION Syntax

例子：

 

代码

DELIMITER $$   
   
DROP   FUNCTION   IF   EXISTS  f_discount_price$$   
CREATE   FUNCTION  f_discount_price   
     (normal_price NUMERIC( 8 , 2 ))   
      RETURNS  NUMERIC( 8 , 2 )   
     DETERMINISTIC   
BEGIN    
      DECLARE  discount_price NUMERIC( 8 , 2 );   
   
      IF  (normal_price  >   500 )  THEN    
          SET  discount_price  =  normal_price  *  . 8 ;   
     ELSEIF (normal_price  > 100 )  THEN    
          SET  discount_price  =  normal_price  *  . 9 ;   
      ELSE    
          SET  discount_price  =  normal_price;   
      END   IF ;   
   
      RETURN (discount_price);   
END $$   
   
DELIMITER ;   

 

 　  12，触发器

触发器在INSERT、UPDATE或DELETE等DML语句修改数据库表时触发

触发器的典型应用场景是重要的业务逻辑、提高性能、监控表的修改等

触发器可以在DML语句执行前或后触发

 

代码

DELIMITER $$   
   
DROP   TRIGGER  sales_trigger$$   
CREATE   TRIGGER  sales_trigger   
     BEFORE  INSERT   ON  sales   
      FOR  EACH ROW   
BEGIN    
      IF  NEW.sale_value  >   500   THEN    
          SET  NEW.free_shipping  =   ' Y ' ;   
      ELSE    
          SET  NEW.free_shipping  =   ' N ' ;   
      END   IF ;   
   
      IF  NEW.sale_value  >   1000   THEN    
          SET  NEW.discount  =  NEW.sale_value  *  . 15 ;   
      ELSE    
          SET  NEW.discount  =   0 ;   
      END   IF ;   
END $$   
   
DELIMITER ;