| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Sybase vs Oracle - which is better?
Joe & Anne Buhl wrote in message <3664C392.5C0C8F4E_at_worldnet.att.net>...
>
>
>>
>>
>> As a cursor declare ...
>> CREATE OR REPLACE proc (p_i IN INTEGER) AS
>> CURSOR c1 is SELECT ...... FROM A,B,C WHERE A.i = p_i;
>> BEGIN
>> FOR w1 in C1 LOOP
>> if valid(w1) then
>> PROCESS (w1);
>> end if;
>> END LOOP;
>> END proc;
>>
>> Simple ... like that (valid returns boolean if the row is to be processed
>> and returns the cursor record values too which might have been modified
by
>> the valid function). No need to store the temporary result. Oracle takes
>> care of that for me. This really shows to me, as an non Sybase
programmer,
>> the difference in "religion" and approach. To me, thinking of temporary
>> tables still "smells like" thinking of files. And THAT is a relational
>> database misconception I've seen in many Oracle programmers ... resulting
in
>> many program problems and strange designs.
>
>I belive once you step down that cursor it's toast. I would like to have
more
>that one pass at the table.
>I don't see anything that simply returns a result set either? I wonder
which is
>faster?
As stated in the original mail which you don't quote, there was only a wish to process the data. The procedure I wrote does that.
If you wan't more than one pass at your data you have a logical problem ... what would you gain? This is where we have a "diversity" in technology and approach. The above statement could actually be done in SQL alone - not using PL/SQL what so ever. But as this debate was about the PL/SQL abilities showing how easy it is (at least in Oracle) to work on temporary datasets. The example is of course very simplistic. If would have wanted to have grouped results I would never use PL/SQL to solve the problem, but solve it entirely in SQL alone. PL/SQL has it's limitations - and so does SQL. But don't solve SQL problems in PL/SQL ... which is in essense what I see the Sybase solution does using explicit temporary tables.
>Also, if you want to modify a set of data you have to step through each
record.
>AS opposed to just excuting one update.
Huh?? A set of data of what? The quest here was to solve ONE "abritary" process statement per record. What that was is not important - could be just printing, could be adding the record to 2 different tables in different ways etc. It really doesn't matter. What you are refering to is having to update a temporary resultset in A SECOND run - and that is entirely not necessary if you program right. For instance:
UPDATE tablename as A
SET fieldname = (select
sum(B.fieldname)*decode(A.condition_expression,'Y',0.5,1)
FROM tablename2 B
where B.fkey = A.key)
WHERE <some further restrictions>;
This will update "fieldname" in tablename as the sum of fieldname in tablename2, and if the field "condition_expression" in tablename is Y divide the sum by half. Hence, the need to first run through the table tablename2, store the summaries, then run through it again joined with tablename (A) to divide it, and THEN update A is totally uncessary.
This is my point ... usage of temporary tables is mostly due to insufficient programming tools or experience with the programming language. Unfortunately we all have "insufficent experience" with tools we never use - because we all get affected by the tools we use daily and their limitations. Yes - ALL languages has their limitations - that includes Oracle's PL/SQL and T/SQL from Sybase. Some assignments are easier to solve in PL/SQL and T/SQL and visa versa. But PLEASE don't come and tell me, that not having the ability of temporary tables prohibits me as a PL/SQL programmer to do efficent programs - for as you see, it does not. I just have to "think differently" than a traditional filebased programmer would do (and hence, this is why so many "traditional programmers" has problems with Oracle).
>Looks gnarly to me.
Well, the usage of explicit temporary tables looks gnarly to me too :)
 |
 |