PLSQL Language Referenc-PL/SQL静态SQL-静态SQL的描述-伪列

DROP TABLE employees_temp;

CREATE TABLE employees_temp AS

    SELECT employee_id, first_name, last_name

    FROM employees;

DROP TABLE employees_temp2;

CREATE TABLE employees_temp2 AS

    SELECT employee_id, first_name, last_name

    FROM employees;

DECLARE

    seq_value NUMBER;

BEGIN

    -- 产生初始序列值

    seq_value := employees_seq.NEXTVAL;

    DBMS_OUTPUT.PUT_LINE ('初始序列值: ' || TO_CHAR(seq_value)); 

    INSERT INTO employees_temp (employee_id, first_name, last_name)

    VALUES (employees_seq.NEXTVAL, 'Lynette', 'Smith');

    INSERT INTO employees_temp2 VALUES (employees_seq.CURRVAL, 'Morgan', 'Smith');

     --因为NEXTVAL可能被不同的用户和应用程序所引用,并且一些NEXTVAL的值

     --可能没有被存储在数据库中，所以值会产生断续

     seq_value := employees_seq.CURRVAL;

     DELETE FROM employees_temp2 WHERE employee_id = seq_value; 

     UPDATE employees_temp SET employee_id = employees_seq.NEXTVAL

     WHERE first_name = 'Lynette'

     AND last_name = 'Smith';   

    seq_value := employees_seq.CURRVAL;

    DBMS_OUTPUT.PUT_LINE (序列的最后值: ' || TO_CHAR(seq_value));

END;