MySQL存储过程和触发器

存储过程

1. 传进去参数示例

创建一个p_persons的函数,传进去的参数是myId varchar(255)类型的

delimiter //
CREATE PROCEDURE p_persons ( IN myId VARCHAR ( 255 ) )
BEGIN
	SELECT *  FROM student  WHERE 	sId = myId;
END;
// 

调用,set句尾加上分号

SET @id = '1';
CALL p_persons(@id)

删除DROP PROCEDURE p_persons;

---

2. 传出参数,还有inout类型的

创建一个fun的函数,传进去的参数是sId varchar(255)类型的,传出来的是nums int类型,
使用INTO nums 来给nums赋值

delimiter //
CREATE PROCEDURE fun(in sId VARCHAR(255),out nums int)
BEGIN
	SELECT count(*) INTO nums FROM xk WHERE sId=sId;
END
//

调用

set @sId='1';
SET @result=-1;
CALL fun(@sId,@result);
SELECT @result;

3. 更高级的示例

delimiter //
CREATE PROCEDURE fun(in stuId VARCHAR(255),   in couId int,     in teaId VARCHAR(255),OUT result int)
BEGIN
	# 申明局部变量
	DECLARE nums int;
	DECLARE max int;
	DECLARE temp int DEFAULT -1;
	
	# 给局部变量赋值
	SELECT persons,maxPersons into nums,max  FROM course WHERE courseId=couId;
	
	#SELECT courseId as '存储过程'  FROM xk WHERE sId=stuId AND courseId=couId;
	
	# 判断是否已经选过课了,重复选课返回参数9
	SELECT courseId into temp FROM xk WHERE sId=stuId AND courseId=couId;


	IF temp!=-1 THEN
		# 已经选过了课,返回-1
		SET result=-1;
	ELSEIF nums+1>max THEN
		# 选课超过了最大人数,返回0
		SET result=0;
	ELSE
		# 插入记录,并且人数加1
		INSERT INTO xk(sId,courseId,tId) VALUES(stuId,couId,teaId);
		UPDATE course SET persons=persons+1 WHERE courseId=couId;
		SET result=1;
	END IF;

END;
//
delimiter ;

sql语句调用

SET @sid='1';
set @courseid=2;
set @tid='2';
set @res=0;
CALL fun(@sid,@courseId,@tId,@res);
SELECT @res;

java通过JDBC调用

String driver = "com.mysql.jdbc.Driver";
String url="jdbc:mysql://ip地址:3306/你的数据库名字?useUnicode=true&characterEncoding=UTF-8";
String user="root";
String password="你的密码";
Connection connection = null;

try{
    Class.forName(driver);
    connection = DriverManager.getConnection(url,user,password);
} catch (ClassNotFoundException e) {
    e.printStackTrace();
    System.out.println("驱动jar包没有导入!");
} catch (SQLException e) {
    e.printStackTrace();
    System.out.println("连接失败!");
}

try {
	// 调用上面的存储过程,对应着4个参数,前面3个是输入,后一个是输出
    String sql="{call fun(?,?,?,?)}";
    CallableStatement cs = connection.prepareCall(sql);

    //设置输入参数
    String sid = "1";
    int cid = 1;
    String tid="1";
    cs.setString(1, sid);
    cs.setInt(2, cid);
    cs.setString(3, tid);

    // 设置输出参数,inout类型的两个都要设置
    cs.registerOutParameter(4, Types.INTEGER); // java.sql.Types.INTEGER

    // 执行
    cs.execute();

    //取得结果
    int res = cs.getInt(4);
    System.out.println(res);

} catch (SQLException e) {
    e.printStackTrace();
}

触发器

# 每次插入课程选课时,更新课程的人数加1
delimiter //
CREATE TRIGGER tri_name
BEFORE INSERT
on xk
FOR EACH ROW

BEGIN
	UPDATE course SET persons=1;
END
//

参数解析:
before和after 设置前还是后
on xk 指定哪个数据表
for each row 和 for each statement指定每一行还是每条语句触发一次
mysql不支持语句触发器,所以必须写for each row;

# 每次插入课程选课时,更新课程的人数加1


delimiter //
CREATE TRIGGER tri_fun
after UPDATE
on xk
FOR EACH ROW	

BEGIN
	if new.score=10 THEN
		UPDATE xk set score=100 WHERE sId=old.sId AND courseId=old.courseId;
	end if;
END
//