Mysql存储过程中的可选参数 [英] Optional Parameters in Mysql stored procedures
本文介绍了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存储过程中的可选参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文