Oracle函数转成pgsql函数 有没有可以解决的

Question

CREATE OR REPLACE FUNCTION "QUERYCARSTATUS"(
p_car_id VARCHAR2,--车辆编号
p_dev_id VARCHAR2--设备全国统一编号
)
return NUMBER
IS
n_caryy NUMBER;--
n_cargz NUMBER;
n_carjx NUMBER;
Result number;
BEGIN
--如果车辆状态不是"运营(2)"状态，视为停运状态
SELECT COUNT(1) INTO n_caryy FROM TB_dtb_CAR WHERE VEHICLE_STATE = '2' AND ID=p_car_id;
IF n_caryy = 0 THEN
Result := 4;--停运
ELSE
--如果有未处理的故障记录，视为故障状态
SELECT COUNT(1) INTO n_cargz FROM TB_JX_CLGZJL WHERE CLZT = '0' AND CAR_ID=p_car_id;
IF n_cargz > 0 THEN
Result := 3;--故障
ELSE
--如果没有学员登签未登出记录，视为空闲状态，否则视为教学状态
SELECT COUNT(1) INTO n_carjx FROM TB_JP_STUDENT_LOGIN WHERE LOGOUT_TIME IS NULL AND device_num=p_dev_id;
IF n_carjx = 0 THEN
Result := 2;--空闲
ELSE
Result := 1;--教学
END IF;
END IF;
END IF;
return(Result);
END;

Answer 1

1、你可以一个SQL实现，也可以将下面SQL放到你函数中，替换函数主体内容即可：

    SELECT CASE WHEN NOT EXISTS( SELECT 1 FROM TB_dtb_CAR WHERE VEHICLE_STATE = '2' AND ID = p_car_id ) < 1 THEN 4 -- 停运
                WHEN EXISTS( SELECT 1 FROM TB_JX_CLGZJL WHERE CLZT = '0' AND CAR_ID = p_car_id ) THEN 3 -- 故障
                WHEN NOT EXISTS( SELECT 1 FROM TB_JP_STUDENT_LOGIN WHERE LOGOUT_TIME IS NULL AND device_num = p_dev_id ) THEN 2 -- 空闲
                ELSE 1 END INTO Result ; --教学