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;