create or replace procedure out_emr(
MRID in varchar
)
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);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';
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= '糖尿病') ;
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
CLOSE audit_cursor;
begin
select MRID,flag,iccardno,dia_date,admiss_times,MDESCRIPT,MHISDESCRIPT,CHECKUP,CHECKASS,COURSEREC,
DIAGNOSECONTENT,DIAGNOSERANGE from ca_emr;
end
end out_emr;
Compilation errors for PROCEDURE SZEMR.OUT_EMRError: PLS-00103: 出现符号 "FETCH"在需要下列之一时:
constant exception
<an identifier> <a double-quoted delimited-identifier> table
LONG_ double ref char time timestamp interval date binary
national character nchar
Line: 54
Text: FETCH audit_cursor INTOError: PLS-00103: 出现符号 "CLOSE"在需要下列之一时:
;
Line: 74
Text: CLOSE audit_cursor;Error: PLS-00103: 出现符号 "END"在需要下列之一时:
; <an identifier>
<a double-quoted delimited-identifier> delete exists prior
<a single-quoted SQL string>
Line: 82
Text: end out_emr;
MRID in varchar
)
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);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';
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= '糖尿病') ;
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
CLOSE audit_cursor;
begin
select MRID,flag,iccardno,dia_date,admiss_times,MDESCRIPT,MHISDESCRIPT,CHECKUP,CHECKASS,COURSEREC,
DIAGNOSECONTENT,DIAGNOSERANGE from ca_emr;
end
end out_emr;
Compilation errors for PROCEDURE SZEMR.OUT_EMRError: PLS-00103: 出现符号 "FETCH"在需要下列之一时:
constant exception
<an identifier> <a double-quoted delimited-identifier> table
LONG_ double ref char time timestamp interval date binary
national character nchar
Line: 54
Text: FETCH audit_cursor INTOError: PLS-00103: 出现符号 "CLOSE"在需要下列之一时:
;
Line: 74
Text: CLOSE audit_cursor;Error: PLS-00103: 出现符号 "END"在需要下列之一时:
; <an identifier>
<a double-quoted delimited-identifier> delete exists prior
<a single-quoted SQL string>
Line: 82
Text: end out_emr;
end loop;--加分号
CLOSE audit_cursor;
end;--还是分号
end out_emr;
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; /*BEGIN
SELECT MRID,
flag,
iccardno,
dia_date,
admiss_times,
MDESCRIPT,
MHISDESCRIPT,
CHECKUP,
CHECKASS,
COURSEREC,
DIAGNOSECONTENT,
DIAGNOSERANGE
FROM ca_emr; --pl/sql中select语句必须要into变量即:select col1,col2 into col1,col2
END;*/ --这儿begin..end多余且没有分号
END out_emr;
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: 83
Text: SELECT convert(VARCHAR(8000), xmlcontent)Error: PL/SQL: SQL Statement ignored
Line: 83
Text: SELECT convert(VARCHAR(8000), xmlcontent)Error: PL/SQL: ORA-00936: 缺少表达式
Line: 87
Text: SELECT convert(VARCHAR(8000), xmlcontent)Error: PL/SQL: SQL Statement ignored
Line: 87
Text: SELECT convert(VARCHAR(8000), xmlcontent)Error: PL/SQL: ORA-00936: 缺少表达式
Line: 91
Text: SELECT convert(VARCHAR(8000), xmlcontent)Error: PL/SQL: SQL Statement ignored
Line: 91
Text: SELECT convert(VARCHAR(8000), xmlcontent)Error: PL/SQL: ORA-00936: 缺少表达式
Line: 95
Text: SELECT convert(VARCHAR(8000), xmlcontent)Error: PL/SQL: SQL Statement ignored
Line: 95
Text: SELECT convert(VARCHAR(8000), xmlcontent)Error: Hint: Variable 'MRID2' is declared but never used in 'out_emr'
Line: 4
Text: MRID2 VARCHAR(20);
constant exception
<an identifier> <a double-quoted delimited-identifier> table
LONG_ double ref char time timestamp interval date binary
national character nchar
Line: 53
Text: FETCH audit_cursor INTOError: PLS-00103: 出现符号 "CLOSE"在需要下列之一时:
end not pragma final
instantiable order overriding static member constructor map
Line: 71
Text: CLOSE audit_cursor;Error: PLS-00103: 出现符号 "OUT_EMR"在需要下列之一时:
;
Line: 79
Text: end out_emr;