欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Oracle的知识点(杂记)

程序员文章站 2022-07-13 08:05:48
...

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系统中将资源回收站,占空间但可还原。