mysql存储过程 text_[整理]Mysql-存储过程 学习整理

简单的使用

创建

DROP PROCEDURE IF EXISTSpro2;

DELIMITER// # $或 /

CREATE PROCEDURE pro2(OUT s INT)BEGIN SELECT COUNT(*) INTO s FROMnavi_issuer;END $$

点：“DELIMITER //”语句的作用是将MYSQL的结束符设置为//，因为MYSQL默认的语句结束符为分号;

为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER 改变存储过程的结束符，并以“END//”结束存储过程。

存储过程定义完毕之后再使用DELIMITER ;恢复默认结束符。DELIMITER 也可以指定其他符号为结束符！！！！！！！！！！！

当使用DELIMITER命令时，应该避免使用反斜杠(\)字符，因为反斜杠是MYSQL的转义字符！！！

1)　调用

CALL pro1(@s);SELECT @s;

2.关于参数

DELIMITER //

DROP PROCEDURE IF EXISTS `test_param_null` //

CREATE DEFINER=`root`@`localhost` PROCEDURE `test_param_null`(IN inVal INT(4),OUT outVal INT(4))BEGIN

DECLARE counter INT(2) DEFAULT 0;SET counter =inVal;

#set outVal = 0;WHILE counter <= 100DOSET outVal = outVal +counter;SET counter = counter +1;END WHILE;END//DELIMITER ;

其中in表示输入参数，out表示输出参数，inout表示既可以输入也可以输出，存储过程没有返回值，所以使用 参数 获取 程序中的值；

点：

变量使用前必须初始化--如果直接使用  CALL test_cursor(@count); 的话，得到的结果 是null，也就是说 输入的参数 是 null的话，相加多少 都是null

即 SELECT NULL + 1 + 1 +1; 怎么都是 null

所以在这里判断 如果 传入的是 null，赋值为0，这样才能有结果。

但是 为什么

SET @count = 1 ;

CALL test_param_null(10,@count);

不判断 这样调用 结果也是null呢，不是 传入值了吗？

SET @co = 1 ;

SELECT @co + 3;

的方式  是可以的啊

变量定义和赋值

DECLARE MYPARAM INT DEFAULT 100;declare v int(2) default 1;DECLAREvar1,var2,var3INT;

SET var1=10,var2=20;SET var3=var1+var2;

点：

MYSQL中还可以通过SELECT...INTO为一个或多个变量赋值

DECLARE NAME CHAR(50);DECLARE id DECIMAL(8,2);SELECT id,NAME INTO id ,NAME FROM t3 WHERE id=2;

附：http://www.cnblogs.com/doit8791/archive/2012/05/11/2495319.html

Mysql 常用数据类型

1.字符数据类型

CHAR

1~255个字符的定长串，它的长度必须在创建时指定，否则MySQL假定为CHAR(1)

VARCHAR

可变长度，最多不超过255字节，如在创建时指定VARCHAR(n)，则可存储0~n个字符的变长串

TINYTEXT

同TEXT，最大长度为255字节

MEDUIMTEXT

同TEXT，最大长度为16K

TEXT

最大长度为64K的变长文本

LONGTEXT

同Text，最大长度为4GB(纯文本，一般不会到4G)

ENUM

接受最多64K个串组成的预定义集合的某个串

SET

接受最多64K个串组成的预定义集合的零个或多个串

注意：　不管任何形式的串数据类型，串值都必须在引号内(通常单引号更好)；

如果数值是计算中使用的数值，则应存储在数值数据类型列中，如果作为字符串使用(如电话号码、邮政编码)则应保存在串数据类型列中。

2.数值数据类型

类型说明

存储需求

取值范围

tinyint[(m)]

1字节

有符号值：-128 到127(- 2^7 到2^7 – 1)

无符号值：0到255(0 到2^8 – 1)

smallint[(m)]

2字节

有符号值：-32768 到32767(- 2^15 到2^15 – 1)

无符号值：0到65535(0 到21 6 – 1)

mediumint[(m)]

3字节

有符号值：-8388608 到8388607(- 2^23 到2^23 – 1 )

无符号值：0到16777215(0 到2^24 – 1)

int[(m)]

4字节

有符号值：-2147683648 到2147683647(- 2^31 到2^31- 1)

无符号值：0到4294967295(0 到2^32 – 1)

bigint[(m)]

8字节

有符号值：-9223372036854775808 到9223373036854775807(- 2^63到2^63-1)

无符号值：0到18446744073709551615(0到2^64 – 1)

float[(m, d)]

4字节

最小非零值：±1.175494351e – 38

double[(m, d)]

8字节

最小非零值：±2.2250738585072014e – 308

decimal (m, d)

