Name
PRG-06: Limit functions to a single RETURN statement in the executable section
Synopsis
A good general rule to follow as you write your stored programs is: “one way in and one way out.” In other words, there should be just one way to enter or call a program (there is; you don’t have any choice in this matter). And there should be one way out, one exit path from a program (or loop) on successful termination. By following this rule, you end up with code that is much easier to trace, debug, and maintain.
For a function, this means you should think of the executable section as a funnel; all the lines of code narrow down to the last executable statement:
RETURN return value
;
Example
Here’s a simple function that relies on multiple RETURN
s:
CREATE FUNCTION status_desc (in_cd CHAR(1)) RETURNS VARCHAR(20) DETERMINISTIC BEGIN IF in_cd = 'C' THEN RETURN 'CLOSED'; ELSEIF in_cd = 'O' THEN RETURN 'OPEN'; ELSEIF in_cd = 'I' THEN RETURN 'INACTIVE'; END IF; END;
At first glance, this function looks very reasonable. Yet
this function has a deep flaw, due to the reliance upon separate
RETURN
s: if you don’t pass in
"C"
, "O"
, or "I"
for the cd_in
argument, the function
raises:
mysql> SELECT status_desc('A'); ERROR 1321 (2F005): FUNCTION status_desc ended without RETURN
Here’s a rewrite that relies upon a single RETURN
at the end of the
function:
CREATE FUNCTION status_desc (in_cd CHAR(1)) RETURNS VARCHAR(20) DETERMINISTIC BEGIN DECLARE v_status VARCHAR(20) ; IF in_cd = 'C' THEN SET v_status='CLOSED'; ...
Get MySQL Stored Procedure Programming now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.