Mysql 函数和存储过程

常用系统函数

日期和时间函数

1.CURDATE() 返回当前日期;
2.CURTIME() 返回当前时间;
3.MONTH(d) 返回日期 d 中的月份值，范围是 1~12
        
SELECT CURDATE(),CURTIME(),MONTH('2018-09-09') 

字符串函数

1.CHAR_LENGTH(ss) 计算字符串 s 的字符数;
2.UPPER(s) 把所有字母变成大写字母;
3.LOWER(s) 把所有字母变成小写字母;

SELECT CHAR_LENGTH('ssss22'),UPPER('hhvh'),LOWER('HGHi8jbj')

数学函数

1.ABS(X) 求绝对值
2.SQRT(X) 求平方根
3.MOD(X,Y) 求余

SELECT ABS(-1)
SELECT SQRT(4),MOD(9,4) 

加密函数

1.PASSWORD(str) 一般对用户的密码加密 不可逆
2.MD5(str) 普通加密 不可逆
3.ENCODE(str，pswd_str) 加密函数，结果是一个二进制数，必须使用 BLOB 类型的字段来保存它
4.DECODE(crypt_str，pswd_str) 解密函数

SELECT PASSWORD('123456');
SELECT MD5('123456');

SELECT ENCODE('7565','aa');--结果是Oȋl

SELECT DECODE('Oȋl','aa')

存储过程和函数

存储过程和函数是在数据库中定义一些 SQL 语句的集合，然后直接调用这些存储过程和函数来执行已经定义好
的 SQL 语句。存储过程和函数可以避免开发人员重复的编写相同的 SQL 语句。而且，存储过程和函数是在 MySQL
服务器中存储和执行的，可以减少客户端和服务器端的数据传输；

存储过程

CREATE PROCEDURE sp_name([proc_parameter[,...]])
[characteristic...] routine_body
sp_name 参数是存储过程的名称；
proc_parameter 表示存储过程的参数列表；
characteristic 参数指定存储过程的特性；
routine_body 参数是 SQL 代码的内容，可以用 BEGIN...END 来标志 SQL 代码的开始和结束。
proc_parameter 中的每个参数由 3 部分组成。这 3 部分分别是输入输出类型、参数名称和参数类型。
[ IN | OUT | INOUT ] param_name type
其中，IN 表示输入参数；OUT 表示输出参数；INOUT 表示既可以是输入，也可以是输出；param_name 参数是
存储过程的参数名称；type 参数指定存储过程的参数类型，该类型可以是 MySQL 数据库的任意数据类型；
Characteristic 参数有多个取值。其取值说明如下：
LANGUAGE SQL：说明 routine_body 部分是由 SQL 语言的语句组成，这也是数据库系统默认的语言。
[ NOT ] DETERMINISTIC ：指明存储过程的执行结果是否是确定的。DETERMINISTIC 表示结果是确定的。每
次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC 表示结果是非确定的，相同的输入
可能得到不同的输出。默认情况下，结果是非确定的。
{ CONTAINS SQL | NO SQL | READS SQL DATA| MODIFIES SQL DATA} ：指明子程序使用 SQL 语句的限制；
CONTAINS SQL 表示子程序包含 SQL 语句，但不包含读或写数据的语句；NO SQL 表示子程序中不包含 SQL
语句；READS SQL DATA 表示子程序中包含读数据的语句；MODIFIES SQL DATA 表示子程序中包含写数据的
语句。默认情况下，系统会指定为 CONTAINS SQL；
SQL SECURITY { DEFINER | INVOKER }；指明谁有权限来执行。DEFINER 表示只有定义者自己才能够执行；
INVOKER 表示调用者可以执行。默认情况下，系统指定的权限是 DEFINER。
COMMENT ‘string’ ：注释信息；

 利用之前的表创建简单存储过程

DELIMITER &&
CREATE PROCEDURE pro_book ( IN bT INT,OUT count_num INT)
   READS SQL DATA
   BEGIN 
     SELECT COUNT(*) FROM book WHERE bookTypeId=bT;
   END 
   &&
DELIMITER ;

调用

CALL pro_book(1,@count);

创建函数

 

CREATE FUNCTION sp_name ( [func_parameter[,...]] )
RETURNS type
[ characteristic... ] routine_body
sp_name 参数是存储函数的名称；func_parameter 表示存储函数的参数列表；RETURNS type 指定返回值的
类型；characteristic 参数指定存储过程的特性，该参数的取值与存储过程中的取值是一样的；routine_body 参数
是 SQL 代码的内容，可以用 BEGIN...END 来标志 SQL 代码的开始和结束；
func_parameter 可以由多个参数组成，其中每个参数由参数名称和参数类型组成，其形式如下：
param_name type 其中，param_name 参数是存储函数的参数名称；type 参数指定存储函数的参数类型，
该类型可以是 MySQL 数据库的任意数据类型；

  简单函数

DELIMITER &&
CREATE FUNCTION func_book (bookId INT)
 RETURNS VARCHAR(20)
 BEGIN 
  RETURN ( SELECT bookName FROM book WHERE id=bookId );
 END 
    &&
DELIMITER ;

调用

SELECT func_book(2);

  函数+循环

DELIMITER &&
CREATE FUNCTION func_house_datas (startDate DATETIME,endDate  DATETIME)
 RETURNS BIGINT(20)
 BEGIN 
 
DECLARE dataAdd DATETIME ;
DECLARE a BIGINT(20) ;
DECLARE a1  BIGINT(20) ;
SET a=0;
SET dataAdd=startDate;
	  myloop:LOOP
	    IF dataAdd> endDate THEN LEAVE myloop ;
	    ELSE 
		    SELECT COUNT(*) INTO a1 FROM rent_hl_house h WHERE  h.enabled_flag='Y'
		     AND  h.creation_date<dataAdd;
		    SET a=a+a1;
		    SET dataAdd= DATE_ADD(dataAdd,INTERVAL 1 DAY);
	    END IF ;
		END LOOP myloop ;
		RETURN a;
 END 
    &&
DELIMITER ;

SELECT func_house_datas('2018-09-09','2018-09-10');

变量的使用

定义变量

DECLARE var_name [,...] type [ DEFAULT value ]

为变量赋值

SET var_name = expr [,var_name=expr] ...
SELECT col_name[,...] INTO var_name[,...]
FROM table_name WHERE condition

创建book表插入数据的存储过程——set赋值

DELIMITER |
CREATE PROCEDURE create_book1 ()
BEGIN 
DECLARE a,c VARCHAR(20) ;
DECLARE d INT(11) ;
DECLARE b  DECIMAL(6,2) ;
SET a="茶花女",b=1212,c="仲马",d=1;
INSERT INTO bookmain.book
            (id,
             bookName,
             price,
             author,
             bookTypeId)
VALUES (NULL,
        a,
        b,
        c,
        d);
END
|
DELIMITER 

CALL create_book1();

创建book表插入数据的存储过程——into赋值

DELIMITER |
CREATE PROCEDURE create_book2 ()
BEGIN 
DECLARE a,c VARCHAR(20) ;
DECLARE d INT(11) ;
DECLARE b  DECIMAL(6,2) ;
SELECT bookName,price,author,booktype INTO a,b,c,d FROM book WHERE id=897 ;
INSERT INTO bookmain.book
            (id,
             bookName,
             price,
             author,
             bookTypeId)
VALUES (NULL,
        a,
        b,
        c,
        d);
END
|
DELIMITER 

CALL create_book1();

游标的使用

 

   就是一个可读的标识，用来标识数据取到什么地方了。

 

查询语句可能查询出多条记录，在存储过程和函数中使用游标来逐条读取查询结果集中的记录。游标的使
用包括声明游标、打开游标、使用游标和关闭游标。游标必须声明在处理程序之前，并且声明在变量和条
件之后。

