【存储过程1】
写一个存储过程 procedure实现两数相加
DELIMITER &&
CREATE PROCEDURE pr_add(xx INT,yy INT)
BEGIN
DECLARE zz INT;
SET zz=xx+yy;
SELECT zz AS SUM;
END
&&
DELIMITER ;
MySql调用存储过程 CALL pr_add(3,5);
Java调用存储过程
使用CallableStatement
package com.zhiqi.fir;
import java.sql.*;
public class Fir {
private static String jdbcName="com.mysql.jdbc.Driver";
private static String dbUrl="jdbc:mysql://localhost:3306/Fruit";
private static String dbUser="root";
private static String dbPassword="123456";
public static void main(String[] args) throws Exception {
Class.forName(jdbcName);
Connection conn=DriverManager.getConnection(dbUrl,dbUser,dbPassword);
CallableStatement cast=conn.prepareCall("call pr_add(?,?)");
cast.setInt(1, 5);
cast.setInt(2, 1);
ResultSet rs=cast.executeQuery();
while(rs.next()){
System.out.println(rs.getInt(1));
}
conn.close();
}
}
【存储过程2】
水果表,存储过程查询库存量大于某数值的水果名称
DELIMITER &&
CREATE PROCEDURE pro_Fruit_kcl(IN xx INT)
READS SQL DATA
BEGIN
SELECT NAME FROM Fruit WHERE Numbers>xx;
END
&&
DELIMITER ;
MySql调用存储过程
CALL pro_Fruit_kcl(99);
Java调用MySql调用存储
package com.zhiqi.fir;
import java.sql.*;
public class Fir {
private static String jdbcName="com.mysql.jdbc.Driver";
private static String dbUrl="jdbc:mysql://localhost:3306/Fruit";
private static String dbUser="root";
private static String dbPassword="123456";
public static void main(String[] args) throws Exception {
Class.forName(jdbcName);
Connection conn=DriverManager.getConnection(dbUrl,dbUser,dbPassword);
CallableStatement cast=conn.prepareCall("CALL pro_Fruit_kcl(?)");
cast.setInt(1, 99);
ResultSet rs=cast.executeQuery();
while(rs.next()){
System.out.println(rs.getString(1));
}
conn.close();
}
}
【存储过程3】