Oracle 笔记(五)

Oracle存储过程语法

1、              Oracle的自定义函数

Oracle的存储过程语法如下:

2、              Oracle的触发器

create procedure 存储过程名称 is 在这里可以定义常量、变量、游标、复杂数据类型这里可以定义变量、常量begin 执行部分end; 

3、              Oracle的存储过程

带参数的存储过程语法:

 

create procedure 存储过程名称 (变量1 数据类型,变量2 数据类型,...,变量n 数据类型)is 在这里可以定义常量、变量、游标、复杂数据类型这里可以定义变量、常量begin 执行部分end; 

知识点一:自定义函数

带输入、输出参数的存储过程语法:

语法:create [or replace] function 函数名(参数)

create procedure 存储过程名称 (变量1 in(或out) 数据类型,变量2 in(或out) 数据类型,...,变量n in(或out) 数据类型)is 在这里可以定义常量、变量、游标、复杂数据类型这里可以定义变量、常量begin 执行部分end;

              return返回值类型—必须

注意:用上面的语法创建存储过程时可能会碰到数据库中已经有了同名的存储过程,这样Oracle就会弹框报错,说名字已被现有对象使用。解决方法有两种:

              is

方法一:换个存储过程名

       –变量的声明

方法二:在最开头的create procedure 之间加上 or replace
关键字,例如:create or replace procedure
存储过程名称。但是这种方法不建议使用,因为这种方法会把之前同名的存储过程替换为你当前写的这个

begin

存储过程案例一:没参数的存储过程

       –执行语句

create replace procedure procedure_1isbegin dbms_output.put_line('procedure_1.......');end;

       –return 返回变量

存储过程案例二:带参数的的存储过程

       –exception 异常块(return 异常编号)

create procedure procedure_2(v_i number,v_j number)is v_m number(5);begin dbms_output.put_line('procedure_2.......'); v_m := v_i + v_j; dbms_output.put_line(v_i||' + '||v_j||' = '||v_m);end;

              end;

存储过程案例三:带输入、输出参数的存储过程

Java:

存储过程的参数分为输入参数和输出参数,

int add(int no1,int no2)

输入参数:输入参数一般会在变量名和数据类型之间加in来表示该参数是输入参数

{

输出参数:输出参数一般会在变量名和数据类型之间加out来表示该变量是输出参数

   int sum = no1+no2;

不写in和out的话,默认为输入参数

   return sum;

create procedure procedure_3(v_i in number,v_j in number ,v_m out number)isbegin dbms_output.put_line('procedure_3.......'); v_m:=v_i - v_j; dbms_output.put_line(v_i||' - '||v_j||' = '||v_m);end;

}

PL/SQL块中调用存储过程

 

下面以调用上面三个存储过程为例

Oracle

declare v_param1 number(5):=2; v_param2 number(5):=8; v_result number(5);begin --调用上面案例一的存储过程 procedure_1(); --调用上面案例二的存储过程 procedure_2(v_param1,v_param2); --调用上面案例三的存储过程 procedure_3(v_param1,v_param2,v_result); dbms_output.put_line(v_result);end;/*执行结果:*/procedure_1.......procedure_2.......2 + 8 = 10procedure_3.......2 - 8 = -610

–编写一个简单的自定义函数

createorreplacefunction fun_add(no1 number,no2 number)

returnnumber

is

v_sum number;

begin

  v_sum := no1+no2;

return v_sum;

exception

whenothersthen

return-1;

end;

java调用存储过程

 

案例一:java调用没有返回值的存储过程

调用函数

要求:编写一个像数据库emp表插入一条编号为6666,姓名为张三,职位为MANAGER的记录

方式一:

/*存储过程*/create procedure procedure_4(v_empno emp.empno%type,v_ename emp.ename%type,v_job emp.job%type )isbegin insert into emp (empno,ename,job) values (v_empno,v_ename,v_job);end;//java调用存储过程public static void main(String[] args) { Connection conn=null; CallableStatement cs=null; ResultSet rs=null; //java调用存储过程 try { Class.forName("oracle.jdbc.OracleDriver"); conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.01:1521:orcl", "scott", "tiger"); cs=conn.prepareCall("{call procedure_4(,,)}"); //给输入参数赋值 cs.setInt(1, 6666); cs.setString(2, "张三"); cs.setString(3, "MANAGER"); cs.execute();//执行 } catch (Exception e) { e.printStackTrace(); }finally{ closeResource(conn,cs,rs);//关闭资源 } }//执行后就会向数据库的emp表中插入一条编号为6666,姓名为张三,职位为MANAGER的记录

