Mysql-存储过程 学习整理

001——207

YEAR

1901——2155

MySQL是以YYYY-MM-DD格式来显示DATE类型的值，插入数据时，数据可以保持这种格式。另外，MySQL还支持一些不严格的语法格式，分隔符"-"可以用"@"、"."等符号来替代。 在插入数据时，也可以使用"YY-MM-DD"格式，YY转化成对应的年份的规则与YEAR类型类似。如果我们想插入当前系统的时间，则可以插入或者NOW()。

TIME类型表示为"时：分：秒"，尽管小时范围一般是0~23，但是为了表示某些特殊时间间隔，MySQL将TIME的小时范围扩发了，而且支持负值。对TIME类型赋值，标准格式是'HH：MM：SS'，但不一定非要是这种格式。 如果插入的是'D HH：MM：SS'格式，则类似插入了'（D*24+HH）：MM：SS'。比如插入'2 23:50:50'，相当于插入了'71:50:50'。如果插入的是'HH：MM'或'SS'格式，则效果是其他未被表示位的值赋为零值。比如插入'30'，相当于插入了'00:00:30'；如果插入'11:25'，相当于插入了'11:25:00'。在MySQl中，对于''格式，系统能够自动转化为标准格式。如果我们想插入当前系统的时间，则可以插入或者NOW()。

类型准格式为"YYYY-MM-DD HH：MM：SS"，具体赋值方法与上面的方法相似。

的取值范围比较小，没有的取值范围大，因此输入值时一定要保证在的范围之内。它的插入也与插入其他日期和时间数据类型类似。那么类型如何插入当前时间？第一，可以使用；第二，输入NULL，系统自动输入当前的；第三，无任何输入，系统自动输入当前的。 另外有很特殊的一点：的数值是与时区相关。

给YEAR类型复制可以有三种方法： 第一种是直接插入4位字符串或者4位数字； 第二种是插入2位字符串，这种情况下如果插入'00'~'69'，则相当于插入2000~2069；如果插入'70'~'99'，则相当于插入1970~1999。第二种情况下插入的如果是'0'，则与插入'00'效果相同，都是表示2000年； 第三种是插入2位数字，它与第二种（插入两位字符串）不同之处仅在于：如果插入的是一位数字0，则表示的是0000，而不是2000年。所以在给YEAR类型赋值时，一定要分清0和'0'，虽然两者相差个引号，但实际效果确实相差了2000年。

数据类型

说明

最大长度为255字节

BLOB

最大长度为64KB

最大长度为16MB

最大长度为4GB

4.二进制数据类型：二进制类型可存储任何数据，如文字、图像、多媒体等。具体类型描述如下：

数据类型

说明

最大长度为255字节

BLOB

最大长度为64KB

最大长度为16MB

最大长度为4GB

DROP PROCEDURE IF EXISTS `test_case`;DELIMITER //CREATE PROCEDURE test_case(IN v INT(2),OUT n INT(2))BEGIN    /*    CALL test_case(3,@n);    SELECT @n;    */    #declare v int(2) default 1;    CASE v     WHEN 1 THEN        SET n = v*1;    WHEN 2 THEN        SET n = v*4;    WHEN 3 THEN        SET n = v*10;            SELECT n;        END CASE;END //

