视图_序列_触发器_事务_函数_存储过程_程序包_同义词_索引

--------------------------------------------------------视图---------------------------------------------------------------

/*
   视图：从一张表或多张表中检索数据，其内部形式为sql语句的定义。
   视图划分为：简单视图和复杂视图
   简单视图：从单表获取数据
   复杂视图：从多表获取数据
   通常情况下，只对视图做查询，不做增删改
   create or replace view 视图名 as 查询语句的定义
*/
 
--授予SCOTT创建视图的权限
grant create view to scott;

----创建简单视图
 create or replace view vw_emp as
        select * from emp;
        
----使用视图：查询vw_emp
select * from vw_emp;

--常见复杂视图：查询部门及其对应的员工信息
create or replace view vw_deptandemp
as
select d.deptno,d.dname,e.empno,e.ename from dept d inner join emp e on d.deptno=e.deptno;

select * from vw_deptandemp;

-----------------------------------------------------序列----------------------------------------------------------------------

--建议每张表建一个自己的序列
--使用序列（oracle不像MySQL支持自增，需要实现序列）
insert into emp(empno,ename) values(seq_emp.nextval,'2222');

----------------------------------------------------触发器----------------------------------------------------------------------

/*
  触发器：模式触发器，数据级触发器、DML触发器
  
                   -------------
                             1、复杂的安全性检查
                             2、数据确认
                             3、实现审计功能
                             4、完成数据的备份和同步
                   -------------
  DML触发器：
       语句级触发器：无论如何操作影响多少行记录，触发器只被调用一次
*/
--当用户向表中增删改数据时，将用户的操作记录到日志表
insert into emp(empno,ename) values(seq_emp.nextval,'55555');
update emp set ename='7777' where empno=3;

---创建语句级触发器
create or replace trigger tri_emp_log 
       after --触发时机
             update or insert on emp 
             begin
               insert into emp_log values(seq_log.nextval,'插入信息',sysdate);
               end;
        
---创建一个行级触发器：

create or replace trigger tri_emp_log1 
       after --触发时机
             update or insert on emp 
             for each row
             begin
               insert into emp_log values(seq_log.nextval,'插入信息',sysdate);
               end;

------删除emp表中记录时，将被删除的记录备份到emp_back表中
-------创建触发器实现将表中数据在删除前备份到emp_back中
create or replace trigger tri_emp_back
       before
          delete on emp
          for each row
          begin
            insert into emp_back 
                   values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);
            end;

delete from emp where empno=3;

---作业：向部门表插入一条记录，同时将该条记录备份到dept_back中
create or replace trigger tri_dept_back
       before
          insert on dept
          for each row
          begin
            insert into dept_back 
                   values(:new.deptno,:new.dname,:new.loc);
          end;

 insert into dept
                   values(seq_deptno.nextval,'技术部'，'二楼');

/*
   instead of触发器
   代替触发器
   语法：create or replace trigger 触发器名
         instead of  insert or update or delete
        on 视图名
       for each row
       begin 
       //通过sql语句操作每一张和视图相关的表
       end;

*/
create or replace trigger tri_deptandemp_back
         instead of  insert  -- or update or delete
        on vw_deptandemp --视图名
       for each row
       begin 
         insert into dept(deptno,dname)
                   values(:new.deptno,:new.dname); 
       insert into emp(empno,ename,deptno)
              values(:new.empno,:new.ename,:new.deptno);
           
       end;


insert into vw_deptandemp
       values(18,'销售部',9999，'啊啊啊');


select * from vw_deptandemp; 


/*
   系统事件触发器：应用对象为数据库
*/
--------特点： 启动数据后将执行操作
create or replace trigger tr_qd
       after startup
             on database
       begin
         insert into scott.emp_log values (scott.seq_log.nextval,'用户启动数据库',sysdate);
         end;


-----------------------------------------------------------
create or replace trigger tr_qd1
       before shutdown 
        on database
       begin

         insert into scott.emp_log values (scott.seq_log.nextval,'关闭启动数据库',sysdate);

         end;

--事务：保证数据一致性

