mysql存储过程

 

1、存储过程简介
一个存储过程是一个可编程的函数，它在数据库中创建并保存。
(1)存储过程增强了SQL语言的功能和灵活性
(2)存储过程允许标准组件是编程。
(3)存储过程能实现较快的执行速度   预编译。 比批处理快
(4)存储过程能够减少网络流量
(5)存储过程可被作为
2、创建存储过程
(1) delimiter //   从新设置分隔符  表示;不代表语句结束。
(2)create procedure demo2()  //创建存储过程  
begin //存储过程开始
select * from ss;       //过程体，要执行的sql语句
select "hello world" as welcome;
insert into ss values(1,'aaa');
end //存储过程结束
//            //用执行的分隔符表示结束
delimiter ;   将分隔符恢复

(3)调用存储过程
call demo2();  //调用存储过程


(4)带有参数的存储过程
create procedure proc1(OUT s int)  OUT表示输出参数
select count(*) into s from user;   //查询多少记录并存入到s中


例： delimiter //
    create procedure demo3(out s int)
    begin
    select count(*) into s from ss;
    end // 
   set @var1=1//
  call demo3(@var1)//
  select @var1//
   delimiter ;   恢复分隔符
 (5)参数共分为三种  输出  输入 输入输出    默认为IN 
IN 输入参数：表示该参数的值必须是调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
delimiter //
create procedure demo_in(IN p_in int)
begin
select p_in;
set p_in=50;
select p_in;
end // 
delimiter ;
调用   call demo_in(100);
变量调用  set @p_in=5;
 call demo_in(@p_in);
 select @p_in;  结果依然为5 
//接受调用该存储过程时传的值  
OUT输出参数：该值可在存储过程内部被改变，并可返回
delimiter //
create procedure demo_out(out out_var int)
begin
select out_var;
set out_var=50;
select out_var;
end // 
delimiter ;
调用  set @out_var=5;
     call demo_out(@out_var);
      select @out_var;
//结果出现null  因为它不能接受值，因为它为输出，
//不能接受调用该存储过程时传的值参数的值

INOUT输入输出：调用时指定，并且改变和返回
delimiter //
create procedure demo_inout(inout in_out_var int)
begin 
select in_out_var;
set in_out_var=50;
select in_out_var;
end // 
delimiter ;
调用：set @in_out_var=5;
     call demo_inout(@in_out_var);
select @in_out_var;
3、变量
(1)局部变量定义 (只在过程中有效)
//声明一定要放在存储过程的开始。
定义：declare l_int int unsigned default 4000;
赋值  set l_int=50;
delimiter //
create procedure demo_var()
begin
declare l_int int default 100;
declare l_varchar varchar(40);
set l_varchar=".net program";
select l_int;
select l_varchar;
select @x;
select @z;
end //
(2)用户变量
可以直接使用。 与js中var类型相似
set @x=10;
set @a='aaa';
//在此会话中全部有效。
set @z=12*8;
select @z;
set @x="java program";
select @x;

concat('aa','bbb');  //连接两个字符串  函数
在存储过程传递全局范围的用户变量
注意：
1)用户变量名一般以@开头
2)滥用用户变量会导致程序难以理解及管理
4、注释
/* 多行注释*/
-- 单行注释
5、查看存储过程
show procedure status where db=数据库名;
show create procedure 存储过程名   查看创建存储过程的语句
drop procedure  存储过程名  删除指定存储过程
alert procedure 存储过程名   更新
begin
end


6、条件语句
语法格式 if-then -else
if 测试条件 then  语句列表
[else if 条件1 then 语句1]
[else 语句]
end if  --注意，表示if语句结束标识
例： 
delimiter //
create procedure campar(in k1 int,in k2 int,out k3 varchar(10)) 
begin
if k1>k2 then
set k3="大于";
else if k1<k2 then
set k3="小于";
else   
set k3="等于";
end if;
end //
delimiter ;
7：case语句
一般格式
case var
when 0 then
insert into t values(11);
when 1 then
insert into t values(12);
else
insert into t values(13);
end case;

case 
when var=0 then
inser into t values(14);
when var>0 then

when var<0 then

else

end case;


8、循环机制  语法
(1)while循环
while 条件 do
insert into t varlues(var);
set var=var-1;
end while;
(2)、repeat.... end repeat;
repeat 
insert into t varlues(v);--循环体
set v=v+1;
util var>=5;  --循环条件
end repeat;

util 直到条件成立  跳出循环
(3)、loop....end loop;
loop循环不需要初始条件
LOOP_LABLE:loop
--循环体
if v>=5 then
leave LOOP_LABLE;   --跳到标号外，跳出循环
end if;
end loop;
9、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 
TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
UCASE (string2 ) //转换成大写
RIGHT(string2,length) //取string2最后length个字符
SPACE(count) //生成count个空格




10、数学类
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为小数位数]
注：返回类型并非均为整数，
二、Java代码处理多个结果集
String sql="{call test_mutil()}";
st=con.prepareCall(sql);
boolean flag=st.execute(); //如果为真，表示最少有一个结果集
int i=0;
while(flag){
System.out.println("第"+(++i)+"结果集：");
rs=st.getResultSet();
while(rs.next){
//读取结果集数据
System.out.println(rs.getString(1));
}
flag=st.getMoreResults(); //判断是否还有更多的结果集  跳出条件