自学Oracle数据库day02

安吉尔

--视图
  --视图的概念：视图就是提供一个查询窗口，所有数据来自于原表
  --查询语句创建表
  create table emp as select * from scott.emp;
  select * from emp;
  --创建视图【必须有dba权限】
  create view v_emp as select ename,job from emp;
  --查询视图
  select * from v_emp;
--修改视图【不推荐】
update v_emp set job='CLERK' where ename='ALLEN';
commit;
--创建只读视图
create view v_emp1 as select ename,job from emp with read only;
--视图的作用？
--第一：视图可以屏蔽掉一点敏感字段。
--第二：保证总部和分部的数据及时统一。

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

--单列索引
--创建单列索引
create index idx_ename on emp(ename);
--单列索引触发规则，条件必须是索引列中的原始值。
--单行函数，模糊查询，都会影响索引的触发。
select * from emp where ename='SCOTT';
--复合索引
create index 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编程语言主要用来编写存储过程和存储函数等。

--声明方法 int只能存整数  
--赋值操作可以使用:=也可以使用into查询语句来赋值
declare
       --定义一个数值类型
       i number(2) :=10;
       s varchar2(10) :='小明';
       --想当于在emp表找到enamel这个列，把它的类型复制粘贴到这个地方
       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) :=ⅈ
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;

--pl/sql中的loop循环
--用三种方式输出1到10十个数字
--while循环
declare
       --定义一个number类型
       i number(2) :=1;
begin
  --满足什么条件的情况下循环
  while i<11 loop
    dbms_output.put_line(i);
    i :=i+1;
  end loop;
end;

--exit循环,一上来就循环
declare
  i number(2) :=1;
begin
  loop
    exit when i>10;
    dbms_output.put_line(i);
    i :=i+1;
  end loop;
end;

--for循环
declare

begin
  --在1-10的情况下进行循环
  for i in 1..10 loop
    dbms_output.put_line(i);
  end loop;
end;
--游标，可以存放多个对象，多行记录。
--输出emp表中所有员工的姓名
declare
  --定义了一个游标为c1的游标，而且把emp表中所有的记录存放到游标中                 
  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;

--给指定部门员工涨工资
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;
--查询10号部门员工信息
select * from emp where deptno=10;

--存储过程：存储过程就是提前已经编译好的一段pl/sql语言，放置在数据库端
--------可以直接被调用。这一段pl/sql一般都是固定步骤的业务。
--创建存储过程的语法：
--create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]
--AS
--begin
         --PLSQL子程序体;
--end;

----给指定员工涨100块钱
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;

--通过存储函数实现计算指定员工的年薪
--存储过程和存储函数的参数都不能带长度
--存储函数的返回值类型不能带长度
--                                    参数  数据类型              数据类型(不能带长度)
create or replace function f_yearsal(eno emp.empno%type) return number
is
  s number(10);
begin
  select sal*12+nav(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类型参数如何使用
--使用存储过程来算年薪
                                                         --输出类型参数
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,nav(comm,0) into s,c from emp where empno = eno;
  yearsal :=s+c;
end;

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

---in和out类型参数的区别是什么？
--凡是涉及到 into查询语句赋值或者 :=赋值操作的参数，都必须使用out来修饰

---存储过程和存储函数的区别
---语法区别：关键字不一样，
------------存储函数比存储过程多了两个return。
---本质区别：存储函数有返回值，而存储过程没有返回值。
----------如果存储过程想实现有返回值的业务，我们就必须使用out类型的参数。
----------即便是存储过程使用了out类型的参数，起本质也不是真的有了返回值，
----------而是在存储过程内部给out类型参数赋值，在执行完毕后，我们直接拿到输出类型参数的值。

----我们可以使用存储函数有返回值的特性，来自定义函数。
----而存储过程不能用来自定义函数。
----案例需求：查询出员工姓名，员工所在部门名称。
----案例准备工作：把scott用户下的dept表复制到当前用户下。

create table dept as select * from scott.dept;
----使用传统方式来实现案例需求
select e.ename, d.dname
from emp e, dept d
where e.deptno=d.deptno;
----使用存储函数来实现提供一个部门编号，输出一个部门名称。
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;
---使用fdna存储函数来实现案例需求：查询出员工姓名，员工所在部门名称。
select e.ename, fdna(e.deptno)
from emp e;

---触发器，就是制定一个规则，在我们做增删改操作的时候，
----只要满足该规则，自动触发，无需调用。
----语句级触发器：不包含有for each row的触发器。
----行级触发器：包含有for each row的就是行级触发器。
-----------加for each row是为了使用:old或者:new对象或者一行记录。

---语句级触发器
----插入一条记录，输出一个新员工入职
create or replace trigger t1
after
insert
on person
declare

begin
  dbms_output.put_line('一个新员工入职');
end;
---触发t1
insert into person values (1, '小红');
commit;
select * from person;

---行级别触发器
---不能给员工降薪
---raise_application_error(-20001~-20999之间, '错误提示信息');
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;
----触发t2
select * from emp where empno = 7788;
update emp set sal=sal-1 where empno = 7788;
commit;

----触发器实现主键自增。【行级触发器】
---分析：在用户做插入操作的之前，拿到即将插入的数据，
------给该数据中的主键列赋值。
create or replace trigger auid
before
insert
on person
for each row
declare

begin
  select s_person.nextval into :new.pid from dual;
end;
--查询person表数据
select * from person;
---使用auid实现主键自增
insert into person (pname) values ('a');
commit;
insert into person values (1, 'b');
commit;

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