/*
  事务：用户定义的一系列操作步骤（一条或者多条语句），这些操作要么全部执行成功，要么全部执行失败。
   
*/


--循环插入记录，并提交事务
--事务隐式执行
begin
  for i in 1..5 loop
    insert into dept values(seq_deptno.nextval,'部门'||seq_deptno.nextval,seq_deptno.nextval||'楼');
    end loop;
    commit;  --提交事务（事务结束）
  end;
  
--模拟aa给cc转账，每次转500，当余额不足时，提醒无法转账
declare
  --声明一个异常对象
  err_money exception;
  --通知编译器使用exception_init()将异常对象与oracle错误代码绑定
  pragma exception_init(err_money,-02290);
begin
  dbms_output.put_line('转账开始~~~~~~~~~~~~~~~');
  update account set amoney = amoney + 500 where aname='cc';  
  update  account set amoney = amoney - 500 where aname='aa';
   commit;
  dbms_output.put_line('转账结束~~~~~~~~~~~~~~');
  exception
      when err_money then
        dbms_output.put_line('余额不足，无法转账');
        rollback;
 
  end;
  
  --------------------向dept表插入10条记录
  declare
  --声明一个异常对象
  err_dept exception;
  --通知编译器使用exception_init()将异常对象与oracle错误代码绑定
  pragma exception_init(err_dept,-00001);
  begin
    insert into dept values(seq_deptno.nextval,'部门'||seq_deptno.nextval,seq_deptno.nextval||'楼');
        insert into dept values(seq_deptno.nextval,'部门'||seq_deptno.nextval,seq_deptno.nextval||'楼');
            insert into dept values(seq_deptno.nextval,'部门'||seq_deptno.nextval,seq_deptno.nextval||'楼');
              savepoint sp;  --定义事务保存点
                insert into dept values(seq_deptno.nextval,'部门'||seq_deptno.nextval,seq_deptno.nextval||'楼');
                    insert into dept values(seq_deptno.nextval,'部门'||seq_deptno.nextval,seq_deptno.nextval||'楼');
                        insert into dept values(seq_deptno.nextval,'部门'||seq_deptno.nextval,seq_deptno.nextval||'楼');
                            insert into dept values(seq_deptno.nextval,'部门'||seq_deptno.nextval,seq_deptno.nextval||'楼');
                                insert into dept values(seq_deptno.nextval,'部门'||seq_deptno.nextval,seq_deptno.nextval||'楼');
                                    insert into dept values(seq_deptno.nextval,'部门'||seq_deptno.nextval,seq_deptno.nextval||'楼');
                                        insert into dept values(10,'部门'||10,10||'楼');   
    commit;  --提交事务（事务结束）
     exception
      when err_dept  then
        dbms_output.put_line('失败');
        rollback to sp;
  end;
  
  
  /*

        事务影响DML：
        
      read only: 只读操作，即只允许事务内使用select
      read write: 可读写操作，属于默认值，即允许事务内使用
                    select insert update delete

        
      read only: 只读操作，即只允许事务内使用select
      read write: 可读写操作，属于默认值，即允许事务内使用
                    select insert update delete
  */
  
  set transaction read only;
  --update emp set ename = '';
  select * from emp;
  --commit;
  
  set transaction read write;
  update emp set ename = 'aaaa';
  commit;
  
  
  --隔离性
  /*

         事务的隔离级别：

         oracle提供了两种事务隔离级别
         1、read committed（已提交读取）：禁止脏读，但允许不可重复和幻读，属于默认隔离级别
         2、serializable（串行化读取）：禁止脏读，禁止不可重复读，禁止幻觉读，尽量少使用，以为会降低数据库的性能
  */
  

------------------------------------------------------函数------------------------------------------------------------------------------

  /*
      oracle函数：
         1、内置函数：  to_date()
         2、自定义函数
         语法：
                  create or replace function 函数名[（参数1，参数2）]
                         return 函数返回值类型（必须有）
                                is/as  --取代了declare
                                声明变量等
                                begin
                                       函数执行体
                                       return 返回具体的值
                                end；
  */
  
  
  --查询人和总工资
  select ename,(sal+comm) from emp;
  
  
  ---使用nvl(字段，‘新值’)对null值进行转换为一个自定义的新值
    select ename,(nvl(sal,0)+nvl(comm,0)) from emp;
  
 
  --创建函数 first_day(),用来返回每个月第一天


    select * from student where substr(to_char(sdate,'yyyy-mm-dd'),9,2) = '01';
