DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: Can someone help me on Cursor For Loops?!?!?!

  1. #1
    Join Date
    Mar 2006
    Posts
    11

    Can someone help me on Cursor For Loops?!?!?!

    Hi im a newbie when it comes to this PL/SQL syntax and such. I have a program loaded in which im suppose to do a cursor for loop and check for something being greater than a value of and if its true add a '*' to that column else write a null in the column.

    Here is the screenshot of the program



    Now im suppose to write a cursor for loop that checks the column stk_flag and compares it to 75, if greater it adds an '*' to that rows particular field is not add a null.

    Could someone give me some insight on this thanks.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,492

    Cool


    Look into using the CASE statement.
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Isn't this just an update statement?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  4. #4
    Join Date
    Mar 2006
    Posts
    11
    Quote Originally Posted by LKBrwn_DBA

    Look into using the CASE statement.

    I dont think i can use a case statement the directions said i had to use a cursor for loop using PL/SQL blocking structure.

    This is what the directions say

    As business is becoming strong and the movie stock is growing for the More Movie Rentals, the manager wants to do more inventory evaluations. One item of interest concerns any movie for which the company is holding $75 or more in value. The manager wants to focus on these movies in regards to their revenue generation to ensure the stock level is warranted. To make these stock queries more efficient, the application team decides that a column should be added to the MM_MOVIE table named STK_FLAG that will hold a value '*' if stock is $75 or more. Otherwise the value should be NULL. Add the needed column and create an anonymous block that contains a CURSOR FOR loop to accomplish the task. The company will run this program monthly to update the STK_FLAG column before the inventory evaluations.

    Make sure that you display the table structure of MM_MOVIE using DESC, and SELECT all data from this table before and after you do this exercise to show the effect of your block. Submit all code and Oracle responses, but please edit it to remove erroneous attempts.


    I already created the stk_flag column and multiplied the values, im just unsure how to do that cursor for loop to do the checks for numbers

  5. #5
    Join Date
    May 2002
    Posts
    2,645
    Sure sounds like a homework problem. No textbook for the course? The instructor left you high and dry on how to code a cursor for loop? Absolutely no references at your disposal, and you live somewhere in China where all of the Internet's search engines are unavailable to you, but yet, somehow, dbasupport.com is available to you? Is this the case?

  6. #6
    Join Date
    Mar 2006
    Posts
    11
    Quote Originally Posted by stecal
    Sure sounds like a homework problem. No textbook for the course? The instructor left you high and dry on how to code a cursor for loop? Absolutely no references at your disposal, and you live somewhere in China where all of the Internet's search engines are unavailable to you, but yet, somehow, dbasupport.com is available to you? Is this the case?

    Nah man, im just having trouble with this and thought someone here could help me

  7. #7
    Join Date
    May 2002
    Posts
    2,645
    declare
    cursor inventory_cur is
    select * from movie_table
    for update;
    begin
    for stk_rec in inventory_cur loop
    if stk_rec.stk_flag > 75 then
    update movie_table
    set stk_flag = whatever
    where current of inventory_cur;
    -- you can add in the else branch
    end loop;
    end;
    /

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by Jplaya2023
    To make these stock queries more efficient, the application team decides that a column should be added to the MM_MOVIE table named STK_FLAG that will hold a value '*' if stock is $75 or more. Otherwise the value should be NULL. Add the needed column and create an anonymous block that contains a CURSOR FOR loop to accomplish the task. The company will run this program monthly to update the STK_FLAG column before the inventory evaluations.[/B]
    Holy moly, OK it's an academic exercise, but as a design, this s-u-c-k-s. It gives the phrase "academic exercise" a bad name.

    From the logical point of view it introduces a completely redundant element that is only accurate once a month.

    From the physical point of view the rows that meet thiscondition are probably scattered randomly throughout the table and index-based access on this column is going to do nothing for you. If you were going to flag these rows you'd probably create a function-based index on value*qty, and possibly only for rows where value*qty >= 75.

    Also, a cursor for loop is absolutely the wrong way to do this ... more code, less performance, higher resource usage ... just run an update statement.

    And these are academics setting these problems? I'm not criticising Jplaya2023 here, 'cos he/she is a victim of his/her environment, but should anyone be surprised at the sheer quantity of crap designs out there?

    End of rant.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Mar 2004
    Location
    India
    Posts
    72
    Use "Decode" Statement at first query itself...

  10. #10
    Join Date
    Mar 2006
    Posts
    11
    Quote Originally Posted by Sathy
    Use "Decode" Statement at first query itself...

    what does decode do??

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