数据库基础知识点二：PLSQL、游标、程序包、存储过程、触发器，序列，视图，索引，数据字典

一、PLSQL操作：

-- PLSQL====逻辑代码+sql语句   

    -- 过程代码结合sql语句语言

  --处理业务复杂的逻辑(不同部门修改薪水)

  --一次编译 反复调用执行   提升性能.

 

-- plsql的基本结构

  declare

    --声明部分

    v_salary number(7,2):=8000.5;

    v_job varchar2(20):='程序员';

   

   

  begin

    --  psql主体部分 核心语句

    --重新赋值

    v_salary :=9000.8;

    v_job:='开发工程师';

    dbms_output.put_line('薪水:'||v_salary||'职位:'||v_job);

  end;

 

 

 

  declare

     v_age emp.age%type;

     v_ename emp.ename%type;

  begin

     -- into 给变量赋值

     select age,ename into v_age,v_ename  from emp where empno=1001;

  dbms_output.put_line(v_ename||':'||v_age);

  end;

-- plsql的基本结构 变量声明赋值

  --变量名 类型;       :=

  --变量名%type;      into

  --变量名%rowtype;

declare

  v_emp  emp%ROWTYPE;

  --声明常量   constant

  v_rate constant number(7,2):=3.14;

begin

  select * into v_emp  from emp where empno=1001;

  dbms_output.put_line('v_rate:'||v_rate);

 dbms_output.put_line(v_emp.empno||v_emp.ename||v_emp.job||v_emp.age);

end;

 

--  plsql语句结构

 declare

   v_count number:=1;

 begin

   loop

       dbms_output.put_line('v_count'||v_count);

       v_count:=v_count+1;

       if v_count>10 then

          --循环退出

           goto end2;

       elsif v_count>5   then

             v_count:=v_count+2;

       else

         --充当语法结构的

          null;      

        end if;

   end loop;

     <<end2>>

     dbms_output.put_line('v_count'||v_count);

 end;

 

 --  plsql异常怎么解决?

  --三种异常

    --a.预定义异常  DUP_VAL_oN_INDEX  不要声明 只需要处理

   declare

  

   begin

       insert into emp(empno,ename,job)

        values('1004','王二麻子','程序员');

   exception

      when DUP_VAL_oN_INDEX  then

        dbms_output.put_line('已经有改员工了!!!!!!');

       when others then

         dbms_output.put_line('其他异常');

   end;

 

    --b.非预定义异常  需要声明 不显示引发需要处理

      declare

        --违背主外键约束  -2291

        v_ex exception;

         --指定错误号

        PRAGMA EXCEPTION_INIT(v_ex,-2291);

  

      begin

        update emp set dno=100 whereempno=1004;

         commit;

     

      exception

        when v_ex then

          dbms_output.put_line('部门中没有对应的部门号!!!');

         when others  then

           dbms_output.put_line('其他异常');

      end;   

   

--c.自定义异常 完全自己定义 需要声明   显示引发  需要处理

declare

  v_ex2 exception;--声明定义异常

  v_bonus emp.bonus%type;

begin

 select bonus into v_bonus from emp whereempno=1001;

  dbms_output.put_line('v_bonus'||v_bonus);

 if v_bonus is null then

   --手动引发异常

    raise v_ex2;

 end if;

 

exception

   when v_ex2 then  

     dbms_output.put_line('改员工没有奖金!!!');

    when others then

     dbms_output.put_line('其他异常!!');

end;

 

--plsqldevloper工具导出导入数据?

  --可以导入导出表的结构以及数据

二、游标：

-- 游标Cursor 显示游标1

declare

  --1.声明游标

    Cursor emp_cur

      is  select ename,job from emp;

   v_ename emp.ename%type;

   v_job emp.job%type;

