现在是出现一场,就抛出一场,终止了,怎样让他循环起来,把异常抛出
create or replace procedure test2 is
mup number(14,2);
udown number(14,2);
sup1 number(14,2);
scode varchar2(30);
scode1 varchar2(30);
scode2 varchar2(30);
sql_str varchar2(30000);
t_time stock_shanghai_newprice.time%type;
cursor cur_tab
is
SELECT stockcode,time,RTrim(To_Char(((newprice-closeprice)/closeprice)*100,'FM99999999990.99'),'.'),RTrim(To_Char(newprice-closeprice,'FM99999999990.99'),'.'),RTrim(To_Char(((maxprice-minprice)/closeprice)*100,'FM99999999990.99'),'.') FROM stock_shanghai_newprice where rownum<5;
begin
open cur_tab;
loop
fetch cur_tab into scode1,t_time,mup,udown,sup1;
exit when cur_tab%notfound;
dbms_output.put_line(t_time);
--select stockcode into scode2 from stock_shanghai_newprice where up=mup and updown=udown and shakeup=sup1 and stockcode=scode1 and time=t_time;
select stockcode into scode from stock_shanghai_newprice where (up!=mup or updown!=udown or shakeup!=sup1 ) and stockcode=scode1 and time=t_time;
--if scode <> 0 then
--dbms_output.put_line('no inconsistency');
dbms_output.put_line(scode);--end if;
end loop;
close cur_tab;
exception
when no_data_found then
--else
--select stockcode into scode from stock_shanghai_newprice where (up!=mup or updown!=udown or shakeup!=sup1 ) and stockcode=scode1 and time=t_time;
--select stockcode into scode2 from stock_shanghai_newprice where up=mup and updown=udown and shakeup=sup1 and stockcode=scode1 and time=t_time;
--dbms_output.put_line(scode);
dbms_output.put_line('Unanimously');
end;
create or replace procedure test2 is
mup number(14,2);
udown number(14,2);
sup1 number(14,2);
scode varchar2(30);
scode1 varchar2(30);
scode2 varchar2(30);
sql_str varchar2(30000);
t_time stock_shanghai_newprice.time%type;
cursor cur_tab
is
SELECT stockcode,time,RTrim(To_Char(((newprice-closeprice)/closeprice)*100,'FM99999999990.99'),'.'),RTrim(To_Char(newprice-closeprice,'FM99999999990.99'),'.'),RTrim(To_Char(((maxprice-minprice)/closeprice)*100,'FM99999999990.99'),'.') FROM stock_shanghai_newprice where rownum<5;
begin
open cur_tab;
loop
fetch cur_tab into scode1,t_time,mup,udown,sup1;
exit when cur_tab%notfound;
dbms_output.put_line(t_time);
--select stockcode into scode2 from stock_shanghai_newprice where up=mup and updown=udown and shakeup=sup1 and stockcode=scode1 and time=t_time;
select stockcode into scode from stock_shanghai_newprice where (up!=mup or updown!=udown or shakeup!=sup1 ) and stockcode=scode1 and time=t_time;
--if scode <> 0 then
--dbms_output.put_line('no inconsistency');
dbms_output.put_line(scode);--end if;
end loop;
close cur_tab;
exception
when no_data_found then
--else
--select stockcode into scode from stock_shanghai_newprice where (up!=mup or updown!=udown or shakeup!=sup1 ) and stockcode=scode1 and time=t_time;
--select stockcode into scode2 from stock_shanghai_newprice where up=mup and updown=udown and shakeup=sup1 and stockcode=scode1 and time=t_time;
--dbms_output.put_line(scode);
dbms_output.put_line('Unanimously');
end;
begin
...
end
包一层,
有异常了不用throw, 记下信息就可以
exception了就可以接着往下执行
mup number(14,2);
udown number(14,2);
sup1 number(14,2);
scode varchar2(30);
scode1 varchar2(30);
scode2 varchar2(30);
sql_str varchar2(30000);
t_time stock_shanghai_newprice.time%type;
cursor cur_tab
is
SELECT stockcode,time,RTrim(To_Char(((newprice-closeprice)/closeprice)*100,'FM99999999990.99'),'.'),RTrim(To_Char(newprice-closeprice,'FM99999999990.99'),'.'),RTrim(To_Char(((maxprice-minprice)/closeprice)*100,'FM99999999990.99'),'.') FROM stock_shanghai_newprice where rownum<5;
begin
open cur_tab;
loop
fetch cur_tab into scode1,t_time,mup,udown,sup1;
exit when cur_tab%notfound;
dbms_output.put_line(t_time);
select stockcode into scode from stock_shanghai_newprice where (up!=mup or updown!=udown or shakeup!=sup1 ) and stockcode=scode1 and time=t_time;
if scode <> 0 then
--dbms_output.put_line('no inconsistency');
dbms_output.put_line(scode);
else
begin
exception
when no_data_found then
dbms_output.put_line('Unanimously');
end;
end if;
end loop;
close cur_tab;
end;是这样吗?报错Compilation errors for PROCEDURE XINHUA.TEST3Error: PLS-00103: 出现符号 "EXCEPTION"在需要下列之一时:
begin case declare
exit for goto if loop mod null pragma raise return select
update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
Line: 26
Text: exception
DO SOMETHING
EXCEPTION
……………………
END;你的错误提示很明显是异常捕捉位置不对。
CREATE OR REPLACE PROCEDURE test2
IS
mup NUMBER (14, 2);
udown NUMBER (14, 2);
sup1 NUMBER (14, 2);
scode VARCHAR2 (30);
scode1 VARCHAR2 (30);
scode2 VARCHAR2 (30);
sql_str VARCHAR2 (30000);
t_time stock_shanghai_newprice.TIME%TYPE; CURSOR cur_tab
IS
SELECT stockcode scode1, TIME t_time,
RTRIM (TO_CHAR (((newprice - closeprice) / closeprice) * 100,
'FM99999999990.99'
),
'.'
) makup,
RTRIM (TO_CHAR (newprice - closeprice, 'FM99999999990.99'),
'.'
) udown,
RTRIM (TO_CHAR (((maxprice - minprice) / closeprice) * 100,
'FM99999999990.99'
),
'.'
) sup1
FROM stock_shanghai_newprice
WHERE ROWNUM < 5;
BEGIN
FOR each_rec IN cur_tab
LOOP
-- FETCH cur_tab
--INTO scode1, t_time, mup, udown, sup1; -- EXIT WHEN cur_tab%NOTFOUND;
DBMS_OUTPUT.put_line ('Procedure started at' || SYSDATE); BEGIN
SELECT stockcode
INTO scode
FROM stock_shanghai_newprice
WHERE ( up != each_rec.makup
OR updown != each_rec.udown
OR shakeup != each_rec.sup1
)
AND stockcode = each_rec.scode1
AND TIME = each_rec.t_time; DBMS_OUTPUT.put_line (scode);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('NO DATE RUTURN FROM THIS CONDITION');
END;
END LOOP;
--CLOSE cur_tab;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('Unanimously');
END;
SELECT stockcode
INTO scode
FROM stock_shanghai_newprice
WHERE ( up != each_rec.makup
OR updown != each_rec.udown
OR shakeup != each_rec.sup1
)
AND stockcode = each_rec.scode1
AND TIME = each_rec.t_time; DBMS_OUTPUT.put_line (scode);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('NO DATE RUTURN FROM THIS CONDITION');
END; 这个部分是捕捉异常的部分
CURSOR cur_tab
IS
SELECT stockcode scode1, TIME t_time,
RTRIM (TO_CHAR (((newprice - closeprice) / closeprice) * 100,
'FM99999999990.99'
),
'.'
) makup,
RTRIM (TO_CHAR (newprice - closeprice, 'FM99999999990.99'),
'.'
) udown,
RTRIM (TO_CHAR (((maxprice - minprice) / closeprice) * 100,
'FM99999999990.99'
),
'.'
) sup1
FROM stock_shanghai_newprice
WHERE ROWNUM < 5;
出现除数为0的情况
begin
...
end
包一层,
有异常了不用throw, 记下信息就可以
exception了就可以接着往下执行
IS
mup NUMBER (14, 2);
udown NUMBER (14, 2);
sup1 NUMBER (14, 2);
scode VARCHAR2 (30);
scode1 VARCHAR2 (30);
scode2 VARCHAR2 (30);
sql_str VARCHAR2 (30000);
t_time stock_shanghai_newprice.TIME%TYPE;
t_time1 stock_shanghai_newprice.TIME%TYPE; CURSOR cur_tab IS SELECT stockcode scode1, TIME t_time,
RTRIM (TO_CHAR (((newprice - closeprice) / closeprice) * 100,
'FM99999999990.99'
),
'.'
) makup,
RTRIM (TO_CHAR (newprice - closeprice, 'FM99999999990.99'),
'.'
) udown,
RTRIM (TO_CHAR (((maxprice - minprice) / closeprice) * 100,
'FM99999999990.99'
),
'.'
) sup1
FROM stock_shanghai_newprice;
-- WHERE ROWNUM < 5;
BEGIN
FOR each_rec IN cur_tab
LOOP
-- FETCH cur_tab
--INTO scode1, t_time, mup, udown, sup1; -- EXIT WHEN cur_tab%NOTFOUND;
-- DBMS_OUTPUT.put_line ('Procedure started at' || SYSDATE); BEGIN
SELECT stockcode,time
INTO scode ,t_time1
FROM stock_shanghai_newprice
WHERE ( up != each_rec.makup
OR updown != each_rec.udown
OR shakeup != each_rec.sup1
)
AND stockcode = each_rec.scode1
AND TIME = each_rec.t_time; DBMS_OUTPUT.put_line (scode||','||t_time1||','||'Please check the data!');
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line (each_rec.scode1||','||'Data correctly');
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE ('Can not divide zero');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unknown Error');
END;
END LOOP;
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE ('Can not divide zero');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unknown Error');
--CLOSE cur_tab;
END;
你需要选择
1.where rownum <5 and closeprice<>0
2.或者用DECODE语句对closeprice进行符合业务规则的处理,语法如下DECODE(closeprice,0,1,closeprice),