Taking user input 'n' times PL/SQL

Question

I want to write a PL/SQL program that takes user input 'n' times. Here n is 10, but this only takes input 1 time and displays that input value 10 times as the loop is from 1 to 10. How can I write a program that takes 'n' input values and display them.

set serveroutput on;
DECALRE
num NUMBER(10);
BEGIN

for i in 1..10 
loop
    dbms_output.put_line(&num);
    END loop;
END;
/

score 0 · Answer 1 · answered Oct 01 '17 at 09:15

0

set serveroutput on;
DECALRE
num NUMBER(10) := &num;

BEGIN

for i in 1..10 
loop
    dbms_output.put_line(num);
    END loop;
END;
/

answered Oct 01 '17 at 09:15

Cyrille MODIANO

2,246
2
21
33

Maybe I didn't understand but this part makes me think I'm correct "Here n is 10, but this only takes input 1 time and displays that input value 10 times as the loop is from 1 to 10." – Cyrille MODIANO Oct 01 '17 at 12:49

score 0 · Answer 2 · answered Oct 01 '17 at 09:19

0

PL/SQL is not an interactive language. It is SQL*Plus that is prompting you for a value and it needs to get all the substitution values it needs before it can send the PL/SQL block to the database to be executed.

So, there is no way for PL/SQL to prompt the user for input in loop.

answered Oct 01 '17 at 09:19

Ravi

30,829
42
119
173

task given to me was :- "insert 10 records into a table using loop - pl/sql" – Daniyal Javaid Oct 01 '17 at 09:31
so if there were 5 columns in a table, do i have to use 50 variables for insertion ? – Daniyal Javaid Oct 01 '17 at 09:32
@DaniyalJavaid as I mentioned it is not possible to use interactive action. But, there are several way, you can use loop counter or could use other table – Ravi Oct 01 '17 at 09:33

score 0 · Answer 3 · answered Oct 02 '17 at 07:17

Reading through the comments:

task given to me was :- "insert 10 records into a table using loop - pl/sql"

and

so if there were 5 columns in a table, do i have to use 50 variables for insertion ?

Answer to your above question is NO if you want the same set of records to be inserted to your table 10 times. Below is the way you can do it. When you execute the block, it will ask you to promt value for 5 columns and accordingly it would insert 10 set of records with the same value to the table. See below.

SQL> DECLARE
       num1   NUMBER (10) := &num1;
       num2   NUMBER (10) := &num2;
       num3   NUMBER (10) := &num3;
       num4   NUMBER (10) := &num4;
       num5   NUMBER (10) := &num5;
    BEGIN
       FOR i IN 1 .. 10
          LOOP
          INSERT INTO TAB (col1,
                           col2,
                           col3,
                           col4,
                           col5)
               VALUES (num1,
                       num2,
                       num3,
                       num4,
                       num5);
       END LOOP;
       COMMIT;
    END;
    /   
    Enter value for num1: 1
    old   2:    num1   NUMBER (10) := &num1;
    new   2:    num1   NUMBER (10) := 1;
    Enter value for num2: 2
    old   3:    num2   NUMBER (10) := &num2;
    new   3:    num2   NUMBER (10) := 2;
    Enter value for num3: 3
    old   4:    num3   NUMBER (10) := &num3;
    new   4:    num3   NUMBER (10) := 3;
    Enter value for num4: 4
    old   5:    num4   NUMBER (10) := &num4;
    new   5:    num4   NUMBER (10) := 4;
    Enter value for num5: 5
    old   6:    num5   NUMBER (10) := &num5;
    new   6:    num5   NUMBER (10) := 5;

    PL/SQL procedure successfully completed.

OUTPUT:

SQL> select * from tab;    

      COL1       COL2       COL3       COL4       COL5
---------- ---------- ---------- ---------- ----------
         1          2          3          4          5
         1          2          3          4          5
         1          2          3          4          5
         1          2          3          4          5
         1          2          3          4          5
         1          2          3          4          5
         1          2          3          4          5
         1          2          3          4          5
         1          2          3          4          5
         1          2          3          4          5

10 rows selected.

Taking user input 'n' times PL/SQL

3 Answers3

Linked