begin

  --2.打开游标

    if not emp_cur%isopen then

      open emp_cur;

    end if;

 

  --3.提取游标中的缓存数据

  loop

     fetch emp_cur  into v_ename,v_job;

     --游标执行那行没有数据了 循环结束

     exit when emp_cur%notfound;

     dbms_output.put_line('第'||emp_cur%rowcount||'行的姓名:'||v_ename||'职位是:'||v_job);

  end loop;

  --4.关闭游标

   close emp_cur;

end;

 

--显示游标2

   --基于游标的 记录变量 

 declare

    cursor emp_cur2 is

      select ename,job,salary,age from empwhere dno=10;

   v_emp emp_cur2%rowtype;

 begin

    open emp_cur2;

   loop

    fetch emp_cur2 into  v_emp;

    exit when emp_cur2%notfound;

     dbms_output.put_line('第'||emp_cur2%rowcount||'个的姓名是:'||v_emp.ename||'薪水是'||v_emp.salary||'年龄是:'||v_emp.age);

   end loop;

   close emp_cur2;  

 end;

--显示游标3

  -- 带参数游标

 DECLARE

CURSOR emp_cur3(no NUMBER) IS

select ename,salary FROM emp WHERE dno=no;

emp_record emp_cur3%ROWTYPE;

 

BEGIN

IF NOT emp_cur3%ISOPEN THEN

OPEN emp_cur3(50);

END IF;

LOOP

FETCH emp_cur3 INTO emp_record;

EXIT WHEN emp_cur3%NOTFOUND;

dbms_output.put_line(emp_record.ename||':'||emp_record.salary);

END LOOP;

CLOSE emp_cur3;

END;

-- 显示游标4

  --通过游标对表的数据进行删除或者修改

  declare

    cursor emp_cur4 is

     select ename,salary,dno from emp forupdate;

     v_emp emp_cur4%rowtype;

  begin

     open emp_cur4;

     loop 

       fetch emp_cur4  into v_emp;

       exit when emp_cur4%notfound;

       if v_emp.dno=10 and v_emp.salary isnull  then

         delete from emp where current ofemp_cur4;

       end if;

     end loop;    

  close emp_cur4;    

  end;

 

--显示游标5

  --循环游标 不需要打开直接使用

  declare

     cursor emp_cur5 is

      select ename,salary,job from emp;

  begin

   for some in emp_cur5 loop

    dbms_output.put_line(some.ename||some.salary);

   end loop;

  end;

--存储过程:可以重复调用执行

  -- 有名字的plsql. plsql没办法重复利用

  --  业务相对复杂(sql+逻辑代码)

  --提升数据库的 性能 一次编译后 反复调用执行

 

 

--存储过程1

create  or replace procedure addemp_proc(v_eno number,v_ename varchar2,v_jobvarchar2,v_salary number,v_age number)

as

  --声明过程 的中间变量

  begin

    insert into emp(empno,ename,job,salary,age)

    values(v_eno,v_ename,v_job,v_salary,v_age);

   commit;

 end addemp_proc;

 --通过plsql调用存储过程

   declare 

   begin

    addemp_proc(1004,'王二麻子','程序员',12345,23);

   end;

  

 --存储过程2

   create or replace procedure deleteemp_proc(v_eno number)

    as

    --声明变量

    begin

      delete from emp where empno=v_eno;

     commit;

    end deleteemp_proc;

 --  plsql调用过程

   declare

  

   begin

     deleteemp_proc(1004);

   end;

  --存储过程3

   create or replace  procedure updateemp_proc(v_eno number)

    as

    --声明变量

    begin

      update emp setsalary=salary+1000,bonus=bonus+500 where empno=v_eno;

      commit;

    end updateemp_proc;

     select * from emp;

 --plsql调用过程

   declare

  

   begin

   updateemp_proc(1002); 

   end;

  

