在存储过程中调用函数时出错 [英] Getting errors when calling functions inside stored procedure

问题描述

我正在编写一个存储过程来管理公司员工的假期.休假应定期记入每位员工的账户.

I am writing a stored procedure to manage leaves of employees in a company. Leave should be credited to each employee periodically.

我的代码如下

DROP FUNCTION IF EXISTS inserter;

DELIMITER $$;
CREATE FUNCTION inserter(emp_id bigint, lpc_id int) RETURNS boolean DETERMINISTIC
BEGIN
    INSERT INTO aaa_test (aaa_emp_id,aaa_lpc_id) 
    VALUES (emp_id,lpc_id);   

    RETURN 1;
END $$;
DELIMITER ;

DROP PROCEDURE IF EXISTS start_credit_test;

DELIMITER $$;

CREATE PROCEDURE start_credit_test()
BEGIN
    DECLARE v_finished INT(11) DEFAULT 0;
    DECLARE my_lpc_id INT(11) DEFAULT 0;
    DECLARE my_emp_id BIGINT(20) DEFAULT 0;

    DECLARE emp_cursor CURSOR FOR 
        SELECT lpc_id, emp_id  
        FROM erp_leave_policy, erp_employees, erp_clients, erp_employee_leave_policy 
        WHERE emp_status = 1 
          AND cli_status = 1 
          AND lpc_status = 1 
          AND emp_id = elp_fk_employees 
          AND lpc_id = elp_fk_leave_policy 
          AND cli_id = emp_fk_clients;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;

    OPEN emp_cursor;

    get_emp: LOOP

        FETCH emp_cursor INTO my_lpc_id,my_emp_id;

        IF v_finished = 1 THEN 
            LEAVE get_emp;
        END IF;

        call inserter(my_emp_id,my_lpc_id);  

   END LOOP get_emp;

   CLOSE emp_cursor;

END $$;
DELIMITER ; 

我正在使用codeigniter.然后，当我这样做

I am using codeigniter. Then when I do

$this->db->query("CALL start_credit_test();");

我遇到错误:

步骤abn_erp.inserter不存在

PROCEDURE abn_erp.inserter does not exist

过程内的查询

SELECT lpc_id, emp_id  
FROM erp_leave_policy, erp_employees, erp_clients, erp_employee_leave_policy 
WHERE emp_status = 1 
  AND cli_status = 1 
  AND lpc_status = 1 
  AND emp_id = elp_fk_employees 
  AND lpc_id = elp_fk_leave_policy 
  AND cli_id = emp_fk_clients;

返回输出:

单击此处

然后我再次插入功能为

            DELIMITER $$;
            CREATE FUNCTION inserter(emp_id bigint, lpc_id int) RETURNS boolean DETERMINISTIC
            BEGIN

                INSERT INTO aaa_test (aaa_emp_id,aaa_lpc_id) VALUES (emp_id,lpc_id);   

                RETURN 1;
            END $$;
            DELIMITER ;

但是它显示错误#1304-功能插入器已经存在

推荐答案

请使用 inseterLeave 重命名，如下所示.

Please rename it with inseterLeave as shown below.

DELIMITER $$;
CREATE FUNCTION inseterLeave (emp_id bigint, lpc_id int) RETURNS boolean DETERMINISTIC
BEGIN
    INSERT INTO aaa_test (aaa_emp_id,aaa_lpc_id) VALUES (emp_id,lpc_id);   
    RETURN 1;
END $$;
DELIMITER ;

并使用以下代码重写过程.

and rewrite procedure with below code.

您不需要 call 关键字来执行 procedure 中的 call function .我已经根据它进行了更改.

You did not need call keyword to call function from procedure.I had made changes according to it.

       DROP PROCEDURE IF EXISTS start_credit_test;

            DELIMITER $$;
            CREATE PROCEDURE start_credit_test()
                BEGIN
                    DECLARE v_finished INT(11) DEFAULT 0;
                    DECLARE my_lpc_id INT(11) DEFAULT 0;
                    DECLARE my_emp_id BIGINT(20) DEFAULT 0;


                    DEClARE emp_cursor CURSOR FOR 

                    SELECT lpc_id,emp_id  FROM erp_leave_policy,erp_employees,erp_clients,erp_employee_leave_policy 
                            WHERE emp_status=1 AND cli_status=1 AND lpc_status=1 AND 
                            emp_id = elp_fk_employees AND lpc_id = elp_fk_leave_policy AND cli_id = emp_fk_clients;

                    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;

                    OPEN emp_cursor;

                        get_emp: LOOP

                            FETCH emp_cursor INTO my_lpc_id,my_emp_id;

                            IF v_finished = 1 THEN 
                            LEAVE get_emp;
                            END IF;
 SELECT inseterLeave(my_emp_id,my_lpc_id) FROM DUAL; <-- Please remove call keyword from this line.Function did not require call keyword!>


                        END LOOP get_emp;

                    CLOSE emp_cursor;

                    END $$;
                    DELIMITER ; 

我认为 insert 是内置的 function 或 Function ，具有相同的名称已经存在.希望这对您有所帮助

I think inseter is inbuilt functionor Function with same name already exist.Hope this will helps you.

这篇关于在存储过程中调用函数时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！