в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:06:36Z

1 версия

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

== Automatically including unique sequence numbers during an INSERT.==



<source lang="sql">
SQL>
SQL> create sequence mySeq start with 1000 increment by 1 cache 100;
Sequence created.
SQL>
SQL> create table myTable (col1 number primary key);
Table created.
SQL>
SQL> insert into myTable values (mySeq.nextval);
1 row created.
SQL> insert into myTable values (mySeq.nextval);
1 row created.
SQL>
SQL> select * from myTable;

COL1
----------
1000
1001
2 rows selected.
SQL>
SQL> drop table myTable;
Table dropped.
SQL>
SQL> drop sequence mySeq;
Sequence dropped.</source>

== Get next value from sequence==



<source lang="sql">
SQL> create table emp(
2 emp_id integer primary key
3 ,lastname varchar2(20) not null
4 ,firstname varchar2(15) not null
5 ,midinit varchar2(1)
6 ,street varchar2(30)
7 ,city varchar2(20)
8 ,state varchar2(2)
9 ,zip varchar2(5)
10 ,shortZipCode varchar2(4)
11 ,area_code varchar2(3)
12 ,phone varchar2(8)
13 ,company_name varchar2(50));
Table created.
SQL>
SQL> create sequence s_emp_id
2 start with 1000;
Sequence created.
SQL>
SQL> insert into emp (emp_id, lastname, firstname)
2 values (s_emp_id.nextval, "Einstein", "Albert");
1 row created.
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL> drop sequence s_emp_id;
Sequence dropped.</source>

== If id is null, use the value from sequence==



<source lang="sql">
SQL>
SQL> create table myTable(
2 id number primary key,
3 blob_content blob
4 )
5 /
Table created.
SQL>
SQL> create sequence myTable_seq
2 /
Sequence created.
SQL>
SQL> create or replace trigger biu_myTable
2 before insert or update on myTable
3 for each row
4 begin
5 if :new.id is null then
6 select myTable_seq.nextval into :new.id from dual;
7 end if;
8 end;
9 /
Trigger created.
SQL> drop table myTable;
Table dropped.
SQL> drop sequence myTable_seq;
Sequence dropped.</source>

== Once initialized, you can get the current value from the sequence using currval.==



<source lang="sql">
SQL>
SQL> CREATE SEQUENCE test_seq;
Sequence created.
SQL>
SQL>
SQL> SELECT test_seq.nextval FROM dual;
NEXTVAL
----------
1
SQL>
SQL>
SQL> SELECT test_seq.currval FROM dual;
CURRVAL
----------
1
SQL>
SQL>
SQL>
SQL> drop sequence test_seq
2 /
Sequence dropped.
SQL></source>

== Query current sequence value==



<source lang="sql">
SQL>
SQL> create sequence deptno_seq start with 50 increment by 10;
Sequence created.
SQL>
SQL> select deptno_seq.currval
2 from dual;
select deptno_seq.currval
*
ERROR at line 1:
ORA-08002: sequence DEPTNO_SEQ.CURRVAL is not yet defined in this session

SQL>
SQL>
SQL> drop sequence deptno_seq;
Sequence dropped.
SQL>
SQL></source>

== Random value based on sequence==



<source lang="sql">
SQL> create table emp (id number(6) );
Table created.
SQL>
SQL> alter table emp modify id number(12);
Table altered.
SQL>
SQL> create sequence cust_seq cache 1000
2 start with 100000;
Sequence created.
SQL>
SQL> create or replace procedure gen_emp is
2 v_new_cid emp.id%type;
3 begin
4 insert into emp values (cust_seq.nextval*100000+ round(dbms_random.value(100000,999999)));
5 end;
6 /
Procedure created.
SQL>
SQL> begin
2 for i in 1 .. 10000 loop
3 gen_emp;
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop sequence cust_seq;
Sequence dropped.
SQL>
SQL>
SQL> drop table emp;
Table dropped.</source>

== select deptno_seq.currval, deptno_seq.nextval==



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

== Set start value, mini/max value and cache==



<source lang="sql">
SQL>
SQL> CREATE SEQUENCE test_seq
2 START WITH 10 INCREMENT BY -1
3 MINVALUE 1 MAXVALUE 10
4 CYCLE CACHE 5;
Sequence created.
SQL>
SQL> select * from user_sequences where sequence_name="TEST_SEQ";
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ---------- ------------ - - ---------- -----------
TEST_SEQ 1 10 -1 Y N 5 10
SQL>
SQL>
SQL>
SQL> drop sequence test_seq
2 /
Sequence dropped.</source>

== Use dual table to check sequence==



<source lang="sql">
SQL>
SQL> create sequence deptno_seq
2 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> select deptno_seq.currval
2 from dual;
CURRVAL
----------
50
SQL>
SQL> select deptno_seq.currval, deptno_seq.nextval
2 from dual;
CURRVAL NEXTVAL
---------- ----------
60 60
SQL>
SQL>
SQL> drop sequence deptno_seq;
Sequence dropped.</source>

== When you select currval , nextval remains unchanged; nextval only changes when you select nextval to 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>
SQL> SELECT test_seq.nextval, test_seq.currval FROM dual;
NEXTVAL CURRVAL
---------- ----------
2 2
SQL>
SQL> SELECT test_seq.nextval, test_seq.currval FROM dual;
NEXTVAL CURRVAL
---------- ----------
3 3
SQL> SELECT test_seq.nextval, test_seq.currval FROM dual;
NEXTVAL CURRVAL
---------- ----------
4 4
SQL>
SQL> SELECT test_seq.nextval, test_seq.currval FROM dual;
NEXTVAL CURRVAL
---------- ----------
5 5
SQL>
SQL> SELECT test_seq.nextval, test_seq.currval FROM dual;
NEXTVAL CURRVAL
---------- ----------
6 6
SQL>
SQL> SELECT test_seq.nextval, test_seq.currval FROM dual;
NEXTVAL CURRVAL
---------- ----------
7 7
SQL>
SQL> drop sequence test_seq
2 /
Sequence dropped.</source>

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

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

в 13:45, 26 мая 2010

Admin: 1 версия