Click here to Skip to main content
15,886,362 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi, Professionals
hope all of you are good.
i am new to oracle and PL SQL, i want to update the received item in stock in previous date by using for loop. i want that when i press the button it will update the record of the given date and id of a specific product and also update the records in next date prior to the maximum date in the table. please check the below code in when-button-pressed trigger. everything is working fine but the update statement does not do anything.
please help me to get me out of this stuck.

What I have tried:

DECLARE 
	DATE_DIF NUMBER;
	MAX_DAT DATE;
BEGIN
	SELECT MAX(DATED) INTO MAX_DAT FROM STOCK;
	 DATE_DIF := MAX_DAT - :TEXT_TOP_DATED;
	
	FOR CNT IN 1..DATE_DIF
	LOOP
		SELECT OPENING, RECIEVED , CONSUME, CLOSING,DATED INTO :OPENING, :RECIEVED, :CONSUME, :CLOSING, :DATED FROM STOCK WHERE DATED = :TEXT_TOP_DATED + 1 AND PRODUCT_ID = :LOV_PNAME;
		:CLOSING := ( :OPENING + :RECIEVED ) - :CONSUME;
		UPDATE STOCK SET RECIEVED = :RECIEVED, CONSUME = :CONSUME , CLOSING = :CLOSING WHERE DATED = :TEXT_TOP_DATED AND PRODUCT_ID = :LOV_PNAME;
		:TEXT_TOP_DATED := :TEXT_TOP_DATED + 1;
		END LOOP;
END;
Posted
Updated 8-Mar-18 19:51pm
v2
Comments
CHill60 8-Mar-18 7:34am    
My advice would be to NOT use a loop of any description. Oracle is set-based. Use JOINs and WHERE clauses to achieve what you want.
Muhammd Aamir 8-Mar-18 22:46pm    
kindly show me some code so i can resolve my problem.
CHill60 9-Mar-18 3:27am    
I wrote an article about avoiding loops - Processing Loops in SQL Server[^].
It's written using SQL Server but most of the principles apply to any T-SQL languague, including Oracle, but you will have to look up the specific documentation.
Muhammd Aamir 9-Mar-18 23:04pm    
@chill60 Thanks for being here for help. i will try it. let see
Muhammd Aamir 8-Mar-18 22:47pm    
i don't have any kind of idea about that

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900