尚硅谷_宋红康_11_其它数据库对象.ppt

Confirming Sequences Once you have created your sequence, it is documented in the data dictionary. Since a sequence is a database object, you can identify it in the USER_OBJECTS data dictionary table. You can also confirm the settings of the sequence by selecting from the USER_SEQUENCES data dictionary view. Instructor Note Demo: 12_dd.sql Purpose: To illustrate the USER_SEQUENCES data dictionary view and its contents. Using a Sequence After you create your sequence, it generates sequential numbers for use in your tables. Reference the sequence values by using the NEXTVAL and CURRVAL pseudocolumns. NEXTVAL and CURRVAL Pseudocolumns The NEXTVAL pseudocolumn is used to extract successive sequence numbers from a specified sequence. You must qualify NEXTVAL with the sequence name. When you reference sequence.NEXTVAL, a new sequence number is generated and the current sequence number is placed in CURRVAL. The CURRVAL pseudocolumn is used to refer to a sequence number that the current user has just generated. NEXTVAL must be used to generate a sequence number in the current user’s session before CURRVAL can be referenced. You must qualify CURRVAL with the sequence name. When sequence.CURRVAL is referenced, the last value returned to that user’s process is displayed. Using a Sequence The example on the slide inserts a new department in the DEPARTMENTS table. It uses the DEPT_DEPTID_SEQ sequence for generating a new department number as follows: You can view the current value of the sequence: SELECT dept_deptid_seq.CURRVAL FROM dual; Suppose now you want to hire employees to staff the new department. The INSERT statement to be executed for all new employees can include the following code: INSERT INTO employees (employee_id, department_id, ...) VALUES (employees_seq.NEXTVAL, dept_deptid_seq .CURRVAL, ...); Note: The preceding example assumes that a sequence called EMPLOYEE_SEQ has already been created for generating new employee numbers. Cachi