mysql数据库怎么存储过程_Mysql数据库存储过程

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;