oracle存储过程和存储函数&触发器

Posted by

一、创建3张表,分别代表不同部门员工信息,不带数据emp10emp20emp30

*oracle**存储过程和存储函数***

create table emp10 as select * from emp where 1=0;create table emp20 as select * from emp where 1=0;create table emp30 as select * from emp where 1=0;

指存储在数据库中供所有用户程序调用的子程序叫存储过程,存储函数

二、创建包,用于统计部门信息1)添加常量 ALLDEPTNAME 值为:
‘部门信息概况’2)声明函数:用于统计某部门员工数声明过程:将不同部门的员工存放到不同表中4)声明函数:统计该部门员工平均工资,及每月要发放工资的总和,并返回5)声明函数:返回该部门的信息概况:部门号,部门名,部门所在地,该部门员工数

存储过程和存储函数的相同点:完成特定功能的程序

create or replace package pack_emp is ALLDEPTNAME constant varchar2 := '部门信息概况'; function getEmpNum(v_eno emp.deptno%type) return number; procedure saveEmp(v_dno emp.deptno%type); function getSumSals(v_dno emp.deptno%type,v_avgsal out emp.sal%type,v_sumsal out emp.sal%type) return number; function getDeptInfo(v_dno in out dept.deptno%type,v_dname out dept.dname%type,v_loc out dept.loc%type) return number;end pack_emp;

存储过程和存储函数的区别:是否用return语句返回值

三、创建包体,实上以上过程

=========================创建和使用存储过程=============================

create or replace package body pack_emp is --用于统计某部门员工数 function getEmpNum(v_eno emp.deptno%type) return number is nu number; begin select count into nu from emp where deptno = v_eno; return nu; end getEmpNum; --将不同部门的员工存放到不同表中 procedure saveEmp(v_dno emp.deptno%type) is cursor cur_emp is select * from emp where deptno = v_dno; begin for emp_record in cur_emp loop if v_dno = 10 then insert into emp10 select * from emp where deptno = v_dno; elsif v_dno = 20 then insert into emp20 select * from emp where deptno = v_dno; elsif v_dno = 30 then insert into emp30 select * from emp where deptno = v_dno; end if; end loop; exception when no_data_found then dbms_output.put_line; when others then dbms_output.put_line; end saveEmp; --统计该部门员工平均工资,及每月要发放工资的总和,并返回 function getSumSals(v_dno emp.deptno%type,v_avgsal out emp.sal%type,v_sumsal out emp.sal%type) return number is begin select avg,sum into v_avgsal,v_sumsal from emp where deptno = v_dno; return 0; end getSumSals; --返回该部门的信息概况:部门号,部门名,部门所在地,该部门员工数 function getDeptInfo(v_dno in out dept.deptno%type,v_dname out dept.dname%type,v_loc out dept.loc%type) return number is nu number; begin select d.dname,d.loc,count into v_dname,v_loc,nu from emp e,dept d where d.deptno = e.deptno and d.deptno=v_dno group by d.dname,d.loc; return nu; end getDeptInfo;end pack_emp;

用create
procedure命令建立存储过程和存储函数

四、分别调用验证

语法:

declare v_dno dept.deptno%type; v_no dept.deptno%type := 10; nu number; v_avgsal emp.sal%type; v_sumsal emp.sal%type; v_dname dept.dname%type; v_loc dept.loc%type;begin nu := pack_emp.getEmpNum; dbms_output.put_line('员工数:'||nu); pack_emp.saveEmp; nu := pack_emp.getSumSals(10,v_avgsal,v_sumsal); dbms_output.put_line('平均工资:'||v_avgsal||'工资的总和:'||v_sumsal); nu := pack_emp.getDeptInfo(v_no,v_dname,v_loc); dbms_output.put_line('部门号:'||v_no||'部门名:'||v_dname||'部门所在地:'||v_loc||'部门员工数:'||nu);end;

create
[or replace] procedure 过程名(参数列表)

as

PLSQL子程序体;

实例:带参数的存储函数

create or
replace procedure RaiseSalry(eno in number)

as

psal
emp.sal%type;

begin

select sal
into psal from emp where EMPNO=eno;

update emp
set sal = sal +100 where EMPNO=eno;

DBMS_OUTPUT.PUT_LINE(‘涨工资前的薪水’||psal||’涨工资后的薪水’||(psal+100));

end;

/

===========================创建存储函数的语法============================

create
[or replace] function 函数名(参数列表)

return
函数值类型

as

plsql子程序体;

实例:查询某个员工的年收入

create or
replace function queryempincome(eno in number)

return
number

as

–定义变量保存员工薪水和奖金

psal
emp.sal%type;

pcomm
emp.comm%type;

begin

–得到员工的月薪和奖金

select
sal,comm into psal,pcomm from emp where empno=eno;

–直接返回年收入

return
psal*12+pcomm;

end;

/

===========================in和out参数=============================

过程和函数可以通过out指定一个或多个输出参数,我们可以利用out参数,在过程和函数中实现多个返回值

原则:如果只有一个返回值,就用存储函数,否则,就用存储过程

实例:out参数,查询员工姓名,月薪和职位

create or
replace procedure queryempinform(eno in number,

pname out
varchar2,

psal out
number,

pjob out
varchar2)

as

begin

–得到该员工的姓名,月薪和职位

select
ename,sal,ejob into pname,psal,pjob from emp where empno=eno;

end;

/

==========================在out参数中使用光标=========================

案例:查询某个部门中所有员工的所有信息

–包头:声明

create or
replace package mypackage as

type
empcursor is ref cursor;

procedure
queryEmpList(dno in number,empList out empcursor);

end
mypackage;

–包体:实现包头声明的所有方法

create or
replace package body mypackage as

procedure
queryEmpList(dno in number,empList out empcursor)

as

begin

open
empList for select * from emp where deptno=dno;

end
queryEmpList;

end
mypackage;

*oracle**触发器***

========================什么是触发器(trigger)==========================

①数据库触发器是一个与表相关联的,存储的pl/sql程序

②没当一个特定的数据操作语句(insert,update,delete)在指定的表上发出时,oracle自动的执行触发器中定义的语句序列

如何创建触发器

create
trigger saynewemp

after
insert

on
emp

declare

begin

相关文章

Leave a Reply

电子邮件地址不会被公开。 必填项已用*标注