1. 条件判断
将工资低于2000的员工工资增加10%
create  or  replace  procedure sp_proc6(spName varchar2)  is
v_sal emp.sal%type;
begin
select sal  into v_sal  from emp  where ename=spName;
if v_sal < 2000  then
update emp  set sal=sal+sal*10%  where ename=spname'
end  if;
end;
/
执行：exec sp_pro6('scott');

将雇员补助不是0的为其增加100， 是0的为之增加200
create  or  replace  procedure sp_proc7(spName varchar2)  is
v_comm emp.comm%type;
begin
select comm  into v_comm  from emp  where ename=spName;
if v_comm <>0  then
update emp  set comm=comm+100  where ename=spName;
else
update emp  set comm=comm+200  where ename=spName;
end  if;
end;
/
执行：exec sp_proc7

为president的工资增加1000， manager增加500， 其他的增加200
create  or  replace  procedure sp_pro8(spNo number)  is
v_job emp.job%type;
begin
select job  into v_job  from emp  where empno=spNo;
if v_job= 'president'  then
update emp  set sal=sal+1000  where empno=spNo;
elsif job= 'manager'  then
update emp  set sal=sal+500  where empno=spNo;
else
update emp  set sal=sal+200  where empno=spNo;
end  if;
end;
/
执行：exec sp_pro8(7839)

2. 循环结构
需求：增加10个用户到user表中，每次输入用户名则可
loop循环， 先循环再执行
create  or  replace  procedure sp_pro9(spName varchar2)  is
--‘:=’表示赋值
v_num number:=1;
begin
loop
     --需要检查users表是否存在，字段是否匹配好， 若表不存在，可能存在编译错误
     insert  into users  values(v_num, spName);
     exit  when v_num=10;
     --v_num自增
    v_num:=v_num+1;
end loop;
end;
/
执行：exec sp_pro9('张三');

while循环
create  or  replace  procedure sp_pro10(spName varchar2)  is
--‘:=’表示赋值
v_num number:=11;
begin
while v_num <=20 loop
         --需要检查users表是否存在，字段是否匹配好， 若表不存在，可能存在编译错误
         insert  into users  values(v_num, spName);
         --v_num自增
        v_num:=v_num+1;
end loop;
end;
/
执行：exec sp_pro10('李四');

create  or  replace  procedure sp_p1(spname varchar2)  is
v_id number:=1;
begin
loop
insert  into t1  values(v_id, spname);
v_id:=v_id+1; 
exit  when v_id=100;
end loop;
end;
/

exec sp_p1( 'zhangsan')
其中表t1包含2个字段:id, name

for循环
由于语法上生涩，其每次的步长只能为1，且循环的次数需先确定，灵活性不是特别的大
create  or  replace  procedure sp_pro11(spName varchar2)  is
begin
     for i  in  reverse 21..30 loop
     insert  into users  values(i,spName)
     end loop;
end;
/
执行：exec sp_pro10('王五');

3. goto语句， null
goto语句用于跳转到特定的标号去执行。
declare
i  int :=1;
begin
    loop
        dbms_output.put_line( '输出值i='||i);
         if i=10  then 
              goto end_loop;
         end  if;
        i:=i+1;
     end loop;
    《end_loop》
    dbms_output.put_line( '循环结束！');
end;
/

null：什么都不做，只是为了增加可读性

4. Java调用存储有输入的存储过程
创建book表
create  table book(bookId number, bookName varchar2(50), publishHouse varchar2(50));

编写存储过程：
-- in:表示输入
--out:表述输出
create  or  replace  procedure sp_pro12(spBookId  in number, spBookName  in varchar2, spPublishHouse  in varchar2)  is
begin
insert  into book  values(spBookId, spBookName, spPublishHouse);
end;
/

