mysql存储过程

mysql 存储过程

SQL语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

  • 存储过程的优点：

    • 增强SQL语言的功能和灵活性
    • 标准组件式编程
    • 较快的执行速度
    • 减少网络流量
    • 作为一种安全机制来充分利用

创建

  • 创建没有参数的存储过程

    //mysql默认以";"为分隔符，如果没有声明分割符，
//编译器会把存储过程当成sql语句进行处理，编译过程就会出错
//事先用DELIMITER来声明当前段分隔符，让编译器把两个“$$”之间的内容当作存储过程，
//不会执行这些代码
DELIMITER $$
    CREATE PROCEDURE test()
        BEGIN
            //过程体，以begin开始end结束
        END $$
//存储过程执行完毕，需要将分隔符还原
DELIMITER ;

  • 创建带有参数的存储过程

    • 存储过程根据需要可能会有输入、输入和输出参数，如果有多个参数用”,”分开

      • IN参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能返回，为默认值
      • OUT 该值可在存储过程内部改变，可返回

      • INOUT 调用时指定，并可被改变和返回

           DELIMITER $$
	CREATE PROCEDURE test(IN a INT, IN b INT, OUT c INT)
		 BEGIN
			SET c = a +b ;
		END$$
DELIMITER ;

  • 变量（这些变量会在end之后失效）

    • 全局变量

      在过程体中定义变量， 形式：

      DECLARE 变量名 数据类型 [默认值];

      DECLARE a INT DEFAULT 0;

      数据类型为MySQL的数据类型

    • 临时变量（不能滥用，否则会导致程序难以理解和管理）

      一般以@开头

    • 赋值

      语法：SET 变量名 = 变量值(全局变量赋值)

       SET @变量名（临时变量）

 SET @execSql = "select ....";

使用

  • 执行

    在sql操作中使用 CALL + 存储过程名

    CALL test(1, 2, @a);
//进行输出
SELECT @a;

  • 查看

    //查看存储过程详细信息
SHOW CREATE PROCEDURE 数据库.存储过程名;

  • 存储过程删除

    //MySQL存储过程的删除
DROP PROCEDURE [过程1[,过程2…]]

MySQL存储过程的控制语句

  • 条件语句

    • IF-THEN-ELSE 语句

      DELIMITER $$
CREATE PROCEDURE proc3(IN parameter int)
  BEGIN
    DECLARE var int;
    SET var=parameter+1;
    IF var=0 THEN
      INSERT INTO t VALUES (17);
    END IF ;
    IF parameter=0 THEN
      UPDATE t SET s1=s1+1;
    ELSE
      UPDATE t SET s1=s1+2;
    END IF ;
  END $$
DELIMITER ;

    • CASE-WHEN-THEN-ELSE语句

      DELIMITER $$
 CREATE PROCEDURE proc4 (IN parameter INT)
    BEGIN
      DECLARE var INT;
      SET var=parameter+1;
      CASE var
        WHEN 0 THEN
          INSERT INTO t VALUES (17);
        WHEN 1 THEN
          INSERT INTO t VALUES (18);
        ELSE
          INSERT INTO t VALUES (19);
      END CASE ;
    END $$
DELIMITER ;

  • 循环语句

    • WHILE-DO…END-WHILE

      DELIMITER $$
 CREATE PROCEDURE proc5()
    BEGIN
      DECLARE var INT;
      SET var=0;
      WHILE var<6 DO
        INSERT INTO t VALUES (var);
        SET var=var+1;
      END WHILE ;
    END $$
DELIMITER ;

    • REPEAT…END REPEAT

      此语句的特点是执行操作后检查结果

      DELIMITER $$
 CREATE PROCEDURE proc6 ()
   BEGIN
     DECLARE v INT;
     SET v=0;
     REPEAT
       INSERT INTO t VALUES(v);
       SET v=v+1;
       UNTIL v>=5
     END REPEAT;
   END$$
DELIMITER ;

    • LOOP…END LOOP

      DELIMITER $$
CREATE PROCEDURE proc7 ()
   BEGIN
     DECLARE v INT;
     SET v=0;
     LOOP_LABLE:LOOP
       INSERT INTO t VALUES(v);
       SET v=v+1;
       IF v >=5 THEN
         LEAVE LOOP_LABLE;
       END IF;
     END LOOP;
   END $$
