mysql - 过程调用中的 in 和 out 变量

Question

我正在使用存储过程调用，如下所示:

    DELIMITER // 
CREATE procedure getCustomer(NID varchar(200),Name varchar(200), OUT Flag INTEGER, OUT CID VARCHAR(200))  
BEGIN 
DECLARE id varchar(200); 
SET Flag = 0;
SET id = CONCAT(NID, '_' ,  Name); 
SELECT 1 INTO Flag FROM Customer WHERE customerID = id;
IF Flag = 1 THEN  
SET CID = id; 
ELSE
INSERT INTO Customer(NID, Name, customerID) VALUES(NID, Name, id); 
SET CID = id; 
END IF; 
END//

你能告诉我如何在测试这个程序调用时调用IN、OUT变量吗？或者只是如何使用 exec proceudre_name(parameter) 格式测试此过程调用？

Answer 1

如 CALL Syntax 下所述:

To get back a value from a procedure using an OUT or INOUT parameter, pass the parameter by means of a user variable, and then check the value of the variable after the procedure returns. (If you are calling the procedure from within another stored procedure or function, you can also pass a routine parameter or local routine variable as an IN or INOUT parameter.) For an INOUT parameter, initialize its value before passing it to the procedure. The following procedure has an OUT parameter that the procedure sets to the current server version, and an INOUT value that the procedure increments by one from its current value:

CREATE PROCEDURE p (OUT ver_param VARCHAR(25), INOUT incr_param INT)
BEGIN
  # Set value of OUT parameter
  SELECT VERSION() INTO ver_param;
  # Increment value of INOUT parameter
  SET incr_param = incr_param + 1;
END;

Before calling the procedure, initialize the variable to be passed as the INOUT parameter. After calling the procedure, the values of the two variables will have been set or modified:

mysql> <strong>SET @increment = 10;</strong>
mysql> <strong>CALL p(@version, @increment);</strong>
mysql> <strong>SELECT @version, @increment;</strong>+--------------+------------+| @version     | @increment |+--------------+------------+| 5.5.3-m3-log |         11 |+--------------+------------+

但是，从您的过程来看，您真正想要的是对 Customer 表中的 customerID 列定义唯一性约束，然后使用 INSERT ... ON DUPLICATE KEY UPDATE :

ALTER TABLE Customer ADD UNIQUE (customerID);

INSERT INTO Customer
  (NID, Name, customerID)
VALUES
  (123, 'foobar', CONCAT(123, '_', 'foobar'))
ON DUPLICATE KEY UPDATE
  NID = NID
;