oracel procedure(存储过程初识1)

jdbc 调用存储过程的四种方式

1.只有输入参数(in)

2.既有输入参数(in)又有输出参数(out,简单输出结果，非列表)

3.既有输入参数(in)又有输出参数(out,简单输出列表)

4.输入参数(in)和输出参数(out)为同一个

写一些简单的例子学习存储过程的写法

准备工作：简单的雇员表，ID,NAME SALARY,ADDRESS

create table EMPLOYEE

(

  ID       NUMBER(8) not null,

  NAME     VARCHAR2(100),

  SALARY   NUMBER(8),

  ADDRESS VARCHAR2(100)

)

然后随意插入几个简单的结果备测试用；下面是我数据截图：

1. 只有输入参数(in)

    a)编写存储过程：

create or replace procedure inwithparameter(inid      in number,

                                            inname     in varchar2,

                                            insalary   in number,

                                            inaddress in varchar2)

is

begin

insert into employee (id,name,salary,address) values(inid,inname,insalary,inaddress);

end inwithparameter;

    b)java 代码实现

public   void inWithPara(){

Connection conn = JdbcUtils.getConnection();

try{

CallableStatement cstmt = conn.prepareCall("{call inwithparameter(?,?,?,?)}");

cstmt.setInt(1, 10);

cstmt.setString(2, "周十");

cstmt.setInt(3, 8888);

cstmt.setString(4, "昌平线");

cstmt.execute();

}catch(Exception e){e.printStackTrace();}

JdbcUtils.close(null, null, conn);

}

2.既有输入参数(in)又有输出参数(out,简单输出结果，非列表)

    a)编写存储过程：

create or replace procedure inandout(e_userid   in number,

                                      e_salary   in number,

                                      e_counts out varchar2)

is

formal_salary number := e_salary ;

begin

if formal_salary is null then formal_salary := 0 ;end if;

if e_userid is null then select count(*) into e_counts from employee e where e.salary > formal_salary ;

else

select count(*) into e_counts from employee e where e.salary > formal_salary and e.id = e_userid;

end if;

end inandout;

    b)java 代码实现

public   void inAndOut(){

Connection conn = JdbcUtils.getConnection();

System.out.println("测试输出简单结果的存储过程开始。。。。");

try{

CallableStatement cstmt = conn.prepareCall("{call inandout(?,?,?)}");

cstmt.setString(1, "");

cstmt.setInt(2, 8000);

//设置输出参数类型

cstmt.registerOutParameter(3, Types.INTEGER);

cstmt.execute();

int i = cstmt.getInt(3);

System.out.println("符合条件数量为 =     "+i);

}catch(Exception e){e.printStackTrace();}

JdbcUtils.close(null, null, conn);

System.out.println("测试结束。。。。");

}

3.既有输入参数(in)又有输出参数(out,简单输出列表)

    a)编写存储过程：

先建立一个游标：

create or replace package test_pkg_cursor is

type test_cursor is ref cursor;

end test_pkg_cursor;

编写存储过程

create or replace procedure out_lists(e_salary in number,

                                      e_lists   out test_pkg_cursor.test_cursor) is

  formal_salary number := e_salary;

begin

  if formal_salary is null then

    formal_salary := 0;

  end if;

  open e_lists for

    select * from employee e where e.salary > formal_salary;

end out_lists;

    b)java 代码实现

public void inAndOutList(){

Connection conn = JdbcUtils.getConnection();

System.out.println("测试输出列表的存储过程开始。。。。");

ResultSet rs = null;

try{

CallableStatement cstmt = conn.prepareCall("{call out_lists(?,?)}");

cstmt.setString(1, "");

//设置输出参数类型

cstmt.registerOutParameter(2, OracleTypes.CURSOR);

cstmt.execute();

//输出列表

rs = (ResultSet) cstmt.getObject(2);

//获取列名和类型

int colunmCount = rs.getMetaData().getColumnCount();

System.out.println("列数=="+colunmCount);

String[] colunmName = new String[colunmCount];

String[] colunmType = new String[colunmCount];

for (int i = 0; i < colunmCount; i++) {

colunmName[i] = rs.getMetaData().getColumnName(i+1);

colunmType[i] = rs.getMetaData().getColumnTypeName(i+1);

System.out.print(colunmName[i]+"("+colunmType[i]+")"+"|");

}

System.out.println();

while(rs.next()){

StringBuffer sb = new StringBuffer();

for (int i = 0; i < colunmCount; i++) {

sb.append(rs.getString(i+1)+"|");

}

System.out.println(sb.toString());

}

}catch(Exception e){e.printStackTrace();}

JdbcUtils.close(null, null, conn);

System.out.println("测试结束。。。。");

}

4.输入参数(in)和输出参数(out)为同一个

    a)编写存储过程：

create or replace procedure samepara(e_salary in out number) is

  formal_count   number;

  formal_salary number := e_salary;

begin

  if formal_salary is null then

    formal_salary := 0;

  end if;

  select count(*)

    into formal_count

    from employee e

    where e.salary >= formal_salary;

  e_salary := formal_count;

end samepara;

    b)java 代码实现

public void inAndOutWithSinglePara(){

Connection conn = JdbcUtils.getConnection();

System.out.println("测试开始。。。。");

try{

CallableStatement cstmt = conn.prepareCall("{call samepara(?)}");

cstmt.setInt(1, 8000);

//设置输出参数类型

cstmt.registerOutParameter(1, Types.INTEGER);

cstmt.execute();

int i = cstmt.getInt(1);

System.out.println("符合条件数量为 =     "+i);

}catch(Exception e){e.printStackTrace();}

JdbcUtils.close(null, null, conn);

System.out.println("测试结束。。。。");

}