PL/SQL 条件分支（二）

--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;

--------------------------------------

测试结果为：

输出i=1
输出i=2
输出i=3
输出i=4
输出i=5
输出i=6
输出i=7
输出i=8
输出i=9
输出i=10
循环结束

---------------------------------------------------------------------------------------------------------------

案例：将7788雇员（SCOTT）的comm改为sal的0.1倍

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;

---------------------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------

分页案例：

--有一张book表，三个字段（书号，书名，出版社），编写存储过程向book表中添加书，用java调用该过程
create table book(
  bookId number(3),
  bookName varchar2(50),
  publishHouse varchar2(50)
);
--编写过程(in表示一个输入参数，可以省略)
create or replace procedure sp_pro22
(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");
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","jsd161102");
//创建CallableStatement
CallableStatement cs = conn.prepareCall("{call sp_pro22(?,?,?)}");
//给？赋值
cs.setInt(1, 10);
cs.setString(2, "笑傲江湖");
cs.setString(3, "人民出版社");
//执行
cs.execute();
*/

==================================================

--有输入和有返回值的存储过程
--输入雇员编号，返回雇员的姓名
create or replace procedure sp_pro33
(spNo in number,spName out varchar2) is
begin
select ename into spName from emp where empno=spNo;
end;

--java代码
/*
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","jsd161102");
//创建CallableStatement
CallableStatement cs = conn.prepareCall("{call sp_pro33(?,?)}");
//给第一个？赋值
cs.setInt(1, 7788);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
//执行
cs.execute();
//取出返回值，要注意？的顺序
String name = cs.getString(2);//第二个？，写2
System.out.println("7788的名字是:"+name);
*/

=================================================================================

扩展案例

--输入雇员编号，返回雇员的姓名,工资，岗位
create or replace procedure sp_pro44
(spNo in number,spName out varchar2,spSal out number,spJob out varchar2) is
begin
select ename,sal,job into spName,spSal,spJob from emp where empno=spNo;
end;
--java代码
/*
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl","scott","jsd161102");
//创建CallableStatement
CallableStatement cs = conn.prepareCall("{call sp_pro44(?,?,?,?)}");
//给第一个？赋值
cs.setInt(1, 7788);
cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
cs.registerOutParameter(3, oracle.jdbc.OracleTypes.DOUBLE);
cs.registerOutParameter(4, oracle.jdbc.OracleTypes.VARCHAR);
//执行
cs.execute();
//取出返回值，要注意？的顺序
String name = cs.getString(2);//第二个？，写2
double sal = cs.getDouble(3);
String job = cs.getString(4);
System.out.println("7788的名字是:"+name+"工资是:"+sal+"工作是:"+job);
*/