CODEWITHSUNDEEP
sqloracle-databaseloopsplsql
DaBulls33
DaBulls33

Reputation: 171

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

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;

Upvotes: 0

Views: 2353

Answers (1)

Art
Art

Reputation: 5782

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

Upvotes: 1

Related Questions