Insert All with Sequence

View All Public Scripts Login to Run Script

Script Name Insert All with Sequence
Visibility Unlisted - anyone with the share link can access
Description Demo for AskTom
Contributor John Keymer
Created Tuesday November 29, 2016

Statement 1
```
Create Sequence xxseq
```
```
Sequence created.
```

Statement 2

Create Table xxa (a Number)

Table created.

Statement 3

Create Table xxb (a Number)

Table created.

Statement 4

Insert All 
  When rn = 1 Then Into xxa(a) Values (xxseq.nextval) 
  When 1=1 Then Into xxb(a) Values (xxseq.currval) 
Select Level rn From Dual Connect By Level <= 5

6 row(s) inserted.

Statement 5
```
Select a From xxa
```
```
A
1
```

A
1

Statement 6

Select a From xxb


A
1
2
3
4
5

5 rows selected.

A
1
2
3
4
5

Statement 7

Create Function xxgetseq(p_method In Varchar2) Return Number 
Is 
Begin 
  If p_method = 'NEXTVAL' Then 
    Return xxseq.nextval; 
  Elsif p_method = 'CURRVAL' Then  
    Return xxseq.currval; 
  End If; 
End;

Function created.

Statement 8
```
Delete xxa
```
```
1 row(s) deleted.
```
Statement 9
```
Delete xxb
```
```
5 row(s) deleted.
```
Statement 10
```
Drop Sequence xxseq
```
```
Sequence dropped.
```
Statement 11
```
Create Sequence xxseq
```
```
Sequence created.
```

Statement 12

Insert All 
  When rn = 1 Then Into xxa(a) Values (xxgetseq(p_method=>'NEXTVAL')) 
  When 1=1 Then Into xxb(a) Values (xxgetseq(p_method=>'CURRVAL')) 
Select Level rn From Dual Connect By Level <= 5

6 row(s) inserted.

Statement 13
```
Select a From xxa
```
```
A
1
```

A
1

Statement 14

Select a From xxb


A
1
1
1
1
1

5 rows selected.

A
1
1
1
1
1

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database