先贴代码:存储过程CREATE OR REPLACE PROCEDURE PRO_DZ_537(DZ_FLAG IN NUMBER, COURSE OUT VARCHAR2)
IS
NO_TEMP T_DZ_LOG_535.NO%TYPE;
TYPE_TEMP T_DZ_LOG_535.TYPE%TYPE;
MSG_TEMP T_DZ_LOG_535.MSG%TYPE;
TABLENAME_TEMP T_DZ_LOG_535.TABLENAME%TYPE;
PROD_NUM_TEMP T_DZ_LOG_535.PROD_NUM%TYPE;
REC_NUM_TEMP T_DZ_LOG_535.REC_NUM%TYPE;
RATE_AMOUNT_TEMP T_DZ_LOG_535.RATE_AMOUNT%TYPE;
ERR_MGS_TEMP T_DZ_LOG_535.ERR_MGS%TYPE;
REMANK_TEMP T_DZ_LOG_535.REMANK%TYPE;
DDATE_TEMP T_DZ_LOG_535.DDATE%TYPE;
DTIME_TEMP T_DZ_LOG_535.DTIME%TYPE;
CURSOR T_DZ_CON_TMP IS
SELECT * FROM T_USAGE_EVENTS_20100607 A WHERE A.TYPE=DZ_FLAG
UNION
SELECT * FROM T_GW_UE_17_5350_20100607 B WHERE B.TYPE=DZ_FLAG;
CURSOR T_DZ_ALL_TMP IS
SELECT * FROM T_USAGE_EVENTS_20100607
UNION
SELECT * FROM T_GW_UE_17_5350_20100607;
BEGIN
IF(DZ_FLAG >=1 OR DZ_FLAG <=4) THEN
OPEN T_DZ_CON_TMP;
LOOP
FETCH T_DZ_CON_TMP
INTO NO_TEMP,TYPE_TEMP,MSG_TEMP,TABLENAME_TEMP,PROD_NUM_TEMP,
REC_NUM_TEMP,RATE_AMOUNT_TEMP,ERR_MGS_TEMP,
REMANK_TEMP,DDATE_TEMP,DTIME_TEMP;
EXIT WHEN T_DZ_CON_TMP%NOTFOUND;
INSERT INTO T_DZ_LOG_535(NO,TYPE,MSG,TABLENAME,PROD_NUM,REC_NUM,RATE_AMOUNT,ERR_MGS,REMANK,DDATE,DTIME)
VALUES(SEQ_DZ_LOG_535.NEXTVAL,TYPE_TEMP,MSG_TEMP,TABLENAME_TEMP,PROD_NUM_TEMP,
REC_NUM_TEMP,RATE_AMOUNT_TEMP,ERR_MGS_TEMP,
REMANK_TEMP,DDATE_TEMP,DTIME_TEMP);
END LOOP;
COMMIT;
ELSIF(DZ_FLAG = 0) THEN
OPEN T_DZ_ALL_TMP;
LOOP
FETCH T_DZ_ALL_TMP
INTO NO_TEMP,TYPE_TEMP,MSG_TEMP,TABLENAME_TEMP,PROD_NUM_TEMP,
REC_NUM_TEMP,RATE_AMOUNT_TEMP,ERR_MGS_TEMP,
REMANK_TEMP,DDATE_TEMP,DTIME_TEMP;
EXIT WHEN T_DZ_ALL_TMP%NOTFOUND;
INSERT INTO T_DZ_LOG_535(NO,TYPE,MSG,TABLENAME,PROD_NUM,REC_NUM,RATE_AMOUNT,ERR_MGS,REMANK,DDATE,DTIME)
VALUES(SEQ_DZ_LOG_535.NEXTVAL,TYPE_TEMP,MSG_TEMP,TABLENAME_TEMP,PROD_NUM_TEMP,
REC_NUM_TEMP,RATE_AMOUNT_TEMP,ERR_MGS_TEMP,
REMANK_TEMP,DDATE_TEMP,DTIME_TEMP);
END LOOP;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
COURSE := '程序出错,请联系管理员';
ROLLBACK;
END PRO_DZ_537;
调用存储:
DECLARE
DZ_TYPE NUMBER;
COURSE VARCHAR2(100);
BEGIN
DZ_TYPE := 1;
COURSE := '添加成功';
PRO_DZ_537(DZ_TYPE,COURSE);
DBMS_OUTPUT.put_line(COURSE);问题是,调用存储过程的时候,DZ_TYPE为1——4的时候都能够添加成功,当为0的时候(存储过程中写明应全部添加),可是执行之后却没有添加任何值!!!问题应该出在判断值为0的时候问题!先谢谢!!!在线等结果!!!!
END;
IS
NO_TEMP T_DZ_LOG_535.NO%TYPE;
TYPE_TEMP T_DZ_LOG_535.TYPE%TYPE;
MSG_TEMP T_DZ_LOG_535.MSG%TYPE;
TABLENAME_TEMP T_DZ_LOG_535.TABLENAME%TYPE;
PROD_NUM_TEMP T_DZ_LOG_535.PROD_NUM%TYPE;
REC_NUM_TEMP T_DZ_LOG_535.REC_NUM%TYPE;
RATE_AMOUNT_TEMP T_DZ_LOG_535.RATE_AMOUNT%TYPE;
ERR_MGS_TEMP T_DZ_LOG_535.ERR_MGS%TYPE;
REMANK_TEMP T_DZ_LOG_535.REMANK%TYPE;
DDATE_TEMP T_DZ_LOG_535.DDATE%TYPE;
DTIME_TEMP T_DZ_LOG_535.DTIME%TYPE;
CURSOR T_DZ_CON_TMP IS
SELECT * FROM T_USAGE_EVENTS_20100607 A WHERE A.TYPE=DZ_FLAG
UNION
SELECT * FROM T_GW_UE_17_5350_20100607 B WHERE B.TYPE=DZ_FLAG;
CURSOR T_DZ_ALL_TMP IS
SELECT * FROM T_USAGE_EVENTS_20100607
UNION
SELECT * FROM T_GW_UE_17_5350_20100607;
BEGIN
IF(DZ_FLAG >=1 OR DZ_FLAG <=4) THEN
OPEN T_DZ_CON_TMP;
LOOP
FETCH T_DZ_CON_TMP
INTO NO_TEMP,TYPE_TEMP,MSG_TEMP,TABLENAME_TEMP,PROD_NUM_TEMP,
REC_NUM_TEMP,RATE_AMOUNT_TEMP,ERR_MGS_TEMP,
REMANK_TEMP,DDATE_TEMP,DTIME_TEMP;
EXIT WHEN T_DZ_CON_TMP%NOTFOUND;
INSERT INTO T_DZ_LOG_535(NO,TYPE,MSG,TABLENAME,PROD_NUM,REC_NUM,RATE_AMOUNT,ERR_MGS,REMANK,DDATE,DTIME)
VALUES(SEQ_DZ_LOG_535.NEXTVAL,TYPE_TEMP,MSG_TEMP,TABLENAME_TEMP,PROD_NUM_TEMP,
REC_NUM_TEMP,RATE_AMOUNT_TEMP,ERR_MGS_TEMP,
REMANK_TEMP,DDATE_TEMP,DTIME_TEMP);
END LOOP;
COMMIT;
ELSIF(DZ_FLAG = 0) THEN
OPEN T_DZ_ALL_TMP;
LOOP
FETCH T_DZ_ALL_TMP
INTO NO_TEMP,TYPE_TEMP,MSG_TEMP,TABLENAME_TEMP,PROD_NUM_TEMP,
REC_NUM_TEMP,RATE_AMOUNT_TEMP,ERR_MGS_TEMP,
REMANK_TEMP,DDATE_TEMP,DTIME_TEMP;
EXIT WHEN T_DZ_ALL_TMP%NOTFOUND;
INSERT INTO T_DZ_LOG_535(NO,TYPE,MSG,TABLENAME,PROD_NUM,REC_NUM,RATE_AMOUNT,ERR_MGS,REMANK,DDATE,DTIME)
VALUES(SEQ_DZ_LOG_535.NEXTVAL,TYPE_TEMP,MSG_TEMP,TABLENAME_TEMP,PROD_NUM_TEMP,
REC_NUM_TEMP,RATE_AMOUNT_TEMP,ERR_MGS_TEMP,
REMANK_TEMP,DDATE_TEMP,DTIME_TEMP);
END LOOP;
COMMIT;
END IF;
EXCEPTION
WHEN OTHERS THEN
COURSE := '程序出错,请联系管理员';
ROLLBACK;
END PRO_DZ_537;
调用存储:
DECLARE
DZ_TYPE NUMBER;
COURSE VARCHAR2(100);
BEGIN
DZ_TYPE := 1;
COURSE := '添加成功';
PRO_DZ_537(DZ_TYPE,COURSE);
DBMS_OUTPUT.put_line(COURSE);问题是,调用存储过程的时候,DZ_TYPE为1——4的时候都能够添加成功,当为0的时候(存储过程中写明应全部添加),可是执行之后却没有添加任何值!!!问题应该出在判断值为0的时候问题!先谢谢!!!在线等结果!!!!
END;
只要把if 和 elsif 里的判断条件 和里面的函数块调换一下就可以了!!可是不知道为什么!希望有人能帮忙解答!!!
IF (dz_flag >= 1 OR dz_flag <= 4) THEN
ELSIF (dz_flag = 0) THEN是不是应该改成用AND?