Home » SQL & PL/SQL » SQL & PL/SQL » calculate the max and sum value by weird way !!! (Oracle 10g , windows XP SP2)
icon5.gif  calculate the max and sum value by weird way !!! [message #342573] Fri, 22 August 2008 18:19 Go to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

this code calculate the sum value without sum clause :-

DECLARE
x NUMBER;
y NUMBER := 0;

CURSOR cur_sal
IS
SELECT sal
FROM emp;
BEGIN
OPEN cur_sal;

LOOP
FETCH cur_sal
INTO x;

EXIT WHEN cur_sal%NOTFOUND;
y := y + x;
END LOOP;

CLOSE cur_sal;

DBMS_OUTPUT.put_line ('sum sal without using sum function = ' || y);
END;
/


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

and this code calculate the max value without max clause :-

DECLARE
x NUMBER;
y NUMBER := 0;

CURSOR cur_sal
IS
SELECT sal
FROM emp;
BEGIN
OPEN cur_sal;

LOOP
FETCH cur_sal
INTO x;

EXIT WHEN cur_sal%NOTFOUND;

IF (x > y)
THEN
y := x;
END IF;
END LOOP;

CLOSE cur_sal;

DBMS_OUTPUT.put_line ('max sal without using max function = ' || y);
END;
/


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

the Question is : how suppose that's happen ?
what's the secret in those codes ?

it's just new idea for me , but I don't understand !

is there any name for this way in ( OracleŽ Database PL/SQL User's Guide and Reference ) ?
if not , so please anyone give me any help to understand those codes !
Re: calculate the max and sum value by weird way !!! [message #342574 is a reply to message #342573] Fri, 22 August 2008 18:32 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
That is just a plain and simple anonymous PL/SQL block with a cursor and a loop in it.

Database PL/SQL User's Guide and Reference


Re: calculate the max and sum value by weird way !!! [message #342575 is a reply to message #342574] Fri, 22 August 2008 18:39 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
ThomasG wrote on Fri, 22 August 2008 16:32
That is just a plain and simple anonymous PL/SQL block with a cursor and a loop in it.

Database PL/SQL User's Guide and Reference






Thanks for the explanation, but please provide details on how this addition operation works; as in 1 + 2 = 3.
Re: calculate the max and sum value by weird way !!! [message #342576 is a reply to message #342575] Fri, 22 August 2008 18:45 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Quote:

Thanks for the explanation, but please provide details on how this addition operation works; as in 1 + 2 = 3.



Well, now that you bring it up that has quite a bit of a background story.

In the beginning there was just "nothing" and "something".

That system later got revised into "nothing", 1, 2, 3, "lots".

Since a lot of people had a lot of "nothing" they decided to split that into "null" and "zero" to be able to put it in different bags.

Then they added a lot of numbers in the middle and renamed "lots" to "buffer overflow", which is basically what we work with today.

PS: Oh, I forgot about negative numbers. We have those, too. They are quite easy to visualize: It's like a room where 10 people go in and 15 people come out. Now 5 people have to go in for it to be empty again. Razz

[Updated on: Fri, 22 August 2008 18:59]

Report message to a moderator

Re: calculate the max and sum value by weird way !!! [message #342590 is a reply to message #342573] Sat, 23 August 2008 00:51 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
> but please provide details on how this addition operation works; as in 1 + 2 = 3.

Welcome to the world of elementary arithmetics, where addition (http://en.wikipedia.org/wiki/Addition) belongs.

By the way, how would you SUM values e.g. 3,4,5. Would you create table with 3 rows and run a query with SUM on them? Are you really unable to count it yourself without any computer aid?
Re: calculate the max and sum value by weird way !!! [message #342594 is a reply to message #342576] Sat, 23 August 2008 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Laughing

A nice laugh at Saturday breakfast time, many thanks for it that's a week-end that well starts.

Michel
Re: calculate the max and sum value by weird way !!! [message #342602 is a reply to message #342573] Sat, 23 August 2008 01:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Hany Freedom wrote on Sat, 23 August 2008 01:19

is there any name for this way

Yeah: stupidity.

This is really the most stupendous way to calculate the max value..
Ever heard of ORDER BY?

I suspect that you are just trolling..
Re: calculate the max and sum value by weird way !!! [message #342619 is a reply to message #342602] Sat, 23 August 2008 04:40 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
LMAO. One of the funniest threads I have seen in a looooong time.
icon1.gif  Re: calculate the max and sum value by weird way !!! [message #342626 is a reply to message #342602] Sat, 23 August 2008 06:09 Go to previous messageGo to next message
Hany Freedom
Messages: 256
Registered: May 2007
Location: Egypt
Senior Member

Frank wrote on Sat, 23 August 2008 09:43
Hany Freedom wrote on Sat, 23 August 2008 01:19

is there any name for this way

Yeah: stupidity.

This is really the most stupendous way to calculate the max value..
Ever heard of ORDER BY?

I suspect that you are just trolling..


I know that there are better ways from what I said ......... but I was just talking about the new idea that may help us in another calculates Very Happy

anyway thanks for all
Re: calculate the max and sum value by weird way !!! [message #342628 is a reply to message #342626] Sat, 23 August 2008 06:36 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Hany Freedom wrote on Sat, 23 August 2008 13:09
I know that there are better ways from what I said ......... but I was just talking about the new idea that may help us in another calculates Very Happy

Yes, the more difficult this approach is, the worse it performs (in comparition with SUM). So I do not see where could it help (except in making the code less transparent and slowing down the execution).

flyboy wrote on Sat, 23 August 2008 07:51
By the way, how would you SUM values e.g. 3,4,5
Maybe you know other methods, but I would do it in the same way the "new idea" calculates it. So I would rather re-classify it to the "old idea" (procedural, non-SQL).
Previous Topic: Job scheduler - SQL Error ORA-06550
Next Topic: Problem in creating abcdic format file
Goto Forum:
  


Current Time: Fri Dec 09 00:05:03 CST 2016

Total time taken to generate the page: 0.06456 seconds