Oracle uses cursors to add primary key statements for all user tables

Application: Add a new primary key to the data table to speed up the data table access speed, and is the fastest index shaping. This program is suitable for running when you import an Oracle database without a primary key being removed.

Code Description: All table primary key field names are set to ID, if the ID field already exists, then determine whether it is shaping, if not rename the field to [table name ID], and then add the ID, if not present, adding the primary key of the self-increment ID directly

Operation Instructions: Open PQSQL Connection database directly execute the following detailed script code to run, the script is risky (will delete the original index and primary KEY constraints), please do not easily on the official run of the database directly executed

--oracle using cursors to add primary key statements for all user tables
--The reference statement is as follows:
--Query All PRIMARY KEY constraints select * from User_constraints
--Query all sequences select * from User_sequences;
--Query all triggers select * from User_triggers;
--User select DISTINCT (table_owner) from User_triggers of the query trigger;

Declare

AddString NVARCHAR2 (2000): = "; --Define the Add field variable
Renamestring NVARCHAR2 (2000): = "; --Define Rename field variables
Tablestring NVARCHAR2 (2000): = "; --Defining sequence variables
Keyidname NVARCHAR2 (255): = ' ID '; --Define the primary key field name variable
Tableidname NVARCHAR2 (255): = "; --Define a new field name variable
Trigerstring NVARCHAR2 (2000): = "; --Define CREATE trigger string variable
Trgname NVARCHAR2 (255): = "; --Define the trigger name variable
Seqstring NVARCHAR2 (2000): = "; --Define create sequence string variable
Seqname NVARCHAR2 (255): = "; --Define the sequence name variable
Pkname NVARCHAR2 (255): = "; --Define the primary key index name variable

Constring NVARCHAR2 (2000): = "; --Define Index variables
Notnullstring NVARCHAR2 (2000): = "; --Define the primary key is not an empty variable

Cursor MyCursor is a select * from User_tables where tablespace_name= ' SZGABL ' ORDER by table_name; --Define the cursor to get all user data table names
Myrecord Mycursor%rowtype; --Define Cursor record type
CounterName int: = 0; --Define if there is a corresponding column name variable
counterdata int: = 0; --Define whether there is a corresponding data type

Begin

Dbms_output.put_line (' Declare counter int: =0;begin ');

Open mycursor; --Open cursor
If Mycursor%isopen then--Judging open success
Loop-Loop Get recordset
Fetch mycursor into Myrecord; --Get the record in the cursor

