Oracle Sql語句

標籤：

---------------------------------------------------------------------------
查看oracle中啟動並執行sql語句
Select Last_Load_Time, First_Load_Time, Sql_Text From V$sql
where rownum < 20 and module=‘w3wp.exe‘
order by Last_Load_Time desc

---------------------------------------------------------------------------
查看資料修改記錄
select object_name, created,last_ddl_time from user_objects order by last_ddl_time desc

---------------------------------------------------------------------------
時間格式化
TO_DATE(‘2014/5/22 20:00:35‘, ‘yyyy-mm-dd hh24:mi:ss‘)

--add(添加視圖、表格、預存程序、函數）
CREATE TABLE "BASE_MANAGERANGE"  (
   "BASE_MANAGERANGEOID" VARCHAR2(40)                    NOT NULL,
   "RANGECODE"          VARCHAR2(40),
   "RANGENAME"          VARCHAR2(300),
   "PARENTRANGEID"      VARCHAR2(40),
   "ISMULTILINGUAL"     INT,
   "KEYID"              VARCHAR2(40)                    NOT NULL,
   "LEVEL"              INT,
   "REMARK"             VARCHAR2(100),
   CONSTRAINT PK_BASE_MANAGERANGE PRIMARY KEY ("BASE_MANAGERANGEOID")
);

--update（修改表結構、資料）
ALTER TABLE (表名) ADD (列名 資料類型);
ALTER TABLE (表名) MODIFY (列名 資料類型);
ALTER TABLE (表名) RENAME COLUMN (當前列名) TO (新列名);
ALTER TABLE (表名) DROP COLUMN (列名);
ALTER TABLE (當前表名) RENAME TO (新表名);
e.g. UPDATE Bm_Buyerdata b SET b.sessionnum=‘116‘ WHERE b.sessionnum=‘117‘;

--delete(刪除資料、表格）
DROP TABLE "BASE_MANAGERANGE"
DELETE FROM Bm_Buyerdata b WHERE b.sessionnum=‘dfasdfsadf‘;

-- 定序
SCHINESE_RADICAL_M 按照部首（第一順序）、筆劃（第二順序）排序
SCHINESE_STROKE_M 按照筆劃（第一順序）、部首（第二順序）排序
SCHINESE_PINYIN_M 按照拼音排序，系統的預設排序方式為拼音排序
e.g. select * from dept order by nlssort(name,‘NLS_SORT=SCHINESE_STROKE_M‘);

-- order時null排序的處理
1）不加“關照”的情況下，我們可以把那些NULL值假想為所有內容中值是最大的，因此，升序排序後NULL值在最後，倒序排序後NULL值在最前！
2）特殊“關照”的情況下，當指定“NULLS FIRST”時，無論是升序排序還是倒序排序，NULL值都會排列在最前面；當指定“NULLS LAST”時，無論是升序排序還是倒序排序，NULL值都會排列在最後面。

-- 常用函數
1、 多行合并為一列 wmsys.wm_concat(col1) 在oracle 10下使用
替換預設的“,”分隔字元，使用replace(wmsys.wm_concat(col1), ‘,‘,‘;‘)； 增加其他的內容 wmsys.wm_concat(col1 || ‘ 單位(‘ || col2 || ‘)‘ )

-- rownum 大於的問題
rowid 與 rownum 雖都被稱為偽列，但它們的存在方式是不一樣的：
rowid 是物理存在的，表示記錄在資料表空間中的唯一位置ID，在DB中是唯一的。只要記錄沒被搬動過，rowid是不變的。
因為rownum總是從1開始的，第一條不滿足去掉的話，第二條的rownum 又成了1。依此類推，所以永遠沒有滿足條件的記錄。可以這樣理解：rownum是一個序列，是Oracle資料庫從資料檔案或緩衝區中讀取資料的順序。
所以select * from table1 where rownum > 1始終擷取不到值
正確的做法是 select * from (select rownum rownum_, t.* from table1 t) where rownum_ > 1

-- 子查詢中不能使用order by的問題
在From 語句中出現的子查詢被稱為 inline view（內聯視圖），在where 語句中出現的子查詢被稱為nested subquery(嵌套子查詢)。
經測試證明在嵌套子查詢中不允許出現order by 語句
select * from scott.emp
where ename in (select ename from scott.emp order by ename)
會報 “ORA-00907:缺少右括弧”的錯誤。
如果將上面的嵌套子查詢再封裝一層，成為第二層查詢的內聯視圖。
select * from scott.emp
where ename in (select * from(select ename from scott.emp order by ename))
則可以成功執行。

-- clob欄位插入資料
ORA-01704: string literal too long
DECLARE
  str varchar2(32767);
BEGIN
  str := ‘{"EM_TaskOID":"da8ebe4d-cbd3-498b-9892-d9dd073aa82a","TemplateID":"000814c8-0000-0000-0000-0000d01e4a99","ContentTemplate":"<div style=\"OVERFLOW: hidden; HEIGHT: 232px; WIDTH: 700px; MARGIN: 0px auto\"><img alt=\"\" src=\"http://invitation.cantonfair.org.cn/images/sendmail/images/115best/top.jpg\" height=\"232\" width=\"700\" /></div><table cellpadding=\"0\" cellspacing=\"0\" align=\"center\" border=\"0\" width=\"700\"><tbody><tr><td background=\"http://invitation.cantonfair.org.cn/images/sendmail/images/115best/left.jpg\" valign=\"top\" width=\"100\">‘|| ‘&‘ ||‘nbsp;</td><td background=\"http://invitation.cantonfair.org.cn/images/sendmail/images/115best/middle.jpg\" height=\"600\" valign=\"top\"><p style=\"TEXT-ALIGN: center\"><span style=\"font-family:仿宋_GB2312;FONT-SIZE: 18pt\"><span style=\"font-size:16px;\"><span style=\"font-family:Arial;mso-fareast-font-family: 仿宋_GB2312\"><br /></span></span></span><span class=\"STYLE1\">歡迎參加第115屆中國進出口商品交易會</span></p><p style=\"TEXT-ALIGN: center\">　　　　　　　　　　　　　　　　　　　　　　　　　　{{content6}}</p><p class=\"STYLE1\">尊敬的{{content2}}： <br />　　您的朋友{{content3}}，來自{{content4}}向您推薦中國第一展— 中國進出口商品交易會。 <br />　　中國進出口商品交易會，又稱廣交會，創辦於1957年春，每年春秋兩季在廣州舉辦，是中國目前曆史最長、層次最高、規模最大、商品種類最全、到會客商最多且國別地區分布最廣、成交效果最好、信譽最佳的綜合性國際貿易盛會。　 <br />　　第{{content5}}屆廣交會將於2014年4月15日—5月5日在廣州中國進出口商品交易會展館分三期舉辦。本屆廣交會總展覽面積達116萬平方米，展位元5.9萬個。<br />　　廣交會的參展企業都是中國優秀品牌企業,信譽良好,同時廣交會的產品種類齊全，且品質好價格優，還能根據客人需求進行個人化設計和製作，非常值得您前來參觀採購。<br />　　歡迎您前來參加第{{content5}}屆廣交會！關於廣交會境外採購商線上申請註冊、報到辦證、翻譯服務、酒店訂房及交通訊息等事宜，請登陸廣交會官方網站<a href=\"http://www.cantonfair.org.cn/\">www.cantonfair.org.cn</a>或諮詢廣交會客戶聯絡中心，電郵：[email protected],電話：4000888999(中國境內)，86-20-28888999（中國境外）。<br />　　出口展區：第一期2014年4月15-19日：電子及家電；照明；車輛及配件；機械；五金工具；建材；化工產品。第二期2014年4月23-27日：日用消費品；禮品；家居裝飾品。第三期2014年5月1日- 5月5日：紡織服裝；鞋；辦公、箱包及休閑用品；醫藥及醫學保健；食品。<br />　　進口展區：第一期2014年4月15-19日：電子及家電；建材及五金；機械裝置；工業原材料等。第三期2014年5月1日-5月5日：食品及農產品；醫學保健及美容護理產品；禮品及裝飾品等。<br />　　展區安排的相關資訊截至2014年3月26日,最新訊息請以廣交會官方網站<a href=\"http://www.cantonfair.org.cn\">www.cantonfair.org.cn</a> 發布為準。<br />　　請您與會辦證時帶上此邀請郵件和境外個人有效證件，您將享受到免費辦理首張入館證件的優惠。</p><span><span style=\"font-size:16px;\"><span style=\"font-family:Times New Roman;\"></span></span></span><p class=\"MsoNormal\" style=\"TEXT-ALIGN: left; MARGIN: 0cm 0cm 0pt; TEXT-INDENT: 21pt; mso-margin-top-alt: auto; mso-margin-bottom-alt: auto; mso-pagination: widow-orphan; mso-char-indent-count: 2.0\" align=\"left\"></p></td><td background=\"http://invitation.cantonfair.org.cn/images/sendmail/images/115best/right.jpg\" valign=\"top\" width=\"103\">‘|| ‘&‘ ||‘nbsp;</td></tr></tbody></table><div class=\"cc\" style=\"WIDTH: 702px; MARGIN: 0px auto; heihgt: 128px\"><div style=\"HEIGHT: 128px; WIDTH: 111px; FLOAT: left\"><img alt=\"\" src=\"http://invitation.cantonfair.org.cn/images/sendmail/images/115best/0830-3_02.jpg\" height=\"128\" width=\"111\" /></div><div class=\"fl\" style=\"HEIGHT: 128px; WIDTH: 590px; FLOAT: left\"><div style=\"HEIGHT: 19px\"><img alt=\"\" src=\"http://invitation.cantonfair.org.cn/images/sendmail/images/115best/0830-3_03cn.jpg\" height=\"19\" width=\"589\" /></div><div class=\"cc\" style=\"HEIGHT: 90px\"><div style=\"HEIGHT: 90px; WIDTH: 275px; BACKGROUND: url([basecodebar]); FLOAT: left\">{{content1}}</div><div style=\"HEIGHT: 90px; WIDTH: 314px; FLOAT: left\"><img alt=\"\" src=\"http://invitation.cantonfair.org.cn/images/sendmail/images/115best/0830-3_05cn.jpg\" height=\"90\" width=\"314\" /></div></div><div><img alt=\"\" src=\"http://invitation.cantonfair.org.cn/images/sendmail/images/115best/0830-3_06cn.jpg\" height=\"19\" width=\"589\" /></div></div></div>","SubjectTemplate":"老採購商推薦新採購商 ","ifSubmit":false,"mailSeq":null,"mailListNo":"185539","mpid":null,"mptempid":null,"mpaccid":null,"html":null,"fileName":"20140725173024.txt","mpTitle":null,"ReplyEmail":"[email protected]","ReplyName":"[email protected]","Language":"ZH"}‘;
  UPDATE EM_EmaiTask SET SYS_LAST_UPD=SYSDATE, submitResult=str WHERE EM_EmaiTaskOID=‘da8ebe4d-cbd3-498b-9892-d9dd073aa82a‘;
END;
分析：把長度超過4000的字串賦值給變數，然後在update或insert

-- &nbsp;在oracle中為變數nbsp;問題解決
update table1 set col1 = ‘text‘|| ‘&‘ || ‘test1‘
分析：oracle中把&當做了自訂變數，也可以使用ASCII碼chr(38)

-- 單引號的處理
Select ‘test ‘‘‘ from dual;
Select ‘It‘ || chr(39) || ‘fine‘ from dual;
分析 ‘‘ 等同於一個單引號，另外chr(39)是單引號的ASCII碼

-- 字串類型的欄位，裡面的值如果都是數字，使用order by排序時，需要 to_number(column1)
你這列是字串，不是int，就按ASCⅡ碼排序，不轉換為數字，那麼類似的效果為
column1
----
9
80
700
10000

-- decode(欄位或欄位的運算，值1，值2，值3）
這個函數啟動並執行結果是，當欄位或欄位的運算的值等於值1時，該函數傳回值2，否則傳回值3；當然值1，值2，值3也可以是運算式。

-- 重建索引
alter index index_name rebuild tablespace tablespace_name
“tablespace_name”加入資料表空間名，會將指定的索引移動到指定的資料表空間當中。
註：
analyze 操作只是統計資訊，並將統計資訊存放起來供日後分析SQL使用，不進行重建之類的具體實施性操作,因此要重建索引的話
還是要用 alter index index_name rebuild
可能造成的錯誤：
無法刪除資料，提示“ORA-01502：索引‘table.index’或所這類索引的分區處於不可用狀態”

--------------------------------------------------------------------------------------------
-- 加號在oracle中的使用
1. LEFT OUTER JOIN：左外關聯
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);

等價於
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id=d.department_id(+);

2. RIGHT OUTER JOIN：右外關聯

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);

等價於

SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+)=d.department_id;

