Use Sequence in a procedure : Sequence Value « Sequence « Oracle PL / SQL

1. Oracle PL / SQL
2. Sequence
3. Sequence Value

Use Sequence in a procedure

  
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>

   
  

Related examples in the same category