implicit vs explicit
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: implicit vs explicit

  1. #1
    Join Date
    Dec 2000
    Posts
    126

    implicit vs explicit

    Hi Gurus,

    Can i have your expert opinion about the following :

    1 . open cursor
    fecth record into var -- fecth 1 record only
    close cursor


    2. select bla into var


    a developer - he claimed to be expert in PL/SQL - say that 1 is faster than 2.

    What do you think ?

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    try it and find out, or better yet ask him why he thinks so because obviously that is not a complete example

  3. #3
    Join Date
    Sep 2007
    Posts
    36
    As Davey says, why not try some benchmarking and find out.

    It depends on a number of factors, but generally the implicit cursor will be faster.

    'Select into' used to be almost frowned upon, maybe that is where the developer is coming from.

    It's much easier to interpret:

    select field1 into variable1 from table1;


    than

    cursor cursor1 is
    select field1
    from table1;

    begin

    open cursor1;
    fetch cursor1
    into variable1;
    close cursor1;



    I know which one I'd rather read.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by culonbu
    What do you think ?
    Most people thinks your developer is dead wrong... http://asktom.oracle.com/pls/asktom/...:1205168148688
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Dec 2000
    Posts
    126

    proven

    Thank you for all your replies.

    I have checked various forums , everyone agrees that implcit is better and less prone for error, but this developer insists that explicit is better according to Joakim Treugut ( http://www.linkedin.com/pub/2/2A0/826) and does not want to change - he always use explicit cursor.

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    ask him why, prove it - should be fairly simple if he insists it is better

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Support Dave.

    On top of it, using explicit cursor all the time makes code very difficult to read which will negatively impact code maintenance.

    Is the guy part of your team, are you his boss?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,003
    The question should not be which is faster but which is better. The answer depends on what you are doing. Unless you only want one row or you are doing a bulk collect into a collection don't use a select into. If you want every row that a query returns then by all means use an implicit cursor. There is less stuff to type and the cursor will be opened and closed for you. However, if you might exit the for loop early, then you might want an explicit cursor.

    I worked on a metadata modeling tool years ago and I replaced dozens of cases of select count(*) into...if count(*) > 1... code with functions that opened a cursor read a single line, closed the cursor of course, then returned either true or false depending on whether or not there was data. I suppose I could have also done a select into with rownum=1. Either way I would have to handle the error if no data was found, which meant that the row did not exist.
    this space intentionally left blank

  9. #9
    Join Date
    Sep 2007
    Posts
    36
    Quote Originally Posted by gandolf989
    Unless you only want one row or you are doing a bulk collect into a collection don't use a select into.
    Gandolf, I'm confused by this statement.
    If I read right, you are saying for:

    SINGLE rows, use select into (implicit cursor)
    MULTIPLE rows use select bulk collect into (again, implicit cursor)

    What else is there (aside from a cursor that returns no rows)?

    Quote Originally Posted by gandolf989
    I suppose I could have also done a select into with rownum=1. Either way I would have to handle the error if no data was found, which meant that the row did not exist.
    I'm not sure the function based approach is the best here. If I have a table with no rows and issue:

    SQL> TRUNCATE TABLE j1;

    Table truncated.

    SQL> SELECT COUNT(*)
    2 FROM j1;

    COUNT(*)
    ----------
    0

    There is no error handling required. I'm completely open to new ideas, were you simply trying to improve readability, or is there another reason?
    Thanks,
    J

  10. #10
    Join Date
    Dec 2000
    Posts
    126

    consultant

    Thanks guy.

    he's working for a consultant company contracting to this project.

    i wonder how he got the job in the first place.

    implicit is better than explicit in oracle ver 7

    he must be in the STONE-aged

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width