--以下这段代码在pl/sql中编译有错CREATE OR REPLACE PROCEDURE LESSONS_SELECTHISTORY1
(
I_UserId in number default null,
I_YearNow in number default null,
I_Lesson in varchar2 default null,
I_PageIndex in number default null,
I_PageSize in number default null,
O_RecordCount out number,
O_Cursor out sys_refcursor
)/******************************************************************************
1.REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2011-7-20 heli 在线课程学习历史查询
2.REF PROGRAMS:3.NOTES:
--k.statusname 考试状态(课程类型改为考试状态)
--获得学时 (通过则和学时一样的,没有通过则为0)
*******************************************************************************/
AS
STRSQL VARCHAR(4000);
BEGIN
STRSQL :='
select a.userid,a.year,b.lessoncode,b.lessonname,c.abilityname,d.abilitytypename,g.lessontypename,a.exportdate,a.score,a.passchk,k.statusname,b.duration
,nvl(a1.rightNum,-1) rightNum
from userlessons a
left join lessons b on a.lessoncode=b.lessoncode
left join abilitylessons f on f.lessoncode=b.lessoncode
left join abilities c on c.abilityid=f.abilityid
left join abilitytypes d on d.abilitytypecode=c.abilitytypecode
left join lessontypes g on g.lessontypecode=b.lessontypecode
left join statusinfo k on k.statuscode=a.status and k.statustypecode=4
left join (select r.lessoncode,r.userid,r.examinationyear,sum(case when q.getscore>0 then 1 else 0 end) as rightNum
from (select o.userid,o.examinationyear,o.examinationid,p.lessoncode
,count(p.examinationid) as examTimes,max(o.examinationseqno) as maxseqno
,max(o.enddate) as enddate
from userexaminations o left join examinations p on p.examinationid=o.examinationid
where p.lessoncode is not null group by o.userid,o.examinationyear,o.examinationid,p.lessoncode) r
left join userexamquestions q on q.userid= r.userid and r.examinationyear=q.examinationyear
and q.examinationid=r.examinationid and q.examinationseqno=r.maxseqno
group by r.userid,r.examinationyear,r.examinationid,r.lessoncode,r.examTimes,r.enddate,r.maxseqno,r.lessoncode
) a1
on a1.userid=a.userid and a1.examinationyear=a.year and a1.lessoncode=a.lessoncode';
-- OPEN O_CURSOR FOR STRSQL;
IF I_AbilityId IS NOT NULL THEN
STRSQL := STRSQL || '
AND a.userid =''' || I_UserId || '''
';
END IF;
IF I_YearNow IS NOT NULL THEN
STRSQL := STRSQL || '
AND a.year =''' || I_YearNow || '''
';
END IF;
IF I_Lesson IS NOT NULL THEN
STRSQL := STRSQL || '
AND b.lessoncode =''' || I_Lesson || '''
';
END IF;
O_Cursor :=PAGE_SPLIT(STRSQL,I_PageIndex,I_PageSize,O_RecordCount);
END
(
I_UserId in number default null,
I_YearNow in number default null,
I_Lesson in varchar2 default null,
I_PageIndex in number default null,
I_PageSize in number default null,
O_RecordCount out number,
O_Cursor out sys_refcursor
)/******************************************************************************
1.REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2011-7-20 heli 在线课程学习历史查询
2.REF PROGRAMS:3.NOTES:
--k.statusname 考试状态(课程类型改为考试状态)
--获得学时 (通过则和学时一样的,没有通过则为0)
*******************************************************************************/
AS
STRSQL VARCHAR(4000);
BEGIN
STRSQL :='
select a.userid,a.year,b.lessoncode,b.lessonname,c.abilityname,d.abilitytypename,g.lessontypename,a.exportdate,a.score,a.passchk,k.statusname,b.duration
,nvl(a1.rightNum,-1) rightNum
from userlessons a
left join lessons b on a.lessoncode=b.lessoncode
left join abilitylessons f on f.lessoncode=b.lessoncode
left join abilities c on c.abilityid=f.abilityid
left join abilitytypes d on d.abilitytypecode=c.abilitytypecode
left join lessontypes g on g.lessontypecode=b.lessontypecode
left join statusinfo k on k.statuscode=a.status and k.statustypecode=4
left join (select r.lessoncode,r.userid,r.examinationyear,sum(case when q.getscore>0 then 1 else 0 end) as rightNum
from (select o.userid,o.examinationyear,o.examinationid,p.lessoncode
,count(p.examinationid) as examTimes,max(o.examinationseqno) as maxseqno
,max(o.enddate) as enddate
from userexaminations o left join examinations p on p.examinationid=o.examinationid
where p.lessoncode is not null group by o.userid,o.examinationyear,o.examinationid,p.lessoncode) r
left join userexamquestions q on q.userid= r.userid and r.examinationyear=q.examinationyear
and q.examinationid=r.examinationid and q.examinationseqno=r.maxseqno
group by r.userid,r.examinationyear,r.examinationid,r.lessoncode,r.examTimes,r.enddate,r.maxseqno,r.lessoncode
) a1
on a1.userid=a.userid and a1.examinationyear=a.year and a1.lessoncode=a.lessoncode';
-- OPEN O_CURSOR FOR STRSQL;
IF I_AbilityId IS NOT NULL THEN
STRSQL := STRSQL || '
AND a.userid =''' || I_UserId || '''
';
END IF;
IF I_YearNow IS NOT NULL THEN
STRSQL := STRSQL || '
AND a.year =''' || I_YearNow || '''
';
END IF;
IF I_Lesson IS NOT NULL THEN
STRSQL := STRSQL || '
AND b.lessoncode =''' || I_Lesson || '''
';
END IF;
O_Cursor :=PAGE_SPLIT(STRSQL,I_PageIndex,I_PageSize,O_RecordCount);
END
重新编译,然后 show error 把错误显示出来看看。
引用 1 楼 benchim888 的回复:
把你的 STRSQL 打印出来然后弄到数据库里执行一下看看,应该是拼接的问题。另外报什么错误,贴出来。PROCEDURE LMSDATA.LESSONS_SELECTHISTORY1 编译错误错误:PLS-00103: 出现符号 "end-of-file"在需要下列之一时:
;
<an identifier> <a double-quoted delimited-identifier>
current delete exists prior <a single-quoted SQL string>
符号 ";" 被替换为 "end-of-file" 后继续。
行:71