IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF
IF implements a basic conditional
construct. If the search_condition
evaluates to true, the corresponding SQL statement list is
executed. If no search_condition
matches, the statement list in the ELSE
clause is executed. Each
statement_list consists of one or
more statements.
DROP FUNCTION hello; delimiter $ CREATE FUNCTION hello (s VARCHAR(20)) RETURNS VARCHAR(50) BEGIN IF s = 'test' THEN RETURN 'HAHA'; END IF; RETURN 'HOHO'; END $ delimiter ;
CREATE DEFINER=`root`@`localhost` PROCEDURE `updateItem`( IN _item_id INT, IN _cat_id SMALLINT, IN _code VARCHAR(8), IN _name VARCHAR(60), IN _price DECIMAL(10,0) ) BEGIN # if item already use then only price is allowed to change. DECLARE _item_in_used INT; SELECT COUNT(*) INTO _item_in_used FROM order_detail WHERE item_id = _item_id;
/* do not update name, UI should protected changing name but we protected it again in the proc ... */ IF _item_in_used >0 THEN UPDATE items SET cat_id = _cat_id, code = _code, price = _price WHERE item_id = _item_id; ELSE UPDATE items SET cat_id = _cat_id, code = _code, name = _name, price = _price WHERE item_id = _item_id; END IF; END
User Comments
-- EXAMPLE:
DROP FUNCTION hello;
delimiter $
CREATE FUNCTION hello (s VARCHAR(20)) RETURNS VARCHAR(50)
BEGIN
IF s = 'test' THEN RETURN 'HAHA';
END IF;
RETURN 'HOHO';
END $
delimiter ;
-- TEST RUN:
select hello('test'), hello('temp');
CREATE DEFINER=`root`@`localhost` PROCEDURE `updateItem`(
IN _item_id INT,
IN _cat_id SMALLINT,
IN _code VARCHAR(8),
IN _name VARCHAR(60),
IN _price DECIMAL(10,0)
)
BEGIN
# if item already use then only price is allowed to change.
DECLARE _item_in_used INT;
SELECT COUNT(*) INTO _item_in_used FROM order_detail WHERE item_id = _item_id;
/* do not update name,
UI should protected changing name
but we protected it again in the proc ...
*/
IF _item_in_used >0 THEN
UPDATE items SET cat_id = _cat_id, code = _code, price = _price WHERE item_id = _item_id;
ELSE
UPDATE items SET cat_id = _cat_id, code = _code, name = _name, price = _price WHERE item_id = _item_id;
END IF;
END
Add your own comment.