1  存储过程简介

存储过程是数据库中为了完成某些复杂功能而设计的一种批量执行sql语句集的结构，编写好这种结构后会编译并存储在数据库服务器中，

调用的时候只需要通过存储过程的名称和参数来执行

2   存储过程的创建

2.1   CREATE  PROCEDURE  存储过程名 (参数列表)

BEGIN
SQL语句代码块
END

下面演示一个简单的例子：

create PROCEDURE test1()
BEGIN
insert into adminuser(username,password) values('agod','3232');
insert into grade(gradename,createdate,course_teacher,master_teacher)  values('098班',null,'afei','vv');
END

调用方式是:call test1()

这个存储过程里插入了两条数据，在实际项目中，这个里面的值必须从外面传进来，下面我们看看存储过程是怎样支持参数的

2.2  存储过程的参数

mysql支持三种类型的参数

类型

说明

IN：输入参数

该值只能传进去，不能返回出来

OUT:输出参数

该值可在内部改变，并返回出来

INOUT:输入输出参数

调用时可以传进去，并且可以返回出来

2.3   存储过程示例

输入参数示例

CREATE PROCEDURE delgrade(IN mygradeid INT)
BEGIN
delete from student where gradeid=mygradeid;
delete from grade where toid=mygradeid;
END

调用:call delgrade(5)

输出参数示例

create PROCEDURE testout(in stuid INT,out sname VARCHAR(20))
BEGIN
select stu.stuname into sname from student as stu where stu.toid=stuid;
END

调用：    

CALL testout(5,@name);
select @name;

3 存储过程语法(声明变量，if和循环)

3.1 声明变量语法：

DECLARE age INT  ;
DECLARE username varchar(20) ;

3.2  if判断的语法：

IF age=100 THEN

SQL语句代码

ELSEIF  age=200 THEN

SQL语句代码

END IF;

3.3 循环LOOP

DROP PROCEDURE IF EXISTS looptest;
CREATE  PROCEDURE looptest()
BEGIN
DECLARE counter int DEFAULT 0;
setcount:LOOP
set counter=counter+1;
IF counter=5 THEN
LEAVE setcount;
END IF;
select counter;
END LOOP setcount;
END;

3.4  循环之while

WHILE counter!= 10 DO
SET counter = counter+1;
END WHILE;

4 .游标

DROP PROCEDURE IF EXISTS cursor_temp;
CREATE PROCEDURE cursor_temp()
BEGIN
DECLARE uname VARCHAR(20);
DECLARE done int;
DECLARE rs_cursor CURSOR
FOR  SELECT au.username from adminuser as au ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
OPEN rs_cursor;
cursor_loop:LOOP
FETCH rs_cursor into  uname;
select uname;
IF done=1 THEN
LEAVE cursor_loop;
END IF;
END LOOP cursor_loop;
END;

5 java调用存储过程

5.1，参数传递

CallableStatement cs=conn.prepareCall("{call delgrade(?)}");
cs.setInt(1,4);
cs.executeUpdate();

5.2   输出参数

CallableStatement cs=conn.prepareCall("{call outtest(?,?)}");
cs.registerOutParameter(1,Types.VARCHAR);
cs.setInt(2,5);
cs.executeUpdate();
String uname=cs.getString(1);
System.out.println(uname);
DROP PROCEDURE IF EXISTS insertProce; create PROCEDURE insertProce(delid INT) BEGIN    INSERT INTO grade  VALUES(7,'0912班','2013-12-5 10:10:53','afei','vv');    DELETE FROM adminuser where toid=delid;     END ; CALL insertProce(4); DROP PROCEDURE IF EXISTS findUname ; CREATE PROCEDURE  findUname(IN userid INT,OUT uname VARCHAR(20)) BEGIN    select au.username INTO uname  from adminuser as  au where au.toid=userid; END ; CALL findUname(2,@myuname); select @myuname; DROP PROCEDURE IF EXISTS insertProce; create PROCEDURE insertProce(delid INT) BEGIN    INSERT INTO grade  VALUES(7,'0912班','2013-12-5 10:10:53','afei','vv');    DELETE FROM adminuser where toid=delid;     END ; CALL insertProce(4); DROP PROCEDURE IF EXISTS findUname ; CREATE PROCEDURE  findUname(IN userid INT,OUT uname VARCHAR(20)) BEGIN    select au.username INTO uname  from adminuser as  au where au.toid=userid; END ; CALL findUname(2,@myuname); select @myuname; DROP PROCEDURE IF EXISTS insertProce; create PROCEDURE insertProce(delid INT) BEGIN    INSERT INTO grade  VALUES(7,'0912班','2013-12-5 10:10:53','afei','vv');    DELETE FROM adminuser where toid=delid;     END ; CALL insertProce(4); DROP PROCEDURE IF EXISTS findUname ; CREATE PROCEDURE  findUname(IN userid INT,OUT uname VARCHAR(20)) BEGIN    select au.username INTO uname  from adminuser as  au where au.toid=userid; END ; CALL findUname(2,@myuname); select @myuname;