Oracle学习2 视图 索引 sql编程 游标 存储过程 存储函数 触发器

                --
                -视图
                
--
                -视图的概念：视图就是提供一个查询的窗口，来操作数据库中的数据，不存储数据，数据在表中。
                
--
                -一个由查询语句定义的虚拟表。
                --
                -查询语句创建表
                create
                table emp asselect*from scott.emp;
select*from emp;
---创建视图【必须有dba权限】createview v_emp asselect ename, job from emp;
---查询视图select*from v_emp;
---修改视图数据，其实在修改表数据[不推荐]update v_emp set job=‘CLERK‘where ename=‘ALLEN‘;
commit;
-- 删除视图dropview v_emp;
---创建只读视图createview v_emp1 asselect ename, job from emp withreadonly;
---视图的作用？
---第一：视图可以屏蔽掉一些敏感字段。
---第二：保证总部和分部数据及时统一。---索引
--索引的概念：索引就是在表的列上构建一个二叉树
----达到大幅度提高查询效率的目的，但是索引会影响增删改的效率。
---单列索引
---创建单列索引createindex idx_ename on emp(ename);
---单列索引触发规则，条件必须是索引列中的原始值。
---单行函数，模糊查询，都会影响索引的触发。select*from emp where ename=‘SCOTT‘---复合索引
---创建复合索引createindex idx_enamejob on emp(ename, job);
---复合索引中第一列为优先检索列
---如果要触发复合索引，必须包含有优先检索列中的原始值。select*from emp where ename=‘SCOTT‘and job =‘xx‘;---触发复合索引select*from emp where ename=‘SCOTT‘or job =‘xx‘;---不触发索引select*from emp where ename=‘SCOTT‘;---触发单列索引。---pl/sql编程语言
---pl/sql编程语言是对sql语言的扩展，使得sql语言具有过程化编程的特性。
---pl/sql编程语言比一般的过程化编程语言，更加灵活高效。
---pl/sql编程语言主要用来编写存储过程和存储函数等。---声明方法
---赋值操作可以使用:=也可以使用into查询语句赋值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;


---pl/sql中的if判断
---输入小于18的数字，输出未成年
---输入大于18小于40的数字，输出中年人
---输入大于40的数字，输出老年人declare
  i number(3) :=ⅈ
beginif i<18then
    dbms_output.put_line(‘未成年‘);
  elsif i<40then
    dbms_output.put_line(‘中年人‘);
  else
    dbms_output.put_line(‘老年人‘);
  endif;
end;

declare
   i number(2) :=28;
beginif i <18then
    dbms_output.put_line(‘child‘);
     elsif i <25then
        dbms_output.put_line(‘teenager‘);
       else
          dbms_output.put_line(‘old‘);
       endif;
end;
---pl/sql中的loop循环
---用三种方式循环打印1到10
---while循环declare
    i number(2) :=0;
beginwhile i <10 loop
        dbms_output.put_line(i +1);
        i := i +1;
    end loop;
end;
---exit循环declare
  i number(2) :=1;
begin
  loop
    exitwhen i>10;
    dbms_output.put_line(i);
    i := i+1;
  end loop;
end;
---for循环declarebeginfor i in1..10 loop
     dbms_output.put_line(i);  
  end loop;
end;

---游标：可以存放多个对象，多行记录。
---游标：通过游标可以操作存储查询结果集。
---游标输出emp表中所有员工的姓名declarecursor c isselect*from emp;
    emprow emp%rowtype;
beginopen c;
        loop
            fetch c into emprow;
            exitwhen c%notfound;
            dbms_output.put_line(emprow.empno ||‘‘|| emprow.ename);
        end loop;
    close c;
end;

-----给指定部门员工涨工资declarecursor c(dno emp.deptno%type)
    isselect*from emp where deptno=dno;
    r emp%rowtype;
beginopen c(10);
        loop
            fetch c into r;
            exitwhen c%notfound;
                update emp set sal = sal +100where deptno=r.deptno and ename=r.ename;
                commit;
        end loop;
    close c;
end;
----查询10号部门员工信息select*from emp where deptno =10;


