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

http://www.dbasupport.com/forums/ima.../2006/03/1.jpg

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. ;)

Isn't this just an update statement?

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

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?

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

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;
/

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.

Use "Decode" Statement at first query itself...

Quote:

---

Originally Posted by Sathy

Use "Decode" Statement at first query itself...

---

what does decode do??

Quote:

---

Originally Posted by stecal

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;
/

---

Thanks alot but its still not compiling it correctly.

This is what i typed


DECLARE
CURSOR STK_CURSOR IS
SELECT * FROM MM_MOVIE
FOR UPDATE NOWAIT;
BEGIN
FOR STK_REC IN STK_CURSOR LOOP
IF STK_REC.STK_FLAG > 75 THEN
UPDATE MM_MOVIE
SET STK_FLAG = '*'
WHERE CURRENT OF STK_CURSOR;
ELSE SET STK_FLAG = 'NULL'
WHERE CURRENT OF STK_CURSOR;
END IF;
END LOOP;
END;
/

And these are the errors its telling me.



FOR UPDATE NOWAIT;
*

ERROR at line 4:
ORA-06550: line 11, column 10:
PL/SQL: ORA-00922: missing or invalid option
ORA-06550: line 11, column 6:
PL/SQL: SQL Statement ignored

This is a stupid assignment. :rolleyes: What about writing a
trigger that flags when someone is trying to cause a negative
inventory? What about creating a package that contains all of your
business logic? i.e. adding inventory, selling inventory, discounting
inventory based on what isn't selling. What about writing a logging
and error handling package? There are a lot of better projects that
you could be working on besides this.

--- end of rant ---

You forgot the update in the second update
statement. You should have a commit in your code. Do you
want the word NULL or the value NULL. If you put it in quotes
you will get the word NULL rather than the value NULL. You are
also corrupting your data by setting it to '*'. You should have
an overstock flag that gets set and unset as inventory is added
and removed. This flag should probably get set through a trigger.

Code:

---

DECLARE
  CURSOR STK_CURSOR IS
      SELECT * FROM MM_MOVIE
        FOR UPDATE NOWAIT;
BEGIN
  FOR STK_REC IN STK_CURSOR LOOP
      IF STK_REC.STK_FLAG > 75 THEN
        UPDATE MM_MOVIE
            SET STK_FLAG = '*'
          WHERE CURRENT OF STK_CURSOR;
      ELSE
        UPDATE MM_MOVIE
            SET STK_FLAG = NULL
          WHERE CURRENT OF STK_CURSOR;
      END IF;
  END LOOP;

  COMMIT;
END;
/

---

Quote:

---

Originally Posted by gandolf989

This is a stupid assignment. :rolleyes: What about writing a
trigger that flags when someone is trying to cause a negative
inventory? What about creating a package that contains all of your
business logic? i.e. adding inventory, selling inventory, discounting
inventory based on what isn't selling. What about writing a logging
and error handling package? There are a lot of better projects that
you could be working on besides this.

--- end of rant ---

You forgot the update in the second update
statement. You should have a commit in your code. Do you
want the word NULL or the value NULL. If you put it in quotes
you will get the word NULL rather than the value NULL. You are
also corrupting your data by setting it to '*'. You should have
an overstock flag that gets set and unset as inventory is added
and removed. This flag should probably get set through a trigger.

Code:

---

DECLARE
  CURSOR STK_CURSOR IS
      SELECT * FROM MM_MOVIE
        FOR UPDATE NOWAIT;
BEGIN
  FOR STK_REC IN STK_CURSOR LOOP
      IF STK_REC.STK_FLAG > 75 THEN
        UPDATE MM_MOVIE
            SET STK_FLAG = '*'
          WHERE CURRENT OF STK_CURSOR;
      ELSE
        UPDATE MM_MOVIE
            SET STK_FLAG = NULL
          WHERE CURRENT OF STK_CURSOR;
      END IF;
  END LOOP;

  COMMIT;
END;
/

---

---

I agree about the assignment, there are so many more efficient ways of doing this it's mind boggling. I think they want students to be familiar with different ways of setting up condition statements so they can be "real world" ready after graduating. To make things worse this is an online class, so i dont even get to sit and chat with the teacher unless its through email or any other type of communication medium like that.

I appreciate all of the help everyone has given me on this, its greatly appreciated.

I want the value to be null not the word null.

I ran the program it works, but nothing is being outputted to the stk_flag column

Use dbms_output.put_line to print out values and see what your program is doing.
Make sure that you put DBMS_OUTPUT.ENABLE(1000000); as the first line after the begin statement.

Quote:

---

