我写了存储过程如下CREATE OR REPLACE procedure Import_350600000000 (Tablename IN varchar2)
AS
taxicount number;
sqlstr varchar(2000);
type mycursor is ref cursor; --定义游标类型
mycur mycursor;
Myrow mycur%type;
begin
sqlstr:= 'SELECT 车牌号,车型,录入时间,录入人,ID FROM '||Tablename;
--先不重复的导入出租车信息 open mycur for sqlstr;
loop
fetch mycur into Myrow;
exit when mycur%notfound;
select count(*) into taxicount from TAXI_MAIN where TAXI_CPHM=Myrow.车牌号;
if taxicount<1 then
INSERT INTO TAXI_MAIN (TAXI_MAIN_ID,TAXI_CPHM,TAXI_CLXH,CREATE_TIME,CREATE_BY,SRC_ID) values (SEQUENCE_TAXI.nextval,Myrow.车牌号,Myrow.车型,Myrow.录入时间,Myrow.录入人,Myrow.ID);
end if;
end loop;
close mycur;
end;
错误如下Project: D:\Program Files\sqldeveloper\jdev\system\oracle.sqldeveloper.10.1.3.1557\DefaultWorkspace\Project1.jpr
PROCEDURE TAXIADMIN.IMPORT_350600000000@TAXIDB
Error(17,3): PL/SQL: SQL Statement ignored
Error(17,65): PL/SQL: ORA-00904: "MYROW"."车牌号": 标识符无效
Error(17,65): PLS-00487: 对变量 'MYROW' 的引用无效
Error(19,4): PL/SQL: SQL Statement ignored
Error(19,184): PLS-00487: 对变量 'MYROW' 的引用无效
Error(19,190): PL/SQL: ORA-00984: 列在此处不允许
AS
taxicount number;
sqlstr varchar(2000);
type mycursor is ref cursor; --定义游标类型
mycur mycursor;
Myrow mycur%type;
begin
sqlstr:= 'SELECT 车牌号,车型,录入时间,录入人,ID FROM '||Tablename;
--先不重复的导入出租车信息 open mycur for sqlstr;
loop
fetch mycur into Myrow;
exit when mycur%notfound;
select count(*) into taxicount from TAXI_MAIN where TAXI_CPHM=Myrow.车牌号;
if taxicount<1 then
INSERT INTO TAXI_MAIN (TAXI_MAIN_ID,TAXI_CPHM,TAXI_CLXH,CREATE_TIME,CREATE_BY,SRC_ID) values (SEQUENCE_TAXI.nextval,Myrow.车牌号,Myrow.车型,Myrow.录入时间,Myrow.录入人,Myrow.ID);
end if;
end loop;
close mycur;
end;
错误如下Project: D:\Program Files\sqldeveloper\jdev\system\oracle.sqldeveloper.10.1.3.1557\DefaultWorkspace\Project1.jpr
PROCEDURE TAXIADMIN.IMPORT_350600000000@TAXIDB
Error(17,3): PL/SQL: SQL Statement ignored
Error(17,65): PL/SQL: ORA-00904: "MYROW"."车牌号": 标识符无效
Error(17,65): PLS-00487: 对变量 'MYROW' 的引用无效
Error(19,4): PL/SQL: SQL Statement ignored
Error(19,184): PLS-00487: 对变量 'MYROW' 的引用无效
Error(19,190): PL/SQL: ORA-00984: 列在此处不允许
错误还是一样,我想关键在于oracle不知道我取出的表结构是固定的,因为我的Sql语句是动态的
还顶
CREATE OR REPLACE procedure Import_350600000000 (myTablename IN varchar2)
AS
taxicount number;
sqlstr varchar(2000);
type mycursor is ref cursor; --定义游标类型
mycur mycursor;
Myrow myTablename%type;
begin
sqlstr:= 'SELECT 车牌号,车型,录入时间,录入人,ID FROM '||myTablename;
--先不重复的导入出租车信息 open mycur for sqlstr;
loop
fetch mycur into Myrow;
exit when mycur%notfound;
select count(*) into taxicount from TAXI_MAIN where TAXI_CPHM=Myrow.车牌号;
if taxicount<1 then
INSERT INTO TAXI_MAIN (TAXI_MAIN_ID,TAXI_CPHM,TAXI_CLXH,CREATE_TIME,CREATE_BY,SRC_ID) values (SEQUENCE_TAXI.nextval,Myrow.车牌号,Myrow.车型,Myrow.录入时间,Myrow.录入人,Myrow.ID);
end if;
end loop;
close mycur;
end;错误如下Project: D:\Program Files\sqldeveloper\jdev\system\oracle.sqldeveloper.10.1.3.1557\DefaultWorkspace\Project1.jpr
PROCEDURE TAXIADMIN.IMPORT_350600000000@TAXIDB
Error(7,8): PLS-00206: %TYPE 必须用于变量, 列, 字段或属性, 而不是 'MYTABLENAME'
Error(7,8): PL/SQL: Item ignored
Error(15,13): PL/SQL: SQL Statement ignored
Error(15,30): PLS-00320: 此表达式的类型声明不完整或格式不正确
Error(17,3): PL/SQL: SQL Statement ignored
Error(17,65): PL/SQL: ORA-00904: "MYROW"."车牌号": 标识符无效
Error(17,65): PLS-00320: 此表达式的类型声明不完整或格式不正确
Error(19,4): PL/SQL: SQL Statement ignored
Error(19,184): PLS-00320: 此表达式的类型声明不完整或格式不正确
Error(19,190): PL/SQL: ORA-00984: 列在此处不允许
的意思是不是我先做一个有固定格式的表作为类似定义的样子,之后导入时按它的type来取??
我所说的"动态表的某个表名"必须真实存在
而不是参数,知道没?
AS
taxicount number;
sqlstr varchar(2000);
type mycursor is ref cursor; --定义游标类型
mycur mycursor;
Myrow mycur%type;
col1 varchar2(100);
col2 varchar2(100);
col3 Date;
col4 varchar2(100);
col5 number;
begin
sqlstr:= 'SELECT 车牌号,车型,录入时间,录入人,ID FROM '||myTablename;
open mycur for sqlstr;
loop
fetch mycur into col1,col2,col3,col4,col5;
exit when mycur%notfound; select count(*) into taxicount from TAXI_MAIN where TAXI_CPHM=col1;
if taxicount<1 then
INSERT INTO TAXI_MAIN (TAXI_MAIN_ID,TAXI_CPHM,TAXI_CLXH,CREATE_TIME,CREATE_BY,SRC_ID) values (SEQUENCE_TAXI.nextval,col1,col2,col3,col4,col5);
end if;
end loop;
close mycur;
end;