CREATE GLOBAL TEMPORARY TABLE ddd
ON COMMIT PRESERVE ROWS AS
SELECT cifno FROM cif where cifno is null;
--这里我想建立临时表,先判断一下有没有这个表,如果有则先删除 ,位置怎么放置呢 ,怎么执行呢
declare v_result number;
begin
select count(*) into v_result from user_tables where temporary='Y' and table_name=upper('ddd');
if v_result>0
then
execute immediate 'truncate table ddd';
execute immediate 'drop table ddd';
end if;
end;
--下面这七面插入语句可以用数组实现吗? 赋值我写好了在这里(‘sav’,'LTY','TAL','SUR','INA','EBT','RIO')
insert into ddd select cifno from sav where concomp='07507';
insert into ddd select cifno from LTY where concomp='07507';
insert into ddd select cifno from TAL where concomp='07507';
insert into ddd select cifno from SUR where concomp='07507';
insert into ddd select cifno from INA where concomp='07507';
insert into ddd select cifno from EBT where concomp='07507' ;
insert into ddd select cifno from RIO where concomp='07507';
--信用卡
insert into ddd select map.cifno from ard ,map
where ard.conacno=map.cardno and ard.concomp='07507';
--这是基金中间换算表
insert into ddd select cifno from MID where concomp='07507';--删除重复的客户号
delete from ddd where cifno in (select cifno from ddd group by cifno
having count(cifno) > 1) and rowid not in (select min(rowid) from ddd group by cifno having count(cifno)>1);
--判断查询结果找到在cif表里面的客户关系comno字段不等于07507的
delete from ddd where exists(select * from cif where ddd.cifno=cif.cifno and cif.comno='07507');
CREATE GLOBAL TEMPORARY TABLE ddd3
ON COMMIT PRESERVE ROWS AS
select cifno,concomp,conacno from tal where exists(select cifno from ddd
where tal.cifno=ddd2.cifno and concomp!='07507');
insert into ddd3 select cifno,concomp,conacno from SAV where cifno in (select cifno from ddd ) and concomp!='07507'
insert into ddd3 select cifno,concomp,conacno from LTY where cifno in (select cifno from ddd ) and concomp!='07507'
insert into ddd3 select cifno,concomp,conacno from SUR where cifno in (select cifno from ddd ) and concomp!='07507'
insert into ddd3 select cifno,concomp,conacno from INA where cifno in (select cifno from ddd ) and concomp!='07507'
insert into ddd3 select cifno,concomp,conacno from EBT where cifno in (select cifno from ddd ) and concomp!='07507'
insert into ddd3 select cifno,concomp,conacno from RIO where cifno in (select cifno from ddd ) and concomp!='07507'
insert into ddd3 select cifno,concomp,conacno from MID where cifno in (select cifno from ddd ) and concomp!='07507'
insert into ddd3 select map.cifno from ard ,map
where ard.conacno=map.cardno and ard.concomp!='07507';select a.cifno,a.cifcname as ,a.cifftel,a.cifmtel,b.codname ,
a.cifid , a.cifasset , a.cifstat27 ,ddd3.concomp , ddd3.conacno
from cif a,ode b,ddd3 where exists(select * from ddd3 where a.cifno=ddd3.cifno) and a.cifidtype=b.codno;
truncate table ddd,ddd3;
drop table ddd,ddd3;
修改
ON COMMIT PRESERVE ROWS AS
SELECT cifno FROM cif where cifno is null;
--这里我想建立临时表,先判断一下有没有这个表,如果有则先删除 ,位置怎么放置呢 ,怎么执行呢
declare v_result number;
begin
select count(*) into v_result from user_tables where temporary='Y' and table_name=upper('ddd');
if v_result>0
then
execute immediate 'truncate table ddd';
execute immediate 'drop table ddd';
end if;
end;
--下面这七面插入语句可以用数组实现吗? 赋值我写好了在这里(‘sav’,'LTY','TAL','SUR','INA','EBT','RIO')
insert into ddd select cifno from sav where concomp='07507';
insert into ddd select cifno from LTY where concomp='07507';
insert into ddd select cifno from TAL where concomp='07507';
insert into ddd select cifno from SUR where concomp='07507';
insert into ddd select cifno from INA where concomp='07507';
insert into ddd select cifno from EBT where concomp='07507' ;
insert into ddd select cifno from RIO where concomp='07507';
--信用卡
insert into ddd select map.cifno from ard ,map
where ard.conacno=map.cardno and ard.concomp='07507';
--这是基金中间换算表
insert into ddd select cifno from MID where concomp='07507';--删除重复的客户号
delete from ddd where cifno in (select cifno from ddd group by cifno
having count(cifno) > 1) and rowid not in (select min(rowid) from ddd group by cifno having count(cifno)>1);
--判断查询结果找到在cif表里面的客户关系comno字段不等于07507的
delete from ddd where exists(select * from cif where ddd.cifno=cif.cifno and cif.comno='07507');
CREATE GLOBAL TEMPORARY TABLE ddd3
ON COMMIT PRESERVE ROWS AS
select cifno,concomp,conacno from tal where exists(select cifno from ddd
where tal.cifno=ddd2.cifno and concomp!='07507');
insert into ddd3 select cifno,concomp,conacno from SAV where cifno in (select cifno from ddd ) and concomp!='07507'
insert into ddd3 select cifno,concomp,conacno from LTY where cifno in (select cifno from ddd ) and concomp!='07507'
insert into ddd3 select cifno,concomp,conacno from SUR where cifno in (select cifno from ddd ) and concomp!='07507'
insert into ddd3 select cifno,concomp,conacno from INA where cifno in (select cifno from ddd ) and concomp!='07507'
insert into ddd3 select cifno,concomp,conacno from EBT where cifno in (select cifno from ddd ) and concomp!='07507'
insert into ddd3 select cifno,concomp,conacno from RIO where cifno in (select cifno from ddd ) and concomp!='07507'
insert into ddd3 select cifno,concomp,conacno from MID where cifno in (select cifno from ddd ) and concomp!='07507'
insert into ddd3 select map.cifno from ard ,map
where ard.conacno=map.cardno and ard.concomp!='07507';select a.cifno,a.cifcname as ,a.cifftel,a.cifmtel,b.codname ,
a.cifid , a.cifasset , a.cifstat27 ,ddd3.concomp , ddd3.conacno
from cif a,ode b,ddd3 where exists(select * from ddd3 where a.cifno=ddd3.cifno) and a.cifidtype=b.codno;
truncate table ddd,ddd3;
drop table ddd,ddd3;
修改
解决方案 »
- 找一本有关Orcale数据库的相关书籍,给个CSDN下载链接(好点的有木有)
- oracle中判斷數據不在表中
- 如何设置10G的启动方式?
- 关于大数据的数据库设计问题咨询
- oracle用plsql打开后,package里有sys.dbms_##的东东,是什么?还有plsql中packages里面一般会发些什么用途东东
- 求一oracle更新语句!!!急啊,在线等
- 一个dblink的问题!
- 如何只比较date字段的月日组合大小,而不考虑年的成分?
- 请问各位大侠!!我怎么在过程中无法访问sys.dba_data_files这个表呢.用单独的SQL语句都可以查看!(都在同一用户下)
- 我刚接触oracle,请大家帮忙写个这个触发器。叩首!
- 请教大家 oracle 查询记录间隔最大记录数
- root连接oracle 提示:ORA-12546: TNS:permission denied (AIX环境)
a.cifid , a.cifasset , a.cifstat27 ,ddd3.concomp , ddd3.conacno
from cif a,ode b,ddd3 where exists(select * from ddd3 where a.cifno=ddd3.cifno) and a.cifidtype=b.codno;你直接说明这句要做什么就行,它关系到有没有改写的意义。