oracle小知识点讲解,Oracle 常用小知识点汇集

使用sqlldr导入数据：1.control控制文件的写法：load datainfile

使用sqlldr导入数据：

1.control控制文件的写法：

load data

infile 'E:\netbu.csv'

insert

into table temp_zhuren_need

FIELDS TERMINATED BY ','

TRAILING NULLCOLS --表的字段没有对应的值时允许为空(空格，空白，null都为空)

(BELONG_CODE,RUN_NAME,PHONE_NO)

2.命令行语句：

sqlldr name/pass@tnsname control='E:\ctl.txt' log='E:\log.txt'

注释：若要中途断了，可以查看日志，入库了多少行，然后使用skip=入库了的行数，就可以继续追加了。但是注意把insert改为append。

Oracle CASE的两种用法：

例子1：

select product_id,product_type_id,

case product_type_id

when 1 then 'Book'

when 2 then 'Video'

else 'Magazine'

end ----注意end不能少

from products

例子2：

select product_id,product_type_id,

case

when product_type_id=1 then 'Book'

when product_type_id=2 then 'Video'

else 'Magazine'

end ----注意end不能少

from products

注意：when这里的条件可以是多个，用and，or连接

在看一个复合的例子：

select t.phone_no1204,

nvl(t.phone_no1201,t.phone_no1204) as "phone_no1201",

nvl(t.phone_no1110,t.phone_no1204) as "phone_no1110",

nvl(t.phone_no1107,t.phone_no1204) as "phone_no1107",

case

when t.phone_no1204=nvl(t.phone_no1201,t.phone_no1204)

and t.phone_no1204=nvl(t.phone_no1110,t.phone_no1204)

and t.phone_no1204=nvl(t.phone_no1107,t.phone_no1204)

then '0'

else '1'

end as phone_no1101

from mark_518_t9 t

instr()函数，特殊用法 --instr()函数代替like，IN

instr()返回值是目标字符(串)在母字符里第一次出现的位置，故而是整数。

例如：

instr("efabcdefg","e")的结果当然是1

instr(2，"efabcdefg","e")的结果就是7

instr(5，"efabcdefg","a")的结果就是0

instr("efabcdefg","k")的结果也是0

instr是内置函数，他是会走索引的，性能好。

like不一定。like '%iii%’是不走索引的，like ‘ik%’走索引。

例如：

SELECT code, name, dept, occupation FROM staff WHERE instr(code, '001') > 0;

等同于===>

SELECT code, name, dept, occupation FROM staff WHERE code LIKE '%001%' ;

代替in：

SELECT code , name , dept, occupation FROM staff WHERE code IN ('A10001','A10002');

等同于===>

SELECT code , name , dept, occupation FROM staff WHERE instr('A10001,A10002',code)>0

组合索引

在ORACLE 中可以创建组合索引，使用表中多个字段的组合作为索引的键值。当我们进行查询时可以使用”where col1 = ?”，也可以使用”where col1 = ? and col2 = ?”，这样的限制条件都会使用索引，但是”where col2 = ?”查询就不会使用该索引。所以限制条件中包含先导列时，该限制条件才会使用该组合索引

创建组合索引的方法：

create index index_name on table_name (column_name1，column_name2)

minus与intersect的使用

minus是A中存在，B中不存在的部分

intersect是AB的交际

eg：

select job from accounts

intersect/minus

select job from sales;

ORACLE set命令

SQL>set colsep' '; //-域输出分隔符

SQL>set echo off; //显示start启动的脚本中的每个sql命令，缺省为on

SQL> set echo on //设置运行命令是是否显示语句

SQL> set feedback on; //设置显示“已选择XX行”

SQL>set feedback off; //回显本次sql命令处理的记录条数，缺省为on

SQL>set heading off; //输出域标题，缺省为on

SQL>set pagesize 0; //输出每页行数，缺省为24,为了避免分页，可设定为0。

SQL>set linesize 80; //输出一行字符个数，缺省为80

SQL>set numwidth 12; //输出number类型域长度，缺省为10

SQL>set termout off; //显示脚本中的命令的执行结果，，缺省为on

SQL>set trimout on; //去除标准输出每行的拖尾空格，缺省为off

SQL>set trimspool on; //去除重定向(spool)输出每行的拖尾空格，缺省为off

SQL>set serveroutput on; //设置允许显示输出类似dbms_output

SQL> set timing on; //设置显示“已用时间：XXXX”

SQL> set autotrace on-; //设置允许对执行的sql进行分析

set verify off //可以关闭和打开提示确认信息old 1和new 1的显示.

查看/恢复回收站的表

select * from recyclebin t where t.original_name like 'MB_G%';

或者 show recyclebin

flashback table MB_G_2009 to before drop;

筛选oralce表中某列是汉字还是字符的方法：

SQL> select * from aa;

A B

---------- ----------

1 aaa

1 11111

1 书

方法1：

原理：asciistr()函数对非ASCII代码，会转换成二进制，且前面加\ ;所以asciistr(acolumn) 如果是汉字;转换出来的会有 \

SQL> select aa.* from aa where asciistr(b) like '\%' ;

A B

---------- ----------

1 书

方法2：

原理：通过截取第一个字符的长度来判断。

SQL> select aa.* from aa where lengthb(substr(b,1,1))=2 ;

A B

---------- ----------

1 书

扩展的GROUP BY

所谓的扩展的GROUP BY就是使用了分析函数rollup() 和 cube()。

rollup()：使分组结果中包含小计及总计信息，并可以传入多列字段

cube()：对做为参数传入的每一列都进行小计，多列的时候能显示优势。

例：

select id,sum(mount) from tt group by rollup(id);

ID SUM(MOUNT)

---------- ----------

1 60

2 150

3 240

4 210

5 440

1100

select id,sum(mount) from tt group by cube(id);

ID SUM(MOUNT)

---------- ----------

1100

1 60

2 150

3 240

4 210

5 440

或者,用nulls 显示指定空值的首尾位置:

select id,sum(mount) from tt group by cube(id) order by id nulls last;

本文原创发布php中文网，转载请注明出处，感谢您的尊重！