Pl/sql sequence

A sequence is a scenario object that Oracle 9i provides to automatically generate data according to set rules. Some fields require this feature in the structure of some data tables. For example, for a student data table of the key field, users can expect to enter data, automatically in the previous record of the Number field automatically add 1. Because the 16 basic data types provided by Oracle 9i do not have such functionality, they can be implemented through a sequence scheme object.

The creation of a sequence

The following describes how to create a sequence in Enterprise Manager.
(1) in the "Fu 瘛 sinks yoracle.mynet"/"scheme"/"sequence" option, click the right mouse button, the shortcut menu appears, select the "Create" option, as shown in Figure 9.48.

(2) The General Information tab for the create sequence as shown in Figure 9.49 appears.
In the Name text box, enter the name "Tempsequence" for the sequence you want to define.
In the Scheme Drop-down list box, select the user name "SCOTT" to which the sequence belongs.
There are two options for the sequence type argument. Selecting the Ascending radio button will create a sequence that increments from the initial value to the maximum value, which is the default setting when the sequence is created; If you select the Descending radio button, the sequence that will be decremented from the initial value to the minimum value is created.
The parameters that can be set on the value are as follows.
Set the minimum value allowed for the sequence in the Minimum text box. The field is initially empty when the sequence is created. If the field is blank when you click the Create button, the default value of 1 is used for ascending sequences, and the default value of 1026 is used for descending sequences.
Set the maximum value allowed for the sequence in the Maximum text box. The field is initially empty when the sequence is created. If the field is blank after you click the Create button, the default value of 1027 is used for ascending sequences, and the default value of 1 is used for descending sequences.
Set the interval numeric value (ascending sequence) or descending sequence descending (descending sequence) of the increment sequence increment in the interval text box. The field is initially empty when the sequence is created, and if the field is blank after you click the Create button, the default value of 1 is used and the field can only be a positive integer.
Sets the starting value of the sequence in the initial value text box. If the field is blank after you click the Create button, the default minimum value for the sequence is used for the ascending sequence, and the default maximum value for the sequence is used for the descending sequence.
The parameters you can set for options are as follows.
If you select the Loop value check box, specifies that the sequence should continue to generate a value after the sequence minimum or maximum value is reached. For ascending sequences, the minimum value is generated when the maximum value is reached. For descending sequences, the maximum value is generated when the minimum value is reached. If this check box is not selected, the sequence stops generating any values after the minimum or maximum value is reached. By default, the state is not selected.
If you select the Sort value check box, the specified sequence number is generated in the order of request and is not selected by default.
Set the number of values that are preconfigured and stored by the database in the cache. If you select the default Value radio button, the default value is set to 20. This option is selected by default, and if the no Cache radio button is selected, specifies the unassigned sequence value; If you select the Size radio button, you enter an acceptable value in the text box with a minimum value of 2, and for the loop sequence, The value must be less than the number of values in the loop. If the maximum number of values that a sequence can generate is less than the cache size, the cache size is automatically changed to the upper limit.
Click the button when the settings are complete.
(3) After the sequence is successfully created, the interface shown in Figure 9.50 appears. Click the OK button.

(3) The reader can also perform the following SQL program creation sequence in "Sqlplus Worksheet".
―――――――――――――――――――――――――――――――――――――
CREATE SEQUENCE "SCOTT". Tempsequence "
INCREMENT by 1 START with 1
MAXVALUE 1.0E28 MinValue 1
Nocycle CACHE Noorder
―――――――――――――――――――――――――――――――――――――
"Matching program Location": 9th Chapter \ Createsequence.sql.

Use of sequences

The following describes how to use a sequence when inserting data into a datasheet.
(1) First set up a data table for the instance "SCOTT." Sequence_table ", for simplicity, the datasheet contains only a data column" NO "of type" number ".
In the General Information tab of the Create table as shown in Figure 9.51, make the following settings.
Enter "Sequence_table" in the Name text box.
In the Scheme Drop-down list box, select SCOTT.
In the Table Space Drop-down list box, select USERS.
Enter NO in the name cell and select number in the Data type Drop-down list box cell.
Click the button when the settings are complete.

(2) The reader can also execute the following SQL code creation data table in "Sqlplus Worksheet" SCOTT. Sequence_table ".
―――――――――――――――――――――――――――――――――――――
CREATE TABLE "SCOTT". " Sequence_table "(" NO "number () not NULL)
Tablespace "USERS"
―――――――――――――――――――――――――――――――――――――
"Matching program Location": 9th Chapter \ Createsequencetable.sql.
(3) when inserting a new record, use the "tempsequence" sequence you just created to automatically generate the value of the "NO" data column. Execute the following SQL code in "Sqlplus Worksheet", as shown in Figure 9.52.
―――――――――――――――――――――――――――――――――――――
INSERT into SCOTT. Sequence_table (NO)
VALUES (SCOTT. Tempsequence. Nextval);
―――――――――――――――――――――――――――――――――――――
"Matching program Location": 9th Chapter \ Insertsequencetable.sql.
"SCOTT. Tempsequence. Nextval table assigns the next unique, available serial number.
Execute "SCOTT. Tempsequence. Nextval "can be used after" SCOTT. Tempsequence. Currval "to identify the last stored sequence value.
(4) Query data table can be executed in "Sqlplus Worksheet" SCOTT. Sequence_table The statement of the data. The execution result, shown in Figure 9.53, shows the sequence "SCOTT." SEQUENCE "The resulting value has been successfully entered into the data table.
―――――――――――――――――――――――――――――――――――――
SELECT * from Scott.sequence_table;
―――――――――――――――――――――――――――――――――――――
"Matching program Location": 9th Chapter \ Selectsequencetable.sql.


Deletion of sequences

The following describes how to delete a sequence in Enterprise Manager.
(1) Click the right mouse button on the created sequence "SEQUENCE1" and select the "Remove" option in the shortcut menu that appears, as shown in Figure 9.54.

(2) The Delete sequence confirmation interface appears as shown in Figure 9.55, click the button.