Oracle PL/SQL/Sequence/Sequence Value
Содержание
- 1 Automatically including unique sequence numbers during an INSERT.
- 2 Get Sequence next value
- 3 If new value is null use the value from a sequence
- 4 Random value based on sequence
- 5 Sequence current value and next value
- 6 Sequence max value: 999999999999999999999999999
- 7 Set sequence max value
- 8 Use Sequence in a procedure
- 9 Use sequence in insert command
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>