mysql存储过程procedure
时间:2015-10-08 12:46 来源:linux.it.net.cn 作者:IT
1、 存储过程的概念：
所谓的存储过程就是存储在数据库当中的可以执行特定工作(查询和更新)的一组SQL代码的程序段。
2、存储过程的优点：
（1）存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
（2）当对数据库进行复杂操作时（如对多个表进行Update、Insert、Query、Delete时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
（3）存储过程可以重复使用，可减少数据库开发人员的工作量。
（4）安全性高，可设定只有某些用户才具有对指定存储过程的使用权。
3、关于MySQL的存储过程
存储过程是数据库存储的一个重要的功能，但是MySQL在5.0以前并不支持存储过程，这使得MySQL在应用上大打折扣。好在MySQL 5.0终于开始已经支持存储过程，这样即可以大大提高数据库的处理速度，同时也可以提高数据库编程的灵活性。
4、mysql存储过程的创建：
格式：
1
2
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
（1）默认地，子程序与当前数据库关联。要明确地把子程序与一个给定数据库关联起来，可以在创建子程序的时候指定其名字为db_name.sp_name。
（2）参数解释：
sp_name 存储过程的名字
proc_parameter指定参数为IN， OUT，或INOUT
characteristic 特征
routine_body 包含合法的SQL过程语句。
（3）简单的例子：
1
2
3
4
5
6
7
mysql> DELIMITER //
mysql> CREATE PROCEDURE proc1(OUT s int)
-> BEGIN
-> SELECT COUNT(*) INTO s FROM user;
-> END
-> //
mysql> DELIMITER ;
注：
1）这里需要注意的是DELIMITER //和DELIMITER ;两句，DELIMITER是分割符的意思，因为MySQL默认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将";"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。
2）存储过程根据需要可能会有输入、输出、输入输出参数，这里有一个输出参数s，类型是int型，如果有多个参数用","分割开。
3）过程体的开始与结束使用BEGIN与END进行标识。
4、列出所有的存储过程
1
SHOW PROCEDURE STATUS;
5、调用存储过程：
1
2
3
mysql > SET @p_out=0;
mysql > CALL proc1(@p_out);
mysql> SELECT @p_out;
查看 变量p_out的前后结果：
?
1
2
3
4
5
6
7
8
9
10
11
+-------+
| p_out |
+-------+
| 0 |
+-------+
+-------+
| p_out |
+-------+
| 2 |
+-------+
6、 删除存储过程：
1
DROP PROCEDURE IF EXISTS proc<span style="font-size:9pt;line-height:1.5;background-color:#FFFFFF;">1</span>
7、Java代码调用存储过程(JDBC)
相关API：java.sql.CallableStatement
使用到java.sql.CallableStatement接口，该接口专门用来调用存储过程;
该对象的获得依赖于java.sql.Connection;
通过Connection实例的prepareCall()方法返回CallableStatement对象
prepareCall()内部为一固定写法{call 存储过程名(参数列表1,参数列表2)}可用?占位
eg: connection.prepareCall("{call proc_employee(?)}");
存储过程中参数处理:
输入参数:通过java.sql.CallableStatement实例的setXXX()方法赋值，用法等同于java.sql.PreparedStatement
输出参数:通过java.sql.CallableStatement实例的registerOutParameter(参数位置, 参数类型)方法赋值,其中参数类型主要使用java.sql.Types中定义的类型
Java代码调用带输入参数的存储过程 (根据输入ID查询user信息)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
publicvoid executeProcedure()
{
try {
/**
*callableStatementjava.sql.CallableStatement
*connectionjava.sql.Connection
*jdbc调用存储过程原型
*{call存储过程名(参数列表1,参数列表2)}可用?代替
*/
callableStatement=connection.prepareCall("{call proc_employee_findById(?)}");
callableStatement.setInt(1, 1); //设置输入参数
resultSet=callableStatement.executeQuery();//执行存储过程
if(resultSet.next())
{
System.out.println(resultSet.getInt(1)+""t"+resultSet.getString(2));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
Java代码调用带输出参数的存储过程 (返回数据库中的记录数)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
<strong>publicvoid executeProcedure()
{
try {
/**
*callableStatementjava.sql.CallableStatement
*connectionjava.sql.Connection
*jdbc调用存储过程原型
*{call存储过程名(参数列表1,参数列表2)}可用?代替
*/
callableStatement=connection.prepareCall("{call proc_employee_getCount(?)}");
//设置输出参数
callableStatement.registerOutParameter(1, Types.INTEGER);
//执行存储过程
resultSet=callableStatement.executeQuery();
if(resultSet.next())
{
System.out.println(resultSet.getInt(1));
}
} catch (SQLException e) {
e.printStackTrace();
}
}</strong>
8、MySQL存储过程的基本函数：
(1).字符串类
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开头,直到字串长度为length
LTRIM (string2 ) //去除前端空格
REPEAT (string2 ,count ) //重复count次
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str
RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length
RTRIM (string2 ) //去除后端空格
STRCMP (string1 ,string2 ) //逐字符比较两字串大小,
SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符,
注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1
(2).数学类
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为小数位数]
(3).日期时间类
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_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) // 分 返回符号 , 正负或 0
SQRT(number2) //开平方
参照：http://my.oschina.net/u/195896/blog/75310
http://www.blogjava.net/sxyx2008/archive/2009/11/24/303497.html
http://pcwanli.blog.163.com/blog/static/4531561120123243279258/
http://wenku.baidu.com/view/d41d44bec77da26925c5b0de.html
(责任编辑：IT)
| 1、 存储过程的概念：
所谓的存储过程就是存储在数据库当中的可以执行特定工作(查询和更新)的一组SQL代码的程序段。
2、存储过程的优点： 3、关于MySQL的存储过程
存储过程是数据库存储的一个重要的功能，但是MySQL在5.0以前并不支持存储过程，这使得MySQL在应用上大打折扣。好在MySQL 5.0终于开始已经支持存储过程，这样即可以大大提高数据库的处理速度，同时也可以提高数据库编程的灵活性。 4、mysql存储过程的创建： 格式：
（2）参数解释：
sp_name 存储过程的名字 （3）简单的例子：
1）这里需要注意的是DELIMITER //和DELIMITER ;两句，DELIMITER是分割符的意思，因为MySQL默认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将";"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。 2）存储过程根据需要可能会有输入、输出、输入输出参数，这里有一个输出参数s，类型是int型，如果有多个参数用","分割开。 3）过程体的开始与结束使用BEGIN与END进行标识。 4、列出所有的存储过程
?
7、Java代码调用存储过程(JDBC)
相关API：java.sql.CallableStatement 使用到java.sql.CallableStatement接口，该接口专门用来调用存储过程; 该对象的获得依赖于java.sql.Connection; 通过Connection实例的prepareCall()方法返回CallableStatement对象 prepareCall()内部为一固定写法{call 存储过程名(参数列表1,参数列表2)}可用?占位 eg: connection.prepareCall("{call proc_employee(?)}"); 存储过程中参数处理: 输入参数:通过java.sql.CallableStatement实例的setXXX()方法赋值，用法等同于java.sql.PreparedStatement 输出参数:通过java.sql.CallableStatement实例的registerOutParameter(参数位置, 参数类型)方法赋值,其中参数类型主要使用java.sql.Types中定义的类型 Java代码调用带输入参数的存储过程 (根据输入ID查询user信息)
8、MySQL存储过程的基本函数： (1).字符串类
CHARSET(str) //返回字串字符集 (2).数学类
ABS (number2 ) //绝对值
(3).日期时间类
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_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) // 分 返回符号 , 正负或 0 SQRT(number2) //开平方
参照：http://my.oschina.net/u/195896/blog/75310 http://www.blogjava.net/sxyx2008/archive/2009/11/24/303497.html http://pcwanli.blog.163.com/blog/static/4531561120123243279258/ http://wenku.baidu.com/view/d41d44bec77da26925c5b0de.html (责任编辑：IT) |