資料庫黑洞

紀錄一些同仁曾問過的、或是實用的語法

 

1.SELECT 大量流水號 (產出 1~ 900009 的資料)

 

from dual
connect by level <= 900009;

 

2.查看語法執行計畫

假設我的query是這樣:

FROM AGS_TBASSACT
LEFT JOIN AGS_TBAssActStatic ON AGS_TBAssActStatic.static_id = AGS_TBASSACT.static_id
LEFT JOIN AGS_TBAssActDynamic ON AGS_TBAssActDynamic.dynamic_id = AGS_TBASSACT.dynamic_id

(1) 早期不知道 SQL Developer 有快捷鍵的寫法

explain plan
FOR 
select * 
FROM AGS_TBASSACT
LEFT JOIN AGS_TBAssActStatic ON AGS_TBAssActStatic.static_id = AGS_TBASSACT.static_id
LEFT JOIN AGS_TBAssActDynamic ON AGS_TBAssActDynamic.dynamic_id = AGS_TBASSACT.dynamic_id;

--2.查看執行計畫
SELECT * FROM table(dbms_xplan.display);

 

 

(2) F10

 

3.SELECT TOP 10

 

FROM 資料表
WHERE ROWNUM <= 10;

 

4.取得 TABLE 欄位名稱

 

FROM user_tab_cols
WHERE table_name = 'AGS_TBASSACT'
order by column_id;

 

5.字串連結應用

 

|| 'b' || 'c' AS "ss" FROM user_tab_cols WHERE table_name = 'AGS_TBASSACT' order by column_id;

 

6.Update 語法

(1) 複雜且慢

 

WHERE EXISTS (SELECT B.欄位 FROM 來源資料表 B WHERE A.ID = B.ID);

 

(2)

SELECT b.col1 as old_col1,
       t.col1 as new_col1,
FROM bigTable b, tempTable t
WHERE b.col = t.col)
   SET old_col1 = new_col1;

 

 

7.自定義參數

 

SELECT *
FROM AGS_TBASSACTMEMO
WHERE MEMO_ID = '&var';

 

8.整批改型態語法 (註: 要做之前必須先把產出的"Query_rename","Query_add","Query_update"語法都保留起來)

 

	'ALTER TABLE ' || b.TABLE_NAME || ' ADD ' || b.COLUMN_NAME || ' DATE; ' AS "Query_add",
	'UPDATE ' || b.TABLE_NAME || ' SET ' || b.COLUMN_NAME || ' = ' || b.COLUMN_NAME || '_AT3184;' AS "Query_update",
	b.*
FROM user_tables a
JOIN user_tab_cols b ON a.TABLE_NAME = b.TABLE_NAME
WHERE b.data_type LIKE 'TIMESTAMP%';

 

9.select * into

 

 

10.產生所有索引重建語法

 

 

11. table 相關

alter table
   資料表
rename to
   新資料表名;

--更改欄位名稱
--alter talbe  rename column  to ;
Alter table ags_fundsource rename column static_id TO ass_id;

--刪除欄位
alter table  drop column ;
Alter table AGS_TBASSACT drop column static_id;

--新增欄位
ALTER TABLE AGS_TBASSACT ADD STATIC_ID NUMBER(10, 0);

--外來鍵 add foreign key
ALTER TABLE ags_inventorydep ADD CONSTRAINT FK_AGS_INVENTORYDEP FOREIGN KEY (RPT_NO) REFERENCES ags_inventory (RPT_NO);

 

12.字串寫入時間 (DATE, TIMESTAMP)欄位

 

(date_field)
values
(TO_DATE('2003/05/03 21:02:44', 'yyyy/mm/dd hh24:mi:ss'));

 

本著作係採用創用 CC 姓名標示-相同方式分享 4.0 國際 授權條款授權，文章歡迎轉載，請註明出處，謝謝~~~