My Oracle Note

一：BASIC

数据类型：
number(5)：最多99999
number(6,2)：小数两位，四舍五入，整数最多四位
varchar2(8)：八个字节
date：默认格式为：’27-4月-15’
CLOB：大文本对象，4G
BLOB：视频等，4G

1.解决null:使用NVL()函数，NVL(a,b)：如果a是NULL，用b替代
2.oracle中单引号表示字符串类型或者是日期类型
3.字符串连接符号||
4.sysdate，系统当前时间，默认oracle只显示日期不显示时间，格式：26-4月-15
5.导出：spool e:/oracle-day01.sql
6.读到实例@ e:/crm.sql
7.转义字符：字符回归本意【like '%\_%' escape '\'】
8.日期函数：
months_between(‘31-12月-15’,sysdate)
add_months(sysdate,1)
next_day(next_day(sysdate,’星期三’),’星期日’)
last_day(add_months(sysdate,1))
三大类型隐式转换：
（1）varchar2变长/char定长–>number，例如：’123’->123
（2）varchar2/char–>date，例如：’25-4月-15’->’25-4月-15’
（3）number—->varchar2/char，例如：123->’123’
（4）date——>varchar2/char，例如：’25-4月-15’->’25-4月-15’
重要函数：to_date，to_char
9.条件语句：

case 字段 
     when 条件 then 表达式1
     when 条件 then 表达式2
     else 表达式n
end

二.基本CRUD部分：

oracle分页：
显示emp表中3-8条记录（方式一：使用集合减运算）
select rownum “伪列”,emp.* from emp where rownum<=8
minus
select rownum,emp.* from emp where rownum<=2;

显示emp表中3-8条记录（方式二：使用子查询，在from子句中使用，重点）
select xx.*
from (select rownum ids,emp.* from emp where rownum<=8) xx
where ids>=2;
注意：在子查询中的别名，不可加”“引号

显示emp表中5-9条记录
select yy.*
from (select rownum ids,emp.* from emp where rownum<=9) yy
where ids>=5;

三.oracle高级应用：存储过程，数据库优化等

1.PLSQL：

类型：

[declare]
      变量声明;
      变量声明;
     begin
          DML/TCL操作;
      DML/TCL操作;
     [exception]
          例外处理;
      例外处理;
 end;
 /
--比如：
declare
    pename emp.ename%type;
    psal   emp.sal%type;
begin  
    select ename,sal into pename,psal from emp where empno = 7369;
    dbms_output.put_line('7369号员工的姓名是'||pename||',薪水是'||psal);    
end;
/

declare
    emp_record emp%rowtype;
begin
    select * into emp_record from emp where empno = 7788;
    dbms_output.put_line('7788号员工的姓名是'||emp_record.ename||',薪水是'||emp_record.sal);
end;
/
--变量类型与字段类型相同，使用%type
--变量与表完全相同，使用%rowtype

判断：

if-else-end i：

declare
    pday varchar2(10);
begin
    select to_char(sysdate,'day') into pday from dual;
    dbms_output.put_line('今天是'||pday);
    if pday in ('星期六','星期日') then
    dbms_output.put_line('休息日');
    else
    dbms_output.put_line('工作日');
    end if;
end;
/


declare
    age number(3) := &age;
begin
    if age < 16 then
       dbms_output.put_line('你未成人');
    elsif age < 30 then
       dbms_output.put_line('你青年人');
    elsif age < 60 then
       dbms_output.put_line('你奋斗人');
    elsif age < 80 then 
       dbms_output.put_line('你享受人');
    else
       dbms_output.put_line('未完再继');
    end if;
end;

--循环：
declare
    i number(2) := 1;
begin
    loop
        --当i>10时，退出循环
        exit when i>10;
        --输出i的值
        dbms_output.put_line(i);
        --变量自加
        i := i + 1;  
    end loop;
end;
/

--例外
使用oracle系统内置例外，演示除0例外【zero_divide】
declare
    myresult number;
begin
    myresult := 1/0;
    dbms_output.put_line(myresult);
exception
    when zero_divide then 
     dbms_output.put_line('除数不能为0');
     delete from emp;  
end;
/

使用oracle系统内置例外，查询100号部门的员工姓名，演示没有找到数据【no_data_found】
declare
    pename varchar2(20);
begin
    select ename into pename from emp where deptno = 100;
    dbms_output.put_line(pename);
exception
    when NO_DATA_FOUND then 
     dbms_output.put_line('查无该部门员工');
     insert into emp(empno,ename) values(1111,'ERROR');
end;
/

