oracle基础学习第二天

           
           
           --- oracle第二天练习
           

--视图
drop table emp;
create table emp as select * from scott.emp;


-- 创建视图
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_emp2 as select ename,job from emp with read only;
-- 视图作用:
   -- 视图可以屏蔽掉一些敏感字段
   -- 视图可以保持总部和分部数据及时统一
   
   
-- 索引的概念：索引就是在表的列上构造一个二叉树
-- 达到大幅度提高查询效率的目的，但是索引会影响增删改的效率
-- 单行函数、模糊查询都会影响索引的触发

create index idx_enam 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';-- 触发单列索引

-- plsql编程语言是对sql语言的扩展，使得sql语言具有过程化编程的特性

-- plsql基本语法
declare 
   i number(3) := 100;
   str varchar2(10) := '戴灵飞';
   ename emp.ename%type;
   emprow emp%rowtype;
begin 
 -- 变量的遍历 
  dbms_output.put_line(i);
  dbms_output.put_line(str);
 -- 赋值
  select ename into ename from emp where empno = 7788;
  dbms_output.put_line(ename);
 
 -- 对象赋值
  select * into emprow from emp where empno = 7788;
  dbms_output.put_line(emprow.ename || '的工作为:' || emprow.job);
end;

-- plsql的if判断

declare

-- 变量键盘录入
   i number(4) := ⅈ
   
begin
  
  dbms_output.put_line(i);
  if i<18 then
    dbms_output.put_line('未成年人');
    elsif i<40 then
      dbms_output.put_line('青年人');
      else
        dbms_output.put_line('中年人');
        end if;
end;


----------------------------  plsql中的循环loop  ----------------------------
-- while循环
declare 
   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 
  for i in 1..10 loop
  dbms_output.put_line(i);
  end loop;
end;



----------------------------  游标的使用   ------------------------------
---输出emp表中所有员工的姓名
declare
  cursor c1 is select * from emp;
  emprow emp%rowtype;
begin
  open c1;
    loop
      -- fetch取来，拿来
      fetch c1 into emprow;
      exit when c1%notfound;
      dbms_output.put_line(emprow.ename);
      end loop;
  close c1;
end;


-----给10号部门员工涨工资

declare
  cursor c2(eno emp.empno%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+1000 where empno = en;
         end loop;
  close c2;
end;

select * from emp where deptno = 10;


---存储过程
--- 存储过程就是提前编译好一段plsql代码，放置在服务端可以直接被调用。这段plsql都是固定
--- 的业务

-- 案例
-- 给指定员工涨工资
create or replace procedure p_updateSal(eno emp.empno%type , updateSal number)
 is
 
begin 
  update emp set sal = sal + updateSal where empno  = eno;
  commit;
end;

-- 测试涨工资
select sal from emp where empno = 7788;

declare
   
begin
  p_updateSal(7788 , 2000);
end;

----通过存储函数实现计算指定员工的年薪
drop function f_getyealsal;

create or replace function f_getYearSal(eno emp.empno%type) return number
is
       res number(10);
begin 
  select sal*12+nvl(comm, 0 ) into res from emp  where empno = eno;
  return res;
end;


-- 测试存储函数f_getyearsal
-- 存储函数在调用的时候，返回值需要接受
declare
   yearSal number(10);
begin
  yearSal := f_getYearSal(7788);
  dbms_output.put_line('7788工号的年薪是：' || yearSal);
end;
 
---out类型参数如何使用
---使用存储过程来算年薪

create or replace procedure p_getyearsal(eno emp.empno%type,yearSal out number)
 is
   commonSal number(10);
   common emp.comm%type;
begin
   select e.sal*12,nvl(e.comm , 0) into commonSal , common from emp e where empno = eno;
   yearSal := commonSal+common;    
end;

-- 测试存储过程（p_getyearsal）来实现计算年薪
declare
   yearsal number(10);
begin
  p_getyearsal(7788 , yearsal);
  dbms_output.put_line(yearsal);
end;


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

-- 存储过程和存储函数的区别
-- 语法区别：
--          1.关键字不同
--          2.存储函数有两个return
-- 本质区别：1.存储函数有返回值，而存储过程没有返回值
         --  如果存储过程想拥有返回值的业务，必须使用out类型参数
         --  即便是存储过程使用来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;

-- 通过存储函数给定一个部门id,来返回相应的部门名称
create or replace function f_getDeptName(dno emp.deptno%type) return dept.dname%type
 is
       dna dept.dname%type;
begin
  select dname into dna from dept where deptno = dno;
  
  return dna;
end;

-- 使用存储函数实现：查询出员工姓名，员工所在部门名称。
select e.ename 员工,f_getDeptName(e.deptno) 相关部门 from emp e;



-- 触发器，就是自定义的一种规则，在程序执行的时候，只要满足
-- 规则，自动触发，无须调用
-- 触发器分为：
-- 语句触发器：不包含for each row的触发器
-- 行级触发器：包含for each row 的就是行级触发器

-- 语句触发器
create or replace trigger t_insert
 after insert on person

declare 

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

-- 测试语句触发器
insert into person(pid , name) values(14 , '小红');
commit;

-- 行级触发器
create or replace trigger t_update
before update on emp
for each row
declare

begin
  if :old.sal > :new.sal then
    raise_application_error(-20001 , '不能给员工降工资！！！');
    end if;
end;

-- 测试行级触发器
-- 禁止给员工降薪
update emp set sal = sal - 1 where empno = 7788;
commit;

-- 查询是否降薪
select sal from emp where empno = 7788;

-- 通过触发器实现主键自增
create or replace trigger auto_id 
before insert on person
for each row
  
declare

begin
  select person_id.nextval into :new.pid from dual;
end;

-- 测试行级触发器
insert into person(name) values('小红');
commit;

-- 查看是否插入数据，主键是否实现自增
select p.pid , p.name from person p;
 
 
-- 连接oracle的相应jar包
-- oracle10g --> ojdbc14.jar
-- oracle11g --> ojdbc6.jar