Oracle存储过程创建时提示: PLS-00103:Encountered the symbol "IS" when expecting one of the following : :=(@%;

非著名魔兽解说 发布于 2016/09/20 14:01
阅读 2K+
收藏 0

@IdleMan 你好,想跟你请教个问题:

Oracle存储过程创建时提示:
PLS-00103:Encountered the symbol "IS" when expecting one of the following : :=(@%;
但是始终没有看到是何处缺少什么符号,现在代码中一共有5处"IS",请各位帮助看看是哪里不对,感谢!
难道是第50行游标这部分不对?
存储过程代码如下:

CREATE OR REPLACE
PROCEDURE "SJJ_ORDER_QUERY"  (

			V_JSON_STR IN  CLOB,
			V_OUT_JSON OUT  CLOB,					
			V_OU_CUR OUT sys_refcursor
)
 IS

V_NUM        NUMBER;--根据ORDER_NO查询EOS_ORDER表关联EOS_ORDER_ITEM表的记录数
V_PAGE_NO    VARCHAR(100);
V_FUN_ID     VARCHAR(100);
V_ORDER_NO   VARCHAR(100);
V_OUT_RETURN VARCHAR(100);
V_OU_MSG VARCHAR(1000);
V_TEMP V_OU_CUR%ROWTYPE; 
BEGIN
		select JSON_VALUE(V_JSON_STR, '$.V_PAGE_NO') INTO V_PAGE_NO from dual;--从输入参数V_JSON_STR中读取V_PAGE_NO
		select JSON_VALUE(V_JSON_STR, '$.V_FUN_ID') INTO V_FUN_ID from dual;--从输入参数V_JSON_STR中读取V_FUN_ID
		select JSON_VALUE(V_JSON_STR, '$.V_ORDER_NO') INTO V_ORDER_NO from dual;--从输入参数V_JSON_STR中读取V_ORDER_NO
		
		IF V_PAGE_NO ='' OR V_PAGE_NO IS NULL OR V_PAGE_NO !='003' 
		OR V_FUN_ID ='' OR V_FUN_ID IS NULL OR V_FUN_ID !='select'
		OR V_ORDER_NO ='' OR V_ORDER_NO IS NULL
		THEN
		dbms_lob.createtemporary(V_OUT_JSON, true);--建立临时LOB
		V_OUT_JSON := '{"SATUS":"ERROR","MESSAGE":"V_PAGE_N不是003或者V_FUN_ID不是select或者V_ORDER_NO是空"}';
		RETURN;
		
		ELSE 
		dbms_lob.createtemporary(V_OUT_JSON, true);--建立临时LOB
		SELECT COUNT(1) INTO V_NUM   FROM  V_EOS_ORDER A
				LEFT JOIN V_EOS_ORDER_ITEM B on A.ORDER_NO = B.ORDER_NO
				WHERE A.ORDER_NO = V_ORDER_NO;
				V_OUT_JSON := '{"SATUS":"OK","MESSAGE":"成功"';	
				dbms_lob.append(V_OUT_JSON, ',');
				
				dbms_lob.append(V_OUT_JSON, '"total":');
				dbms_lob.append(V_OUT_JSON, to_char(V_NUM));--根据V_ORDER_NO查询出的记录数					
				dbms_lob.append(V_OUT_JSON, ','); 
				
				dbms_lob.append(V_OUT_JSON, '
					"footer": null,
					"columns": null,
					"frozenColumns": null,
					"AttachRemark": null,
					"Attach": null}'); 
				
				V_OU_CUR IS SELECT  
								A.ORDER_NO,--EOS_ORDER表表订单号
								A.MEMBER_NAME,
								A.CONSIGNEE,
								A.CONSIGNEE_MOBILE,
								A.SHOP_NAME,
								A.CONSIGNEE_AREA,
								A.CONSIGNEE_ADDR,
								A.GOODS_AMOUNT,
								A.ORDER_CREATE_TIME,
								A.BRANCH_NAME,
								A.PMT_AMOUNT,
								B.NAME,
								B.BARCODE,
								B.NUMS
								FROM  V_EOS_ORDER A LEFT JOIN V_EOS_ORDER_ITEM B on A.ORDER_NO = B.ORDER_NO
								WHERE A.ORDER_NO = V_ORDER_NO;
				OPEN V_OU_CUR;
				LOOP
				FETCH V_OU_CUR INTO V_TEMP;
				END LOOP;
				CLOSE V_OU_CUR;
				
		RETURN;
		END IF;
		
	EXCEPTION
    WHEN OTHERS THEN
    V_OUT_RETURN := 'ERROR';
    V_OU_MSG    := '失败,原因是:' || SQLERRM;
    ROLLBACK;
    INSERT INTO SYS_ERROR_LOG
      (ROW_ID, PR_NAME, ERROR_DESC, INSDT)
    VALUES
      (SYS_ERROR_LOG_SEQ.NEXTVAL, 'SJJ_ORDER_QUERY', V_OU_MSG, SYSDATE);
    COMMIT;
END SJJ_ORDER_QUERY;


修改为就可以了,关键代码OPEN V_OU_CUR FOR  SELECT ,如下

CREATE OR REPLACE
PROCEDURE "SJJ_ORDER_QUERY"  (

			V_JSON_STR IN  CLOB,
			V_OUT_JSON OUT  CLOB,
			--V_OU_TABLE OUT CLOB 						
			V_OU_CUR OUT sys_refcursor
)
 IS

V_NUM        NUMBER;--根据ORDER_NO查询EOS_ORDER表关联EOS_ORDER_ITEM表的记录数
V_PAGE_NO    VARCHAR(100);
V_FUN_ID     VARCHAR(100);
V_ORDER_NO   VARCHAR(100);
V_OUT_RETURN VARCHAR(100);
V_OU_MSG VARCHAR(1000);
--V_TEMP V_OU_CUR%ROWTYPE; 
BEGIN

		V_OUT_RETURN := '';
		select JSON_VALUE(V_JSON_STR, '$.V_PAGE_NO') INTO V_PAGE_NO from dual;--从输入参数V_JSON_STR中读取V_PAGE_NO
		select JSON_VALUE(V_JSON_STR, '$.V_FUN_ID') INTO V_FUN_ID from dual;--从输入参数V_JSON_STR中读取V_FUN_ID
		select JSON_VALUE(V_JSON_STR, '$.V_ORDER_NO') INTO V_ORDER_NO from dual;--从输入参数V_JSON_STR中读取V_ORDER_NO
		
		IF V_PAGE_NO ='' OR V_PAGE_NO IS NULL OR V_PAGE_NO !='003' 
		OR V_FUN_ID ='' OR V_FUN_ID IS NULL OR V_FUN_ID !='select'
		OR V_ORDER_NO ='' OR V_ORDER_NO IS NULL
		THEN
		dbms_lob.createtemporary(V_OUT_JSON, true);--建立临时LOB
		V_OUT_JSON := '{"SATUS":"ERROR","MESSAGE":"V_PAGE_N不是003或者V_FUN_ID不是select或者V_ORDER_NO是空"}';
		RETURN;
		
		ELSE 
		dbms_lob.createtemporary(V_OUT_JSON, true);--建立临时LOB
		SELECT COUNT(1) INTO V_NUM   FROM  V_EOS_ORDER A
				LEFT JOIN V_EOS_ORDER_ITEM B on A.ORDER_NO = B.ORDER_NO
				WHERE A.ORDER_NO = V_ORDER_NO;
				V_OUT_JSON := '{"SATUS":"OK","MESSAGE":"成功"';	
				dbms_lob.append(V_OUT_JSON, ',');
				
				dbms_lob.append(V_OUT_JSON, '"total":');
				dbms_lob.append(V_OUT_JSON, to_char(V_NUM));--根据V_ORDER_NO查询出的记录数					
				dbms_lob.append(V_OUT_JSON, ','); 
				
				dbms_lob.append(V_OUT_JSON, '
					"footer": null,
					"columns": null,
					"frozenColumns": null,
					"AttachRemark": null,
					"Attach": null}'); 
				
				OPEN V_OU_CUR FOR  SELECT  
								A.ORDER_NO,--EOS_ORDER表表订单号
								A.MEMBER_NAME,
								A.CONSIGNEE,
								A.CONSIGNEE_MOBILE,
								A.SHOP_NAME,
								A.CONSIGNEE_AREA,
								A.CONSIGNEE_ADDR,
								A.GOODS_AMOUNT,
								A.ORDER_CREATE_TIME,
								A.BRANCH_NAME,
								A.PMT_AMOUNT,
								B.NAME,
								B.BARCODE,
								B.NUMS
								FROM  V_EOS_ORDER A LEFT JOIN V_EOS_ORDER_ITEM B on A.ORDER_NO = B.ORDER_NO
								WHERE A.ORDER_NO = V_ORDER_NO;
				--OPEN V_OU_CUR;
				--LOOP
				--FETCH V_OU_CUR INTO V_TEMP;
				--END LOOP;
				--CLOSE V_OU_CUR;--这里如果如果要返回参数游标的话,不要在存储过程里面关闭游标。否则,应用程序是得不到存储过程的返回值		
				
			RETURN;
			END IF;
	EXCEPTION
    WHEN OTHERS THEN
    V_OUT_RETURN := 'ERROR';
    V_OU_MSG    := '失败,原因是:' || SQLERRM;
    ROLLBACK;
    INSERT INTO SYS_ERROR_LOG
      (ROW_ID, PR_NAME, ERROR_DESC, INSDT)
    VALUES
      (SYS_ERROR_LOG_SEQ.NEXTVAL, 'SJJ_ORDER_QUERY', V_OU_MSG, SYSDATE);
    COMMIT;
END SJJ_ORDER_QUERY;




加载中
返回顶部
顶部