DELIMITER $$DROP PROCEDURE IF EXISTS `syntax_if`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `syntax_if`(IN id_p INT,OUT u_name VARCHAR(200))BEGIN    DECLARE u_p_name VARCHAR(200) DEFAULT '-NULL-';    DECLARE num  INT(2) DEFAULT 100;            #这是单行注释    /*    问题：        1.no data - zero rows fetched,selected,or processed.    使用 SELECT fullname INTO u_p_name FROM navi_issuer   WHERE id = 1;    的语句  总是 出 no data - zero rows fetched,selected,or processed.    SELECT  INTO 语句没有发挥作用。    后来改为     SELECT i.fullname INTO u_p_name FROM navi_issuer i  WHERE i.id = 1;            基本可以确定 的原因是  参数名称和 要查询的字段 名称 的参数 重复，id 实际上是传入的参数。        这个字段名 和参数名 重复的问题一定要注意！易出错误且难以查找。        2.sql中拼接字符 没有 “+” 号，而是使用 CONCAT("11" ,"22","1111"....);        3.SELECT就算　打印了，但是 如果写了多行，　好像只能　打印出　第一行。       4.这样可以 动态的 调用存储过程--动态sql？ 是否可以直接 写 sql 时定义语句，自己测试 总是 出错DECLARE    @verifyUserId INT(2) ;SET @verifyUserId = 1;DECLARE    cashOutId INT DEFAULT 1;DECLARE    userIp VARCHAR(200) DEFAULT '127';================√==============SET @verifyUserId = 1;SET @cashOutId = 1;SET @userIp = '1111';INSERT INTO navi_opt_log(muser_id,opt_content,ADDTIME,addip)VALUES(verifyUserId,CONCAT(FROM_UNIXTIME(UNIX_TIMESTAMP()),',提现申请【',cashOutId,'】已经审核通过！管理员标识',verifyUserId),                        UNIX_TIMESTAMP(),userIp);    */    #SELECT id_p>0 AND id_p <num;            IF id_p>0 AND id_p > num THEN        SELECT shortname INTO u_p_name FROM navi_issuer i  WHERE i.id = 1;        SELECT u_p_name;        ELSEIF id_p>0 AND id_p <num THEN        SELECT i.shortname INTO u_p_name FROM navi_issuer i WHERE i.id = 2;        #set u_p_name ='222222';        SELECT u_p_name;    ELSE        SELECT i.shortname INTO u_p_name FROM navi_issuer i WHERE i.id = 3;        SELECT u_p_name;    END IF;#注意 enif 后面有分号。            #IF id_p>0 AND id_p > num THEN        #SELECT i.fullname INTO u_p_name FROM navi_issuer i WHERE i.id = 1;    #END IF;        #select count(*) into num from navi_issuer;        #SELECT id_p;    #select u_p_name;                SET u_name = CONCAT(u_p_name ,"TTT");END$$DELIMITER ;

附：

存储过程 操作符：

算术运算符+ 加 SET var1=2+2; 4- 减 SET var2=3-2; 1* 乘 SET var3=3*2; 6/ 除 SET var4=10/3; 3.3333DIV 整除 SET var5=10 DIV 3; 3% 取模 SET var6=10%3 ; 1比较运算符> 大于 1>2 False< 小于 2<1 False<= 小于等于 2<=2 True>= 大于等于 3>=2 TrueBETWEEN 在两值之间 5 BETWEEN 1 AND 10 TrueNOT BETWEEN 不在两值之间 5 NOT BETWEEN 1 AND 10 FalseIN 在集合中 5 IN (1,2,3,4) FalseNOT IN 不在集合中 5 NOT IN (1,2,3,4) True= 等于 2=3 False<>, != 不等于 2<>3 False<=> 严格比较两个NULL值是否相等 NULL<=>NULL TrueLIKE 简单模式匹配 "Guy Harrison" LIKE "Guy%" TrueREGEXP 正则式匹配 "Guy Harrison" REGEXP "[Gg]reg" FalseIS NULL 为空 0 IS NULL FalseIS NOT NULL 不为空 0 IS NOT NULL True逻辑运算符
AND  OR  XOR
位运算符| 位或& 位与<< 左移位>> 右移位~ 位非(单目运算，按位取反)

#loop test 循环测试:loop;while;repeatDROP PROCEDURE IF EXISTS `syntax_loop`;DELIMITER //CREATE PROCEDURE syntax_loop(INOUT loop_param INT(4),INOUT while_param INT(4),INOUT repeat_param INT(4))    BEGIN        /*    loop1: LOOP       SET i=i+1;       IF i>=10 THEN                 LEAVE loop1;       ELSEIF MOD(i,2)=0 THEN         ITERATE loop1;       END IF;              SELECT CONCAT(i,' is an odd number');    END LOOP loop1;        可以这样理解ITERATE就是我们程序中常用的contiune,而LEAVE 就是break.当然在MySQL存储过程,需要循环结构有个名称,其他都是一样的.    */        # loop leave loop    /**/    loop1:LOOP        SET loop_param =2*loop_param;            IF(loop_param>100) THEN            LEAVE  loop1;        END IF;###################### Attention：end if后面有分号。。。。。。    END LOOP loop1;            #while do        /**/    WHILE while_param <=100 DO        SET  while_param = 3*while_param;    END WHILE;            #repeat until    REPEAT         SET repeat_param = 4*repeat_param;    UNTIL                repeat_param >100#Attention：until 后面的的条件 后面没有分号。        END REPEAT ;            /**/    SELECT loop_param;    SELECT while_param;    SELECT repeat_param;            /*    #http://www.blogjava.net/nonels/archive/2009/04/22/233324.html    动态的调用（InOut 参数的 形式）    SET @param_loop = 3;    SET @param_while = 2;    SET @param_repeat = 5;    CALL syntax_loop(@param_loop,@param_while,@param_repeat);    SELECT @param_loop;    SELECT @param_while;    SELECT @param_repeat;        */END//

