-
Procedure
Hey guys, I have created the following procedure but the problem is that the SELECT INTO will not work if more than one row is selected I was just wondering if there is a way round this problem? Thanks.
Code:
CREATE OR REPLACE PROCEDURE HOURS IS
HOURVAR NUMBER;
BEGIN
SELECT HOURS
INTO HOURVAR
FROM ADMIN
THEN
IF HOURVAR > 250
UPDATE SECTION
SET RATE = 'RRC'
END IF
END;
-
-
-
SELECT HOURS
INTO HOURVAR
FROM ADMIN
where rownum = 1;
But, this is generally bad - how many possible values for hours are there in the table? If what you are doing is checking for existence of hours being more than 250, and one instance is all that is needed, then this will work. Or, you could do:
SELECT count(*) into hourvar
FROM ADMIN
where hours > 250
and rownum = 1;
If hourvar > 0 then
do what you were doing before
end if;
-
Thanks for your reply, what I am trying to do is to is select all the hours from that table and then compare them one at a time and then change the rate code in the section table for each person whos hours are over 250. Or if the hours are below 250 then that persons record will not change.
Last edited by 182; 12-01-2005 at 01:15 PM.
-
Then you will need to use a cursor. The general process is shown below. You will have to do some reading on cursors and their syntax and make a modification or two before this will work. How is a record in admin with hours > 250 related to a record in section?
CREATE OR REPLACE PROCEDURE HOURS IS
HOURVAR NUMBER;
cursor c_hour_check is
SELECT HOURS
HOURVAR
FROM ADMIN;
begin
for r_hours in c_hour_check loop
If HOURS > 250
UPDATE SECTION
SET RATE = 'RRC';
end if;
end loop;
END IF
END;
-
Thanks again for your advice.
Last edited by 182; 12-01-2005 at 06:27 PM.
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
|
|