8）控制结构(其中goto语句了解）
a)条件语句 if---then/if--then--else/if--then--eslif--then
例子1）：编写一个过程，可以输入雇员名，如果该雇员工资低于2000，就给该雇员增加工资10%
---------------------------------------------------------------------------
SQL>create or replace procedure p(pName varchar2) is
v_sal emp.sal%type;--定义变量%type指的是让sal的类型和v_sal相同
begin
select sal into v_sal from emp where ename = pName;
if v_sal<2000 then
update emp set sal = sal + sal*10% where ename = pName;
end if;
end;
/
SQL>exec p('arthur');
---------------------------------------------------------------------------
例子2）：编写一个过程，可以输入一个雇员名，入股雇员的补助comm不是零，就在原来的基础上加100，否则设置为200
---------------------------------------------------------------------------
SQL>create or replace procedure p1(pName varchar2) is
v_comm emp.comm%type;--定义变量%type指的是让sal的类型和v_sal相同
begin
select comm into v_comm from emp where ename = pName;
if v_comm<>0 then
update emp set comm = comm + 100 where ename = pName;
else
update emp set comm = 200 where ename = pName;
end if;--始终放在最后
end;
/
SQL>exec p('arthur');
---------------------------------------------------------------------------
例子3）：编写一个过程，可以输入一个雇员编号，如果该雇员的职位是PRESIDENT，就给他的工资增加1000，如果职位是
MANAGER，工资就增加500，其它职位工资增加200
---------------------------------------------------------------------------
SQL>create or replace procedure p2(pNo number) is
v_job emp.job%type;
begin
select job into v_job from emp where empno = pNo;
if v_job = 'PRERENT' then
update emp set sal = sal + 1000 where empno = pNo;
elsif v_job = 'MANAGER' then
update emp set sal = sal + 500 where empno = pNo;
else
update emp set sal = sal + 200 where empno = pNo;
end if;--一定要加上
end;
/
---------------------------------------------------------------------------
b)循环语句
i)loop...end loop 这种循环至少被循环一次
例子1）： 编写一个过程，可输入用户名，并添加10个用户到users表中，用户标号id从1开始增加
---------------------------------------------------------------------------
SQL>create or replace procedure p3(pName varchar2) is
v_num number := 1;--控制循环的变量，初始值为1
begin
loop
insert into users values(v_num,pName);
--判断是否循环十次
exit when v_num = 10;
v_num := v_num + 1;--自增1
end loop
end;
/
SQL>exec p3('arthur');
---------------------------------------------------------------------------
ii):while 循环，以whlie....loop开始，以end loop结束
例子1）：编写一个过程，可输入用户名，并添加10个用户到users表中，用户标号id从11开始增加
---------------------------------------------------------------------------
SQL>create or replace procedure p4(pName varchar2) is
v_num number := 11;--控制循环的变量，初始值为1
begin
while v_num < 21 loop
insert into users values(v_num,pName);
v_num := v_num + 1;--自增1
end loop
end;
/
SQL>exec p3('arthur');
---------------------------------------------------------------------------
9）null,null语句不会执行任何操作，并且会直接将控制传递到下一条语句，主要好处是增加了PL/sql的可读性
10）编写分页过程
例子1）：编写一个过程，可以向book表添加书，要求通过java程序调用
---------------------------------------------------------------------
--in代表bookID为输入变量，其实in是默认的，
--out:表示一个输出参数
--这是一个无返回值的过程
SQL>create or replace procedure p5(bookID in number，bookName in varchar2,publicHouse in varchar2) is
begin
insert into book values(bookID,bookName,publicHouse);
end;
/
---------------------------------------------------------------------
通过java代码调用该过程
---------------------------------------------------------------------
//加载驱动
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:MYORA1","userName","passWord")
CallableStatement cs = ct.prepareCall("{call p5(?,?,?)}");
//给？赋值
cs.setInt(1,10);
cs.setString(2,"星辰变");
cs.setString(3,"新华出版社");
//执行
cs.execute();
//关闭资源操作，此处省略
例子2）：有返回的存储过程
编写一个过程，可以输入员工的编号，返回该员工的姓名，工资和岗位
---------------------------------------------------------------------
-- 有输入和输出的存储过程
SQL>create procedure p6(bianHao in number,name out varchar2，pSal out number,pJob out varchar2) is
begin
select ename,sal,job into name,pSal,pJob from emp where empno = bianHao;
end;
/
---------------------------------------------------------------------
在java里面获得上述过程中的姓名
---------------------------------------------------------------------
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:
CallableStatement cs = ct.prepareCall("{call p6(?,?,?,?)}");
//给第一个？赋值
cs.setInt(1,8888);
//给第二个？赋值
cs.registOutPrameter(2,oracle.jdbc.OracleTypes.VARCHAR);
cs.registOutPrameter(3,oracle.jdbc.OracleTypes.DOUBLE);
cs.registOutPrameter(4,oracle.jdbc.OracleTypes.VARCHAR);
//执行
cs.execute();
//取出返回值,以为返回值是第二个？，所以是2
String name = cs.getString(2);
String job = cs.getString(4);
//关闭相关资源，此处略写
---------------------------------------------------------------------
例子3）：有返回值的存储过程（列表[结果集])
编写一个过程，可以输入部门的编号，返回该部门所有的员工信息
--由于oracle存储过程没有返回值，它的所有返回值都是通过out
--参数来代替，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用package
---------------------------------------------------------------------
--第一部分，建一个包，定义了一个游标类型test_cursor
SQL>create or replace package testPackage as type test_cursor(游标名) is ref cursor(游标)
end testPackage
--第二部分，创建过程
SQL>create or replace procedure p7(bianHao in number,p_cursor out testPackage.test_cursor) is
begin
--打开一个游标
open p_cursor for select * from emp where deptno = bianHao;
end;
/
---------------------------------------------------------------------
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:
CallableStatement cs = ct.prepareCall("{call p7(?,?)}");
//给第一个？赋值
cs.setInt(1,8888);
//给第二个？赋值
cs.registOutPrameter(2,oracle.jdbc.OracleTypes.CURSOR);
//执行
cs.execute();
//得到结果集
ResultSet rs = (ResultSet) cs.getObject(2);
while(rs.next()){
System.out.println(rs.getInt(1) + "--" + rs.getString(2));
}
//关闭相关资源，此处略写