Oracle(四)

/*
  序列：Oracle使用来模拟ID自动增长
*/
create sequence seq_test4;

create table test2(
       tid number primary key,
       tname varchar2(10)
);

insert into test2 values(seq_test4.nextval,'张三');
select * from test2;

---

 

/*
   游标(光标)：用来操作查询结果集，相当于JDBC中ResultSet
   语法：cursor 游标名[(参数名  参数类型)] is 查询结果集
   
   开发步骤：
       1、声明游标
       2、打开游标          open游标名
       3、从游标中取数据    fetch 游标名 into 变量
                    游标名%found：找到数据
                    游标名%notfound：没有找到数据
       4、关闭游标       close 游标名
   系统引用游标
       1、声明游标：游标名 sys_refcursor
       2、打开游标：open 游标名 for 结果集
       3、从游标中取数据
       4、关闭游标
       
   for循环遍历游标：
       不需要声明额外变量
       不需要打开游标
       不需要关闭游标
*/
--输出员工表中所有的员工姓名和工资(不带参数游标)
/*
游标：所有员工
声明一个变量，用来记录一行数据  %rowtype
*/
declare
  --游标
  cursor vrows is select * from emp;
  --声明变量，记录一行数据
  vrow emp%rowtype;
begin
  --1、打开游标
  open vrows;
  --2、从游标中取数据
  --循环游标
  loop
    fetch vrows into vrow;
    exit when vrows%notfound;
    dbms_output.put_line('姓名：'||vrow.ename||'工资：'||vrow.sal);
  end loop;
  --3、关闭游标
  close vrows;
end;

--输出指定部门下的员工姓名和工资
/*
游标：制定部门下的员工
声明一个变量记录一行数据
*/
declare
  --声明游标
  cursor vrows(dno number) is select * from emp where deptno=dno;
  --声明变量
  vrow emp%rowtype;
begin
  --1、打开游标,指定10号部门
  open vrows(10);
  --2、从游标中取数据
  --循环游标
  loop
    fetch vrows into vrow;
    exit when vrows%notfound;
    dbms_output.put_line('姓名：'||vrow.ename||'工资：'||vrow.sal);
  end loop;
  --3、关闭游标
  close vrows;
end;

--系统引用游标
--输出员工表中所有的员工姓名和工资
declare
  --声明系统游标
  vrows sys_refcursor;
  --声明变量
  vrow emp%rowtype;
begin
  --1、打开游标
  open vrows for select * from emp;
  --2、从游标中取数据
  --循环游标
  loop
    fetch vrows into vrow;
    exit when vrows%notfound;
    dbms_output.put_line('姓名：'||vrow.ename||'工资：'||vrow.sal);
  end loop;
  --3、关闭游标
  close vrows;
end;

--扩展内容--只用for循环遍历游标
declare
  --声明游标
  cursor vrows is select * from emp;
begin
  for vrow in vrows loop
    dbms_output.put_line('姓名：'||vrow.ename||'工资：'||vrow.sal);
  end loop;
end;

select * from emp;

--按照员工工作给所有员工涨工资,总裁涨1000，经理涨800，其他人涨400
/*
游标：所有员工
声明一个变量记录仪行数据
*/
declare
  --声明游标
  cursor vrows is select * from emp;
  --声明变量
  vrow emp%rowtype;
begin
  --1、打开游标
  open vrows;
  --2、循环游标取数据
  loop
    --取数据
    fetch vrows into vrow;
    --退出条件
    exit when vrows%notfound;
    if vrow.job='PRESIDENT' then
      update emp set sal=sal+1000 where empno=vrow.empno;
    elsif vrow.job='MANAGER' then
      update emp set sal=sal+800 where empno=vrow.empno;
    else
      update emp set sal=sal+400 where empno=vrow.empno;
    end if;
  end loop;
  --3、关闭游标
  close vrows;
  --4、提交事务
  commit;
end;

---

/*
  例外：(意外)程序运行的过程发生异常，相当于是java中的异常
  
  declare
    --声明变量
  begin
    --业务逻辑
  exception
    --处理异常
    when 异常1 then
      ...
    when 异常2 then
      ...
    when others then
      ...处理其他异常
  end;
  
  zero_divide:除零异常
  value_error:类型转换异常
  too_many_rows:查询出多行记录，但是赋值给了rowtype记录一行数据变量
  no_data_found:没有找到数据
  
  自定义异常：
      异常名 exception
      raise 异常名
*/
declare
  vi number;
  vrow emp%rowtype;