----------------------------自己写函数---------------------------------------
/*
    使用函数：
    1、直接在DML语句中被使用
    2、在PL/SQL块中使用
*/
 create or replace function first_day1(fdate date)
  return date
  as
  v_date date;
  v_year varchar2(10);
  v_month varchar2(10);
  v_day varchar2(10):='01';
  begin
       v_year:=to_char(fdate,'yyyy');
       v_month:=to_char(fdate,'mm');
       v_date:=to_date(v_year||v_month||v_day,'yyyy-mm-dd');  
      return v_date;
    end;
    
    --测试
    select first_day(hiredate) from emp;
      select * from emp where hiredate=first_day1(hiredate);
-----------------------------------------------------------------------------------------------------------------------------
  create or replace function first_day(fdate date)
  return date
  as
  v_date date;
  begin
        --v_date:=to_date(substr(to_char(fdate,'yyyy-mm-dd'),1,8)||'01','yyyy-mm-dd'); 
        --v_date:=last_day(fdate-1)+1;
        v_date:=last_day(add_months(fdate,-1))+1;
        -- dbms_output.put_line(v_date);    
      return v_date;
    end;
    --测试
  select * from emp where hiredate=first_day(hiredate);
  
  
  declare
  v_count number;
  v_ename emp.ename%type;
  v_hiredate emp.hiredate%type;
  begin
     select count(*) into v_count from emp where hiredate=first_day(hiredate);
     dbms_output.put_line(v_count||'条信息');
    for i in 1 .. v_count  loop
    select ename,hiredate into v_ename,v_hiredate from
            (select rownum as rn,ename,hiredate from emp where hiredate=first_day(hiredate))
                    where rn=i;
    dbms_output.put_line('生日在月初的人是：'||v_ename||'  生日是： '||v_hiredate);
    end loop;
    end;
  
  -----使用函数根据员工编号查询员工的总工资，
  create or replace function total_sal(v_empno emp.empno%type)   --名字在前，类型在后
  return emp.sal%type
  as
  v_sal emp.sal%type;
  begin
    select nvl(sal,0)+nvl(comm,0) into v_sal from emp where empno=v_empno;
       
      return v_sal;
    end;
  --测试  
    begin
      dbms_output.put_line(total_sal(7499));
      end;
      

------------------------------------------------------存储过程-----------------------------------------------------------------------------

  /*
  存储过程是将一组sql预编译（预编译对象就是把一些格式固定的SQL编译后，
  存放在内存池中即数据库缓冲池，当我们再次执行相同的SQL语句时就不需要预编译的过程）之后，
  并且经过优化后直接存储于数据库中，使用时无需再次编译，提高了工作效率。
  
  函数侧重于对复杂过程的处理结果，能直接写在SQL语句
  存储过程侧重于对SQL语句的批量操作，不能应用在SQL语句中
  
  存储过程的参数通过out\inout返回值

      语法：
      create [or replace] .
      procedure  过程名(参数1 in number,参数2 in varchar2)    
              --参数的数据类型只需要指明类型名即可，不需要指定宽度。
       as    --可以理解为pl/sql的declare关键字，用于声明变量
       begin 
         --sql语句
         end;


  */
  
  -------------------------------创建存储过程，无参
   create or replace procedure pro_empsal
  is
  begin
    update emp set sal=10000;
    commit;
  end;
  
  ------------------------------一、创建存储过程，将emp表中‘aaaa’的deptno改为10， 通过in参数
  create or replace procedure pro_emp(v_ename in emp.ename%type,v_deptno emp.deptno%type)  --in可以缺省
  as
  begin
    update emp set deptno=v_deptno where ename=v_ename;
    commit;
  end;
  
  --execute pro_emp('aaaa',10);  
