自己写了一段存储过程,其中有这部分代码
procedure queryTrainAllPersonM(v_total out number, v_result out ref_cur,……)……
v_totalstu number(15):=0;
v_totalTC number(15):=0;tempSql := 'select count(1) from tables1 where conditions1;
execute immediate tempSql into v_totalstu;
tempSql:= 'select count(1) from tables2 where conditions2;
execute immediate tempSql into v_totalTC;
v_total:=v_totalstu+v_totalTC; 就这段代码,在我的机器上运行正常,但是换成另一台机器就总是报ORA-06502: PL/SQL: 数字或值错误,位置指向v_total:=v_totalstu+v_totalTC; 这到底错在哪里!为什么我的机器上跑就可以呢?
procedure queryTrainAllPersonM(v_total out number, v_result out ref_cur,……)……
v_totalstu number(15):=0;
v_totalTC number(15):=0;tempSql := 'select count(1) from tables1 where conditions1;
execute immediate tempSql into v_totalstu;
tempSql:= 'select count(1) from tables2 where conditions2;
execute immediate tempSql into v_totalTC;
v_total:=v_totalstu+v_totalTC; 就这段代码,在我的机器上运行正常,但是换成另一台机器就总是报ORA-06502: PL/SQL: 数字或值错误,位置指向v_total:=v_totalstu+v_totalTC; 这到底错在哪里!为什么我的机器上跑就可以呢?
--给LZ看两段存贮过程的例子吧:
--例子一(使用cursor取值后使变量相乘或相加):CREATE OR REPLACE procedure tran_get_money(user_id IN VARCHAR2) as
cursor c1 is
select fees from info_admin where userid = user_id;
cursor c2 is
select money from info_admin where userid = user_id;
cursor c3 is
select max(money) from info_admin;
l_fees number := 0;
l_money number := 0;
l_maxmoney number := 0;
l_compmax number := 0;
begin
open c1;
fetch c1
into l_fees;
open c2;
fetch c2
into l_money;
open c3;
fetch c3
into l_maxmoney;
l_money := l_money * l_fees;
if l_maxmoney >= l_money then
l_compmax := l_maxmoney;
else
l_compmax := l_money;
end if;
update Account set fees_money = l_compmax where userid = user_id;
close c1;
close c2;
close c3;
end;
--例子二:(直接返回查询的记录集)
create or replace procedure p_test(in_var number, out_var out sys_refcursor) as
begin
open out_var for select * from test where aaa = in_var;
end;