-
Junior Member
Inserting value using FOR LOOP in PROCEDURE
Dear all
i need help,
i created a table named ABC with one column named A.
now i want to insert 10 values in this column using procedure.
i am using this code with errors.
1 create or replace procedure ins
2 (i IN number)
3 AS
4 -- i number :=2;
5 BEGIN
6 LOOP
7 insert into abc(a)
8 values (i);
9 i :=i+1;
10 EXIT WHEN i>11;
11 END LOOP;
12* END ins;
SQL> /
Warning: Procedure created with compilation errors.
SQL> show error;
Errors for PROCEDURE INS:
LINE/COL ERROR
-------- -----------------------------------------------------------------
9/2 PLS-00363: expression 'I' cannot be used as an assignment target
9/2 PL/SQL: Statement ignored
please write me correct code how to enter values using procedure.
thank
-
Expert Member
Re: Inserting value using FOR LOOP in PROCEDURE
In this procedure you have used i as in paramater. You can not assign a value to in parameter . Rewrite the procedure as follows
create or replace procedure ins
AS
i number :=1;
BEGIN
LOOP
insert into abc(a) values (i);
i :=i+1;
EXIT WHEN i>11;
END LOOP;
COMMIT;
END ins;
The above procedure will insert 10 recods.
-
Expert Member
Re: Inserting value using FOR LOOP in PROCEDURE
Try this
set serveroutput on
create or replace procedure ins
AS
j number;
BEGIN
j:=1;
LOOP
insert into abc (a)
values (j);
j :=j+1;
EXIT WHEN j>10;
END LOOP
commit;
END ins;
exec ins;
select * from abc;
-
Expert Member
Re: Inserting value using FOR LOOP in PROCEDURE
If you want to insert 10 values starting with number that was passed as parameter write it as follows
create or replace procedure ins (x in number)
AS
i number :=1;
y number;
BEGIN
y := x;
LOOP
insert into abc(a) values (x);
y :=y+1;
i :=i+1;
EXIT WHEN i>11;
END LOOP;
COMMIT;
END ins;
But do not use same identifier as in paramter and variable.
Last edited by krishnaindia2007; 01-04-2008 at 03:51 AM.
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