第七章 事务、过程、触发器

   
   
declare
v_money float;
begin
--记录事务的回滚点（还原点）
--savepoint p1;
     update bank set balance=balance+1200where cardId=1002;
--一个事务可以定义多个回滚点
--savepoint p2;
     update bank set balance=balance-1200where cardId=1001;
--查询转账后的数据是否合法
select balance into v_money from bank where cardId=1001;
if v_money<0then
--非法数据进行回滚
        rollback to p1;
else
--提交事务
        commit;
endif;
end;

   
   
loop
   fetch  游标名into变量名;
exitwhen游标名%notfound;//当找不到游标值就退出循环
--执行的一系列操作
end loop;

   
   
--将部门的所有员工的薪水增加100
declare
cursor c_empId isselect employee_id from emp;
v_empId int;
begin
     open c_empId;
     loop
--读取一行数据
         fetch c_empId into v_empId;
--判断是否需要退出
exitwhen c_empId%notfound;
--每读到一个人的编号就进行一次加薪
         update emp set salary=salary+100where emp.employee_id=v_empId;
end loop;
     close c_empId;
end;

   
   
--将sales部门的所有员工的薪水增加100
--游标对应的结果集可以使用子查询实现
--游戏对应的select中可以使用变量
declare
v_depId int;
--cursor c_empId isselect emp.employee_id from emp where department_id=(select department_id from dep where dep.department_name='Sales');
cursor c_empId isselect emp.employee_id from emp where department_id=v_depId;
v_empId int;
begin
--获取Sales的部门编号
select department_id into v_depId from dep where dep.department_name='Sales';
     open c_empId;
     loop
--读取一行数据
         fetch c_empId into v_empId;
--判断是否需要退出
exitwhen c_empId%notfound;
--每读到一个人的编号就进行一次加薪
--update emp set salary=salary+100where emp.employee_id=v_empId;
         dbms_output.put_line(v_empId);
end loop;
     close c_empId;
end;

   
   
--打印显示Sales部门的员工的姓名，薪水，入职时间
declare
cursor c_emp isselect first_name||last_name,salary,hire_date from emp where department_id=(select department_id from dep where dep.department_name='Sales');
--定义复合类型接收游标获取的每一行数据
type empInfo is record
(
 empName varchar2(50),
 salary float,
 hireDate emp.hire_date%type
);
--复合类型的变量
v_empRow empInfo;
begin
     open c_emp;
     loop
         fetch c_emp into v_empRow;
exitwhen c_emp%notfound;
         dbms_output.put_line(v_empRow.empName||'  '||v_empRow.salary||'  '||v_empRow.hireDate);
end loop;
     close c_emp;
end;

   
   
--打印显示在Seattle工作的所有员工的姓名、薪水，所在部门的名称，岗位名称
declare
v_locationId int;
v_depId int;
--查询Seattle对应部门的游标
cursor c_dep isselect department_id from dep where location_id=v_locationId;
--查询部门对应员工的游标
cursor c_emp isselect 	first_name||last_name,salary,department_id,job_id from emp 		where department_id=v_depId;
type empInfo is record
(
 empName varchar2(50),
 salary float,
 depId int,
 jobId job.job_id%type
);
--自定义类型的变量
v_emp empInfo;
v_depName dep.department_name%type;
v_jobName job.job_title%type;
begin
--查询对应的地区编号
select location_id into v_locationId from locations where locations.city='Seattle';
--开启游标
     open c_dep;
     loop
--读取每一条部门编号
         fetch c_dep into v_depId;
exitwhen c_dep%notfound;
         open c_emp;
         loop
--读取每个部门对应的员工信息
              fetch c_emp into v_emp;
exitwhen c_emp%notfound;
--根据部门编号查询部门名称
select dep.department_name into v_depName from dep where dep.department_id=v_emp.depId;
--根据岗位编号查询岗位名称
select job.job_title into v_jobName from job where job.job_id=v_emp.jobId;
              dbms_output.put_line(v_emp.empName||'  '||v_emp.salary||'  '||v_depName||'  '||v_jobName);
end loop;
         close c_emp;
end loop;
     close c_dep;
end;

   
   
--创建存储过程
create or replace procedure myPro
as
begin
     dbms_output.put_line('我的第一个存储过程');
end;

   
   
--在过程块外执行存储过程
call myPro();

   
   
--在过程内调用存储过程
begin
    myPro();
end;

   
   
--编写存储过程用于新增用户信息
--参数默认为in输入参数
create or replace procedure insertUser(v_userId int,v_uname in varchar2,v_birthday in date)
as
begin
     insert into users(userId,userName,birthday) values(userId,uname,birthday);
end;
call insertUser(1028,'jerry',to_date('19901010','yyyymmdd'));

   
   
