Oracle PL/SQL/Sequence/Sequence Value

Материал из SQL эксперт
Перейти к: навигация, поиск

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>