02 Oracle 视图 索引 pl/sql编程语言 触发器 java调用oracle

目录

视图

概念

创建只读视图

视图的作用

索引

概念

单列索引

复合索引

pl/sql编程语言

概念

基本语法

if语句

loop循环

while

exit

for

游标

存储过程

概念

参数类型

or replace

存储函数

存储函数和存储过程的区别

语法区别

本质区别

out类型参数

触发器

概念

分类

触发器实现主键自增

java调用存储过程

环境准备

环境测试

java调用存储过程和存储函数

---

1. 视图

   1. 概念

      1. 是基于一个表或多个表或视图的逻辑表
      2. 是一个虚表, 只存储了对视图的定义, 没有存储数据
      3. 创建视图必须具有dba权限
      4. 通过视图是可以对原表中的数据进行查询和修改

   2. 创建只读视图

      1. --创建只读视图
         create view v_emp1 as select ename, job from emp with read only
         

          

   3. 视图的作用

      1. 可以用于屏蔽一些敏感字段
         1. 比如说, 你想让程序员只能使用表中数据的一部分(某些列不可以访问(员工的工资)), 就可以在创建视图的时候定义需要显示的字段
      2. 保证总部和分部数据及时统一

   4. --查询语句创建表
      create table emp as select * from scott.emp--scott.  是跨用户查询
      select * from emp
      --创建视图
      create view v_emp as select ename, job from emp
      --查询视图
      select * from v_emp
      

       

2. 索引

   1. 概念

      1. 在列上构建一个二叉树(目录)
      2. 会大幅度提高查询效率, 但是会降低增删改的效率

   2. 单列索引

      1. 概念
         1. 把索引加到单列上
      2. 触发规则
         1. 条件必须是索引列中的原始值

      3. --单列索引
         create index idx_ename on emp(ename)
         select * from emp where ename='SCOTT'
         

          

   3. 复合索引

      1. 概念
         1. 第一列为优先检索列

         2. --复合索引  
            create index idx_ename_job on emp(ename, job)
            

             

      2. 触发条件

         1. --触发规则
            select * from emp where ename = 'SCOTT' and job = 'xx' --触发复合索引
            select * from emp where ename = 'SCOTT' or job = 'xx' --不触发索引
            select * from emp where ename = 'SCOTT' --触发单列索引
            

             

