Oracle uses a cursor to add a primary key statement to all user tables. oracle cursor

Oracle uses a cursor to add a primary key statement to all user tables. oracle cursor

Application Scenario: adding an auto-increment primary key to a data table can speed up data table access, and is the fastest in integer indexing. This program is suitable for running when the Oracle database is imported and the primary key does not exist.

Code Description: All table primary key field names are set to ID. If the ID field already exists, it determines whether it is an integer. If not, rename the field to [Table name ID] and add an ID, if this parameter does not exist, add a primary key with an ID added directly.

Operation instructions: Open PQSQL to connect to the database and run the following detailed script code. The script is risky (the original index and primary key constraints will be deleted ), do not execute it directly on the officially running database.

-- Oracle uses a cursor to add a primary key statement to 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;
-- Query the trigger user select distinct (table_owner) from user_triggers;

Declare

Addstring NVARCHAR2 (2000): = ''; -- defines adding a field variable
Renamestring NVARCHAR2 (2000): = ''; -- defines the rename field variable.
Tablestring NVARCHAR2 (2000): = ''; -- Define sequence Variables
Keyidname NVARCHAR2 (255): = 'id'; -- defines the primary key field name variable
Tableidname NVARCHAR2 (255): = ''; -- define a new field name variable
Trigerstring NVARCHAR2 (2000): = ''; -- Define the create trigger string variable
Trgname NVARCHAR2 (255): = ''; -- Define the trigger name variable
Seqstring NVARCHAR2 (2000): = ''; -- defines the creation sequence string variable
Seqname NVARCHAR2 (255): = ''; -- defines the sequence name variable
Pkname NVARCHAR2 (255): = ''; -- defines the primary key index name variable

Constring NVARCHAR2 (2000): = ''; -- Define the index variable
Notnullstring NVARCHAR2 (2000): = ''; -- defines a variable whose primary key is not empty.

Cursor mycursor is select * from user_tables where TABLESPACE_NAME = 'szgabl' order by TABLE_NAME; -- Define a cursor to get the names of all user data tables
Myrecord mycursor % rowtype; -- defines the cursor record type
CounterName int: = 0; -- defines whether the corresponding column name variable exists.
CounterData int: = 0; -- defines whether the corresponding data type exists.

Begin

Dbms_output.put_line ('maid int: = 0; In in ');

Open mycursor; -- open the cursor
If mycursor % isopen then -- determines whether the open is successful
Loop -- Obtain Record Sets cyclically
Fetch mycursor into myrecord; -- get records in the cursor

If mycursor % found then -- the cursor's found attribute determines whether a record exists
Begin
-- Get a valid data table name
Select replace (myrecord. TABLE_NAME, 'tb _ ', '') into tablestring from dual;
Select 'seq _ '| tablestring into seqname from dual;
Select 'trg _ '| tablestring into trgname from dual;
Select 'pk _ '| tablestring into pkname from dual;
Select tablestring | UPPER (keyidname) into tableidname from dual;

-- Determines whether the current data table contains columns whose field name is ID.
Select count (*) INTO CounterName FROM dual where exists (SELECT * FROM user_tab_cols where lower (COLUMN_NAME) = LOWER (keyidname) and TABLE_NAME = myrecord. TABLE_NAME );
If CounterName = 0 then
Begin
Dbms_output.put_line ('-- current data table' | myrecord. TABLE_NAME | 'the column with the field name ID does not exist ');
-- Add a 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 | 'crement 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 primary key constraints
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;
-- The primary key to be updated 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 = ''); 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
-- Determines whether the current data table contains the field name 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 (keyidname) 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 | 'columns whose field name is ID but 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 a 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 | 'crement 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 primary key constraints
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;
-- The primary key to be updated 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 = ''); 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 | 'columns whose field name is ID and 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 | 'crement 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 primary key constraints
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;
-- The primary key to be updated 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 = ''); 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 ('-- the cursor is not open ');
End if;

Close mycursor;

Dbms_output.put_line ('end ;');
End;