Data cleansing Note: Generation of the primary key class ID field, note Field

Data cleansing Note: Generation of the primary key class ID field, note Field

Original Works are from the blog of "Deep Blue blog". You are welcome to repost them. You must specify the source when you repost them. Otherwise, you have the right to pursue legal liability for copyright.

Deep Blue blog: http://blog.csdn.net/huangyanlong/article/details/46340623

Background]

After data is extracted from the source end and inserted to the target end, a primary key ID field needs to be created.

 

[Solution]

Method 1: Use the sys_guid () function to generate a 32-bit Note Number

For example, in the emp table, create a field and set the default value of the field to automatically generate a 32-bit random number with the sys_guid () function.

Alter table EMP add ID varchar2 (32) default sys_guid ();

 

Method 2: Use a trigger to insert an auto-incrementing Sequence

For example, before inserting a new piece of data into the emp1 table, insert an ID value. Specifically, seq_idadd is a sequence that has been created in advance. You can customize the starting value and difference value of the sequence.

Create or replace trigger tr_IDADD

Before insert on emp1

For each row

Begin

Select seq_idadd.nextval into: new. id from dual;

End;

 

Method 3: Splicing

For example, an ID Primary Key is formed by splicing the source business fields. For example:

Source ID|To_char (sysdate, 'yyyymmdd ')|Lpad (NEXTNUM. Nextval, 10, 0)

Assume that the source ID is 12 bits. In this way, a 12 + 8 + 10 = 30-bit primary key number is intercepted.

 

[Experiment]

Use sys_guid to obtain the ID value, as shown below:

With the trigger, ID increments by 1 for each new piece of data, as shown below:

 

Obtain the primary key ID by splicing, as shown below:

Small knowledge, easy to remember.

 

**************************************** * ** Blue growth series_ 20150523*************************************

Original works, from the blog of "Deep Blue". You are welcome to reprint them. Please indicate the source (Http://blog.csdn.net/huangyanlong).

 

Blue growth note-chasing DBA (1): traveling on the road to Shandong

Blue growth notes-Chase DBA (2): Install! Install! Long-lost memories have aroused my new awareness of DBAs.

Blue growth note-chasing DBA (3): importing and exporting data on antiques becomes a problem

Blue growth note-chasing DBA (4): recalling the sorrow of teenagers, and exploring oracle Installation (10g and 11g in Linux)

Blue growth note-chasing DBA (5): Not talking about technology or business, annoying Application Systems

Blue growth note-chasing DBA (6): doing things and being human: Small technology, great human

Blue growth note-Chase DBA (7): Basic commands, foundation stone

Blue growth notes-chasing DBA (8): repicking SP reports and recalling oracle's STATSPACK Experiment

Blue growth note-chasing DBA (9): Chasing DBA, new planning, new departure

Blue growth note-chasing DBA (10): Flying knife defense, familiarity rather than expertise: Playing with middleware Websphere

Blue growth note-chasing DBA (11): It's easy to go home and wake up.

Blue growth notes-Chase DBA (12): seven days and seven gains of SQL

Blue growth note-chasing DBA (13): Coordinating hardware manufacturers, six stories: what you see as "servers, storage, switches ......"

Blue growth note-chasing DBA (14): An unforgettable "Cloud" end, started hadoop deployment

Blue growth note-chasing DBA (15): Who thinks FTP is "simple" and thinks it is a trigger

Blue growth note-chasing DBA (16): DBA also drank alcohol and was rejected

**************************************** **************************************** **********************************

 

 

**************************************** * *** Football and oracle Series_ 20150528***********************************

Original works, from the blog of "Deep Blue". You are welcome to reprint them. Please indicate the source (Http://blog.csdn.net/huangyanlong).

 

Football and oracle series (1): 32-way zhoudianbing, overall view of group A Brazil smon process of oracle32 process Alliance

Football and oracle series (2)

Football and oracle series (3): oracle process rankings, the World Cup round is about to fight!

Football and oracle series (4): from Brazil to Germany, think of the different RAC topology comparison! 

Football and oracle series (5): The directX library missing in the voda14 game is similar to the oracle rpm package!

Football and oracle series (6): Asian Cup with database creation-come on, Chinese Team

**************************************** **************************************** **********************************