Run SQL Code Block with Variables in Oracle SQL Developer

DECLARE   
  p_cart_id NUMBER:= 125330;
  p_cart_item_id NUMBER;  
BEGIN
  FOR i IN 1..200 LOOP
    -- get next sequence
    p_cart_item_id := tbl_cart_item.NEXTVAL;
 
    --insert new line from select	
    INSERT INTO TBL_CART_ITEM(cart_item_id,cart_id,item,qty, line_no)
    SELECT t.* FROM (
      SELECT  cart_item_id,cart_id,item,qty,line_no+1 FROM TBL_CART_ITEM WHERE cart_id = p_cart_id ORDER BY  line_no DESC
    ) t WHERE rownum =1 ;   
 
    --print out in DBMS
    DBMS_OUTPUT.PUT_LINE ( 'loop:' || i || ' : p_cart_item_id: ' || TO_CHAR(p_cart_item_id) );     
 
    commit;
  END LOOP;    
END;

Le Kevin

software engineer, photographer

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *