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>或咨询广交会客户联络中心，电邮：info@cantonfair.org.cn,电话：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":"cmail@cantonfair.org.cn","ReplyName":"cmail@cantonfair.org.cn","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通常查询速度快的原因