转载

Mysql 储存过程以及 python callproc调用

一、存储过程（stored procedure）

存储过程将存入的一系列SQL语句进行预编译，执行并存放在数据库中，之后如果需要使用sql语句对这一组sql进行访问时可以直接提取（很好理解存储过程就是将sql执行过程存储在数据库中，来方便提取）。

优点：1.多次提取，减少编译时间，2.因为每次提取都需要传入sql语句，如果用存储过程名来调用的话，就减少了访问流量3.增加了重用（可以相较之与（函数对编程的影响））缺点：1.存储过程将会占用内存空间，并且复杂的过程操作需要一定的cpu 2.存储过程难以调试，如果存储过程太过复杂，不有利于业务逻辑3.存储过程高级，难以维护

 1 DELIMITER $$   // 设置注释  2 CREATE PROCEDURE CountOrderByStatus(  3  IN orderStatus VARCHAR(25),     //需要给定存入使用的参数  4  OUT total INT)                 //需要给定输出的参数  5 BEGIN  6  SELECT count(orderNumber)  7  INTO total  8  FROM orders  9  WHERE status = orderStatus; 10 END$$ 11 DELIMITER ;

这个存储过程接受两个过程参数一个是输入参数orderStatus，另一个是输出参数 total 最后可以使用Call CountOrderByStatus('23',@title)来进行调用，变量前面要加@.

参数类型

IN 参数名参数类型 :表示该参数需要在创建存储类型时给出，以便在下面的语句中使用

官方样例:

DELIMITER // CREATE PROCEDURE GetOfficeByCountry(IN countryName VARCHAR(255))  BEGIN  SELECT *   FROM offices  WHERE country = countryName;  END // DELIMITER ;


CALL GetOfficeByCountry('USA')//调用

Mysql 储存过程以及 python callproc调用

OUT 参数名参数类型:该参数在执行完所有的参数语句后可以返回.

INOUT 参数名参数类型: 该参数的不同之处在于它传入的参数需要赋值，并且在callprogram之后他的参数值将会被修改返回给变量值.

DELIMITER $$ CREATE PROCEDURE set_counter(INOUT count INT(4),IN inc INT(4))//传入一个Inout参数和一个in参数 BEGIN  SET count = count + inc; END$$ DELIMITER ;

现在尝试调用此储存过程

SET @counter = 1; CALL set_counter(@counter,1); -- 2   CALL set_counter(@counter,1); -- 3  //@count的值已经被修改 CALL set_counter(@counter,5); -- 8 SELECT @counter; -- 8

谓词逻辑：

IF（基本）

IF if_expression THEN commands    [ELSEIF elseif_expression THEN commands]    [ELSE commands]    END IF;

While loop

WHILE expression DO    Statements END WHILE

 REPEAT    Statements;  UNTIL expression    END REPEAT

Case

CASE  case_expression    WHEN when_expression_1 THEN commands    WHEN when_expression_2 THEN commands    ...    ELSE commands END CASE;

注意事项：能用Case用Case，能简化就简化

重点

如何python调用 callproc 进行调用储存过程

1.创建完整的Mysql数据库连接

2.使用cursor()初始化数据库游标

3.使用游标来调用callproc函数里面添加需要传入的变量例如 callproc(name,args) name="proc_user",args=['21',syh];

3.cursor可以传递出一系列的结果集，使用storeresult来获取一系列的iterator指向结果集

4.用fetchall方法获取结果

callproc 无法直接获得out和INOUT变量，但是变量存在server中，可以通过@_procname_n 来获取变量值，可以按照传入参数的位置获取，如第1个 SELECT @_procname_0

from mysql.connector import MySQLConnection, Error from python_mysql_dbconfig import read_db_config def call_find_all_sp():  try:   db_config = read_db_config()   conn = MySQLConnection(**db_config)   cursor = conn.cursor()   cursor.callproc('find_all')   # print out the result   for result in cursor.stored_results():    print(result.fetchall())  except Error as e:   print(e)  finally:   cursor.close()   conn.close() if __name__ == '__main__':  call_find_all_sp()