в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:06:36Z

1 версия

Новая страница

== Create a sequence==



<source lang="sql">
SQL>
SQL>
SQL> CREATE SEQUENCE myTableIDSeq;
Sequence created.
SQL>
SQL>
SQL> drop sequence myTableIDSeq;
Sequence dropped.
SQL></source>

== Create a sequence starting with 1000==



<source lang="sql">
SQL>
SQL> create sequence s_emp_id
2 start with 1000;
Sequence created.
SQL>
SQL>
SQL> drop sequence s_emp_id;
Sequence dropped.
SQL></source>

== Create a sequence starting with "100000000 order"==



<source lang="sql">
SQL>
SQL> create sequence EXTERNAL_ID_SEQ
2 start with 100000000 order;
Sequence created.
SQL>
SQL> drop sequence EXTERNAL_ID_SEQ;
Sequence dropped.
SQL></source>

== create sequence deptno_seq start with 50 increment by 10==



<source lang="sql">
SQL>
SQL>
SQL> create sequence deptno_seq start with 50 increment by 10;
Sequence created.
SQL>
SQL> select deptno_seq.nextval, deptno_seq.currval
2 from dual;
NEXTVAL CURRVAL
---------- ----------
50 50
SQL>
SQL>
SQL> drop sequence deptno_seq;
Sequence dropped.
SQL>
SQL></source>

== create sequence emps_seq start with 501 increment by 10==



<source lang="sql">
SQL>
SQL> create sequence emps_seq
2 start with 501
3 increment by 10;
Sequence created.
SQL>
SQL> drop sequence emps_seq;
Sequence dropped.
SQL>
SQL></source>

== CREATE SEQUENCE myTableIDSeq INCREMENT BY 1 START WITH 1000;==



<source lang="sql">
SQL>
SQL>
SQL>
SQL> CREATE SEQUENCE myTableIDSeq INCREMENT BY 1 START WITH 1000;
Sequence created.</source>

== CREATE SEQUENCE test_seq;==



<source lang="sql">
Sequence created.
SQL>
SQL> SELECT test_seq.nextval FROM dual;
NEXTVAL
----------
1
SQL> SELECT test_seq.currval FROM dual;
CURRVAL
----------
1
SQL> SELECT test_seq.nextval FROM dual;
NEXTVAL
----------
2
SQL>
SQL> ALTER SEQUENCE test_seq
2 INCREMENT BY 2;
Sequence altered.
SQL>
SQL> SELECT test_seq.nextval FROM dual;
NEXTVAL
----------
4
SQL> SELECT test_seq.currval FROM dual;
CURRVAL
----------
4
SQL> SELECT test_seq.nextval FROM dual;
NEXTVAL
----------
6
SQL>
SQL>
SQL> drop sequence my_seq;
SQL></source>

== Create the simplest sequence==



<source lang="sql">
SQL>
SQL> CREATE SEQUENCE test_seq;
Sequence created.
SQL>
SQL>
SQL> drop sequence test_seq;
Sequence dropped.
SQL></source>

== Creating a sequence and then get the next value==



<source lang="sql">
SQL>
SQL> CREATE SEQUENCE test_seq;
Sequence created.
SQL>
SQL> SELECT test_seq.nextval FROM DUAL;
NEXTVAL
----------
1
SQL> SELECT test_seq.nextval FROM DUAL;
NEXTVAL
----------
2
SQL> SELECT test_seq.nextval FROM DUAL;
NEXTVAL
----------
3
SQL>
SQL> DROP SEQUENCE test_seq;
Sequence dropped.
SQL>
SQL></source>

== Sequences==

A sequence is a database item that generates a sequence of integers.
You typically use the integers generated by a sequence to populate a numeric primary key column.
You create a sequence using the CREATE SEQUENCE statement, which has the following syntax:



<source lang="sql">
CREATE SEQUENCE sequence_name
[START WITH start_num]
[INCREMENT BY increment_num]
[ { MAXVALUE maximum_num | NOMAXVALUE } ]
[ { MINVALUE minimum_num | NOMINVALUE } ]
[ { CYCLE | NOCYCLE } ]
[ { CACHE cache_num | NOCACHE } ]
[ { ORDER | NOORDER } ];</source>

== Test sequence step==



<source lang="sql">
SQL>
SQL> CREATE SEQUENCE test_seq
2 START WITH 10 INCREMENT BY 5
3 MINVALUE 10 MAXVALUE 20
4 CYCLE CACHE 2 ORDER;
Sequence created.
SQL>
SQL>
SQL> SELECT test_seq.nextval FROM dual;
NEXTVAL
----------
10
SQL>
SQL> SELECT test_seq.nextval FROM dual;
NEXTVAL
----------
15
SQL>
SQL> SELECT test_seq.nextval FROM dual;
NEXTVAL
----------
20
SQL>
SQL> SELECT test_seq.nextval FROM dual;
NEXTVAL
----------
10
SQL>
SQL>
SQL> drop sequence test_seq
2 /
Sequence dropped.
SQL>
SQL></source>

== Using a sequence to populate a table"s column==



<source lang="sql">
SQL>
SQL>
SQL> CREATE TABLE test (
2 record_id NUMBER(18,0),
3 record_text VARCHAR2(10)
4 );
Table created.
SQL>
SQL> CREATE SEQUENCE test_seq;
Sequence created.
SQL>
SQL> SELECT test_seq.nextval FROM DUAL;
NEXTVAL
----------
1
SQL> SELECT test_seq.nextval FROM DUAL;
NEXTVAL
----------
2
SQL> SELECT test_seq.nextval FROM DUAL;
NEXTVAL
----------
3
SQL>
SQL>
SQL> INSERT INTO test VALUES (
2 test_seq.nextval,
3 "Record A"
4 );
1 row created.
SQL>
SQL> INSERT INTO test VALUES (
2 test_seq.nextval,
3 "Record B"
4 );
1 row created.
SQL>
SQL> SELECT * FROM test;
RECORD_ID RECORD_TEX
---------- ----------
4 Record A
5 Record B
SQL>
SQL>
SQL> DROP SEQUENCE test_seq;
Sequence dropped.
SQL>
SQL> drop table test;
Table dropped.
SQL></source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/Sequences/Create Sequence - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия