oracle is ref cursor java_oracle 存储过程及REF CURSOR的使用

CREATE TABLEuser_info

(

idVARCHAR2(4) not null primary key,

nameVARCHAR2(15),

pwdVARCHAR2(15),

addressVARCHAR2(30)

);--创建一个添加用户的stored_procedure;

create or replace procedurepro_addUser(

n_id user_info.id%type,

n_name user_info.name%type,

n_pwd user_info.pwd%TYPE,

n_address user_info.address%TYPE

)as

begin

--插入数据

insert intouser_info(id,name,pwd,address)values(n_id, n_name, n_pwd, n_address);endpro_addUser;--调用、有变量需要声明的时候才有declare、没有就直接begin

beginpro_addUser('1', 'chy', 'admin', 'nanjin');if SQL%found thendbms_output.put_line('add successed');end if;end;--根据id查询用户名和密码

create or replace procedurepro_getUserInfo(

n_id user_info.id%type,

n_name out user_info.name%type,

n_pwd out user_info.pwd%type

)as

begin

select user_info.name, user_info.pwd inton_name, n_pwdfromuser_infowhere user_info.id=n_id;endpro_getUserInfo;--调用

declarev_id user_info.id%type := '1';

v_name user_info.name%type;

v_pwd user_info.pwd%type;beginpro_getUserInfo(v_id, v_name, v_pwd);

dbms_output.put_line('name:' || v_name || 'pwd:' ||v_pwd);end;--打印九九乘法表

create or replace procedurepro_multiplication_tableisiinteger;

jinteger;begin

for i in 1..9loopfor j in 1..9loopif i>=j thenDBMS_output.put(To_Char(j)||'*'||to_char(i)||'='||to_char(i*j)||'　　 ');end if;endloop;

DBMS_output.put_line('');endloop;endpro_multiplication_table;--调用

call pro_multiplication_table();--使用自定义游标、根据工作and薪水查询员工姓名

create or replace procedurepro_getName(

n_sal emp.sal%type,

n_ename out emp.ename%type,

n_jobin out emp.job%type

)isn_countnumber;cursor cur is select ename from emp where emp.sal > n_sal and emp.job=n_job;

n_row cur%rowtype;begin

select count(*) into n_count from emp where emp.sal > n_sal and emp.job=n_job;if n_count > 1 then

for n_row incur loop

DBMS_output.put_line('职工姓名为:'||n_row.ename||'工作为:'||n_job);endloop;elseDBMS_output.put_line('未查到符合条件的记录！');end if;endpro_getName;--调用

declarev_sal emp.sal%type := 2000;

v_job emp.job%type :='MANAGER';

v_ename emp.ename%type;beginpro_getName(v_sal, v_ename, v_job);end;--ref cursor的使用

--创建存放弱引用和强引用的cursor的包

create or replacepackage refcursor_pkgastype weak_ref_cursoris ref cursor;

type strong_ref_cursoris ref cursor return emp%rowtype;endrefcursor_pkg;--将弱引用的cursor作为结果返回

create or replace proceduretest(

p_deptnoin number,

p_cursor out refcursor_pkg.weak_ref_cursor

)is begin

open p_cursor for select * from emp where deptno=p_deptno;endtest;/**或者不用包直接使用下面这种定义

create or replace procedure test_1(

p_deptno IN number,

p_cursor OUT SYS_REFCURSOR

)

is

begin

open p_cursor FOR select *from emp where deptno = p_deptno;

end test_1;*/

declarev_deptnonumber := 20;

v_cursor refcursor_pkg.weak_ref_cursor;

r_emp emp%rowtype;begintest(v_deptno, v_cursor);

loopfetch v_cursor intor_emp;exit when v_cursor%notfound;

dbms_output.put_line('empno:' || r_emp.empno || 'ename:' || r_emp.ename || 'job:' ||r_emp.job);endloop;closev_cursor;end;/**

//java中使用ref cursor

public void method() throws SQLException{

Connection conn = getConnection();

CallableStatement cstmt = null;

ResultSet rs = null;

int deptno = 10;

Object temp;

try{

cstmt = conn.prepareCall("begin test(?,?); end;");

cstmt.setInt(1, deptno);

cstmt.registerOutParameter(2, OracleTypes.CURSOR);

cstmt.execute();

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

ResultSetMetaData rsm = rs.getMetaData();

int columnCount = rsm.getColumnCount();

while (rs.next()){

for (int j=0;j< columnCount;j++){

temp = rs.getObject(j+1);

}

}

} finally {

if (!rs==null){

rs.close();

}

if (!stmt==null){

stmt.close();

}

if (!conn==null){

conn.close();

}

}

}*/