2020：0617 --- Oracle(三)

今日内容

1.视图
    视图就是提供一个查询的窗口，所有数据来自于原表。

1. 视图

1.1 跨用户查询

    1. 当前用户

    2. 查询SCOTT用户下的EMP表
    
        ```
        select * from scott.emp
        ```

    3. 在itheima用户下:查询语句创建表一个一样的EMP表
        ```
        create table emp as select * from scott.emp
        ```

1.2 创建视图

    1.必须有dba权限
    ```
    create view v_emp as select ename, job from emp;
    ```
    
    2.查询视图
    ```
    select * from v_emp
    ```

    3. 修改视图
    ```
    update v_emp set job='CLERK' where ename='ALLEN';
    commit
    ```
    
        注意：修改了视图后，表中的数据也会随之改变。所以不推荐。
        
    4. 创建只读视图
    
    ```
    create view v_emp1 as select ename, job from emp with read only;
    ```

1.3 视图的作用

    1. 视图可以屏蔽掉一些敏感字段。
    
        例如emp表中的工资字段。
        
    2. 保证总部和分布数据及时统一。

2. 索引

2.1 索引的概念

    索引就是在表的列上构建一个二叉树，达到大幅度提高查询效率的目的，但是
索引会影响增删改的效率。

    没有索引时，查询数据时从上向下一页一页查询。有了索引是就根据索引很快
定位到要查询的数据。
    但是增删改时，删掉数据时，都会改变现有的索引结构，要重新构造这个二叉树
重新修改索引。
    
    索引分为单列索引和复合索引

2.2 单例索引

    1. 创建单列索引
    ```
    create index idx_ename on emp(ename);
    ```

    2. 单列索引触发规则
    ```
    select * from emp where ename = 'SCOTT'
    ```
    
        条件必须是索引列中的原始值,单行函数，模糊查询，都会影响索引的触发。

2.3 复合索引 ：创建在多列上的索引

    1. 创建复合索引
    ```
    create index idx_enamejob on emp(ename, job);
    ```
    
    2. 复合索引中第一列为优先检索列    
       如果要触发复合索引，必须包含优先索引列中的原始值
       
    ```
    select * from emp where ename = 'SCOTT' and job='xx';
    ```
    
    3. 如果这一列即是单列索引，也包含在复合索引里面。如果只是这
       一列当条件时，触发的是单列索引。
    ```
    select * from emp where ename = 'SCOTT';
    ```
    
    4. 特殊情况
    ```
    select * from emp where ename = 'SCOTT' or job='xx'
    ```
    
       or关键字相当于两个查询语句：
       
       ```
        select * from emp where ename = 'SCOTT'
        select * from emp where job='xx'
       ```
       第一个触发，第二个不触发，所以结合起来就不触发。

3. pl/sql编程语言

    pl/sql编程语言是对sql语言的扩展，使得sql语言具有过程化编程的特性。
    pl/sql编程语言比一般的过程化编程语言，更加灵活高效。
    pl/sql编程语言主要用来编写存储过程和存储函数等。

3.1 声明方法

    declare   
    
    begin   
    
    end;

3.2 pl/sql定义变量

```
declare
   i number(2) := 10;
   s varchar2(10) := '小明';
   ena emp.ename%type;     ----引用型变量：得到emp表中ename列的数据类型，赋给ena
   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;
```

    输出：
    ```
    dbms_output.put_line(i);
    dbms_output.put_line(s);
    ```
    
    赋值操作可以用 :=  
    ```
    i number(2) := 10;
    s varchar2(10) := '小明';
    ```
    
    也可以用查询加into语句赋值
    ```
    select ename into ena from emp where empno = 7788;
    select * into emprow from emp where empno = 7788;
    ```

3.3 pl/sql中的if判断

    案例：
        输入小于18的数字，输出未成年
        输入大于18小于40的数字，输出中年人
        输入大于40的数字，输出老年人
    
    代码：
    ```
    declare

            i number(3) := &age;
    
    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;
    ```

