存储过程And游标

-----无参数游标----

declare
  --游标
 cursor vrows is select * from emp;
 --声明变量，记录一行数据。
  vrow emp%rowtype;
begin
   --打开游标
   open vrows;
  --从游标提取数据
  --循环取数据
  loop
      fetch vrows into vrow;
      exit when vrows%notfound;
      dbms_output.put_line('姓名：'|| vrow.ename || '工资' || vrow.sal);
  end loop;
   ---关闭游标
  close vrows;
end;

-----有参数游标----

declare
  --游标
 cursor vrows(dno number) is select * from emp where deptno = dno;
 --声明变量，记录一行数据。
  vrow emp%rowtype;
begin
   --打开游标
   open vrows(10);
  --从游标提取数据
  --循环取数据
  loop
      fetch vrows into vrow;
      exit when vrows%notfound;
      dbms_output.put_line('姓名：'|| vrow.ename || '工资' || vrow.sal);
  end loop;
   ---关闭游标
  close vrows;
end;

-----系统引用游标----

declare
  --声明系统引用游标
 vrows sys_refcursor;
  --声明变量，记录一行数据。
  vrow emp%rowtype;
begin
   --打开游标
   open vrows for select * from emp;
  --从游标提取数据
  --循环取数据
  loop
      fetch vrows into vrow;
      exit when vrows%notfound;
      dbms_output.put_line('姓名：'|| vrow.ename || '工资' || vrow.sal);
  end loop;
   ---关闭游标
  close vrows;
end;

declare
--声明一个游标
cursor vrows is select * from emp;
begin 
     for vrow in vrows loop
     dbms_output.put_line('姓名：'|| vrow.ename || '工资' || vrow.sal || '工作' || vrow.job);
   end loop;
end;

游标案例

declare
  --游标
 cursor vrows is select * from emp;
 --声明变量，记录一行数据。
  vrow emp%rowtype;
begin
   --打开游标
   open vrows;
  --从游标提取数据
  --循环取数据
  loop
      fetch vrows into vrow;
      exit when vrows%notfound;
      if vrow.job = 'PRESIDENT' then
        update emp set sal =sal + 1000 where empno = vrow.empno;
       else if 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;
   ---关闭游标
  close vrows;
  --提交事务
   commit;
end;

游标处理异常

declare
    vi number;
    vrow emp%rowtype;
begin
         --vi :=8/0;
         --vi :='aaa';
   --select * from vrow from emp;
     select * into vrow from emp where empno=1234567 
exception
  when zero_divide then
  dbms_output.put_line('发生了除0异常');
when value_error then
  dbms_output.put_line('发生了类型转换异常');
when too_many_rows then
  dbms_output.put_line('查询多行记录，赋值给一行变量');
when others then
   dbms_output.put_line('发生了其他异常'|| sqlerrm);
end

游标自定义异常

declare
    vrow emp%rowtype;
    no_emp exception;
begin
 select * into vrow from emp where empno=88888888;
if vrow.sal is null then
     raise no_emp;
end if;
exception
  when no_emp then
    dbms_output.put_line('输出自定义异常！');
 when other then 
     dbms_output.put_line('输出其他异常！'|| sqlerrm);
end;

总结：游标自定义异常

declare
    --声明一个游标
    cursor vrows is select * from emp where empno=88888888;
    ---声明记录型变量
    vrow emp%rowtype;
    ---声明自定义异常
    no_emp exception;
begin
   --打开游标
   open vrows;
    ---取数据
fetch vrows into vrow;
--判断游标是否有数据
 if vrows%notfount then
     raise no_emp;
end if;
--关闭游标
close vrows;
exception
  when no_emp then
    dbms_output.put_line('输出自定义异常！');
 when other then 
     dbms_output.put_line('输出其他异常！'|| sqlerrm);
end;

存储过程（更新工资）
vempno :涨薪者的编号 vnum ：涨薪多少。

create or replace procedure pro_updatesal(vempno in number,vnum in number)
is
---声明变量，记录当前工资
vsal number;
--vsal emp%rowtype;
begin
---查询当前工资(放入变量vasal中)
select sal into vsal from emp where empno=vempno;
--输出涨薪前工资
     dbms_output.put_line('涨薪前：'||vsal);
--更新工资
update emp set sal = vsal + vnum where empno = vempno;
--输出涨薪后工资
dbms_output.put_line('涨薪后：'||(vsal+vnum));
commit;
end

调用：

--1
call pro_updatesal(7788,100);
--2
declare
begin
  pro_updatesal(7788,-100);
end;

存储函数（查询年薪）

create or replace function fun_getsal(vempno number)
return number
is
--声明变量，保存年薪
vtotalsal number;
begin
select sal*12 + nvl(comm,0) into vtotalsal from emp where empno=vempno
return vtotalsal;
end;

调用

declare
vsal number;
begin
 vsal :=fun_getsal(7788);
dbms_output.put_line(vsal);
end;

存储函数可以在sql查询语句直接调用（存储函数与过程区别）
存储函数有返回值，存储过程没有，但存储过程有输出变量 out

create or replace procedure pro_gettotalsal(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
   pro_gettotalsal(7788,vtotal);
   dbms_output.put_line(vtotal);
end;

---------------------------触发器------------------------------

--插入数据时，触发动作！
 create or replace tirigger tri_test01
  after
    insert 
      on emp
declare
begin
dbms_output.put_line('欢迎加入我们！');
end;
--测试
insert into emp (empno,ename)values(7755,'cwq');

周六不能向数据库新增数据。

create or replace trigger tri_test02
before
insert
on emp
    
declare
--声明变量
vday varchar2(10);
begin
  --查询当前日期
   select trim(to_char(sysdate,'day'))into vday from dual;//oracle的虚拟表dual
---判断当前日期：
if vday = 'saturday' then
dbms_output.put_line('周六不能办理入职！');
--抛出异常
raise_application_error(-2000,老板不在，不能办理入职！)
end if;
end;


insert into emp(empno,ename)values(7755,'cwq');

触发器种类
语句触发器 ：不管影响多少行，都会执行一次
行级触发器 ：影响多少行，就触发多少次
–创建触发器—tri_test03

create or replace trigger tri_test03
after  --执行更新操作后执行declare的内容
update
on emp
for each row  //行级触发器
declare
begin
dbms_output.put_line('更新了数据！');
end;
---测试
update emp set sal =sal+10;

create or replace trigger tri_test04
before --执行更新操作后执行declare的内容
update
on emp
for each row  //行级触发器
declare
begin
  if old.sal >:new.sal then
   raise_application_error(-20002,'旧的工资不能大于新的工资！');
end;

update emp set sal =sal+10;

-----------------------触发器模拟主键自增长--------------------------------

create table person(
     pid number primkey key;
      pname vachar2(20)
);
insert into person values(null,'zs');

create sequence seq_person_pid;
--触发器
create or replace trigger tri_add_person_pid
before --执行插入操作之前执行declare的内容
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(pid,pname)values(null,'zs');