в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T09:55:15Z

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 Sequence next value==



<source lang="sql">

SQL>
SQL>
SQL> CREATE SEQUENCE StudentNumSeq
2 INCREMENT BY 1
3 START WITH 1100
4 MAXVALUE 99999
5 NOCACHE
6 NOCYCLE;
Sequence created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select StudentNumSeq.NEXTVAL from DUAL;
NEXTVAL
------------------------
1100.00
SQL>
SQL> drop sequence StudentNumSeq;
Sequence dropped.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL>

</source>

==If new value is null use the value from a sequence==



<source lang="sql">

SQL>
SQL>
SQL> create table myTable (id number);
Table created.
SQL>
SQL> create sequence wo_seq;

SQL>
SQL> create or replace trigger trg_id before insert
2 on myTable
3 for each row
4 when (new.id is null)
5 begin
6 select wo_seq.nextval into :new.id from dual;
7 end;
8 /
Trigger created.
SQL>
SQL> drop table myTable;
Table dropped.
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>

==Sequence current value and next value==



<source lang="sql">

SQL>
SQL> -- It is important to note that the value of CURRVAL is not populated until
after NEXTVAL has been used.
SQL>
SQL>
SQL> CREATE SEQUENCE StudentNumSeq
2 INCREMENT BY 1
3 START WITH 1100
4 MAXVALUE 99999
5 NOCACHE
6 NOCYCLE;
Sequence created.
SQL>
SQL>
SQL> select StudentNumSeq.NEXTVAL from DUAL;
NEXTVAL
------------------------
1100.00
SQL>
SQL>
SQL> SELECT StudentNumSeq.CURRVAL FROM DUAL;
CURRVAL
------------------------
1100.00
SQL>
SQL>
SQL> drop sequence StudentNumSeq;
Sequence dropped.
SQL>
SQL>

</source>

==Sequence max value: 999999999999999999999999999==



<source lang="sql">

SQL>
SQL>
SQL> -- If you omit the MAXVALUE option, your sequence will automatically default to:
SQL>
SQL> -- MAXVALUE 999999999999999999999999999
SQL>
SQL> -- So you can simplify your CREATE SEQUENCE command as follows:
SQL>
SQL> CREATE SEQUENCE mySequence
2 MINVALUE 1
3 START WITH 1
4 INCREMENT BY 1
5 CACHE 20;
Sequence created.
SQL>
SQL> select mySequence.nextval from dual;
NEXTVAL
----------
1
SQL>
SQL>
SQL> drop sequence mySequence;
Sequence dropped.
SQL>

</source>

==Set sequence max value==



<source lang="sql">

SQL>
SQL> -- A CYCLE sequence
SQL>
SQL>
SQL> CREATE SEQUENCE StudentNumSeq
2 INCREMENT BY 50000
3 START WITH 50000
4 MAXVALUE 99999
5 NOCACHE
6 CYCLE;
Sequence created.
SQL>
SQL>
SQL> select studentNumSeq.nextVal from dual;
NEXTVAL
----------
50000
SQL> select studentNumSeq.nextVal from dual;
NEXTVAL
----------
1
SQL> select studentNumSeq.nextVal from dual;
NEXTVAL
----------
50001
SQL>
SQL>
SQL> drop sequence studentNumSeq;
Sequence dropped.
SQL>
SQL>

</source>

==Use Sequence in a procedure==



<source lang="sql">

SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL>
SQL>
SQL> CREATE SEQUENCE temp_seq
2 START WITH 1
3 INCREMENT BY 1;
Sequence created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE TempInsert AS
2 BEGIN
3 INSERT INTO MyTable (num_col)
4 VALUES (temp_seq.nextval);
5 COMMIT;
6 END TempInsert;
7 /
Procedure created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE TempInsert
2 (p_NextDate IN OUT DATE) AS
3 v_CurrVal NUMBER;
4 BEGIN
5 INSERT INTO MyTable (num_col)
6 VALUES (temp_seq.nextval);
7 SELECT temp_seq.currval
8 INTO v_CurrVal
9 FROM dual;
10 IF v_CurrVal > 15 THEN
11 p_NextDate := NULL;
12 END IF;
13 COMMIT;
14 END TempInsert;
15 /
Procedure created.
SQL>
SQL> VARIABLE v_JobNum NUMBER
SQL> BEGIN
2 DBMS_JOB.SUBMIT(:v_JobNum, "TempInsert(next_date);", sysdate,
3 "sysdate + (1/(24*60*60))");
4 END;
5 /
PL/SQL procedure successfully completed.
SQL> PRINT v_JobNum
V_JOBNUM
----------
42
SQL>
SQL>
SQL> drop SEQUENCE temp_seq;
Sequence dropped.
SQL> drop table MyTable;
Table dropped.
SQL>

</source>

==Use sequence in insert command==



<source lang="sql">

SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL> -- A NOCYCLE sequence
SQL>
SQL>
SQL> CREATE SEQUENCE MySeq
2 INCREMENT BY 1
3 START WITH 1
4 MAXVALUE 99999
5 NOCACHE
6 NOCYCLE;
Sequence created.
SQL>
SQL>
SQL> insert into emp (empno) values(mySeq.nextVal);
1 row created.
SQL>
SQL> select * from emp;
Hit a key to continue
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
1
15 rows selected.
SQL> drop sequence mySeq;
Sequence dropped.
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>
SQL>

</source>

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

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

в 13:45, 26 мая 2010

Admin: 1 версия