java 调用Oracle存储过程（输入参数，输出参数，游标）的使用!

怼烎@ 2024-02-17 17:56 33阅读 0赞

**一、存储过程**

\--（1）添加数据  
create or replace procedure proc\_add(mname varchar2,mpwd varchar2,mmoney number,errid out number)  
is  
v\_count number;  
begin  
select count(1) into v\_count from master where name=mname;  
if v\_count>0 then  
errid:=1;  
else  
errid:=0;  
insert into master values(seq\_pid.nextval,mname,mpwd,mmoney);  
end if;  
end;

\--调用  
declare  
errid number;  
begin  
proc\_add('back','aaa',1200,errid);  
dbms\_output.put\_line(errid);  
if errid=1 then  
dbms\_output.put\_line('已存在!');  
else  
dbms\_output.put\_line('可以添加!');  
end if;  
end;

\--（2）登录  
select count(1) from master where name='mike' and pwd='aaa1'  
  
create or replace procedure proc\_login(mname varchar2,mpwd varchar2,msg out varchar2)  
is  
v\_count number;  
begin  
select count(1) into v\_count from master where name=mname and pwd=mpwd;  
if v\_count>0 then  
msg:='登录成功！';  
else  
msg:='登录失败！';  
end if;  
end;

\--调用  
declare  
errid varchar2(20);  
begin  
proc\_login('back','aaa1',errid);  
dbms\_output.put\_line(errid);  
if errid='登录成功！' then  
dbms\_output.put\_line('OK');  
else  
dbms\_output.put\_line('NO');  
end if;  
end;

\--（3）查询所有的数据  
create or replace procedure proc\_all(v\_name varchar2,refMaster out sys\_refcursor)  
is  
begin  
if (v\_name is null) then --是否为空  
open refMaster for select \* from master order by id ;  
else --不为空！  
open refMaster for select \* from master where name like v\_name order by id ;  
end if;  
end;

--cmd命令窗口
      C:\>sqlplus "sqb/aaa@ORCL"
      var c1 refcursor  --声明游标
      execute proc_all(null,:c1);  --执行存储过程
      print c1;   --显示结果

\--调用  
declare  
c1 sys\_refcursor; --引用游标  
type master\_table is table of master%rowtype; --表格  
mytable master\_table; --表格变量  
begin  
\--proc\_all('%a%',c1); --调用存储过程  
proc\_all('',c1); --调用存储过程  
fetch c1 bulk collect into mytable;  
for i in 1..mytable.count loop  
dbms\_output.put\_line(mytable(i).id||'--'||mytable(i).name);  
end loop;  
end;

二、Java调用Oracle存储过程

private static void getAdd(String name, String pwd, double money) {
    		BaseDao dao = new BaseDao();
    		// 1.
    		Connection conn = dao.getConnection();
    
    		String sql = "{ call proc_add(?,?,?,?)}";
    
    		// 2.
    		try {
    			CallableStatement cs = conn.prepareCall(sql);
    
    			// set value
    			cs.setString(1, name);
    			cs.setString(2, pwd);
    			cs.setDouble(3, money);
    			// register out
    			cs.registerOutParameter(4, Types.INTEGER);
    			// execute sql
    			cs.execute();
    			// get value
    			int count = cs.getInt(4);
    
    			if (count > 0) {
    				System.out.println("已存在数据!");
    			} else {
    				System.out.println("添加成功！");
    			}
    
    			// close
    			cs.close();
    			conn.close();
    
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    	}

private static String isLogin(String name, String pwd) {
    		BaseDao dao = new BaseDao();
    		// 1.
    		Connection conn = dao.getConnection();
    
    		String sql = "{ call proc_login(?,?,?)}";
    
    		// 2.
    		try {
    			CallableStatement cs = conn.prepareCall(sql);
    
    			// set value
    			cs.setString(1, name);
    			cs.setString(2, pwd);
    
    			// register out
    			cs.registerOutParameter(3, Types.VARCHAR);
    
    			// execute sql
    			cs.execute();
    
    			// close
    			// cs.close();
    			// conn.close();
    
    			// get value
    			return cs.getString(3);
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    
    		return null;
    	}

private static void list() {
    		BaseDao dao = new BaseDao();
    		// 1.
    		Connection conn = dao.getConnection();
    
    		String sql = "{ call proc_all(?,?)}";
    
    		// 2.
    		try {
    			CallableStatement cs = conn.prepareCall(sql);
    
    			cs.setString(1, "%a%");
    //			cs.setString(1, "");
    			// register out cursor
    			cs.registerOutParameter(2, OracleTypes.CURSOR);
    
    			// execute sql
    			cs.execute();
    
    			ResultSet rs = (ResultSet) cs.getObject(2);
    			while (rs.next()) {
    				System.out.println(rs.getInt(1) + "\t" + rs.getString(2) + "\t"
    						+ rs.getString(3));
    			}
    
    			// close
    			cs.close();
    			conn.close();
    
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    
    	}

总结：

Java中可以灵活，方便的调用存储过程！