DELIMITER ;

      LABLES标号
      标号可以用在begin repeat while 或者loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。

    • ITERATE迭代

      通过引用复合语句的标号,来从新开始复合语句

    • ITERATE

      DELIMITER $$
CREATE PROCEDURE proc8()
  BEGIN
    DECLARE v INT;
    SET v=0;
    LOOP_LABLE:LOOP
      IF v=3 THEN
        SET v=v+1;
        ITERATE LOOP_LABLE;
      END IF;
      INSERT INTO t VALUES(v);
      SET v=v+1;
      IF v>=5 THEN
        LEAVE LOOP_LABLE;
      END IF;
    END LOOP;
  END $$
DELIMITER ;

MySql拼接字符串

  • 字符串函数

    CHARSET(str) //返回字串字符集

CONCAT (string2 [,... ]) //连接字串

INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0
LCASE (string2 ) //转换成小写

LEFT (string2 ,length ) //从string2中的左边起取length个字符

LENGTH (string ) //string长度

LOAD_FILE (file_name ) //从文件读取内容

LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定开始位置

LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length

LTRIM (string2 ) //去除前端空格

REPEAT (string2 ,count ) //重复count次

REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str

RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length

RTRIM (string2 ) //去除后端空格

STRCMP (string1 ,string2 ) //逐字符比较两字串大小,

SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符,

注： mysql中处理字符串时，默认第一个字符下标为1，即参数position**必须大于等于1**

  • 拼接sql

    拼接字符串，利用相应的函数

    DELIMITER $$
	CREATE PROCEDURE clearbindinfo(IN erp_no VARCHAR(255), IN out_pc INT)
	  	BEGIN
	 	 DECLARE codes VARCHAR(255);
	//这是一个sql查询的语句，利用行转列，并拼接字符
	  SET codes = (SELECT GROUP_CONCAT(' a.code',type_id,'= NULL ') FROM 	orderinfo_flow_info_dtl a 
	JOIN orderinfo_flow_info c ON a.order_flow_id=c.order_flow_id 
	JOIN bas_flow_model_dtl b ON a.model_id=b.model_id AND b.model_dtl_id=a.model_dtl_id 
	JOIN orderinfo_barcoderule_config d ON c.connect_id=d.order_id AND a.barcode_type_id=d.type_id
	JOIN orderinfo_production o ON c.connect_id = o.id 
	WHERE b.flag='BD' AND o.erpNo = erp_no );
	//判断变量是否为空
	  IF(codes IS NOT NULL) 
	   THEN 
	//为临时变量赋值
	SET @_erp_no = erp_no;
	SET @_out_pc = out_pc;
	//拼接sql
	SET @execSql = " UPDATE barcode_binding_info a 
		JOIN  orderinfo_production b ON a.`connect_id` = b.`id`
		JOIN stockin_main c  ON c.`connect_id` = b.`id`
		JOIN stockin_dtl d ON c.stockin_id=d.stockin_id  SET ";
	SET @execSql = CONCAT(@execSql, codes, " WHERE b.ErpNo= ? AND c.out_pc= ?" );
	//采用preparestatment形式，执行sql
	//声明smt
	PREPARE smt FROM @execSql;
	//执行smt,并将sql中的问号替代
	EXECUTE smt USING @_erp_no, @_out_pc;
	//取消smt的声明 
	DEALLOCATE PREPARE smt;
	END IF;
	  END$$
DELIMITER ;

MySql存储过程事务

存储过程也是支持事务的，使用如下：

DELIMITER $$
CREATE  PROCEDURE clear_data(IN erpNo VARCHAR(255), IN out_pc INT)
    BEGIN
        //定义一个错误码
        DECLARE error INTEGER DEFAULT 0;
        //声明当存储过程执行出现错误时会给错误码赋值
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error = 1;

        /* 开启事务*/
        START TRANSACTION;

            //执行相应的存储过程
            ...
        //判断错误码的值，然后决定是提交还是回滚
        IF error = 1
        THEN 
            ROLLBACK;
        ELSE 
            COMMIT;
        END IF;
        //输出错误码
        SELECT error;
    END$$
DELIMITER ;

本博客：http://blog.csdn.net/xiaowu_zhu/article/details/70139155

posted @ 2017-04-12 09:46  jxiaow  阅读(41)  评论(0)    收藏  举报