--存储过程4

 create or replace procedure selectone_proc(v_eno in number)

 as

  v_name emp.ename%type;

  v_job emp.job%type;

 begin

   select ename,job into v_name,v_job from empwhere empno=v_eno;

   dbms_output.put_line(v_name|| v_job);

 end selectone_proc;

 -- plsql调用过程

 declare

 

 begin

   selectone_proc(1001);

 end;

 

 --过程中的三种参数 的模式

   -- a.输入参数in 默认的

   -- b.输出参数  out  往外返回值的

   -- c.输入 输出 参数 in out

   

    --带有输出参数模式的过程

  create or replace procedure  countemp_proc(v_dno  number,v_count  out number)

    as

     

    begin

     select count(*) into v_count from empwhere dno=v_dno;

    end countemp_proc;

 -- plsql调用过程

   declare

    v_c number;

   begin

    countemp_proc(50,v_c);

    dbms_output.put_line(v_c);  

   end;  

 

 -- 传递俩个参数 交换对应的值

create  or replace procedure swap_proc(v_a in outnumber,v_b in out number)

as

  v_t number;

begin

   v_t:=v_a;

   v_a:=v_b;

   v_b:=v_t;

 

endswap_proc;

 

-- plsql调用过程

   declare

   v_a number:=10;

   v_b number:=20;

   begin

    dbms_output.put_line(v_a||':'||v_b);

    swap_proc(v_a,v_b);

    dbms_output.put_line(v_a||':'||v_b);

   end;

三、程序包：

--程序包:

  --程序包规范(声明 定义  )

  ---程序包主体(具体实现)

 

-- 创建程序包规范

  create or replace package emp_package

  is

    minsal emp.salary%type;

    maxsal emp.salary%type;

    --    1.添加雇员信息

     procedure add_employee(v_empno  number,v_ename varchar2,v_salary number,v_dnonumber);

    --    2.通过雇员编号修改薪水

    procedure upd_sal(v_empno number,v_salarynumber);

    --    3.通过雇员名称修改薪水

    procedure upd_sal(v_ename varchar2,v_salarynumber);

    --    4.通过雇员编号查询薪水

    function get_sal(v_empno number) returnnumber;

endemp_package;

 --创建程序包主体

 create or replace  package bodyemp_package

 as

 

   --实现规范中的函数和过程 实现过程1

  procedure add_employee(v_empno  number,v_ename varchar2,v_salary number,v_dnonumber)

IS

begin

     if v_salary between minsal and maxsal  then

        insert into emp(empno,ename,salary,dno)values (v_empno,v_ename,v_salary,v_dno);

        commit;

     end if;

 end;

 

 -- 实现过程2

    procedure upd_sal(v_empno number,v_salarynumber)

IS

begin

    if v_salary between minsal and maxsal  then

              update emp set salary=v_salary  where empno=v_empno;

            

     end if;

end;

    --实现过程3

  procedure upd_sal(v_ename varchar2,v_salarynumber)

IS

begin

     if v_salary between minsal and maxsal  then

            update emp setsalary=v_salary where UPPER(ename)=UPPER(v_ename);

     end if;    

end;

   function get_sal(v_empno number) returnnumber

is

v_salemp.salary%type;

begin

     select salary into v_sal from emp whereempno=v_empno;

     return v_sal;

end;

  begin

    select max(salary),min(salary) intomaxsal,minsal from emp;

 end emp_package;

-- plsql调用程序包的函数和过程

declare

   v_sal number;

begin

   v_sal:=emp_package.get_sal(1002);

  emp_package.add_employee(1005,'李莫愁',32345,50);

   dbms_output.put_line(v_sal);

end;

 

四、存储过程：

