CREATE OR REPLACE PROCEDURE CHECK_NEWPRICE_22
(
s_code stock_shanghai_newprice.stockcode%TYPE,
t_time2 stock_shanghai_newprice.stockcode%TYPE
)
IS
nprice number;
vlum number;
amt number;
maxpri number;
minpri number;
closepri number;
openpri number;
rat number;
scode sh_origin_stock.stockcode%TYPE;
t_time1 sh_origin_stock.indicationtime%TYPE;
scode1 stock_shanghai_newprice.stockcode%TYPE;
t_time stock_shanghai_newprice.time%TYPE;
t_time3 stock_shanghai_newprice.time%TYPE;
id_1 stock_shanghai_newprice.id%TYPE;
id_2 stock_shanghai_newprice.id%TYPE;
CURSOR cur_tab
IS
SELECT id id_1,time t_time3
FROM stock_shanghai_newprice
WHERE stockcode=NVL(s_code, stockcode) and time=NVL(to_date(t_time2,'yyyymmdd hh24miss'),time);
/*
SELECT time t_time,stockcode scode1,newprice nprice,volum vlum,amount amt,maxprice maxpri,
minprice minpri,closeprice closepri,openprice openpri,ratio rat
FROM stock_shanghai_newprice
WHERE stockcode=NVL(s_code, stockcode) and time=NVL(to_date(t_time2,'yyyymmdd hh24miss'),time);
-- and stockcode=000001;*/
BEGIN
FOR each_rec IN cur_tab
LOOP
BEGIN
-- DBMS_OUTPUT.put_line (each_rec.t_time3);
SELECT stockcode,newprice,volum,amount,maxprice,minprice,closeprice,openprice,ratio
INTO scode1,nprice,vlum,amt,maxpri,minpri,closepri,openpri,rat
FROM stock_shanghai_newprice
WHERE id=each_rec.id_1
AND stockcode=NVL(s_code, stockcode) and time=each_rec.t_time3;
DBMS_OUTPUT.put_line(each_rec.id_1);
DBMS_OUTPUT.put_line(vlum);
SELECT stockcode ,indicationtime
INTO scode,t_time1
FROM sh_origin_stock
WHERE
( newprice!=nprice or dealvolum!=vlum or amount!=amt
or maxprice!=maxpri or minprice!=minpri or closeprice!=closepri
or openprice!=openpri or ratio!=rat )
AND stockcode = scode1
AND indicationtime = t_time;
DBMS_OUTPUT.put_line('feng');
--AND id = each_rec.id_1 ;
DBMS_OUTPUT.put_line (scode1||','||each_rec.t_time3||','||'Please check the data!');
EXCEPTION
WHEN ZERO_DIVIDE
THEN
DBMS_OUTPUT.put_line (scode1||','||each_rec.t_time3||','||'Can not divide zero!');
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line (scode1||','||each_rec.t_time3||','||'Data correctly!');
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(scode1||','||each_rec.t_time3||','||'Unknown Error!'); END;
end loop;
end;我怎样让红色部分取得蓝色部分的值
(
s_code stock_shanghai_newprice.stockcode%TYPE,
t_time2 stock_shanghai_newprice.stockcode%TYPE
)
IS
nprice number;
vlum number;
amt number;
maxpri number;
minpri number;
closepri number;
openpri number;
rat number;
scode sh_origin_stock.stockcode%TYPE;
t_time1 sh_origin_stock.indicationtime%TYPE;
scode1 stock_shanghai_newprice.stockcode%TYPE;
t_time stock_shanghai_newprice.time%TYPE;
t_time3 stock_shanghai_newprice.time%TYPE;
id_1 stock_shanghai_newprice.id%TYPE;
id_2 stock_shanghai_newprice.id%TYPE;
CURSOR cur_tab
IS
SELECT id id_1,time t_time3
FROM stock_shanghai_newprice
WHERE stockcode=NVL(s_code, stockcode) and time=NVL(to_date(t_time2,'yyyymmdd hh24miss'),time);
/*
SELECT time t_time,stockcode scode1,newprice nprice,volum vlum,amount amt,maxprice maxpri,
minprice minpri,closeprice closepri,openprice openpri,ratio rat
FROM stock_shanghai_newprice
WHERE stockcode=NVL(s_code, stockcode) and time=NVL(to_date(t_time2,'yyyymmdd hh24miss'),time);
-- and stockcode=000001;*/
BEGIN
FOR each_rec IN cur_tab
LOOP
BEGIN
-- DBMS_OUTPUT.put_line (each_rec.t_time3);
SELECT stockcode,newprice,volum,amount,maxprice,minprice,closeprice,openprice,ratio
INTO scode1,nprice,vlum,amt,maxpri,minpri,closepri,openpri,rat
FROM stock_shanghai_newprice
WHERE id=each_rec.id_1
AND stockcode=NVL(s_code, stockcode) and time=each_rec.t_time3;
DBMS_OUTPUT.put_line(each_rec.id_1);
DBMS_OUTPUT.put_line(vlum);
SELECT stockcode ,indicationtime
INTO scode,t_time1
FROM sh_origin_stock
WHERE
( newprice!=nprice or dealvolum!=vlum or amount!=amt
or maxprice!=maxpri or minprice!=minpri or closeprice!=closepri
or openprice!=openpri or ratio!=rat )
AND stockcode = scode1
AND indicationtime = t_time;
DBMS_OUTPUT.put_line('feng');
--AND id = each_rec.id_1 ;
DBMS_OUTPUT.put_line (scode1||','||each_rec.t_time3||','||'Please check the data!');
EXCEPTION
WHEN ZERO_DIVIDE
THEN
DBMS_OUTPUT.put_line (scode1||','||each_rec.t_time3||','||'Can not divide zero!');
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line (scode1||','||each_rec.t_time3||','||'Data correctly!');
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE(scode1||','||each_rec.t_time3||','||'Unknown Error!'); END;
end loop;
end;我怎样让红色部分取得蓝色部分的值
那就定义一个变量,储存起来就好了。
而且在蓝色部分不是有INTO句嘛,INTO后面的变量已经把值储存起来了
除非蓝色部分本身就得不到值,也就是没有满足条件记录
在根据值直接把红色部分的SQL取出来不要into只要select一下看有没有结果就知道了.
估计是不符合条件才没有结果.