oracle循环控制语句

--(1)if---then
--案例：编写一个过程，可以输入一个雇员名，如果该雇员的工资低于2000，就给该雇员工资增加10%
create or replace procedure pro1(v_in_ename varchar2) is
--定义雇员变量
v_sal emp.sal%type;
begin
  select sal into v_sal from emp where ename=v_in_ename;
  if v_sal<2000 then
    update emp set sal=sal*1.1 where ename=v_in_ename;
  end if;
end;

--(2)if---then---else
--【案例】编写一个过程，可以输入雇员名，如果该雇员的补助不是0就在原来的基础上增加100,；
--如果补助为0，就把补助设置为200
create or replace procedure pro1(v_in_ename varchar2)
is
v_comm emp.comm%type;
begin
  select comm into v_comm from emp where ename=v_in_ename;
  if v_comm<>0 then 
    update emp set comm=comm+100 where ename=v_in_ename;
  else 
    update emp set comm=200 where ename=v_in_ename;      
  end if;
end;

--(3)if---then---elsif(...elsif)---else
--【案例】编写一个过程，可以输入一个雇员的编号，如果该雇员的职位是president就给他的工资增加1000，
--如果该雇员的职位是manager，就给他的工资增加500，其他职位的雇员工资增加200
create or replace procedure pro1(v_in_empno number)
is
v_job emp.job%type;
v_sal emp.sal%type;
begin
select job,sal into v_job,v_sal from emp where empno=v_in_empno;
if v_job='PRESIDENT' then 
  update emp set sal=sal+1000 where empno=v_in_empno;
elsif v_job='MANAGER' then
  update emp set sal=sal+500 where empno=v_in_empno;
else 
  update emp set sal=sal+200 where empno=v_in_empno;
  end if;
end;

--【案例】编写一个过程，可以输入用户名，和添加用户的个数n，循环添加
--n个用户到users3表中，用户编号从1开始增加，直到n
---定义一个users3表
create table users3
(
id number primary key,
name varchar2(32)
);
--创建存储过程
create or replace procedure pro1(v_in_ename varchar2,n number)
is
v_id users3.id%type:=1;
begin
loop
  insert into users3 values (v_id,v_in_ename);
  exit when  v_id=n;
  v_id:=v_id+1;
end loop;
end;

--********************循环语句while
create or replace procedure pro1(v_in_ename varchar2,v_count number)
is
--定义变量
v_no number:=20;
begin
while v_no<=20+v_count loop
insert into users3 values (v_no,v_in_ename);
v_no:=v_no+1;
end loop;
end;

--********************常见的错误1：
create or replace procedure sp_pro5(spName varchar2)
is
v_test varchar2(32);
v_test:='aaa';
--不能在is..begin之间赋值，但是赋初值可以，改成v_test varchar2(32):=是正确的
begin
dbms_output.put_line(v_test);
end;
--【修改后】
create or replace procedure sp_pro5(spName varchar2)
is
v_test varchar2(32):='aaa';
--不能在is..begin之间赋值，但是赋初值可以，改成v_test varchar2(32):=是正确的
begin
dbms_output.put_line(v_test);
end;

--********************常见错误2
create or replace procedure sp_pro5(spName varchar2)
is
v_test varchar2(32):='aaa';
begin
  spName:='你好';--错误，不能给输入参数赋值
  dbms_output.put_line(v_test||'传入参数'||spName);
end;
--********************修改后
create or replace procedure sp_pro5(spName varchar2)
is
v_test varchar2(32):='aaa';
begin
  dbms_output.put_line(v_test||'传入参数'||spName);
end;
-------------goto语句
declare
i number:=1;
begin
<<start_loop>>  --<<start_loop>>表示start_loop是一个标号，下同
	loop
	   dbms_output.put_line('输出i='||i);
	   if i=12 then 
	   goto end_loop;
	   end if;
	   i:=i+1;
	   if i=10 then
	   goto start_loop;
	   end if;
	end loop;
<<end_loop>>
dbms_output.put_line('循环结束');
end;

--********************for循环
--语法：
begin
	for i in reverse 1..10 loop
	insert into users3 values(i,'百度');
	end loop;
end;
/

---null语句不会执行任何操作，并且会直接将控制传递到下一条语句。
--使用null语句的好处是可以提高pl/slq的可读性。
declare
v_sal emp.sal%type;
v_ename emp.ename%type;
begin
	select ename,sal into v_ename,v_sal
	from emp where empno=&no;
	if v_sal<3000 then
	update emp set comm=sal*0.1 where ename=v_ename;
	else
	null;
	end if;
end;

--------带有输出参数的存储过程
create or replace procedure pro1(v_in_empno in number,v_out_ename out varchar2)
--注意：v_out_ename out varchar2表示v_out_ename是输出变量，
--out是关键字 varchar2是输出变量的类型
is
begin
select ename into v_out_ename from emp where empno=v_in_empno;
end;

--编写一个存储过程，输入部门号，返回该部门所有雇员信息。
--①建立一个package
create or replace package mypackage is
type my_cursor is ref cursor;
end;
--②建立存储过程
create or replace procedure pro1(v_in_deptno number,v_out_result out mypackage.my_cursor) is
begin
open v_out_result for select * from emp where deptno=v_in_deptno;
--注意：这里不要关闭游标，让调用程序去关闭游标
end;

--(3)oracle中使用存储过程分页：
--【案例】编写一个存储过程，要求可以输入表名，每页显示记录数，当前页，排序字段
--(deptno降序).返回总记录数，总页数，和返回的结果集。
--oracle的分页查询：
select t2.* from (select t1.*,rownum  rn from (select * from emp) t1 where rownum<=9) t2
where rn>=4;
--那么使用上面的查询为模板，进行修改：
create or replace procedure fenyePro
(v_in_table in varchar2,v_in_pagesize in number,
v_in_pagenow in number,v_out_result out mypackage.my_cursor)
is
--定义变量
v_sql varchar2(2000);
v_start number;
v_end number;
begin
--计算v_start 和v_end是多少
v_start:=(v_in_pagenow-1)*v_in_pagesize+1;
v_end:=v_in_pagenow*v_in_pagesize;
v_sql:='select t2.* from (select t1.*,rownum  rn from (select * from ' ||v_in_table||') t1 where rownum<='||v_end||') t2 where rn>='||v_start;
--打开游标
open v_out_result for v_sql;
end;

java调用上面的分页存储过程：

//oracle 使用存储过程进行分页
		Connection conn = null;
		ResultSet rs = null;
		CallableStatement cs = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "tiger");
			cs = conn.prepareCall("{call fenyePro(?,?,?,?)}");
			cs.setString(1, "emp"); //第一个参数表示表名
			cs.setInt(2, 5); //第二个参数表示pagesize
			cs.setInt(3, 2); //第三个参数表示pagenow
			cs.registerOutParameter(4, oracle.jdbc.OracleTypes.CURSOR); //第四个参数是返回的结果集
			cs.execute();
			rs = (ResultSet) cs.getObject(4);
			while(rs.next()){
				//直接取列名
				//System.out.println(rs.getString("EMPNO")+" "+rs.getString("ENAME"));
				//取列的编号
				System.out.println(rs.getString(1)+" "+rs.getString(2));
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			SQLHelper.close(conn, cs, rs);
		}