MYSQL存储过程以及JAVA调用

调用存储过程

call 存储过程名();

以上我们创建了一个简单的存储过程，当然我们的应用程序不可能使用这么简单的存储过程，我们需要的是能给存储过程传递参数，以返回给我们所需要的结果数据。下面就了解下存储过程的参数。

存储过程的参数

通常存储过程接受用户的参数，返回结果给调用用户。

mysql规定对于存储过程的参数要求其每个参数都必须声明其参数名，数据类型以及该参数是输入参数还是用于返回信息还是两者兼有，对于存储函数php只支持输入参数。

声明参数时规定要使用关键字IN,OUT,INOUT。

其中：

IN：用于输入参数

OUT：用于返回参数

INOUT：用于向存储过程传递参数值，如果该值改变则返回

另外规定对于声名为OUT，INOUT的参数当我们调用存储过程时需要在参数名前加@,以确保参数在过程外调用，下面我们修改上面的存储过程以传递信息编号给存储过程select_news,返回对应的信息标题给我们查看。

drop procedure if exists select_news;

create procedure select_news(IN id int,OUT title varchar(200))

select db_news.title from db_news where db_news.id=id;

与存储过程返回参数不同的是存储函数在定义时没用直接声明哪个变量是返回参数，而只是使用了returns声明了返回参数所属的数据类型，返回参数是在函数体中使用return返回要返回的数据变量的形式来表示的。这就需要注意的是：

存储函数只支持输入参数，并且输入参数前没有IN或INOUT.

返回上面的存储函数，我们来解释下代码含义：

drop function if exists count_news;

如果存在存储函数count_news则删除该存储函数，与存储过程一样我们可以使用下面的语句删除存储过程或函数

drop procedure|function [if exists] 存储过程名|存储函数名;

delimiter //

使用delimiter更改mysql默认使用分号(;)使用新的结束符号来结束当前语句，使用delimiter后原先默认的分号(;)结束语句符号不再起作用，直到重新恢复声明结束符后。

create function count_news(hits int) returns int

注意存储函数的参数只有输入参数并且前不再声明IN或INOUT，返回只需声明要返回的数据类型

comment '根据传入的点击次数统计超过此点击数的信息数目'

使用comment 来描述该存储过程或存储函数的功能信息。使用格式为 comment '描述字符串 '

begin

使用begin限定一个处理模块

declare total_news int;

声明变量 格式 declare 变量名 数据类型 [default 默认值]

   declare hits_num int default 0;

声明变量 格式 declare 变量名 数据类型 [default 默认值]

   if hits>=0 then

     set hits_num=hits;

      使用set 给变量赋值

select count(id) into total_news from db_news where db_news.hits>hits_num;

sql语句可以使用into给变量赋值

else

     set total_news=0;

   end if;

   上面是一个判断语句，注意格式为if-else-end if 或者 if –else if-else-end if

注意分号(;)

   return total_news;

   使用return 返回存储函数要返回的值，注意该值只能是一个值。

end;

//

delimiter ;

综合一下我们上面的概念知识：

1.       存储函数的参数与存储过程有何不同。

2.       在存储例程中如何更改mysql的默认结束符号。

3.       在存储例程中如何描述功能信息。

4.       声明、设置变量。

5.       begin-end语句块。

一.创建存储过程
create procedure sp_name()
begin
.........
end

二.调用存储过程
1.基本语法：call sp_name()
注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递

三.删除存储过程
1.基本语法：
drop procedure sp_name//

2.注意事项
(1)不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程

四.其他常用命令

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

2.show create procedure sp_name
显示某一个mysql存储过程的详细信息

mysql存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT
Create procedure|function([[IN |OUT |INOUT ] 参数名 数据类形...])

IN 输入参数
表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

OUT 输出参数
该值可在存储过程内部被改变，并可返回

INOUT 输入输出参数
调用时指定，并且可被改变和返回

IN参数例子：
CREATE PROCEDURE sp_demo_in_parameter(IN p_in INT)
BEGIN
SELECT p_in; --查询输入参数
SET p_in=2; --修改
select p_in;--查看修改后的值
END;

执行结果:
mysql> set @p_in=1
mysql> call sp_demo_in_parameter(@p_in)
略
mysql> select @p_in;
略
以上可以看出，p_in虽然在存储过程中被修改，但并不影响@p_id的值

OUT参数例子
创建:
mysql> CREATE PROCEDURE sp_demo_out_parameter(OUT p_out INT)
BEGIN
SELECT p_out;/*查看输出参数*/
SET p_out=2;/*修改参数值*/
SELECT p_out;/*看看有否变化*/
END;

执行结果:
mysql> SET @p_out=1
mysql> CALL sp_demo_out_parameter(@p_out)
略

mysql> SELECT @p_out;
略

INOUT参数例子：
mysql> CREATE PROCEDURE sp_demo_inout_parameter(INOUT p_inout INT)
BEGIN
SELECT p_inout;
SET p_inout=2;
SELECT p_inout;
END;

执行结果：
set @p_inout=1
call sp_demo_inout_parameter(@p_inout) //
略
select @p_inout;
略

附：函数库
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开头,直到字串长度为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
mysql> 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个空格