Oracle 9i SP實例

create or replace PROCEDURE "SP_SETSCORE" (strQUESTAPP_NO VARCHAR2,strID VARCHAR2,o_Date Date)
/****************************************************
' 函數名稱 : 重計分數
' 功能說明 :　重新計算商品的分數
' 參數說明 :
'  範例'
   版本變更：
'  xx. YYYY/MM/DD   VER     AUTHOR          COMMENTS
'  1.  2008/06/17   1.00       xxxx              CREATE
*****************************************************/
is
--問卷調查評語資料檔
s_COMMODITY_NO VARCHAR2(20):='';--商品編號
s_HIDE_SCORE char(1):='';--是否隱藏不雅評分
f_AVG_SCORE float:=0.0;--綜合平均分數
--商品評分及人數統計檔
f_CAVG_SCORE float:=0.0;--商品評比總分
i_TOTAL_COUNT NUMBER:=0;--評比總人數
i_COMM_RATE_1 NUMBER:=0;--落點人數
i_COMM_RATE_2 NUMBER:=0;--落點人數
i_COMM_RATE_3 NUMBER:=0;--落點人數
i_COMM_RATE_4 NUMBER:=0;--落點人數
--商品題目資料檔
--i_TOTAL_SCORE NUMBER:=0;--本題目的評比總分
--i_TOTAL_COUNT2 NUMBER:=0;--本題目的評比總人數
begin

     SELECT A.COMMODITY_NO,A.AVG_SCORE ,A.HIDE_SCORE
     INTO s_COMMODITY_NO,f_AVG_SCORE,s_HIDE_SCORE
     FROM QUEST_APPRAISAL A
     WHERE A.STATUS='Y' AND A.QUESTAPP_NO=strQUESTAPP_NO;
    
     -- 如果以隱藏評分，就跳出
     IF s_HIDE_SCORE='Y' THEN
        RETURN;
     END IF;
     --隱藏評分
     UPDATE QUEST_APPRAISAL SET HIDE_SCORE='Y',MDY_USER=strID,MDY_DATE=o_Date WHERE QUESTAPP_NO=strQUESTAPP_NO;
    
     --商品評分及人數統計檔
     SELECT B.CAVG_SCORE ,B.TOTAL_COUNT ,B.COMM_RATE_1,B.COMM_RATE_2,B.COMM_RATE_3,B.COMM_RATE_4
     INTO f_CAVG_SCORE,i_TOTAL_COUNT,i_COMM_RATE_1,i_COMM_RATE_2,i_COMM_RATE_3,i_COMM_RATE_4
     FROM COMMSCORE B WHERE B.COMMODITY_NO=s_COMMODITY_NO ;
    
     IF f_AVG_SCORE>=1 AND f_AVG_SCORE<2 THEN--(1 ~ 2)
        i_COMM_RATE_1:=i_COMM_RATE_1-1;
     ELSE
        IF f_AVG_SCORE>=2 AND f_AVG_SCORE<3 THEN--(2 ~ 3)
           i_COMM_RATE_2:=i_COMM_RATE_2-1;
        ELSE
             IF f_AVG_SCORE>=3 AND f_AVG_SCORE<4 THEN--(3 ~ 4)
                i_COMM_RATE_3:=i_COMM_RATE_3-1;
             ELSE
                 IF f_AVG_SCORE>=4 AND f_AVG_SCORE<=5 THEN--(4 ~ 5)
                 i_COMM_RATE_4:=i_COMM_RATE_4-1;
                 END IF;
             END IF;
        END IF;       
     END IF;
    
     f_CAVG_SCORE:=f_CAVG_SCORE-f_AVG_SCORE;
     i_TOTAL_COUNT:=i_TOTAL_COUNT-1;
    
     UPDATE COMMSCORE
     SET CAVG_SCORE=f_CAVG_SCORE,TOTAL_COUNT=i_TOTAL_COUNT
     ,COMM_RATE_1=i_COMM_RATE_1,COMM_RATE_2=i_COMM_RATE_2
     ,COMM_RATE_3=i_COMM_RATE_3,COMM_RATE_4=i_COMM_RATE_4
     ,MDY_USER=strID,MDY_DATE=o_Date
     WHERE COMMODITY_NO=s_COMMODITY_NO ;

     --問卷調查評分資料檔
     DECLARE CURSOR C1 IS
            SELECT * FROM QUEST_SURVEY  WHERE QUESTAPP_NO=strQUESTAPP_NO;
     R1 C1%ROWTYPE;      
     BEGIN
      OPEN C1;--開啟游標C1
      LOOP   --letbbilliard
      FETCH C1 INTO R1;
      EXIT WHEN C1%NOTFOUND;--如果游標紀錄取完推出回圈 
 
         UPDATE COMM_QUEST SET TOTAL_SCORE=TOTAL_SCORE-R1.SCORE,TOTAL_COUNT=TOTAL_COUNT-1
         WHERE COMM_NO=s_COMMODITY_NO AND QUEST_NO=R1.QUESTION_NO ;
        
      END LOOP;
      CLOSE C1;--關閉游標C1
      END;
      COMMIT;
end SP_SETSCORE;