写入存储过程:对传入的表名进行数据统计并写入中间表中
CREATE OR REPLACE PROCEDURE TEST(iv_y in varchar2) Authid Current_User IS
vsql varchar2(2000);
vrows number;
begin
for i in (select column_name from all_tab_columns where TABLE_NAME = iv_y) loop
vsql :='select count(1) from '|| iv_y ||' where '|| i.column_name ||' is not null';
execute immediate vsql into vrows;
MERGE INTO test T1
USING (SELECT i.column_name AS TCOLMENTNAME, vrows AS TSUM, iv_y AS TABLENAME FROM dual) T2
ON (T1.TABLENAME = T2.TABLENAME and T1.TCOLMENTNAME = T2.TCOLMENTNAME)
WHEN MATCHED THEN
update set t1.TSUM = t2.TSUM
WHEN NOT MATCHED THEN
insert (TCOLMENTNAME, TSUM, TABLENAME) VALUES (t2.TCOLMENTNAME, t2.TSUM, t2.TABLENAME);
commit;
end loop;
end;
创建中间表
-- Create table
create table SUMTABLE
(
TCOLMENTNAME VARCHAR2(50),
TSUM VARCHAR2(100),
TABLENAME VARCHAR2(100)
)
tablespace user
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64
next 8
minextents 1
maxextents unlimited
);
执行存储过程
begin
test('tablename');
end;
查询执行结果
select * from sumtable
对Oracle11g的insert和update进行改造
MERGE INTO test T1
USING (SELECT 'DESCINFO' AS TCOLMENTNAME, '12' AS TSUM, 'MDS_DS_ROLE' AS TABLENAME FROM dual) T2
ON (T1.TABLENAME = T2.TABLENAME and T1.TCOLMENTNAME = T2.TCOLMENTNAME)
WHEN MATCHED THEN
update set T1.TSUM = T2.TSUM
WHEN NOT MATCHED THEN
insert (TCOLMENTNAME, TSUM, TABLENAME) VALUES (t2.TCOLMENTNAME, t2.TSUM, t2.TABLENAME);
在ON后可以追加多个条件,但是在update时,要去掉条件的语句
两张表同步数据
MERGE INTO GW_R_Z R --目标表
USING GW_H_Z T --原表
ON ( R.STCD = T.STCD AND T.TM = R.TM) --匹配条件
WHEN MATCHED THEN UPDATE SET R.BD = T.BD --更新字段
WHEN NOT MATCHED THEN INSERT (STCD,TM,BD,TS) VALUES(t.STCD,t.TM,t.BD,t.TS) --添加字段和内容
查询所有表名:
select t.table_name from user_tables t;
查询所有字段名:
select t.column_name from user_col_comments t;
查询指定表的所有字段名:
select t.column_name from user_col_comments t where t.table_name = 'table_name ';
查询指定表的所有字段名和字段说明:
select t.column_name, t.column_name from user_col_comments t where t.table_name = 'table_name ';
查询所有表的表名和表说明:
select t.table_name,f.comments from user_tables t inner join user_tab_comments f on t.table_name = f.table_name;
查询模糊表名的表名和表说明:
select t.table_name from user_tables t where t.table_name like 'yourtablelikename%';
select t.table_name,f.comments from user_tables t inner join user_tab_comments f on t.table_name = f.table_name where t.table_name like 'yourtablelikename%';
--查询表的数据条数、表名、中文表名
select a.num_rows, a.TABLE_NAME, b.COMMENTS from user_tables a, user_tab_comments b WHERE a.TABLE_NAME = b.TABLE_NAME order by TABLE_NAME;
--使表中的主外键暂时失效
使主键失效:alter table tableName disable primary key;
使主键恢复:alter table tableName enable primary key;
删除主键:alter table tableName drop primary key;
使外键失效:alter table tableName disable constraint 外键名;
使外键恢复:alter table tableName enable constraint 外键名;
删除外键:alter table tableName drop constraint 外键名;