-------测试
  begin
    pro_emp('aaaa',10);
  end;
  
  
  -----------------------二、创建存储过程，通过out参数，将emp表中‘cccc’的deptno改为50，并返回修改后的年龄进行输出显示
   create or replace procedure pro_emp1(v_ename in emp.ename%type,v_deptno in emp.deptno%type,v_out_result out number)
  as
  err_notfound exception;
  --未找到记录的错误代码： 100    ------------------加载找不到的异常
  pragma exception_init(err_notfound,100);
  begin
    update emp set deptno=v_deptno where ename=v_ename;
    select deptno into v_out_result from emp where ename=v_ename;
    commit;
    exception 
      when err_notfound then
      dbms_output.put_line('没有找到记录');
  end;
  -----------------测试1
  方式一： SQL> var a number;  --在命令行声明变量 需要使用 var 关键字
SQL> execute pro_stu1(50,‘张三’,:a); --在调用存储过程中一定要为输出参数指定输出的变量名，将存储过程的输出结果利用“ ：”与 变量绑定，运行后直接输出


-------测试2
  declare
   v_deptno emp.deptno%type;
  begin
    pro_emp1('dddd',50,v_deptno);
    dbms_output.put_line(v_deptno);
  end;
  
    
  ------------------------怪异想法测试----------select到null值之后的异常问题，可以用if_else代替处理
     create or replace procedure pro_nullvalues(v_ename in emp.ename%type,v_deptno in emp.deptno%type,v_out_result out number)
  as
  v_count number;
  begin
    select deptno into v_count from emp where ename=v_ename;
    if v_count = 0 then
      dbms_output.put_line('没有找到记录');
      else
    update emp set deptno=v_deptno where ename=v_ename;
    select deptno into v_out_result from emp where ename=v_ename;
    end if;
    commit;
  end;
-------测试
  declare
   v_deptno emp.deptno%type;
  begin
    pro_emp1('dddd',50,v_deptno);
    dbms_output.put_line(v_deptno);
  end;
  
  
  -----------------------三、创建存储过程，通过in out参数，将emp表中‘cccc’的deptno改为30
  
  create or replace procedure pro_emp2(v_inout_ename in out emp.ename%type, v_inout_deptno in out emp.deptno%type)
  as
  begin
    update emp set deptno=v_inout_deptno where ename=v_inout_ename;
    select deptno into v_inout_deptno from emp where ename=v_inout_ename;
    commit;
  end;
  
 --------------测试
  declare
  v_ename emp.ename%type:='cccc';
   v_deptno emp.deptno%type :=30;
  begin
    pro_emp2(v_ename,v_deptno);
    dbms_output.put_line(v_deptno);
  end; 
  

  ---------------------------------------------------程序包--------------------------------------------------------------------------

  /*
    如果某个功能模块需要定义大量的自定义函数和存储过程，为了对这些函数和过程管理上的方便，可以使用程序包来组织和管理这些函数和过程
    程序包：简称包，由规范和主体组成
    
    规范：只有函数和过程的声明，没有具体实现
    主体：必须遵从规范，实现规范中所定义的函数和过程
  */
  
  --需要在包中定义函数和过程
  create or replace package pk_first
  as 
  function fun_checkename(v_empno number)
    return varchar2; --声明一个函数
  procedure pro_updatesal(v_empno number); --声明一个过程
    end pk_first;
    
--------------------创建主体
  create or replace package body pk_first
  as 
  --函数：根据编号查询姓名
  function fun_checkename(v_empno number)
    return varchar2
    is
    v_enam varchar2(50);
    begin
      select ename into v_enam from emp where empno=v_empno;
      return v_enam;
      --函数在主体中的实现在end后需要添加函数名
      end fun_checkename;
      --根据编号修改工资
   procedure pro_updatesal(v_empno number)
         is
         begin
           update emp set sal=sal+1000 where empno=v_empno;
           commit;
           end pro_updatesal;
  end pk_first;
  
