Home » SQL & PL/SQL » SQL & PL/SQL » Better than GOTO (oracle 9.2.0.1.0)
Better than GOTO [message #377881] Fri, 26 December 2008 01:08 Go to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Is there anything which can be used in oracle to move control in a stored procedure from one point to another.
For example GOTO.

But GOTO doesn't seems like a good option.
So can anybody suggest me something better than GOTO.

Re: Better than GOTO [message #377883 is a reply to message #377881] Fri, 26 December 2008 01:17 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Well-designed programs seldom need jump-instructions.
One of the major reasons for needing GOTO or whatever is (in my experience) not breaking down procedures into more atomic units.
Don't be tempted to write a single procedure that handles your complete transaction.
Break it up until each procedure does one thing.
Make it a habit to give a procedure a name, indicating what it does. When you find yourself coding something different in that procedure, you know you have to break up some more.
Re: Better than GOTO [message #377887 is a reply to message #377881] Fri, 26 December 2008 01:54 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Yeah Frank

I got your point but it comes to me as a requirement and i cannot do much about that. the flow is like that. So thats why i asked if there some thing better than GOTO , i think CONTINUE is in Oracle 11g. Is there something in Oracle 9 version like that.
Re: Better than GOTO [message #377890 is a reply to message #377881] Fri, 26 December 2008 01:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PL/SQL User's Guide and Reference
Chapter 4 Using PL/SQL Control Structures

Regards
Michel
Re: Better than GOTO [message #377898 is a reply to message #377883] Fri, 26 December 2008 02:47 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
is there any restriction on number of procedures for any given schema
Re: Better than GOTO [message #377899 is a reply to message #377881] Fri, 26 December 2008 02:51 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Yes the number of stored procedure are fixed here so cant increase that.
Re: Better than GOTO [message #377903 is a reply to message #377898] Fri, 26 December 2008 03:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
dr.s.raghunathan wrote on Fri, 26 December 2008 09:47
is there any restriction on number of procedures for any given schema

No.

Regards
Michel

Re: Better than GOTO [message #377964 is a reply to message #377881] Sat, 27 December 2008 01:45 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Firstly, if this is related to the return to For loop thread, I wonder why you did create a new topic. As there are many different situations where GOTO statement may be used, they shall be treated differently.

To the question: as you were told, it is all about logical code flow. There are ways to adjust it, but not very handsome either.
1) Use nested IF's:
LOOP
  <statement(s)>
  IF <condition for staying in the loop> THEN
    <statement(s)>
    IF <condition for staying in the loop> THEN
      <statement(s)>
      IF <condition for staying in the loop> THEN
        <statement(s)>
      END IF;
    END IF;
  END IF;
END LOOP;

2) Introduce a temporary boolean variable to identify the loop break:
LOOP
  <statement(s)>
  l_var := <condition for staying in the loop>;

  IF l_var THEN
    <statement(s)>
    l_var := l_var AND <condition for staying in the loop>;
  END IF;

  IF l_var THEN
    <statement(s)>
    l_var := l_var AND <condition for staying in the loop>;
  END IF;

  IF l_var THEN
    <statement(s)>
    l_var := l_var AND <condition for staying in the loop>;
  END IF;
END LOOP;

GOTO (when used reasonably) is not so bad in this case, is it?
Re: Better than GOTO [message #377965 is a reply to message #377881] Sat, 27 December 2008 01:49 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Yeah flyboy i agree

But suppose if i am using more than 2 cursor . And there is a situation where i want use the values fetched by the cursor , but if i put GOTO then before actually closing the cursor it just goes to the point mentioned.

So before every GOTO statement , i need close the cursor and again if i want to use values of cursor then open,fetch and close again.
Re: Better than GOTO [message #377966 is a reply to message #377965] Sat, 27 December 2008 02:10 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
shaksing wrote on Sat, 27 December 2008 08:49
But suppose if i am using more than 2 cursor . And there is a situation where i want use the values fetched by the cursor , but if i put GOTO then before actually closing the cursor it just goes to the point mentioned.

So before every GOTO statement , i need close the cursor
What about putting it behind the GOTO label (if it is the last statement of the outer loop; but otherwise you cannot close the cursor before every GOTO statement)?
Anyway, if you would use CONTINUE statement, you would have to issue it before too.
Quote:
and again if i want to use values of cursor then open,fetch and close again.

Are you unable to fetch it before issuing GOTO (if it is not already fetched)?

But without any example, it is hard to tell, what you are trying to do.

[Edit: Added note about CONTINUE statement]

[Updated on: Sat, 27 December 2008 02:13]

Report message to a moderator

Re: Better than GOTO [message #377976 is a reply to message #377881] Sat, 27 December 2008 04:43 Go to previous messageGo to next message
shaksing
Messages: 115
Registered: November 2008
Senior Member
Hi Flyboy...

Please don't bother about the working of the code...

My main concern is about the GOTO and closing of the cursor....

CREATE OR REPLACE PROCEDURE test
IS
STATE VARCHAR2(1);
STEP NUMBER := 1;



CURSOR MY_CURSOR
    IS
 SELECT * FROM table_name1 WHERE --------;
    V_RECORD MY_CURSOR%ROWTYPE;
CURSOR T_CURSOR
  IS
  SELECT * FROM table_name2 WHERE ----- ;
  T_RECORD T_CURSOR%ROWTYPE;


BEGIN

IF sysdate BETWEEN trunc(sysdate) AND trunc(sysdate) + 6/24 THEN
GOTO ABC; 


ELSE
		SELECT count(*) FROM table_name1 into record_count where unique_id = v_record.identificador;
		IF record_count > 0 then
		ELSE
		END IF;

END IF;
<<ABC>>

 OPEN MY_CURSOR;
 	FETCH MY_CURSOR INTO V_RECORD;
 	IF MY_CURSOR%NOTFOUND THEN
  	   INSERT INTO table_name1(.........) VALUES (........ );
  	   OPEN T_CURSOR;
  	   FETCH T_CURSOR INTO T_RECORD;
  	   --- call a stored procedure , parameter passed to the procedure are using t_reocrd rowtype
	   
    ELSE
     	     STATE := V_RECORD.state;
  	     IF STATE =  'E' THEN
   	 	 	DBMS_OUTPUT.PUT_LINE ('somthing');
     	     ELSIF STATE =  'P' THEN
   	 	 	SELECT date -----
			IF -------
   			   select count from table_name1 where ---------------------------
			   IF record_count > 0 then
		   	   	  --call to the procedure of alarm management
  			   ELSE
			   	  DBMS_OUTPUT.PUT_LINE ('NOT ALLOWED');
			   end if;
   			ELSE
   		  		DBMS_OUTPUT.PUT_LINE ('SHUTDOWN EXPIRED');
			END IF;
	  	 
	     ELSIF STATE =  'T' THEN
   	    		select -------
   			IF  THEN
   				DBMS_OUTPUT.PUT_LINE ('THE MAXIMUM NUMBER ALLOWED BY PROCESS HAVE BEEN PROCESSED');
   			ELSE
   				INSERT INTO NPPI_table_name1 (......) VALUES (.......);
   				----call stored procedure using t_reocrd rowtype
   				close t_cursor;
				GOTO ABC;
				
			END IF;
			
            ELSIF STATE =  'F' THEN
   		 	STEP :=step+1;
   		 	OPEN MY_CURSOR;
   		 	FETCH MY_CURSOR INTO V_RECORD;
   		 	IF MY_CURSOR%FOUND THEN
    	 			SELECT sum(REGISTROS) into total_registros FROM NPPI_PURGADO_S WHERE IDENTIFICADOR= V_RECORD.IDENTIFICADOR;
    				 IF REGISTROS > t_record.REGISTROS_MAX THEN
					 DBMS_OUTPUT.PUT_LINE ('---------');
				 ELSE
     					 INSERT INTO NPPI_table_name1 (......) VALUES (.......);
   					----call stored procedure using t_reocrd rowtype
   					close t_cursor;
					GOTO ABC;
   				 END IF;
			  
   	     
		       ELSE
    	 	 		SELECT sum(REGISTROS) into total_registros FROM NPPI_PURGADO_S WHERE IDENTIFICADOR= V_RECORD.IDENTIFICADOR;
    		 		IF REGISTROS > t_record.REGISTROS_MAX THEN
					DBMS_OUTPUT.PUT_LINE ('-------');
		     		ELSE
     					 INSERT INTO NPPI_table_name1 (......) VALUES (.......);
   					----call stored procedure using t_reocrd rowtype
   					close t_cursor;
					GOTO ABC;
    				END IF;
		      END IF;
			
  		ELSE
  			DBMS_OUTPUT.PUT_LINE ('NO STATUS MENTIONED');
  			close my_cursor;
  		END IF;
		
	END IF;



EXCEPTION
          /*If there is no data */
       WHEN NO_DATA_FOUND
       THEN
             raise_application_error (-20001,' NO DATA FOUND ');

     /* If too much data*/
       WHEN TOO_MANY_ROWS
       THEN
             raise_application_error (-20002,' THERE ARE TOO MANY ROWS ');

    /* If internal error in program*/

    WHEN OTHERS THEN
             err_num := SQLCODE;

        IF err_num <> -54 THEN
             raise_application_error (-20004,' ERROR NOT CONTROLLED IN THE PROGRAM. ERROR CODE '||err_num||'. MESSAGE '||SQLERRM);
        END IF;

END;
/


Re: Better than GOTO [message #377979 is a reply to message #377881] Sat, 27 December 2008 05:46 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
OK, now it seems this is different problem (or at least its solution) than the one in the post I mentioned. The main difference lies in the fact that here you do not use any LOOP and it is (in my opinion wrongly) "emulated" by GOTO statement. I agree that this is not a good option. Maybe you misunderstood me - I did not advice to replace LOOP by GOTO, just place GOTO label to the place where CONTINUE would go on (= emulate the CONTINUE behaviour).

I have no idea what is the relationship between those two cursors, how many rows are they supposed to return and what is the expected result, so I cannot advice more.

The last comment to the code flow: are you aware that all FETCHes take the same row in each pass (as they are directly after OPEN statement)? You may issue CLOSE nearly directly after FETCH (the only step between them would be checking %NOTFOUND). The whole cursor treating may be replaced with SELECT INTO statement and catching NO_DATA_FOUND exception. All those GOTOs probably end in infinite LOOP (as you investigate the same row from MY_CURSOR in each pass). But, as I said, it is hard to say without specifying what you are trying to achieve with this piece of code.
Previous Topic: Create User (merged 4)
Next Topic: logical oprator 'like' & 'in' not working.
Goto Forum:
  


Current Time: Fri Dec 09 17:15:42 CST 2016

Total time taken to generate the page: 0.42871 seconds