Oracle学习总结5-存储过程，存储函数，触发器
二．存储过程与存储函数：procedure
1.hello world
create or replace procedure hello_world is begin dbms_output.put_line('hello world'); end hello_world;
2. --给指定员工涨100工资，并打印涨前涨后的薪水(存储过程)
create or replace procedure raiseSalary(eno in number) is 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 raiseSalary;
3. --查询某个员工的年收入(存储函数)
create or replace function queryEmpIncome(eno in number) return number is income number; begin select sal*12+nvl(comm,0) into income from emp where empno=eno; return income; end queryEmpIncome;
4. --查询某个员工的姓名薪水和职位
create or replace procedure queryEmpInformation(eno in number,pename out varchar2,psal out number,pjob out varchar2) is begin select ename,sal,job into pename,psal,pjob from emp where empno=eno; end queryEmpInformation;
5. --查询某个员工的年收入的java调用(存储函数调用)
//存储函数调用 @Test public void testFunction(){ String sql = "{?=call queryEmpIncome(?)}"; Connection conn = null; CallableStatement call = null; try { conn=JDBCUtils.getConnection(); call=conn.prepareCall(sql); //返回值声明 call.registerOutParameter(1, OracleTypes.NUMBER); //对于in参数，赋值 call.setInt(2,7839); //执行 call.execute(); //输出 double income=call.getDouble(1); System.out.println(income); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ JDBCUtils.release(conn, call, null); } }
6. --查询某个员工的姓名薪水和职位的java调用（存储过程调用）
@Test public void testProcedure(){ String sql = "{call queryEmpInformation(?,?,?,?)}"; Connection conn = null; CallableStatement call = null; try { conn=JDBCUtils.getConnection(); call=conn.prepareCall(sql); //对于in参数，赋值 call.setInt(1,7839); //对于out参数，申明 call.registerOutParameter(2, OracleTypes.VARCHAR); call.registerOutParameter(3, OracleTypes.NUMBER); call.registerOutParameter(4, OracleTypes.VARCHAR); //执行 call.execute(); //输出 String name=call.getString(2); double sal=call.getDouble(3); String job=call.getString(4); System.out.println(name+"---"+sal+"---"+job); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ JDBCUtils.release(conn, call, null); } }
7.返回多列-使用光标实现。Plsql中建立包和体的结构。然后再由java中的resultset接收
Plsql中：
包：
create or replace package mypackage is type empcursor is ref cursor; procedure queryEmpList(dno in number,empList out empcursor); end mypackage;
体：
create or replace package body mypackage is procedure queryEmpList(dno in number,empList out empcursor) as begin open empList for select * from emp where deptno=dno; end; end mypackage;
java调用：
//存储函数调用,返回光标类型的 @Test public void testCursor(){ String sql = "{call mypackage.QUERYEMPLIST(?,?)}"; Connection conn = null; CallableStatement call = null; ResultSet rs = null; try { conn = JDBCUtils.getConnection(); call = conn.prepareCall(sql); //对于in参数，赋值 call.setInt(1,20); //对于out参数，申明 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+"\t"+sal); } } catch (Exception e) { e.printStackTrace(); }finally{ JDBCUtils.release(conn, call, rs); } }
三．触发器（trigger）
1. 插入员工时候触发
create or replace trigger firsttrigger after insert on emp declare begin dbms_output.put_line('成功插入新员工'); end firsttrigger;
2. --不允许在非工作时间插入数据
create or replace trigger securityemp before insert on emp declare begin if to_char(sysdate,'day') in ('星期六','星期日') or to_number(to_char(sysdate,'hh24')) not between 9 and 17 then raise_application_error(-20001,'禁止在非工作时间插入新员工'); end if; end securityemp;
3. --不允许降薪
create or replace trigger checksalary before update on emp for each row declare begin if :new.sal<:old.sal then raise_application_error(-20002,'涨后的薪水不能少于涨前的薪水。涨前:'||:old.sal||' 涨后:'||:new.sal); end if; end checksalary;
