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_salexec 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_comm0 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)); } //关闭相关资源，此处略写
