客户端调用mysql存储过程

DELIMITER //

CREATE PROCEDURE get_user_by_id(IN user_id INT)
BEGIN
    SELECT * FROM users WHERE id = user_id;
END //

DELIMITER ;

import java.sql.*;

public class CallStoredProcedure {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydatabase";
        String user = "username";
        String password = "password";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            CallableStatement cstmt = conn.prepareCall("{call get_user_by_id(?)}");
            cstmt.setInt(1, 123); // 设置参数
            ResultSet rs = cstmt.executeQuery();

            while (rs.next()) {
                System.out.println("User ID: " + rs.getInt("id"));
                System.out.println("User Name: " + rs.getString("name"));
                // 输出其他字段
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

import mysql.connector

def call_stored_procedure(user_id):
    try:
        conn = mysql.connector.connect(
            host="localhost",
            user="username",
            password="password",
            database="mydatabase"
        )
        cursor = conn.cursor()
        cursor.callproc('get_user_by_id', [user_id])
        for result in cursor.stored_results():
            rows = result.fetchall()
            for row in rows:
                print(f"User ID: {row[0]}, User Name: {row[1]}")
                # 输出其他字段
    except mysql.connector.Error as err:
        print(f"Error: {err}")
    finally:
        if conn.is_connected():
            cursor.close()
            conn.close()

call_stored_procedure(123)