存储过程，存储函数，触发器。。。

1.游标Cursor在写java程序中有集合的概念，那么在pl/sql中也会用到多条记录，这时候我们就要用到游标，游标可以存储查询返回的多条数据。语法：    CURSOR  游标名  [ (参数名  数据类型,参数名 数据类型,...)]  IS  SELECT   语句;例如：cursor c1 is select ename from emp;游标的使用步骤：打开游标：      open c1;    (打开游标执行查询)取一行游标的值：fetch c1 into pjob; (取一行到变量中)关闭游标：       close  c1;(关闭游标释放资源)游标的结束方式   exit when c1%notfound注意： 上面的pjob必须与emp表中的job列类型一致：         定义：pjob emp.empjob%type;范例1：使用游标方式输出emp表中的员工编号和姓名declare  cursor pc is    select * from emp;  pemp emp%rowtype;begin  open pc;  loop    fetch pc      into pemp;    exit when pc%notfound;    dbms_output.put_line(pemp.empno || ' ' || pemp.ename);  end loop;  close pc;end;范例2：写一段PL/SQL程序，为部门号为10的员工涨工资。declare  cursor pc(dno myemp.deptno%type) is    select empno from myemp where deptno = dno;  pno myemp.empno%type;begin  open pc(20);  loop    fetch pc      into pno;    exit when pc%notfound;    update myemp t set t.sal = t.sal + 1000 where t.empno = pno;  end loop;  close pc;end;2.异常异常是程序设计语言提供的一种功能，用来增强程序的健壮性和容错性。系统定义异常 no_data_found    (没有找到数据)too_many_rows   (select …into语句匹配多个行) zero_divide   ( 被零除)value_error   (算术或转换错误)timeout_on_resource  (在等待资源时发生超时)范例1：写出被0除的异常的plsql程序declare  pnum number;begin  pnum := 1 / 0;exception  when zero_divide then    dbms_output.put_line('被0除');  when value_error then    dbms_output.put_line('数值转换错误');  when others then    dbms_output.put_line('其他错误');end;用户也可以自定义异常，在声明中来定义异常DECLAREMy_job   char(10);v_sal   emp.sal%type;No_data    exception;cursor c1 is select distinct job from emp    order by job;如果遇到异常我们要抛出raise no_data;范例2：查询部门编号是50的员工declare  no_emp_found exception;  cursor pemp is    select t.ename from emp t where t.deptno = 50;  pename emp.ename%type;begin  open pemp;  fetch pemp    into pename;  if pemp%notfound then    raise no_emp_found;  end if;  close pemp;exception  when no_emp_found then    dbms_output.put_line('没有找到员工');  when others then    dbms_output.put_line('其他错误');end;3.存储过程存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。创建存储过程语法：create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]  AS begin        PLSQL子程序体；End;或者create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]  isbegin        PLSQL子程序体；End  过程名;范例1：给指定的员工涨100工资，并打印出涨前和涨后的工资分析：我们需要使用带有参数的存储过程create or replace procedure addSal1(eno in number) is  pemp myemp%rowtype;begin  select * into pemp from myemp where empno = eno;  update myemp set sal = sal + 100 where empno = eno;  dbms_output.put_line('涨工资前' || pemp.sal || '涨工资后' || (pemp.sal + 100));end addSal1;调用begin  -- Call the procedure  addsal1(eno => 7902);       commit;end;4.存储函数create or replace function 函数名(Name in type, Name out type, ...) return 数据类型 is  结果变量 数据类型;begin  return(结果变量);end[函数名];存储过程和存储函数的区别一般来讲，过程和函数的区别在于函数可以有一个返回值；而过程没有返回值。 但过程和函数都可以通过out指定一个或多个输出参数。我们可以利用out参数，在过程和函数中实现返回多个值。范例：使用存储函数来查询指定员工的年薪create or replace function empincome(eno in emp.empno%type) return number is  psal  emp.sal%type;  pcomm emp.comm%type;begin  select t.sal into psal from emp t where t.empno = eno;  return psal * 12 + nvl(pcomm, 0);end;使用存储过程来替换上面的例子create or replace procedure empincomep(eno in emp.empno%type, income out number) is  psal emp.sal%type;  pcomm emp.comm%type;begin  select t.sal, t.comm into psal, pcomm from emp t where t.empno = eno;  income := psal*12+nvl(pcomm,0);end empincomep;调用：declare  income number;begin  empincomep(7369, income);  dbms_output.put_line(income);end;5.触发器数据库触发器是一个与表相关联的、存储的PL/SQL程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle自动地执行触发器中定义的语句序列。 1.触发器作用数据确认  实施复杂的安全性检查做审计，跟踪表上所做的数据操作等 数据的备份和同步 2.触发器的类型 语句级触发器 ：在指定的操作语句操作之前或之后执行一次，不管这条语句影响             了多少行 。 行级触发器（FOR EACH ROW） ：触发语句作用的每一条记录都被触发。在行级触        发器中使用old和new伪记录变量, 识别值的状态。 语法：CREATE  [or REPLACE] TRIGGER  触发器名   {BEFORE | AFTER}   {DELETE | INSERT | UPDATE [OF 列名]}   ON  表名   [FOR EACH ROW [WHEN(条件) ] ]declare    ……begin   PLSQL 块 End 触发器名范例：插入员工后打印一句话“一个新员工插入成功”create or replace trigger testTrigger  after insert on person  declare  -- local variables herebegin  dbms_output.put_line('一个员工被插入');end testTrigger;范例：不能在休息时间插入员工create or replace trigger validInsertPerson  before insert on persondeclare  weekend varchar2(10);begin  select to_char(sysdate, 'day') into weekend from dual;  if weekend in ('星期一') then    raise_application_error(-20001, '不能在非法时间插入员工');  end if;end validInsertPerson;当执行插入时会报错在触发器中触发语句与伪记录变量的值触发语句    :old    :newInsert  所有字段都是空(null)   将要插入的数据Update  更新以前该行的值    更新后的值delete  删除以前该行的值    所有字段都是空(null)范例：判断员工涨工资之后的工资的值一定要大于涨工资之前的工资create or replace trigger addsal4p  before update of sal on myemp  for each rowbegin  if :old.sal >= :new.sal then    raise_application_error(-20002, '涨前的工资不能大于涨后的工资');  end if;end;调用update myemp t set t.sal = t.sal - 1;3.触发器实际应用需求：使用序列，触发器来模拟mysql中自增效果1.创建序列1、建立表复制代码 代码如下:create table user  (       id   number(6) not null,       name   varchar2(30)   not null primary key  )  2、建立序列SEQUENCE代码如下:create sequence user_seq increment by 1 start with 1 minvalue 1 maxvalue 9999999999999 nocache order;2.创建自增的触发器分析：创建一个基于该表的before insert 触发器，在触发器中使用刚创建的SEQUENCE。代码如下:create or replace trigger user_trigger   before insert on user  for each row   begin        select   user_seq.nextval  into:new.id from sys.dual ;   end;  3.测试效果 insert into itcastuser(name) values('aa');commit;insert into itcastuser(name) values('bb');commit;5.Java代码访问Oracle对象1.java连接oracle的jar包可以在虚拟机中xp的oracle安装目录下找到jar包 :ojdbc14.jar2.数据库连接字符串String driver="oracle.jdbc.OracleDriver";String url="jdbc:oracle:thin:@192.168.56.10:1521:orcl";String username="scott";String password="tiger";测试代码：3.实现过程与函数的调用1.调用过程1.过程定义--统计年薪的过程create or replace procedure proc_countyearsal(eno in number,esal out number)asbegin   select sal*12+nvl(comm,0) into esal from emp where empno=eno;end;--调用declare   esal number;begin   proc_countyearsal(7839,esal);   dbms_output.put_line(esal);end;2.过程调用@Test    public void testProcedure01(){        String driver="oracle.jdbc.OracleDriver";        String url="jdbc:oracle:thin:@192.168.56.10:1521:orcl";        String username="scott";        String password="tiger";        try {            Class.forName(driver);            Connection con  = DriverManager.getConnection(url, username, password);            CallableStatement callSt = con.prepareCall("{call proc_countyearsal(?,?)}");            callSt.setInt(1, 7839);            callSt.registerOutParameter(2, OracleTypes.NUMBER);            callSt.execute();            System.out.println(callSt.getObject(2));        } catch (Exception e) {            e.printStackTrace();        }    }2.调用函数1.函数定义--统计年薪的函数create or replace function fun_countyearsal(eno in number) return numberas   esal number:=0;begin  select sal*12+nvl(comm,0) into esal from emp where empno=eno;  return esal;end;--调用declare   esal number;begin   esal:=fun_countyearsal(7839);   dbms_output.put_line(esal);end;2.函数调用@Test    public void testFunction01(){        String driver="oracle.jdbc.OracleDriver";        String url="jdbc:oracle:thin:@192.168.56.10:1521:orcl";        String username="scott";        String password="tiger";        try {            Class.forName(driver);            Connection con  = DriverManager.getConnection(url, username, password);            CallableStatement callSt = con.prepareCall("{?= call fun_countyearsal(?)}");            callSt.registerOutParameter(1, OracleTypes.NUMBER);            callSt.setInt(2, 7839);            callSt.execute();            System.out.println(callSt.getObject(1));        } catch (Exception e) {            e.printStackTrace();        }    }4.游标引用的java测试1.定义过程，并返回引用型游标--定义过程create or replace procedure proc_cursor_ref(dno in number,empList out sys_refcursor)asbegin  open empList for select * from emp where deptno = dno;end;--pl/sql中调用declare  mycursor_c sys_refcursor;  myempc emp%rowtype;begin  proc_cursor_ref(20,mycursor_c);  loop    fetch mycursor_c into myempc;    exit when mycursor_c%notfound;    dbms_output.put_line(myempc.empno||','||myempc.ename);  end loop;  close mycursor_c;end;2.java代码调用游标类型的out参数@Test    public void testFunction(){        String driver="oracle.jdbc.OracleDriver";        String url="jdbc:oracle:thin:@192.168.56.10:1521:orcl";        String username="scott";        String password="tiger";        try {            Class.forName(driver);            Connection con  = DriverManager.getConnection(url, username, password);            CallableStatement callSt = con.prepareCall("{call proc_cursor_ref (?,?)}");            callSt.setInt(1, 20);            callSt.registerOutParameter(2, OracleTypes.CURSOR);            callSt.execute();            ResultSet rs = ((OracleCallableStatement)callSt).getCursor(2);            while(rs.next()){                System.out.println(rs.getObject(1)+","+rs.getObject(2));            }        } catch (Exception e) {            e.printStackTrace();        }    }