select * from dept
select * from (select e.empno,e.ename,rownum r from (select empno,ename from emp order by empno) e where rownum>=20)where r<=30
--无返回值的存储过程
create or replace procedure proc_ins_dept
(procdeptno in number,
procdeptname in varchar2,
procdeptloc in varchar2)
as
begin
insert into dept values(procdeptno,procdeptname,procdeptloc);
end;
drop procedure proc_ins_dept
call proc_ins_dept(01,'ffd','fdg')
--有返回值的存储过程
create or replace procedure proc_ins_dept2
(procdeptno in number,
procdeptname in varchar2,
procdeptloc in varchar2,
resu out varchar2)
is
begin
insert into dept values(procdeptno,procdeptname,procdeptloc);
resu:='success';
exception
when others then
resu:='error';
end;
drop procedure proc_ins_dept2
--
call proc_ins_dept2(7,'sa','dfsa');
--
--创建函数
create or replace function getDeptAvg(deno in number)
return number as favg number;
begin
select avg(sal) into favg from emp where(deptno=deno);
return favg;
end;
--调用
drop function getDeptavg;
--
declare
re number;
begin
re:=getDeptAvg(10);
dbms_output.put_line(re);
end;
select * from emp
------------
CREATE OR REPLACE PROCEDURE ShowInfo
(vStudentId IN number) AS
CURSOR s_info IS select StudentName,Address from
Student where StudentId = vStudentId;
BEGIN
for s_student IN s_info LOOP
Dbms_Output.put_line(s_student.StudentName||''||s_student.Address);
end lOOP;
end;
--Grant execute on SHOWINFO to jbit;
begin
ShowInfo(10011);
end;
--
create or replace procedure showinfo
(empid in number,
empno out varchar2,
ename out varchar2,
job out varchar2,
)
as
cursor c is select empno,ename,job from emp where empno=empid;
begin
for e in c loop
dbms_output.put_line(e.empno||' '||e.ename||' '||e.job);
end loop;
end;
--
drop procedure showinfo;
--
select * from emp
call showinfo(7369)
begin
showinfo(7369);
end;
--
create or replace procedure getcur(cur out sys_refcursor,
empid in number)
is
begin
open cur for select empno,ename,job from emp where empno=empid;
end getcur;
java操作:
public class Test {
private static Connection conn;
private static PreparedStatement ps;
private static ResultSet rs;
public static void main(String[] args) throws Exception {
// toPage(1, 10);// 分页
// proce1(02, "bob", "hpe");// 无返回值的存储过程
// proce2(9,"admin","beijing");// 有返回值的存储过程
// function(10);//访问过程
proce3(7369);// 带游标的存储过程
}
private static void proce3(int num) throws Exception {
// TODO Auto-generated method stub
BaseDao bd = new BaseDao();
conn = bd.getConn();
String sql = "call getcur(?,?)";
CallableStatement cs = conn.prepareCall(sql);
cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
cs.setObject(2, num);
cs.execute();
ResultSet rs=(ResultSet) cs.getObject(1);
while(rs.next())
{
System.out.println(rs.getObject(1)+"\t"+rs.getObject(2)+"\t"+rs.getObject(3));
}
rs.close();
cs.close();
conn.close();
}
private static void function(int num) throws Exception {
// TODO Auto-generated method stub
BaseDao bd = new BaseDao();
conn = bd.getConn();
String sql = "select getDeptAvg(?) from dual";
ps = conn.prepareStatement(sql);
ps.setObject(1, num);
rs = ps.executeQuery();
NumberFormat nf = NumberFormat.getInstance();// 创建一个数字格式化对象
nf.setMaximumFractionDigits(2);// 设置小数部分为2
double d = 0;
while (rs.next()) {
d = rs.getDouble(1);
}
System.out.println(nf.format(d));// 对结果进行格式化
bd.release(conn, ps, rs);
}
private static void proce2(int no, String name, String loc)
throws Exception {
// TODO Auto-generated method stub
BaseDao bd = new BaseDao();
conn = bd.getConn();
String sql = "call proc_ins_dept2(?,?,?,?)";
CallableStatement cs = conn.prepareCall(sql);
cs.setObject(1, no);
cs.setObject(2, name);
cs.setObject(3, loc);
cs.registerOutParameter(4, java.sql.Types.VARCHAR);
cs.execute();
String result = (String) cs.getObject(4);
System.out.println(result);
cs.close();
conn.close();
}
public static void proce1(int no, String name, String loc) throws Exception {
// TODO Auto-generated method stub
BaseDao bd = new BaseDao();
conn = bd.getConn();
String sql = "call proc_ins_dept(?,?,?)";
CallableStatement cs = conn.prepareCall(sql);
cs.setObject(1, no);
cs.setObject(2, name);
cs.setObject(3, loc);
cs.execute();
cs.close();
conn.close();
}
public static void toPage(int currentPage, int pageSize) throws Exception {
// TODO Auto-generated method stub
int startPage = (currentPage - 1) * pageSize;// 起始页
int endPage = currentPage * pageSize;// 结束页
BaseDao bd = new BaseDao();
conn = bd.getConn();
String sql = "select * from (select e.empno,e.ename,rownum r from (select empno,ename from emp order by empno) e where rownum>=?)where r<=? ";
ps = conn.prepareStatement(sql);
ps.setObject(1, startPage);
ps.setObject(2, endPage);
rs = ps.executeQuery();
while (rs.next()) {
System.out.println(rs.getObject(1) + "\t" + rs.getObject(2));
}
bd.release(conn, ps, rs);
}
}