m字节(mysql < 3.23)，m+2字节(mysql > 3.23 )

可变；其值的范围依赖于m 和d

mysql提供了5种整型： tinyint、smallint、mediumint、int和bigint(字节数1、2、3、4、8)，这些类型在可表示的取值范围上是不同的。整数列可定义为unsigned从而禁用负值；这使列的取值范围为0以上。

mysql 提供三种浮点类型： float、double和decimal。与整型不同，浮点类型不能是unsigned的，其取值范围也与整型不同，这种不同不仅在于这些类型有最大值，而且还有最小非零值。最小值提供了相应类型精度的一种度量，这对于记录科学数据来说是非常重要的(当然，也有负的最大和最小值)。

在选择了某种数值类型时，应该考虑所要表示的值的范围，只需选择能覆盖要取值的范围的最小类型即可。选择较大类型会对空间造成浪费，使表不必要地增大，处理起来没有选择较小类型那样有效。对于整型值，如果数据取值范围较小，如人员年龄或兄弟姐妹数，则tinyint最合适。mediumint能够表示数百万的值并且可用于更多类型的值，但存储代价较大。bigint在全部整型中取值范围最大，而且需要的存储空间是表示范围次大的整型int类型的两倍，因此只在确实需要时才用。对于浮点值，double占用float的两倍空间。除非特别需要高精度或范围极大的值，一般应使用只用一半存储代价的float型来表示数据。

在定义整型列时，可以指定可选的显示尺寸m。如果这样，m应该是一个1 到255的整数。它表示用来显示列中值的字符数。例如，mediumint(4)指定了一个具有4个字符显示宽度的mediumint列。如果定义了一个没有明确宽度的整数列，将会自动分配给它一个缺省的宽度。缺省值为每种类型的"最长"值的长度。如果某个特定值的可打印表示需要不止m个字符，则显示完全的值；不会将值截断以适合m个字符。需要注意的是，使用一个宽度指示器不会影响字段的大小和它可以存储的值的范围。

对每种浮点类型，可指定一个最大的显示尺寸m 和小数位数d。m 的值应该取1 到255。d的值可为0 到3 0，但是不应大于m – 2(如果熟悉odbc 术语，就会知道m 和d 对应于odbc 概念的"精度"和"小数点位数")。m和d对float和double 都是可选的，但对于decimal是必须的。在选项m 和d时，如果省略了它们，则使用缺省值。

3.

日期和时间数据类型MySQl中有多种表示日期和时间的数据类型。其中YEAR表示年份，DATE表示日期，TIME表示时间，DATETIME和TIMESTAMP表示日期和实践。具体如下表：

数据类型

存储字节数

取值范围

DATE

4

1000-01-01——9999-12-31

TIME

3

-838:59:59——838:59:59

DATETIME

8

1000-01-01 00:00:00——9999-12-31 23:59:59

TIMESTAMP

4

19700101080001——20380119111407

YEAR

1

1901——2155

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

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

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

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

数据类型

说明

TITYBLOB

最大长度为255字节

BLOB

最大长度为64KB

MEDIUMBLOB

最大长度为16MB

LONGBLOB

最大长度为4GB

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

数据类型

说明

TITYBLOB

最大长度为255字节

BLOB

最大长度为64KB

MEDIUMBLOB

最大长度为16MB

LONGBLOB

最大长度为4GB

流程控制语句 WHEN...Case

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;CASEvWHEN 1 THEN

SET n = v*1;WHEN 2 THEN

SET n = v*4;WHEN 3 THEN

SET n = v*10;SELECTn;END CASE;END //

流程控制语句 IF

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 0 AND id_p > num THEN

SELECT shortname INTO u_p_name FROM navi_issuer i WHERE i.id = 1;SELECTu_p_name;

ELSEIF id_p>0 AND id_p

SELECT i.shortname INTO u_p_name FROM navi_issuer i WHERE i.id = 2;

#set u_p_name ='222222';SELECTu_p_name;ELSE

SELECT i.shortname INTO u_p_name FROM navi_issuer i WHERE i.id = 3;SELECTu_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 fromnavi_issuer;

#SELECTid_p;

#selectu_p_name;SET u_name =CONCAT(u_p_name ,"TTT");END$$

DELIMITER ;

附：

存储过程 操作符：

http://de.cel.blog.163.com/blog/static/5145123620113201033629/

算术运算符+ 加 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>2False< 小于 2<1False<= 小于等于 2<=2True>= 大于等于 3>=2TrueBETWEEN 在两值之间 5 BETWEEN 1 AND 10TrueNOT BETWEEN 不在两值之间 5 NOT BETWEEN 1 AND 10FalseIN 在集合中 5 IN (1,2,3,4) FalseNOT IN 不在集合中 5 NOT IN (1,2,3,4) True= 等于 2=3False<>, != 不等于 2<>3False<=> 严格比较两个NULL值是否相等 NULL<=>NULLTrueLIKE 简单模式匹配 "Guy Harrison" LIKE "Guy%" True