3. pl/sql编程语言

   1. 概念

      1. 对sql语言的扩展, 使sql语言具有过程化变成的特性

   2. 基本语法

      1. 赋值方法 :=
      2. 引用表中某一字段的类型
         1. ena emp.ename%type;
      3. 使用select给变量赋值使用关键字into
      4. rowtype
         1. 记录型变量
         2. 可以存储一行的数据
      5. 连接符 ||
      6. 代码

         1. declare
              i number(2) := 10;
              s varchar2(10) := '李乾';
              ena emp.ename%type;--引用型变量
              emprow emp%rowtype;--记录型变量
            begin
              dbms_output.put_line(i);
              dbms_output.put_line(s);
              select ename into ena from emp where empno=7788;
              dbms_output.put_line(ena);
              select * into emprow from emp where empno=7788;
              dbms_output.put_line(emprow.ename || '的工作是: ' || emprow.job);
            end;
            

             

   3. if语句

      1. 按照输入给变量赋值
         1. i number(3) := &i;

      2. --if语句
         declare
           i number(3) := &i;
         begin
           if i<18 then
             dbms_output.put_line('未成年');
           elsif i<40 then
             dbms_output.put_line('中年人');
           else
             dbms_output.put_line('老年人');
           end if;
         end;
         

          

   4. loop循环

      1. while

         1. --while
            declare
              i number(2) :=1;
            begin
              while i<11 loop
                dbms_output.put_line(i);
                i := i + 1;
              end loop;
            end;
            

             

      2. exit

         1. 常用

         2. --exit
            declare
              i number(2) :=1;
            begin
              loop
                exit when i>10;
                dbms_output.put_line(i);
                i := i + 1;
              end loop;
            end;
            

             

      3. for

         1. --for
            declare
              i number(2) :=1;
            begin
              for i in 1..10 loop
                dbms_output.put_line(i);
              end loop;
            end;
            

             

   5. 游标

      1. 概念
         1. 类似于java中的集合, 用于存储记录
         2. 可以存储多个对象(多行记录)
      2. 游标也可以传递参数

      3. --游标
         declare
           cursor c1 is select * from emp;
           emprow emp%rowtype;
         begin
           open c1;
             loop
               fetch c1 into emprow;
               exit when c1%notfound;
               dbms_output.put_line(emprow.ename);
             end loop;
           close c1;
         end;
         --给指定部门的员工涨工资
         select * from emp where deptno=10;
         
         declare
           cursor c2(eno emp.deptno%type) is select empno from emp where deptno=eno;
           en emp.empno%type;
         begin
           open c2(10);
             loop
               fetch c2 into en;
               exit when c2%notfound;
               update emp set sal=sal+100 where empno=en;
               commit;
             end loop;
           close c2;
         end;
         

          

   6. 存储过程

      1. 概念

         1. 类似于java中的方法
         2. 是提前已经编译好的pl/sql语言, 可以直接调用
         3. 语法中不能使用return

      2. 参数类型

         1. 有in和out两种, 默认是in

      3. or replace

         1. 可选选项
         2. 带上的话, 如果创建的有同名的会覆盖, 不带的话, 如果创建的有同名的会报错
         3. 一般都是带上

      4. --存储过程
         create or replace procedure p1(eno emp.empno%type)
         is
         
         begin
           update emp set sal=sal+100 where empno=eno;
           commit;
         end;
         
         --使用
         select * from emp where empno=7788
         
         declare 
         
         begin
           p1(7788);
         end;
         

          

   7. 存储函数

      1. 概念
         1. 和存储过程差不多
         2. 语法中有两个return, 第一个return绝对不能带有长度(直接写个number就行)
            1. 存储过程和存储函数的参数都不能带长度
      2. 有返回值的方法在调用时必须使用一个变量接收, 否则报错

      3. --存储函数
         create or replace function f_yearsal(eno emp.empno%type) return number
         is
           s number(10);
         begin
           select sal*12+nvl(comm, 0) into s from emp where empno=eno;
           return s;
         end;
         
         --测试
         declare 
           s number(10);
         begin
           s := f_yearsal(7788);
           dbms_output.put_line(s);
         end;
         

          

   8. 存储函数和存储过程的区别

      1. 语法区别

         1. 关键字不一样
            1. 存储函数比存储过程多了两个return

      2. 本质区别

         1. 存储函数有返回值, 而存储过程没有返回值
            1. 在一些需要用到返回值的地方就必须使用存储函数了
            2. 案例: 必须使用存储函数

               1. --准备工作
                  create table dept as select * from scott.dept
                  --仅使用emp表来实现查询员工姓名以及员工所在部门名称
                  create or replace function fdna(dno dept.deptno%type) return dept.dname%type
                  is
                  	dna dept.dname%type;
                  begin
                    select dname into dna from dept where deptno=dno;
                    return dna;
                  end;
                  
                  select ename, fdna(deptno)
                  from emp;
                  

                   

   9. out类型参数

      1. 可以用来向外输出结果, 让存储过程实现存储函数中return的功能
      2. out和in的区别
         1. 凡是涉及到into查询语句复制或:=赋值的参数, 都必须使用out修饰, 否则报错
         2. 如果参数并没有涉及到上面的两种赋值情况, 而你还是使用了out给参数赋值了, 同样会报错

      3. --out类型参数
         create or replace procedure p_yearsal(eno emp.empno%type, yearsal out number)
         is
           s number(10);
           c emp.comm%type;
         begin
           select sal*12, nvl(comm, 0) into s, c from emp where empno = eno;
           yearsal := s+c;
         end;
         
         --测试
         declare
           yearsal number(10);
         begin
           p_yearsal(7788, yearsal);
           dbms_output.put_line(yearsal);
         end;
         

          