--存储过程: 结合游标

 

 create or replace procedure selectAll_proc(v_dno  number)

 as

   Cursor emp_cur6 is

      select * from emp where dno=v_dno;

   v_emp emp_cur6%rowtype;

 

 begin

    open emp_cur6;

     loop

       fetch emp_cur6 into   v_emp;

       exit when emp_cur6%notfound;

      dbms_output.put_line(v_emp.ename||v_emp.salary);

     end loop;

   

    close emp_cur6;   

 end selectAll_proc;

 

 -- plsql 调用过程

   declare

  

   begin

   selectAll_proc(10);

  

   end;

 -- oracle中 有自带游标 

   create or replace procedure selectAll_proc2(v_dno in number,sys_cur out SYS_REFCURSOR)

   as

   begin

    open sys_cur for select * from emp wheredno=v_dno;

   end selectAll_proc2;

   

  --调用执行过程

    declare

       emp_cur7 sys_refcursor;

       v_emp emp%rowtype;

        

    begin

     selectAll_proc2(10,emp_cur7);

    

     loop

       fetch emp_cur7 into v_emp;

       exit when  emp_cur7%notfound;

      dbms_output.put_line(v_emp.ename||v_emp.job);

     end loop;

    end;

    --   oracle中的自定义函数(了解)

     create or replace  function get_avgsal_func(v_dno number)

      return number

      as

      --声明函数中间变量

      v_avg number;

      begin

       select avg(salary) into v_avg from empwhere dno=v_dno;

       return v_avg;

     end;

五、触发器，序列，视图，索引，数据字典

--复制表结构

 --创建审计表(记录emp中删除掉的数据)

  create table del_emp

   as

 select * from emp where 1=2;

--创建触发器(trigger)

create  or replace trigger del_emp_trigger

before  delete on emp --触发事件

for  each  row -- 触发类型(行级触发器)

when  (old.dno <>50)--触发器的条件限制

begin

  --触发器的主体操作(触发器执行后做什么)

   --将emp表中删除掉的数据备份在审计表中(del_emp)

  insert into del_emp

  values(:old.empno,:old.ename,:old.job,:old.salary,:old.bonus,:old.hiredate,:old.sex,:old.age,:old.dno);

end;

 

 

 

 

 

--=======================序列(Sequence)

  --为了实现唯一标识的 自增的

  -- 一般用于主键自增的 auto_increment

   --创建序列??

 create sequence my_4b_seq

    start with 1  increment by 2;

  

 create table test_seq(id number primarykey,name varchar2(20));

 insert into test_seq values

 (my_4b_seq.nextval,'ccc');

 select * from test_seq;

 delete from test_seq;

 

 select my_4b_seq.nextval from dual;

 -- hibernate中的主键策略(assigned increment   nativesequence.....)

  -- hibernate_sequence(默认的序列名)

  --<id name="id"column="id">

  -- <genaratorclass="sequence">

  --   <param name="sequence">my_4b_seq</param>

  -- </genarator>

  -- </id>

  -- 如果没有写increment默认是自增1  

  --没有写startwith从1开始

 create sequence my_4b_2_seq;

 

 

 

--视图(view)可以理解为:有名字的结果集

  --本质就是一条select语句 依赖表 虚表  

  --控制某些列的访问

  --提升性能

  --简化查询

 

 --表被drop了 视图就没有意义(非法了)了.

  -- drop  view视图名;

 

--创建视图

   create view my_4b_view

   as

    select empno,ename,job,age,sex,hiredate,dnofrom emp;

  

   --简单视图(单表)

 create view my_4b_2_view

    as    

 select avg(salary)avg_sal from emp wheredno=10; 

-- 复杂视图(多表)

createview my_4b_3_view

 as

selectd.dname name,count(e.empno) count

  from emp e,dept d where

   e.dno=d.dno group by d.dname;

 

 

-- ===================索引:index

  --提升查询性能的(降低查询的次数)

  --索引不是万能的 

 

  -- 索引的分类:列 表达式 函数

  --  B树(二叉树)索引

  -- 位图索引(位图机制)

  -- 唯一索引(主键列默认加上了唯一索引)

 

  --创建索引(树索引)

  create index my_index_2 on  emp(job);

     

--索引的使用场景(原则)

  -- 表的基数很大 但是select的数据量很少(<15%)

  -- 表的数据全部导入了 以后很少做修改操作

  -- 限制索引的数量不是越多越好 因为表的数据更新

   --  索引也需要同时做维护.

  -- 经常用于查询的列上或者表达式上

 

  --数字字典(特殊表)

     select * from user_tables where table_name='EMP';

    select * from user_objects whereobject_type='INDEX';