DROP PROCEDURE IF EXISTS `test_cursor`;#DROP 这里 绝对不能用 单引号，必须 用 [`]DELIMITER //CREATE PROCEDURE test_cursor(OUT outVal INT(10))BEGIN    DECLARE cunter INT(2) DEFAULT 1;    DECLARE num INT(4) DEFAULT 4;        #定义游标    DECLARE  cur CURSOR FOR SELECT id FROM  navi_issuer;    /*    表示将从table1表中选取col1列的内容放到游标curl中，    即每次游标遍历的结果都放在curl中，要注意游标只能向前遍历，而不能向后，并且注意，游标不能更新，最后关闭游标。    */        DECLARE  CONTINUE HANDLER FOR NOT FOUND SET cunter = -1;    #定义 当 CURSOR 没有数据返回的 时候的 操作；    #它的含义是：若没有数据返回,程序继续,并将变量 cunter 设置值 ，这种情况是出现在select XX into XXX from tablename的时候发生的。                #打开游标    OPEN cur;        #select cur;        /*        变量使用前初始化的问题--如果直接使用  CALL test_cursor(@count); 的话，    得到的结果 是null，也就是说 输入的参数 是 null的话，相加多少 都是null    即 SELECT NULL + 1 + 1 +1; 怎么都是 null       */    IF outVal IS NULL THEN        SET outVal = 0;    END IF;    #select outVal;        WHILE cunter >0 DO        FETCH cur INTO num;                #select num;                IF num >0 THEN         #select num;         SET outVal = outVal + num;                END IF;                SELECT outVal;    END WHILE;        #关闭游标    CLOSE cur;    END //

点：

MYSQL中，光标只能在存储过程和函数中使用！！

DECLARE  CONTINUE HANDLER FOR NOT FOUND SET cunter = -1;    #定义 当 CURSOR 没有数据返回的 时候的 操作；    #它的含义是：若没有数据返回,程序继续,并将变量 cunter 设置值 ，这种情况是出现在select XX into XXX from tablename的时候发生的。

DECLARE  CONTINUE HANDLER FOR NOT FOUND SET cunter = -1;   #定义 当 CURSOR 没有数据返回的 时候的 操作；   #它的含义是：若没有数据返回,程序继续,并将变量 cunter 设置值 ，这种情况是出现在select XX into XXX from tablename的时候发生的。实际上 游标的 没有数据时 的定义 就是 捕获操作，意为 ：当发生了 某个异常，程序 继续，并且 赋值 给 某个变量//方法一：    捕获sqlstate_value     DECLARE CONTINUE HANDLER FOR SQLSTATE '42000'SET @info='CAN NOT FIND'; //方法二：捕获mysql_error_code     DECLARE CONTINUE HANDLER FOR 1148 SET @info='CAN NOT FIND'; //方法三：先定义条件，然后调用    DECLARE can_not_find CONDITION FOR 1146 ;     DECLARE CONTINUE HANDLER FOR can_not_find SET @info='CAN NOT FIND';//方法四：    使用SQLWARNING     DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR'; //方法五：    使用NOT FOUND     DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND'; //方法六：    使用SQLEXCEPTION     DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

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

MERIC(8,2);

()=;

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

,,;

=1;

;

,,;

;

语句用于存储过程返回结果集（？）

,,,,,,

u其他的SQL语句也可以在存储过程中使用

、、、等非查询语句也可以嵌入存储过程里

