Using a cursor with a sequential number [message #10458] |
Fri, 23 January 2004 12:06 |
Edith
Messages: 3 Registered: January 2004
|
Junior Member |
|
|
I am trying to do a loop by inserting a sql statement using cursor. The problem is that I have no idea how to do that and that I need to insert a sequential number at the same time. Does anyone have any suggestions or ideas? I know I need NEXTVAL and a variable, but since I am new I have no clue where to begin.
|
|
|
Re: Using a cursor with a sequential number [message #10459 is a reply to message #10458] |
Fri, 23 January 2004 12:52 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
Edith, here are 3 different approaches for generating sequential values. The first uses just a single SQL statement and the ROWNUM pseudocolumn - this is by far the most efficient way. The second uses PL/SQL and an incrementing variable. The third uses a sequence object.
sql>desc emp_copy
Name Null? Type
---------------------------------------- -------- ---------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
SEQ_NO NUMBER(38)
sql>insert into emp_copy
2 select empno, ename, rownum
3 from (select empno, ename
4 from emp
5 where ename between 'A' and 'F'
6 order by ename);
4 rows created.
sql>select * from emp_copy;
EMPNO ENAME SEQ_NO
--------- ---------- ---------
7876 ADAMS 1
7499 ALLEN 2
7698 BLAKE 3
7782 CLARK 4
4 rows selected.
sql>delete from emp_copy;
4 rows deleted.
sql>declare
2 v_seq_no emp_copy.seq_no%type := 1;
3 begin
4 for r in (select empno, ename
5 from emp
6 where ename between 'A' and 'F'
7 order by ename) loop
8 insert into emp_copy values (r.empno, r.ename, v_seq_no);
9 v_seq_no := v_seq_no + 1;
10 end loop;
11 end;
12 /
PL/SQL procedure successfully completed.
sql>select * from emp_copy;
EMPNO ENAME SEQ_NO
--------- ---------- ---------
7876 ADAMS 1
7499 ALLEN 2
7698 BLAKE 3
7782 CLARK 4
4 rows selected.
sql>delete from emp_copy;
4 rows deleted.
sql>create sequence emp_seq;
Sequence created.
sql>insert into emp_copy
2 select empno, ename, emp_seq.nextval
3 from (select empno, ename
4 from emp
5 where ename between 'A' and 'F'
6 order by ename);
4 rows created.
sql>select * from emp_copy;
EMPNO ENAME SEQ_NO
--------- ---------- ---------
7876 ADAMS 1
7499 ALLEN 2
7698 BLAKE 3
7782 CLARK 4
4 rows selected.
|
|
|
Re: Using a cursor with a sequential number [message #10478 is a reply to message #10459] |
Mon, 26 January 2004 03:43 |
Edith
Messages: 3 Registered: January 2004
|
Junior Member |
|
|
Does the PL/SQL work for inputting data from one table into another table using sequential numbers? What you gave me looks like putting information back into the same table but with the addition of sequential numbers. I have created a new table and I want to put information from various joined tables into the new table and at the same time give them a sequential number. I was told I would need a cursor for this and nextval. What suggestions do you have?
Thanks for the help! I appreciate it.
|
|
|
|