存储过程，游标，触发器

た入场券 2022-05-19 23:18 262阅读 0赞

\-- 语法  
/\*  
create \[or replace\] procedure 存储过程名称(参数名1 in|out 数据类型，参数名2 in|out   
  
数据类型,...)  
as|is  
\-- 声明变量  
begin  
\-- 过程化语句  
end;  
\*/  
\--- 根据员工编号得到员工的年薪  
create or replace procedure getYearSal(eno in number , yearsal out number)  
as  
  
begin  
select sal \* 12 + nvl(comm,0) into yearsal from emp where empno = eno;  
end;  
  
\-- 访问存储过程  
declare  
ys number;  
begin  
getYearSal(7788, ys);  
dbms\_output.put\_line('年薪'||ys);  
end;  
  
\--- 给某员工涨工资（打印涨前的工资和涨后的工资）  
create or replace procedure updateSal(eno in number ,psal in number)  
is  
oldsal number;  
newsal number;  
begin  
\-- 打印涨前的工资  
select sal into oldsal from emp where empno = eno;  
dbms\_output.put\_line('涨前的工资：'||oldsal);  
\-- 涨工资  
update emp set sal = sal + psal where empno = eno;  
commit;  
\-- 打印涨后的工资  
select sal into newsal from emp where empno = eno;  
dbms\_output.put\_line('涨后的工资：'||newsal);  
  
end;  
\-- 访问只有输入参数的存储过程

call updateSal(7788,100);

\---- 举例：返回游标的存储过程  
\-- 得到某部门所有的员工信息  
create or replace procedure getEmps(dno in number ,emps out sys\_refcursor)  
as  
  
begin  
\-- 给动态的游标赋值  
open emps for select \* from emp where deptno = dno;  
end;  
\-- 访问带有输出参数为游标的存储过程  
declare  
emps sys\_refcursor;  
prow emp%rowtype;  
begin  
getEmps(20, emps);  
loop  
fetch emps into prow;  
exit when emps%notfound;  
dbms\_output.put\_line(prow.empno||','||prow.ename);  
  
end loop;  
close emps;  
end;  
游标  
\#\# 四、游标&例外  
\`\`\`  
\--- 游标(集合): 用来处理返回多行记录的问题  
\-- select into 语句只能解决返回一行记录的问题  
declare  
pname emp.ename%type;  
begin  
select ename into pname from emp where deptno = 40;  
dbms\_output.put\_line(pname);  
end;  
  
\--- 声明游标  
\-- cursor 游标名 is sql查询语句;  
\-- 遍历游标的过程  
\-- 打开游标  
\-- 提取游标中的一行内容: fetch 游标名 into 变量名;  
\-- 循环语句， exit when 游标名%notfound;  
\-- 关闭游标  
\-- 举例： 使用游标打印20号部门的员工姓名和工作  
declare  
cursor cur is select ename ,job from emp where deptno = 20;  
pname emp.ename%type;  
pjob emp.job%type;  
begin  
\-- 打开游标  
open cur;  
loop  
fetch cur into pname,pjob;  
\-- 当游标中没有记录时退出  
exit when cur%notfound;  
dbms\_output.put\_line(pname || ','|| pjob);  
end loop;  
\-- 关闭游标  
close cur;  
end;  
\-- 举例  
\-- 使用游标对20号部门的员工涨工资(100)  
\-- 找出20号部门的员工编号，更新工资  
declare  
cursor cur is select empno from emp where deptno = 20;  
begin  
\-- for 循环:自动打开,关闭游标  
for c in cur loop  
update emp set sal = sal + 100 where empno = c.empno;  
end loop;  
end;  
  
update emp set sal = sal +100 where deptno = 20;  
  
\#\#\# 八、触发器  
\`\`\`  
\---- 触发器（监听器): 监听表中的数据是否发生了改变  
/\*  
create or replace trigger 触发器名称  
before |after -- 改变之前执行触发器还是之后执行  
insert|update|delete  
on 表 -- 修改的是哪一张表  
\[触发器的级别:表级的触发器，行级触发器\]  
declare  
begin  
end;  
\*/  
\-- 举例 ： 添加一条记录（打印添加了一条记录）  
create or replace trigger insertEmp  
after  
insert  
on emp  
  
declare  
begin  
dbms\_output.put\_line('添加了一条记录');  
end;  
  
insert into emp(empno ,ename) values(1002,'zhangfei');  
  
\-- 不能给员工降薪  
create or replace trigger notUpdateLowerSal  
before  
update  
on emp  
for each row -- 行级的触发器：只要使用new,old 就必须使用行级触发器  
declare  
  
begin  
if :new.sal < :old.sal then  
\-- raise\_application\_error(p1,p2)  
\-- p1 : 错误的编号：- 20001 ~ -20999  
\-- p2 :错误的信息  
raise\_application\_error(-20001 , '不能给员工降薪');  
end if;

end;

update emp set sal = sal - 1 where empno = 7788;  
  
\-- 使用触发器来模拟mysql中自增的效果  
create sequence tseq;  
  
  
create or replace trigger autoIncrement  
before  
insert  
on emp  
for each row  
declare  
  
begin  
select tseq.nextval into :new.empno from dual;  
end;  
  
insert into emp(empno,ename) values(100,'lisi');  
select \* from emp;