2.存储过程,函数和触发器

2.1
创建存储过程：

create [or replace] procedure 过程名[(参数列表)]  
as
        PLSQL程序体；【begin…end;/】无declare

调用存储过程：

--方法一：
set serveroutput on
begin
 raiseSalary(7369);
end;
/
--方法二：
set serveroutput on
exec raiseSalary(7369);

2.2函数
创建：

CREATE [OR REPLACE] FUNCTION 函数名【(参数列表) 】
 RETURN  返回值类型
AS
PLSQL子程序体；【begin…end;/】

调用：

declare
    v_sal number;
begin
    v_sal:=queryEmpSalary(7369);
    dbms_output.put_line('salary is:' || v_sal);
end;

2.3触发器

CREATE  [or REPLACE] TRIGGER  触发器名
   {BEFORE | AFTER}
   { INSERT | DELETE|-----语句级
      UPDATE OF 列名}----行级
   ON  表名
   [FOR EACH ROW]
   PLSQL 块【declare…begin…end;/】

3.SQL语句优化

（01）选择最有效率的表名顺序（笔试常考）
ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，
FROM子句中写在最后的表将被最先处理，
在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表放在最后，
如果有3个以上的表连接查询,那就需要选择那个被其他表所引用的表放在最后。
例如：查询员工的编号，姓名，工资，工资等级，部门名
select emp.empno,emp.ename,emp.sal,salgrade.grade,dept.dname
from salgrade,dept,emp
where (emp.deptno = dept.deptno) and (emp.sal between salgrade.losal and salgrade.hisal)
1)如果三个表是完全无关系的话，记录和列名最少的表，放最后
2)如果三个表是有关系的话，将引用最多的表，放在最后，然后依次类推

（02）WHERE子句中的连接顺序（笔试常考）
ORACLE采用自右而左的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之左,
那些可以过滤掉最大数量记录的条件必须写在WHERE子句的之右。
例如：查询员工的编号，姓名，工资，部门名
select emp.empno,emp.ename,emp.sal,dept.dname
from emp,dept
where (emp.deptno = dept.deptno) and (emp.sal > 1500)

（03）SELECT子句中避免使用*号
ORACLE在解析的过程中,会将*依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间
select empno,ename from emp;

（04）使用DECODE**函数**来减少处理时间
使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表

（05）整合简单，无关联的数据库访问

（06）用TRUNCATE替代DELETE

（07）尽量多使用COMMIT
因为COMMIT会释放回滚点

（08）用WHERE子句替换HAVING子句
WHERE先执行，HAVING后执行

（09）多使用内部函数提高SQL效率

（10）使用表的别名
salgrade s

（11）使用列的别名
ename e

（12）用索引提高效率
在查询中，善用索引

（13）字符串型，能用=号，不用like
因为=号表示精确比较，like表示模糊比较

（14）SQL语句用大写的
因为Oracle服务器总是先将小写字母转成大写后，才执行
在eclipse中，先写小写字母，再通过ctrl+shift+X转大写;ctrl+shift+Y转小写

（15）避免在索引列上使用NOT
因为Oracle服务器遇到NOT后，他就会停止目前的工作，转而执行全表扫描

（16）避免在索引列上使用计算
WHERE子句中，如果索引列是函数的一部分，优化器将不使用索引而使用全表扫描，这样会变得变慢
例如，SAL列上有索引，
低效：
SELECT EMPNO,ENAME
FROM EMP
WHERE SAL*12 > 24000;
高效：
SELECT EMPNO,ENAME
FROM EMP
WHERE SAL > 24000/12;

（17）用 >= 替代 >
低效：
SELECT * FROM EMP WHERE DEPTNO > 3
首先定位到DEPTNO=3的记录并且扫描到第一个DEPT大于3的记录
高效：
SELECT * FROM EMP WHERE DEPTNO >= 4
直接跳到第一个DEPT等于4的记录

（18）用IN替代OR
select * from emp where sal = 1500 or sal = 3000 or sal = 800;
select * from emp where sal in (1500,3000,800);

（19）总是使用索引的第一个列
如果索引是建立在多个列上，只有在它的第一个列被WHERE子句引用时，优化器才会选择使用该索引
当只引用索引的第二个列时，不引用索引的第一个列时，优化器使用了全表扫描而忽略了索引
create index emp_sal_job_idex
on emp(sal,job);
———————————-
select *
from emp
where job != ‘SALES’;

（20）避免改变索引列的类型，显示比隐式更安全
当字符和数值比较时，ORACLE会优先转换数值类型到字符类型
select 123 || ‘123’ from dual;