可重复执行SQL
来源:互联网 发布:php 批量不重复卡密 编辑:程序博客网 时间:2024/04/30 01:23
--删除重复数据-------------删除重复数据--------------------------declare pvar_i_cunt integer; scol1 varchar2(20); scol2 varchar2(10); sSql varchar2(1000);begin /* 删除重复数据: 用一个临时表保存重复数据,但只记录重复数据中的一条(rowid最大的一条),然后将这表来关联实际表, 把重复数据中rowid不是最大其它数据删除,这样就剩下一条了。*/ --创建一个临时表来保存重复数据(这个表里面:只记录其中一条--rowid最大的一条) select count(1) into pvar_i_cunt from user_tables s where s.TABLE_NAME='表名称'; if pvar_i_cunt>0 then execute immediate 'drop table 表名称'; end if ; execute immediate ' create table 表名称 (row_id ROWID, -----------下面字段是主键字段---------------- 字段1 VARCHAR2(20) not null, 字段2 VARCHAR2(20) default '' '' not null )' ; for cur in (select a.cn, a.字段1, 字段2 from (select count(*) cn, s.字段1, s.字段2 from 表名称 s group by s.字段1, s.字段2) a where a.cn > 1) loop scol1 := cur.字段1; scol2 := cur.字段2; --取出重复数据的其中一条 (max(rowid)那条) sSql :=' insert into 表名称 ' ||' (row_id, 字段1, 字段2) ' ||' select max(a.rowid), a.字段1, a.字段2 ' ||' from STOCKCOMPS a ' ||' where a.字段1 = '''||scol1||'''' ||' and a.字段2 = '''||scol2||'''' ||' group by a.字段1, a.字段2 '; execute immediate sSql ; end loop; --删除重复数据,用rowid来关联(重复数据中rowid不等于临时表的row_id的数据删除,这时只剩下一条) sSql := 'delete from 表名称 s ' ||' where Exists (select ''X'' from 表名称 b ' ||' where b.字段1 = s.字段1 ' --主键字段必须要 ||' and b.字段2 = s.字段2 ' --主键字段必须要 ||' and b.row_id <> s.rowid) '; --rowid来判断删除 execute immediate sSql ; commit; --操作完成后删除临时表 execute immediate 'drop table 表名称';end;/-- user_constraints 主键约束declare tm_i integer; pvar_PK_constraint varchar2(100);begin --查出主键,并删除,重新建立 select count(*) into tm_i from user_constraints where table_name='表名' and constraint_type='P' ; if tm_i>0 then select constraint_name into pvar_PK_constraint from user_constraints where table_name='表名' and constraint_type='P' ; --删除主键 execute immediate 'alter table 表名 drop constraint '|| pvar_PK_constraint ||' cascade'; end if ; --再检查是否有重名的索引 select count(*) into tm_i from user_indexes where index_name='主键名称'; if tm_i>0 then execute immediate 'DROP INDEX 主键名称'; end if; --创建主键 execute immediate 'alter table 表名 add constraint 主键名称 primary key (字段1,字段2,....)'; end;-- user_tab_cols 表增加字段declare tm_i integer; Begin --如果字段关联了主键则先删除主键 select count(*) into tm_i from user_constraints <span style="white-space:pre"></span> where lower(table_name)=Lower('table_name') <span style="white-space:pre"></span> and lower(constraint_name)=lower('PK_Constraints_name'); if tm_i>0 then execute immediate 'alter table table_name drop constraint PK_Constraints_name cascade'; end if; --删除索引 select count(*) into tm_i from user_indexes where lower(table_name)=Lower('table_name') and lower(index_name)=lower('PK_Constraints_name'); if tm_i>0 then execute immediate 'DROP INDEX PK_Constraints_name'; end if; --删除主键END---------------------------------------------- select count(*) into tm_i from user_tab_cols where lower(table_name)=lower('table_name') and lower(column_name)=lower('Column_name'); if tm_i=0 then execute immediate 'alter table table_name add Column_name varchar2(1) default ''0'' not null'; end if; --创建主键 execute immediate 'alter table table_name add constraint PK_Constraints_name primary key (Column_name1, Column_name2)';end;--modify user_tab_cols 表修改字段declare tm_i integer; begin select count(*) into tm_i from user_tab_cols where lower(table_name)=lower('表名') and lower(column_name)=lower('字段1') And Lower(NULLABLE) = Lower('N'); if tm_i > 0 then execute immediate 'alter table 表名 Modify 字段1 varchar2(1) default ''0'' not null'; else execute immediate 'alter table 表名 Add 字段1 varchar2(1) default ''0'' not null'; end if;end;-- table 增加表declare tm_i integer; begin select count(*) into tm_i from user_tables where lower(table_name)=lower('表名1'); if tm_i=0 then execute immediate 'create table 表名2 as select * from 表名1'; end if;end; -- table 删除表declare tm_i integer; begin select count(*) into tm_i from user_tables where lower(table_name)=lower('表名1'); if tm_i>0 then execute immediate 'drop table 表名1'; end if; Select Count(*) Into tm_i From User_Objects t Where Lower(t.OBJECT_NAME) = Lower('table_name') And Lower(t.OBJECT_TYPE) = Lower('Table'); If tm_i > 0 Then Execute Immediate 'Drop table table_name'; End If;end;declare tm_i integer; begin select count(*) into tm_i from user_tables where lower(table_name)=lower('表名1'); if tm_i>0 then execute immediate 'drop table 表名1'; end if;end;--删除触发器Declare tm_i Integer;Begin Select Count(*) Into tm_i From User_Objects t Where Lower(t.OBJECT_NAME) = Lower('Trigger_name') And Lower(t.OBJECT_TYPE) = Lower('Trigger'); If tm_i > 0 Then Execute Immediate 'Drop Trigger Trigger_name'; End If;End;--禁用触发器Declare tm_i Integer;Begin Select Count(*) Into tm_i From User_Triggers t Where Lower(t.trigger_name) = Lower('trigger_name') And Lower(t.status) = Lower('ENABLED'); If tm_i > 0 Then Execute Immediate 'ALTER TRIGGER trigger_name DISABLE'; End If;End;--删除视图,Declare tm_i Integer;Begin Select Count(*) Into tm_i From User_Objects t Where Lower(t.OBJECT_NAME) = Lower('Viewer_name') And Lower(t.OBJECT_TYPE) = Lower('View'); If tm_i > 0 Then Execute Immediate 'Drop view viewer_name'; End If;End; --删除函数,Declare tm_i Integer;Begin Select Count(*) Into tm_i From User_Objects t Where Lower(t.OBJECT_NAME) = Lower('function_name') And Lower(t.OBJECT_TYPE) = Lower('Function'); If tm_i > 0 Then Execute Immediate 'Drop Function function_name'; End If;End;-- sequence 创建Sequencedeclare tm_i integer; begin select count(*) into tm_i from User_Objects t where Lower(t.OBJECT_NAME) = Lower('SEQ_PARAMLOGS') And Lower(t.OBJECT_TYPE) = Lower('Sequence'); if tm_i=0 then execute immediate 'create sequence SEQ_PARAMLOGS_ID minvalue 1 maxvalue 999999999999999999999999999'<span style="white-space:pre"></span> ||' start with 1 increment by 1 cache 20'; end if; end; -- sequence 删除Sequencedeclare tm_i integer; begin select count(*) into tm_i from User_Objects t where Lower(t.OBJECT_NAME) = Lower('SEQ_PARAMLOGS') And Lower(t.OBJECT_TYPE) = Lower('Sequence'); if tm_i>0 then execute immediate 'Drop sequence seq_paramlogs'; end if; end;-- forientkey 创建外键begin for x in ( select a.constraint_name, a.table_name <span style="white-space:pre"></span> from user_constraints a <span style="white-space:pre"></span> inner join user_constraints b on a.r_constraint_name=b.constraint_name where lower(b.table_name) = Lower('表名1') and Lower(a.constraint_type)=Lower('R') ) loop execute immediate 'alter table '|| x.table_name || ' disable constraint '||x.constraint_name; end loop; for x in ( select a.constraint_name, a.table_name <span style="white-space:pre"></span> from user_constraints a <span style="white-space:pre"></span> inner join user_constraints b on a.r_constraint_name=b.constraint_name where lower(b.table_name) = Lower('表名1') and Lower(a.constraint_type)=Lower('R') ) loop execute immediate 'alter table '|| x.table_name || ' enable constraint '||x.constraint_name; end loop;end;-- procedure 删除过程declare tm_i integer; begin select count(*) into tm_i from User_Objects t Where lower(t.object_name) = lower('up_report_data_10259') And Lower(t.OBJECT_TYPE) = Lower('Procedure'); if tm_i>0 then execute immediate 'drop procedure up_report_data_10259'; end if; end;-- Index 删除索引Declare tm_i Integer;Begin Select Count(*) Into tm_i From User_Objects t Where Lower(t.OBJECT_NAME) = Lower('Index_name') And Lower(t.OBJECT_TYPE) = Lower('Index'); If tm_i > 0 Then Execute Immediate 'Drop Index index_name'; End If;End;-- 修改字段类型declare tm_i integer;Begin --如果字段关联了主键先删除相关主键 select count(*) into tm_i from user_constraints <span style="white-space:pre"></span> where Lower(table_name)=Lower('table_name') <span style="white-space:pre"></span> and lower(constraint_name)=lower('PK_Constraints_name'); if tm_i>0 then execute immediate 'alter table table_name drop constraint PK_Constraints_name cascade'; end if; --判断索引 select count(*) into tm_i from user_indexes <span style="white-space:pre"></span> where table_name='table_name' <span style="white-space:pre"></span> and lower(index_name)=lower('PK_Constraints_name'); if tm_i>0 then execute immediate 'DROP INDEX PK_Constraints_name'; end if; --判断字段 select count(*) into tm_i from user_tab_cols where table_name = upper('table_name') and column_name =upper('column_name_temp'); if tm_i<=0 then execute immediate 'alter table table_name add column_name_temp varchar2(20) default '' '' not null'; End If; --将旧列值更新到新列 execute immediate 'update table_name set column_name_temp=column_name'; Execute Immediate 'Update table_name Set column_name_temp = '' '' where column_name_temp = ''0'' '; --删除原列 execute immediate 'alter Table table_name drop column column_name'; --重命名新列 execute immediate 'alter table table_name rename column column_name_temp to column_name'; --如果有主键重新创建主键 execute immediate 'alter table risk_table_index add constraint PK_risk_table_index primary key (TABLE_NAME, FIELD_NAMES)';end;/
0 0
- 可重复执行SQL
- sql可重复执行专题
- 可重复执行的SQL Script, 在已有表中添加字段
- oracle可重复执行脚本
- 有关MySql编写可重复执行的sql(主要用于部署升级等)
- 可重复升级SQL语句
- oracle可重复执行脚本写法---总结
- oracle可重复执行脚本写法---总结
- MySQL修改表-防止SQL重复执行
- 重复执行SQL判断语句整合
- 可为NULL 但不能重复的SQL约束
- MS SQL 执行过程中执行状态,可查看当前正在执行的sql等信息
- SQL Server 重复执行作业中某个步骤
- SQL Server 重复执行作业中某个步骤
- Hibernate执行sql语句 返回可遍历list
- 执行多条SQL语句,执行数据库事务(可传入Sql参数)
- 执行多条SQL语句,执行数据库事务(可传入Sql参数)
- 可重复组合
- C 特别关键字 static、extern、const等
- MySQL-5.6.13免安装版配置方法
- 系统测试(System Testing)
- 省市选择器WinFrom
- 5.1.2 测试用例的选择原则
- 可重复执行SQL
- Windows系统下硬盘安装UBUNTU教程
- 6 测试用例设计的原则
- CXF与Spring的整合
- 7.1 测试用例内容
- linux部署项目(Java项目+Tomcat+mysql)
- 《杰克·韦尔奇》备忘笔记
- 测试流程
- HDOJ u Calculate e(java)