伪列和普通列一样,但不保存在表中。静态SQL包含伪列:CURRVAL、NEXTVAL、
ROWID、CURRVAL and NEXTVAL
通过创建序列可以引用CURRVAL and NEXTVAL,CURRVAL代表当前值,NEXTVAL递
增+1。使用方法是sequence_name. CURRVAL
CREATE OR REPLACE PORCEDURE P_6_2 AS
seq_value NUMBER;
BEGIN
-- Generate initial sequence number
seq_value := employees_seq.NEXTVAL;
-- Print initial sequence number:
DBMS_OUTPUT.PUT_LINE ('Initial sequence value: ' || TO_CHAR(seq_value));
-- Use NEXTVAL to create unique number when inserting data:
INSERT INTO employees_temp (employee_id, first_name, last_name)
VALUES (employees_seq.NEXTVAL, 'Lynette', 'Smith');
-- Use CURRVAL to store same value somewhere else:
INSERT INTO employees_temp2
VALUES (employees_seq.CURRVAL,'Morgan', 'Smith');
-- Use CURRVAL to specify record to delete:
seq_value := employees_seq.CURRVAL;
DELETE FROM employees_temp2
WHERE employee_id = seq_value;
-- Update employee_id with NEXTVAL for specified record:
UPDATE employees_temp
SET employee_id = employees_seq.NEXTVAL
WHERE first_name = 'Lynette' AND last_name = 'Smith';
GBase 8s PL/SQL手册
南大通用数据技术股份有限公司
- 95 -
-- Display final value of CURRVAL:
seq_value := employees_seq.CURRVAL;
DBMS_OUTPUT.PUT_LINE ('Ending sequence value: ' || TO_CHAR(seq_value));
END;