create or replace PROCEDURE UPD_RELATEDPPS
IS

TYPE tp_tab_product IS  TABLE  OF  product_ranking.product_id%TYPE
   INDEX   BY   BINARY_INTEGER;
  
-- product array
tab_products   tp_tab_product;

--supplier cursor
CURSOR cur_supplier_id IS
    SELECT distinct(supplier_id)
    FROM product_ranking;
   
--v_supp_id  cur_supplier_id%ROWTYPE;
v_supp_id product_ranking.supplier_id%TYPE;
--supplier count
i BINARY_INTEGER := 0;

--product cursor
CURSOR cur_product_id (supp_id product_ranking.supplier_id%TYPE)IS
SELECT product_id
  FROM product_ranking
 WHERE supplier_id = supp_id;
v_pdt_id  product_ranking.product_id%TYPE;
--product count
j BINARY_INTEGER := 0;
pdt_cnt number default 0;

--key array for related PP (3 recs)
TYPE tp_pdt_key IS  TABLE  OF  number
   INDEX   BY   BINARY_INTEGER;
data_pdt_key tp_pdt_key;

--function to get relatedPP's key
FUNCTION getRelatePPKey(v_pdt_cnt number default 0,
                        pdt_k number default 0
                          ) RETURN tp_pdt_key AS
v_pdt_key tp_pdt_key;
BEGIN
v_pdt_key.delete;
IF  v_pdt_cnt = 2 THEN
   IF pdt_k =1 THEN
   v_pdt_key(1) := 2;
   ELSIF pdt_k = 2 THEN
   v_pdt_key(1) := 1;
   END IF;
END IF;

IF  v_pdt_cnt = 3 THEN
 IF pdt_k =1 THEN
   v_pdt_key(1) := 2;
   v_pdt_key(2) := 3;
   ELSIF pdt_k = 2 THEN
      v_pdt_key(1) := 3;
      v_pdt_key(2) := 1;
  ELSIF pdt_k = 3 THEN
      v_pdt_key(1) := 1;
      v_pdt_key(2) := 2;
   END IF;
END IF;

IF  v_pdt_cnt > 3 THEN
    IF pdt_k <= v_pdt_cnt - 3 THEN
      v_pdt_key(1) := pdt_k + 1 ;
      v_pdt_key(2) := pdt_k + 2 ;
      v_pdt_key(3) := pdt_k + 3 ;
    ELSIF pdt_k = v_pdt_cnt - 2 THEN
      v_pdt_key(1) := pdt_k + 1 ;
      v_pdt_key(2) := pdt_k + 2 ;
      v_pdt_key(3) := 1 ;
    ELSIF pdt_k = v_pdt_cnt - 1 THEN
      v_pdt_key(1) := pdt_k + 1 ;
      v_pdt_key(2) := 1 ;
      v_pdt_key(3) := 2 ;
    ELSIF pdt_k = v_pdt_cnt  THEN
      v_pdt_key(1) := 1 ;
      v_pdt_key(2) := 2 ;
      v_pdt_key(3) := 3 ;
    END IF;
   
END IF;

return v_pdt_key;
exception
  when others then
       return v_pdt_key;
END;

BEGIN
OPEN cur_supplier_id;
LOOP --supplier loop
   FETCH cur_supplier_id INTO v_supp_id;
   EXIT WHEN cur_supplier_id%NOTFOUND;
   --DBMS_OUTPUT.PUT_LINE('supplier_id: ' || v_supp_id);

   tab_products.delete;
   OPEN cur_product_id(v_supp_id);
   j := 0;
   LOOP
       FETCH cur_product_id INTO v_pdt_id;
       EXIT WHEN cur_product_id%NOTFOUND;
       --DBMS_OUTPUT.PUT_LINE('==product_id:  '||(j+1)  ||' ' || v_pdt_id);
       j := j+1;
       tab_products(j):= v_pdt_id;
   END LOOP;-- end of product loop
   CLOSE cur_product_id;

   pdt_cnt := tab_products.COUNT ;

/******************update related PPs**********************************/
    FOR k IN 1..pdt_cnt LOOP
       data_pdt_key.delete;
       data_pdt_key := getRelatePPKey (pdt_cnt,k);

         IF (data_pdt_key.COUNT = 3) THEN
         UPDATE product_ranking
           SET related_product_id1 = tab_products(data_pdt_key(1)),
               related_product_id2 = tab_products(data_pdt_key(2)),
               related_product_id3 = tab_products(data_pdt_key(3))
         WHERE supplier_id=v_supp_id
           AND product_id=tab_products(k);
         END IF;
        
         IF (data_pdt_key.COUNT = 2) THEN
         UPDATE product_ranking
           SET related_product_id1 = tab_products(data_pdt_key(1)),
               related_product_id2 = tab_products(data_pdt_key(2))
         WHERE supplier_id=v_supp_id
           AND product_id=tab_products(k);
         END IF;
        
         IF (data_pdt_key.COUNT = 1) THEN
         UPDATE product_ranking
            SET related_product_id1 = tab_products(data_pdt_key(1))
          WHERE supplier_id=v_supp_id
            AND product_id=tab_products(k);
         END IF;

    END LOOP;--end of pdt_cnt loop

COMMIT;
/****************update related PPs*****************/

END LOOP;-- end of supplier loop

CLOSE cur_supplier_id;

EXCEPTION
    WHEN NO_DATA_FOUND THEN
       DBMS_OUTPUT.PUT_LINE('End of updating related products in product_ranking');
END; -- Procedure


GRANT EXECUTE ON UPD_RELATEDPPS TO AAAA;

----------------------------------------------------------------------------------------

1.cursor : 
CURSOR cur_product_id (supp_id product_ranking.supplier_id%TYPE)IS
SELECT product_id
  FROM product_ranking
 WHERE supplier_id = supp_id;
----------
 OPEN cur_product_id(v_supp_id);
   LOOP
       FETCH cur_product_id INTO v_pdt_id;
       EXIT WHEN cur_product_id%NOTFOUND;
       DBMS_OUTPUT.PUT_LINE('---------snow ------------');
       tab_products(j):= v_pdt_id;
   END LOOP;
   CLOSE cur_product_id;

note : FETCH cur_product_id INTO v_pdt_id;
       EXIT WHEN cur_product_id%NOTFOUND; 这两句要挨在一起，否则cursor 会多一条记录，（最后一个记录重复出现两次）

2.funcion define :
 PROCEDURE FUNNEL_LOGIC_UPD_RELATEDPPS
 IS
  function ABC(){
  }
 BEGIN
 END

3.array define ，数组清空
 
 定义：
 TYPE tp_pdt_key IS  TABLE  OF  number
   INDEX   BY   BINARY_INTEGER;
 变量声明：
 data_pdt_key tp_pdt_key;

 数组传递：
 data_pdt_key := getRelatePPKey (pdt_cnt,k);

 数组清空：
data_pdt_key.delete;

4.procedure 结构：

create or replace PROCEDURE FUNNEL_LOGIC_UPD_RELATEDPPS
IS
--type definition
--variable declare
BEGIN

END;