4. 触发器

   1. 概念

      1. 由事件驱动的特殊过程

   2. 分类

      1. 语句级触发器
         1. 不包含for each row的触发器
         2. 针对一些语句(update insert)进行限制
      2. 行级触发器
         1. 包含for each row的触发器
         2. 加for each row是为了使用:old或者:new对象或一行记录

   3. --触发器
      select * from person
      --语句级触发器
      create or replace trigger t1
      after
      insert on person
      declare
      
      begin
        dbms_output.put_line('一个新员工入职');
      end;
      --测试
      insert into person values(23,'123')
      
      --行级触发器
      --不能给员工降薪
      create or replace trigger t2
      after
      update on emp
      for each row
      declare
      
      begin
        if :old.sal>:new.sal then
          raise_application_error(-20001,'不能给员工降薪');
        end if;
      end;
      
      --测试
      update emp set sal=sal-1 where empno=7788;
      commit;
      select * from emp where empno=7788
      

       

   4. 触发器实现主键自增

      1. 需要使用到序列

      2. --触发器实现主键自增
         create or replace trigger auid
         before
         insert on person
         for each row
         declare
         
         begin
           select s_person.nextval into :new.pid from dual;
         end;
         
         select * from person;
         
         --测试
         insert into person(pname) values('qwe');
         

          

5. java调用存储过程

   1. 环境准备

      1. 不同的oracle版本需要使用不同的驱动jar包
         1. oracle10g  --  ojdbc14.jar
         2. oracle11g  --  ojdbc6.jar
      2. 驱动jar包可以给他加上一个scope作用域runtime
         1. 驱动jar包只在运行的时候才用到, 正常编译的时候没有用到

   2. 环境测试

      1. @Test
         public void test01() throws Exception {
             Class.forName("oracle.jdbc.driver.OracleDriver");
             Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.102.10:1521:orcl", "lq01", "liqian0519");
             PreparedStatement preparedStatement = connection.prepareStatement("select * from emp where empno=?");
             preparedStatement.setObject(1,"7788");
             ResultSet resultSet = preparedStatement.executeQuery();
             while (resultSet.next()){
                 System.out.println(resultSet.getString("ename"));
             }
         }
         

          

   3. java调用存储过程和存储函数

      1. 调用存储过程

         1. /**
             * java调用存储过程
             * @throws Exception
             */
            @Test
            public void test02() throws Exception {
                Class.forName("oracle.jdbc.driver.OracleDriver");
                Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.102.10:1521:orcl", "lq01", "liqian0519");
                //调用存储过程或存储函数时必须使用CallableStatement接口, 此接口是PreparedStatement接口的子接口
                CallableStatement callableStatement = connection.prepareCall("{call p_yearsal(?, ?)}");
                callableStatement.setObject(1, "7788");
                callableStatement.registerOutParameter(2, OracleTypes.NUMBER);
                callableStatement.execute();
                System.out.println(callableStatement.getObject(2));
                callableStatement.close();
                connection.close();
            }

             

      2. 调用存储函数

         1. /**
             * java调用存储函数
             * @throws Exception
             */
            @Test
            public void test03() throws Exception {
                Class.forName("oracle.jdbc.driver.OracleDriver");
                Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@192.168.102.10:1521:orcl", "lq01", "liqian0519");
                //调用存储过程或存储函数时必须使用CallableStatement接口, 此接口是PreparedStatement接口的子接口
                CallableStatement callableStatement = connection.prepareCall("{?= call f_yearsal(?)}");
                callableStatement.setObject(2, "7788");
                callableStatement.registerOutParameter(1, OracleTypes.NUMBER);
                callableStatement.execute();
                System.out.println(callableStatement.getObject(1));
                callableStatement.close();
                connection.close();
            }