1、 函数<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
日期时间相关：
u To_Date(时间字段,时间格式) 字符串转换为日期、时间格式
² TO_DATE('<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" />2004-8-4','YYYY-MM-DD’);
² insert into ins_date values(to_date('2004-08-10 15:33:12','yyyy-mm-dd hh24:mi:ss'));
² insert into tdate(col2) values(to_date('2004-8-10下午 03:33:12',15:17:26 2 'yyyy-mm-dd am hh:mi:ss'));
u Trunc(Sysdate)
² 1.TRUNC(for dates)
TRUNC函数为指定元素而截去的日期值。
其具体的语法格式如：TRUNC（date[,fmt]）
date一个日期值
fmt日期格式，该日期将由指定的元素格式所截去。忽略它则由最近的日期截去
<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" />
|
--求天数 select trunc(sysdate)-to_date('2004-11-4','yyyy-mm-dd') from dual; select TO_DATE('1999-12-23 08:40:44','yyyy-MM-dd hh:mi:ss') from dual; --1999-12-23 8:40:44 select TRUNC(TO_DATE('1999-12-23 08:40:44','yyyy-MM-dd hh:mi:ss')) from dual; --1999-12-23 select TRUNC(TO_DATE('1999-12-23 08:40:44','yyyy-MM-dd hh:mi:ss'),'yyyy') from dual; --1999-01-01 select TRUNC(TO_DATE('1999-12-23 08:40:44','yyyy-MM-dd hh:mi:ss'),'MM') from dual; --1999-12-01 select TRUNC(TO_DATE('1999-12-23 08:40:44','yyyy-MM-dd hh:mi:ss'),'dd') from dual; --1999-12-23 select TRUNC(TO_DATE('1999-12-23 08:40:44','yyyy-MM-dd hh:mi:ss'),'hh') from dual; --1999-12-23 8:00:00 select TRUNC(TO_DATE('1999-12-23 08:40:44','yyyy-MM-dd hh:mi:ss'),'mi') from dual; --1999-12-23 8:40:00 |
² 2.TRUNC(for number)
TRUNC函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。
其具体的语法格式如下
TRUNC（number[,decimals]）
number待做截取处理的数值
decimals指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分
下面是该函数的使用情况：
TRUNC（89.985，2）=89.98
TRUNC（89.985）=89
TRUNC（89.985，-1）=80
注意：第二个参数可以为负数，表示为小数点左边指定位数后面的部分截去，即均以0记。
u SYSDATE 取得数据库服务器当前日期、时间
² SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH:MI:SS') FROM DUAL;
u NEXT_DAY
u LAST_DAY
u ADD_MONTHS
u MONTHS_BETWEEN
² SELECT LAST_DAY(SYSDATE) FROM DUAL
u 日期的各部分的常用的的写法
² 取时间点的年份的写法：
SELECT TO_CHAR(SYSDATE，'YYYY') FROM DUAL；
² 取时间点的月份的写法：
SELECT TO_CHAR(SYSDATE，'MM') FROM DUAL；
² 取时间点的日的写法：
SELECT TO_CHAR(SYSDATE，'DD') FROM DUAL；
² 取时间点的时的写法：
SELECT TO_CHAR(SYSDATE，'HH24') FROM DUAL；
² 取时间点的分的写法：
SELECT TO_CHAR(SYSDATE，'MI') FROM DUAL；
² 取时间点的秒的写法：
SELECT TO_CHAR(SYSDATE，'SS') FROM DUAL；
² 取时间点的日期的写法：
SELECT TRUNC(SYSDATE) FROM DUAL；
² 取时间点的时间的写法：
SELECT TO_CHAR(SYSDATE，'HH24：MI：SS') FROM DUAL；
² 日期，时间形态变为字符形态：
SELECT TO_CHAR(SYSDATE) FROM DUAL；
² 将字符串转换成日期或时间形态：
SELECT TO_DATE('2003/08/01') FROM DUAL；
² 返回参数的星期几的写法：
SELECT TO_CHAR(SYSDATE，'D') FROM DUAL；
² 返回参数一年中的第几天的写法：
SELECT TO_CHAR(SYSDATE，'DDD') FROM DUAL；
² 返回午夜和参数中指定的时间值之间的秒数的写法：
SELECT TO_CHAR(SYSDATE，'SSSSS') FROM DUAL；
² 返回参数中一年的第几周的写法：
SELECT TO_CHAR(SYSDATE，'WW') FROM DUAL；
数子相关：
u To_Number()
u +、-、*、/
u ABS、COS、EXP、LN、LOG、MOD、POWER、ROUND
u SIN、SINH、SQRT、TAN、TRUNC
u AVG、COUNT、MAX、MIN、SUM
u GREATEST 返回参数的最大值
u LEAST 返回参数的最小值
² SELECT GREATEST(3, 4, 5)*4 FROM DUAL;
² SELECT POWER(2,3) FROM DUAL;
字符串相关：
u To_Char()
To_Char(日期字段,'yyyymmdd')
u || 连接两个字符串
² SELECT RM_INT_FIELD||'--'||RM_STR_FIELD||'YES' FROM ROYAL_MTABLE;
u TRIM/LTRIM/RTRIM 截断串左（右）指定字符串（包括空串）
u ASCII(CHAR) 得到字符串的第一个字符的ASCII值
SELECT ASCII('ABCDE') FROM DUAL；
结果： 65
u CHR(N) 得到数值N指定的字符
SELECT CHR(68) FROM DUAL；
结果： D
u CONCAT(CHAR1，CHAR2) 如何连接两个字符串
SELECT CONCAT('ABC'，'DEFGH') FROM DUAL；
结果： 'ABCDEFGH'
u DECODE(CHAR，N1，CHAR1，N2，CHAR2...) 如何将列中的数值代替为字符串
SELECT DECODE(DAY，1，'SUN'，2，'MON') FROM DUAL；
u INITCAP(CHAR) 将字符串CHAR的第一个字符为大写，其余为小写
SELECT INITCAP('ABCDE') FROM DUAL；
u LENGTH(CHAR) 取一字符串CHAR的长度。
SELECT LENGTH('ABCDE') FROM DUAL；
u LOWER(CHAR) 将字符串CHAR全部变为小写。
SELECT LOWER('ABCDE') FROM DUAL；
u LPAD(CHAR1，N，CHAR2) 用字符串CHAR2包括的字符左填CHAR1，使其长度为N
SELECT LPAD('ABCDEFG'，10'123') FROM DUAL；
结果： '123ABCDEFG'
u LTRIM(CHAR，SET) 从字符串CHAR的左边移去字符串SET中的字符，直到第一个不是SET中的字符为止。
SELECT ('CDEFG'，'CD') FROM DUAL；
结果： 'EFG'
u NLS_INITCAP(CHAR) 取字符CHAR的第一个字符大写，其余字符为小写。
SELECT NLS_INITCAP('ABCDE') FROM DUAL；
u NLS_LOWER(CHAR) 将字符串CHAR包括的字符全部小写。
SELECT NLS_LOWER('AAAA') FROM DUAL；
u NLS_UPPER(CHAR) 将字符串CHAR包括的字符全部大写。
SELECT NLS_UPPER('AAAA') FROM DUAL；
u REPLACE(CHAR1，CHAR2，CHAR3) 用字符串CHAR3代替每一个列值为CHAR2的列，其结果放在CHAR1中
SELECT REPLACE(EMP_NO，'123'，'456') FROM DUAL；
u RPAD(CHAR1，N，CHAR2) 用字符串CHAR2右填字符串CHAR1，使其长度为N
SELECT RPAD('234'，8，'0') FROM DUAL；
u RTRIM(CHAR，SET) 移去字符串CHAR右边的字符串SET中的字符，直到最后一个不是SET中的字符为止
SELECT RTRIM('ABCDE'，'DE') FROM DUAL；
u SUBSTR(CHAR，M，N) 得到字符串CHAR从M处开始的N个字符. 双字节字符，一个汉字为一个字符的
SELECT SUBSTR('ABCDE'，2，3) FROM DUAL；
u TRANSLATE(CHAR1，CHAR2，CHAR3) 将CHAR1中的CHAR2的部分用CHAR3代替。
SELECT TRANSLATE('ABDCDEFGdH','DE','MN') FROM DUAL; 结果： ABMCMNFGdH
SELECT translate('12a3.456','0123456789.',' ') FROM DUAL; 结果：a
u UPPER(CHAR) 将字符串CHAR全部为大写。
u TO_MULTI_BYTE(CHAR) 将半角转换为全角。
select 'a',to_multi_byte('a') from dual; 结果：a ａ
系统函数：
u DECODE函数
格式：DECODE(value, if1, then1, if2, then2...,else)
例子：
假设表ROYAL_DTABLE中有如下数据：
RD_INT_FIELD RD_STR_FIELD RM_INT_FIELD
--------------------------------------
1 royal 1
2 bill 2
3 joy 1
请观察如下SQL语句输出结果。
SELECT DECODE(RD_STR_FIELD, 'royal', 'royaltest', 'bill', 'billgates', RD_STR_FIELD) AS DC FROM ROYAL_DTABLE;
DC
---------
royaltest
billgates
joy
2、 语法
u having子句的用法
having子句对group by子句所确定的行组进行控制，having子句条件中只允许涉及常量,聚组函数或group by 子句中的列。
u 外部联接"+"的用法
外部联接"+"按其在"="的左边或右边分左联接和右联接.若不带"+"运算符的表中的一个行不直接匹配于带"+"预算符的表中的任何行,则前者的行与后者中的一个空行相匹配并被返回.若二者均不带’+’,则二者中无法匹配的均被返回.利用外部联接"+",可以替代效率十分低下的 not in 运算,大大提高运行速度.例如,下面这条命令执行起来很慢
用外联接提高表连接的查询速度
在作表连接(常用于视图)时，常使用以下方法来查询数据:
SELECT PAY_NO, PROJECT_NAME
FROM A
WHERE A.PAY_NO NOT IN (SELECT PAY_
NO FROM B WHERE VALUE >=120000);
---- 但是若表A有10000条记录，表B有10000条记录，则要用掉30分钟才能查完，主要因为NOT IN要进 行一条一条的比较，共需要10000*10000次比较后，才能得到结果。该用外联接后，可以缩短到1分左右的时间:
SELECT PAY_NO,PROJECT_NAME
FROM A,B
WHERE A.PAY_NO=B.PAY_NO(+)
AND B.PAY_NO IS NULL
AND B.VALUE >=12000;
3、 SQL语句
Select 检索相关：
u 设置日期语言
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
也可以这样
TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')
可查看
select * from nls_session_parameters
select * from V$NLS_PARAMETERS
u 处理不同时区
select to_char( NEW_TIME( sysdate, 'GMT','EST'), 'dd/mm/yyyy hh:mi:ss') ,sysdate from dual;
u 时间为null的用法
select id, active_date from table1
UNION
select 1, TO_DATE(null) from dual;
注意：要用TO_DATE(null)
u 求某天是星期几
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;
显示：星期一
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
显示：monday
u 查询两个日期间的天数
select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;
u 查询某天的数据
select * from table_name where trunc(日期字段)＝to_date('2003-05-02'，'yyyy-mm-dd')；
u 查询某时间段的数据
select * from table_name where 日期字段>=To_Date('2004-09-01','YYYY-MM-DD hh24:mi:ss') AND 日期字段<= To_Date('2004-09-30','YYYY-MM-DD hh24:mi:ss')；
u 查询某月中所有周五的具体日期
select to_char(t.d,'YY-MM-DD') from (
select trunc(sysdate, 'MM')+rownum-1 as d
from dba_objects
where rownum < 32) t
where to_char(t.d, 'MM') = to_char(sysdate, 'MM') --找出当前月份的周五的日期
and trim(to_char(t.d, 'Day')) = '星期五'
--------
03-05-02
03-05-09
03-05-16
03-05-23
03-05-30
如果把where to_char(t.d, 'MM') = to_char(sysdate, 'MM')改成sysdate-90，即为查找当前月份的前三个月中的每周五的日期。
u 日期时间间隔操作
当前时间减去7分钟的时间
select sysdate,sysdate - interval '7' MINUTE from dual
当前时间减去7小时的时间
select sysdate - interval '7' hour from dual
当前时间减去7天的时间
select sysdate - interval '7' day from dual
当前时间减去7月的时间
select sysdate,sysdate - interval '7' month from dual
当前时间减去7年的时间
select sysdate,sysdate - interval '7' year from dual
时间间隔乘以一个数字
select sysdate,sysdate - 8 *interval '2' hour from dual
u 日期到字符操作
select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual
select sysdate,to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual
select sysdate,to_char(sysdate,'yyyy-ddd hh:mi:ss') from dual
select sysdate,to_char(sysdate,'yyyy-mm iw-d hh:mi:ss') from dual
参考oracle的相关关文档(ORACLE901DOC/SERVER.901/A90125/SQL_ELEMENTS4.HTM#48515)
u 字符到日期操作
select to_date('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual
具体用法和上面的to_char差不多。
u 毫秒级的数据类型
--返回当前时间 年月日小时分秒毫秒
select to_char(current_timestamp(5),'DD-MON-YYYY HH24:MI:SSxFF') from dual;
--返回当前 时间的秒毫秒，可以指定秒后面的精度(最大=9)
select to_char(current_timestamp(9),'MI:SSxFF') from dual;
u 获取当前年龄、天数
SELECT TO_CHAR(SYSDATE,'YYYY')-TO_CHAR(birthday,'YYYY') Age FROM 表名；
SELECT TO_CHAR(sysdate,'yyyyMMdd')-TO_CHAR(birthday,'yyyyMMdd') Days FROM 表名；
u 查找出前N条记录
SELECT * FROM empLOYEE WHERE ROWNUM < n ORDER BY empno；
u 查找表中[N，M]条记录
取得某列中第N大的行
select column_name from
(select table_name.*,dense_rank() over (order by column desc) rank from table_name)
where rank = &N；
假如要返回前5条记录：
select * from tablename where rownum<6;(或是rownum <= 5 或是rownum != 6)
假如要返回第5-9条记录：
select * from tablename
where …
and rownum<10
minus
select * from tablename
where …
and rownum<5
order by name
选出结果后用name排序显示结果。(先选再排序)
注意：只能用以上符号(<、<=、!=)。
select * from tablename where rownum != 10;返回的是前９条记录。
不能用：>,>=,=,Between...and。由于rownum是一个总是从1开始的伪列，Oracle 认为这种条件 不成立，查不到记录.
另外，这个方法更快：
select * from (
select rownum r,a from yourtable
where rownum <= 20
order by name )
where r > 10
这样取出第11-20条记录!(先选再排序再选)
要先排序再选则须用select嵌套：内层排序外层选。
rownum是随着结果集生成的，一旦生成，就不会变化了；同时,生成的结果是依次递加的，没有1就永远不会有2!
rownum 是在 查询集合产生的过程中产生的伪列，并且如果where条件中存在 rownum 条件的话，则:
1： 假如 判定条件是常量，则：
只能 rownum = 1, <= 大于1 的自然数， = 大于1 的数是没有结果的，大于一个数也是没有结果的
即 当出现一个 rownum 不满足条件的时候则 查询结束 this is stop key!
2: 当判定值不是常量的时候
若条件是 = var , 则只有当 var 为1 的时候才满足条件，这个时候不存在 stop key ,必须进行 full scan ,对每个满足其他where条件的数据进行判定
选出一行后才能去选rownum=2的行……
u 查找一列中第N大的值
select * from (select t.*，dense_rank() over (order by sal) rank from employee) where rank = N；
u 查找、删除重复记录
法一: 用Group by语句此查找很快的
select count(num), max(name) from student --查找表中num列重复的，列出重复的记录数，并列出他的name属性
group by num
having count(num) >1 --按num分组后找出表中num列重复，即出现次数大于一次
delete from student(上面Select的)
这样的话就把所有重复的都删除了。-----慎重
法二:当表比较大(例如10万条以上)时,这个方法的效率之差令人无法忍受,需要另想办法:
---- 执行下面SQL语句后就可以显示所有DRAWING和DSNO相同且重复的记录
SELECT * FROM EM5_PIPE_PREFAB
WHERE ROWID!=(SELECT MAX(ROWID) FROM EM5_PIPE_PREFAB D --D相当于First,Second
WHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING AND
EM5_PIPE_PREFAB.DSNO=D.DSNO);
---- 执行下面SQL语句后就可以刪除所有DRAWING和DSNO相同且重复的记录
DELETE FROM EM5_PIPE_PREFAB
WHERE ROWID!=(SELECT MAX(ROWID) FROM EM5_PIPE_PREFAB D
WHERE EM5_PIPE_PREFAB.DRAWING=D.DRAWING AND
EM5_PIPE_PREFAB.DSNO=D.DSNO);
u 两个结果集互加的函数
SQL>SELECT * FROM BSEMPMS_OLD INTERSECT SELECT * FROM BSEMPMS_NEW；
SQL>SELECT * FROM BSEMPMS_OLD UNION SELECT * FROM BSEMPMS_NEW
SQL>SELECT * FROM BSEMPMS_OLD UNION ALL SELECT * FROM BSEMPMS_NEW；
u 两个结果集互减的函数
SQL>SELECT * FROM BSEMPMS_OLD MINUS SELECT * FROM BSEMPMS_NEW；
u 把select出来的结果导到一个文本文件中
SQL>SPOOL C：\ABCD.TXT；
SQL>select * from table；
SQL >spool off；
u 中文排序
Oracle9i之前，中文是按照二进制编码进行排序的
在oracle9i中新增了按照拼音、部首、笔画排序功能。设置NLS_SORT值
SCHINESE_RADICAL_M 按照部首（第一顺序）、笔划（第二顺序）排序
SCHINESE_STROKE_M 按照笔划（第一顺序）、部首（第二顺序）排序
SCHINESE_PINYIN_M 按照拼音排序
u 自动生成序号
select rownum,COL from table;
u 內連接INNER JOIN
Select a.* from bsempms a,bsdptms b where a.dpt_no=b.dpt_no;
u 外連接
Select a.* from bsempms a,bsdptms b where a.dpt_no=b.dpt_no(+);
Select a.* from bsempms a,bsdptms b wherea.dpt_no(+)=b.dpt_no;
Update更改相关：
u 更改日期、时间字段
² UPDATE Table SET MODIFY_DAT =TO_DATE('2002-05-23','YYYY-MM-DD hh24:mi:ss') WHERE ID=’0001’;
Insert 插入相关：
u 将日期、时间插入date型字段中
² INSERT INTO ROYAL_MTABLE (RM_INT_FIELD, RM_STR_FIELD, RM_DATE_FIELD) VALUES (9, 'Y', TO_DATE('2002-05-23', 'YYYY-MM-DD'));
² INSERT INTO ROYAL_MTABLE (RM_INT_FIELD, RM_STR_FIELD, RM_DATE_FIELD) VALUES (10, 'Y', TO_DATE('2002-10-10 8:23:33', 'YYYY-MM-DD HH:MI:SS'));
Delete 删除相关：
u 快速清空一个大表
SQL>truncate table table_name；
临时表用法
u 语法
在Oracle中，可以创建以下两种临时表：
² 1) 会话特有的临时表
CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification> )
ON COMMIT PRESERVE ROWS；
² 2) 事务特有的临时表
CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification> )
ON COMMIT DELETE ROWS；
CREATE GLOBAL TEMPORARY TABLE MyTempTable
所建的临时表虽然是存在的，但是如果insert 一条记录然后用别的连接登上去select，记录是空的。
--ON COMMIT DELETE ROWS 说明临时表是事务指定，每次提交后ORACLE将截断表（删除全部行）
--ON COMMIT PRESERVE ROWS 说明临时表是会话指定，当中断会话时ORACLE将截断表。
u 动态创建
create or replace procedure pro_temp(v_col1 varchar2,v_col2 varchar2) as v_num number;
begin
select count(*) into v_num from user_tables where table_name='T_TEMP';
² --create temporary table
if v_num<1 then
execute immediate 'CREATE GLOBAL TEMPORARY TABLE T_TEMP (
COL1 VARCHAR2(10),
COL2 VARCHAR2(10)
) ON COMMIT delete ROWS';
end if;
² --insert data
execute immediate 'insert into t_temp values('''||v_col1||''','''||v_col2||''')';
execute immediate 'select col1 from t_temp' into v_num;
dbms_output.put_line(v_num);
execute immediate 'delete from t_temp';
commit;
execute immediate 'drop table t_temp';
end pro_temp;
² 测试:
15:23:54 SQL> set serveroutput on
15:24:01 SQL> exec pro_temp('11','22');
11
PL/SQL 过程已成功完成。
已用时间: 00: 00: 00.79
15:24:08 SQL> desc t_temp;
ERROR：
ORA-04043: 对象 t_temp 不存在
u 特性和性能(与普通表和视图的比较)
² 临时表只在当前连接内有效
² 临时表不建立索引,所以如果数据量比较大或进行多次查询时,不推荐使用
² 数据处理比较复杂的时候时表快,反之视图快点
² 在仅仅查询数据的时候建议用游标: open cursor for 'sql clause';
4、 SQL语句优化
操作符优化
u IN 操作符
用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。
但是用IN的SQL性能总是比较低的，从ORACLE执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别：
ORACLE试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。
[推荐方案] ：在业务密集的SQL当中尽量不采用IN操作符。
u NOT IN操作符
此操作是强列推荐不使用的，因为它不能应用表的索引。
[推荐方案] ：用NOT EXISTS 或（外连接+判断为空）方案代替
u <> 操作符（不等于）
不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。
[推荐方案] ：用其它相同功能的操作运算代替，如
a<>0 改为 a>0 or a<0
a<>’’ 改为 a>’’
u IS NULL 或IS NOT NULL操作（判断字段是否为空）
判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。
[推荐方案] ：用其它相同功能的操作运算代替，如
a is not null 改为 a>0 或a>’’等。
不允许字段为空，而用一个缺省值代替空值，如业扩申请中状态字段不允许为空，缺省为申请。
建立位图索引（有分区的表不能建，位图索引比较难控制，如字段值太多索引会使性能下降，多人更新操作会增加数据块锁的现象）
> 及 < 操作符（大于或小于操作符）
大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。
u LIKE操作符
LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE ‘X5400%’则会引用范围索引。一个实际例子：用YW_YHJBQK表中营业编号后面的户标识号可来查询营业编号 YY_BH LIKE ‘%5400%’ 这个条件会产生全表扫描，如果改成YY_BH LIKE ’X5400%’ OR YY_BH LIKE ’B5400%’ 则会利用YY_BH的索引进行两个范围的查询，性能肯定大大提高。
u UNION操作符
UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：
select * from gc_dfys
union
select * from ls_jg_dfys
这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。
推荐方案：采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。
select * from gc_dfys
union all
select * from ls_jg_dfys
SQL书写的影响
u 同一功能同一性能不同写法SQL的影响
如一个SQL在A程序员写的为
Select * from zl_yhjbqk
B程序员写的为
Select * from dlyx.zl_yhjbqk（带表所有者的前缀）
C程序员写的为
Select * from DLYX.ZLYHJBQK（大写表名）
D程序员写的为
Select * from DLYX.ZLYHJBQK（中间多了空格）
以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的，但是从ORACLE共享内存SGA的原理，可以得出ORACLE对每个SQL 都会对其进行一次分析，并且占用共享内存，如果将SQL的字符串及格式写得完全相同则ORACLE只会分析一次，共享内存也只会留下一次的分析结果，这不仅可以减少分析SQL的时间，而且可以减少共享内存重复的信息，ORACLE也可以准确统计SQL的执行频率。
u WHERE后面的条件顺序影响
WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响，如
Select * from zl_yhjbqk where dy_dj = '1KV以下' and xh_bz=1
Select * from zl_yhjbqk where xh_bz=1 and dy_dj = '1KV以下'
以上两个SQL中dy_dj（电压等级）及xh_bz（销户标志）两个字段都没进行索引，所以执行的时候都是全表扫描，第一条SQL的dy_dj = '1KV以下'条件在记录集内比率为99%，而xh_bz=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行dy_dj及xh_bz的比较，而在进行第二条SQL的时候0.5%条记录都进行dy_dj及xh_bz的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。
u 查询表顺序的影响
在FROM后面的表中的列表顺序会对SQL执行性能影响，在没有索引及ORACLE没有对表进行统计分析的情况下ORACLE会按表出现的顺序进行链接，由此因为表的顺序不对会产生十分耗服务器资源的数据交叉。（注：如果对表进行了统计分析，ORACLE会自动先进小表的链接，再进行大表的链接）
u SQL语句索引的利用
对操作符的优化（见上节）
对条件字段的一些优化
采用函数处理的字段不能利用索引，如：
substr(hbs_bh,1,4)=’5400’，优化处理：hbs_bh like ‘5400%’
trunc(sk_rq)=trunc(sysdate)， 优化处理：
sk_rq>=trunc(sysdate) and sk_rq<trunc(sysdate+1)
进行了显式或隐式的运算的字段不能进行索引，如：
ss_df+20>50，优化处理：ss_df>30
‘X’||hbs_bh>’X5400021452’，优化处理：hbs_bh>’5400021542’
sk_rq+5=sysdate，优化处理：sk_rq=sysdate-5
hbs_bh=5401002554，优化处理：hbs_bh=’ 5401002554’，注：此条件对hbs_bh 进行隐式的to_number转换，因为hbs_bh字段是字符型。
条件内包括了多个本表的字段运算时不能进行索引，如：
ys_df>cx_df，无法进行优化
qc_bh||kh_bh=’5400250000’，优化处理：qc_bh=’5400’and kh_bh=’250000’
u 应用ORACLE的HINT（提示）处理
提示处理是在ORACLE产生的SQL分析执行路径不满意的情况下要用到的。它可以对SQL进行以下方面的提示
² 目标方面的提示：
COST（按成本优化）
RULE（按规则优化）
CHOOSE（缺省）（ORACLE自动选择成本或规则进行优化）
ALL_ROWS（所有的行尽快返回）
FIRST_ROWS（第一行数据尽快返回）
² 执行方法的提示：
USE_NL（使用NESTED LOOPS方式联合）
USE_MERGE（使用MERGE JOIN方式联合）
USE_HASH（使用HASH JOIN方式联合）
² 索引提示：
INDEX（TABLE INDEX）（使用提示的表索引进行查询）
² 其它高级提示（如并行处理等等）
ORACLE的提示功能是比较强的功能，也是比较复杂的应用，并且提示只是给ORACLE执行的一个建议，有时如果出于成本方面的考虑ORACLE也可能不会按提示进行。根据实践应用，一般不建议开发人员应用ORACLE提示，因为各个数据库及服务器性能情况不一样，很可能一个地方性能提升了，但另一个地方却下降了，ORACLE在SQL执行分析方面已经比较成熟，如果分析执行的路径不对首先应在数据库结构（主要是索引）、服务器当前性能（共享内存、磁盘文件碎片）、数据库对象（表、索引）统计信息是否正确这几方面分析。
精妙SQL语句
u 复制表(只复制结构,源表名：a 新表名：b)
SQL: select * into b from a where 1<>1
u 拷贝表(拷贝数据,源表名：a 目标表名：b)
SQL: insert into b(a, b, c) select d,e,f from b;
u 显示文章、提交人和最后回复时间
SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
u 外连接查询(表名1：a 表名2：b)
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
u 日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
u 两张关联表，删除主表中已经在副表中没有的信息
SQL: delete from info where not exists ( select * from infobz where info.infid=infobz.infid )
u --
SQL: SELECT A.NUM, A.NAME, B.UPD_DATE, B.PREV_UPD_DATE FROM TABLE1,(SELECT X.NUM, X.UPD_DATE, Y.UPD_DATE PREV_UPD_DATE FROM (SELECT NUM, UPD_DATE, INBOUND_QTY, STOCK_ONHAND FROM TABLE2 WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(SYSDATE, 'YYYY/MM')) X, (SELECT NUM, UPD_DATE, STOCK_ONHAND FROM TABLE2 WHERE TO_CHAR(UPD_DATE,'YYYY/MM') = TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYY/MM') || '/01','YYYY/MM/DD') - 1, 'YYYY/MM') ) Y, WHERE X.NUM = Y.NUM （+）AND X.INBOUND_QTY + NVL(Y.STOCK_ONHAND,0) <> X.STOCK_ONHAND ) B WHERE A.NUM = B.NUM
u --
SQL: select * from studentinfo where not exists(select * from student where studentinfo.id=student.id) and 系名称='"&strdepartmentname&"' and 专业名称='"&strprofessionname&"' order by 性别,生源地,高考总成绩
u 从数据库中去一年的各单位电话费统计(电话费定额贺电化肥清单两个表来源）
SQL:
SELECT a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy') AS telyear, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '01', a.factration)) AS JAN, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '02', a.factration)) AS FRI, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '03', a.factration)) AS MAR, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '04', a.factration)) AS APR, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '05', a.factration)) AS MAY, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '06', a.factration)) AS JUE,SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '07', a.factration)) AS JUL, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '08', a.factration)) AS AGU, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '09', a.factration)) AS SEP, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '10', a.factration)) AS OCT, SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '11', a.factration)) AS NOV,SUM(decode(TO_CHAR(a.telfeedate, 'mm'), '12', a.factration)) AS DEC FROM (SELECT a.userper, a.tel, a.standfee, b.telfeedate, b.factration FROM TELFEESTAND a, TELFEE b WHERE a.tel = b.telfax) a GROUP BY a.userper, a.tel, a.standfee, TO_CHAR(a.telfeedate, 'yyyy')
u 得到表中最小的未使用的ID号
SQL:SELECT MIN((CASE WHEN EXISTS(SELECT * FROM Handle b WHERE b.HandleID = 1) THEN HandleID + 1 ELSE 1 END)) as HandleID FROM Handle WHERE NOT HandleID IN (SELECT a.HandleID - 1 FROM Handle a)
5、 数据库编码
自定义函数
u 字串转为数字
|
--字串转为数字 function ToNumber(strNumber varchar2) return number is begin return (to_number(strNumber)); exception when others then return (0); end ToNumber; |
u 获取年龄
|
--获取年龄 function GetAge(dtBirthDate date) return varchar2 is intYear int; strAge varchar2(10); begin intYear:=Floor((SysDate-dtBirthDate)/365); strAge:=to_char(intYear) || '岁' ; return(strAge); end GetAge; |
存储过程
u 计算程序运行的时间(ms)
declare
type rc is ref cursor;
l_rc rc;
l_dummy all_objects.object_name%type;
l_start number default dbms_utility.get_time;
begin
for I in 1 .. 1000
loop
open l_rc for
'select object_name from all_objects '||
'where object_id = ' || i;
fetch l_rc into l_dummy;
close l_rc;
end loop;
dbms_output.put_line
( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
' seconds...' );
end;
触发器
u 在数据库触发器中使用列的新值与旧值
在数据库触发器中几乎总是要使用触发器基表的列值，如果某条语句需要某列修改前的值，使用:OLD就可以了，使用某列修改后的新值，用:NEW就可以了。如:OLD.DEPT_NO,:NEW.DEPT_NO。
其他
u 在Oracle中建一个编号会自动增加的字段,以利于查询
1、建立序列：
CREATE SEQUENCE checkup_no_seq
NOCYCLE
MAXVALUE 9999999999
START WITH 2;
2、建立触发器：
CREATE OR REPLACE TRIGGER set_checkup_no
BEFORE INSERT ON checkup_history
FOR EACH ROW
DECLARE
next_checkup_no NUMBER;
BEGIN
--Get the next checkup number from the sequence
SELECT checkup_no_seq.NEXTVAL
INTO next_checkup_no
FROM dual;
--use the sequence number as the primary key
--for the record being inserted
:new.checkup_no := next_checkup_no;
END;
6、 数据库维护
SQL查看数据库：
u 测试SQL语句执行所用的时间
SQL>set timing on；
SQL>select * from tablename；
u 找数据库表的主键字段的名称
SQL>SELECT * FROM user_constraints WHERE CONSTRAINT_TYPE='P' and table_name='TABLE_NAME'；
u 執行腳本SQL文件
SQL>@$PATH/filename.sql;
u 查詢數据庫有多少表
SQL>select * from all_tables;
u 查看表空间的名称及大小
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;
u 查看表空间物理文件的名称及大小
select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;
u 查看回滚段名称及大小
select segment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
max_extents, v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name ;
u 查看控制文件
select name from v$controlfile;
u 查看日志文件
select member from v$logfile;
u 查看表空间的使用情况
select sum(bytes)/(1024*1024) as free_space,tablespace_name
from dba_free_space
group by tablespace_name;
SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;
u 查看数据库库对象
select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;
u 查看数据库的版本
Select version FROM Product_component_version
Where SUBSTR(PRODUCT,1,6)='Oracle';
u 查看数据库的创建日期和归档方式
Select Created, Log_Mode, Log_Mode From V$Database;
u 查看对象的依赖关系(比如视图与表的引用)
查看视图：dba_dependencies 记录了相关的依赖关系
查东西不知道要查看哪个视图时，可以在DBA_Objects里看，
select object_name from dba_objects where object_name like '%ROLE%'(假如查看ROLE相关)
然后DESC一下就大体上知道了。
u 删除表空间
DROP TABLESPACE TableSpaceName [INCLUDING CONTENTS [AND DATAFILES]]
u 删除用户
DROP USER User_Name CASCADE
u 删除表的注意事项
在删除一个表中的全部数据时，须使用TRUNCATE TABLE 表名;因为用DROP TABLE，DELETE * FROM 表名时，TABLESPACE表空间该表的占用空间并未释放，反复几次DROP，DELETE操作后，该TABLESPACE上百兆的空间就被耗光了。
set transaction命令的用法
在执行大事务时,有时oracle会报出如下的错误:
ORA-01555:snapshot too old (rollback segment too small)
这说明oracle给此事务随机分配的回滚段太小了,这时可以为它指定一个足够大的回滚段,以确保这个事务的成功执行.例如
set transaction use rollback segment roll_abc;
delete from table_name where ...
commit;
回滚段roll_abc被指定给这个delete事务,commit命令则在事务结束之后取消了回滚段的指定.
数据库重建应注意的问题
在利用import进行数据库重建过程中,有些视图可能会带来问题,因为结构输入的顺序可能造成视图的输入先于它低层次表的输入,这样建立视图就会失败.要解决这一问题,可采取分两步走的方法:首先输入结构,然后输入数据.命令举例如下 (uesrname:jfcl,password:hfjf,host sting:ora1,数据文件:expdata.dmp):
imp jfcl/hfjf@ora1 file=empdata.dmp rows=N
imp jfcl/hfjf@ora1 file=empdata.dmp full=Y buffer=64000
commit=Y ignore=Y
第一条命令输入所有数据库结构,但无记录.第二次输入结构和数据,64000字节提交一次.ignore=Y选项保证第二次输入既使对象存在的情况下也能成功.
select a.empno from emp a where a.empno not in (select empno from emp1 where job=’SALE’);
倘若利用外部联接,改写命令如下:
select a.empno from emp a ,emp1 b
where a.empno=b.empno(+)
and b.empno is null
and b.job=’SALE’;
可以发现,运行速度明显提高.
快速编译所有视图
当在把数据库倒入到新的服务器上后(数据库重建)，需要将视图重新编译一遍，因为该表空间视图到其它表空间的表的连接会出现问题，可以利用PL/SQL的语言特性，快速编译。
SQL >SPOOL ON.SQL
SQL >SELECT ‘ALTER VIEW ‘||TNAME||’
COMPILE;’ FROM TAB;
SQL >SPOOL OFF
然后执行ON.SQL即可。
SQL >@ON.SQL
当然，授权和创建同义词也可以快速进行，如：
SQL >SELECT ‘GRANT SELECT ON ’
||TNAME||’ TO USERNAME;’ FROM TAB;
SQL >SELECT ‘CREATE SYNONYM
‘||TNAME||’ FOR USERNAME.’||TNAME||’;’ FROM TAB;
读写文本型操作系统文件
---- 在PL/SQL 3.3以上的版本中，UTL_FILE包允许用户通过PL/SQL读写操作系统文件。如下：
DECALRE
FILE_HANDLE UTL_FILE.FILE_TYPE;
BEGIN
FILE_HANDLE:=UTL_FILE.FOPEN(
‘C:\’,’TEST.TXT’,’A’);
UTL_FILE.PUT_LINE(FILE_HANDLE,’
HELLO,IT’S A TEST TXT FILE’);
UTL_FILE.FCLOSE(FILE_HANDLE);
END;
数据库文件的移动方法
当想将数据库文件移动到另外一个目录下时，可以用ALTER DATABASE命令来移动(比ALTER TABLESPACE适用性强)：
² 1. 使用SERVER MANAGER关闭实例.
SVRMGR > connect internal;
SVRMGR > shutdown;
SVRMGR >exit;
² 2. 使用操作系统命令来移动数据库文件位置(假设这里操作系统为SOLARIS 2.6). 在UNIX中用 mv命令可以把文件移动到新的位置，
#mv /ora13/orarun/document.dbf /ora12/orarun
² 3. 装载数据库并用alter database命令来改变数据库中的文件名.
SVRMGR > connect internal;
SVRMGR > startup mountRUN73;
SVRMGR > alter database rename file
> ‘/ ora13/orarun/document.dbf’
> ‘/ ora12/orarun/document.dbf’;
² 4. 启动实例.
SVRMGR > alter database open;
68
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
