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_EMRError: 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)
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_EMRError: 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)
解决方案 »
- 关于commit的问题
- 奇怪!!ORACLE 10G为什么我用IE无法打开管理控制台,而用MAXTHON却可以??????????
- 目前做oracle + shell批处理,以后能往什么方向发展
- 在forms中如何屏蔽Fn等快捷键?
- 在PL/SQL中定义一个Varchar2 变量咋成了未定义的
- 创建视图时一个奇怪的错误。
- 求三个表关联的查询语句,在线等
- 浏览器远程访问oracle10g的问题
- dotnet中的winform如何连接oracle8.0.5
- 怎样改变资料库数据文件的建立目标位置呢?我目前的DB_CREATE_FILE_DEST已经改变了,
- 不建表,获取select查询的字段
- 如何更新oracle数据库中blob对象的十六进制数值?
磨刀不误砍柴功!不然,一个个贴上论坛,要修改什么时候!!