begin
  --vi := 8/0;--zero_divide
  --vi := 'aaa';--value_error
  --select * into vrow from emp;--too_many_rows
  select * into vrow from emp where empno=123456;
exception
  when zero_divide then
    dbms_output.put_line('发生了除零异常');
  when value_error then
    dbms_output.put_line('发生了除零异常');
  when too_many_rows then
    dbms_output.put_line('查询出多行记录，但是赋值给了rowtype记录一行数据变量');
  when no_data_found then
    dbms_output.put_line('没有找到数据');
  when others then
    dbms_output.put_line('发生了其他异常');
end;

--查询指定编号的员工，如果没有找到，则抛出自定义的异常
/*
    --错误的演示
    
    1.声明一个变量 %rowtype
    2.查询员工信息,保存起来
    3.判断员工信息是否为空
    4. 如果是 则抛出异常
*/
declare
    --1、声明一个变量
    vrow emp%rowtype;
    --2、声明一个自定义的异常
    no_emp exception;
begin
  --查询员工信息，保存起来
  select * into vrow from emp where empno=8888;--抛出异常 
  if vrow.sal is null then
    raise no_emp;--抛出自定义的异常
  end if;
exception
  when no_emp then
    dbms_output.put_line('输出了自定义的异常');
  when others then
    dbms_output.put_line('输出了其他异常'||sqlerrm);
end;

--查询指定编号的员工,如果没有找到,则抛出自定义的异常
/*
     游标来判断
       %found %notfound
    声明一个游标
    声明一个变量,记录数据
    从游标中取记录
       如果有,则不管它
       如果没有就抛出自定义的异常
*/
declare
  --声明游标
  cursor vrows is select * from emp where empno=8888;
  --声明一个记录型变量
  vrow emp%rowtype;
  --声明一个自定义异常
  no_emp exception;
begin
  --1、打开游标
  open vrows;
  --2、从游标中取数据
  fetch vrows into vrow;
  --3、判断游标是否有数据
  if vrows%notfound then
    raise no_emp;
  end if;
  --4、关闭游标
  close vrows;
exception
  when no_emp then
    dbms_output.put_line('发生了自定义的异常');
end;

---

 

/*
  存储过程：实际上是封装在服务器上一段PLSQL代码片段，已经编译好了的代码
            客户端调用存储过程，执行效率就会非常高效
  语法：
      create [or replace] procedur 存储过程的名称(参数名 in|out 参数类型，参数名 in|out 参数类型)
      is | as
         --声明部分
      begin
         --业务逻辑
      end;
*/
--给指定员工涨薪，并打印涨薪前和涨薪后的工资
/*
    参数：in 员工编号
    参数：in 涨多少

    声明一个变量：存储涨工资前的工资

    查询出当前是多少
    打印涨薪前的工资
    更新工资
    打印涨薪后的工资
*/
create or replace procedure proc_updatesal(vempno in number,vnum in number)
is
--声明变量,记录当前工资
   vsal number;
begin
  --查询出当前是多少
  select sal into vsal from emp where empno=vempno;
  --打印涨薪前的工资
  dbms_output.put_line('涨薪前的工资：'||vsal);
  --更新工资
  update emp set sal=sal+vnum where empno=vempno;
  --打印涨薪后的工资
  dbms_output.put_line('涨薪后的工资：'||(vsal+vnum));
end;

--调用存储过程

--方式一
call proc_updatesal(7788,100);

--方式二 用的最多的方式
declare

begin
  proc_updatesal(7788,-100);
end;

---

 

/*
  存储函数：实际上是一段封装在Orcle服务器中的一段PLSQL代码片段，它是已经编译好了的代码片段
  语法：
      create [or replace] function
      is | as
      begin
        
      end;
   存储过程和存储函数的区别：
      1、本质上没有区别
      2、函数存在的意义是给过程调用，存储过程里面调用存储函数  
      3、函数可以在sql语句里面直接调用
      4、存储过程能实现的，存储函数也能实现，存储函数能实现的，存储过程也能实现
*/
--查询指定员工的年薪
/*
  参数：员工的编号
  返回：年薪
*/
create or replace function func_getsal(vempno number) return number
is
--声明变量，保存年薪
  vtotalsal number;
