使用Oracle11随笔

写入存储过程：对传入的表名进行数据统计并写入中间表中

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 外键名;