mysql存储过程 cast_MySql存储过程的操作

【存储过程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】