After the operation will not on the sequence-CodePudding

Under table, create a key field, create a content increasing sequence as a key field,

But every time after the operation of the key content will change, is there any way can make the content of the original key is changeless, insert a new record at the same time the key automatically add new content again

CodePudding user response:

The building Lord, for example;

CodePudding user response:

Select
(sequence) seq. Nextval as key,
Tc ode
The from T (table name);

In this way, every time after the run once in the PL/SQL, key content will change, but the code does not change,

Need every time after the operation of the key content don't change, can you tell me how should deal with?

CodePudding user response:

Don't use sequence,

Select rownum key, code from t

Or

Select row_number () over the key order by code, code from t

CodePudding user response:

reference wmxcn2000 reply: 3/f

don't use sequence,

Select rownum key, code from t

Or

Select row_number () over the key order by code, the code from t

But, if you use a number of lines as a key value, so when change the order of the code, code corresponding to the key value will be changed

CodePudding user response:

Z771499387

reference 4 floor response:

but, if use line number as the key values, so when change the order of the code, code corresponding to the key value will change

Is will change, if you don't want to change, can only add a column, the key to the table,

CodePudding user response:

reference 5 floor wmxcn2000 reply:

Quote: refer to 4th floor Z771499387 response:

But, if you use a number of lines as a key value, so when change the order of the code, code corresponding to the key value will change

Is will change, if you don't want to change, can only add a column, the key to the table,

So it need to modify the table structure? How to operate

CodePudding user response:

Your table's primary key?

CodePudding user response:

 
 - use triggers
SQL> 
SQL> Create table test (key int, code varchar (10)); 
The Table created 
SQL> The create sequence seq_test; 
The Sequence created 
SQL> Create the trigger tri_test_id 
2 before the insert on the test 
3 for each row 
4 the begin 
5: the new key:=seq_test nextval; 
6 the end; 
7/
The Trigger created 
SQL> The begin 
2 insert into test (code) values (' zhao); 
3 the insert into test (code) values (" qian "); 
Insert into test (code) values (' sun '); 
5 the insert into test (code) values (" li "); 
6 the end; 
7/
PL/SQL procedure successfully completed 
SQL> Select * from the test; 
The KEY CODE 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
Zhao 1 
2 qian 
3 the sun 
4 li 
SQL> Drop table test purge; 
Table dropped 
SQL> The drop sequence seq_test; 
The Sequence dropped 

SQL>

CodePudding user response:

Currval returns the current sequence value
Nextval return value after the sequence of values

CodePudding user response:

Is there any way you can make the content of the original key is changeless, insert a new record at the same time the key is automatically add new content
If the KEY storage, the query of the original KEY must have the same content,