Oracle 自主学习笔记（五）

一、
存储过程语法结构

create or replace procedure 存储过程名
is|as
声明语句段;
begin
执行语句段;
exception
异常处理语句段;
end 存储过程名;

in 模式的形参只能只能将实参传给形参，进入存储过程内部，只能读不能写
out 模式在存储过程内部可以读写，返回时形参的值会返回给形参
in out 具有以上两种属性

例子：
create or replace procedure proc(i in int)
as
a varchar(20);
begin
for j in 1..i loop
a:=a'*';
dbms_output.put_line(a);
end loop;
end
/

执行：exec proc(5);

函数语法

create or replace function 函数名
return 数据类型
as|is
声明部分
begin
可执行部分
return(表达式)
exception
异常处理部分
END[函数名]

二、
触发器是响应插入、更新或删除等数据库事件而执行的过程
它定义了当一些数据库相关事件发生时应采取的动作。触发
器即用来监视数据库的各项操作。

DML触发器语法

create or replace trigger triggername

before/after
 
update/insert/delete...

on triggername

for each row

when 条件

create or replace trigger trigg1

before insert on emp

for each row

begin 

if :new.empno < 0 then        在插入之前，不让这个存储过程为NULL

  raise_application_error(-20001, '员工编号为负，不能插入到表中!');
 end if;
end;

 

declare
  empno integer;
  ename varchar2(20);
  hiredate date;
  comm number;
  t int;
  lc number;
  i integer;
begin
  select empno,ename,iredate,comm into empno,ename,hiredate,comm from emp where empno = &i;
  select mons_between(sysdate,hiredate) into t from emp where empno = &i;
  if t > 120 theni
    update emp set comm = comm + 10000 where empno = i;
  else if t > 60 and t <= 120 then
    update emp set comm = comm + 5000 where empno = i;
  end if;
  commit； 提交

  select comm into lc from emp where empno = i;
  dbms_output.put_line(empno||' 'ename||' '||hiredate||' '||comm||' '||lc);
end;

 
5、对每位员工的薪水进行判断，如果该员工薪水高于其所在部门的平均薪水，则将其薪水减50元

PL/SQL 语句
declare                             先declare声明

myempno emp.emp%type := '&no';
empeg   scott.emp%rowtype;
avgsal  number;
saleg   number;
depteg  scott.dept%rowtype;

begin
select * into empeg from scott.emp where emp.empno = myempno;
select * into depteg from scott.dept where dept.deptno = empeg.deptno;
select avg(sal) into avgsal from scott.emp group by emp.deptno having emp.deptno = empeg.deptno;
if empeg.sal > avgsal then
saleg := empeg.sal-50;
else saleg := empeg.sal;
end if;
update emp set emp.sal = saleg where emp.empno = myempno;

dbms_output.put_line('员工编号:' || myempno || '姓名:' || empeg.ename ||
'之前工资:' || empeg.sal || '现在工资:'|| saleg);

6、

创建一个存储过程，实现：通过输入员工编号查看员工姓名、工资、奖金：
1.1 如果输入的编号不存在，进行异常提示处理：
1.2 如果工资高于4000，进行异常提示处理：
1.3 如果奖金没有或为0，进行异常提示处理：

存储过程 procedure

create or replace procedure proemp(myempno in int) as

empeg scott.emp%rowtype;

ifexists number;

begin 
select * into empeg from scott.emp where emp.empno = myempno;
select count(1) into ifexists from scott.emp where emp.empno = myempno;

if ifexists = 0 then
dbms_output.put_line('您输入的编号不存在!');
elsif empeg.sal > 4000 then
dbms_output.put_line('该员工工资高于4000');
elsif nvl(empeg.comm,0) = 0 then
dbns_output.put_line('该员工没有奖金!');
else null;
end if;

dbms_output.put_line('员工编号:' || myempno ||'姓名' || empeg.ename || '工资:' || empeg.sal
|| '奖金:' || empeg.comm);

end proemp;   end  过程名

exec proemp;