PL/SQL(二)

PLSQL  条件控制语句

if-then ,if -then -elseif-else,if-then-else

输入一个员工，如果其工资小于2000 就让其工资加100

create or replace procedure my_pro(name varchar2) is

v_sal emp.sal%type;

begin 

select sal into v_sal from emp where ename  = name;

if (v_sal<2000) 

then

update emp set sal = sal + 100 where ename = name;

end if;

end;

 

exec mypro('SCOTT');

 

如果补助不为0就加100，如果为0就设为200补助

if v_comm <>0 then   --  +100 操作

else  -- 设为200

end if;

 

多种判断

if b1 then   xxx

elsif b2 then  xxx

else  xxx

end if;

 

循环结构

for while ,do..while

loop  

end loop;

 

while b loop 

  xxx

end loop

end;

for 很少用,不太灵活，每次只能增1

for i in reverse 1..10 loop

xxxx;

end loop;

end;

 

goto  不建议使用

如果有多种循环，如果某种原因需要从最里面的循环跳到外面其中的某种循环的话需要用goto.goto 可读性很差

goto mylabel  

用法 <<mylabel>>

<<mylabel>> 放到哪里就跳到哪里

 

null 

if b then else null; end if;

 

分页过程

 

过程的参数  过程名(参数名1 in 类型，....)  注意in为可选参数

 

有返回值的过程 (有输入有输出)

create or replace procedure mypro (noid in varchar2,name out varchar2) is

begin 

select ename into name from emp where empno = noid;

end;

 

java 如何得到输出值呢？

CallableStatement cs = conn.prepareCall("{call mypro(?,?)}");

cs.setInt(1,7873); //员工阿訇为7873

cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);

// 此处必须一一对应，都必须注册

cs.execute();

System.out.print("员工名字为"+ cs.getString(2));

 

多个返回值就写多个out 

但是参数很多，想得到结果集如何办？

 

用package

创建一个包,定义一个mytest_cursor游标类型

create or replace package testpackage as 

type mytest_cursor is ref cursor

end testpackage;

 

2 创建过程

create or replace procedure my_pro(deptno in number,out_cursor out  testpackage.mytest_cursor) is 

begin 

open out_cursor for 

select * from emp where deptno = deptno;

end;

 

3 java调用返回结果集

CallableStatement cs = conn.prepareCall("{call mypro(?,?)}");

cs.setInt(1,10); //10 号部门

cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);

cs.execute();

// 得到结果集

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

while(rs.next){

System.out.println(rs.get(1) + "...........");

//

}

 

分页：传入表名，每页显示的记录数，当前页。

然后返回总条数，总页数，当前页的结果集

分页的语句

select * from (select t1.*,rowrnum from(select * from emp t1) where rownum >= 10) where rn >= 6;

 

 

create or replace procedure page

(tablename in varchar2,pagesize in number,currentpage in number

allsize out number,allpage out number,

out_cursor out  testpackage.mytest_cursor

) is

-- 定义一个sql

v_sql varchar2(300);

v_startnum number:=(current-1)*pagesize+1;

v_endnum number:=v_startnum + pagesize;

v_sql:='select * from (select t1.*,rowrnum from(select * from '||tablename||' t1) where rownum >= '||endnum||') where rn >= '||startnum||';';

beign 

open out_cursor for v_sql;

 -- 计算总行数总页数

v_sql:='select count(*) from '||tablename;

 -- 执行并返回记录数

execute immediate into allsize;

 -- 计算总页数

if mod(allsize,pagesize)=0

  then 

all page  := allsize / pagesize- 1;

end if;

close out_cursor ;

end;

 

用java测试

 

CallableStatement cs = conn.prepareCall("{call page(?,?,?,?,?,?)}");

cs.setString(1,"emp");

cs.setInt(2,5);

cs.setInt(3,1);

cs.registerOutParameter(4,oracle.jdbc.OracleTypes.INTEGER);

cs.registerOutParameter(5,oracle.jdbc.OracleTypes.INTEGER);

cs.registerOutParameter(6,oracle.jdbc.OracleTypes.CURSOR);

cs.execute();

// 得到其他信息

// 得到结果集

ResultSet rs = (ResultSet)cs.getObject(6);

while(rs.next){

System.out.println(rs.get(1) + "...........");

//

}

 

如果想按照薪水倒叙呢只需要在过程拼接的sql 添加 order by sal 

疑问？ 动态的为表分页，但是不同的表可能按照不同的字段排序，同一个表不同的地方也可能按照不同的字段排序。如何解决？