我编写了这个mysql程序,用于将客户账单详细信息插入电子商务数据库。
如果我在phpmyadmin中运行它,它会为任何分号抛出错误。我删除了分号,但它给出了以下错误
1064 - 您的 SQL 语法有错误;请查看与您的 MySQL 服务器版本对应的手册,了解在"声明addr_id INT UNSIGNED ...在 4 号线
我以前从未使用过过程,但我仔细检查了语法,无法找出错误。
DELIMITER //
CREATE PROCEDURE addCustomer(email VARCHAR(60), status VARCHAR(15), bill_pin_id SMALLINT UNSIGNED, bill_addr VARCHAR(175), name VARCHAR(60), tel VARCHAR(15))
BEGIN
DECLARE em_id INT UNSIGNED;
DECLARE addr_id INT UNSIGNED;
DECLARE cust_id INT UNSIGNED;
DECLARE sql_error TINYINT DEFAULT FALSE;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET sql_error = true;
--check if customer email already exists
SELECT id INTO em_id FROM email_list WHERE email = email;
-- not sure of the return value, haven't tested it
IF em_id = 1 THEN
-- if emails exists check if customer exists
SELECT id INTO cust_id FROM customer WHERE email_list_id = em_id;
IF cust_id = 1 THEN
-- customer must have address information
SELECT address_id INTO addr_id from customer_addr
INNER JOIN address on address_id = address.id
WHERE customer_id = cust_id and address = bill_addr;
END IF
END IF
START TRANSACTION;
IF (em_id = 0) THEN
-- if email doesn't exist, no customer no address - simple insert
INSERT INTO email_list (email, status) VALUES (email, status);
SELECT LAST_INSERT_ID() INTO em_id;
INSERT INTO customer (email_list_id, full_name, phone)
VALUES (em_id, name, tel);
SELECT LAST_INSERT_ID() INTO cust_id;
INSERT INTO address (pincode_id, address) VALUES (bill_pin_id, bill_addr);
SELECT LAST_INSERT_ID() INTO addr_id;
INSERT INTO customer_addr (address_id, customer_id)
VALUES (addr_id, cust_id);
ELSE
UPDATE email_list SET status = status where id = em_id;
UPDATE customer SET full_name = name, phone = tel WHERE id = cust_id;
UPDATE address SET pincode_id = bill_pin_id, address = bill_addr;
END IF
IF sql_error = FALSE THEN
COMMIT;
SELECT 'SUCCESS';
ELSE
ROLLBACK;
SELECT 'FAILED';
END IF
END //
DELIMITER ;
尝试此解决方案,我在某些注释中添加了一些缺少的分号和空格。
CREATE addCustomer(email VARCHAR(60), status VARCHAR(15), bill_pin_id SMALLINT UNSIGNED, bill_addr VARCHAR(175), name VARCHAR(60), tel VARCHAR(15))
BEGIN
DECLARE em_id INT UNSIGNED;
DECLARE addr_id INT UNSIGNED;
DECLARE cust_id INT UNSIGNED;
DECLARE sql_error TINYINT DEFAULT FALSE;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET sql_error = true;
-- check if customer email already exists
SELECT id INTO em_id FROM email_list WHERE email = email;
-- not sure of the return value, haven't tested it
IF em_id = 1 THEN
-- if emails exists check if customer exists
SELECT id INTO cust_id FROM customer WHERE email_list_id = em_id;
IF cust_id = 1 THEN
-- customer must have address information
SELECT address_id INTO addr_id from customer_addr
INNER JOIN address on address_id = address.id
WHERE customer_id = cust_id and address = bill_addr;
END IF;
END IF;
START TRANSACTION;
IF (em_id = 0) THEN
-- if email doesn't exist, no customer no address - simple insert
INSERT INTO email_list (email, status) VALUES (email, status);
SELECT LAST_INSERT_ID() INTO em_id;
INSERT INTO customer (email_list_id, full_name, phone)
VALUES (em_id, name, tel);
SELECT LAST_INSERT_ID() INTO cust_id;
INSERT INTO address (pincode_id, address) VALUES (bill_pin_id, bill_addr);
SELECT LAST_INSERT_ID() INTO addr_id;
INSERT INTO customer_addr (address_id, customer_id)
VALUES (addr_id, cust_id);
ELSE
UPDATE email_list SET status = status where id = em_id;
UPDATE customer SET full_name = name, phone = tel WHERE id = cust_id;
UPDATE address SET pincode_id = bill_pin_id, address = bill_addr;
END IF;
IF sql_error = FALSE THEN
COMMIT;
SELECT 'SUCCESS';
ELSE
ROLLBACK;
SELECT 'FAILED';
END IF;
END
我通过 mysql 控制台执行整个过程来解决这个问题
它接受了它,没有任何错误。然后,我从 phpmyadmin 例程选项卡访问了该过程并导出了该语句。
我能找到的唯一区别是这个
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `addCustomer`(
IN `email` VARCHAR(60),
IN `status` VARCHAR(15),
IN `bill_pin_id` SMALLINT UNSIGNED,
IN `bill_addr` VARCHAR(175),
IN `name` VARCHAR(60),
IN `tel` VARCHAR(15)
)
-- regular procedure statements
END$$
DELIMITER ;
正如多明戈·桑博(Domingo Sambo(指出的那样,我在if语句后没有添加分号,这犯了一些错误。
我删除了该过程并再次对其进行了测试,并通过phpmyadmin查询框添加了它,并且它运行良好。