请求编写验证用户账户特定金额的SQL函数（含账户与支付历史表）

Hey there! As someone self-learning programming, I get how these database function problems can feel tricky at first. Let's walk through building this solution step by step based on your table structure.

First, let's clarify the core requirement: we need a function that takes a user identifier (either id or username—we'll cover both options) and a target amount, then checks if the user's current account balance meets or exceeds that amount.

Quick Context #

The accounts table holds the current, up-to-date balance for each user, so that's our primary source of truth here. The payment_history tracks past balance changes, but we don't need it for a real-time balance check unless your assignment specifically requires validating historical balances (we'll touch on that bonus case too).

Example 1: PostgreSQL Function #

PostgreSQL uses PL/pgSQL for function logic, which is perfect for this task. Here's a version that checks balance by username (easy to adjust for user ID):

CREATE OR REPLACE FUNCTION verify_user_balance(
    p_username VARCHAR,
    p_target_amount NUMERIC(9,2)
) RETURNS BOOLEAN AS $$
BEGIN
    -- Reject invalid negative target amounts (doesn't make sense for currency checks)
    IF p_target_amount < 0 THEN
        RETURN FALSE;
    END IF;

    -- Check if the user exists and has enough balance
    RETURN EXISTS (
        SELECT 1
        FROM accounts
        WHERE username = p_username
        AND balance >= p_target_amount
    );
END;
$$ LANGUAGE plpgsql;

How to Use It #

Test if user "jane_smith" has at least $75.50 with this call:

SELECT verify_user_balance('jane_smith', 75.50);

Switch to User ID Instead of Username #

Just tweak the parameter and WHERE clause:

CREATE OR REPLACE FUNCTION verify_user_balance(
    p_user_id INT,
    p_target_amount NUMERIC(9,2)
) RETURNS BOOLEAN AS $$
BEGIN
    IF p_target_amount < 0 THEN
        RETURN FALSE;
    END IF;

    RETURN EXISTS (
        SELECT 1
        FROM accounts
        WHERE id = p_user_id
        AND balance >= p_target_amount
    );
END;
$$ LANGUAGE plpgsql;

Example 2: MySQL Function #

MySQL uses a slightly different syntax for stored functions. Here's the equivalent version:

DELIMITER //
CREATE FUNCTION verify_user_balance(
    p_username VARCHAR(255),
    p_target_amount DECIMAL(9,2)
) RETURNS BOOLEAN
DETERMINISTIC
BEGIN
    DECLARE has_enough BOOLEAN DEFAULT FALSE;

    -- Reject invalid negative amounts
    IF p_target_amount < 0 THEN
        RETURN FALSE;
    END IF;

    -- Check balance and store result
    SELECT EXISTS (
        SELECT 1
        FROM accounts
        WHERE username = p_username
        AND balance >= p_target_amount
    ) INTO has_enough;

    RETURN has_enough;
END //
DELIMITER ;

Using the MySQL Function #

SELECT verify_user_balance('jane_smith', 75.50);

Bonus: Check Historical Balance (If Required) #

If your assignment needs to verify if a user ever had the target amount in their history (using payment_history), here's a PostgreSQL variation:

CREATE OR REPLACE FUNCTION verify_historical_balance(
    p_user_id INT,
    p_target_amount NUMERIC(9,2)
) RETURNS BOOLEAN AS $$
BEGIN
    IF p_target_amount < 0 THEN
        RETURN FALSE;
    END IF;

    -- Note: "user" is a reserved SQL keyword, so we wrap it in double quotes
    RETURN EXISTS (
        SELECT 1
        FROM payment_history
        WHERE "user" = p_user_id
        AND (oldbalance >= p_target_amount OR newbalance >= p_target_amount)
    );
END;
$$ LANGUAGE plpgsql;

Key Edge Cases to Keep in Mind #

• Non-existent users: The function returns FALSE if the user isn't in the accounts table—this is a safe, logical default.
• Negative target amounts: We explicitly return FALSE here because checking for a negative "required" amount doesn't make practical sense for currency.
• Precision: Using NUMERIC(9,2)/DECIMAL(9,2) ensures we avoid floating-point errors that can mess up currency calculations.

内容的提问来源于stack exchange，提问作者Dirty skull