1.新建表
CREATE TABLE test (
`id` int(20) NOT NULL AUTO_INCREMENT ,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
PRIMARY KEY (`id`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=4
ROW_FORMAT=COMPACT
;
2.新建存储过程
1.创建存储过程,指定参数值
drop procedure if exists pr_param_in2out;
create procedure pr_param_in2out
(
in pid int,
out pname varchar(50) CHARSET utf8
)
begin
# 创建存储过程,指定参数的字符集,如果带有中文varchar需要指定 它的字符集为 utf-8,否则 java 取值会报错的
SELECT `name` INTO pname from test where id=pid;
end;
2.创建存储过程,返回多个结果集
drop procedure if exists pr_param_in2out1;
create procedure pr_param_in2out1
(
in pid int,
)
begin
# 可以返回多个结果集
SELECT * from test where id=pid;
SELECT * from test;
end;
3创建存储过程,返回单个结果集
drop procedure if exists pr_param_in2out2;
create procedure pr_param_in2out2
(
in pid int
)
begin
SELECT * from test where id=pid;
end;
4. 创建带有游标的存储过程
drop procedure if exists pro_handledata;
CREATE PROCEDURE `pro_handledata`()
BEGIN
#本存储过程用来根据student表和class表来加工生成stu_info表的数据
DECLARE done INT DEFAULT 0;
#定义stu_info表所需要的变量
DECLARE var_sid INTEGER(10);
DECLARE var_sname VARCHAR(50) CHARSET utf8 DEFAULT '';
DECLARE var_classid INTEGER(10);
DECLARE var_classname VARCHAR(50) CHARSET utf8 DEFAULT '';
#定义游标
DECLARE cur_student CURSOR FOR
SELECT sid,sname,classid from student;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
#循环处理纳税主体的数据
OPEN cur_student;
FETCH cur_student INTO var_sid,var_sname,var_classid;
WHILE done = 0 DO
#获取var_classid所对应的班级名称
SELECT getClassName(var_classid) INTO var_classname;
#插入数据
INSERT INTO stu_info VALUES(var_sid,var_sname,var_classname);
FETCH cur_student INTO var_sid,var_sname,var_classid;
END WHILE;
CLOSE cur_student;
END;
5.创建函数,指定返回值
drop FUNCTION if exists fc_getName;
CREATE FUNCTION `fc_getName`(pid INTEGER)
RETURNS varchar(50)
DETERMINISTIC
BEGIN
#本函数根据传递进来的班级编号,获取它所在班级名称
DECLARE pname VARCHAR(50) CHARSET utf8 DEFAULT '';
SELECT `name` INTO name from test where id=pid;
RETURN pname;
END;
3.存储过程的一个综合示例
1.创建三张表:
1.1.student
CREATE TABLE `student` (
`sid` int(10) NOT NULL DEFAULT 0 ,
`sname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`classid` int(10) NULL DEFAULT NULL ,
PRIMARY KEY (`sid`)
)
插入数据:
INSERT INTO `student` VALUES (1, '小强', 1);
INSERT INTO `student` VALUES (2, '小刚', 2);
INSERT INTO `student` VALUES (3, '小明', 3);
INSERT INTO `student` VALUES (4, '小王', 1);
1.2.class
CREATE TABLE `class` (
`cid` int(10) NOT NULL DEFAULT 0 ,
`cname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`cid`)
)
插入数据:
INSERT INTO `class` VALUES (1, '一班');
INSERT INTO `class` VALUES (2, '二班');
INSERT INTO `class` VALUES (3, '三班');
1.3.stu_info
CREATE TABLE `stu_info` (
`id` int(10) NOT NULL DEFAULT 0 ,
`name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`className` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
PRIMARY KEY (`id`)
)
2.创建根据班级id获取班级名称的函数getClassName
CREATE FUNCTION `getClassName`(classId INTEGER)
RETURNS varchar(50) CHARSET utf8
DETERMINISTIC
BEGIN
#本函数根据传递进来的班级编号,获取它所在班级名称
DECLARE className VARCHAR(50) CHARSET utf8 DEFAULT '';
SELECT `cname` INTO className from class where cid=classId;
RETURN className;
END;
3.创建自动处理数据的存储过程process_data
CREATE PROCEDURE `process_data`()
BEGIN
#本存储过程用来根据student表和class表来加工生成stu_info表的数据
DECLARE done INT DEFAULT 0;
#定义stu_info表所需要的变量
DECLARE var_sid INTEGER(10);
DECLARE var_sname VARCHAR(50) CHARSET utf8 DEFAULT '';
DECLARE var_classid INTEGER(10);
DECLARE var_classname VARCHAR(50) CHARSET utf8 DEFAULT '';
#定义游标
DECLARE cur_student CURSOR FOR
SELECT sid,sname,classid from student;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
#循环处理纳税主体的数据
OPEN cur_student;
FETCH cur_student INTO var_sid,var_sname,var_classid;
WHILE done = 0 DO
#获取var_classid所对应的班级名称
SELECT getClassName(var_classid) INTO var_classname;
#插入数据
INSERT INTO stu_info VALUES(var_sid,var_sname,var_classname);
FETCH cur_student INTO var_sid,var_sname,var_classid;
END WHILE;
CLOSE cur_student;
END;
4.执行存储过程结果:
id name className
1 小强 一班
2 小刚 二班
3 小明 三班
4 小王 一班
5.注意:
在mysql中经常会出现:1366 - Incorrect string value: '\xE4\xB8\xAD\xE6\x96\x87' for column 'cName' at row 1类似的错误,这儿中错误主要是字符集问题导致的,一般都需要在定义变量,或者创建表的时候指定列或者变量的编码格式为utf8.
4.使用java 操作存储过程
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Types;
public class MySqlTester {
public static String user;
public static String password;
public static String url;
public static Connection conn;
public static void main(String[] args) throws Exception {
init_mysql();
// insert();
// update();
delete();
// insertAndGetGeneraKey();
// executeProcedureInAndOut();
// executeProcedureGetMultiResultSet();
// executeProcedureGetResultSet();
// executeFunction1();
// executeFunction2();
}
/**
* 描述:新增
* <pre>
* 举例:
* </pre>
* @throws Exception
*/
public static void insert() throws Exception {
Statement stmt = conn.createStatement();
String insert = "insert into test(name) values('hehe')";
stmt.execute(insert);
String query = "select * from test";
ResultSet result = stmt.executeQuery(query);
while (result.next()) {
System.out.println("id : " + result.getInt("id")
+ "\tname : " + result.getString("name"));
}
}
/**
* 描述:修改
* <pre>
* 举例:
* </pre>
* @throws Exception
*/
public static void update() throws Exception {
Statement stmt = conn.createStatement();
String update = "update test set name='haha' where id=3";
stmt.execute(update);
String query = "select * from test";
ResultSet result = stmt.executeQuery(query);
while (result.next()) {
System.out.println("id : " + result.getInt("id")
+ "\tname : " + result.getString("name"));
}
}
/**
* 描述:删除
* <pre>
* 举例:
* </pre>
* @throws Exception
*/
public static void delete() throws Exception {
Statement stmt = conn.createStatement();
String update = "delete from test where id=3";
stmt.execute(update);
String query = "select * from test";
ResultSet result = stmt.executeQuery(query);
while (result.next()) {
System.out.println("id : " + result.getInt("id")
+ "\tname : " + result.getString("name"));
}
}
/**
* 新增数据并且返回自增的 key 值
*
* @throws Exception
*/
public static void insertAndGetGeneraKey() throws Exception {
String sqlInsert = "insert into test(name) values('test')";
PreparedStatement pstmt = null;
pstmt = conn.prepareStatement(sqlInsert,
Statement.RETURN_GENERATED_KEYS);
pstmt.execute();
ResultSet result = pstmt.getGeneratedKeys();
result.next();
int key = result.getInt(1);
System.out.println("自增之后的 id : " + key);
}
/**
* 描述:jdbc 操作存储过程:根据输入值获取一个返回值
*
* <pre>
* 举例:
* </pre>
*
* @throws Exception
*/
public static void executeProcedureInAndOut() throws Exception {
String sql = "{call pr_param_in2out(?,?)}";
CallableStatement callableStatement = conn.prepareCall(sql);
callableStatement.registerOutParameter(2, Types.VARCHAR);
callableStatement.setInt(1, 1);
callableStatement.execute();
String name = callableStatement.getString(2);
System.out.println(name);
}
/**
* 描述:jdbc 操作存储过程:返回多个返回结果集
*
* <pre>
* 举例:
* </pre>
*
* @throws Exception
*/
public static void executeProcedureGetMultiResultSet() throws Exception {
String sql = "{call pr_param_in2out1(?)}";
CallableStatement callableStatement = conn.prepareCall(sql);
callableStatement.setInt(1, 1);
boolean hadResults = callableStatement.execute();
int i = 0;
while (hadResults) {
System.out.println("result No:----" + (++i));
ResultSet rs = callableStatement.getResultSet();
while (rs != null && rs.next()) {
int id1 = rs.getInt(1);
String name1 = rs.getString(2);
System.out.println(id1 + ":" + name1);
}
hadResults = callableStatement.getMoreResults(); // 检查是否存在更多结果集
}
}
/**
* 描述:jdbc 操作存储过程:返回单个返回结果集
*
* <pre>
* 举例:
* </pre>
*
* @throws Exception
*/
public static void executeProcedureGetResultSet() throws Exception {
String sql = "{call pr_param_in2out2(?)}";
CallableStatement callableStatement = conn.prepareCall(sql);
callableStatement.setInt(1, 1);
callableStatement.execute();
ResultSet rs = callableStatement.getResultSet();
while (rs != null && rs.next()) {
int id1 = rs.getInt(1);
String name1 = rs.getString(2);
System.out.println(id1 + ":" + name1);
}
}
/**
* 描述:jdbc 操作函数1:根据输入值获取一个返回值
*
* <pre>
* 举例:
* </pre>
*
* @throws Exception
*/
public static void executeFunction1() throws Exception {
String sql = "{?=call fc_getName(?)}";
CallableStatement callableStatement = conn.prepareCall(sql);
// 注意这个设置值得时候,第一个 ? 并没有被记入到参数个数中,后面括号里面有多少 ? 就是多少个
callableStatement.registerOutParameter(1, Types.VARCHAR);
callableStatement.setInt(1, 1);
callableStatement.execute();
String name = callableStatement.getString(1);
System.out.println(name);
}
/**
* 描述:jdbc 操作函数2:根据输入值获取一个返回值
*
* <pre>
* 举例:
* </pre>
*
* @throws Exception
*/
public static void executeFunction2() throws Exception {
String sql = "select fc_getName(?)";
CallableStatement callableStatement = conn.prepareCall(sql);
// 注意这个设置值得时候,第一个 ? 并没有被记入到参数个数中,后面括号里面有多少 ? 就是多少个
callableStatement.setInt(1, 1);
callableStatement.execute();
ResultSet rs = callableStatement.getResultSet();
String name = null;
if (rs.next()) {
name = rs.getString(1);
}
System.out.println(name);
}
/**
* 描述:初始化数据库连接
*
* <pre>
* 举例:
* </pre>
*
* @return
*/
public static Connection init_mysql() {
try {
// 初始化url,user,password
// url参数:"jdbc:数据库类型:主机IP/数据库名?用户名=&密码=&characterEncoding="
url = "jdbc:mysql://localhost/test1?characterEncoding=utf-8";
user = "root";
password = "123456";
// 加载驱动
Class.forName("org.gjt.mm.mysql.Driver");
// 建立连接
conn = DriverManager.getConnection(url, user, password);
} catch (Exception e) {
System.out.println("数据库连接异常!");
e.printStackTrace();
}
return conn;
}
}