3.4 pl/sql中的loop循环

    案例：用三种方式输出1-10
    
    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循环
    ```
    declare
      i number(2) := 1;
    
    begin
      loop
        exit when i>10;
        dbms_output.put_line(i);
        i := i+1;
      end loop;
    
    end;
    ```
    
    3.for循环
    ```
    declare

    begin
      for i in 1..10 loop
        dbms_output.put_line(i);
      end loop;  
    
    end;
    ```
    
    用的较多的是exit循环。

3.5 pl/sql中的游标

    可以存放多个对象，多行记录。
    
    1.案例：输出emp表中所有员工的姓名
    
    2.代码
        ```
        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;
        ```
        
        分析一下：
            先定义一个游标：存放emp表数据
            cursor c1 is select * from emp;
            
            定义一个记录型引用变量：引用emp表的行类型
            emprow emp%rowtype;
            
            打开游标：open c1
            
            开始循环：
                取出右边中的一个数据（emp的一行数据）放入记录型变量emprow
                
                定义退出条件：当没有数据时，退出
                
                输出数据
            关闭循环
            
            关闭游标：close c1;
    
    3.案例：给指定部门员工涨工资
    
    4.代码：
        ```
        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;
        ```
        
    5.结果

    6.分析一下：
        先定义一个有参的游标：存放指定部门编号的员工的编号
        cursor c2(eno emp.deptno%type) is select empno from emp where deptno = eno;
        
        定义一个存放emp表员工编号的变量，类型相同
        en emp.empno%type;
        
        打开游标同时传参：open c2(10);
        
            开启循环loop
            
                取出游标中的一个数，存入en变量
                fetch c2 into en;
                
                更新该用户的工资，提交事务：sql语句
                update emp set sal=sal+100 where empno=en;
                commit;
            
            结束循环：end loop;
            
        关闭游标：close c2;

4. 存储过程

1.概述
    存储过程就是提前已经编译好的一段pl/sql语言，放置在数据库端，可以直接
被调用。
    这一段pl/sql一般都是固定步骤的业务。
    
    java操作数据库，必须先从连接池中拿到connection对象。但是pl/sql语言它
本身就写在数据库端，它不用通过连接池也可以直接操作数据库。这也是它的优势。
但是它不能写太复杂的业务逻辑。

    我们一般把哪些业务放到数据库端，用存储过程写呢？
    都是一些固定步骤的业务，放在数据库端，使用存储函数或者存储过程来编写。
    

2.案例：给指定员工涨100元

    正确的创建了存储过程
    ```
    create or replace procedure p1(eno emp.empno%type)
    is
    
    begin
      update emp set sal = sal + 100 where empno = eno;
      commit;
      
    end; 
    ```

    错误的创建了存储过程

3. or replace
    
  如果p1这个存储过程的名字被占用了，加上or replace的意思就是我可以直接修改。
否则会出错。
    
  建议都加上。
  
4. 结果
    这个代码，也就是存储过程已经编译好了，而且也存在了服务器端。
即使重新登录这个存储过程也还是在那。
    可以直接被调用。
    
5. 调用方式
    
    1.声明方式
    declare
    
    begin
    
    end
    
    2.java代码
    
6. 测试
    ```
    declare

    begin
    
      p1(7788);
    end;
    ```

5. 存储函数

1. 通过存储函数实现计算指定员工的年薪

2. 代码
```
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;
```

3. 注意：
    存储过程和存储函数的参数都不能带长度。
    存储函数的返回值类型不能带长度。

4.测试:存储函数在调用时，返回值需要接收

    ```
        ---存储函数在调用时，返回值需要接受
        declare
          s number(10);
        begin
          s := f_yearsal(7788);   
          dbms_output.put_line(s);   
        end;
    ```

6. out类型参数如何使用

    1.案例：使用存储过程算年薪
    
    2.代码：
    ```
    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;
    ```
    3. 测试p_yearsal存储过程
    ```
    declare
            yearsal number(10);
    begin
           p_yearsal(7788, yearsal);  ---调用方法，传递参数
           dbms_output.put_line(yearsal);
    
    end;
    ```
    
    4.注意：in和out类型参数的区别是什么？
    
        凡是涉及到into查询语句赋值或者:=赋值操作的参数，都必须
    使用out来修饰。

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

    都是一段编好的pl/sql代码放到服务器端供我们调用。即我们可以把一部分
