Oracle常用SQL

<pre class="sql" name="code">正则表达式： 

regexp_like(t.user_account,'^[A-z][0-9]{5,8}+$')

自动生成增删改查SQL语句：

INSERT：

SELECT T.COLUMN_NAME || ' ' ,
       'ORCHESTRATE.'||T.COLUMN_NAME||',',
       T.DATE_TYPE,
       T.DATE_LENGTH,
       T.COLUMN_ID COLUMN_ID
  FROM ALL_TABLE_COLUMNS T
 WHERE T.TABLE_NAME = 'TABLE_NAME'
   AND T.OWNER = 'OWNER_NAME'
 ORDER BY T.COLUMN_ID           

UPDATE:

SELECT T.CLOUM_NAME||':ORCHESTRATE.'||T.COLUMN_NAME||','
  FROM ALL_TABLE_COLUMNS T
 WHERE T.TABLE_NAME = 'TABLE_NAME'
   AND T.OWNER = 'OWNER_NAME'
 ORDER BY T.COLUMN_ID;

DELETE:

SELECT 'T.'||CLOUMN_NAME||',' 
  FROM ALL_TABLE_COLUMNS T
 WHERE T.TABLE_NAME = 'TABLE_NAME'
   AND T.OWNER = 'OWNER_NAME'
 ORDER BY T.COLUMN_ID;

数据同步：

INSERT INTO TABLE_NAME NOLOGGING SELECT * FROM TABLE@DB_LINK

闪回被删除的表：

FLASHBACK TABLE TABLE_NAME TO BEFORE DROP;

查询重复数据：

<pre class="sql" name="code">SELECT COL_A,COL_B,COL_C,COUNT(*) OVER(PARTITION BY NULL) CNT FROM TABLE_NAME;

SELECT COL_A,COL_B,COL_C,COUNT(*) FROM TABLE_NAMEGROUP BY COL_A,COL_B,COL_C HAVING COUNT(*) > 1

删除重复数据：

begin
  delete from table_name t
    where t.rowid> (select min(rowid)
                      from table_name s where t.id = s.id);
  commit;
end;

 

未完待续...