oracle自主学习笔记,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;