begin
  ----使用包中的函数
 dbms_output.put_line(pk_first.fun_checkename(7499));
  --调用包中的过程
    pk_first.pro_updatesal(7399);
 end; 
  select * from emp where empno=7499;

  ---------------------------------------------同义词-----------------------------------------------

  /*
    同义词：是现有对象的一个别名，然后存储于数据库中（作为一个对象来使用）
            共有两种类型：私有同义词、共有同义词
            sys有这个权限
    --作用：
    1、简化sql语句
    2、隐藏对象的名称和所有者
    3、提供了对对象的公共访问
    语法：
            create [public] synonym 同义词名称
                   for 模式.对象
  */
  
  grant create synonym to scott;
  
  grant create public synonym to scott;
  ---私有同义词 
  create or replace synonym emp1 for emp;
   
    select * from emp1;
  
  /*
    创建公有同义词的步骤：
    1、由于同义词映射的是某个对象，所以需要对其他普通用户授予对象权限
    2、创建公有同义词
  */
  ----公有同义词 ----用户看不见
  create public synonym public_sy_emp for scott.emp;
  
  grant select on emp to xiyou;
  
    select * from public_sy_emp;
  
  

-------------------------------------------------索引------------------------------------------------------------

/*
  索引：数据库中内部编排数据的方法，默认情况下创建的索引为B-Tree索引
  
         优点：加快访问速度，加强行的唯一性，适合数据的繁琐查询
         缺点：带索引的表需要更多的存储空间，增删改操作将会变得缓慢
  normal：非唯一索引（一般索引），即索引列上的值允许重复，通常用户可以自己定义要建立索引的列，也是默认索引方式
                                                --------
  unique：唯一索引，与非唯一索引最大的区别就是列上增加了一层唯一约束，即列值不允许重复
                                                                              --------
         通常主键或唯一约束建立时，唯一索引将被自动建立
              
  bitmap:位图索引，数据以一种压缩格式存放，因此占用的磁盘空间比标准索引要小的多，
  
  使用索引的原则：
         1、在需要经常搜索的列上创建索引
         2、需要经常排序的列上创建索引
         3、经常用于where字句的列上创建索引
         4、主键或唯一约束建立时，唯一索引将被自动建立 
         
   语法：
    create normal\unique\bitmap index 索引名称 on emp (列名1，列名2···)
           nocompress | compress prefix_length nosort | reverse local
*/
 /*
    1、4、5、6、7、8、9 二分查找
    先排序
 */
  
  --对员工姓名列创建索引


  create index index_ename on emp(ename) nocompress;
------------------------------------------------------
  select * from emp where ename='aaaa';
 
  
  begin
    for i in 1 .. 100 loop
      insert into emp(empno,ename,deptno) values(i,dbms_random.string('w',3),30);
      end loop;
      commit;
    end;
  
  select * from emp where ename like '%G%'; 
  
  delete from emp_back;
  update emp set deptno=30;
  
  
  -------------------------------------------------------------------------------作业
  --1、编写存储过程。根据雇员编号，查询该雇员的姓名和薪水，并通过输出参数输出
 --2创建存储过程 ，交换两个变量的值？通过调用存储过程传入实参，最后输出！
  
  create or replace procedure pro_emp_enameandsal(v_empno in emp.empno%type,v_ename out emp.ename%type,v_sal out emp.sal%type)
  as
  err_notfound exception;
  --未找到记录的错误代码： 100    ------------------加咋找不到的异常
  pragma exception_init(err_notfound,100);
  begin
    
    select ename,sal into v_ename,v_sal from emp where empno=v_empno;
    commit;
    exception 
      when err_notfound then
      dbms_output.put_line('没有找到记录');
  end;
-------测试1
  declare
   v_ename emp.ename%type;
   v_sal emp.sal%type;
  begin
    pro_emp_enameandsal(7499,v_ename,v_sal);
    dbms_output.put_line(v_ename);
    dbms_output.put_line(v_sal);
  end;
  
  
------------------------------------------------------------------------------
  create or replace procedure pro_ab(v_inout_a in out number, v_inout_b in out number)
  as
  v_t number;
  begin
    v_t := v_inout_a;
    v_inout_a := v_inout_b;
    v_inout_b := v_t;
    commit;
  end;
  
 --------------测试
  declare
  v_a number := 2;
   v_b number := 8;
  begin
     pro_ab(v_a,v_b);
    dbms_output.put_line(v_b);
  end;