---存储过程
--存储过程：存储过程就是提前已经编译好的一段pl/sql语言，放置在数据库端
--------可以直接被调用。这一段pl/sql一般都是固定步骤的业务。
----给指定员工涨100块钱createorreplaceprocedure p1(eno emp.empno%type)
isbeginupdate emp set sal=sal+100where empno = eno;
   commit;
end;

select*from emp where empno =7788;
----测试p1declarebegin
  p1(7788);
end;

----通过存储函数实现计算指定员工的年薪
----存储过程和存储函数的参数都不能带长度
----存储函数的返回值类型不能带长度createorreplacefunction f_yearsal(eno emp.empno%type) returnnumberis
  s number(10);     
beginselect sal*12+nvl(comm, 0) into s from emp where empno = eno;
  return s;
end;

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

---out类型参数如何使用
---使用存储过程来算年薪createorreplaceprocedure p_yearsal(eno emp.empno%type, yearsal out number)
is
   s number(10);
   c emp.comm%type;
beginselect sal*12, nvl(comm, 0) into s, c from emp where empno = eno;
   yearsal := s+c;
end;

---测试p_yearsaldeclare
  yearsal number(10);
begin
  p_yearsal(7788, yearsal);
  dbms_output.put_line(yearsal);
end;

----in和out类型参数的区别是什么？
---凡是涉及到into查询语句赋值或者:=赋值操作的参数，都必须使用out来修饰。---存储过程和存储函数的区别
---语法区别：关键字不一样，
------------存储函数比存储过程多了两个return。
---本质区别：存储函数有返回值，而存储过程没有返回值。
----------如果存储过程想实现有返回值的业务，我们就必须使用out类型的参数。
----------即便是存储过程使用了out类型的参数，起本质也不是真的有了返回值，
----------而是在存储过程内部给out类型参数赋值，在执行完毕后，我们直接拿到输出类型参数的值。----我们可以使用存储函数有返回值的特性，来自定义函数。
----而存储过程不能用来自定义函数。
----案例需求：查询出员工姓名，员工所在部门名称。
----案例准备工作：把scott用户下的dept表复制到当前用户下。createtable dept asselect*from scott.dept;
----使用传统方式来实现案例需求select e.ename, d.dname
from emp e, dept d
where e.deptno=d.deptno;
----使用存储函数来实现提供一个部门编号，输出一个部门名称。createorreplacefunction fdna(dno dept.deptno%type) return dept.dname%type
is
  dna dept.dname%type;
beginselect dname into dna from dept where deptno = dno;
  return dna;
end;
---使用fdna存储函数来实现案例需求：查询出员工姓名，员工所在部门名称。select e.ename, fdna(e.deptno)
from emp e;


---触发器，就是制定一个规则，在我们做增删改操作的时候，
----只要满足该规则，自动触发，无需调用。
----语句级触发器：不包含有for each row的触发器。
----行级触发器：包含有for each row的就是行级触发器。
-----------加for each row是为了使用:old或者:new对象或者一行记录。---语句级触发器
----插入一条记录，输出一个新员工入职--应用场景：1.下订单用触发器修改库存 2.帖子评论增删时，修改帖子的评论数。createorreplacetrigger t1
after
inserton person
declarebegin
  dbms_output.put_line(‘一个新员工入职‘);
end;
---触发t1insertinto person values (1, ‘小红‘);
commit;
select*from person;

---行级别触发器
---不能给员工降薪
---raise_application_error(-20001~-20999之间, ‘错误提示信息‘);createorreplacetrigger t2
before
updateon emp
for each row
declarebeginif :old.sal>:new.sal then
     raise_application_error(-20001, ‘不能给员工降薪‘);
  endif;
end;
----触发t2select*from emp where empno =7788;
update emp set sal=sal-1where empno =7788;
commit;


----触发器实现主键自增。【行级触发器】
---分析：在用户做插入操作的之前，拿到即将插入的数据，
------给该数据中的主键列赋值。createorreplacetrigger auid
before
inserton person
for each row
declarebeginselect s_person.nextval into :new.pid from dual;
end;
--查询person表数据select*from person;
---使用auid实现主键自增insertinto person (pname) values (‘a‘);
commit;
insertinto person values (1, ‘b‘);
commit;


----oracle10g    ojdbc14.jar
----oracle11g    ojdbc6.jar