Oracle相关技术

1. Distinct消除重复行数据: SELECT DISTINCT DEPTNO FROM EMP
2. NULL操作
   如果某条记录中有缺少的数据值，就是空值（NULL值）。空值不等于0或者空格，空值是指未赋值、未知或不可用的值。
   任何数据类型的列都可以包括NULL值，除非该列被定义为非空或者主键。
   在查询条件中NULL值用IS NULL作条件，非NULL值用IS NOT NULL做条件： SELECT ENAME,JOB,SAL,COMM FROM EMP WHERE SAL<2000 AND COMM IS NULL;
3. IN 操作在Where子句中可以使用IN操作符来查询其列值在指定的列表中的行。对应IN操作的还有NOT IN，用法一样，结果相反。： SELECT ENAME,JOB,SAL FROM EMP   WHERE job IN ('SALESMAN', 'PRESIDENT', 'ANALYST');

4. BETWEEN…AND

   在WHERE子句中，可以使用BETWEEN操作符来查询列值包含在指定区间内的行。BWTWEEN操作所指定的范围也包括边界。SELECT ename,job,sal FROM EMP WHERE sal BETWEEN 1000 AND 2000

oracle 集合运算

• 集合运算就是将两个或者多个结果集组合成为一个结果集。集合运算包括：

INTERSECT(交集)，返回两个查询共有的记录。
UNION ALL(并集)，返回各个查询的所有记录，包括重复记录。
UNION(并集)，返回各个查询的所有记录，不包括重复记录。
MINUS(补集)，返回第一个查询检索出的记录减去第二个查询检索出的记录之后剩余的记录。

当使用集合操作的时候，要注意：查询所返回的列数以及列的类型必须匹配，列名可以不同。

例子:查询出dept表中哪个部门下没有员工。只需求出dept表中的部门号和emp表中的部门号的补集即可

SELECT DEPTNO FROM DEPT MINUS SELECT DEPTNO FROM EMP

• 把一个结果集插入到另一张结构相同的表中 ，使用union把若干条记录一次性插入到一张表中

INSERT   INTO  DEPT     SELECT  50, '公关部' , '台湾'   FROM  DUAL     UNION       SELECT  60, '研发部' , '西安'   FROM  DUAL     UNION      SELECT  70, '培训部' , '西安'   FROM  DUAL  

• like模糊查询

一般like模糊查询 如下：

select b.* from b_city_info b where b.c_name like '%西藏%';---用时:0.062 , 此查询将会进行全表扫描

修改为如下的语句:

select b.* from b_city_info b where b.c_name like '西藏%';--用时：0.009 

查询执行计划将会变成 （INDEX RANGE SCAN），成功的利用了name字段的索引

使用rowId实现分页

--分页第一步 获取数据物理地址

select t.rowid rid, t.c_id from b_city_info t order by t.c_id desc;

--分页第二步 取得最大页数

select rownum rn, rid

  from (select t.rowid rid, t.c_id from b_city_info t order by t.c_id desc)

 where rownum <= 10;

 

 --分页第三步 取得最小页数

 select rn,rid

   from (select rownum rn, rid

           from (select t.rowid rid, t.c_id from b_city_info t order by t.c_id desc)

          where rownum <= 10)

  where rn > 5;

 

  --分页第四步 再根据物理地址，查询出具体数据

  select t1.*,t1.rowid

    from b_city_info t1

   where t1.rowid in

         (select dt.rid

            from (select rownum rn, rid

                    from (select t.rowid rid, t.c_id from b_city_info t order by t.c_id desc)

                   where rownum <= 10000)dt

           where rn > 5000);

Oracle日期和字符串、数字的格式化

1. select SYSDATE from dual t;--系统日期
2. select TO_CHAR(SYSDATE,'YYYY/MM/DD HH24:MI:SS') from dual t;--格式化日期
3. select TO_DATE('20140630 18:28:20','YYYY/MM/DD HH24:MI:SS') from dual t;--格式化日期
4. select sysdate,sysdate - interval '7' MINUTE from dual;--当前时间减去7分钟的时间
5. select sysdate - interval '7' hour from dual;--当前时间减去7小时的时间
6. select sysdate - interval '7' day from dual;--当前时间减去7天的时间 
7. select sysdate,sysdate - interval '7' month from dual;--当前时间减去7月的时间
8. select sysdate,sysdate - interval '7' year from dual;--当前时间减去7年的时间
9. select sysdate,sysdate - 8*interval '7' hour from dual;--时间间隔乘以一个数字
10. select to_number('222.2') from dual t;--输出222.2
11. select to_char(10000000000,'$999,999,999,99') from dual;--按格式输出
12. select to_char(1000000,'L99,999,99') from dual;--输出 RMB10,000,00
13. select trunc(to_number('1000000.123'),3) from dual;--保留三位有效数字

• Dual虚拟表,只有一列

当查询时间或者日期这些对象的时候就需要使用这个表

 select sysdate from dual

查询当前连接用户

select user from dual;

• 把字符串按照分割符分解成不同的部分

select regexp_substr('A,B,C,D,E', '[^,]+', 1, level, 'i') as str

  from dual

connect by level <=

           length('A,B,C,D,E') - length(regexp_replace('A,B,C,D,E', ',', '')) + 1;

• 将一张表的数据导入到另一张表中

前提:两表结构一样

insert into 表A select * from 表B

如果两表的数据结构不一样

INSERT INTO 表1(字段1,字段2,字段n)
select 表2.字段1,表2.字段2,表2.字段n from 表2

• oracle 表数据恢复

第一中方式：创建一个备份表，存放三小时前的数据

create table tabletest_2014bak nologging as 

select * from tabletest as of timestamp sysdate-21/24 where name != 'boat';

第二种方式：回复表tabletest中的数据到某一个时间点，这个时间点一定要前于当前时间

alter table tabletest enable row movement;

Flashback table tabletest to timestamp to_timestamp('2014-12-30 17:00:00','yyyy-mm-dd hh24:mi:ss');

• Oracle修改原始表中字段的数据类型

方法一：

// 创建一样的临时表

create table T_BUSINESS_MODEL_TEMP  as select * from  T_BUSINESS_MODEL where 1=2 ;

// 对临时表改数据类型

alter table T_BUSINESS_MODEL_TEMP modify MODEL_MONEY NUMBER(10,2);

// 将原表数据赋值到最新的表中

insert into T_BUSINESS_MODEL_TEMP select * from T_BUSINESS_MODEL;

// 删掉原来的表

drop table T_BUSINESS_MODEL;

// 将临时表重新命名为原表

alter table T_BUSINESS_MODEL_TEMP rename to T_BUSINESS_MODEL;

// 添加原表的约束 （主键，外键，索引）

alter table T_BUSINESS_MODEL

  add constraint PK_T_BUSINESS_MODEL primary key (ID);

方法二：

-- 字段重命名

alter table PROJECT rename column NAME to NAME1;

-----  把字段名称改成原来的名称

alter table PROJECT add  NAME VARCHAR2(300);

--- 赋值

update PROJECT np

set np.name=np.name1;

commit;

--- 删掉原来重命名的字段

alter table PROJECT drop column  NAME1;

• Oracle 递归查询  START WITH....CONNECT BY PRIOR......

  SELECT * FROM B_CITY_INFO   START WITH c_id=540000

    CONNECT BY PRIOR c_id=p_c_id ;

• Oracle调用存储过程和删除
  

调用：

 set  serveroutput  on ;  
 exec  存储过程名;

删除：

drop procedure 存储过程名