oracle - PLS-00103 : Encountered the symbol "(" when expecting

Question

下面的过程有什么问题？我收到以下错误。

LINE/COL ERROR

---

1/107 PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character The symbol ":=" was substituted for "(" to continue.

1/114 PLS-00103: Encountered the symbol "IS" when expecting one of the following: return

CREATE OR REPLACE FUNCTION MOBLIE_RX_VERIFICATION(ARG_DAD_ID IN   NUMBER,ARG_PATIENT_NAME IN VARCHAR2,ARG_VERIFY_MODE VARCHAR2(15))  AS

DECLARE 

NEW_CCH_ID NUMBER;
CCH_COUNT NUMBER;
CCL_COUNT NUMBER;
vPAT_ID VARCHAR2(20);
vENCNTR_ID NUMBER;
vSTM_ID  NUMBER;
vADMIN_DT DATE;
vGIM_ID NUMBER;
vIDM_ID NUMBER;
vQTY NUMBER;
vUOM_ID NUMBER;
vMEDIUM_IDM NUMBER;
vMEDIUM_QTY NUMBER;
vMEDIUM_UOM NUMBER;
vMEDIUM_GIM NUMBER;
vPPD_ID NUMBER;
vADMIN_TYPE VARCHAR2(50);
vSTATUS VARCHAR2(50);
vDOSE VARCHAR2(15);

BEGIN

SELECT DAD_PATIENT_ID, DAD_ENCOUNTER_ID, DAD_STM_ID, TRUNC(DAD_ADMIN_DATE), DAD_GIM_ID, DAD_IDM_ID, DAD_QUANTITY, DAD_UOM_ID, NVL(DAD_MEDIUM_IDM_ID,0), DAD_MEDIUM_QTY, DAD_MEDIUM_UOM_ID, NVL(DAD_PPD_ID,0), DAD_ADMIN_TYPE,DAD_STATUS, DAD_DOSE_NUM
INTO vPAT_ID,vENCNTR_ID,vSTM_ID,vADMIN_DT,vGIM_ID,vIDM_ID,vQTY,vUOM_ID,vMEDIUM_IDM, vMEDIUM_QTY, vMEDIUM_UOM,vPPD_ID,vADMIN_TYPE, vSTATUS,vDOSE
FROM DRUG_ADMIN_DETAIL
WHERE DAD_ID = ARG_DAD_ID;

SELECT COUNT(*) INTO CCH_COUNT FROM CURRENT_CONSUMPTION_HEADER
WHERE CCH_STM_ID = vSTM_ID AND CCH_CONSUMPTION_TYPE = 'PATIENT' AND TRUNC(CCH_CONSUMPTION_DATE) = vADMIN_DT;