DELIMITER $$USE `mytest`$$DROP PROCEDURE IF EXISTS `test_sql_in_procedure`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `test_sql_in_procedure`(IN add_title VARCHAR(500),IN upt_id INT(4),IN del_id INT(4),OUT outVal VARCHAR(10))BEGIN/*CALL test_sql_in_procedure('13省养老金并轨.',1,295,@outVal);SELECT @outVal;*//*动态sql 的编译和执行：mysql> PREPARE prod FROM "INSERT INTO examlple VALUES(?,?)";   mysql> SET @p='1';   mysql> SET @q='2';   mysql> EXECUTE prod USING @p,@q;   mysql> SET @name='3';   mysql> EXECUTE prod USING @p,@name;   mysql> DEALLOCATE PREPARE prod;  */    DECLARE num INT(2) DEFAULT 0;        #insert    IF add_title IS NULL OR add_title ='' THEN        SET add_title = '空值。。。';    END IF;        INSERT INTO navi_new (title) VALUES(add_title);    #update    IF upt_id IS NULL OR upt_id < 1 THEN        SET upt_id = 1;    END IF;        UPDATE navi_issuer  SET shortname = CONCAT(shortname,'_UPT_',NOW()) WHERE id = upt_id;        #delete        IF del_id IS NULL OR del_id <1 THEN        SET del_id = 295;    END IF;        DELETE FROM navi_issuer WHERE id = del_id;            SET num = 3;        SET outVal = num;END$$DELIMITER ;

u动态sql 的执行和引用

（怎么获取 update 语句的 影响了多少行 的那个 返回值？）http://maoyifa100.iteye.com/blog/1900305  语法[sql]PREPARE statement_name FROM sql_text /*定义*/   EXECUTE statement_name [USING variable [,variable...]] /*执行预处理语句*/  
DEALLOCATE PREPARE statement_name /*删除定义*/   例[sql]mysql> PREPARE prod FROM "INSERT INTO examlple VALUES(?,?)";   mysql> SET @p='1';   mysql> SET @q='2';   mysql> EXECUTE prod USING @p,@q;   mysql> SET @name='3';   mysql> EXECUTE prod USING @p,@name;   mysql> DEALLOCATE PREPARE prod;  

1.用变量做表名：简单的用set或者语句定义变量，然后直接作为sql的表名是不行的，mysql会把变量名当作表名。

在其他的sql数据库中也是如此，mssql的解决方法是将整条sql语句作为变量，其中穿插变量作为表名，然后用调用该语句。

这在.0之前是不行的，5.0之后引入了一个全新的语句，可以达到类似的功能（仅对有效，不支持动态查询）：

;

[USING@[,@]...];

{|DROP};

CONCAT：SELECT CONCAT('FIRST ', 'SECOND',’1111’,......);SET title=REPLACE(sendTitle,'webName',webName);Now();
。。。。。mysql常用函数 - sugang_ximi的专栏 - 博客频道 - CSDN.NET  http://blog.csdn.net/sugang_ximi/article/details/6664748 
MySql 存储过程中要用到的运算符 - de.cel的日志 - 网易博客  http://de.cel.blog.163.com/blog/static/5145123620113201033629/