声明游标
DECLARE cursor_name CURSOR FOR select_statement ;
打开游标
OPEN cursor_name;
使用游标
FETCH cursor_name INTO var_name [,var_name ... ];
关闭游标
CLOSE cursor_name;

DELIMITER |

CREATE PROCEDURE create_book3 ()
BEGIN 
DECLARE a,c VARCHAR(20) ;
DECLARE d INT(11) ;
DECLARE b  DECIMAL(6,2) ;
DECLARE cur_book CURSOR FOR SELECT bookName,price,author,bookTypeId  FROM book ;
OPEN cur_book;
FETCH cur_book INTO a,b,c,d;
INSERT INTO bookmain.book
            (id,
             bookName,
             price,
             author,
             bookTypeId)
VALUES (NULL,
        a,
        b,
        c,
        d);
CLOSE cur_book;
END
|
DELIMITER 

CALL create_book3();  --调用

 

流程控制的使用

 

存储过程和函数中可以使用流程控制来控制语句的执行。MySQL 中可以使用 IF 语句、CASE 语句、LOOP

语句、LEAVE 语句、ITERATE 语句、REPEAT 语句和 WHILE 语句来进行流程控制.

IF 语句
IF search_condition THEN statement_list
[ ELSEIF search_condition THEN statement_list ]...
[ ELSE statement_list ]
END IF

CASE 语句
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list]...
[ELSE statement_list ]
END CASE


LOOP,LEAVE 语句

LOOP 语句可以使某些特定的语句重复执行，实现一个简单的循环。但是 LOOP 语句本身没有停止循环
的语句，必须是遇到 LEAVE 语句等才能停止循环。LOOP 语句的语法的基本形式如下：
[begin_label：]LOOP
Statement_list
END LOOP [ end_label ]
LEAVE 语句主要用于跳出循环控制。语法形式如下：
LEAVE label

给book表添加数据

DELIMITER |

CREATE PROCEDURE create_book4(IN totalNum INT)
	BEGIN
	  aaa:LOOP
	    
	      SET totalNum=totalNum-1;
	    IF totalNum=0 THEN LEAVE aaa ;
	    ELSE INSERT INTO bookmain.book
            (id,
             bookName,
             price,
             author,
             bookTypeId)
VALUES (NULL,
        'test',
        122,
        '里斯',
        23);
	    END IF ;
	  END LOOP aaa ;
	END 
|
DELIMITER ;

CALL create_book4(200);

ITERATE 语句

ITERATE 语句也是用来跳出循环的语句。但是，ITERATE 语句是跳出本次循环，然后直接进入下一次
循环。基本语法：
ITERATE label ;

DELIMITER |

CREATE PROCEDURE create_book5(IN totalNum INT)
	BEGIN
	  aaa:LOOP
	    
	      SET totalNum=totalNum-1;
	    IF totalNum=0 THEN LEAVE aaa ;
	      ELSEIF totalNum=3 THEN  ITERATE  aaa;
	     END IF ;
	     INSERT INTO bookmain.book
            (id,
             bookName,
             price,
             author,
             bookTypeId)
VALUES (NULL,
        'test',
        122,
        '里斯',
        22);
	   
	  END LOOP aaa ;
	END 
|
DELIMITER ;

CALL create_book5(4);

  经过对比，实际插入的数据是2条，无误~

REPEAT 语句

REPEAT 语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。REPEAT 语句的基本
语法形式如下：
[ begin_label : ] REPEAT
Statement_list
UNTIL search_condition
END REPEAT [ end_label ]

DELIMITER |

CREATE PROCEDURE create_book6(IN totalNum INT)
	BEGIN
	    REPEAT
	     SET totalNum=totalNum-1;
	    INSERT INTO bookmain.book
            (id,
             bookName,
             price,
             author,
             bookTypeId)
