• (cs) in reply to Phil the ruler of heck
    Phil the ruler of heck:

    db-newbie:
    Just a newbie question, but couldn't the same thing be accomplished (without iterating) with a select/join/exists type test?

    I'm not exactly an expert in these matters either, but...  almost certainly, the obvious WTF would seem to be that opening a cursor, actually requesting the data, and then iterating over the returned records is pointless if you just want to know whether or not there is at least one record matching the WHERE clause.  The Sybase way to do it would probably have been

    IF EXISTS (SELECT 1 FROM ... WHERE ...)

    if I recall that correctly, other DBMS's might use a slightly different syntax...



    Well I think you're missing the point here is:
    He checks for NOT @@fetch_status =0
    @@fetch_status=  <font face="Verdana, Arial, Helvetica, sans-serif" size="-1"><font face="Verdana, Arial, Helvetica, sans-serif" size="-1">0 if the fetch was successful...

    So he in a very strange way does a check on a constraint.
    No this is not the way to do it now (at least not in SQL Server), no this is not an elegant approach.

    It's the typical example of a programmer without a thorough understanding of the framework.


    </font></font>
  • (cs)

    WTF! Just wtf!

  • (cs) in reply to ammoQ
    ammoQ:
    Anonymous:

    ammoQ:
    A referential integrity constraint generally requires a primary key constraint on the referenced table (maybe some database systems handle that differently...). Which, in fact, implies "not null" and "unique". This trigger might be necessary because those requirements are not met. Which would in turn be another WTF, but that's another story.

    Well, it implies "only 1 null" and "unique" if the PK is on a nullable column.



    In Oracle, primary keys cannot be null.

    In MS SQL, primary keys cannot be null.  However, unique constraints can placed on nullable columns.  The weird SQL behavior is that the second null is considered a violation of the unique constraint, whereas in most of the rest of the DB industry, including Oracle, mutiple rows with null key values are allowed.  That is consistent with the standard null != null rule.
  • (cs) in reply to ammoQ
    ammoQ:
    Anonymous:
    Anonymous:

    The set based and cursor comments are both justified though...AAMOF, I didn't catch the fact that he wasn't looping the cursor.

    Cursors have featured more than once on this site. Having never used them, or felt the need to, could someone give an example of where they are actually appropriate?


    Consider a table with 10^9 records, and your program wants to process them all (e.g. write them to a tape drive). A "result set" would be too large for the computer's memory, but a cursor is just an interator over those records, so it doesn't require much memory.

    A query of this kind would not necessarily load the rows into memory regardless of whether a cursor is used.  In the worst case -- where the result set is large, unindexed, and there is an ORDER BY in the query -- it would put the rows in tempdb to do the sort.  It would exhibit the same behavior for both a cursor and a normal query in most cases.  However, if it were an unusual cursor, the performance of the cursor could be far worse.  For example, if you open an INSENSITIVE or STATIC cursor, the entire resultset would be copies to tempdb before the first row was processed.

    As for the example given above, I have never seen an instance where a cursor solution outperforms an equivalent non-cursor solution.  The best you can hope for is "not too bad" when using cursors.

    Cursors are appropriate when there is no other way to accomplish the task.  For example, if you wanted to send an email to each of several addresses returned by a query, you'd have to call xp_sendmail on each row and that could only be done in a cursor.  Starting with SQL 2000, you could put the call to xp_sendmail in a function and call it in a SELECT statement, but I'm not sure that would be such a great idea.

    BTW, since ammoq is here:
    I find it strange that Oracle documentation and training seem to encourage the use of cursors even in fairly trivial situations whereas MS SQL intentionally doesn't even include the topic in their standard training courses.  I've done a small amount of testing and Oracle does seem to suffer the same fate as MS SQL with cursors.  Generally, if an equivalent non-cursor solution is available then it will be many times faster than the cursor solution.  I believe it has to do with the internal context switching the server has to do between the internal query engine powering the cursor and the engine that executes the custom code that is the body of the cursor.  Back when I took Oracle classes, my Oracle instructor wasn't even aware that there were performance considerations to choosing cursors over set-oriented solutions.  Before you pick on the instructor, please note that at the time I was training to become an Oracle University instructor and they required me to take all my classes with hand-picked senior instructors.  And yes, I did get the gig and I have taught at the Oracle building in downtown Bethesda, MD.
    I think part of the answer is that cursors in the DB tend to be a bad idea but cursors in client apps tend to be the only way to process the returned data.  Most client apps aren't written with a set oriented language, so they need cursors to manage the data.  In Oracle land, PL/SQL is the recommended server side, client side, and middle tier language, so learning PL/SQL cursors is important for the client and mt portion.  In MS SQL land, server side stuff is done in T-SQL but other work is done in other languages.  So learning T-SQL cursors will actually encourage you to write bad code.  A beginner is usually far more comfortable building a convoluted cursor procedure than learning the SQL techniques needed to get the same results without cursors.

  • Clowns (unregistered) in reply to HardCode
    Anonymous:
    Nothing like a "person customer" entity, as opposed to ... I guess ... a robot customer, or an animal customer?
    ...or Company Customer.
  • (cs) in reply to jsmith
    jsmith:

    I find it strange that Oracle documentation and training seem to encourage the use of cursors even in fairly trivial situations whereas MS SQL intentionally doesn't even include the topic in their standard training courses. (...)



    Generaly speaking, iterative processing (aka "cursor") is a stupid idea when set-based operations are sufficient.

    For example,

    update mytable set x=y where a=b;

    and

    for r in (select rowid from mytable where a=b) loop
      update mytable set x=y where rowid=r.rowid;
    end loop;

    are roughly equivalent in what they do, but the second one is plain stupid and much slower. Anyway, if you use a user-defined function (UDF) instead of a PL/SQL cursor, you probably gain less than you think - because calling the UDF also causes a context-switch between SQL and PL/SQL. I'd rather not use a UDF with side effects in a SQL statement, just because; so I would rather write

    for r in (select * from wtf where level>3) loop
      send_to_alex(r);
    end loop;

    than

    select send_to_alex(*) from wtf where level>3;

    And yes, PL/SQL is used everywhere in Oracle-Land, so there are probably more legitimate uses for cursors in Oracle than in T-SQL. PL/SQL writes to files, PL/SQL sends emails, ... and that's just the server part of the game.
    Finally, I may be wrong, but I think T-SQL is more set-oriented than PL/SQL, so it's no surprise people use cursors more regulary in T-SQL than in PL/SQL.
  • (cs)
    Anonymous:
    I don't get it - almost all of the stored procedures I have at work are like this - most are worse!

    Where's the WTF? The fact that I can't see one after having so many shoved down my throat over the last year?

    http://www.drarok.com/Stored.txt

    Really, what's the point in that SP!?!?

    captcha - hacker :D

    One point could be permissions / security.
    The user does not have insert/update/delete granted, but have it for execute on the stored proc.
  • Robert Synnott (unregistered) in reply to David
    Anonymous:
    Umm Yeah they never got rid of capital punishment.  I think you mean corporal punishment (after all it is rather harsh to kill somebody for bad code).

    Presumably the poster was from one of the few countries (every developed nation except the US and Japan) that has banned capital punishment.

  • [os] (unregistered)

    SQL Server can't handle circular references. You handle that by implemented this kind of code. The WTF is the use of cursors

  • (cs) in reply to jsmith
    jsmith:

    I find it strange that Oracle documentation and training seem to encourage the use of cursors even in fairly trivial situations whereas MS SQL intentionally doesn't even include the topic in their standard training courses.  I've done a small amount of testing and Oracle does seem to suffer the same fate as MS SQL with cursors.


    It does? User Guide only shows the concept of cursors:

    http://download-uk.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_sqlproc.htm#sthref209

    Please note the first line.

    jsmith:

    Generally, if an equivalent non-cursor solution is available then it will be many times faster than the cursor solution.  I believe it has to do with the internal context switching the server has to do between the internal query engine powering the cursor and the engine that executes the custom code that is the body of the cursor.  Back when I took Oracle classes, my Oracle instructor wasn't even aware that there were performance considerations to choosing cursors over set-oriented solutions.


    That's why they introduced BULK PROCESSING. May not be as fast as SQL only processing, but certainly faster than standard row by row processing (on larger result sets).


    jsmith:

    Before you pick on the instructor, please note that at the time I was training to become an Oracle University instructor and they required me to take all my classes with hand-picked senior instructors.  And yes, I did get the gig and I have taught at the
    Oracle building in downtown Bethesda, MD.


    Well, there are some material form experts you can access through the web that correct some of the common misconceptions about Oracle:

    asktom.oracle.com
    www.jlcomp.demon.co.uk (if you ever have the chance to attend one of Jonathan's sessions, it's worth every cent)

    jsmith:

    I think part of the answer is that cursors in the DB tend to be a bad idea but cursors in client apps tend to be the only way to process the returned data.  Most client apps aren't written with a set oriented language, so they need cursors to manage the data.  In Oracle land, PL/SQL is the recommended server side, client side, and middle tier language, so learning PL/SQL cursors is important for the client and mt portion.  In MS SQL land, server side stuff is done in T-SQL but other work is done in other languages.  So learning T-SQL cursors will actually encourage you to write bad code.  A beginner is usually far more comfortable building a convoluted cursor procedure than learning the SQL techniques needed to get the same results without cursors.

    I had to develop under Sybase ASE with T-SQL. All I can say: never, ever again if I can avoid it. PL/SQL is (in my opinion) far more advanced and mature.

    l.

  • (cs) in reply to dande
    dande:
    Anonymous:
    I don't get it - almost all of the stored procedures I have at work are like this - most are worse!

    Where's the WTF? The fact that I can't see one after having so many shoved down my throat over the last year?

    http://www.drarok.com/Stored.txt

    Really, what's the point in that SP!?!?

    One point could be permissions / security.
    The user does not have insert/update/delete granted, but have it for execute on the stored proc.


    Ah-ha, no. See, all users log on to the database as "sa". Yep. System Administrator access for even the lowliest of users. Good job, team! *thumbs up*
  • (cs) in reply to Drarok

    Assuming that too much hasn't been lost in translation, surely the bigest fault is the fact that like so many triggers I come across it assumes that inserted will only contain 1 row. This seems to be a very common problem.

  • (cs) in reply to tster

    tster:
    Alex,
    you mis-spelled brillance.

    Making Paula Proud

  • (cs) in reply to lofwyr
    lofwyr:

    jsmith:

    I think part of the answer is that cursors in the DB tend to be a bad idea but cursors in client apps tend to be the only way to process the returned data.  Most client apps aren't written with a set oriented language, so they need cursors to manage the data.  In Oracle land, PL/SQL is the recommended server side, client side, and middle tier language, so learning PL/SQL cursors is important for the client and mt portion.  In MS SQL land, server side stuff is done in T-SQL but other work is done in other languages.  So learning T-SQL cursors will actually encourage you to write bad code.  A beginner is usually far more comfortable building a convoluted cursor procedure than learning the SQL techniques needed to get the same results without cursors.

    I had to develop under Sybase ASE with T-SQL. All I can say: never, ever again if I can avoid it. PL/SQL is (in my opinion) far more advanced and mature.


    Of course PL/SQL is more advanced than T-SQL.  T-SQL is a set oriented language with a few procedure extensions thrown in.  You aren't supposed to do procedural programming in T-SQL.  A complete 3 tier Oracle solution often contains just PL/SQL and Oracle SQL running on various Oracle platforms such as Oracle Database Server and Oracle Application Server and maybe Oracle Forms.  A complete 3-tier MS SQL solution would need some platform/language for the first and middle tier such as C# and the .Net framework running on various Windows services such as IIS and COM+.  A comparison of PL/SQL to T-SQL is apples to oranges.  An apples to apples comparison of PL/SQL to C# and T-SQL leaves PL/SQL in the dust.
  • (cs) in reply to jsmith
    jsmith:
    lofwyr:

    jsmith:

    I think part of the answer is that cursors in the DB tend to be a bad idea but cursors in client apps tend to be the only way to process the returned data.  Most client apps aren't written with a set oriented language, so they need cursors to manage the data.  In Oracle land, PL/SQL is the recommended server side, client side, and middle tier language, so learning PL/SQL cursors is important for the client and mt portion.  In MS SQL land, server side stuff is done in T-SQL but other work is done in other languages.  So learning T-SQL cursors will actually encourage you to write bad code.  A beginner is usually far more comfortable building a convoluted cursor procedure than learning the SQL techniques needed to get the same results without cursors.

    I had to develop under Sybase ASE with T-SQL. All I can say: never, ever again if I can avoid it. PL/SQL is (in my opinion) far more advanced and mature.


    Of course PL/SQL is more advanced than T-SQL.  T-SQL is a set oriented language with a few procedure extensions thrown in.  You aren't supposed to do procedural programming in T-SQL.  A complete 3 tier Oracle solution often contains just PL/SQL and Oracle SQL running on various Oracle platforms such as Oracle Database Server and Oracle Application Server and maybe Oracle Forms.  A complete 3-tier MS SQL solution would need some platform/language for the first and middle tier such as C# and the .Net framework running on various Windows services such as IIS and COM+.  A comparison of PL/SQL to T-SQL is apples to oranges.  An apples to apples comparison of PL/SQL to C# and T-SQL leaves PL/SQL in the dust.


    Not really. A typical 3 tier Oracle application with OAS uses Java in the middle tier.
    Oracle Forms is rather outdated, though you can't kill it.
    But: because PL/SQL is relatively powerfull, it's possible to move more logic to the database.
  • (cs) in reply to jsmith
    jsmith:

    Of course PL/SQL is more advanced than T-SQL.  T-SQL is a set oriented language with a few procedure extensions thrown in.  You aren't supposed to do procedural programming in T-SQL.


    Well guess what's beeing done in _Sybase_.

    jsmith:

    A complete 3 tier Oracle solution often contains just PL/SQL and Oracle SQ running on various Oracle platforms such as Oracle Database Server and Oracle Application Server and maybe Oracle Forms.


    Oracle Forms is one option, yes.

    jsmith:

    A complete 3-tier MS SQL solution would need some platform/language for the first and middle tier such as C# and the .Net framework running on various Windows services such as IIS and COM+.  A comparison of PL/SQL to T-SQL is apples to oranges.  An apples to apples comparison of PL/SQL to C# and T-SQL leaves PL/SQL in the dust.


    As long as C# is not directly integrated in the database, you're comparing apples to oranges, I was comparing stored procedure/trigger languages. Another valid comparison, in my opinion, would be Java vs. C#, although both are used for special purpose considering the amount of code you need to accomplish the same things in said  stored procedure languages.

    C.
  • (cs) in reply to lofwyr
    lofwyr:

    As long as C# is not directly integrated in the database, you're comparing apples to oranges.


    Actually it is, .net is integrated in SQLServer 2005.
  • oliverthered (unregistered) in reply to HardCode
    Anonymous:
    Nothing like a "person customer" entity, as opposed to ... I guess ... a robot customer, or an animal customer?

    What about a company customer?
  • (cs) in reply to ammoQ

    ammoQ:
    lofwyr:

    As long as C# is not directly integrated in the database, you're comparing apples to oranges.


    Actually it is, .net is integrated in SQLServer 2005.

    As tightly as JAVA in Oracle. You have seen some of the code examples for sqlserver/.net, right?

    l.

     

  • me love you long time (unregistered) in reply to FredSaw
    FredSaw:

    "person customer" -- reminds me of that line in CSN's "Southern Cross": 

    Searching for that woman girl
    Who knows love can endure

    As opposed to a man girl, I guess.



    or a lady boy...
  • robert (unregistered)

    What is really going here is that the coder of this works for a big corporation and it took far less paperwork to get the DBA to install this trigger than it did to get the DBA to setup RI because the DBA didn't do the design and won't touch it, because it belongs to someone labeling themself as an 'architect' and that architect won't hear anything that anybody below them has to say.

  • It's Atomic! (unregistered)

    (Sorry, I'm late!)

     
    Does anyone else feel uneasy when seeing variables containing the word "number" declared as varchar(20)?

  • (cs) in reply to It's Atomic!
    Anonymous:

    (Sorry, I'm late!)

     
    Does anyone else feel uneasy when seeing variables containing the word "number" declared as varchar(20)?

    Not at all. Number as in "house number", "customer number", "product number" is not necessarily digits only; in the database, it must be varchar.
     

  • Anonymous 2 (unregistered) in reply to ammoQ

    Then, why not call them "house code", "costomer code", and "product code"?  :-b

     

  • (unregistered) in reply to jsmith
    jsmith:
    lofwyr:
    jsmith:
    I think part of the answer is that cursors in the DB tend to be a bad idea but cursors in client apps tend to be the only way to process the returned data.  Most client apps aren't written with a set oriented language, so they need cursors to manage the data.  In Oracle land, PL/SQL is the recommended server side, client side, and middle tier language, so learning PL/SQL cursors is important for the client and mt portion.  In MS SQL land, server side stuff is done in T-SQL but other work is done in other languages.  So learning T-SQL cursors will actually encourage you to write bad code.  A beginner is usually far more comfortable building a convoluted cursor procedure than learning the SQL techniques needed to get the same results without cursors.
    I had to develop under Sybase ASE with T-SQL. All I can say: never, ever again if I can avoid it. PL/SQL is (in my opinion) far more advanced and mature.
    Of course PL/SQL is more advanced than T-SQL.  T-SQL is a set oriented language with a few procedure extensions thrown in.  You aren't supposed to do procedural programming in T-SQL.  A complete 3 tier Oracle solution often contains just PL/SQL and Oracle SQL running on various Oracle platforms such as Oracle Database Server and Oracle Application Server and maybe Oracle Forms.  A complete 3-tier MS SQL solution would need some platform/language for the first and middle tier such as C# and the .Net framework running on various Windows services such as IIS and COM+.  A comparison of PL/SQL to T-SQL is apples to oranges.  An apples to apples comparison of PL/SQL to C# and T-SQL leaves PL/SQL in the dust.

Leave a comment on “Classic WTF - And I think I'll call it .... &quot;Referential Integrity&quot; !”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article