业务逻辑写到服务器端。

    1、区别
        1.语法区别：
            关键字不一样
            存储函数比存储过程多了两个return
        2.本质区别：
            存储函数有返回值
            存储过程没有返回值
            如果存储过程想实现有返回值的业务，我们就必须使用out类型的参数。
        即便是存储过程使用了out类型的参数，其本质也不是真的有了返回值。
        而是在存储过程内部给out类型参数赋值，然后再执行完毕后拿到输出类型的值。
        
    2. 我们可以使用存储函数有返回值的特性，来自定义函数，而存储过程不能用来
       自定义函数。
       
    
    3. 案例需求：查询出员工姓名，员工所在部门名称。
    
        3.1 使用传统方式来实现案例需求
        ```
        create table dept as select * from scott.dept;
        
        select e.ename, d.dname
        
        from emp e, dept d
        
        where e.deptno = d.deptno
        ```
        
        3.2 使用存储函数来实现：提供一个部门编号，输出一个部门名称
        ```
        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;
        ```
        
        使用fnda存储函数来实现案例需求
        ```
        select e.ename, fdna(e.deptno)  --- 存储过程有返回值

        from emp e;
        ```

8 触发器

        触发器就是制定一个规则，在我们做增删改操作的时候。是要满足
    改规则，自动触发，无需调用。
    
        触发器分两类：
            1.语句级触发器：不包含有for each row
                用不到老的和新的记录
            2.行级触发器：包含有for each row的就是行级触发器
                要用到老的和新的记录
                
                加foe each row是为了使用:old或者new拿到对象（一行记录）
            
        在触发器中触发语句与伪记录变量的值

8.1 语句级触发器

案例：插入一条记录，输出一个新员工入职。

```
create or replace trigger t1
after  ---插入后触发
insert ---插入语句的触发器
on person 

declare

begin
  dbms_output.put_line('一个新员工入职');
end;

```

触发ti触发器
```
insert into person values (1, '小李');
commit;
```

8.2 行级触发器：包含有for each row的就是行级触发器

foe each row是为了使用:old或者new拿到对象（一行记录）

1.行级触发器t2
    ```
    create or replace trigger t2
    before 
    update
    on emp
    for each row
     
    declare
    
    begin
      if :old.sal>:new.sal then
        raise_application_error(-20001, '不能给员工降薪'); ---抛一个异常
        
      end if;
    end;
    ```
注意抛异常的语法：raise_application_error(-20001~-20999之间, '错误提示信息');

2.触发行级触发器t2
    ```
    ---触发t2
    update emp set sal = sal -1 where empno = 7788;
    commit;
    ```

8.3 使用触发器实现主键自增

    分析：
        在用做插入操作之前，拿到即将插入的数据，给该数据中的主键列赋值。
        所以要拿到这个数据，就是行级触发器。
        
    触发器代码
    ```
    create or replace trigger auid
    before
    insert 
    on person
    for each row
    declare
    
    begin
      ---拿到主键的id并赋值
      ---s_person是一个序列Sequences
      select s_person.nextval into:new.pid from dual;
    end;
    ```
    
    触发auid，实现自增
    ```
    insert into person (pname) values ('小狗');
    commit;
    ```

9. Java程序调用存储过程

    1. 环境准备
    
        java连接oracle的jar包：
        可以在虚拟机中xp的oracle安装目录下找到jar包 :ojdbc14.jar
        C:\ORACLE\product\10.2.0\db_1\jdbc\lib

        一般流行的oracle版本：
        oracle10g           ojdbc14.jar
        oracle11g           ojdbc6.jar
        
    
    2. 创建Maven工程
        导入ojdbc驱动包（对应着oracle的版本导入）
        ```
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc14</artifactId>
            <version>10.2.0.4.0</version>
            <!--驱动包一般运行才用到，正常编译时没有用到-->
            <scope>runtime</scope>
        </dependency>
        ```