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);
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(1)
)
on commit PRESERVE rows;
begin
null;
end out_emr;
Error: PLS-00103: 出现符号 "CREATE"在需要下列之一时:
begin function package
pragma procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor
Line: 24
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);
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(1)
)
on commit PRESERVE rows;
begin
null;
end out_emr;
Error: PLS-00103: 出现符号 "CREATE"在需要下列之一时:
begin function package
pragma procedure subtype type use <an identifier>
<a double-quoted delimited-identifier> form current cursor
Line: 24
先创建,然后在过程中使用.对不同session表数据是隔离的.
oracle的过程中是不支持静态ddl的,除非你用动态sql.
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
--要在存储过程中执行DDL,需要动态执行
--最好在SQL环境中先创建好临时表,ORACLE临时表是数据临时,表不临时!!!
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(1)) ON COMMIT PRESERVE rows'; NULL;
END out_emr;