Oracle存储过程及调用-木庄网络博客

本文整理自网络，侵删。

Oracle存储过程语法

Oracle的存储过程语法如下：

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

（2）带参数的存储过程语法：

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

（3）带输入、输出参数的存储过程语法：

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

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

　　　方法一：换个存储过程名

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

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

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

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

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;

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

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

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

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

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

create procedure procedure_3(v_i in number,v_j in number ,v_m out number)
is
begin
    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块中调用存储过程

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

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 = 10
procedure_3.......
2 - 8 = -6
10

java调用存储过程

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

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

/*存储过程*/
create procedure procedure_4(v_empno emp.empno%type,v_ename emp.ename%type,v_job emp.job%type )
is
begin
    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的记录

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

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

/*存储过程*/
create procedure procedure_5(v_empno in emp.empno%type,v_ename out emp.ename%type)
is
begin
    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);//关闭资源
  }  
}
 
/*执行结果,控制台打印：*/
结果：员工姓名：张三

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

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

/*存储过程*/
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)
is
begin
    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调用返回多行多列（返回列表）的存储过程

阅读剩余部分