Originally Posted by gandolf989

Use dbms_output.put_line to print out values and see what your program is doing.
Make sure that you put DBMS_OUTPUT.ENABLE(1000000); as the first line after the begin statement.

---

I'm using SQL+ and it doesnt like the commands you told me to to enter after the begin statement.

Quote:

---

Originally Posted by Jplaya2023

I'm using SQL+ and it doesnt like the commands you told me to to enter after the begin statement.

---

I'm not sure what you mean.

Code:

---

DECLARE
  CURSOR STK_CURSOR IS
      SELECT * FROM MM_MOVIE
        FOR UPDATE NOWAIT;
BEGIN
  DBMS_OUTPUT.ENABLE(1000000);
  FOR STK_REC IN STK_CURSOR LOOP
      IF STK_REC.STK_FLAG > 75 THEN
        dbms_output.put_line ('STK_REC.STK_FLAG > 75 - ' || STK_REC.STK_FLAG);
        UPDATE MM_MOVIE
            SET STK_FLAG = '*'
          WHERE CURRENT OF STK_CURSOR;
        dbms_output.put_line ('Rows updated: ' || SQL%ROWCOUNT);
      ELSE
        dbms_output.put_line ('STK_REC.STK_FLAG <= 75 - ' || STK_REC.STK_FLAG);
        UPDATE MM_MOVIE
            SET STK_FLAG = NULL
          WHERE CURRENT OF STK_CURSOR;
        dbms_output.put_line ('Rows updated: ' || SQL%ROWCOUNT);
      END IF;
  END LOOP;

  COMMIT;
END;
/

---

This is what's happening, I created mt column, and did my calculations so it shows the updated columns, this is the result

http://www.dbasupport.com/forums/ima.../2006/03/2.jpg

Then i ran the syntax you had gave me, and my column just shows a blank now on a select


http://www.dbasupport.com/forums/ima.../2006/03/3.jpg

Try this instead. You may want to use sqlplus if you have it.

Code:

---

set serveroutput on

DECLARE
  CURSOR STK_CURSOR IS
      SELECT movie_id,
            movie_value*movie_qty as movies_in_stock
        FROM MM_MOVIE;
BEGIN
  DBMS_OUTPUT.ENABLE(1000000);
  FOR STK_REC IN STK_CURSOR
  LOOP
      IF STK_REC.movies_in_stock > 75 THEN
        dbms_output.put_line ('STK_REC.movies_in_stock > 75 - '
                            || STK_REC.movies_in_stock);
        UPDATE MM_MOVIE
            SET STK_FLAG = '*'
          WHERE movie_id = STK_REC.movie_id;
        dbms_output.put_line ('Rows updated: ' || SQL%ROWCOUNT);
      ELSE
        dbms_output.put_line ('STK_REC.movies_in_stock <= 75 - '
                            || STK_REC.movies_in_stock);
        UPDATE MM_MOVIE
            SET STK_FLAG = STK_REC.movies_in_stock
          WHERE movie_id = STK_REC.movie_id;
        dbms_output.put_line ('Rows updated: ' || SQL%ROWCOUNT);
      END IF;
  END LOOP;

  COMMIT;
END;
/

---

http://www.dbasupport.com/forums/ima.../2006/03/4.jpg

What kind of field is stk_flag? I would guess that it is a number field. In which case you can't set it to '*'.

Quote:

---

Originally Posted by gandolf989

What kind of field is stk_flag? I would guess that it is a number field. In which case you can't set it to '*'.

---

yes stk_flag is a column with number declaration. So do i rid myself of the quotes??? or do i use the := operator?

Quote:

---

Originally Posted by Jplaya2023

yes stk_flag is a column with number declaration. So do i rid myself of the quotes??? or do i use the := operator?

---

You can not assign * to a number column with or without quotes. You can change it to a varchar2,
or you can assign a number to it. The := operator only work when assigning values within
pl/sql, not within a sql statement such as update.

Perhaps you should go back to the Devry Institute and get them to help you figure this out.
You are paying them aren't you??? :rolleyes:

Quote:

---

Originally Posted by gandolf989

You can not assign * to a number column with or without quotes. You can change it to a varchar2,
or you can assign a number to it. The := operator only work when assigning values within
pl/sql, not within a sql statement such as update.

Perhaps you should go back to the Devry Institute and get them to help you figure this out.
You are paying them aren't you??? :rolleyes:

---

Yeah my problem was i was declaring stk_flag as a number instead of a varchar so it wouldnt output a number. Thankyou for all your help i appreciate it.

It's an online class so its really tough to get help but i appreciate everyone who helped, thanks.