Home » SQL & PL/SQL » SQL & PL/SQL » Comparing two or more cursors (10g)
icon5.gif  Comparing two or more cursors [message #420208] Thu, 27 August 2009 09:45 Go to next message
Mark Regler
Messages: 9
Registered: April 2003
Junior Member
Hi all,

this may be a totally nooby question but....

Is it possible to compare values across cursors?

For example if I have 3 cursors each with

ID, VAL1, VAL2 and VAL3

Just that the first cursor only has ID and VAL1 filled, second cursor only has ID and VAL2 filled and third only has ID and VAL3 filled.

Would it then be possible to make a choice along the lines of
IF cursor1 VAL1 > cursor2 VAL2
AND cursor1 VAL1 > cursor3 VAL3
THEN cursor1 VAL1.....

I know SQL is the better solution for this Smile but I'm interested in seeing if I can make a PL/SQL work almost as fast.

Thanks for any help

M.
Re: Comparing two or more cursors [message #420210 is a reply to message #420208] Thu, 27 August 2009 09:51 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.

> but I'm interested in seeing if I can make a PL/SQL work almost as fast.

Row by row is slow by slow.


>Would it then be possible to make a choice along the lines of
>IF cursor1 VAL1 > cursor2 VAL2
Possible only after you design & implement your own custom programming language.
Re: Comparing two or more cursors [message #420266 is a reply to message #420208] Thu, 27 August 2009 23:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Cursors in PL/SQL are typically used to fetch multiple rows. Therefore, comparing column-values between cursors does not really make a lot of sense. Which row from cursor 1 will you compare with which row from cursor 2?

Since in PL/SQL you can use local variables, your idea with the if-then construction would work (albeit that the syntax is not really correct). You can assign a value to a variable, depending on the value of results from cursors.
I am sorry if this all sounds a bit vague, but it is all I can say about your problem without any further details from your side.
Re: Comparing two or more cursors [message #420284 is a reply to message #420208] Fri, 28 August 2009 03:10 Go to previous messageGo to next message
Mark Regler
Messages: 9
Registered: April 2003
Junior Member
First off thank you black swan and frank for your help.

@Frank: all three cursors would have the same data, only the information in the columns VAL1, VAL2 and VAL3 would be different. I would want to compare the same row in all cursors.
Re: Comparing two or more cursors [message #420291 is a reply to message #420284] Fri, 28 August 2009 03:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I think it would help a lot if you could show us what you have so far. Maybe simplify it a bit (e.g. we don't need to see a zillion columns, etc)
Since I have no idea what you plan to do with the recalculated VAL1, I can't really tell you what to do.
Re: Comparing two or more cursors [message #420311 is a reply to message #420208] Fri, 28 August 2009 04:48 Go to previous messageGo to next message
Mark Regler
Messages: 9
Registered: April 2003
Junior Member
Hi Frank.

I'm not sure if this will help in any way. This is the PL/SQL I was given

DECLARE
 
  h_MANDNT               NUMBER(4)     := 0    ;              
  h_ADRNR                NUMBER(12)    := 0    ;
  h_RELATN               NUMBER(2)     := 0    ;
  h_ADRNRU               NUMBER(12)    := 0    ; 
  h_NAMNR		 NUMBER(9)     := 0    ;
  h_ZIP 		 VARCHAR2(9)   := 0    ;
  h_STRNR		 NUMBER(12)    := 0    ;
  h_ORTNR		 NUMBER(12)    := 0    ;
  h_HSNR		 NUMBER(5)     := 0    ;
                                       
  h_AREACD               NUMBER(5)     := 0    ;
  h_CNTCOD               NUMBER(3)     := 0    ;
  h_COMNUM               NUMBER(14)    := 0    ;
  h_DATE                 DATE                  ;
  h_STATD                NUMBER(2)     := 0    ;
  h_COMSRC               NUMBER(4)     := 0    ;
  h_COMTYP               NUMBER(2)     := 0    ;
                                       
  h_CAF_AREACD           NUMBER(5)     := 0    ;
  h_CAF_CNTCOD           NUMBER(3)     := 0    ;
  h_CAF_COMNUM           NUMBER(14)    := 0    ;
  h_CAF_DATE             DATE                  ;
  h_CAF_STATD            NUMBER(2)     := 0    ;
  h_CAF_COMTYP           NUMBER(2)     := 0    ;
                                      
  h_CAFNAB_AREACD        NUMBER(5)     := 0    ;
  h_CAFNAB_CNTCOD        NUMBER(3)     := 0    ;
  h_CAFNAB_COMNUM        NUMBER(14)    := 0    ;
  h_CAFNAB_DATE          DATE                  ;
  h_CAFNAB_STATD         NUMBER(2)     := 0    ;
  h_CAFNAB_COMTYP        NUMBER(2)     := 0    ;
                                       
  h_CAFTTEL_AREACD       NUMBER(5)     := 0    ;
  h_CAFTTEL_CNTCOD       NUMBER(3)     := 0    ;
  h_CAFTTEL_COMNUM       NUMBER(14)    := 0    ;
  h_CAFTTEL_DATE         DATE                  ;
  h_CAFTTEL_STATD        NUMBER(2)     := 0    ;
  h_CAFTTEL_COMTYP       NUMBER(2)     := 0    ;
    
  e_CancelError          EXCEPTION             ;

  nDummy                 NUMBER(10)    := 0    ;
------------------------------------------------------------------------------------------------------------------------------------------------------------            
 

/* Aufbau des Cursors: Selektion der Adressnummern für jeden Mandanten im Cursor c_main */

 CURSOR c_main IS SELECT c.mandnt, c.adrnr, 0, c.adrnr, c.namnr, c.zip, c.strnr, c.ortnr, c.hsnr 
                    FROM ytst_mb2cab_x c, yt_mandnt m
                      WHERE c.mandnt          = m.mandnt
                         AND m.mandnt_type_id = 1
                         AND c.statd          = 0
                  ;  
BEGIN

  DBMS_OUTPUT.ENABLE; /* Serveroutput aktivieren */

 OPEN c_main;
 FETCH c_main INTO h_MANDNT, h_ADRNR, h_RELATN, h_ADRNRU, h_NAMNR, h_ZIP, h_STRNR, h_ORTNR, h_HSNR ;
 
 WHILE (c_main%found) LOOP

nDummy := nDummy +1 ;  
dbms_application_info.set_client_info( nDummy || '    ' || TO_CHAR(SYSDATE,'hh24:mi') ) ;  

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

/*    reset Variables    */

h_CAF_AREACD         := 0                              ;      
h_CAF_CNTCOD         := 0                              ;
h_CAF_COMNUM         := 0                              ;
h_CAF_COMTYP         := 0                              ;
h_CAF_DATE           := TO_DATE('01011900','ddmmyyyy') ;
h_CAF_statd          := 0                              ;
                     
h_CAFNAB_AREACD      := 0                              ;
h_CAFNAB_CNTCOD      := 0                              ;
h_CAFNAB_COMNUM      := 0                              ;
h_CAFNAB_COMTYP      := 0                              ;
h_CAFNAB_DATE        := TO_DATE('01011900','ddmmyyyy') ;             
h_CAFNAB_statd       := 0                              ;
                                                       
h_CAFTTEL_AREACD     := 0                              ;
h_CAFTTEL_CNTCOD     := 0                              ;
h_CAFTTEL_COMNUM     := 0                              ;
h_CAFTTEL_COMTYP     := 0                              ;
h_CAFTTEL_DATE       := TO_DATE('01011900','ddmmyyyy') ;             
h_CAFTTEL_statd      := 0                              ;
                     
--h_MANDNT             := 0                              ;
--h_ADRNR              := 0                              ;
h_AREACD             := 0                              ;
h_CNTCOD             := 0                              ;
h_COMNUM             := 0                              ;
h_COMTYP             := 0                              ;
h_DATE               := TO_DATE('01011900','ddmmyyyy') ;             
h_STATD              := 0                              ;

/* Add the tel numbers from CUSTD*/

BEGIN
       SELECT caf.areacd   , caf.cntcod   , caf.comnum  , caf.comtyp  , NVL(caf.srcend,caf.updstp), caf.statd 
         INTO h_CAF_AREACD , h_CAF_CNTCOD , h_CAF_COMNUM, h_CAF_COMTYP, h_CAF_DATE                , h_CAF_statd 
       FROM ytrf_mb2caf_x caf 
         WHERE caf.mandnt     = h_mandnt 
           AND caf.adrnr      = h_adrnr
           AND caf.adrnru     = h_adrnr
           AND caf.relatn     = 0
           AND caf.areacd     IS NOT NULL
           AND caf.statd      IN (0,5,6)
           AND caf.comtyp     IN (1,11)
           AND caf.cntcod     = 49
           AND ROWNUM=1
       ; 
         EXCEPTION 
           WHEN NO_DATA_FOUND THEN 
             h_CAF_AREACD:=0;

           WHEN OTHERS then
             DBMS_OUTPUT.PUT_LINE('caf: ' || substr( SQLERRM, 1, 200 )   )       ;
             RAISE e_CancelError ;
END;

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

/* Add the tel numbers from NABD*/

BEGIN
       SELECT      cafn.areacd , cafn.cntcod     , cafn.comnum    , cafn.comtyp    , NVL(cafn.srcend,cafn.updstp) , cafn.statd 
         INTO  h_CAFNAB_AREACD , h_CAFNAB_CNTCOD , h_CAFNAB_COMNUM, h_CAFNAB_COMTYP, h_CAFNAB_DATE                , h_CAFNAB_statd 
       FROM ytrf_mb2caf_x@tnabd cafn, ytst_mb2cab_x@tnabd cabn 
         WHERE cafn.mandnt     = cabn.mandnt
           AND cafn.adrnr      = cabn.adrnr
           AND cafn.adrnru     = cabn.adrnr
           AND cafn.relatn     = 0
           AND cafn.statd      IN (0,5,6)
           AND cafn.comtyp     IN (1,11)
           AND cafn.cntcod     = 49
           AND cabn.statd      = 0
           AND cabn.mandnt     = 990
           AND cafn.areacd     IS NOT NULL
           AND cabn.namnr      = h_NAMNR
           AND cabn.zip        = h_ZIP
           AND cabn.strnr      = h_STRNR
           AND cabn.ortnr      = h_ORTNR
           AND cabn.hsnr       = h_HSNR
           AND ROWNUM=1
       ; 
         EXCEPTION 
           WHEN no_data_found THEN 
             h_CAFNAB_AREACD:=0;
--DBMS_OUTPUT.PUT_LINE('h_CAFNAB_AREACD: '||TO_CHAR(h_CAFNAB_AREACD)     ); 

           WHEN OTHERS then
             DBMS_OUTPUT.PUT_LINE('nab: ' || substr( SQLERRM, 1, 200 )   )       ;
DBMS_OUTPUT.PUT_LINE( h_NAMNR  )   ; 
DBMS_OUTPUT.PUT_LINE( h_ZIP    )   ; 
DBMS_OUTPUT.PUT_LINE( h_STRNR  )   ; 
DBMS_OUTPUT.PUT_LINE( h_ORTNR  )   ; 
DBMS_OUTPUT.PUT_LINE( h_HSNR   )   ; 
             RAISE e_CancelError ;

         
END;

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

/* Add the tel numbers from SMARTTEL*/

BEGIN
       SELECT caft.areacd      , caft.cntcod      , caft.comnum     , caft.comtyp     , NVL(caft.srcend,caft.updstp) , caft.statd 
         INTO h_CAFTTEL_AREACD , h_CAFTTEL_CNTCOD , h_CAFTTEL_COMNUM, h_CAFTTEL_COMTYP, h_CAFTTEL_DATE               , h_CAFTTEL_statd 
       FROM ytrf_mb2caf_x@ttel caft, ytst_mb2cab_x@ttel cabt  
         WHERE caft.mandnt     = cabt.mandnt 
           AND caft.adrnr      = cabt.adrnr
           AND caft.adrnru     = cabt.adrnr
           AND caft.relatn     = 0
           AND caft.statd      IN (0,5,6)
           AND caft.comtyp     IN (1,11)
           AND caft.cntcod     = 49
           AND cabt.statd      = 0   
           AND cabt.mandnt     = 985 
           AND caft.areacd     IS NOT NULL
           AND cabt.namnr      = h_NAMNR
           AND cabt.zip        = h_ZIP
           AND cabt.strnr      = h_STRNR
           AND cabt.ortnr      = h_ORTNR
           AND cabt.hsnr       = h_HSNR
           AND ROWNUM=1
       ; 
         EXCEPTION 
           WHEN no_data_found THEN 
             h_CAFTTEL_AREACD:=0;

           WHEN OTHERS then
             DBMS_OUTPUT.PUT_LINE('tel: ' || substr( SQLERRM, 1, 200 )   )       ;
             RAISE e_CancelError ;

END;

------------------------------------------------------------------------------------------------------------------------------------------------------------
/* Compare date values:  
   If there is a value for SRCEND in ytrf_mb2caf_x@CUSTD and if this value is larger than the date values from ytrf_mb2caf_x@SMARTTELD and yt_mb2caf_x@NABD then retain the values from ytrf_mb2caf_x@CUSTD. If the SRCEND is null use UPDSTP */

   BEGIN 
          IF     h_CAF_DATE   >= h_CAFNAB_DATE   
             AND h_CAF_DATE   >= h_CAFTTEL_DATE
             AND h_CAF_AREACD != 0
                 THEN   h_DATE   := h_CAF_DATE;
                        h_AREACD := h_CAF_AREACD;
                        h_CNTCOD := h_CAF_CNTCOD;  
                        h_COMNUM := h_CAF_COMNUM;  
                        h_STATD  := h_CAF_STATD;
                        h_COMTYP := h_CAF_COMTYP;
                           
/* Compare date values:  
   If there is a value for SRCEND in ytrf_mb2caf_x@NABD and if this value is larger than the date values from ytrf_mb2caf_x@CUSTD and yt_mb2caf_x@SMARTTEL then retain the values from ytrf_mb2caf_x@NABD. If the SRCEND is null use UPDSTP */

        ELSIF h_CAFNAB_DATE   >= h_CAF_DATE
          AND h_CAFNAB_DATE   >= h_CAFTTEL_DATE
          AND h_CAFNAB_AREACD != 0
                THEN   h_DATE   := h_CAFNAB_DATE;
                       h_AREACD := h_CAFNAB_AREACD;
                       h_CNTCOD := h_CAFNAB_CNTCOD;
                       h_COMNUM := h_CAFNAB_COMNUM;
                       h_STATD  := h_CAFNAB_STATD;
                       h_COMTYP := h_CAFNAB_COMTYP;

/* Compare date values:  
   If there is a value for SRCEND in ytrf_mb2caf_x@smarttel and if this value is larger than the date values from ytrf_mb2caf_x@CUSTD and yt_mb2caf_x@NABD then retain the values from ytrf_mb2caf_x@smarttel. If the SRCEND is null use UPDSTP */
                     
        ELSIF h_CAFTTEL_DATE   >= h_CAF_DATE 
          AND h_CAFTTEL_DATE   >= h_CAFNAB_DATE         
          AND h_CAFTTEL_AREACD != 0
               THEN   h_DATE := h_CAFTTEL_DATE;
                    h_AREACD := h_CAFTTEL_AREACD;  
                    h_CNTCOD := h_CAFTTEL_CNTCOD;  
                    h_COMNUM := h_CAFTTEL_COMNUM;  
                    h_STATD  := h_CAFTTEL_STATD;
                    h_COMTYP := h_CAFTTEL_COMTYP;   
         ELSE
            NULL ;
         END IF;   
  END;              
------------------------------------------------------------------------------------------------------------------------------------------------------------

/*Insert into table POOL.YT_TEMP_OUTB_COMNUM*/

         IF h_AREACD != 0 THEN
             BEGIN  
             
                  INSERT INTO POOL.YT_TEMP_OUTB_COMNUM(  MANDNT, ADRNR  , AREACD  , CNTCOD  , COMNUM  , COMTYP  , SRCEND, STATD  , UPDSTP , UPDTPR, UPDTOP                  , UPDTBG )
                       VALUES                         (h_MANDNT, h_ADRNR, h_AREACD, h_CNTCOD, h_COMNUM, h_COMTYP, h_DATE, h_STATD, SYSDATE, 7000  , yf_uid_by_name('MGRAAP'), 0      );
                                                         

--                 EXCEPTION
--                    when OTHERS then
--                       DBMS_OUTPUT.PUT_LINE(  substr( SQLERRM, 1, 200 )   )       ;
              END;            
         END IF;                                         
  FETCH c_main INTO h_MANDNT, h_ADRNR, h_RELATN, h_ADRNRU, h_NAMNR, h_ZIP, h_STRNR, h_ORTNR, h_HSNR ;

END LOOP ;
 CLOSE c_main;
         
         
 EXCEPTION 
   WHEN OTHERS then
     DBMS_OUTPUT.PUT_LINE(  'Abbruch ' || TO_CHAR(SYSDATE,'dd.mm.yyyy hh24:mi:ss')   )       ;


END;


What happens is that telephone numbers from different sources are added to the original row from the cursor. We then have to choose which number to use based on date values. I was thinking along the lines of reducing the number of context switches.
Re: Comparing two or more cursors [message #420318 is a reply to message #420311] Fri, 28 August 2009 05:01 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Ok, the way to reduce context switches is to rewrite this beast into a single query, using CASE and maybe some outer joins (read the code 'diagonally')
Re: Comparing two or more cursors [message #420319 is a reply to message #420208] Fri, 28 August 2009 05:13 Go to previous messageGo to next message
Mark Regler
Messages: 9
Registered: April 2003
Junior Member
My thoughts exactly! Rewriting to a single query would basically leave me with the statement I already wrote in SQL with just a smidgeon of PL/SQL construct around it Laughing I was really just wondering, if it were possible to make PL/SQL as performant as SQL. I'm starting to believe this isn't really viable.
Re: Comparing two or more cursors [message #420326 is a reply to message #420208] Fri, 28 August 2009 05:49 Go to previous message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
When it comes to comparing data in tables SQL is almost always faster than PL/SQL and if it isn't then it's usually becuase you're doing something wrong.

Some other notes on the code you posted:
1) variables should be typed to the column that's used to populate them wherever possible.
2) This:
 OPEN c_main;
 FETCH c_main INTO h_MANDNT, h_ADRNR, h_RELATN, h_ADRNRU, h_NAMNR, h_ZIP, h_STRNR, h_ORTNR, h_HSNR ;
 
 WHILE (c_main%found) LOOP


Is logically equivalent to a FOR LOOP

3) Those exception when others should almost certainly be got rid of - they don't add any real value.
Previous Topic: PL/SQL: numeric or value error
Next Topic: update a big table
Goto Forum:
  


Current Time: Thu Dec 08 00:13:19 CST 2016

Total time taken to generate the page: 0.08241 seconds