刚刚把参数名换掉,还是不行, 报错点在 EXECUTE IMMEDIATE A_SQL; 如果我没有参数 CREATE OR REPLACE PROCEDURE P_TEST006 AUTHID current_user IS A_SQL VARCHAR2(2000); CURSOR CURSOR_1 IS SELECT DISTINCT P.QID,(case when questionname like '%-%' then substr(questionname,0,(instr(questionname,'-')-1)) else substr(questionname,0,5) end) questionname FROM PAP_SYS_PAPANSWER p,Pap_Sys_Question ps where p.qid=ps.questrecid AND p.paperid=''||paperid||''; BEGIN A_SQL := 'SELECT PAPERID,USRID,MAX(MARKINGMAN) as 阅卷人,MAX(MARKINGTIME) AS 阅卷时间,MAX(MARKINGSTATUS) AS 阅卷状态'; FOR V_QUESID IN CURSOR_1 LOOP A_SQL := A_SQL || ',' || 'MAX(CASE WHEN PA.QID=''' || V_QUESID.QID || '''THEN PA.ANSINFO END ) AS ' || V_QUESID.questionname ; END LOOP; A_SQL := A_SQL || ' FROM PAP_SYS_PAPANSWER WHERE PA.PAPERID=''MT100'' PA GROUP BY PAPERID,USRID ORDER BY PAPERID,USRID'; dbms_output.put(A_SQL); A_SQL := 'CREATE OR REPLACE VIEW RESULT6 AS '|| A_SQL; EXECUTE IMMEDIATE A_SQL; END; 这个存数过程是可以的,
FROM PAP_SYS_PAPANSWER WHERE PA.PAPERID=''MT100'' PA 这块顺序错了吧,改为 FROM PAP_SYS_PAPANSWER PA WHERE PA.PAPERID=''MT100''
如p.paperid=''||paperid||''; 这个判断会一直返回true的,等号右边的paperid会被认为是字段名而不是参数名
直接导致最后拼接的sql语句超出定义的2000长度,多出的部分被舍弃了
最终A_SQL中存储的是一个不完整的语句,因此在执行的时候会报这样的错误
如果我没有参数 CREATE OR REPLACE PROCEDURE P_TEST006 AUTHID current_user IS
A_SQL VARCHAR2(2000);
CURSOR CURSOR_1 IS SELECT DISTINCT P.QID,(case when questionname like '%-%' then substr(questionname,0,(instr(questionname,'-')-1))
else substr(questionname,0,5) end) questionname FROM PAP_SYS_PAPANSWER p,Pap_Sys_Question ps where p.qid=ps.questrecid AND p.paperid=''||paperid||''; BEGIN
A_SQL := 'SELECT PAPERID,USRID,MAX(MARKINGMAN) as 阅卷人,MAX(MARKINGTIME) AS 阅卷时间,MAX(MARKINGSTATUS) AS 阅卷状态'; FOR V_QUESID IN CURSOR_1
LOOP
A_SQL := A_SQL || ',' || 'MAX(CASE WHEN PA.QID=''' || V_QUESID.QID ||
'''THEN PA.ANSINFO END ) AS ' || V_QUESID.questionname ;
END LOOP;
A_SQL := A_SQL || ' FROM PAP_SYS_PAPANSWER WHERE PA.PAPERID=''MT100'' PA GROUP BY PAPERID,USRID ORDER BY PAPERID,USRID';
dbms_output.put(A_SQL);
A_SQL := 'CREATE OR REPLACE VIEW RESULT6 AS '|| A_SQL;
EXECUTE IMMEDIATE A_SQL;
END;
这个存数过程是可以的,
这块顺序错了吧,改为
FROM PAP_SYS_PAPANSWER PA WHERE PA.PAPERID=''MT100''