CREATE OR REPLACE PROCEDURE PRO_WEEK_MAX_YIELD(STOCKID IN CHAR,AYZER_ID IN NUMBER)
AS
BEGIN
DECLARE
AVERAGE number(10,3); -- 推荐日均价
TOTAL_BALANCE number(16,3); --成交金额
MAX_PRICE number(10,3); --本周最高成交价
LAST_CLOSE_PRICE number(10,3); --上周收盘价
TOTAL_QTY number(38,0); --成交数量
WEEKMAXYIELD number(10,3); --选手周最大化收益率
STOCKCOUNT number(38,0); --选手推荐股票数量
CURSOR C_CHOICE(AYZERID NUMBER) IS SELECT STOCK_CODE FROM T_SKRACE_CHOICE WHERE ANALYZER_ID=AYZERID;
STOCKCODE CHAR(6);
BEGIN
--选手推荐股票数量
SELECT COUNT(1) INTO STOCKCOUNT FROM T_SKRACE_CHOICE WHERE ANALYZER_ID=AYZER_ID;
IF STOCKCOUNT<>0
THEN
OPEN C_CHOICE(AYZER_ID);
LOOP
FETCH C_CHOICE INTO STOCKCODE;
EXIT WHEN C_CHOICE%NOTFOUND;
--本周最高成交价
DBMS_OUTPUT.put_line(STOCKCODE);
******* SELECT LAST_CLOSE_PRICE INTO LAST_CLOSE_PRICE FROM T_SKRACE_STOCK WHERE STOCK_CODE=STOCKCODE; END LOOP;
CLOSE C_CHOICE;
END IF;
END;
END PRO_WEEK_MAX_YIELD;
打星号的这一行总是报取不到数据,但是我输出变量STOCKCODE,请教高手指点一下! 谢谢了
AS
BEGIN
DECLARE
AVERAGE number(10,3); -- 推荐日均价
TOTAL_BALANCE number(16,3); --成交金额
MAX_PRICE number(10,3); --本周最高成交价
LAST_CLOSE_PRICE number(10,3); --上周收盘价
TOTAL_QTY number(38,0); --成交数量
WEEKMAXYIELD number(10,3); --选手周最大化收益率
STOCKCOUNT number(38,0); --选手推荐股票数量
CURSOR C_CHOICE(AYZERID NUMBER) IS SELECT STOCK_CODE FROM T_SKRACE_CHOICE WHERE ANALYZER_ID=AYZERID;
STOCKCODE CHAR(6);
BEGIN
--选手推荐股票数量
SELECT COUNT(1) INTO STOCKCOUNT FROM T_SKRACE_CHOICE WHERE ANALYZER_ID=AYZER_ID;
IF STOCKCOUNT<>0
THEN
OPEN C_CHOICE(AYZER_ID);
LOOP
FETCH C_CHOICE INTO STOCKCODE;
EXIT WHEN C_CHOICE%NOTFOUND;
--本周最高成交价
DBMS_OUTPUT.put_line(STOCKCODE);
******* SELECT LAST_CLOSE_PRICE INTO LAST_CLOSE_PRICE FROM T_SKRACE_STOCK WHERE STOCK_CODE=STOCKCODE; END LOOP;
CLOSE C_CHOICE;
END IF;
END;
END PRO_WEEK_MAX_YIELD;
打星号的这一行总是报取不到数据,但是我输出变量STOCKCODE,请教高手指点一下! 谢谢了
两个名字可以一样吗?
AS
VERAGE number(10,3); -- 推荐日均价
TOTAL_BALANCE number(16,3); --成交金额
MAX_PRICE number(10,3); --本周最高成交价
V_LAST_CLOSE_PRICE number(10,3); --上周收盘价
TOTAL_QTY number(38,0); --成交数量
WEEKMAXYIELD number(10,3); --选手周最大化收益率
STOCKCOUNT number(38,0); --选手推荐股票数量
CURSOR C_CHOICE(AYZERID NUMBER) IS SELECT STOCK_CODE FROM T_SKRACE_CHOICE WHERE ANALYZER_ID=AYZERID;
STOCKCODE CHAR(6);BEGIN --选手推荐股票数量
SELECT COUNT(1) INTO STOCKCOUNT FROM T_SKRACE_CHOICE WHERE ANALYZER_ID=AYZER_ID;
IF STOCKCOUNT <>0
THEN
OPEN C_CHOICE(AYZER_ID);
LOOP
FETCH C_CHOICE INTO STOCKCODE;
EXIT WHEN C_CHOICE%NOTFOUND;
--本周最高成交价
DBMS_OUTPUT.put_line(STOCKCODE);
******* SELECT LAST_CLOSE_PRICE INTO V_LAST_CLOSE_PRICE FROM T_SKRACE_STOCK WHERE STOCK_CODE=STOCKCODE; END LOOP;
CLOSE C_CHOICE;
END IF; END PRO_WEEK_MAX_YIELD;
/
show err
/
LOOP
--本周最高成交价
STOCKCODE := cur_result.STOCK_CODE;
DBMS_OUTPUT.put_line(STOCKCODE);
SELECT LAST_CLOSE_PRICE INTO V_LAST_CLOSE_PRICE FROM T_SKRACE_STOCK WHERE STOCK_CODE = STOCKCODE; END LOOP; 用这段LOOP替换下