Mysql存储过程中的可选参数

问题描述：

如何在mysql存储过程中创建可选参数?

How do I create an optional parameter in a mysql stored procedure?

答

我使用的(有些笨拙)解决方法是定义一些我认为可以使用的参数，然后测试一下可选参数的值是否不是使用前为NULL:

The (somewhat clunky) workaround I used was to define a number of parameters that I thought I might use and then test to see if the optional parameter's value is NOT NULL before using it:

CREATE PROCEDURE add_product(product_name VARCHAR(100), product_price FLOAT,
                             cat1 INT, cat2 INT, cat3 INT)
-- The cat? parameters are optional; provide a NULL value if not required
BEGIN

  ...

  -- Add product to relevant categories  
  IF cat1 IS NOT NULL THEN
    INSERT INTO products_to_categories (products_id, categories_id) VALUES (product_id, cat1);
  END IF;
  IF cat2 IS NOT NULL THEN
    INSERT INTO products_to_categories (products_id, categories_id) VALUES (product_id, cat2);
  END IF;
  IF cat3 IS NOT NULL THEN
    INSERT INTO products_to_categories (products_id, categories_id) VALUES (product_id, cat3);
  END IF;

END

如果我不想在调用存储时使用该参数，则只需传递一个NULL值.这是上述存储过程被调用的示例:

If I don't want to use the parameter when calling the stored, I simply pass a NULL value. Here's an example of the above stored procedure being called:

CALL add_product("New product title", 25, 66, 68, NULL);

Mysql存储过程中的可选参数

相关推荐