mysql存储过程基本函数一.字符串类CHARSET(str) //返回字串字符集CONCAT (string2 [,... ]) //连接字串INSTR (STRING ,SUBSTRING ) //返回substring首次在string中出现的位置,不存在返回0LCASE (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开头,直到字串长度为lengthLTRIM (string2 ) //去除前端空格REPEAT (string2 ,COUNT ) //重复count次REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_strRPAD (string2 ,LENGTH ,pad) //在str后用pad补充,直到长度为lengthRTRIM (string2 ) //去除后端空格STRCMP (string1 ,string2 ) //逐字符比较两字串大小,SUBSTRING (str , POSITION [,LENGTH ]) //从str的position开始,取length个字符,注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1mysql> SELECT SUBSTRING(’abcd’,0,2);+———————–+| SUBSTRING(’abcd’,0,2) |+———————–+| |+———————–+1 ROW IN SET (0.00 sec)mysql> SELECT SUBSTRING(’abcd’,1,2);+———————–+| SUBSTRING(’abcd’,1,2) |+———————–+| ab |+———————–+1 ROW IN SET (0.02 sec)TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符UCASE (string2 ) //转换成大写RIGHT(string2,LENGTH) //取string2最后length个字符SPACE(COUNT) //生成count个空格二.数学类ABS (number2 ) //绝对值BIN (decimal_number ) //十进制转二进制CEILING (number2 ) //向上取整CONV(number2,from_base,to_base) //进制转换FLOOR (number2 ) //向下取整FORMAT (number,decimal_places ) //保留小数位数HEX (DecimalNumber ) //转十六进制注：HEX()中可传入字符串，则返回其ASC-11码，如HEX(’DEF’)返回4142143也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19LEAST (number , number2 [,..]) //求最小值MOD (numerator ,denominator ) //求余POWER (number ,POWER ) //求指数RAND([seed]) //随机数ROUND (number [,decimals ]) //四舍五入,decimals为小数位数]注：返回类型并非均为整数，如：(1)默认变为整形值mysql> SELECT ROUND(1.23);+————-+| ROUND(1.23) |+————-+| 1 |+————-+1 ROW IN SET (0.00 sec)mysql> SELECT ROUND(1.56);+————-+| ROUND(1.56) |+————-+| 2 |+————-+1 ROW IN SET (0.00 sec)(2)可以设定小数位数，返回浮点型数据mysql> SELECT ROUND(1.567,2);+—————-+| ROUND(1.567,2) |+—————-+| 1.57 |+—————-+1 ROW IN SET (0.00 sec)SIGN (number2 ) //返回符号,正负或0SQRT(number2) //开平方三.日期时间类ADDTIME (date2 ,time_interval ) //将time_interval加到date2CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区CURRENT_DATE ( ) //当前日期CURRENT_TIME ( ) //当前时间CURRENT_TIMESTAMP ( ) //当前时间戳DATE (DATETIME ) //返回datetime的日期部分DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间DATE_FORMAT (DATETIME ,FormatCodes ) //使用formatcodes格式显示datetimeDATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间DATEDIFF (date1 ,date2 ) //两个日期差DAY (DATE ) //返回日期的天DAYNAME (DATE ) //英文星期DAYOFWEEK (DATE ) //星期(1-7) ,1为星期天DAYOFYEAR (DATE ) //一年中的第几天EXTRACT (interval_name FROM DATE ) //从date中提取日期的指定部分MAKEDATE (YEAR ,DAY ) //给出年及年中的第几天,生成日期串MAKETIME (HOUR ,MINUTE ,SECOND ) //生成时间串MONTHNAME (DATE ) //英文月份名NOW ( ) //当前时间SEC_TO_TIME (seconds ) //秒数转成时间STR_TO_DATE (STRING ,FORMAT ) //字串转成时间,以format格式显示TIMEDIFF (datetime1 ,datetime2 ) //两个时间差TIME_TO_SEC (TIME ) //时间转秒数]WEEK (date_time [,start_of_week ]) //第几周YEAR (DATETIME ) //年份DAYOFMONTH(DATETIME) //月的第几天HOUR(DATETIME) //小时LAST_DAY(DATE) //date的月的最后日期MICROSECOND(DATETIME) //微秒MONTH(DATETIME) //月MINUTE(DATETIME) //分附:可用在INTERVAL中的类型DAY ,DAY_HOUR ,DAY_MINUTE ,DAY_SECOND ,HOUR ,HOUR_MINUTE ,HOUR_SECOND ,MINUTE ,MINUTE_SECOND,MONTH ,SECOND ,YEAR

◆存储过程里面是可以调用其他存储过程的，使用CALL语句调用其他存储过程就可以了

◆存储过程参数列表里的参数名尽量不要和数据库中表的字段名一样，否则有可能出错

◆存储过程的参数可以使用中文，在定义存储过程的时候加上 set gbk就可以了

例如

　　　　CREATE PROCEDURE useInfo(IN u_name VARCHAR(50) CHARACTER SET gbk,OUT u_age INT)

SHOW  FUNCTION STATUS LIKE '%name_from_t3%'； like 存储过程名称SHOW CREATE FUNCTION  name_from_t3;查看定义SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME=' sp_name ' ; 从information_schema.Routines表中查看存储过程和函数的信息DROP FUNCTION IF EXISTS `name_from_t3`$$

1.

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;  

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

文章列表：

Mysql存储过程-博客园找找看 %E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B&t=b

我的MYSQL学习心得（十） 自定义存储过程和函数 - 桦仔 - 博客园

MySQL数据库新特性之存储过程入门教程_知识库_博客园

MySQL 存储过程 常用语法 - 的专栏 - 博客频道 - CSDN.NET

MySQL存储过程详解 mysql 存储过程_王者佳暮_新浪博客

mysq 存储过程判断为空 null , mysql存储过程 字符串拼接 - 不念书的小子 - ITeye技术网站

MySql 存储过程中要用到的运算符 - de.cel的日志 - 网易博客 //

Mysql学习笔记之常用数据类型 - - 博客园

存储过程和函数的区别 - - 博客园