在Navicat for MySQL里用存储过程和存储函数实现两数相加

本文详细介绍了如何在Navicat for MySQL中使用存储过程和存储函数实现两数相加，包括创建过程和函数的SQL语句，以及对应的Java调用示例。展示了如何通过CallableStatement在Java中成功执行并获取结果。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在Navicat for MySQL里用存储过程和存储函数实现两数相加

在工具栏打开查询=>新建查询=>编写sql语句：

存储过程

sql语句如下：其中school为数据库名

 use school;

delimiter $
CREATE PROCEDURE addTwoNum ( in num1 int, in num2 int, out result int)
BEGIN
	set result = num1+num2;
END;
$
delimiter;

Java代码如下（调用）

public class JDBCPCallableStatement {
	private static final String URL = "jdbc:mysql://localhost:3306/Scholl?characterEncoding=UTF-8";
	private static final String USERNAME = "root" ;
	private static final String PWD = "123456" ;
	
	public static void invokeProdure(){
		Connection connection = null;
		CallableStatement cstmt = null;
		try{
			Class.forName("com.mysql.jdbc.Driver");
		
			connection = DriverManager.getConnection(URL,USERNAME,PWD);
		
			cstmt = connection.prepareCall("{call addTwoNum(?,?,?)}");
			cstmt.setInt(1, 20);
			cstmt.setInt(2, 20);
			
			cstmt.registerOutParameter(3, Types.INTEGER);
			cstmt.execute();
		
			
			int result = cstmt.getInt(3);
			System.out.println(result);
			
			
		}catch(ClassNotFoundException e){
			e.printStackTrace();
		}catch(SQLException e){
			e.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(cstmt != null) cstmt.close();
				if(connection != null) connection.close();
			}catch(SQLException e){
				e.printStackTrace();
			}			
		}		
	}
	
	public static void main(String[] args) {
		invokeProdure();
	}
	
}

结果如下：

在这里插入图片描述
存储函数

sql语句如下：其中school为数据库名

 use scholl;

delimiter $
CREATE FUNCTION addTwoNumfunction (num1 int,num2 int) RETURNS int

BEGIN
	DECLARE result int;
	set result = num1+num2;
	return result;
END;
$
delimiter;

Java代码如下（调用）

 public class JDBCPCallableStatement {
	private static final String URL = "jdbc:mysql://localhost:3306/Scholl?characterEncoding=UTF-8";
	private static final String USERNAME = "root" ;
	private static final String PWD = "123456" ;
	
	public static void invokeFunction(){
		Connection connection = null;
		CallableStatement cstmt = null;
		try{
			Class.forName("com.mysql.jdbc.Driver");
		
			connection = DriverManager.getConnection(URL,USERNAME,PWD);
		
			cstmt = connection.prepareCall("{? = call addTwoNumfunction(?,?)}");
			cstmt.setInt(2, 30);
			cstmt.setInt(3, 40);
			
			cstmt.registerOutParameter(1, Types.INTEGER);
			cstmt.execute();
		
			
			int result = cstmt.getInt(1);
			System.out.println(result);
			
			
		}catch(ClassNotFoundException e){
			e.printStackTrace();
		}catch(SQLException e){
			e.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}
		finally{
			try{
				if(cstmt != null) cstmt.close();
				if(connection != null) connection.close();
			}catch(SQLException e){
				e.printStackTrace();
			}			
		}		
	}
		
	public static void main(String[] args) {
		invokeFunction();
	}
	
}