IF ARG_VERIFY_MODE = 'VERIFY' THEN


    UPDATE DRUG_ADMIN_DETAIL SET DAD_STATUS= 'VERIFIED' WHERE DAD_ID= ARG_DAD_ID;

    IF vPPD_ID <> 0 THEN

        UPDATE PATIENT_PRESCRIPTION_DETAIL SET PPD_STATUS= 'VERIFIED' WHERE PPD_ID= vPPD_ID AND PPD_STATUS= 'ADMINISTERED';    

    END IF;

    IF CCH_COUNT = 0 THEN

        INSERT INTO CURRENT_CONSUMPTION_HEADER(CCH_STM_ID, CCH_CONSUMPTION_DATE, CCH_CONSUMPTION_TYPE, CCH_STATUS) 
        VALUES (vSTM_ID, vADMIN_DT, 'PATIENT', 'OUTSTANDING') RETURNING CCH_ID INTO NEW_CCH_ID;

    ELSE

        SELECT CCH_ID INTO NEW_CCH_ID FROM CURRENT_CONSUMPTION_HEADER
        WHERE CCH_STM_ID = vSTM_ID AND CCH_CONSUMPTION_TYPE = 'PATIENT' AND TRUNC(CCH_CONSUMPTION_DATE) = vADMIN_DT;


    END IF;

    INSERT INTO CURRENT_CONSUMPTION_LINE (CCL_CCH_ID, CCL_ADMISSION_NO, CCL_PATIENT_ID, CCL_IDM_ID, UOM_ID,CCL_CONS_CATOGORY, 
    CCL_CONS_FACTOR, CCL_QTY, CCL_PATIENT_NAME, CCL_SHIFT,CCL_STATUS, CCL_DAD_ID) 
    VALUES ( NEW_CCH_ID,vENCOUNTER_ID, vPATIENT_ID,vIDM_ID,VUOM_ID, (SELECT GIM_CONS_CATEGORY FROM GENERIC_ITEM_MASTER WHERE GIM_ID=vGIM_ID),
    (SELECT GIM_CONS_FACTOR FROM GENERIC_ITEM_MASTER WHERE GIM_ID=vGIM_ID),vQTY,ARG_PATIENT_NAME,vDOSE,'OUTSTANDING', ARG_DAD_ID);    

    IF vMEDIUM_QTY > 0 

        SELECT IDM_GIM_ID INTO vMEDIUM_GIM
        FROM ITEM_dETAILS_MASTER
        WHERE  IDM_ID = vMEDIUM_IDM;

        INSERT INTO CURRENT_CONSUMPTION_LINE (CCL_CCH_ID, CCL_ADMISSION_NO, CCL_PATIENT_ID, CCL_IDM_ID, UOM_ID,CCL_CONS_CATOGORY, 
        CCL_CONS_FACTOR, CCL_QTY, CCL_PATIENT_NAME, CCL_SHIFT) 
        VALUES (NEW_CCH_ID,vENCOUNTER_ID, vPATIENT_ID,vMEDIUM_IDM,vMEDIUM_UOM, (SELECT GIM_CONS_CATEGORY FROM GENERIC_ITEM_MASTER WHERE GIM_ID=vMEDIUM_GIM),
        (SELECT GIM_CONS_FACTOR FROM GENERIC_ITEM_MASTER WHERE GIM_ID=vMEDIUM_GIM),vMEDIUM_QTY,ARG_PATIENT_NAME,vDOSE,'OUTSTANDING', ARG_DAD_ID); 

    END IF;


ELSIF ARG_VERIFY_MODE = 'DEVERIFY' THEN

    UPDATE DRUG_ADMIN_DETAIL SET DAD_STATUS= 'ADMINISTERED' WHERE DAD_ID= ARG_DAD_ID;

    IF vPPD_ID <> 0 THEN

        UPDATE PATIENT_PRESCRIPTION_DETAIL SET PPD_STATUS= 'ADMINISTERED' WHERE PPD_ID= vPPD_ID AND PPD_STATUS= 'VERIFIED';    

    END IF;   


    SELECT CCL_CCH_ID INTO NEW_CCH_ID FROM CURRENT_CONSUMPTION_LINE WHERE CCL_DAD_ID = ARG_DAD_ID;

    DELETE CURRENT_CONSUMPTION_LINE
    WHERE CCL_DAD_ID = ARG_DAD_ID;


    DELETE CURRENT_COMSUMPTION_HEADER
    WHERE CCH_ID = NEW_CCH_ID
    AND (SELECT COUNT(*) FROM CURRENT_CONSUMPTION_LINE WHERE CCL_CCH_ID = NEW_CCH_ID) = 0;


END IF; 

COMMIT;
END;

Answer 1

您的程序签名有三个语法错误。

1. 它不返回值，因此它是一个 PROCEDURE 而不是 FUNCTION
2. 您已经指定了第三个参数的长度；这对 PL/SQL 参数无效。
3. DECLARE 关键字仅用于匿名 block 。它不是命名 PL/SQL 单元的有效语法(IS 或 AS 关键字履行其标记变量声明部分的功能)。

所以你的代码应该这样开始

CREATE OR REPLACE procedure MOBLIE_RX_VERIFICATION
   (ARG_DAD_ID IN   NUMBER,
    ARG_PATIENT_NAME IN VARCHAR2,
    ARG_VERIFY_MODE VARCHAR2)  
AS  
    NEW_CCH_ID NUMBER;