select fun_add(50,50) from dual;

案例二:java调用返回单列单行的存储过程

 

要求:编写一个根据员工编号查找员工姓名的存储过程,并用java调用该存储过程

方式二:

/*存储过程*/create procedure procedure_5(v_empno in emp.empno%type,v_ename out emp.ename%type)isbegin select ename into v_ename from emp where empno=v_empno;end;//java调用存储过程public static void main(String[] args) { Connection conn=null; CallableStatement cs=null; ResultSet rs=null; try { Class.forName("oracle.jdbc.OracleDriver"); conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.01:1521:orcl", "scott","tiger"); cs=conn.prepareCall("{call procedure_5(,)}"); cs.setInt(1, 6666);//给输入参数赋值 /*指定输出参数的数据类型 语法:oracle.jdbc.OracleTypes.输出参数的数据类型 此例输出参数的数据类型是varchar,所以是oracle.jdbc.OracleTypes.VARCHAR*/ cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR); cs.execute();//执行 //获取输出参数的值,位置要和输出参数对应?的位置对应起来,该例输出参数对应第2个问号,而且输出参数的数据类型为字符型,所以是cs.getString(2) String a=cs.getString(2); System.out.println("员工姓名:"+a); } catch (Exception e) { e.printStackTrace(); }finally{ closeResource(conn,cs,rs);//关闭资源 } } /*执行结果,控制台打印:*/结果:员工姓名:张三

declare

v_no1 number(2);

v_no2 number(2);

v_sum number(3);

begin

  v_no1 :=50;

  v_no2 :=50;

  v_sum := fun_add(v_no1,v_no2);

 

if v_sum =-1then

     dbms_output.put_line(‘输入的数据有误!’);

else

       dbms_output.put_line(v_sum);

endif;

 

exception

whenothersthen

      dbms_output.put_line(‘输入的数据有误!’);

end;

案例三:java调用返回单行多列的存储过程

 

要求:编写一个根据员工编号查找员工姓名、职位和工资的存储过程,并用java调用该存储过程

知识点二:触发器 trigger(DML触发器)

/*存储过程*/create procedure procedure_6(v_empno in emp.empno%type,v_ename out emp.ename%type,v_job out emp.job%type,v_sal out emp.sal%type)isbegin select ename,job,sal into v_ename,v_job,v_sal from emp where empno=v_empno;end;//java调用存储过程public static void main(String[] args) { Connection conn=null; CallableStatement cs=null; ResultSet rs=null; try { Class.forName("oracle.jdbc.OracleDriver"); conn=DriverManager.getConnection("jdbc:oracle:thin:@127.0.01:1521:orcl", "scott","tiger"); cs=conn.prepareCall("{call procedure_6(,,,)}"); cs.setInt(1, 7788); //指定输出参数的数据类型,注意:顺序要对应起来 cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR); cs.registerOutParameter(3, oracle.jdbc.OracleTypes.VARCHAR); cs.registerOutParameter(4, oracle.jdbc.OracleTypes.DOUBLE); cs.execute();//执行 //获取返回值 String ename=cs.getString(2);//获取姓名 String job=cs.getString(3);//获取职位 double sal=cs.getDouble(4);//获取薪水 System.out.println("员工编号为7788的姓名为:"+ename+" 职位是:"+job+" 薪水是:"+sal); } catch (Exception e) { e.printStackTrace(); }finally{ closeResource(conn,cs,rs);//关闭资源 }}/*执行结果,控制台打印:*/员工编号为7788的姓名为:SCOTT 职位是:ANALYST 薪水是:3000.0

语法:

案例四:java调用返回多行多列的存储过程

create[or replace]  trigger tri_名字