VALUES (NULL,
        'test',
        122,
        '里斯',
        22);
	     UNTIL totalNum=1 
	  END REPEAT;
	    
	     
	END 
|
DELIMITER ;

CALL create_book6(4);/**-插入了3条数据**/

 

 

 

WHILE 语句

[ begin_label : ] WHILE search_condition DO
Statement_list
END WHILE [ end_label ]

DELIMITER |

CREATE PROCEDURE create_book7(IN totalNum INT)
	BEGIN
	    WHILE totalNum>0 DO
	  INSERT INTO bookmain.book
            (id,
             bookName,
             price,
             author,
             bookTypeId)
VALUES (NULL,
        'test',
        122,
        '里斯',
        22);
	  SET totalNum=totalNum-1;
	 END WHILE ;
	     
	END 
|
DELIMITER ;




CALL create_book7(4);    --插入了2条数据

查看存储过程和函数

SHOW STATUS 语句查看存储过程和函数的状态

SHOW { PROCEDURE | FUNCTION } STATUS [ LIKE ‘pattern’] ;
SHOW PROCEDURE STATUS LIKE 'create_book1';

SHOW CREATE 语句查看存储过程的函数的定义

SHOW CREATE { PROCEDURE | FUNCTION } sp_name ;
SHOW CREATE PROCEDURE create_book1;

从 information_schema.Routines 表中查看存储过程和函数的信息

修改存储过程和函数

ALTER { PROCEDURE | FUNCTION } sp_name [ characteristic ... ]
characteristic :
{ CONTAINS SQL } NO SQL | READS SQL DATA| MODIFIES SQL DATA}
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT ‘string’
其中，sp_name 参数表示存储过程或函数的名称；characteristic 参数指定函数的特性。CONTAINS SQL 表示子程
序包含 SQL 语句，但不包含读或写数据的语句；NO SQL 表示子程序中不包含 SQL 语句；READS SQL DATA
表 示 子 程 序 中 包 含 数 据 的 语 句 ； MODIFIES SQL DATA 表 示 子 程 序 中 包 含 写 数 据 的 语 句 。 SQL
SECURITY{ DEFINER | INVODER } 指明谁有权限来执行。DEFINER 表示只有定义者自己才能够执行；
INVODER 表示调用者可以执行。COMMENT ‘string’ 是注释信息。

ALTER PROCEDURE create_book1  COMMENT '这是一个增加书本信息的存储过程';

 

删除存储过程和函数

 

DROP {PROCEDURE | FUNCTION } sp_name ;
DROP PROCEDURE create_book1;

存储过程和函数的区别
函数：只能返回一个变量的限制。而存储过程可以返回多个。
函数是可以嵌入在sql中使用的,可以在select中调用，而存储过程不行 
执行的本质都一样。 
函数限制比较多，比如不能用临时表，只能用表变量．还有一些函数都不可用等等．而存储过程的限制相对就比较少
1. 一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。
2. 对于存储过程来说可以返回参数，而函数只能返回值或者表对象。
3. 存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。
4. 当存储过程和函数被执行的时候，SQL Manager会到PRocedure cache中去取相应的查询语句，如果在procedure cache里没有相应的查询语句，SQL Manager就会对存储过程和函数进行编译
5.二者都可以有出参 ,存储过程和函数目的是为了 可重复地 执行操作数据库的sql语句的集合。

 

不同点：
1、标识符不同。函数的标识符为FUNCTION，过程为：PROCEDURE。
2、函数中有返回值，且必须返回，而过程没有返回值。
3、过程无返回值类型，不能将结果直接赋值给变量；函数有返回值类型，调用时，除在select中，必须将返回值赋给变量。
4、函数可以在select语句中直接使用，而过程不能，例如：假设已有函数fun_getAVG() 返回number类型绝对值。那么select fun_getAVG(col_a) from table 这样是可以的。
 5.存储过程的返回值，可以有多个值,函数的返回值，只有一个值。

 

 

 

　　　　，