【结】光标中的sql语句的日期格式不对。

bfaomei

我的存储过程用了光标，但调试时出现日期格式错误。该怎么改？
出错地方：
光标中的sql语句的日期格式不对。
编译后变成：
SELECT PRIMARY_ID,USERID,JOB_X3_ID,FUNCTION_ID,QUERY_DATE FROM APPLICATION_LOG WHERE TO_CHAR(QUERY_DATE,'YYYYMM') >= TO_CHAR(01-6月 -09,'YYYYMM') AND TO_CHAR(QUERY_DATE,'YYYYMM') <= TO_CHAR(31-8月 -09,'YYYYMM') ORDER BY USERID,JOB_X3_ID,QUERY_DATE;


存储过程如下：
create or replace procedure PROC_RANK_RECORD
(
   P_STATR_DATE    IN APPLICATION_LOG.QUERY_DATE%type,
   P_END_DATE      IN APPLICATION_LOG.QUERY_DATE%type,
   P_TYPE          IN NUMBER,
   N_RESULT        OUT NUMBER,
   R_RESULT        OUT VARCHAR2
) IS
    v_temp_userid NUMBER;
    v_temp_job_x3_id APPLICATION_LOG.JOB_X3_ID%TYPE;
    v_temp_function_id NUMBER;
    v_temp_query_date date;
    v_temp_userid_1 NUMBER;
    v_temp_job_x3_id_1 APPLICATION_LOG.JOB_X3_ID%TYPE;
    v_temp_function_id_1 NUMBER;
    v_temp_query_date_1 date;
    v_temp_primary_id NUMBER;
    V_COUNT number;
    V_SQL VARCHAR2(2000);
    TYPE CUR_TYP IS REF CURSOR;
    RANK_CUR CUR_TYP;
BEGIN
     SET TRANSACTION READ WRITE;
     N_RESULT:=1;
     R_RESULT:='';
     v_temp_userid:=0;
     v_temp_job_x3_id:='';
     v_temp_function_id:=0;
     v_temp_query_date:='';
     IF P_TYPE=1 THEN

        OPEN RANK_CUR FOR V_SQL;
        LOOP
           FETCH RANK_CUR INTO v_temp_userid_1,v_temp_job_x3_id_1,v_temp_function_id_1,v_temp_query_date_1;
           EXIT WHEN RANK_CUR%NOTFOUND;
           IF v_temp_function_id<>v_temp_function_id_1 OR v_temp_userid<>v_temp_userid_1 OR v_temp_job_x3_id<> v_temp_job_x3_id_1 OR TO_CHAR(v_temp_query_date,'YYYYMMDD')<>TO_CHAR(v_temp_query_date_1,'YYYYMMDD') THEN
               UPDATE APPLICATION_LOG SET STATUS=1 WHERE PRIMARY_ID=v_temp_primary_id;
            END IF;
            v_temp_userid:=v_temp_userid_1;
            v_temp_job_x3_id:=v_temp_job_x3_id_1;
            v_temp_function_id:=v_temp_function_id_1;
            v_temp_query_date:=v_temp_query_date_1;
        END LOOP;
        INSERT INTO RANK_RECORD(PRIMARY_ID,USERID,JOB_X3_ID,TOTAL,UPDATE_DATE)
        SELECT SEQ_RANK_STAR.NEXTVAL,USERID,JOB_X3_ID,TOTAL,SYSDATE FROM (
        SELECT USERID,JOB_X3_ID,SUM(SCORE) AS TOTAL FROM (
        SELECT USERID,QUERY_DATE,JOB_X3_ID,FUNCTION_ID,V_COUNT,(CASE WHEN V_COUNT<=4 THEN V_COUNT*1 WHEN V_COUNT>10 THEN V_COUNT*0.7 ELSE 4 END) AS SCORE
        FROM (
        SELECT TO_CHAR(QUERY_DATE,'YYYYMMDD') AS QUERY_DATE,JOB_X3_ID,FUNCTION_ID,USERID,COUNT(FUNCTION_ID) AS V_COUNT
        FROM (SELECT * FROM  APPLICATION_LOG  ORDER BY PRIMARY_ID ) WHERE TO_CHAR(QUERY_DATE,'YYYYMM') >= TO_CHAR(P_STATR_DATE,'YYYYMM') AND TO_CHAR(QUERY_DATE,'YYYYMM') <= TO_CHAR(P_END_DATE,'YYYYMM') AND STATUS=1
        GROUP BY TO_CHAR(QUERY_DATE,'YYYYMMDD'),JOB_X3_ID,FUNCTION_ID,USERID
        ) GROUP BY QUERY_DATE,JOB_X3_ID,FUNCTION_ID,V_COUNT,USERID )
        GROUP BY USERID,JOB_X3_ID
        );
        COMMIT;
     END IF;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
     ROLLBACK;
     R_RESULT := '出错，请检查再尝试，若有疑问可与IT部联系查找原因！';
     N_RESULT := 0;
   WHEN OTHERS THEN
     ROLLBACK;
     R_RESULT := '出错，请检查再尝试，若有疑问可与IT部联系查找原因！';
     N_RESULT := 0;

END PROC_RANK_RECORD;

bfaomei

这里出现异常。
IF P_TYPE=1 THEN
        V_SQL :='SELECT PRIMARY_ID,USERID,JOB_X3_ID,FUNCTION_ID,QUERY_DATE FROM APPLICATION_LOG WHERE TO_CHAR(QUERY_DATE,''YYYYMM'') >= TO_CHAR('||P_STATR_DATE||',''YYYYMM'') AND TO_CHAR(QUERY_DATE,''YYYYMM'') <= TO_CHAR('||P_END_DATE||',''YYYYMM'') ORDER BY USERID,JOB_X3_ID,QUERY_DATE';
        OPEN RANK_CUR FOR V_SQL;
        LOOP


           FETCH RANK_CUR INTO v_temp_userid_1,v_temp_job_x3_id_1,v_temp_function_id_1,v_temp_query_date_1;
           EXIT WHEN RANK_CUR%NOTFOUND;

lixy

这个不属于平台问题的范畴，请楼主查阅相关资料。

bfaomei

哦。那我查查资料。