Oracle03——游标、相当、存储过程、存储函数、触发器和Java代码访问Oracle对象

3.触发器实际用

急需:使用系列,触发器来效仿mysql中从增效益

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();

        }

    }

 

1.函数概念
--统计年薪的函数

create or replace function fun_countyearsal(eno in number)

return number

as

   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.数据库连接字符串

String driver="oracle.jdbc.OracleDriver";

String url="jdbc:oracle:thin:@192.168.56.10:1521:orcl";

String username="scott";

String password="tiger";

测试代码: 

图片 1

 

 

1.经过定义
--统计年薪的过程

create or replace procedure proc_countyearsal(eno in number,esal out number)

as

begin

   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;

  

作者: kent鹏  

1.触发器效能

l 数据肯定 

l 实施复杂的安全性检查

l 举行审计,跟踪表上所举行的数操作等 

l 数据的备份和同步 

 

3.测试效果

insert into itcastuser(name) values('aa');

commit;

insert into itcastuser(name) values('bb');

commit;

 

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;  

 

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.调用函数
 

5.触发器

数据库触发器是一个和申相关联的、存储的PL/SQL程序。每当一个一定的数目操作语词(Insert,update,delete)在指定的表上发出时,Oracle自动地实施触发器中定义的报句子体系。 

 

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 here

begin

  dbms_output.put_line('一个员工被插入');

end testTrigger;

 

范例:不可知当休息时间插入员工

create or replace trigger validInsertPerson

  before insert on person



declare

  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

:new

Insert

所有字段都是空(null)

将要插入的数据

Update

更新以前该行的值

更新后的值

delete

删除以前该行的值

所有字段都是空(null)

范例:判断员工涨工资之后的工薪的值一定即便盖涨工资以前的薪资

create or replace trigger addsal4p

  before update of sal on myemp

  for each row

begin

  if :old.sal >= :new.sal then

    raise_application_error(-20002, '涨前的工资不能大于涨后的工资');

  end if;

end;

 

调用

update myemp t set t.sal = t.sal - 1;

 

1.概念过程,并重临引用型游标

--定义过程

create or replace procedure proc_cursor_ref(dno in number,empList out sys_refcursor)

as

begin

  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;

 

转载请注解出处: http://www.cnblogs.com/xieyupeng/p/7476717.html 

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.Java代码访问Oracle对象

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;

光标属性:%isopen
%rowcount(影响之行数)  %found  %notfound

范例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;

 

1.java连接oracle的jar包

得当虚拟机中xp的oracle安装目录下找到jar包 :ojdbc14.jar

 

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测试
 

3.落实过程以及函数的调用

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;

 

用户也得以自定义很是,在宣称遭来定义非凡

DECLARE

My_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;

 

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;

 

1.调所以经过

3.储存过程

存储过程(Stored
Procedure)是当大型数据库系统中,一组为好一定效率的SQL 语句集,经编译后存储在数据库被,用户通过点名存储过程的讳并吃闹参数(假诺该存储过程带有参数)来施行其。存储过程是数据库中之一个要目的,任何一个计划好的数据库应用程序都应当据此到囤过程。

 

创办存储过程语法:

create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]  

AS

begin

        PLSQL子程序体;

End;

或者

create [or replace] PROCEDURE 过程名[(参数名 in/out 数据类型)]  

is

begin

        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;

 

相关文章