oracle 表达式错误

zt735268255 2011-06-17 01:02:40

CREATE OR REPLACE PROCEDURE out_emr(MRID IN VARCHAR2) IS

MRID1 VARCHAR(20);
MRID2 VARCHAR(20);
dia_date VARCHAR(30);
iccardno VARCHAR(20);
MDESCRIPT VARCHAR(8000);
MHISDESCRIPT VARCHAR(8000);
CHECKUP VARCHAR(8000);
CHECKASS VARCHAR(8000);
COURSEREC VARCHAR(8000);
MDESCRIPT1 VARCHAR(8000);
MHISDESCRIPT1 VARCHAR(8000);
CHECKUP1 VARCHAR(8000);
CHECKASS1 VARCHAR(8000);
COURSEREC1 VARCHAR(8000);
admiss_times INT;
DIAGNOSECONTENT VARCHAR(60);
DIAGNOSERANGE NCHAR(1);

--游标声明必须放到变量声明处,declare多余
CURSOR audit_cursor IS

SELECT mainmr.MRID,
mainmr.papernum,
inpatient.outdate,
inpatient.admiss_times,
inpatient.MDESCRIPT,
inpatient.MHISDESCRIPT,
inpatient.CHECKUP,
inpatient.CHECKASS,
inpatient.COURSEREC,
alldiagnoserecord.DIAGNOSECONTENT,
alldiagnoserecord.DIAGNOSERANGE
FROM mainmr, inpatient, alldiagnoserecord
WHERE mainmr.MRID = inpatient.MRID
AND inpatient.MRID = alldiagnoserecord.sickid
AND mainmr.MRID = MRID
AND (diagnosecontent = '妊娠糖尿病' OR diagnosecontent = '2型糖尿病' OR
diagnosecontent = '1型糖尿病' OR diagnosecontent = '特殊类型糖尿病' OR
diagnosecontent = '糖尿病');

BEGIN
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE ca_emr(
MRID varchar(20),
dia_date varchar(30),
flag varchar(1) ,
doctor varchar(5),
iccardno varchar(20),
MDESCRIPT long,
MHISDESCRIPT long ,
CHECKUP long ,
CHECKASS long ,
COURSEREC long,
admiss_times smallint ,
DIAGNOSECONTENT varchar(60),
DIAGNOSERANGE nchar(2)
)
on commit PRESERVE rows';

OPEN audit_cursor;
LOOP

FETCH audit_cursor
INTO MRID1,
iccardno,
dia_date,
admiss_times,
MDESCRIPT1,
MHISDESCRIPT1,
CHECKUP1,
CHECKASS1,
COURSEREC1,
DIAGNOSECONTENT,
DIAGNOSERANGE;

EXIT WHEN audit_cursor%NOTFOUND;

SELECT convert(VARCHAR(8000), xmlcontent)

INTO MDESCRIPT
FROM xmlfilerecord
WHERE xmlcontentid = MDESCRIPT1;

SELECT convert(VARCHAR(8000), xmlcontent)
INTO CHECKUP
FROM xmlfilerecord
WHERE xmlcontentid = CHECKUP1;

SELECT convert(VARCHAR(8000), xmlcontent)
INTO CHECKASS
FROM xmlfilerecord
WHERE xmlcontentid = CHECKASS1;

SELECT convert(VARCHAR(8000), xmlcontent)
INTO COURSEREC
FROM xmlfilerecord
WHERE xmlcontentid = COURSEREC1;

SELECT convert(VARCHAR(8000), xmlcontent)
INTO MHISDESCRIPT
FROM xmlfilerecord
WHERE xmlcontentid = MHISDESCRIPT1;

INSERT INTO ca_emr
(MRID, flag, iccardno, dia_date, admiss_times, MDESCRIPT,
MHISDESCRIPT, CHECKUP, CHECKASS, COURSEREC, DIAGNOSECONTENT,
DIAGNOSERANGE)
VALUES
(MRID1, 'z', iccardno, dia_date, admiss_times, MDESCRIPT,
MHISDESCRIPT, CHECKUP, CHECKASS, COURSEREC, DIAGNOSECONTENT,
DIAGNOSERANGE);

END LOOP; --end loop缺少分号
CLOSE audit_cursor;

END out_emr;

Compilation errors for PROCEDURE SZEMR.OUT_EMR

Error: PL/SQL: ORA-00936: 缺少表达式
Line: 79
Text: SELECT convert(VARCHAR(8000), xmlcontent)

Error: PL/SQL: SQL Statement ignored
Line: 79
Text: SELECT convert(VARCHAR(8000), xmlcontent)

Error: PL/SQL: ORA-00936: 缺少表达式
Line: 84
Text: SELECT convert(VARCHAR(8000), xmlcontent)

Error: PL/SQL: SQL Statement ignored
Line: 84
Text: SELECT convert(VARCHAR(8000), xmlcontent)

Error: PL/SQL: ORA-00936: 缺少表达式
Line: 89
Text: SELECT convert(VARCHAR(8000), xmlcontent)

Error: PL/SQL: SQL Statement ignored
Line: 89
Text: SELECT convert(VARCHAR(8000), xmlcontent)

Error: PL/SQL: ORA-00936: 缺少表达式
Line: 94
Text: SELECT convert(VARCHAR(8000), xmlcontent)

Error: PL/SQL: SQL Statement ignored
Line: 94
Text: SELECT convert(VARCHAR(8000), xmlcontent)

Error: PL/SQL: ORA-00936: 缺少表达式
Line: 99
Text: SELECT convert(VARCHAR(8000), xmlcontent)

Error: PL/SQL: SQL Statement ignored
Line: 99
Text: SELECT convert(VARCHAR(8000), xmlcontent)

...全文