Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Block Structure
PL/SQL Block Structure [message #597994] Wed, 09 October 2013 11:54 Go to next message
nataliafoster26
Messages: 64
Registered: October 2013
Member
if i have a function
function test_1
......
begin
 if....then ...end

SELECT...
    BULK COLLECT INTO..
    FROM ....
order by
if...then
while ...
if....then
if ...then
else...end if
else......
end loop;
else
if ...
end if,
end if.
return variable
end;

obviously this is not the whole code, but lets say the first if...then..end is true , does this mean it will exit the block? like it wont continue to the other stuff like select, if ...then statements?
ps sorry is question doesn't make sense

[Updated on: Wed, 09 October 2013 11:55]

Report message to a moderator

Re: PL/SQL Block Structure [message #597995 is a reply to message #597994] Wed, 09 October 2013 12:00 Go to previous messageGo to next message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
It depends on what's an executable statement within the IF. If it is RETURN, then yes - function will terminate. Otherwise, it probably won't (unless it raises an exception).
Re: PL/SQL Block Structure [message #597996 is a reply to message #597994] Wed, 09 October 2013 12:03 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
nataliafoster26 wrote on Wed, 09 October 2013 22:24
if i have a function
[code] lets say the first if...then..end is true , does this mean it will exit the block? like it wont continue to the other stuff like select, if ...then statements?


An IF-ELSE block has it's own scope till it finds and END IF. Now, if any of the condition is met, it doesn't have to move to the ELSE or ELSIF block. It is just like a boolean algebra. However, if you have another IF-ELSE block it will again go into it and the condition will be checked.

I insist you keep DBMS_OUTPUT in each block, debug youself and understand how the logic works for each value whether satisfying the condition or not.

Regards,
Lalit
Re: PL/SQL Block Structure [message #597998 is a reply to message #597996] Wed, 09 October 2013 12:48 Go to previous messageGo to next message
nataliafoster26
Messages: 64
Registered: October 2013
Member
thanks guys, @lalit that is what im doing
Re: PL/SQL Block Structure [message #598000 is a reply to message #597996] Wed, 09 October 2013 13:03 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Lalit Kumar B wrote on Wed, 09 October 2013 18:03

I insist you keep DBMS_OUTPUT in each block, debug youself and understand how the logic works for each value whether satisfying the condition or not.

Not sure I agree with you there Lalit. If one is developing in an IDE such as toad or sql developer, it is now possible to step through the code one line at a time and view which lines are being executed and which ones aren't.
@OP are you using a gui such as sql developer or Toad?
Re: PL/SQL Block Structure [message #598001 is a reply to message #598000] Wed, 09 October 2013 13:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>@OP are you using a gui such as sql developer or Toad?
http://www.orafaq.com/forum/mv/msg/189828/597838/#msg_597838
Re: PL/SQL Block Structure [message #598003 is a reply to message #598000] Wed, 09 October 2013 13:15 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
pablolee wrote on Wed, 09 October 2013 23:33
Lalit Kumar B wrote on Wed, 09 October 2013 18:03

I insist you keep DBMS_OUTPUT in each block, debug youself and understand how the logic works for each value whether satisfying the condition or not.

Not sure I agree with you there Lalit. If one is developing in an IDE such as toad or sql developer, it is now possible to step through the code one line at a time and view which lines are being executed and which ones aren't.
@OP are you using a gui such as sql developer or Toad?


Pablolee,

I am sure you are talking about the debugger tool(though it is helpful sometimes as a shortcut), however, if we hit a bug or so called unusual behavior of Oracle, we cannot report it to Oracle(of course raising a SR) saying that we found it while debugging through a tool. We will have to walk through the code using the Oracle tools. I hope I correctly understood to your statement.

Regards,
Lalit
Re: PL/SQL Block Structure [message #598006 is a reply to message #598003] Wed, 09 October 2013 13:20 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
If you hit a bug? Seriously, you're adding goodness knows how many lines of code (all those dbms_outputs) into your code on the off chance that there is an Oracle Bug! That really doesn't sound too bright to me Lalit. You use the debugger to find bugs that you have introduced into your code, which I will guarantee that you do in most of the code that you write (nobody writes perfect code)
Re: PL/SQL Block Structure [message #598009 is a reply to message #598006] Wed, 09 October 2013 13:34 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
pablolee wrote on Wed, 09 October 2013 23:50
you're adding goodness knows how many lines of code


Nobody debugs all the lines of code at once. We do it functionality/business logic wise each block at a time. After all we are working with procedural programming, one after another. I am not against the use of tool, I myself use them couple of times.

In a nutshell, yes, I would completely agree with you given that you not only mean the debugger tool itself to check the execution of each line, but, also the other features like break points and placeholders to see the variables' values etc. I might sound incorrect to you, but, these are the coding practices that each one of us have developed through a period of time, not necessarily to be similar.
Re: PL/SQL Block Structure [message #598014 is a reply to message #598009] Wed, 09 October 2013 14:17 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Lalit Kumar B wrote on Wed, 09 October 2013 19:34
pablolee wrote on Wed, 09 October 2013 23:50
you're adding goodness knows how many lines of code


Nobody debugs all the lines of code at once.
Where did I say that you did? Why don't you run me through your thought process...


Quote:
We do it functionality/business logic wise each block at a time.
Define what you mean by block. I'm going to guess that you're singling out individual Looping structures or contraol structures? What if you have a control structure within a nested loop? How do you do a single block at a time there?
Quote:
After all we are working with procedural programming, one after another.
What point are you making here?
Quote:
I am not against the use of tool, I myself use them couple of times.
Then you are seriously underusing them.

Quote:
In a nutshell, yes, I would completely agree with you
That's not a nutshell, that's a complete uturn from your original position.

Quote:
given that you not only mean the debugger tool itself to check the execution of each line, but, also the other features like break points and placeholders to see the variables' values etc.
That's very good of you, thanks.
Quote:
I might sound incorrect to you,
You sound a little confused.
Quote:
but, these are the coding practices that each one of us have developed through a period of time,
So, because yo've always done it that way, you should continue to? So do you still use all_objects to get a row generator? Do you use PIVOT or do you use aggregates and DECODEs for pivoting data. New tools become available, don't avoid them just because you've always used a different method.
Re: PL/SQL Block Structure [message #598016 is a reply to message #598014] Wed, 09 October 2013 14:24 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
pablolee wrote on Thu, 10 October 2013 00:47
New tools become available, don't avoid them just because you've always used a different method.


Now that's a /forum/fa/1964/0/
No choice, have to agree to it. New tools, new features, most advanced, pushing us to new limits. Change takes a bit of time with age, but it's a must.
Re: PL/SQL Block Structure [message #598018 is a reply to message #598016] Wed, 09 October 2013 14:27 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Lalit Kumar B wrote on Wed, 09 October 2013 20:24
Change takes a bit of time with age, but it's a must.

Are you having a dig because it's my birthday tomorrow? Wink
Re: PL/SQL Block Structure [message #598020 is a reply to message #598018] Wed, 09 October 2013 14:31 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Just taking the leverage to reply once again. The answer is NO, but, might be since you made me stay awake till 12:00 AM Smile
Re: PL/SQL Block Structure [message #598027 is a reply to message #598020] Wed, 09 October 2013 16:32 Go to previous messageGo to next message
nataliafoster26
Messages: 64
Registered: October 2013
Member
nice conversation you guys are having Smile
just so you know i solve the issue
does anyone have any advice of a good website or youtube channel that i can learn more about plsql (free would be great)
thanks
Re: PL/SQL Block Structure [message #598028 is a reply to message #598027] Wed, 09 October 2013 16:49 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
Documentation
Re: PL/SQL Block Structure [message #598034 is a reply to message #598027] Wed, 09 October 2013 23:22 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
nataliafoster26 wrote on Thu, 10 October 2013 03:02
does anyone have any advice of a good website or youtube channel that i can learn more about plsql (free would be great)


I got to know about these from Michel,
http://www.konagora.com/tutorials/courseintro.php?id=8
http://www.youtube.com/user/roughsealtd

Thanks Michel for the links.

Regards,
Lalit
Previous Topic: error
Next Topic: Use ANSI standard JOIN syntax
Goto Forum:
  


Current Time: Thu Mar 28 19:04:10 CDT 2024