CREATE OR REPLACE PROCEDURE EA_MARKET_INTERFACE.P_GetCustInfo_Active(
TITLE OUT VARCHAR2, --结果集字段TITLE描述表名
NAME OUT VARCHAR2, --结果集字段名描述表名
RESULT OUT SYS_REFCURSOR, --结果集临时表名描述
AGENT_ID IN VARCHAR2
)ISRESERVE_TIME NUMERIC(14);--预约回复的时间范围
DATE INTEGER;
TIME INTEGER;
MIN INTEGER;
SRC_CUST_CODE VARCHAR(20);
TEMP_SQL VARCHAR2(1000);
BEGIN
--取预提的时间(分钟)
SELECT CAST(PARAM_VAL AS INTEGER) INTO COLLECT_CUST_INC_MIN
FROM PARAM_DEF
WHERE PARAM_CODE='MIN'; TIME:=CAST(TO_CHAR(SYSDATE,'HH24MISS')AS INTEGER);
DATE:=CAST(TO_CHAR(SYSDATE,'YYYYMMDD') AS INTEGER);
COLLECT_CUST_INC_MIN:=COLLECT_CUST_INC_MIN*60;
--计算预约回复的时间范围
CALL ADD_TIME(TIME,MIN);--调用另一个存储过程,这里也报错 RESERVE_TIME:=DATE*1000000+TIME; TEMP_SQL:='CREATE GLOBAL TEMPORARY TABLE VT_RESULT(
CUST_CODE VARCHAR2(20) NOT NULL
,CUST_NAME VARCHAR2(60)
,CUST_SEX VARCHAR2(10)
,DEPT_CODE VARCHAR2(10)
,DEPT_NAME VARCHAR2(100)
,CUST_AGE NUMERIC(3)
,CUST_KIND CHAR(1)
,EDUCATION VARCHAR2(50)
,VOCATION VARCHAR2(50)
,INCOME_LEVEL VARCHAR2(50)
,PRIVATE_PHONE1 VARCHAR2(20)
,PRIVATE_PHONE2 VARCHAR2(20)
,PRIVATE_PHONE3 VARCHAR2(20)
,PUBLIC_PHONE VARCHAR2(20)
,EMAIL VARCHAR2(80)
,MKT_PHONE1 VARCHAR2(20)
,MKT_PHONE2 VARCHAR2(20)
,MKT_PHONE3 VARCHAR2(20)
,MKT_PHONE4 VARCHAR2(20)
,MKT_PHONE5 VARCHAR2(20)
,RESERVE_TIME NUMERIC(14)
,RESERVE_PHONE VARCHAR2(20)
,MKT_MEMO VARCHAR2(500)
)';
EXECUTE IMMEDIATE TEMP_SQL;--建一个临时表 BEGIN TRANSACTION;
LOCKING TABLE EA_MARKET.MS_RESERVE_CUST_INDEX FOR EXCLUSIVE--这个报错了,不知道怎么改 SELECT CUST_CODE INTO SRC_CUST_CODE
FROM RESERVE_CUST_INDEX A
WHERE A.RESERVE_TIME<=AI_RESERVE_TIME
AND A.PRODUCT_CLASS_CODE='01'
SAMPLE 1; IF COALESCE(AC_SRC_CUST_CODE,'')<>'' THEN
DELETE FROM EA_MARKET.MS_RESERVE_CUST_INDEX
WHERE SRC_CUST_CODE=AC_SRC_CUST_CODE;
END IF;
END TRANSACTION;
IF COALESCE(ASRC_CUST_CODE,'')='' THEN
BEGIN TRANSACTION;
LOCKING TABLE TARGET_CUST_INDEX FOR EXCLUSIVE
SELECT SRC_CUST_CODE INTO AC_SRC_CUST_CODE
FROM EA_MARKET.MS_TARGET_CUST_INDEX A
WHERE A.PRODUCT_CLASS_CODE='01'
SAMPLE 1;
DELETE FROM EA_MARKET.MS_TARGET_CUST_INDEX
WHERE SRC_CUST_CODE=AC_SRC_CUST_CODE;
END TRANSACTION;
END IF; IF COALESCE(AC_SRC_CUST_CODE,'')<>'' THEN
INSERT INTO VT_RESULT
SELECT COALESCE(A.SRC_CUST_CODE,'')
,COALESCE(A.CUST_NAME,'')
,COALESCE(A.SEX_CODE,'')
,COALESCE(A.DEP_CODE,'')
,COALESCE(A.AGE,0)
,COALESCE(A.CUST_KIND,'F')
,COALESCE(A.EDU_CODE,'')
,COALESCE(A.VOC_CODE,'')
,COALESCE(A.EARN_LEVEL_CODE,'')
,COALESCE(A.PRIVATE_PHONE1,'')
,COALESCE(A.PRIVATE_PHONE2,'') FROM EA_MARKET.MS_MKT_CUST_INFO A
WHERE SRC_CUST_CODE=AC_SRC_CUST_CODE;
IF SQL%ROWCOUNT<>0 THEN
--更新机构代码
UPDATE A
FROM VT_RESULT A,E_SYSTEM.SYS_DEP_INFO B
SET DEPT_NAME=B.DEP_NAME
WHERE A.DEPT_CODE=B.DEP_CODE;
OPEN AC_RESULT FOR SELECT* FROM VT_RESULT;
END IF ;
END IF;
END;
TITLE OUT VARCHAR2, --结果集字段TITLE描述表名
NAME OUT VARCHAR2, --结果集字段名描述表名
RESULT OUT SYS_REFCURSOR, --结果集临时表名描述
AGENT_ID IN VARCHAR2
)ISRESERVE_TIME NUMERIC(14);--预约回复的时间范围
DATE INTEGER;
TIME INTEGER;
MIN INTEGER;
SRC_CUST_CODE VARCHAR(20);
TEMP_SQL VARCHAR2(1000);
BEGIN
--取预提的时间(分钟)
SELECT CAST(PARAM_VAL AS INTEGER) INTO COLLECT_CUST_INC_MIN
FROM PARAM_DEF
WHERE PARAM_CODE='MIN'; TIME:=CAST(TO_CHAR(SYSDATE,'HH24MISS')AS INTEGER);
DATE:=CAST(TO_CHAR(SYSDATE,'YYYYMMDD') AS INTEGER);
COLLECT_CUST_INC_MIN:=COLLECT_CUST_INC_MIN*60;
--计算预约回复的时间范围
CALL ADD_TIME(TIME,MIN);--调用另一个存储过程,这里也报错 RESERVE_TIME:=DATE*1000000+TIME; TEMP_SQL:='CREATE GLOBAL TEMPORARY TABLE VT_RESULT(
CUST_CODE VARCHAR2(20) NOT NULL
,CUST_NAME VARCHAR2(60)
,CUST_SEX VARCHAR2(10)
,DEPT_CODE VARCHAR2(10)
,DEPT_NAME VARCHAR2(100)
,CUST_AGE NUMERIC(3)
,CUST_KIND CHAR(1)
,EDUCATION VARCHAR2(50)
,VOCATION VARCHAR2(50)
,INCOME_LEVEL VARCHAR2(50)
,PRIVATE_PHONE1 VARCHAR2(20)
,PRIVATE_PHONE2 VARCHAR2(20)
,PRIVATE_PHONE3 VARCHAR2(20)
,PUBLIC_PHONE VARCHAR2(20)
,EMAIL VARCHAR2(80)
,MKT_PHONE1 VARCHAR2(20)
,MKT_PHONE2 VARCHAR2(20)
,MKT_PHONE3 VARCHAR2(20)
,MKT_PHONE4 VARCHAR2(20)
,MKT_PHONE5 VARCHAR2(20)
,RESERVE_TIME NUMERIC(14)
,RESERVE_PHONE VARCHAR2(20)
,MKT_MEMO VARCHAR2(500)
)';
EXECUTE IMMEDIATE TEMP_SQL;--建一个临时表 BEGIN TRANSACTION;
LOCKING TABLE EA_MARKET.MS_RESERVE_CUST_INDEX FOR EXCLUSIVE--这个报错了,不知道怎么改 SELECT CUST_CODE INTO SRC_CUST_CODE
FROM RESERVE_CUST_INDEX A
WHERE A.RESERVE_TIME<=AI_RESERVE_TIME
AND A.PRODUCT_CLASS_CODE='01'
SAMPLE 1; IF COALESCE(AC_SRC_CUST_CODE,'')<>'' THEN
DELETE FROM EA_MARKET.MS_RESERVE_CUST_INDEX
WHERE SRC_CUST_CODE=AC_SRC_CUST_CODE;
END IF;
END TRANSACTION;
IF COALESCE(ASRC_CUST_CODE,'')='' THEN
BEGIN TRANSACTION;
LOCKING TABLE TARGET_CUST_INDEX FOR EXCLUSIVE
SELECT SRC_CUST_CODE INTO AC_SRC_CUST_CODE
FROM EA_MARKET.MS_TARGET_CUST_INDEX A
WHERE A.PRODUCT_CLASS_CODE='01'
SAMPLE 1;
DELETE FROM EA_MARKET.MS_TARGET_CUST_INDEX
WHERE SRC_CUST_CODE=AC_SRC_CUST_CODE;
END TRANSACTION;
END IF; IF COALESCE(AC_SRC_CUST_CODE,'')<>'' THEN
INSERT INTO VT_RESULT
SELECT COALESCE(A.SRC_CUST_CODE,'')
,COALESCE(A.CUST_NAME,'')
,COALESCE(A.SEX_CODE,'')
,COALESCE(A.DEP_CODE,'')
,COALESCE(A.AGE,0)
,COALESCE(A.CUST_KIND,'F')
,COALESCE(A.EDU_CODE,'')
,COALESCE(A.VOC_CODE,'')
,COALESCE(A.EARN_LEVEL_CODE,'')
,COALESCE(A.PRIVATE_PHONE1,'')
,COALESCE(A.PRIVATE_PHONE2,'') FROM EA_MARKET.MS_MKT_CUST_INFO A
WHERE SRC_CUST_CODE=AC_SRC_CUST_CODE;
IF SQL%ROWCOUNT<>0 THEN
--更新机构代码
UPDATE A
FROM VT_RESULT A,E_SYSTEM.SYS_DEP_INFO B
SET DEPT_NAME=B.DEP_NAME
WHERE A.DEPT_CODE=B.DEP_CODE;
OPEN AC_RESULT FOR SELECT* FROM VT_RESULT;
END IF ;
END IF;
END;
:= . ( @ % ;
符号 ":=" 被替换为 "P_ADD_TIME" 后继续。
行:51
文本:CALL EA_MARKET_INTERFACE.P_ADD_TIME(AI_TIME,AI_COLLECT_CUST_INC_MIN);错误:PLS-00103: 出现符号 "TABLE"在需要下列之一时:
:= . ( @ % ;
行:92
文本:LOCKING TABLE EA_MARKET.MS_RESERVE_CUST_INDEX FOR EXCLUSIVE错误:PLS-00103: 出现符号 "FOR"在需要下列之一时:
. , @ in <an identifier>
<a double-quoted delimited-identifier> partition subpartition
行:92
文本:LOCKING TABLE EA_MARKET.MS_RESERVE_CUST_INDEX FOR EXCLUSIVE错误:提示: Parameter 'AC_AGENT_ID' 已被声明但从来没有被用于 'EA_MARKET_INTERFACE.P_GetCustInfo_Active'
行:5
文本:AC_AGENT_ID IN VARCHAR2 --坐席工号错误:提示: Variable 'ACTIVECOUNT' 已被声明但从来没有被用于 'EA_MARKET_INTERFACE.P_GetCustInfo_Active'
行:37
文本:ACTIVECOUNT INTEGER;错误:提示: Variable 'RESULT_SQL' 已被声明但从来没有被用于 'EA_MARKET_INTERFACE.P_GetCustInfo_Active'
行:39
文本:RESULT_SQL VARCHAR2(1000);错误:提示:EA_MARKET_INTERFACE.P_GetCustInfo_Active 里怀疑有 NULL 进行比较的情况
行:99
文本:IF COALESCE(AC_SRC_CUST_CODE,'')<>'' THEN错误:提示:EA_MARKET_INTERFACE.P_GetCustInfo_Active 里怀疑有 NULL 进行比较的情况
行:106
文本:IF COALESCE(AC_SRC_CUST_CODE,'')='' THEN错误:提示:EA_MARKET_INTERFACE.P_GetCustInfo_Active 里怀疑有 NULL 进行比较的情况
行:118
文本:IF COALESCE(AC_SRC_CUST_CODE,'')<>'' THEN
IF COALESCE(AC_SRC_CUST_CODE,'') is not null then;
RESULT_SQL VARCHAR2(1000);
Variable 'ACTIVECOUNT'; Variable 'RESULT_SQL';没用就注释掉;
其他的我也看着很郁闷;