Results 1 to 7 of 7

Thread: Procedure

  1. #1
    Join Date
    Dec 2004
    Posts
    37

    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;

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    In which rdbms?

  3. #3
    Join Date
    Dec 2004
    Posts
    37
    Oracle 10g.

  4. #4
    Join Date
    Oct 2005
    Posts
    2,557
    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;

  5. #5
    Join Date
    Dec 2004
    Posts
    37
    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.

  6. #6
    Join Date
    Oct 2005
    Posts
    2,557
    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;

  7. #7
    Join Date
    Dec 2004
    Posts
    37
    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
  •