-
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. 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;
/
-
Originally Posted by gandolf989
This is a stupid assignment. 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
Last edited by Jplaya2023; 03-17-2006 at 12:12 AM.
-
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.
-
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.
-
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
Then i ran the syntax you had gave me, and my column just shows a blank now on a select
-
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;
/
-
-
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 '*'.
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
|