Oracle学习(12):存储过程,函数和触发器
存储过程和存储函数
l存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数。
注意:存储过程与存储函数声明变量时,用的是as 而不是declare
存储过程与存储函数区别
存储过程不带有返回值,存储函数有返回值存储过程
创建存储过程
l用CREATE PROCEDURE命令建立存储过程l语法:
create [or replace] PROCEDURE过程名(参数列表)
AS
PLSQL子程序体;
创建存储过程简单示例
/*第一个存储过程:Hello World
调用存储过程:
1. exec sayHello(); 2. begin sayHello(); end; / */ create or replace procedure sayHello as --变量说明 begin dbms_output.put_line('Hello World'); end; /
调用存储过程
方法一
set serveroutput on
begin
raisesalary(7369);
end;
/
方法二
set serveroutput on
exec raisesalary(7369);
存储过程(涨工资实例)
实例一
为指定的职工在原工资的基础上长10%的工资,并打印涨工资前和涨工资后的工资
代码: ************************************************************************************************** /* 为指定的职工在原工资的基础上长10%的工资,并打印涨工资前和涨工资后的工资 可能用到的sql语句 update emp set sal = sal * 1.1 where empno = empid; */ create or replace procedure raiseSalary(empid in number) as pSal emp.sal%type; --保存员工当前工资 begin --查询该员工的工资 select sal into pSal from emp where empno=empid; --给该员工涨工资 update emp set sal = sal * 1.1 where empno = empid; --打印涨工资前后的工资 dbms_output.put_line('员工号:' || empid || ' 涨工资前:' || psal || ' 涨工资后' || psal * 1.1); end; / **************************************************************************************************
实例二
为指定员工增加指定额度的工资(传递多个参数)代码: ************************************************************************************************** create or replace procedure raiseSalary2(empid in number, rate in NUMBER) as pSal emp.sal%type; --保存员工当前工资 begin --查询该员工的工资 select sal into pSal from emp where empno=empid; --给该员工涨工资 update emp set sal = sal * rate where empno = empid; --打印涨工资前后的工资 dbms_output.put_line('员工号:' || empid || ' 涨工资前:' || psal || ' 涨工资后' || psal * rate); end; / **************************************************************************************************
存储函数
l函数(Function)为一命名的存储程序,可带参数,并返回一计算值。函数和过程的结构类似,但必须有一个RETURN子句,用于返回函数值。函数说明要指定函数名、结果值的类型,以及参数类型等。l建立存储函数的语法: l
CREATE [OR REPLACE] FUNCTION函数名(参数列表)
RETURN 函数值类型
AS
PLSQL子程序体;
存储函数示例
示例:查询某职工的年收入。代码:
**************************************************************************** /* 查询某职工的总收入。 */ create or replace function queryEmpSalary(empid in number) RETURN NUMBER as pSal number; --定义变量保存员工的工资 pComm number; --定义变量保存员工的奖金 begin select sal,comm into pSal, pcomm from emp where empno = empid; return psal*12+ nvl(pcomm,0); end; / ****************************************************************************
存储函数的调用
调用一
declare
v_salnumber;
begin
v_sal:=queryEmpSalary(7934);
dbms_output.put_line('salary is:' ||v_sal);
end;
/
调用二
begin
dbms_output.put_line('salaryis:' ||queryEmpSalary(7934));
end;
过程和函数中的in和out
l一般来讲,过程和函数的区别在于函数可以有一个返回值;而过程没有返回值。
l但过程和函数都可以通过out指定一个或多个输出参数。我们可以利用out参数,在过程和函数中实现返回多个值。
带out函数的存储过程示例
/* out参数:查询某个员工的姓名,月薪和职位 */ create or replace procedure queryEmpInfo(eno in number, pename out varchar2, psal out number, pjob out varchar2) as begin select ename,sal,empjob into pename,psal,pjob from emp where empno=eno; end; /
在out参数中使用游标
l首先申明包结构l然后创建包体
在Java语言中访问游标类型的out参数
代码实现:
package demo.test; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.ResultSet; import oracle.jdbc.OracleTypes; import oracle.jdbc.OracleCallableStatement; import org.junit.Test; import demo.utils.JDBCUtills; /* * Statement < PreparedStatement < CallableStatement */ public class TestOracle { /* * create or replace procedure queryEmpInfo(eno in number, pename out varchar2, psal out number, pjob out varchar2) */ @Test public void testProcedure(){ //存储过程测试用例 //{call procedure-name(??/)} String sql = "{call queryEmpInfo(?,?,?,?)}"; Connection conn = null; CallableStatement call = null; try{ conn = JDBCUtills.getConnection(); call = conn.prepareCall(sql); //Set value to param call.setInt(1, 7839); //declare out parameter call.registerOutParameter(2, OracleTypes.VARCHAR); call.registerOutParameter(3, OracleTypes.NUMBER); call.registerOutParameter(4, OracleTypes.VARCHAR); //run call.execute(); //get returned values String name = call.getString(2); double sal = call.getDouble(3); String job = call.getString(4); System.out.println(name); System.out.println(sal); System.out.println(job); }catch(Exception ex){ ex.printStackTrace(); }finally{ JDBCUtills.release(conn, call, null); } } /* * create or replace function queryEmpIncome(eno in number) return number */ @Test public void testFunction(){ //存储函数测试用例 //{?=call procedure-name(??/)} String sql = "{?= call queryEmpIncome(?)}"; Connection conn = null; CallableStatement call = null; try{ conn = JDBCUtills.getConnection(); call = conn.prepareCall(sql); call.registerOutParameter(1, OracleTypes.NUMBER); call.setInt(2, 7839); call.execute(); double income = call.getDouble(1); System.out.println(income); }catch(Exception ex){ ex.printStackTrace(); }finally{ JDBCUtills.release(conn, call, null); } } @Test public void testCursor(){ //访问游标测试用例 String sql = "{call MYPACKAGE.queryEmpList(?,?)}"; //注意此句要有{} Connection conn = null; CallableStatement call = null; ResultSet rs = null; try{ conn = JDBCUtills.getConnection(); call = conn.prepareCall(sql); call.setInt(1, 20); call.registerOutParameter(2, OracleTypes.CURSOR); call.execute(); rs = ((OracleCallableStatement)call).getCursor(2); while(rs.next()){ String name = rs.getString("ename"); double sal = rs.getDouble("sal"); System.out.println(name+ " " + sal); } }catch(Exception ex){ ex.printStackTrace(); }finally{ JDBCUtills.release(conn, call, rs); } } }
在Java语言中调用
在Java语言中调用存储过程
在Java语言中调用存储函数
什么时候用存储过程/存储函数?
原则:如果只有一个返回值,用存储函数;否则,就用存储过程。
触发器
触发器定义
数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时,Oracle自动地执行触发器中定义的语句序列。触发器的类型
语句级触发器
?在指定的操作语句操作之前或之后执行一次,不管这条语句影响了多少行 。行级触发器(FOR EACH ROW)
?触发语句作用的每一条记录都被触发。在行级触发器中使用old和new伪记录变量, 识别值的状态。创建触发器
CREATE [or REPLACE] TRIGGER 触发器名 {BEFORE | AFTER} {DELETE | INSERT | UPDATE [OF列名]} ON 表名 [FOR EACH ROW [WHEN(条件) ] ] PLSQL块
触发器简单实例
/* 第一个触发器:对update */ create or replace trigger sayHello after update on emp begin dbms_output.put_line('Hello World'); end; /
行级触发器
触发语句与伪记录变量的值触发器应用场景实例
场景一(语句触发器)
/*触发器应用场景一:实现复杂的安全性检查
限制非工作时间向数据库emp插入数据
1. 周末:星期六 星期日 to_char(sysdate,'day') 2. <9 or >18点 to_number(to_char(sysdate,'hh24')) */ create or replace trigger securityEmp before insert on emp begin if to_char(sysdate,'day') in ('星期三','星期六','星期日') or to_number(to_char(sysdate,'hh24')) not between 9 and 18 then raise_application_error(-20001,'不能在非工作时间插入数据'); end if; end; /
场景二(行级触发器)
/*
触发器应用场景二:确认数据
涨工资不能越涨少
行级触发器的两个伪记录变量 :old , :new
*/ create or replace trigger checksal before update on emp for each row begin if :old.sal > :new.sal then raise_application_error(-20002,'涨后的工资不能少于涨前的。涨后:'||:new.sal||' 涨前:'||:old.sal); end if; end; /
触发器练习
限制每个部门只招聘5名职工,超过计划则报出错误信息***************************************************************************** /* 练习:限制每个部门只招聘5名职工,超过计划则报出错误信息 */ create or replace trigger limitEmpCount before insert on emp for each row declare pCount number;-- 保存每个部门的员工数 begin select count(*) into pcount from emp where deptno=:new.deptno; if pcount > 5 then raise_application_error(-20004,'部门:' || :new.deptno || ' 员工已有5人'); end if; end;
本站文章为和通数据库网友分享或者投稿,欢迎任何形式的转载,但请务必注明出处.
同时文章内容如有侵犯了您的权益,请联系QQ:970679559,我们会在尽快处理。