--编写存储过程根据用户编号查询姓名和性别
create or replace procedure findUserById(v_userId int,v_userName out varchar2,v_sex out varchar2)
as
begin
select userName,sex into v_userName,v_sex from users where userId=v_userId;
end;

   
   
--带有输出参数的存储过程必须要先创建变量用来保存输出参数
declare
v_name varchar2(20);
v_sex users.sex%type;
begin
     findUserById(1024,v_name,v_sex);
     dbms_output.put_line(v_name||' '||v_sex);
end;

   
   
--带有inout类型参数的存储过程
create or replace procedure testPro(a varchar2,b out varchar2,c inout varchar2)
as
begin
     dbms_output.put_line(a||','||b||','||c);
     c:='d';
end;
declare
b varchar2(20):='b';
c varchar2(20):='c';
begin
     testPro('a',b,c);
     dbms_output.put_line(c);
end;

   
   
--创建存储过程根据指定员工的编号将该员工所在部门中比其收入高的员工收入增加200，如果该编号的员工的薪资是低于该部门的平均薪资
--则加薪至平均薪资，并返回该部门的平均薪资
create or replace procedure findDepSalaryById(v_empId int,v_avg outfloat)
as
--员工的薪资
v_salary float;
--部门编号
v_depId int;
--游标收入指定高的结果集
cursor c_emp isselect emp.employee_id from emp where emp.department_id=v_depId and salary>v_salary;
--每个员工的编号
v_id int;
--加工资的人数
v_count int:=0;
begin
--查询员工的薪资和部门编号
select salary,emp.department_id into v_salary,v_depId from emp where emp.employee_id=v_empId;
--查询员工对应的平均薪资
select avg(salary)into v_avg from emp where emp.department_id=v_depId;
--开启游标
     open c_emp;
--读取游标的每一行数据
     loop
         fetch c_emp into v_id;
exitwhen c_emp%notfound;
--增加员工的薪资200
         update emp set salary=salary+200where emp.employee_id=v_id;
         v_count:=v_count+1;
end loop;
     dbms_output.put_line('加薪人数是：'||v_count);
--判断员工自身薪资是否低于平均薪资
if v_salary<v_avg then
        update emp set salary=v_avg where emp.employee_id=v_empId;
endif;
     close c_emp;
end;
declare
v_avg float;
begin
     findDepSalaryById(198,v_avg);
     dbms_output.put_line(v_avg);
end;

   
   
create  or  replace function函数名(参数)
return数据类型
as
begin
--PL/SQL语句
return数据;
end;

   
   
--创建自定义函数
create or replace function showDepName(v_depId int)
return varchar2
as
v_depName dep.department_name%type;
begin
select dep.department_name into v_depName from dep where dep.department_id=v_depId;
return v_depName;
end;

   
   
select first_name,showDepName(emp.department_id)from emp

   
   
--返回游标的存储过程
--根据部门编号返回该部门的所有员工姓名
create or replace procedure findEmpByDepId(v_depId int,c_emp out sys_refcursor)
as
begin
--开启系统游标
     open c_emp forselect first_name||last_name from emp where emp.department_id=v_depId;
end;
--调用返回游标的存储过程
declare
c_emp sys_refcursor;
v_empName varchar2(20);
begin
     findEmpByDepId(50,c_emp);
--系统游标获取时不需要打开
     loop
       fetch c_emp into v_empName;
exitwhen c_emp%notfound;
       dbms_output.put_line(v_empName);
end loop;
     close c_emp;
end;

   
   
--使用存储过程查询指定版块中的所有主贴标题,返回游标
create or replace procedure getTopicById(v_id int,c_tid out sys_refcursor)
as
begin
     open c_tid forselect tid from bbstopic where tsid = v_id;
end;


declare 
c_tid sys_refcursor;
v_topic bbstopic.ttopic%type;
v_id int;
begin
     getTopicById(1,c_tid);
     loop 
          fetch c_tid into v_id;
exitwhen c_tid%notfound;
select ttopic into v_topic from bbstopic where tid = v_id;
          dbms_output.put_line(v_id||' '||v_topic);
end loop;
     close c_tid;
end;

   
   
drop  trigger  触发器名称;

   
   
create or replace trigger users_before_insert
before insert on users for each row
begin
--使用伪记录获取数据
--:new表示新增的数据行
     dbms_output.put_line(:new.userId||'  '||:new.userName);
--insert触发器无法访问old伪记录
     dbms_output.put_line(:old.userName);
     dbms_output.put_line('在users表新增数据时触发了');
end;

   
   
create or replace trigger users_before_delete
before delete on users for each row
begin
--:old表示被删除的数据行
--delete触发器无法访问new伪记录
     dbms_output.put_line(:old.userName);
     dbms_output.put_line('执行了删除');
