创建MySQL函数会产生一个错误

Question

这是运行此脚本后的错误: MySQL说：

#1064 -您的SQL语法出现了错误；请检查与您的MySQL服务器版本对应的手册，以获得在第3行“@adjustment float(50); IF (@3 = 1) THEN SELECT SUM(exchange_rate *”附近使用的正确语法。

这是一个脚本：

CREATE OR REPLACE FUNCTION getAdjustment(@3 int, @2 int, @1 int)   RETURNS float(50)
AS
BEGIN

    DECLARE @adjustment float(50);


    IF (@3 = 1) THEN
        SELECT SUM(exchange_rate * amount) INTO adjustment
        FROM employee_adjustments
        WHERE (Employee_Month_ID = @1) AND (adjustment_type = @2);
    ELSIF (@3 = 2) THEN
        SELECT SUM(exchange_rate * amount) INTO adjustment
        FROM employee_adjustments
        WHERE (Employee_Month_ID = @1) AND (adjustment_type = @2) AND (In_payroll = true) AND (Visible = true);
    ELSIF (@3 = 3) THEN
        SELECT SUM(exchange_rate * amount) INTO adjustment
        FROM employee_adjustments
        WHERE (Employee_Month_ID = @1) AND (adjustment_type = @2) AND (In_Tax = true);
    ELSIF (@3 = 4) THEN
        SELECT SUM(exchange_rate * amount) INTO adjustment
        FROM employee_adjustments
        WHERE (Employee_Month_ID = @1) AND (adjustment_type = @2) AND (In_payroll = true);
    ELSIF (@3 = 5) THEN
        SELECT SUM(exchange_rate * amount) INTO adjustment
        FROM employee_adjustments
        WHERE (Employee_Month_ID = @1) AND (adjustment_type = @2) AND (Visible = true);
    ELSIF (@3 = 11) THEN
        SELECT SUM(exchange_rate * (amount + additional)) INTO adjustment
        FROM employee_tax_types
        WHERE (Employee_Month_ID = @1);
    ELSIF (@3 = 12) THEN
        SELECT SUM(exchange_rate * (amount + additional)) INTO adjustment
        FROM employee_tax_types
        WHERE (Employee_Month_ID = @1) AND (In_Tax = true);
    ELSIF (@3 = 14) THEN
        SELECT SUM(exchange_rate * (amount + additional)) INTO adjustment
        FROM employee_tax_types
        WHERE (Employee_Month_ID = @1) AND (Tax_Type_ID = @2);
    ELSIF (@3 = 21) THEN
        SELECT SUM(exchange_rate * amount * adjustment_factor) INTO adjustment
        FROM employee_adjustments
        WHERE (employee_month_id = @1) AND (in_tax = true);
    ELSIF (@3 = 22) THEN
        SELECT SUM(exchange_rate * amount * adjustment_factor) INTO adjustment
        FROM employee_adjustments
        WHERE (Employee_Month_ID = @1) AND (In_payroll = true) AND (Visible = true);
    ELSIF (@3 = 23) THEN
        SELECT SUM(exchange_rate * amount * adjustment_factor) INTO adjustment
        FROM employee_adjustments
        WHERE (employee_month_id = @1) AND (in_tax = true) AND (adjustment_factor = 1);
    ELSIF (@3 = 24) THEN
        SELECT SUM(exchange_rate * tax_reduction_amount) INTO adjustment
        FROM employee_adjustments
        WHERE (employee_month_id = @1) AND (in_tax = true) AND (adjustment_factor = -1);
    ELSIF (@3 = 25) THEN
        SELECT SUM(exchange_rate * tax_relief_amount) INTO adjustment
        FROM employee_adjustments
        WHERE (employee_month_id = @1) AND (in_tax = true) AND (adjustment_factor = -1);
    ELSIF (@3 = 26) THEN
        SELECT SUM(exchange_rate * amount) INTO adjustment
        FROM employee_adjustments
        WHERE (employee_month_id = $1) AND (pension_id is not null) AND (adjustment_type = 2);
    ELSIF (@3 = 27) THEN
        SELECT SUM(employee_adjustments.exchange_rate * employee_adjustments.amount) INTO adjustment
        FROM employee_adjustments INNER JOIN adjustments ON employee_adjustments.adjustment_id = adjustments.adjustment_id
        WHERE (employee_adjustments.employee_month_id = @1) AND (adjustments.adjustment_effect_id = @2);
    ELSIF (@3 = 28) THEN
        SELECT SUM(employee_adjustments.exchange_rate * employee_adjustments.tax_relief_amount) INTO adjustment
        FROM employee_adjustments INNER JOIN adjustments ON employee_adjustments.adjustment_id = adjustments.adjustment_id
        WHERE (employee_adjustments.employee_month_id = @1) AND (adjustments.adjustment_effect_id = @2);
    ELSIF (@3 = 31) THEN
        SELECT SUM(overtime * overtime_rate) INTO adjustment
        FROM employee_overtime
        WHERE (Employee_Month_ID = @1) AND (approve_status = 'Approved');
    ELSIF (@3 = 32) THEN
        SELECT SUM(exchange_rate * tax_amount) INTO adjustment
        FROM employee_per_diem
        WHERE (Employee_Month_ID = @1) AND (approve_status = 'Approved');
    ELSIF (@3 = 33) THEN
        SELECT SUM(exchange_rate * (full_amount -  cash_paid)) INTO adjustment
        FROM Employee_Per_Diem
        WHERE (Employee_Month_ID = @1) AND (approve_status = 'Approved');
    ELSIF (@3 = 34) THEN
        SELECT SUM(exchange_rate * amount) INTO adjustment
        FROM employee_advances
        WHERE (Employee_Month_ID = @1) AND (in_payroll = true);
    ELSIF (@3 = 35) THEN
        SELECT SUM(exchange_rate * amount) INTO adjustment
        FROM advance_deductions
        WHERE (Employee_Month_ID = @1) AND (In_payroll = true);
    ELSIF (@3 = 36) THEN
        SELECT SUM(exchange_rate * paid_amount) INTO adjustment
        FROM employee_adjustments
        WHERE (Employee_Month_ID = @1) AND (In_payroll = true) AND (Visible = true);
    ELSIF (@3 = 37) THEN
        SELECT SUM(exchange_rate * tax_relief_amount) INTO adjustment
        FROM employee_adjustments
        WHERE (Employee_Month_ID = @1);

        IF(adjustment IS NULL)THEN
            adjustment := 0;
        END IF;
    ELSIF (@3 = 41) THEN
        SELECT SUM(exchange_rate * amount) INTO adjustment
        FROM employee_banking
        WHERE (employee_month_id = @1);
    ELSE SET
        adjustment := 0
    END IF;

    IF(adjustment is null) THEN
        SET adjustment := 0;
    END IF;

    RETURN adjustment;
END;

Answer 1

用户定义的变量不能用作函数或过程定义中的标识符。使用普通变量并像这样初始化UDV：

CREATE OR REPLACE FUNCTION getAdjustment(param3 int, param2 int, param1 int)   
RETURNS float(50)
BEGIN
DECLARE @adjustment float(50);
SET @3 = param3, @2 = param2, @1 = param1;
. . . .