REGEXP 正则式匹配 "Guy Harrison" REGEXP "[Gg]reg" FalseIS NULL 为空 0 IS NULLFalseIS NOT NULL 不为空 0 IS NOT NULL True

逻辑运算符AND  OR  XOR位运算符

| 位或

& 位与

<< 左移位

>> 右移位

~ 位非(单目运算，按位取反)

流程控制语句 循环---Loop...ITERATE---LEAVE loop;While....do;repeat...until

#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:LOOPSET loop_param =2*loop_param;IF(loop_param>100) THENLEAVE loop1;END IF;###################### Attention：endif后面有分号。。。。。。ENDLOOP loop1;

#whiledo/**/

WHILE while_param <=100DOSET while_param = 3*while_param;END WHILE;

#repeat until

REPEATSET repeat_param = 4*repeat_param;

UNTIL

repeat_param>100#Attention：until 后面的的条件 后面没有分号。ENDREPEAT ;/**/

SELECTloop_param;SELECTwhile_param;SELECTrepeat_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//

游标 (光标)cursor

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 FROMnavi_issuer;/*表示将从table1表中选取col1列的内容放到游标curl中，

即每次游标遍历的结果都放在curl中，要注意游标只能向前遍历，而不能向后，并且注意，游标不能更新，最后关闭游标。*/

DECLARE CONTINUE HANDLER FOR NOT FOUND SET cunter = -1;

#定义 当CURSOR没有数据返回的 时候的 操作；

#它的含义是：若没有数据返回,程序继续,并将变量 cunter 设置值 ，这种情况是出现在select XXinto XXX fromtablename的时候发生的。

#打开游标OPENcur;

#selectcur;/*变量使用前初始化的问题--如果直接使用 CALL test_cursor(@count); 的话，

得到的结果 是null，也就是说 输入的参数 是 null的话，相加多少 都是null

即 SELECT NULL + 1 + 1 +1; 怎么都是 null*/

IF outVal IS NULL THEN

SET outVal = 0;END IF;

#selectoutVal;WHILE cunter >0DOFETCH cur INTOnum;

#selectnum;IF num >0 THEN#selectnum;SET outVal = outVal +num;END IF;SELECToutVal;END WHILE;

#关闭游标CLOSEcur;END //

点：

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

DECLARE CONTINUE HANDLER FOR NOT FOUND SET cunter = -1;

#定义 当CURSOR没有数据返回的 时候的 操作；

#它的含义是：若没有数据返回,程序继续,并将变量 cunter 设置值 ，这种情况是出现在select XXinto XXX fromtablename的时候发生的。

异常捕获和处理

DECLARE CONTINUE HANDLER FOR NOT FOUND SET cunter = -1;

#定义 当 CURSOR 没有数据返回的 时候的 操作；

#它的含义是：若没有数据返回,程序继续,并将变量 cunter 设置值 ，这种情况是出现在select XX into XXX fromtablename的时候发生的。

实际上 游标的 没有数据时 的定义 就是 捕获操作，意为 ：

当发生了 某个异常，程序 继续，并且 赋值 给 某个变量//方法一：

捕获sqlstate_valueDECLARE CONTINUE HANDLER FOR SQLSTATE '42000'SET @info='CAN NOT FIND';//方法二：捕获mysql_error_codeDECLARE 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';//方法四：

使用SQLWARNINGDECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';//方法五：

使用NOT FOUNDDECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND';//方法六：

使用SQLEXCEPTIONDECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

数据库交互(存储过程中的sql语句)

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

DECLAREtotal_sales NUMERIC(8,2);

SELECTSUM(sale_value)INTOtotal_salesFROMsalesWHEREcustomer_id=in_customer_id;

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

DECLAREcur1CURSORFORSELECTemployee_id, salary, department_idFROMemployees;

DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=1;

OPENcur1;

FETCHcur1INTOl_employee_id, l_salary, l_department_id;

CLOSEcur1;

uunbounded SELECT语句用于存储过程返回结果集(？)

SELECT employee_id, surname, firstname, address1, address2, zipcode, date_of_birth FROM employees

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

UPDATE、INSERT、DELETE、CREATE TABLE等非查询语句也可以嵌入存储过程里

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或者declare语句定义变量，然后直接作为sql的表名是不行的，mysql会把变量名当作表名。

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

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

PREPAREstmt_nameFROMpreparable_stmt;

