-
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.
-
Look into using the CASE statement.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
-
Isn't this just an update statement?
-
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
-
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?
-
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
-
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;
/
-
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.
-
Use "Decode" Statement at first query itself...
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|