一、
存储过程语法结构
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;