Oracle的知识点(杂记)

1.由于1不等于2 故将表的结构拷给了emp01

     create table emp01 as 
        select * 
        from emp
        where 1=2;

2.层次查询(遍历树结构)

    SELECT
        LEVEL,
        T .*
    FROM
        a_test T CONNECT BY PRIOR curid = subid START WITH subid = 10;

总结：

    1) 向下遍历(遍历子节点)
        connect by 父节点 = prior 子节点 start with 父节点 条件；
    2) 向上遍历(遍历父节点)
        connect by 子节点 = prior 父节点 start with 子节点 条件；

3.top-N 问题

    SELECT
        ROWNUM,
        empno,
        ename,
        sal
    FROM
        (
            SELECT
                *
            FROM
                emp
            ORDER BY
                sal DESC
        )
    WHERE
        ROWNUM <= 3;

ROWNUM伪列的使用

    1) 永远按默认的顺序生成
    2) rownum只能使用<或<=运算符

4.oracle分页。

    SELECT
        ROWNUM empno,
        ename,
        SAL
    FROM
        (
            SELECT
                ROWNUM r,
                E .*
            FROM
                (
                    SELECT
                        *
                    FROM
                        emp
                    ORDER BY
                        sal DESC
                ) E
            WHERE
                ROWNUM <= 8
        )
    WHERE
        r >= 5; 

用法上的注意事项：

    1) 按伪列先筛选出小于结束索引的元素
    2) 对伪列rownum取别名后,在外层循环中按别名筛选出大于起始索引的元素

5.oracle支持的事务隔离级别

1) read committed(读已提交)
2) serializable(序列化)

6.where是行级过滤，select是列级过滤。

7.创建视图.

简化查询，但不能提高性能，数据库中不存放索引对应的数据项。个人理解为windows中的快捷方式

    CREATE OR REPLACE VIEW emp_view AS SELECT
        empno,
        ename,
        sal,
        deptno
    FROM
        EMP E
    WHERE
        sal > (
            SELECT
                AVG (sal)
            FROM
                emp
            WHERE
                deptno = E .deptno
        )
    with read only

8.创建序列

    create sequence mySequence
        increment by 2
        start with 1
        ;

使用伪列nextval,currval:

    select mySequence.nextval from dual;
    select mySequence.currval from dual;
    nextval应在currval之前指定，且查询nextval一次，序列自增。

9.创建索引，oracle会自动维护索引

CREATE INDEX emp_index ON emp (empno, ename, sal, deptno);
适用于：列中的数值分布范围广，表经常被访问，而且数据量大，列经常用在where子句或连接条件中。
不适用于：表经常更新，数据量小。

10.创建同义词synonym

CREATE SYNONYM emp_synonym FOR emp;
使用：
select * from emp_synonym;

11.光标的使用

    set serveroutput on
    declare
    --声明光标
    cursor c is select * from emp;
    --声明记录型变量
    pemp emp%rowtype;
    --声明引用类型变量
    psal emp.sal%type;
    begin
      open c;
      loop
      fetch c into pemp;
      exit when c%notfound;
      dbms_output.put_line(pemp.ename||'的薪水是'||pemp.sal);
      end loop;
      close c;
    end;

注意：

例子中的输出语句要放在exit的后面，否则会多输出一条语句。

12.存储过程

    create or replace procedure raisemoney(eno in number)
    as 
    psal emp.sal%type;
    begin
    select sal into psal from emp where empno = eno;
    update emp set sal = sal+100 where empno = eno;
    dbms_output.put_line('加薪前：'||psal||' 加薪后：'||(psal+100));
    end;

存储过程的使用:

    1) exec raisemoney(7369)
    2)  begin aisemoney(7369) end;

13.存储函数

    create or replace function calcsalary(eno in number)
    return number
    as
    psal emp.sal%type;
    pcomm emp.comm%type;
    begin
    select sal,comm into psal,pcomm from emp where empno = eno;
    return psal*12+nvl(pcomm,0);
    end;

存储函数的使用：
select scott.calcsalary(7369) from dual;

14.游标类型的out参数的存储函数

需要为存储过程创建包头和包体。

包头

    create or replace package mypackage as
    type empcursor is ref cursor;
    procedure queryEmpList(dno in number,empList out empcursor);
    end mypackage;

包体

    create or replace package body mypackage as
    procedure queryEmpList(dno in number,empList out empcursor)
    as
    begin
    open empList for select * from emp where deptno = dno;
    end queryEmpList;
    end mypackage;

注意：

与包头中的procedure语句不同的是，包体中的procedure语句句末无分号。

15.测试存储过程和存储函数

    public class TestOracle {
        /*
         * create or replace procedure queryempinfo(eno in number, pname out
         * varchar2, psal out number, pjob out varchar2) as begin select
         * ename,sal,job into pname,psal,pjob from emp where empno = eno; end;
         */
        @Test
        public void testProcedure() {
            String sql = "{call queryempinfo(?,?,?,?)}";
            CallableStatement call = null;
            Connection conn = null;
            conn = JDBCUtils.getConnection();
            try {
                call = conn.prepareCall(sql);
                call.setInt(1, 7369);
                call.registerOutParameter(2, OracleTypes.VARCHAR);
                call.registerOutParameter(3, OracleTypes.NUMBER);
                call.registerOutParameter(4, OracleTypes.VARCHAR);
                call.execute();
                String pname = call.getString(2);
                Integer psal = call.getInt(3);
                String pjob = call.getString(4);
                System.out.println(pname + "\t" + psal + "\t" + pjob);
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                JDBCUtils.release(conn, call, null);

            }
        }

        @Test
        public void testFunction() {
            String sql = "{?= call calcsalary(?)}";
            CallableStatement call = null;
            Connection conn = null;
            try {
                conn = JDBCUtils.getConnection();
                call = conn.prepareCall(sql);
                call.registerOutParameter(1, OracleTypes.NUMBER);
                call.setInt(2, 7369);
                call.execute();
                int salary = call.getInt(1);
                System.out.println(salary);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        @Test
        public void testCursor() {
            Connection conn = null;
            CallableStatement call = null;
            ResultSet rs = null;
            String sql =  "{call mypackage.queryEmpList(?,?)}";
            try {
                conn = JDBCUtils.getConnection();
                call = conn.prepareCall(sql);
                call.setInt(1, 30);
                call.registerOutParameter(2, OracleTypes.CURSOR);
                call.execute();
                rs = ((OracleCallableStatement)call).getCursor(2);
                while(rs.next()){
                    String name = rs.getString("ename");
                    double sal = rs.getDouble("sal");
                    System.out.println(name+"的薪水是"+sal);
                }
            } catch (Exception e) {
                e.printStackTrace();
            }finally{
                JDBCUtils.release(conn, call, rs);
            }
        }
    }   

16.触发器的使用

    create or replace trigger raisemoney 
    before update of sal
    on emp
    for each row
    begin
    if :new.sal < :old.sal then
    raise_application_error(-20002,'涨后的工资不能少于涨前的工资 涨前工资:'||:old.sal||' 涨后的工资:'||:new.sal);
    end if;
    end;    

区分行级触发器和列级触发器:

1) 含for each row则为行级触发器，不含则是语句级触发器
2) 语句级触发器，不管这条操作语句影响多少行，在该语句执行前或后，只执行一次。
3) 行级触发器，操作语句作用每一条记录时都会触发。
4) 伪记录 :odd :new ,指的是某条记录，来识别状态。 

17.delete和truncate的区别：

delete	逐条删除，将删除的操作以日志的形式进行保存	DML,可以回滚	产生碎片，不释放资源，可以闪回
truncate	先摧毁，在重建	DDL，隐式提交，无法回滚	不产生碎片，释放资源，无法闪回

delete删除类似window系统中将资源回收站，占空间但可还原。