我测试了另外一个 存储过程 (v_management varchar2,V_busno out varchar2) as begin select VEHICLEID into V_busno from AJVEMAINTAIN where COMPANY=v_management; end;调用 declare management varchar2(20); begin testdb2('嘉兴管理处',management); dbms_output.put_line(management); end; / ERROR 位于第 1 行: ORA-01422: 实际返回的行数超出请求的行数 ORA-06512: 在"SA.TESTDB2", line 3 ORA-06512: 在line 4 还是这个问题!
试试这个select VEHICLEID into V_busno from AJVEMAINTAIN where COMPANY=v_management and rownum<2
CREATE OR REPLACE PACKAGE pkg_test AS TYPE myrctype IS REF CURSOR; END pkg_test; /create procedure TESTDB(COMPANY2 in varchar2,MTDATE2 in char,MTDATE1 in char,p_rc out pkg_test.myrctype) as str varchar2(200); begin str:='select COMPANY,to_char(MTDATE,''yyyy''),to_char(MTDATE,''mm''), VEHICLEID,to_char(MTDATE,''mm/dd''), MTTYPE, CARALLRUN,DISTANCERUN,SPPROJECT from AJVEMAINTAIN where COMPANY='||COMPANY2||' and to_char(MTDATE,''yyyy'')='||MTDATE2||' and to_char(MTDATE,''mm'')='||MTDATE1; open p_rc for str; END; / declare v_rc pkg_test.myrctype; v_COMPANY1 varchar2(10); v_outmate varchar2(10); v_outmate1 varchar2(10); v_VEHICLEID varchar2(10); v_repdatte varchar2(10); v_mttype1 varchar2(10); v_carallrun1 number; v_DISTANCERUN1 number; v_SPPROJECT1 varchar2(10); begin TESTDB('嘉兴管理处','2003','05'); loop fetch v_rc into v_COMPANY1,v_outmate,v_outmate1,v_VEHICLEID,v_repdatte,v_mttype1,v_carallrun1,v_DISTANCERUN1,v_SPPROJECT1; exit when v_rc%notfound; dbms_output.put_line(v_COMPANY1||v_outmate||v_outmate1||v_VEHICLEID||v_repdatte||v_mttype1||v_carallrun1||v_DISTANCERUN1||v_SPPROJECT1); end loop; end; /
存储过程
(v_management varchar2,V_busno out varchar2) as
begin
select VEHICLEID
into V_busno
from AJVEMAINTAIN
where COMPANY=v_management;
end;调用
declare
management varchar2(20);
begin
testdb2('嘉兴管理处',management);
dbms_output.put_line(management);
end;
/
ERROR 位于第 1 行:
ORA-01422: 实际返回的行数超出请求的行数
ORA-06512: 在"SA.TESTDB2", line 3
ORA-06512: 在line 4
还是这个问题!
into V_busno
from AJVEMAINTAIN
where COMPANY=v_management and rownum<2
AS
TYPE myrctype IS REF CURSOR;
END pkg_test;
/create procedure TESTDB(COMPANY2 in varchar2,MTDATE2 in char,MTDATE1 in char,p_rc out pkg_test.myrctype)
as
str varchar2(200);
begin
str:='select COMPANY,to_char(MTDATE,''yyyy''),to_char(MTDATE,''mm''),
VEHICLEID,to_char(MTDATE,''mm/dd''), MTTYPE,
CARALLRUN,DISTANCERUN,SPPROJECT
from AJVEMAINTAIN
where
COMPANY='||COMPANY2||' and to_char(MTDATE,''yyyy'')='||MTDATE2||' and to_char(MTDATE,''mm'')='||MTDATE1;
open p_rc for str;
END;
/
declare
v_rc pkg_test.myrctype;
v_COMPANY1 varchar2(10);
v_outmate varchar2(10);
v_outmate1 varchar2(10);
v_VEHICLEID varchar2(10);
v_repdatte varchar2(10);
v_mttype1 varchar2(10);
v_carallrun1 number;
v_DISTANCERUN1 number;
v_SPPROJECT1 varchar2(10);
begin
TESTDB('嘉兴管理处','2003','05');
loop
fetch v_rc into v_COMPANY1,v_outmate,v_outmate1,v_VEHICLEID,v_repdatte,v_mttype1,v_carallrun1,v_DISTANCERUN1,v_SPPROJECT1;
exit when v_rc%notfound;
dbms_output.put_line(v_COMPANY1||v_outmate||v_outmate1||v_VEHICLEID||v_repdatte||v_mttype1||v_carallrun1||v_DISTANCERUN1||v_SPPROJECT1);
end loop;
end;
/
在sql plus里运行提示出错
TESTDB('嘉兴管理处','2003','05');
*
ERROR 位于第 13 行:
ORA-06550: 第 13 行, 第 1 列:
PLS-00306: 调用 'TESTDB' 时参数数量或类型错
ORA-06550: 第 13 行, 第 1 列:
PL/SQL: Statement ignored
我改成TESTDB('嘉兴管理处','2003','05',v_rc );后出错信息变成了
declare
*
ERROR 位于第 1 行:
ORA-06502: PL/SQL: 数字或值错误
ORA-06512: 在"SA.TESTDB", line 6
ORA-06512: 在line 13
我真的不知道怎么做?你能帮帮我吗?