CREATE OR REPLACE PROCEDURE CHECK_DATA_test_2
(
s_code stock_shanghai_newprice.stockcode%TYPE,
t_time stock_shanghai_newprice.time%TYPE
)
IS
mup stock_shanghai_newprice.up%TYPE;
udown stock_shanghai_newprice.updown%TYPE;
sup1 stock_shanghai_newprice.shakeup%TYPE;
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;
id_1 stock_shanghai_newprice.id%TYPE;
CURSOR cur_tab
IS
IF s_code is null THEN
s_code:=stockcode;
END IF;
IF t_time is null THEN
t_time:=time;
END IF; SELECT stockcode scode1,Time t_time,up mup,updown udown,shakeup sup1,id id_1 from stock_shanghai_newprice
WHERE stockcode=s_code and time=to_date(t_time,'yyyymmdd hh24miss');
--where stockcode=600781;
--where rownum<2000;
--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
(RTRIM (TO_CHAR (((newprice - closeprice) / closeprice) * 100,'FM99999999990.99'),'.') != each_rec.mup or
RTRIM (TO_CHAR (newprice - closeprice, 'FM99999999990.99'),'.') != each_rec.udown or
RTRIM (TO_CHAR (((maxprice - minprice) / closeprice) * 100, 'FM99999999990.99'),'.' ) != each_rec.sup1)
AND stockcode = each_rec.scode1
AND TIME = each_rec.t_time
AND id = each_rec.id_1 ;
DBMS_OUTPUT.put_line (each_rec.scode1||','||each_rec.t_time||','||'Please check the data!');
EXCEPTION
WHEN ZERO_DIVIDE
THEN
DBMS_OUTPUT.put_line (each_rec.scode1||','||each_rec.t_time||','||'Can not divide zero!');
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('Data correctly!');
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(each_rec.scode1||','||each_rec.t_time||','||'Unknown Error!');
END; END LOOP;
--CLOSE cur_tab;
END;
(
s_code stock_shanghai_newprice.stockcode%TYPE,
t_time stock_shanghai_newprice.time%TYPE
)
IS
mup stock_shanghai_newprice.up%TYPE;
udown stock_shanghai_newprice.updown%TYPE;
sup1 stock_shanghai_newprice.shakeup%TYPE;
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;
id_1 stock_shanghai_newprice.id%TYPE;
CURSOR cur_tab
IS
IF s_code is null THEN
s_code:=stockcode;
END IF;
IF t_time is null THEN
t_time:=time;
END IF; SELECT stockcode scode1,Time t_time,up mup,updown udown,shakeup sup1,id id_1 from stock_shanghai_newprice
WHERE stockcode=s_code and time=to_date(t_time,'yyyymmdd hh24miss');
--where stockcode=600781;
--where rownum<2000;
--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
(RTRIM (TO_CHAR (((newprice - closeprice) / closeprice) * 100,'FM99999999990.99'),'.') != each_rec.mup or
RTRIM (TO_CHAR (newprice - closeprice, 'FM99999999990.99'),'.') != each_rec.udown or
RTRIM (TO_CHAR (((maxprice - minprice) / closeprice) * 100, 'FM99999999990.99'),'.' ) != each_rec.sup1)
AND stockcode = each_rec.scode1
AND TIME = each_rec.t_time
AND id = each_rec.id_1 ;
DBMS_OUTPUT.put_line (each_rec.scode1||','||each_rec.t_time||','||'Please check the data!');
EXCEPTION
WHEN ZERO_DIVIDE
THEN
DBMS_OUTPUT.put_line (each_rec.scode1||','||each_rec.t_time||','||'Can not divide zero!');
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('Data correctly!');
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(each_rec.scode1||','||each_rec.t_time||','||'Unknown Error!');
END; END LOOP;
--CLOSE cur_tab;
END;
IS
SELECT stockcode scode1,Time t_time,up mup,updown udown,shakeup sup1,id id_1 from stock_shanghai_newprice
WHERE stockcode=NVL(s_sode, stockcode) and time=NVL(to_date(t_time,'yyyymmdd hh24miss'),time);