mysql - 函数中的 SQL Case 语句

Question

我正在尝试编写一个函数，该函数接受两个参数并根据 case 语句返回计算结果(请参见下文)。我不断收到语法错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE when (medToConvert) = "Codeine" then MME = doseToConver' at line 13

这是我迄今为止尝试过的:

    /* Function that takes two parameters as input:
   Dosage of an opioid
   Name of the opioid

   Returns the morphine equivalent dosage */

    CREATE FUNCTION convertToMorphineEquiv (doseToConvert INT, medToConvert VARCHAR(20))
    RETURNS INT

    BEGIN
        DECLARE MME INT

        CASE       
                 when (medToConvert) = "Codeine" then MME = doseToConvert * 0.15

                 -- Fentanyl Transdermal (in mcg/hr)
                 when (medToConvert) = "Fentanyl" then MME = doseToConvert * 2.4

                 when (medToConvert) = "Hydrocodone" then MME = doseToConvert * 1
                 when (medToConvert) = "Hydromorphone" then MME = doseToConvert * 4
                 when (medToConvert) = "Methadone" AND doseToConvert BETWEEN 1 AND 20 then MME = doseToConvert * 4
                 when (medToConvert) = "Methadone" AND doseToConvert BETWEEN 21 AND 40 then MME = doseToConvert * 8
                 when (medToConvert) = "Methadone" AND doseToConvert BETWEEN 41 AND 60 then MME = doseToConvert * 10
                 when (medToConvert) = "Methadone" AND doseToConvert >=60 then MME = doseToConvert * 12
                 when (medToConvert) = "Morphine" then MME = doseToConvert * 1
                 when (medToConvert) = "Oxycodone" then MME = doseToConvert * 1.5
                 when (medToConvert) = "Oxymorphone" then MME = doseToConvert * 3
                 when (medToConvert) = "Tapentadol" then MME = doseToConvert * 0.4

                 else "Conversion for this opioid is not available"
        END

         RETURN MME
    END

Answer 1

创建一个表，然后加入其中。使用 CROSS APPLY 操作将获得更快的性能，因为标量值的用户定义函数会受到 RBAR(Row By Agonizing Row)性能损失。

CREATE TABLE dbo.MedicineDoseConversion (
  medicine_name varchar(20) not null,
  dose_to_convert_min_units int null,
  dose_to_convert_max_units int null,
  dosage_multiplier decimal(18,10) not null
)
GO

INSERT dbo.MedicineDoseConversion (medicine_name, dose_to_convert_min_units, 
dose_to_convert_max_units, dosage_multiplier)
SELECT 'Codeine', null, null, 0.15 UNION ALL
SELECT 'Fentanyl', null, null, 2.4 UNION ALL
SELECT 'Hydrocodone', null, null, 1 UNION ALL
SELECT 'Hydromorphone', null, null, 4 UNION ALL
SELECT 'Methadone', 1, 20, 4 UNION ALL
SELECT 'Methadone', 21, 40, 8 UNION ALL
SELECT 'Methadone', 41, 60, 10 UNION ALL
SELECT 'Methadone', 60, null, 12 UNION ALL
SELECT 'Morphine', null, null, 1 UNION ALL
SELECT 'Oxycodone', null, null, 1.5 UNION ALL
SELECT 'Oxymorphone', null, null, 3
;
GO