If Mycursor%found then--the found property of the cursor determines if there is a record
Begin
--Get a valid data table name
Select Replace (Myrecord. TABLE_NAME, ' tb_ ', ') into the tablestring from dual;
Select ' Seq_ ' | | Tablestring to seqname from dual;
Select ' Trg_ ' | | Tablestring to trgname from dual;
Select ' Pk_ ' | | Tablestring to pkname from dual;
Select Tablestring| | UPPER (Keyidname) to tableidname from dual;

--Determine if the current data table contains a column with the field name ID
Select COUNT (*) into CounterName from dual where EXISTS (SELECT * from User_tab_cols where LOWER (column_name) =lower (Keyidna Me) and Table_name=myrecord. TABLE_NAME);
If Countername=0 Then
Begin
Dbms_output.put_line ('--current data table ' | | Myrecord. table_name| | ' There is no column with field name ID);
--Add primary key field
Addstring:= ' execute immediate ' ALTER TABLE ' | | Myrecord. table_name| | ' Add ' | | keyidname| | ' Number '; ';
Dbms_output.put_line (addstring);
--execute immediate addstring;

--Create a sequence
Seqstring:= ' SELECT COUNT (*) into counter from dual where exists (SELECT * from user_sequences where sequence_name= ' | | seqname| | "); If Counter>0 then execute immediate ' drop sequence ' | | seqname| | "; End If; Execute immediate ' Create sequence seq_ ' | |  Tablestring| | ' INCREMENT by 1 START with 1 nomaxvalue nocycle NOCACHE '; ';
Dbms_output.put_line (seqstring);
--execute immediate seqstring;
--Create a trigger
Trigerstring:= ' SELECT COUNT (*) into counter from dual where exists (SELECT * from user_triggers where trigger_name= ' | | trgname| | "); If Counter>0 then execute immediate ' drop trigger ' | | trgname| | "; End If; Execute immediate ' create Trigger Trg_ ' | | Tablestring| | ' Before INSERT on ' | | Myrecord. table_name| | ' For each ROW when (new. ' | | keyidname| | ' is null) begin SELECT ' | | seqname| | '. Nextval into:new. ' | | keyidname| | ' from dual; End '; ';
Dbms_output.put_line (trigerstring);
--execute immediate trigerstring;
--Add a PRIMARY KEY constraint
Constring:= ' SELECT COUNT (*) into counter from dual where exists (SELECT * from user_constraints where constraint_name= ' | |pkname| | "); If Counter>0 then execute immediate ' drop constraint ' | | pkname| | "; End If; Execute immediate ' ALTER TABLE ' | | Myrecord. table_name| | ' Add constraint ' | | pkname| | ' primary key (' | | keyidname| | ') '; ';
Dbms_output.put_line (constring);
--execute immediate constring;
--Update the primary key is not empty
Notnullstring:= ' SELECT COUNT (*) into counter from dual where exists (SELECT * from User_tab_cols where table_name= ' | | Myrecord.  table_name| | " and Column_name= ' ' | | keyidname| | " and nullable= ' Y '); if Counter>0 then execute immediate ' ALTER TABLE ' | | Myrecord. table_name| | ' Modify ' | | keyidname| | ' NOT NULL '; End If; ';
Dbms_output.put_line (notnullstring);
--execute immediate notnullstring;
End
Else
Begin
--Determine if the current data table contains a field named ID and the data type is number
Select COUNT (*) into counterdata from dual where EXISTS (SELECT * from User_tab_cols where LOWER (column_name) =lower (Keyidna Me) and data_type= ' number ' and Table_name=myrecord. TABLE_NAME);
If Counterdata=0 Then
Begin
Dbms_output.put_line ('--current data table ' | | Myrecord. table_name| | ' There is a column with the field name ID but the data type is not number ');
--Rename the field first and then add the primary key field
Renamestring:= ' execute immediate ' ALTER TABLE ' | | Myrecord. table_name| | ' rename column ' | | keyidname| | ' to ' | | tableidname| | ";";
Dbms_output.put_line (renamestring);
--execute immediate renamestring;
--Add primary key field
Addstring:= ' execute immediate ' ALTER TABLE ' | | Myrecord. table_name| | ' Add ' | | keyidname| | ' Number '; ';
Dbms_output.put_line (addstring);

--execute immediate addstring;
--Create a sequence
Seqstring:= ' SELECT COUNT (*) into counter from dual where exists (SELECT * from user_sequences where sequence_name= ' | | seqname| | "); If Counter>0 then execute immediate ' drop sequence ' | | seqname| | "; End If; Execute immediate ' Create sequence seq_ ' | |  Tablestring| | ' INCREMENT by 1 START with 1 nomaxvalue nocycle NOCACHE '; ';
Dbms_output.put_line (seqstring);
--execute immediate seqstring;
--Create a trigger
Trigerstring:= ' SELECT COUNT (*) into counter from dual where exists (SELECT * from user_triggers where trigger_name= ' | | trgname| | "); If Counter>0 then execute immediate ' drop trigger ' | | trgname| | "; End If; Execute immediate ' create Trigger Trg_ ' | | Tablestring| | ' Before INSERT on ' | | Myrecord. table_name| | ' For each ROW when (new. ' | | keyidname| | ' is null) begin SELECT ' | | seqname| | '. Nextval into:new. ' | | keyidname| | ' from dual; End '; ';
Dbms_output.put_line (trigerstring);
--execute immediate trigerstring;
--Add a PRIMARY KEY constraint
Constring:= ' SELECT COUNT (*) into counter from dual where exists (SELECT * from user_constraints where constraint_name= ' | |pkname| | "); If Counter>0 then execute immediate ' drop constraint ' | | pkname| | "; End If; Execute immediate ' ALTER TABLE ' | | Myrecord. table_name| | ' Add constraint ' | | pkname| | ' primary key (' | | keyidname| | ') '; ';
Dbms_output.put_line (constring);
--execute immediate constring;
--Update the primary key is not empty
Notnullstring:= ' SELECT COUNT (*) into counter from dual where exists (SELECT * from User_tab_cols where table_name= ' | | Myrecord.  table_name| | " and Column_name= ' ' | | keyidname| | " and nullable= ' Y '); if Counter>0 then execute immediate ' ALTER TABLE ' | | Myrecord.  table_name| | ' Modify ' | | keyidname| | ' NOT NULL '; End If; ';
Dbms_output.put_line (notnullstring);
--execute immediate notnullstring;
End
Else
Begin
Dbms_output.put_line ('--current data table ' | | Myrecord. table_name| | ' There is a column with the field name ID and the data type is number ');
--Create a sequence
Seqstring:= ' SELECT COUNT (*) into counter from dual where exists (SELECT * from user_sequences where sequence_name= ' | | seqname| | "); If Counter>0 then execute immediate ' drop sequence ' | | seqname| | "; End If; Execute immediate ' Create sequence seq_ ' | |  Tablestring| | ' INCREMENT by 1 START with 1 nomaxvalue nocycle NOCACHE '; ';
Dbms_output.put_line (seqstring);
--execute immediate seqstring;
--Create a trigger
Trigerstring:= ' SELECT COUNT (*) into counter from dual where exists (SELECT * from user_triggers where trigger_name= ' | | trgname| | "); If Counter>0 then execute immediate ' drop trigger ' | | trgname| | "; End If; Execute immediate ' create Trigger Trg_ ' | | Tablestring| | ' Before INSERT on ' | | Myrecord. table_name| | ' For each ROW when (new. ' | | keyidname| | ' is null) begin SELECT ' | | seqname| | '. Nextval into:new. ' | | keyidname| | ' from dual; End '; ';
Dbms_output.put_line (trigerstring);
--execute immediate trigerstring;
--Add a PRIMARY KEY constraint
Constring:= ' SELECT COUNT (*) into counter from dual where exists (SELECT * from user_constraints where constraint_name= ' | |pkname| | "); If Counter>0 then execute immediate ' drop constraint ' | | pkname| | "; End If; Execute immediate ' ALTER TABLE ' | | Myrecord. table_name| | ' Add constraint ' | | pkname| | ' primary key (' | | keyidname| | ') '; ';
Dbms_output.put_line (constring);
--execute immediate constring;
--Update the primary key is not empty
Notnullstring:= ' SELECT COUNT (*) into counter from dual where exists (SELECT * from User_tab_cols where table_name= ' | | Myrecord.  table_name| | " and Column_name= ' ' | | keyidname| | " and nullable= ' Y '); if Counter>0 then execute immediate ' ALTER TABLE ' | | Myrecord.  table_name| | ' Modify ' | | keyidname| | ' NOT NULL '; End If; ';
Dbms_output.put_line (notnullstring);
--execute immediate notnullstring;
End
End If;
End
End If;
Dbms_output.put_line (");
End

Else
Exit
End If;

End Loop;
Else
Dbms_output.put_line ('--cursor not open ');
End If;

Close MyCursor;

Dbms_output.put_line (' end; ');
End

Oracle uses cursors to add primary key statements for all user tables