Your Location is: Home > Sql

I need a cursor maybe, the exact fetch returns more than requested number of rows [closed]

From: Yugoslavia View: 1016 SamiAnas 

Question

After execution it is supposed to fill the stock_history table with multiple rows of data. It is supposed to go through the procedure and insert the data gathered from stock listing and inserted into the stock_history table

CREATE OR REPLACE PROCEDURE open_trading_day(
p_stock_exchange IN VARCHAR2
)

IS
v_stock_ex_id   number(6);
l_stock_ex_id   number(6);
v_stock_id      STOCK_LISTING.stock_id%TYPE;
l_market_price  company.starting_price%TYPE;

BEGIN



SELECT stock_ex_id
INTO l_stock_ex_id
FROM stock_exchange
WHERE symbol = p_stock_exchange;

SELECT stock_ex_id
INTO v_stock_ex_id
FROM stock_listing
WHERE stock_ex_id = l_stock_ex_id;


SELECT stock_id
into v_stock_id
FROM stock_listing
WHERE stock_ex_id = v_stock_ex_id;



SELECT starting_price
INTO l_market_price
FROM company
WHERE stock_id = v_stock_id;



INSERT INTO STOCK_HISTORY(stock_ex_id, stock_id, time_open, open)
VALUES (v_stock_ex_id, v_stock_id , sysdate, l_market_price);



END;
/

exec open_trading_day('AFE');

Best answer

Actually, unless I'm wrong, you need a JOIN and direct INSERT into the target table:

create or replace procedure open_trading_Day (p_stock_exchange in varchar2)
is
begin
  insert into stock_history
    (stock_ex_id, stock_id, time_open, open) 
  select sl.stock_ex_id,
         sl.stock_id,
         sysdate, 
         c.starting_price
  from stock_exchange se join stock_listing sl on sl.stock_ex_id = se.stock_ex_id
                         join company c        on c.stock_id     = sl.stock_id
  where se.symbol = p_stock_exchange;
end;