Getting a PL/SQL Cursor for loop to work with input variable?

Question

I have to produce PL/SQL block that takes the input in of an integer where n (1 ≤ n ≤ 100) and display the most popular names for male and female. If the number entered is out of that range display an invalid input. I have created a table called popular names that has 100 of the most popular names for male and female. I am struggling with my FOR loop and getting it to work with the user input. My code is below:

ACCEPT p_1 prompt 'please enter an integer between 1 and 100'

DECLARE
    CURSOR name_cur IS
            SELECT    rank, male_given_name, female_given_name
            FROM          popular_names
          ORDER BY  rank;

        v_nrank         popular_names.rank%TYPE := &p_1;
        v_nmale           popular_names.male_given_name%TYPE;
        v_nfemale     popular_names.female_given_name%TYPE;



BEGIN   
      OPEN name_cur;
      FETCH name_cur INTO v_nrank, v_nmale, v_nfemale;

      IF name_cur%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Rank     Male Given Name     Female Given Name');
        DBMS_OUTPUT.PUT_LINE('----------------------------------------------');


        FOR i IN 1..v_nrank LOOP
            DBMS_OUTPUT.PUT_LINE((RPAD(v_nrank, 18) || '  ' || 
                (RPAD(v_nmale, 18) || '  ' || v_nfemale)));
                FETCH name_cur INTO v_nrank, v_nmale, v_nfemale; 
              EXIT WHEN name_cur%NOTFOUND;
        END LOOP;


      ELSE
          DBMS_OUTPUT.PUT_LINE('Invalid number!');
      END IF;

        CLOSE name_cur;
END;

So if the user inputs a 10 the Names of male and females 1-10 would display. — DaBulls33
– DaBulls33, Commented Apr 25, 2013 at 18:20

Art · Accepted Answer · 2013-04-25 18:47:31Z

1

No need for cursor. Or use similar query in your cursor. You can use Rownum pseudocolumn in place of Row_Number() function:

SELECT * FROM
(
 SELECT deptno
      , empno
      , ename
      , Row_Number() OVER (ORDER BY empno) AS row_seq -- can order/partition by any field 
      -- , Rownum AS row_seq
  FROM scott.emp
) 
WHERE row_seq BETWEEN 1 AND 10 -- any integer as in your example --
/


DECLARE
  CURSOR e_cur 
  IS
  SELECT * FROM
  (
   SELECT deptno
     , empno
     , ename
     , Row_Number() OVER (ORDER BY empno) AS row_seq --'AS' is for clarity-not required
     --, Rownum AS row_seq 
    FROM scott.emp
   ) 
  WHERE row_seq BETWEEN 1 AND 10 -- any integer, can use variable like &num --
  ORDER BY row_seq;

  v_deptno  scott.dept.deptno%TYPE;
  v_emp_no  scott.emp.empno%TYPE;
  v_name    scott.emp.ename%TYPE;
  v_rank    NUMBER;
BEGIN
  OPEN e_cur;
  LOOP
    FETCH e_cur INTO v_deptno, v_emp_no, v_name, v_rank;
    EXIT WHEN e_cur%NOTFOUND;
      dbms_output.put_line(v_rank||chr(9)||v_deptno||chr(9)||v_emp_no||chr(9)||v_name);
  END LOOP;
 CLOSE e_cur;
END;
/

edited Apr 25, 2013 at 18:47

answered Apr 25, 2013 at 18:27

Art

5,8222 gold badges24 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DaBulls33 Over a year ago

I am required to use a cursor :/

Collectives™ on Stack Overflow

Getting a PL/SQL Cursor for loop to work with input variable?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related