end;

   
   
create or replace trigger users_before_update
before update on users for each row
begin
--update触发器可以同时访问:new和:old伪记录
--:new表示修改后的数据行
--:old表示修改前的数据行
     dbms_output.put_line(:new.userName);
     dbms_output.put_line(:old.userName);
     dbms_output.put_line('执行了修改');
end;

   
   
--编写触发器将用户修改数据的情况备份在user_log表中
create or replace trigger users_log
after update on users for each row
begin
--触发器内部可以对其他数据表执行DML操作
--触发器不能对当前表执行DML操作
     insert into user_log values(logId.Nextval,'update',sysdate);
end;

   
   
--异常处理
--所有异常的处理应在过程的末端执行
begin
     insert into users values(1025,'tom',sysdate,null,null,20,null);
--处理系统异常
     exception when others then
           dbms_output.put_line('数据新增出现了异常');
end;

   
   
--新增用户数据时，用户的年龄不能超过用户表中所有用户的平均年龄
create or replace trigger users_before_insert
before insert on users for each row
declare
v_avg int;
--自定义异常
myError exception;
begin
--查询平均年龄
select round(avg(age))into v_avg from users;
     dbms_output.put_line('平均年龄是'||v_avg);
--判断新增的年龄是否超过平均年龄
if:new.age>v_avg then
        dbms_output.put_line('新增的年龄大于平均年龄');
--可以改变伪记录的数据
--:new.age:=v_avg;
--抛出异常
raise myError;
endif;
--处理异常
     exception when myError then
--第一个参数是错误编号，所有自定义异常编号从-20000开始
--第二个参数是错误的信息内容
         raise_application_error(-20001,'age must less than avg age!');
end;

   
   
--编写触发器，要求当用户修改emp表的数据时，salary的值必须在其岗位的薪资范围内，同时用户的薪水不超过该部门经理的薪水
create or replace trigger emp_update
before update on emp for each row
declare
PRAGMA  AUTONOMOUS_TRANSACTION;
v_jobid job.job_id%type;
v_min float;
v_max float;
--部门经理的薪水
v_salary float;
--自定义异常
myError exception;
begin
--查询修改数据行对应的岗位编号
select job_id into v_jobid from emp where emp.employee_id=:new.employee_id;
--根据岗位编号查询薪资范围
select min_salary,max_salary into v_min,v_max from job where job_id=v_jobid;
--查询部门经理的薪水
select salary into v_salary from emp where emp.employee_id=(select emp.manager_id from emp where emp.employee_id=:new.employee_id);
--判断修改后的薪资是否合法
if:new.salary<v_min or:new.salary>v_max or:new.salary>v_salary then
raise myError;
endif;
--处理异常
     exception when myError then
        raise_application_error(-20001,'薪水范围错误！');
end;
update emp set salary=5600where emp.employee_id=198;

   
   
--使用触发器实现成绩表的数据添加不能新增学生中学号以外的非法学号
create or replace trigger score_insert
before insert on score for each row
--创建游标获取所有学号
declare
cursor c_stuid isselect stuId from student;
v_stuId int;
--标识新增的学号是否存在
v_hasId boolean:=false;
--异常
myError exception;
begin
--判断要新增的成绩信息中的学号是否出现在学生的学号范围内
     open c_stuid;
     loop
         fetch c_stuid into v_stuid;
exitwhen c_stuid%notfound;
if v_stuid=:new.stuid then
            v_hasId:=true;
--退出循环
exit;
endif;
end loop;
if v_hasId=falsethen
--抛出异常阻止数据的写入
raise myError;
endif;
     close c_stuid;
     exception when myError then
        raise_application_error(-20001,'违背了外键约束！');
end;
insert into score values(1000,1,9999);

   
   
--自动插入序列
create or replace trigger auto_logId
before insert on user_log for each row
begin
--将序列的值写入新纪录
select logId.nextval into:new.logid from dual;
end;

   
   
--使用视图存储允许可见的数据
create or replace view cashView asselect cardid,userName from card;

   
   
--使用视图查看数据
select*from cashview;

   
   
--银行卡号表
create table card
(
 cardid varchar2(20) primary key,
 userName varchar2(50)notnull,
 pwd varchar(6)notnull
);
create table cashRecord
(
 recordId int primary key,
 cardid int,
 cash float,
 cashType int,--0表示支出1表示收入
 cashTime date
);

   
   
create or replace view cashView asselect recordId,card.cardId,userName,cash,case cashtype 
when1then'收入'
when0then'支出'endcase,cashtime from card inner join cashRecord on card.cardId=cashRecord.Cardid;

   
   
update cashView set userName='tony'where cardId='100000001';