MySQL存储过程--整理加实例

一.创建存储过程

基本语法：
create procedure demo()
begin

#存储过程体
end

二.调用存储过程

基本语法：当前数据库schema调用：call demo() ， 带schema前缀调用：call test.demo()
注意：存储过程名称后面必须加括 号，哪怕该存储过程没有参数传递

三.删除存储过程

基本语法：drop procedure demo 或者 drop procedure if exists demo
注意：1.不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程；2.存储过程名称后不需要带圆括号

四.区块，条件，循环

1.区块定义，常用
 begin
 #区块体
 end;
 也可以给 区块起别名，如：
 lable:begin
#区块体
 end lable;
 可以用leave lable;跳出区块，执行区块以后的代码

 2.条件语句

if  condition then
 statement
 else
 statement
 end   if ;

3.循环语句
 (1).while循环

[ label: ]   WHILE  expression DO

 statements

 END   WHILE   [ label ]  ;

(2).loop循环

[ label: ]  LOOP

 statements

 END  LOOP  [ label ] ;

(3).repeat until循环

[ label: ]  REPEAT

 statements

 UNTIL expression

 END  REPEAT  [ label ]  ;

五.查看存储过程

1.show procedure status
 显示数据库中所有存储的存储 过程基本信息，包括所属数据库，存储过程名称，创建时间等

2.show create procedure demo 或者show create function your_func_name
 查看存储过程或函数的创建代码

3.select `specific_name` from MySQL.proc where  `db` = 'your_db_name' and `type` = 'procedure'

 直接查询某个具体的存储过程

4.select specific_name from mysql.proc

 查看当前数据库里存储过程列表

5.select body from mysql.proc where specific_name = 'your_proc_name'

 查看某一个存储过程的具体内容

 

六.简单实例

<strong><span style="color:#330033;">##实例用于查询系统中所有注释不为空和‘VIEW’的表格，并将所有这些表格的表名、SELECT全量字段语句及表注释写入一张单独的表GetTableSQL中。</span></strong>

#DELIMITER //  
CREATE PROCEDURE demo()
BEGIN

DECLARE rt VARCHAR (100) CHARACTER SET gbk DEFAULT NULL;
DECLARE done TINYINT (1) DEFAULT 0;
DECLARE ttname VARCHAR (60) CHARACTER SET gbk DEFAULT NULL;
DECLARE tsqltxt VARCHAR (512) CHARACTER SET gbk DEFAULT NULL;
DECLARE tremarks VARCHAR (60) CHARACTER SET gbk DEFAULT NULL;
DECLARE tfield VARCHAR (60) CHARACTER SET gbk DEFAULT NULL;
##创建下面紧邻的SELECT语句相关的游标curtable, SELECT语句查询information_schema系统schema中的TABLES表的所有表名，
##即本数据库中的所有表名（表名注释不为空， 表名注释不为'VIEW'）
DECLARE curtable CURSOR FOR  
SELECT DISTINCT TABLE_name FROM information_schema. TABLES
WHERE
 TRIM(TABLE_COMMENT) <> '' AND TRIM(TABLE_COMMENT) <> 'VIEW' ORDER BY TABLE_name;
##定义当游标下一个指向无数据时，设置变量done的值为1
DECLARE CONTINUE HANDLER FOR NOT found SET done = 1;

SET NAMES 'utf8';
DROP TABLE IF EXISTS GetTableSQL;

CREATE TABLE `gettablesql` (
 `tbname` VARCHAR (60) CHARACTER SET gbk DEFAULT NULL,
 `sqltxt` VARCHAR (4096) CHARACTER SET gbk DEFAULT NULL,
 `tabletitle` VARCHAR (51) CHARACTER SET gbk DEFAULT NULL
) ENGINE = INNODB;

##打开curtable游标，循环游标指向
OPEN curtable;
  ##开始表的循环
 tableloop :
 LOOP

  SET tsqltxt = 'select';

  ##将游标的下一个指向的表名字段赋值给ttname(我们可以认为游标的首指向没有数据), 并将游标移动一位
    ##下面语句等价FETCH next from curtable into ttname
  FETCH curtable INTO ttname;

  ##当游标的下一个指向已无数据，此时结束循环
  IF done = 1 THEN
   LEAVE tableloop;
  END IF;

  -- select ttname;
  BEGIN
  DECLARE done1 TINYINT (1) DEFAULT 0;
  ##创建表的字段游标curfield， 下面的SELECT语句为从系统的COLUNMS表中查询特定表ttname的所有字段和字段注释信息
  DECLARE curfield CURSOR FOR 
   SELECT COLUMN_NAME, COLUMN_COMMENT FROM information_schema. COLUMNS
      WHERE upper(TABLE_name) = upper(ttname) AND (COLUMN_COMMENT <> '');
  DECLARE CONTINUE HANDLER FOR NOT found
  SET done1 = 1;
    ##打开游标
  OPEN curfield;
   ##开始字段的循环
   fieldloop :
   LOOP
    FETCH curfield INTO tfield, tremarks; ##等价FETCH next from curfield into tfield, tremarks;
        ##当游标的下一个指向已无数据，此时结束循环
    IF done1 = 1 THEN
     LEAVE fieldloop;
    END IF;
    ##拼接每个表格的SELECT全量的字段查询语句
    IF tsqltxt = 'select' THEN  ##首次拼接
    SET tsqltxt = CONCAT(tsqltxt,' ',tfield,' ','''',tremarks,'''');
    ELSE
    SET tsqltxt = CONCAT(tsqltxt,',',tfield,' ','''',tremarks,'''');
    END IF;
   ##结束字段的循环
   END LOOP fieldloop;
  ##关闭字段游标
  CLOSE curfield;

  ##此处完成全部的SELECT语句拼接
  SET tsqltxt = concat(tsqltxt, ' from ', ttname);

  INSERT INTO GetTableSQL VALUES(ttname, tsqltxt, '');
  END;

  ##结束表的循环
 END LOOP tableloop;
##关闭表的游标
CLOSE curtable;
##更新表格标题字段为系统表TABLES中的表的注释
UPDATE GetTableSQL AS G
SET tabletitle = (SELECT TABLE_COMMENT FROM information_schema. TABLES s 
WHERE(trim(s.TABLE_COMMENT) <> '') AND g.tbname = s.table_name);

##将从GetTableSQL表中查出的总记录数转换成CHAR字符后赋值给rt变量
SELECT cast(count(*) AS CHAR) INTO rt FROM GetTableSQL;

SET rt = concat('成功更新',rt,'个表的表名注释和字段注释到字典库GetTableSQL中！');
##显示rt变量值
SELECT rt;
END;
#DELIMITER ;

七.其他

1.获取错误信息和错误编号

DECLARE continue HANDLER FOR SQLEXCEPTION
    BEGIN
      GET DIAGNOSTICS CONDITION 1 
--通过GET DIAGNOSTICS这样的方式获取returned_sqlstate message_text(当然错误信息错误编号可以自己定义)
        code = RETURNED_SQLSTATE, msg = MESSAGE_TEXT;
    END;

2.获取上一个SQL语句所影响的行数（Update, delete, insert into)

  ROW_COUNT()

3.日期函数date_add示例

mysql> select date_add('2013-01-18', interval '1 2' YEAR_MONTH);
 +-----------------------------------------------------+
 | date_add('2013-01-18', interval '1 2' YEAR_MONTH) |
 +-----------------------------------------------------+
 | 2014-03-18                                          |
 +-----------------------------------------------------+

4.返回传入的参数中第一个非null的值

  SELECT COALESCE(NULL, NULL, 1); 
  -- Return 1