create or replace procedure tally_log_oper(inserttally in varchar2
,updatetally out varchar2,deletetally out varchar2)
is
v_na number;
v_nb number;
v_nc number;
v_sum number;
value_null exception;
begin
select v_na:=count(*) from log_file_view where operater='insert';
select v_nb:=count(*) from log_file_view where operater='update';
select v_nc:=count(*) from log_file_view where operater='delete';
v_sum:=v_na+v_nb+v_nc;
if v_sum is null
then raise value_null;
end if;
inserttally:=to_char(round(v_na/v_sum*100,2))||'%';
updatetally:=to_char(round(v_nb/v_sum*100,2))||'%';
deletetally:=to_char(round(v_nc/v_sum*100,2))||'%';
exception
when value_null then dbms_output.pu_line('没有任何操作');
when others then exit();
end;
上面SQL代码有问题,但我又看不出来错误,麻烦大家帮帮我。
我想问下,有没有什么工具能找出PL/SQL的错误,就像上面这种,谢了!
create or replace procedure tally_log_oper(inserttally in varchar2
,updatetally out varchar2,deletetally out varchar2)
is
--建议在声明变量的时候应初始化变量
v_na number:=0;
v_nb number:=0;
v_nc number:=0;
v_sum number:=0;
value_null exception;--异常名称
pragme EXCEPTION_init(value_null,-20001);--异常名称和异常号绑定
begin
select count(*) into v_na from log_file_view where operater='insert';
select count(*) into v_nb from log_file_view where operater='update';
select count(*) into v_nc from log_file_view where operater='delete';
v_sum:=(v_na+ v_nb+ v_nc);
if v_sum is null then
raise_application_error(-20001,'v_sum 为空异常!');
end if;
--inserttally:=to_char(round(v_na/v_sum*100,2))||'%';--此处你声明的inserttally为传入参数(in),不可以给其赋值
updatetally:=to_char(round(v_nb/v_sum*100,2))||'%';
deletetally:=to_char(round(v_nc/v_sum*100,2))||'%';
exception
when value_null then
dbms_output.put_line('没有任何操作');
when others then
null;--修改处
end tally_log_oper;
10/8 PLS-00201: 必须说明标识符 'EXCEPTION_INIT'
10/8 PL/SQL: Item ignored我查了资料,但没有解决问题,希望能帮忙解决
-- select count(*) into v_na from log_file_view where operater='insert';
-- select count(*) into v_nb from log_file_view where operater='update';
-- select count(*) into v_nc from log_file_view where operater='delete';
select v_na:=count(*) from log_file_view where operater='insert';
的地方要改为
select count(*) into v_na from log_file_view where operater='insert';
这样的形式
is
v_na number;
v_nb number;
v_nc number;
v_sum number;
value_null exception;
begin
select count(*) into v_na from log_file_view where operater='insert';
select count(*) into v_nb from log_file_view where operater='update';
select count(*) into v_nc from log_file_view where operater='delete';
v_sum:= v_na+v_nb+v_nc;
if v_sum is null then
raise value_null;
end if; select round(v_na/v_sum*100,2), round(v_nb/v_sum*100,2), round(v_nc/v_sum*100,2)
INTO inserttally, updatetally, deletetally FROM dual;exception
when value_null then dbms_output.put_line('没有任何操作');
end;
/set serveroutput on;
var v_inserttally number;
var v_updatetally number;
var v_deletetally number;
exec tally_log_oper(:v_inserttally, :v_updatetally, :v_deletetally);
print v_inserttally;
print v_updatetally;
print v_deletetally;
create or replace procedure tally_log_oper(inserttally out varchar2
,updatetally out varchar2,deletetally out varchar2)
is
v_na number;
v_nb number;
v_nc number;
v_sum number;
value_null exception;
begin
select count(*) into v_na from log_file_view where operater='insert';
select count(*) into v_nb from log_file_view where operater='update';
select count(*) into v_nc from log_file_view where operater='delete';
v_sum:=v_na+v_nb+v_nc;
if v_sum is null
then raise value_null;
end if;
inserttally:=to_char(round(v_na/v_sum*100,2))||'%';
updatetally:=to_char(round(v_nb/v_sum*100,2))||'%';
deletetally:=to_char(round(v_nc/v_sum*100,2))||'%';
exception
when value_null then dbms_output.pu_line('没有任何操作');
when others then exit();
end;----sql语句中赋值是 into 变量的。
--inserttally in 你的书传入参数 不能被赋值的 只能把他的值传给其他的变量。要么把他的类型改为 inserttally in out(可赋值给变量,也可以被赋值) 或者 out