Home » SQL & PL/SQL » SQL & PL/SQL » Using a cursor with a sequential number
Using a cursor with a sequential number [message #10458] Fri, 23 January 2004 12:06 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: Using a cursor with a sequential number [message #10484 is a reply to message #10478] Mon, 26 January 2004 07:57 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
I'm using two tables in my example (emp = source, emp_copy = target). The first example I gave is still probably your best option. Based on the information you've supplied so far, you don't need a cursor or sequence.
Previous Topic: Two processes - One table - What is most Performant?
Next Topic: Embedded bytes in VarChar2 column
Goto Forum:
  


Current Time: Thu Mar 28 14:37:31 CDT 2024