一、存储过程
--(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中可以灵活,方便的调用存储过程!