--------------------------------------------------------------------------------------------
like的速度

可以使用instr來替換，來提高速度

----------------------------------------------------
instr用法
instr( string1, string2, start_position,nth_appearance ) [1]  [2]
string1
源字串，要在此字串中尋找。
string2
要在string1中尋找的字串 。
start_position
代表string1 的哪個位置開始尋找。此參數可選，如果省略預設為1. 字串索引從1開始。如果此參數為正，從左至右開始檢索，如果此參數為負，從右至左檢索，返回要尋找的字串在源字串中的開始索引。
nth_appearance
代表要尋找第幾次出現的string2. 此參數可選，如果省略，預設為 1.如果為負數系統會報錯。
注意：
　　位置索引號從1開始。
　　如果String2在String1中沒有找到，instr函數返回0。
　　樣本：
　　SELECT instr(‘syranmo‘,‘s‘) FROM dual; -- 返回 1
　　SELECT instr(‘syranmo‘,‘ra‘) FROM dual; -- 返回 3
　　SELECT instr(‘syran mo‘,‘a‘,1,2) FROM dual; -- 返回 0

--------------------------------------------------------------------------------------------
EXISTS 與 in
1、exists的用法
EXISTS裡的子查詢結果集非空，EXISTS()子句的值就是true；
EXISTS裡的子查詢結果集為空白，EXISTS()子句的值就是false。
e.g. select * from T1 where exists(select 1 from T2 where T1.a=T2.a)
2、效能分析
資料量大的時候用exists，資料量少的時候用in
通過使用EXISTS，Oracle會首先檢查主查詢，然後運行子查詢直到它找到第一個匹配項，這就節省了時間。Oracle在執行IN子查詢時，首先執行子查詢，並將獲得的結果清單存放在一個加了索引的暫存資料表中。在執行子查詢之前，系統先將主查詢掛起，待子查詢執行完畢，存放在暫存資料表中以後再執行主查詢。這也就是使用EXISTS比使用IN通常查詢速度快的原因

Oracle Sql語句