Home » SQL & PL/SQL » SQL & PL/SQL » Please help me to implement this. (Oracle 11g)
Please help me to implement this. [message #584907] Mon, 20 May 2013 07:36 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi Experts,

In my below code the procedures "total_score_proc" and "CopyInternalScores" are calling "score_proc" procedure 50 times
for different variable values.

Instead of calling the "score_proc" procedure 50 times.
I want to hold the values in to collection , defining it in package and call that procedure only once.

Please help me how to implement "score_proc" using bulk collect.

Thanks in advance.
CREATE OR REPLACE PACKAGE total_score_pkg 
IS

PROCEDURE total_score_proc(pBUID       IN STAGING_ORDER_DATA.BUID%TYPE,
                         OrderNum    IN STAGING_ORDER_DATA.ORDER_NUM%TYPE,
                         ReturnValue OUT VARCHAR2);
PROCEDURE score_proc(pBUID       IN score.buid%TYPE,
                        OrderNum    IN score.order_num%TYPE,
                        ScoreType   IN VARCHAR2,
                        score       IN score.velocity_score%TYPE);
  PROCEDURE CopyInternalScores
  (pBUID    IN STAGING_ORDER_DATA.BUID%TYPE,
   OrderNum IN STAGING_ORDER_DATA.ORDER_NUM%TYPE);
END;
/

CREATE OR REPLACE PACKAGE BODY total_score_pkg 
IS
PROCEDURE total_score_proc
 (pBUID  IN STAGING_ORDER_DATA.BUID%TYPE,
  OrderNum  IN STAGING_ORDER_DATA.ORDER_NUM%TYPE,
  ReturnValue OUT VARCHAR2) 
IS
   tLocalChannel   STAGING_ORDER_DATA.LOCAL_CHANNEL%TYPE;
    tZipCode        STAGING_ORDER_DATA.ZIP_CODE%TYPE;
    tCountryCode    STAGING_ORDER_DATA.COUNTRY_CODE%TYPE;
    tFreeEmail      STAGING_ONLINE_DATA.FREE_EMAIL_FLAG%TYPE;
    tPayCode        ORDERS.PAY_CODE%TYPE;
    tOrderAmt       ORDERS.ORDER_AMOUNT%TYPE;
    tProdDesc       ORDERS.PRODUCT_DESC%TYPE;
    tShipCode       ORDERS.SHIP_CODE%TYPE;
    tSrcSys         ORDERS.SOURCE_SYSTEM%TYPE;
    tSTDiff         ORDERS.ST_ADDRESS_DIFF_FLAG%TYPE;
    tCCDiff         ORDERS.CC_ADDRESS_DIFF_FLAG%TYPE;
    tBTDateDiff     NUMBER := 0;
    tAVS            VARCHAR2(50);
    tEmailDomain    STAGING_ONLINE_DATA.EMAIL_ADDRESS%TYPE;
    tCID            VARCHAR2(50);
    tIPContinent    STAGING_ONLINE_DATA.IP_CONTINENT%TYPE;
    tIPCountry      STAGING_ONLINE_DATA.IP_COUNTRY%TYPE;
    tEmailAddress   STAGING_ONLINE_DATA.EMAIL_ADDRESS%TYPE;
    tDeviceTimezone IOV_DEVICE_TRAN_DETAIL.DEVICE_TIMEZONE%TYPE;
    tDeviceBrowserLanguage IOV_DEVICE_TRAN_DETAIL.DEVICE_BROWSER_LANG%TYPE;
    tDeviceOS IOV_DEVICE_TRAN_DETAIL.DEVICE_OS%TYPE;
    tIPRoutingType  STAGING_ONLINE_DATA.IP_ROUTING_TYPE%TYPE;
    tScore          SCORE.VENDOR_COMBINED_SCORE%TYPE;
    tAmtBucketRange VARCHAR2(50);
    tTrendAmtBucket MASTER_BUCKET.BUCKET_TYPE%TYPE := 'Trend Order Amount Bucket';
    tZipDigits      CONFIG_PARAMS.VARIABLE_NAME%TYPE := 'TREND_ZIP_DIGITS';
    tCleanProdDesc  ORDERS.PRODUCT_DESC%TYPE;
    tBIN            TEMP_INTSCORING_CC.BIN_NUM%TYPE;
    tScoringID      STAGING_ORDER_DATA.SCORING_ID%TYPE;
    tMinOrderNum    STAGING_ORDER_DATA.ORDER_NUM%TYPE;
    tSKUNum         STAGING_ORDER_DATA.SKU_NUM%TYPE;
    tExists         NUMBER := 0;
    tCount          NUMBER := 0;
    tIncludeZipDigits NUMBER := 3;
    tDeductionPoints  TREND.DEDUCTION_POINTS%TYPE := 0;
    tFrdPct           TREND.FRAUD_TOTAL_PERCENTAGE%TYPE := 0;
    tFrdWkdPct        TREND.FRAUD_WORKED_PERCENTAGE%TYPE := 0;

    tTrendScore NUMBER := 5000;

  BEGIN
    ReturnValue := 'get order header';

    SELECT stgOrder.LOCAL_CHANNEL,
           TRUNC(SYSDATE - ADDRESS_CREATE_DATE) "Dy"
      INTO tLocalChannel, tBTDateDiff
      FROM STAGING_ORDER_DATA stgOrder
     WHERE stgOrder.BUID = pBUID AND stgOrder.ORDER_NUM = OrderNum AND
           stgOrder.ADDRESS_TYPE = 'B';

    SELECT stgOrder.ZIP_CODE,
           stgOrder.COUNTRY_CODE,
           NVL(stgOnline.FREE_EMAIL_FLAG, ''),
           stgOnline.IP_CONTINENT,
           stgOnline.IP_COUNTRY,
           stgOnline.EMAIL_ADDRESS,
           stgOnline.IP_ROUTING_TYPE
      INTO tZipCode,
           tCountryCode,
           tFreeEmail,
           tIPContinent,
           tIPCountry,
           tEmailAddress,
           tIPRoutingType
      FROM STAGING_ORDER_DATA stgOrder 
      LEFT OUTER JOIN STAGING_ONLINE_DATA stgOnline 
      ON stgOrder.BUID = stgOnline.BUID 
      AND stgOrder.ORDER_NUM = stgOnline.ORDER_NUM
     WHERE stgOrder.ADDRESS_TYPE = 'S' 
     AND stgOrder.BUID = pBUID AND
     stgOrder.ORDER_NUM = OrderNum;

       SELECT iov.DEVICE_TIMEZONE,
           iov.DEVICE_BROWSER_LANG,
           iov.DEVICE_OS
      INTO tDeviceTimezone,
           tDeviceBrowserLanguage,
           tDeviceOS
      FROM STAGING_ORDER_DATA stgOrder 
      LEFT OUTER JOIN Iov_Device_Tran_Detail iov 
      ON stgOrder.BUID = iov.BUID 
      AND stgOrder.ORDER_NUM = iov.ORDER_NUM
     WHERE stgOrder.ADDRESS_TYPE = 'S' 
     AND stgOrder.BUID = pBUID
     AND stgOrder.ORDER_NUM = OrderNum;

    SELECT (ord.ORDER_AMOUNT * exchg.EXCHANGE_RATE),
           ord.PAY_CODE,
           ord.PRODUCT_DESC,
           ord.SHIP_CODE,
           ord.SOURCE_SYSTEM,
           ord.ST_ADDRESS_DIFF_FLAG,
           ord.CC_ADDRESS_DIFF_FLAG,
           NVL(ord.SKU_NUM,''),
           NVL(cc.AVS_CC_CODE, ''),
           NVL(cc.CID_CODE, ''),
           NVL(s.VENDOR_COMBINED_SCORE, -1)
      INTO tOrderAmt,
           tPayCode,
           tProdDesc,
           tShipCode,
           tSrcSys,
           tSTDiff,
           tCCDiff,
           tSKUNum,
           tAVS,
           tCID,
           tScore
      FROM MASTER_CURRENCY exchg,
           ORDERS          ord LEFT OUTER JOIN (SELECT BUID,
                                                       ORDER_NUM,
                                                       MIN(AVS_CC_CODE) AS AVS_CC_CODE,
                                                       MIN(CID_CODE) AS CID_CODE
                                                  FROM CREDIT_CARD
                                                 WHERE BUID = pBUID AND
                                                       ORDER_NUM = OrderNum
                                                 GROUP BY BUID, ORDER_NUM) cc 
						 ON ord.BUID = cc.BUID 
						 AND ord.ORDER_NUM = cc.ORDER_NUM LEFT OUTER JOIN SCORE s 
						 ON ord.BUID = s.BUID 
						 AND ord.ORDER_NUM = s.ORDER_NUM
     WHERE ord.CURRENCY_CODE = exchg.CURRENCY_CODE AND ord.BUID = pBUID AND
           ord.ORDER_NUM = OrderNum;

    SELECT NVL(SCORING_ID, 0)
      INTO tScoringID
      FROM STAGING_ORDER_DATA
     WHERE BUID = pBUID AND ORDER_NUM = OrderNum AND ADDRESS_TYPE = 'S';

    IF tScoringID <> 0 THEN
      SELECT MIN(ORDER_NUM)
        INTO tMinOrderNum
        FROM STAGING_ORDER_DATA
       WHERE SCORING_ID = tScoringID AND ADDRESS_TYPE = 'S';

      SELECT (ord.ORDER_AMOUNT * exchg.EXCHANGE_RATE), ord.PRODUCT_DESC
        INTO tOrderAmt, tProdDesc
        FROM ORDERS ord, MASTER_CURRENCY exchg
       WHERE ord.CURRENCY_CODE = exchg.CURRENCY_CODE AND ord.BUID = pBUID AND
             ORDER_NUM = tMinOrderNum;
    END IF;
------SKU Num---------------------------------------------------------
     ReturnValue := 'sku Num';
   BEGIN

     SELECT DEDUCTION_POINTS,
             FRAUD_TOTAL_PERCENTAGE,
             FRAUD_WORKED_PERCENTAGE
        INTO tDeductionPoints, tFrdPct, tFrdWkdPct
        FROM TREND
       WHERE VARIABLE_NAME = 'sku num' AND
             UPPER(VARIABLE_VALUE) = tSKUNum AND BUID = pBUID AND
             LOCAL_CHANNEL = tLocalChannel
         AND DEDUCTION_POINTS IS NOT NULL;
         
    ReturnValue := 'product degredation';

     score_proc(pBUID, OrderNum, 'S', tNegativeScore);  
         
EXCEPTION 
WHEN no_data_found THEN
 tCleanProdDesc := fn_cleanproddesc(UPPER(tProdDesc));
    ReturnValue := 'product degredation';

--product degredation---------------------------------------------------------

    BEGIN
    SELECT DEDUCTION_POINTS,
              FRAUD_TOTAL_PERCENTAGE,
              FRAUD_WORKED_PERCENTAGE
         INTO tDeductionPoints, tFrdPct, tFrdWkdPct
         FROM TREND
        WHERE VARIABLE_NAME = 'product' AND
             UPPER(VARIABLE_VALUE) = tCleanProdDesc 
         AND BUID = pBUID 
         AND LOCAL_CHANNEL = tLocalChannel
     AND DEDUCTION_POINTS IS NOT NULL;
       
     score_proc(pBUID, OrderNum, 'N', tNegativeScore);
       
     EXCEPTION 
     WHEN no_data_found THEN
      NULL;
     END;
END;
 -----pay code--------------------------------------------------------------------
 ReturnValue := 'pay code';

    BEGIN
      SELECT DEDUCTION_POINTS,
             FRAUD_TOTAL_PERCENTAGE,
             FRAUD_WORKED_PERCENTAGE
        INTO tDeductionPoints, tFrdPct, tFrdWkdPct
        FROM TREND
       WHERE VARIABLE_NAME = 'payment code' AND VARIABLE_VALUE = tPayCode AND
             BUID = pBUID AND LOCAL_CHANNEL = tLocalChannel
              AND DEDUCTION_POINTS IS NOT NULL;

      InsertTrend_ram(pBUID,OrderNum,'pay code: ' || tPayCode,tDeductionPoints,tFrdPct,tFrdWkdPct);
      IF tNegativeScore < 1 THEN
      tNegativeScore := 1;
    END IF;

    score_proc(pBUID, OrderNum, 'N', tNegativeScore);

    --if no errors clear this
    ReturnValue := '';

EXCEPTION
WHEN no_data_found THEN
NULL;
END;
---inserttrend_ram procedure calling 70 times 
END  total_score_proc;

  PROCEDURE CopyInternalScores
  (pBUID    IN STAGING_ORDER_DATA.BUID%TYPE,
   OrderNum IN STAGING_ORDER_DATA.ORDER_NUM%TYPE) 
  IS
    tVelocity  SCORE_FILTER_APPLY.VELOCITY_SCORE%TYPE;
    tNegative  SCORE_FILTER_APPLY.VELOCITY_SCORE%TYPE;
    tTrend     SCORE_FILTER_APPLY.VELOCITY_SCORE%TYPE;
    tVendorBT   SCORE_FILTER_APPLY.VENDOR_BT_SCORE%Type;
    tScoringID STAGING_ORDER_DATA.SCORING_ID%TYPE;

    CURSOR scoreCursor(scoreID NUMBER, OrdNum VARCHAR2) IS
      SELECT ORDER_NUM
        FROM STAGING_ORDER_DATA
       WHERE SCORING_ID = scoreID AND ADDRESS_TYPE = 'S' AND
             ORDER_NUM != OrdNum;

  BEGIN

    SELECT NVL(SCORING_ID, 0)
      INTO tScoringID
      FROM STAGING_ORDER_DATA
     WHERE BUID = pBUID AND ORDER_NUM = OrderNum AND ADDRESS_TYPE = 'S';

    UPDATE SCORE_FILTER_APPLY
       SET VENDOR_SCORED = 'Y'
     WHERE BUID = pBUID AND ORDER_NUM = OrderNum;

    UPDATE SCORE
       SET VENDOR_SCORED = 'Y'
     WHERE BUID = pBUID AND ORDER_NUM = OrderNum;

    IF tScoringID <> 0 THEN
      SELECT VELOCITY_SCORE, NEGATIVE_SCORE, TREND_SCORE, VENDOR_BT_SCORE
        INTO tVelocity, tNegative, tTrend, tVendorBT
        FROM SCORE_FILTER_APPLY
       WHERE BUID = pBUID AND ORDER_NUM = OrderNum;

      FOR ord_rec IN scoreCursor(tScoringID, OrderNum) LOOP
        INTERNAL_SCORING.score_proc(pBUID,
                                   ord_rec.ORDER_NUM,
                                   'V',
                                   tVelocity);
        INTERNAL_SCORING.score_proc(pBUID,
                                   ord_rec.ORDER_NUM,
                                   'N',
                                   tNegative);
        score_proc(pBUID, ord_rec.ORDER_NUM, 'T', tTrend);
        score_proc(pBUID, ord_rec.ORDER_NUM, 'XB', tVendorBT);
      END LOOP;

      IF scoreCursor%ISOPEN THEN
        CLOSE scoreCursor;
      END IF;

    END IF;

  END CopyInternalScores;

  PROCEDURE score_proc (pBUID       IN score.buid%TYPE,
                        OrderNum    IN score.order_num%TYPE,
                        ScoreType   IN VARCHAR2,
                        score       IN score.velocity_score%TYPE)
   IS
   BEGIN
      MERGE INTO score s
           USING (SELECT pBUID buid, OrderNum order_num, score score
                    FROM DUAL) p
              ON (s.buid = p.buid AND s.order_num = p.order_num)
      WHEN MATCHED
      THEN
         UPDATE SET
            velocity_score = DECODE (ScoreType, 'V', p.score, velocity_score),
            negative_score = DECODE (ScoreType, 'N', p.score, negative_score),
            trend_score =
               DECODE (ScoreType, 'T', ROUND (p.score), trend_score),
            vendor_combined_score =
               DECODE (ScoreType, 'X', p.score, vendor_combined_score),
            vendor_bt_score =
               DECODE (ScoreType, 'XB', p.score, vendor_bt_score),
            vendor_st_score =
               DECODE (ScoreType, 'XS', p.score, vendor_st_score)
      WHEN NOT MATCHED
      THEN
         INSERT     (buid, order_num, received_date_time)
             VALUES (p.buid, p.order_num, SYSDATE);

      MERGE INTO score_filter_apply s
           USING (SELECT pBUID buid, OrderNum order_num, score score
                    FROM DUAL) p
              ON (s.buid = p.buid AND s.order_num = p.order_num)
      WHEN MATCHED
      THEN
         UPDATE SET
            velocity_score = DECODE (ScoreType, 'V', p.score, velocity_score),
            negative_score = DECODE (ScoreType, 'N', p.score, negative_score),
            trend_score =
               DECODE (ScoreType, 'T', ROUND (p.score), trend_score),
            vendor_combined_score =
               DECODE (ScoreType, 'X', p.score, vendor_combined_score),
            vendor_bt_score =
               DECODE (ScoreType, 'XB', p.score, vendor_bt_score),
            vendor_st_score =
               DECODE (ScoreType, 'XS', p.score, vendor_st_score)
      WHEN NOT MATCHED
      THEN
         INSERT     (buid, order_num, received_date_time)
             VALUES (p.buid, p.order_num, SYSDATE);
   END score_proc;
END;
/
Re: Please help me to implement this. [message #584913 is a reply to message #584907] Mon, 20 May 2013 08:03 Go to previous messageGo to next message
Michel Cadot
Messages: 59822
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

You will find in these books example on how to use collections.

Regards
Michel
Re: Please help me to implement this. [message #584995 is a reply to message #584913] Tue, 21 May 2013 02:55 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi,

I have gone through the link.
But in that no example related to merge using bulk collections.
Please provide an example using MERGE and BULK COLLECTION in a stored procedure.
Else please provide code for my requirement.

Thanks in advance.
Re: Please help me to implement this. [message #585005 is a reply to message #584995] Tue, 21 May 2013 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 59822
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot use MERGE with FORALL for the moment, only INSERT, UPDATE, DELETE are supported.

Regards
Michel
Re: Please help me to implement this. [message #585110 is a reply to message #584995] Wed, 22 May 2013 04:04 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
My Oracle version is
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production.

In Google I have scene one example FORALL with MERGE.

Please help me.

Thanks.
Re: Please help me to implement this. [message #585113 is a reply to message #585110] Wed, 22 May 2013 04:54 Go to previous messageGo to next message
Michel Cadot
Messages: 59822
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
In Google I have scene one example FORALL with MERGE.


Post the link.

Regards
Michel
Re: Please help me to implement this. [message #585116 is a reply to message #585113] Wed, 22 May 2013 05:06 Go to previous messageGo to next message
_jum
Messages: 490
Registered: February 2008
Senior Member
FORALL MERGE (testet in ORACLE 11.2.0.3)

[Updated on: Wed, 22 May 2013 05:08]

Report message to a moderator

Re: Please help me to implement this. [message #585119 is a reply to message #585116] Wed, 22 May 2013 05:57 Go to previous message
Michel Cadot
Messages: 59822
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ah ah, introduced in 11.2.0.3? It is not in the documentation for the moment.

Regards
Michel
Previous Topic: Display data in Chinese language in window 7
Next Topic: How the SQL hash is computed ?
Goto Forum:
  


Current Time: Sat Nov 29 00:34:23 CST 2014

Total time taken to generate the page: 0.10059 seconds