java调用存储过程
Class.forName( "oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManage.getConnection( "jdbc:oracle:thin@127.0.0.1:1521:wilson", "scott", "tiger");
//通过连接，取得存储过程
CallableStatement cs = conn.prepareCall( "{call sp_pro12(?,?,?)}");
//对里面的内容赋值
cs.setString(1，1011)；
cs.setInt(2,'Java编程思想');
cs.serString(3,'机械工业出版社');
//执行存储过程
cs.execute();
//释放资源
cs.close();
conn.close();

有输出的存储过程：
create  or  replace  procedure sp_pro13(spno  in number, spName out varchar2)
begin
select ename  into spName  from emp  where empno=spno;
end;
/

调用该存储过程：
Class.forName( "oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManage.getConnection( "jdbc:oracle:thin@127.0.0.1:1521:wilson", "scott", "tiger");
//通过连接，取得存储过程
CallableStatement cs = conn.prepareCall( "{call sp_pro13(?,?)}");
//对里面的内容赋值
cs.setString(1，1011)；
//注册返回类型
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
//执行存储过程
cs.execute();
String name = cs.getString(2); //取出返回值，在存储过程中第2个变量是返回值
System.out.println(name);
//释放资源
cs.close();
conn.close();
多个返回值的以此类同, 当Java去调用存储过程时，需要取出其中一部分的内容时， 但是需要把所有变量都关联， 否则出现没有关联的错误。取出来是可以不用都接收存储过程的返回值的。

***在项目中，更常用的是需要返回结果集的情况，比如传入一个部门，返回这个部门下的所有员工。由于一般的变量都是返回一个个的值，不能完成需求，这时就需要用到package了。
--返回结果集的过程
--1. 创建一个包，该包中，定义类型test_cursor,是一个游标
create  or  replace package testpackage  as 
type test_cursor  is ref  cursor;
end testpackage;
/

--2.建立存储过程
create  or  replace  procedure sp_pro14(spNo  in number, p_cursor out testpackage.test_cursor)  is
begin
open p_cursor  for  select *  from emp  where deptno=spNo;
end;
/

用Java调用该存储过程返回的结果集：
Class.forName( "oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManage.getConnection( "jdbc:oracle:thin@127.0.0.1:1521:wilson", "scott", "tiger");
//通过连接，取得存储过程
CallableStatement cs = conn.prepareCall( "{call sp_pro14(?,?)}");
//对里面的内容赋值
cs.setString(1，10)；
//注册游标类型的返回类型
cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
//执行存储过程
cs.execute();
ResultSet rs = (ResultSet)cs.getObject(2); //取出返回值，在存储过程中第2个变量是返回值
while(rs.next()){
    System.out.println(rs.getInt(1)+ ", " + rs.getString(2));
}
//释放资源
cs.close();
conn.close();
注意点：1、注册的类型为 CURSOR 2、Java取值用getObject, 用ResultSet接收。

5. Oracle的分页
--取出rownum
select t1.*, rownum rn  from ( select *  from emp) t1;

--取出前10条记录
select t1.*, rownum rn  from ( select *  from emp) t1  where rownum < 10;

--取出第6条到第10条记录, 在分页时可以作为模板使用
select *  from (
select t1.*, rownum rn  from ( select *  from emp) t1  where rownum < 10
)  where rn >=6;

--开发游标相关的包
create  or     replace package testpackage  as type test_coursor  is ref  cursor;
end testpackage;

--开始编写分页的存储过程
create   or   replace   procedure  fenye(tableName  in  varchar2, 
pageSize  in  number, 
pageNow  in  number, 
myrows out number, 
myPageCount out number, 
p_cursor out testpackage.test_coursor )  is
--定义部分
--定义sql语句 字符串
v_sql varchar2(1000);
 --定义两个整数, 表示页的起始number号和最后的number号
v_begin number:=(pageNow-1)* pageSize + 1;
v_end number:= pageNow*pageSize ;
 begin
--执行部分, 按照薪水由低到高排序，再进行分页
v_sql:=' select  *  from  (
 select  t1.*, rownum rn  from  ( select  *  from   '|| tableName ||' order by sal ) t1  where  rownum <  '|| v_end ||' )  where  rn >= '|| v_begin    ||' ;';
 --把游标和sql关联
open  p_cursor  for  v_sql;
 --计算myrows和myPageCount
--组织一个sql语句
v_sql:= 'select count(*) from ' || tableName;
 --执行sql， 并把返回的值， 赋给myrows
execute  immediate v_sql  into  myrows;
 --计算myPageCount
if  mod(myrows,PageSize)=0  then
    myPageCount:=myrows/Pagesize;
 else
    myPageCount:=myrows/Pagesize + 1;
endif;
 --关闭游标
close  p_cursor;
 end ;
/

Java调用分页存储过程：
Class.forName( "oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManage.getConnection( "jdbc:oracle:thin@127.0.0.1:1521:wilson", "scott", "tiger");
//通过连接，取得存储过程
CallableStatement cs = conn.prepareCall( "{call fenye(?,?,?,?,?,?)}");
//对里面的内容赋值
cs.setString(1， "emp");
cs.setInt(2,5);
cs.set(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();
//getInt中的4是由该参数的位置决定的
int rowNum=cs.getInt(4);
int pageCount=cs.getInt(5);
System.out.println( "总行数："+ rowNum);
System.out.println( "总页数："+ pageCount);

ResultSet rs = (ResultSet)cs.getObject(6); //取出返回值，在该存储过程中第6个变量是返回值
while(rs.next()){
         //取出的是整个emp表中的第1列和第2列
        System.out.println(rs.getInt(1)+ ", " + rs.getString(2));
}
//释放资源
cs.close();
conn.close();