PL-SQL - Can I pass enum as function argument?

user_5638630 0
Viewed 627

I've a field/column named 'type' with enum datatype in my table, I wish to use similar type of parameter for my PL-SQL function.

Can I pass enum as function argument?

I've tried following function (for mySQL) but it does accept enum as function argument/parameter.

CREATE FUNCTION `save_type` (type('new', 'existing', 'xyz') IN enum)

RETURNS INTEGER
BEGIN

RETURN 1;
END
parameter-passing enums function mysql
2 Answers

Function parmameters need to be of MySQL data types. ENUM is not supported.

Would like to recommend staying away from ENUM altogether. Use separate table for the readable values, so you can change them later if needed without major hassle.

user_10521300

You can use an ENUM type for a function parameter, but you've got the wrong syntax for MySQL.

This works:

CREATE FUNCTION `save_type` (type enum('new', 'existing', 'xyz'))

RETURNS INTEGER
DETERMINISTIC
BEGIN

RETURN 1;
END

Differences:

  • List the enum values after the enum keyword.
  • There is no option for IN versus OUT parameter for MySQL stored functions.
  • Functions should be declared with one of DETERMINISTIC, NO SQL, or READS SQL DATA.
Bill_Karwin_208600
Related