EXECUTEstmt_name[USING @var_name [, @var_name]...];

{DEALLOCATE|DROP}PREPAREstmt_name;

常用函数

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中出现的位置,不存在返回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开头,直到字串长度为lengthLTRIM (string2 ) //去除前端空格

REPEAT (string2 ,COUNT ) //重复count次REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str

RPAD (string2 ,LENGTH ,pad)//在str后用pad补充,直到长度为lengthRTRIM (string2 ) //去除后端空格

STRCMP (string1 ,string2 )//逐字符比较两字串大小,SUBSTRING (str , POSITION [,LENGTH]) //从str的position开始,取length个字符,

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

mysql> SELECT SUBSTRING(’abcd’,0,2);+———————–+

| SUBSTRING(’abcd’,0,2) |

+———————–+

| |

+———————–+

1 ROW IN SET (0.00sec)

mysql> SELECT SUBSTRING(’abcd’,1,2);+———————–+

| SUBSTRING(’abcd’,1,2) |

+———————–+

| ab |

+———————–+

1 ROW IN SET (0.02sec)

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)返回19

LEAST (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.00sec)

mysql> SELECT ROUND(1.56);+————-+

| ROUND(1.56) |

+————-+

| 2 |

+————-+

1 ROW IN SET (0.00sec)

(2)可以设定小数位数，返回浮点型数据

mysql> SELECT ROUND(1.567,2);+—————-+

| ROUND(1.567,2) |

+—————-+

| 1.57 |

+—————-+

1 ROW IN SET (0.00sec)SIGN (number2 ) //返回符号,正负或0SQRT(number2) //开平方

三.日期时间类

ADDTIME (date2 ,time_interval )//将time_interval加到date2

CONVERT_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格式显示datetime

DATE_SUB (date2 , INTERVAL d_value d_type )//在date2上减去一个时间DATEDIFF (date1 ,date2 ) //两个日期差DAY (DATE ) //返回日期的天

DAYNAME (DATE )//英文星期

DAYOFWEEK (DATE )//星期(1-7) ,1为星期天

DAYOFYEAR (DATE )//一年中的第几天

EXTRACT (interval_nameFROM 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

TIPS

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

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

◆存储过程的参数可以使用中文，在定义存储过程的时候加上character 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存储过程名称

SHOWCREATE FUNCTIONname_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 DATABEGIN

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

SELECTemployee_id, salary, department_idFROMemployeesWHERE department_id=in_department_id;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;CREATE TEMPORARY TABLE IF NOT EXISTSemp_raises

(employee_idINT, department_id INT, new_salary NUMERIC(8,2));OPENcur1;

emp_loop: LOOPFETCH cur1 INTOl_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*/

UPDATEemployeesSET salary=l_new_salaryWHERE employee_id=l_employee_id;/*Keep track of changed salaries*/

INSERT INTOemp_raises(employee_id, department_id, new_salary)VALUES(l_employee_id, l_department_id, l_new_salary);END IF:ENDLOOP emp_loop;CLOSEcur1;/*Print out the changed salaries*/

SELECT employee_id, department_id, new_salary fromemp_raisesORDER BY employee_id; END;

文章列表：

Mysql存储过程-博客园找找看 http://zzk.cnblogs.com/s?w=Mysql%E5%AD%98%E5%82%A8%E8%BF%87%E7%A8%8B&t=b

我的MYSQL学习心得(十) 自定义存储过程和函数 - 桦仔 - 博客园 http://www.cnblogs.com/lyhabc/p/3793524.html

MySQL数据库新特性之存储过程入门教程_知识库_博客园 http://kb.cnblogs.com/page/107457/

MySQL 存储过程 常用语法 - juanna_ding的专栏 - 博客频道 - CSDN.NET http://blog.csdn.net/juanna_ding/article/details/5381188

MySQL存储过程详解 mysql 存储过程_王者佳暮_新浪博客 http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html

mysq 存储过程判断为空null , mysql存储过程 字符串拼接 - 不念书的小子 - ITeye技术网站 http://tompig.iteye.com/blog/1568213

MySql 存储过程中要用到的运算符 - de.cel的日志 - 网易博客 http://de.cel.blog.163.com/blog/static/5145123620113201033629/

Mysql学习笔记之常用数据类型 - yingsuixindong - 博客园 http://www.cnblogs.com/doit8791/archive/2012/05/11/2495319.html

存储过程和函数的区别 - lengbingshy - 博客园 http://www.cnblogs.com/lengbingshy/archive/2010/02/25/1673476.html

2015-10-28 16:18:18

DELIMITER $$

CREATEPROCEDUREpro2(OUT sINT)

BEGINSELECTCOUNT(*)INTOsFROMnavi_issuer;

END$$