begin
  select sal*12 into vtotalsal from emp where empno=vempno;
  return vtotalsal;
end;

--调用存储函数
declare
  vsal number;
begin
  vsal := func_getsal(7788);
  dbms_output.put_line(vsal);
end;

--查询指定员工的姓名和他的年薪
select ename,func_getsal(empno) from emp where empno=7788;

--查询指定员工的年薪--存储过程来实现
--参数: 员工编号
--输出: 年薪
create or replace procedure proc_getsal(vempno in number,vtotalsal out number)
is

begin
  select sal*12+nvl(comm,0) into vtotalsal from emp where empno=vempno;
end;

declare
  vtotal number;
begin
  proc_getsal(7788,vtotal);
  dbms_output.put_line('年薪：'||vtotal);
end;

---

 

/*
    JAVA调用存储过程
       JDBC的开发步骤:
          1.导入驱动包
          2.注册驱动
          3.获取连接
          4.获取执行SQL的statement
          5.封装参数
          6.执行SQL
          7.获取结果
          8.释放资源   
*/

/*
   封装一个存储过程 : 输出所有表中的记录
   
   输出类型 : 游标  
*/
create or replace procedure proc_getemps(vrows out sys_refcursor)
is

begin
  --1.打开游标, 给游标赋值
  open vrows for select * from emp;
end;

---

 

/*
  触发器：当用户执行了 insert | update | delete 这些操作后，可以触发一系列其他的动作/业务逻辑
  作用：
      在动作执行之前或之后，出发业务处理逻辑
      插入数据，做一些校验
  语法：
      create [or replace] trigger 触发器名称
      before | after
      insert | update | delete
      on [表名]
      [for each row]
      declare
      
      begin
        
      end;
   触发器的分类：
      语句级触发器：不管影响多少行，都只会执行一次
      行级触发器：影响多少行，就触发多少次
           :old  代表旧的记录，更新前的记录
           :new  代表的是新的记录
*/

--新员工入职之前，输出一句话：欢迎加入新团队
create or replace trigger tri_test1
before
insert
on emp
declare

begin
  dbms_output.put_line('欢迎加入新团队');
end;

insert into emp(empno,ename) values(9999,'HUAAN');

--数据校验，星期六老板不在，不能办理新员工入职
--在插入数据之前
--判断当前日期是否是周六
--如果是周六，就不能插入
create or replace trigger tri_test2
before
insert
on emp
declare
   --声明变量
   vday varchar2(10);
begin
  --查询当前
  select trim(to_char(sysdate,'day')) into vday from dual;
  if vday='星期六' then
    dbms_output.put_line('老板不在，不能办理新员工入职');
    --抛出系统异常
    raise_application_error(-20001,'老板不在，不能办理新员工入职');
  end if;
end;

insert into emp(empno,ename) values(9999,'HUAAN');

--更新所有员工的工资，输出一句话
create or replace trigger tri_test3
after
update
on emp
for each row
declare

begin
  dbms_output.put_line('更新了数据');
end;

update emp set sal=sal+10;

--判断员工涨工资后的工资工资一定要大于涨工资前的工资
/*
   200 --> 100
   触发器 : before
      旧的工资 
      新的工资
      如果旧的工资大于新的工资 , 抛出异常,不让它执行成功   
 
   触发器中不能提交事务,也不能回滚事务 
*/
create or replace trigger tri_test4
before
update
on emp
for each row
declare

begin
  if :old.sal>:new.sal then
    raise_application_error(-20002,'旧的工资不能大于新的工资');
  end if;
end;

update emp set sal = sal + 10;
select * from emp;

update emp set sal = sal - 100;

/*
  模拟MySQL中ID的自增属性 auto_increment
  insert into person(null,'张三');
  
  触发器：
  pid=1  insert pid=1
  序列： create sequence seq_person_pid;
*/
create table person(
    pid number primary key,
    pname varchar2(20)   
);

insert into person values(null,'张三'); 

create sequence seq_person_pid;

--触发器
create or replace trigger tri_add_person_pid
before
insert
on person
for each row
declare

begin
  dbms_output.put_line(:new.pname);
  --给新的纪录pid赋值
  select seq_person_pid.nextval into :new.pid from dual;
end;

insert into person values(null,'张三'); 

select * from person;