php - Mysql 存储过程未按预期运行

Question

我正在尝试编写存储的程序来管理公司的休假，因为休假应定期记入每个员工的贷方。我正在使用表格

erp_leave_policy => Describing Leave policy, 
erp_employees    => Describing Employees, 
erp_clients      => Describing Employees are belonged to which Client, 
erp_employee_leave_policy => Describing leave policy of employees.

我在存储过程中使用以下查询

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;

它运行良好，没有任何问题。

但是在我的存储过程中，这个查询不起作用。我正在使用代码点火器。我的代码如下。

$this->db->query("DROP FUNCTION IF EXISTS inserter;");
$q = "  CREATE FUNCTION inserter(emp_id bigint,lpc_id int) RETURNS boolean DETERMINISTIC
            BEGIN

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

             RETURN 1;
            END;";

$this->db->query($q);
$this->db->query("DROP PROCEDURE IF EXISTS start_credit_test;");
$q = " CREATE PROCEDURE start_credit_test()
        BEGIN
            DECLARE v_finished INTEGER DEFAULT 0;
            DECLARE lpc_id INT(11) DEFAULT 0;
            DECLARE 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 lpc_id,emp_id;

                    IF v_finished = 1 THEN 
                    LEAVE get_emp;
                    END IF;

                    inserter(emp_id,lpc_id);

                END LOOP get_emp;

            CLOSE emp_cursor;

           END ";


$this->db->query($q);

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

错误如下

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(emp_id,lpc_id); END LOOP get_emp; CLO' at line 27

CREATE PROCEDURE start_credit_test() BEGIN DECLARE v_finished INTEGER DEFAULT 0; DECLARE lpc_id INT(11) DEFAULT 0; DECLARE 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 lpc_id,emp_id; IF v_finished = 1 THEN LEAVE get_emp; END IF; inserter(emp_id,lpc_id); END LOOP get_emp; CLOSE emp_cursor; END 

我尝试了这段代码，但没有调用函数 inserter()，而是将 inserter(emp_id,lpc_id); 替换为

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

现在它不显示错误。但表 aaa_test 中没有插入任何内容。另外，当在 CREATE PROCEDURE start_credit_test() 之前使用 DELIMITER// 以及当我调用 $this->db->query() 时，它也会显示错误。例如

$q = " DELIMITER //
        CREATE PROCEDURE start_credit_test()
        BEGIN
         .........
        .............";
 $this->db->query($q);

错误如下

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER // CREATE PROCEDURE start_credit_test() BEGI' at line 1

你能帮忙吗

Answer 1

错误可能包含在:DECLARE v_finished INTEGER DEFAULT 0;

更改为:DECLARE v_finished INT(11) DEFAULT 0;