Oracle PL/SQL进阶

控制结构

在任何计算机语言(c,java,c#,c++)都有各种控制语句(条件语句，循环语句，顺序控制结构..)在pl/sql中也存在这样的控制结构。

 

条件分支语句

pl/sql中提供了三种条件分支语句

if--then 

if--then--else 

if--then--elsif--elsif--else

 

 

简单的条件判断if--then

基本语法：

if 条件表达式 then

执行语句...;

end if;

--控制结构--条件语句--编写过程，输入员工编号，如果员工工资低于2000，则给员工工资增加10%create or replace procedure p10(v_in_empno in emp2.empno%type) isv_sal emp2.sal%type;begin  select sal into v_sal from emp2 where empno = v_in_empno;  if v_sal < 2000 then    update emp2 set sal = sal * 1.1 where empno = v_in_empno;  end if;end;/

二重条件分支if--then--else

基本语法：

if 条件表达式 then

执行语句;

else

执行语句;

end if;

--编写过程，输入员工编号，如果员工的补助不是0则在原来基础上加上100，如果补助为0则设补助为200create or replace procedure p11(v_in_empno in emp2.empno%type) isv_comm emp2.comm%type;begin  select comm into v_comm from emp2 where empno = v_in_empno;  if v_comm != 0 then    v_comm := v_comm + 100;  else    v_comm := 200;  end if;  update emp2 set comm = v_comm where empno = v_in_empno;end;/

多重条件分支if--then--elsif--else

基本语法：

if 条件表达式 then

执行语句;

elsif 条件表达式 then

执行语句;

else

执行语句;

end if;

--编写过程，输入员工编号，如果职位是PRESIDENT则工资加1000，如果是MANAGER则加500,其他职位加200create or replace procedure p12(v_in_empno in emp2.empno%type) isv_sal emp2.sal%type;v_job emp2.job%type;begin  select sal,job into v_sal,v_job from emp2 where empno = v_in_empno;  if v_job = 'PRESIDENT' then    v_sal := v_sal + 1000;  elsif v_job = 'MANAGER' then    v_sal := v_sal + 500;                                                                                                              v_sal := v_sal + 200;  end if;  update emp2 set sal = v_sal where empno = v_in_empno;end;/

循环结构

loop循环

是pl/sql中最简单的循环语句，这种循环语句以loop开头，以end loop结尾，这种循环至少会被执行一次。

基本语法：

loop

执行语句;

exit when 条件表达式;

end loop;

--循环语句--编写过程，输入用户名和添加用户的个数n，循环添加n个用户到users表中，从1到ncreate or replace procedure p13(v_in_username in users.username%type,v_in_number in number) isv_userid number := 1;begin  loop    exit when v_userid > v_in_number or v_in_number <= 0;    insert into users values(v_userid,v_in_username);    v_userid := v_userid + 1;  end loop;end;/

while循环

loop基本循环至少要执行循环体一次，而对于while循环来说，只有条件为true时，才会执行循环体语句，while循环以while..loop开始，以end loop结束。

基本语法：

while 条件表达式 loop

执行语句;

end loop;

--编写过程，输入用户名和添加用户的个数n，循环添加n个用户到users表中，从1到ncreate or replace procedure p14(v_in_username in users.username%type,v_in_number in number) isv_userid number := 1;begin  while v_userid <= v_in_number and v_in_number > 0 loop    insert into users values(v_userid,v_in_username);    v_userid := v_userid + 1;  end loop;end;/

 

说明：

1、在is--begin之间只能定义变量类型同时初始化赋值，或定义变量类型后在begin内进行赋值，不能在is--begin之间定义变量类型之后再对变量赋值。

2、传入的参数变量不能在存储过程中再次赋值。

 

for循环

     基本for循环的基本结构如下：

begin

for i in [reverse] 起始值..终止值 loop

     执行语句;

end loop;

end;

--编写过程，输入用户名和添加用户的个数n，循环添加n个用户到users表中，从1到ncreate or replace procedure p15(v_in_username in users.username%type,v_in_number in number) isbegin  for i in 1..v_in_number loop    insert into users values(i,v_in_username);  end loop;end;/

注意：推荐使用loop循环或者是while循环，不推荐使用for循环。

goto,null

1、goto语句

     goto语句用于跳转到特定标号去执行语句，注意由于使用goto语句会增加程序的复杂性，并使得应用程序可读性变差，所以一般不使用goto语句。

     基本语法如下：goto lable，其中lable是已定义好的标号名。

 

基本语法：goto 标号;

标号定义：<<标号>>

例：

--输出1至12 循环结束。declarei number:=1;begin<<start_loop>>loopdbms_output.put_line('输出i='||i);if i=12 thengoto end_loop;end if;i:=i+1;if i=10 thengoto start_loop;end if;end loop;<<end_loop>>dbms_output.put_line('循环结束');end;

2、null

null语句不会执行任何操作，并且会直接将控制传递到下一条语句。使用null语句的主要好处是可以提高pl/sql的可读性。

例：

declarev_sal emp.sal%type;v_ename emp.ename%type;beginselect ename,sal into v_ename,v_sal from emp where empno=&no;if v_sal<3000 thenupdate emp set comm=sal*0.1 where ename=v_ename;elsenull;end if;end;

存储过程实际运用——编写分页过程

PS:分页过程主要运用的是过程中的返回值实现的，而返回值概念比较抽象，故一步一步了解返回值，从无返回值->有返回值的非列表形式->有返回值的列表形式->有多个返回值的列表非列表组合形式(实现分页功能)。

无返回值的存储过程

实例：

--编写过程，以输入的员工号，显示员工的姓名、工资、个人所得税(税率为0.03)create or replace procedure p5(v_in_empno in number) isv_ename varchar2(30);v_sal number;v_tax_rate number(3,2) := 0.03;v_tax number;begin  select ename,sal into v_ename,v_sal from emp2 where empno = v_in_empno;  v_tax := v_sal * v_tax_rate;  dbms_output.put_line('员工的姓名为：' || v_ename || ' 员工的工资为：' || v_sal || ' 员工的个人所得税为： ' || v_tax);end;/

有返回值的存储过程(非列表，只有一个返回值)

建立有返回值的存储过程基本语法：

create or replace procedure 过程名(参数名 in 类型,..,参数名 out 类型,..) is

定义变量..;

begin

执行语句..;

exception

when 错误提示 then

处理或提示语句;

end;

实例：

--编写过程，输入员工编号，返回员工姓名--out表示输出类型create or replace procedure p16(v_in_empno in emp2.empno%type,v_out_ename out emp2.ename%type) isbegin  select ename into v_out_ename from emp where empno = v_in_empno;end;/

/** * 调用返回非列表的值 */package com.oracle.db;import java.sql.*;public class db8 {public static void main(String[] args) {Connection ct = null;CallableStatement cs = null;ResultSet rs = null;try {// 加载驱动Class.forName("oracle.jdbc.driver.OracleDriver");// 得到连接ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:Switch", "scott","123456");// 创建CallableStatementcs = ct.prepareCall("{call p16(?,?)}");// 给?赋值cs.setString(1, "7839");cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);// 执行cs.execute();// 取出输出值String ename = cs.getString(2);System.out.println("名字是：" + ename);} catch (Exception e) {// TODO: handle exceptione.printStackTrace();}}}

说明：

1、对于过程的输入值，使用set方法，对于输出值使用registerOutParameter来注册接收返回值。问号的顺序要对应，同时考虑类型。

2、取出过程返回值的方法是CallableStatement提供的get方法(输出参数的位置)；同时要考虑输出的参数类型。

有返回值的存储过程(列表[结果集])

PS：列表，结果集是用游标来实现的，但是游标类型是不能直接使用的，必须要定义。这时候就能将游标定义在包里面，然后调用包中的游标来实现列表，结果集。

 

实例：

--编写过程，输入部门号，返回该部门所有员工信息--1.声明包，包中定义一个游标类型create or replace package pack2 is--定义一个游标类型type my_cursor is ref cursor;end;/--2.编写过程create or replace procedure p17(v_in_deptno in emp2.deptno%type,v_out_result out pack2.my_cursor) isbegin  open v_out_result for select * from emp where deptno = v_in_deptno;end;/

/** * 3.调用列表值(集合) */package com.oracle.db;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;public class db9 {public static void main(String[] args) {Connection ct = null;CallableStatement cs = null;ResultSet rs = null;try {// 加载驱动Class.forName("oracle.jdbc.driver.OracleDriver");// 得到连接ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:Switch", "scott","123456");// 创建CallableStatementcs = ct.prepareCall("{call p17(?,?)}");// 给?赋值cs.setString(1, "10");// 注册第二个?cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);// 执行cs.execute();// 取出集合rs = (ResultSet) cs.getObject(2);// 循环取值while (rs.next()) {String ename = rs.getString("ename");int empno = rs.getInt("empno");System.out.println("员工号是：" + empno + " 员工名是：" + ename);}} catch (Exception e) {// TODO: handle exceptione.printStackTrace();} finally {try {// 关闭资源if (rs != null) {rs.close();}if (cs != null) {cs.close();}if (ct != null) {ct.close();}} catch (Exception e2) {e2.printStackTrace();}}}}

--编写过程，输入员工编号，返回该员工的姓名、工资和岗位--1.声明包，定义一个游标类型，可以参考上一个案例，这里不在定义--2.编写过程create or replace procedure p18(v_in_empno in emp2.empno%type,v_out_result out pack2.my_cursor) isbegin  open v_out_result for select ename,sal,job from emp2 where empno = v_in_empno;end;/

/** * 3.从存储过程中接收游标(集合) */package com.oracle.db;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;public class d10 {public static void main(String[] args) {Connection ct = null;CallableStatement cs = null;ResultSet rs = null;try {// 1.加载驱动Class.forName("oracle.jdbc.driver.OracleDriver");// 2.获取连接ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:Switch", "scott","123456");// 3.创建CallableStatementcs = ct.prepareCall("{call p18(?,?)}");// 4.给?赋值cs.setString(1, "7839");// 5.注册返回类型参数cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);// 6.执行cs.execute();// 7.从游标中获取集合rs = (ResultSet) cs.getObject(2);if (rs.next()) {String ename = rs.getString("ename");int sal = rs.getInt("sal");String job = rs.getString("job");System.out.println("员工姓名是：" + ename + " 员工的薪水是：" + sal+ " 员工的岗位是：" + job);}} catch (Exception e) {e.printStackTrace();// 抛出运行异常throw new RuntimeException(e.getMessage());} finally {// 关闭资源try {if (rs != null) {rs.close();}if (cs != null) {cs.close();}if (ct != null) {ct.close();}} catch (Exception e2) {// TODO: handle exceptione2.printStackTrace();}}}}

有多个返回值的列表非列表组合形式(实现分页功能)

把一个字符串，当做sql语句执行，并把查询得到的结果赋给某个变量，语法如下：

execute immediate v_sql into myrows;

基本语法：

execute immediate 变量(sql拼接语句) into 输出变量名;

--分页过程--编写过程，输入表名、每页显示记录数、当前页、排序字段(deptno降序)。返回总记录数，总页数和返回的结果集--1.声明一个包含游标类型的包create or replace package pack3 is       type my_cursor is ref cursor;end;/--2.编写过程create or replace procedure p19(v_in_table in varchar2,v_in_pagesize in number,v_in_currpage in number,v_in_order in varchar2,v_out_totalrecords out number,v_out_totalpage out number,v_out_results out pack3.my_cursor) is--定义sql拼接块v_sql varchar(2000);--页开始v_pagebegin number;--页结束v_pageend number;begin  --计算页开始数  v_pagebegin := v_in_pagesize * (v_in_currpage - 1) + 1;  --计算页结束数  v_pageend := v_in_pagesize * v_in_currpage;  --查询分页结果并放入游标  --sql语句需要拼接  v_sql := 'select t2.* from (select t1.*,rownum rn from (select * from ' || v_in_table ||' order by '  || v_in_order || ') t1 where rownum <= ' || v_pageend || ') t2 where rn >= ' || v_pagebegin;  --打开游标，指向结果集  open v_out_results for v_sql;  --查询总记录数  --能力有限，并没有实现按输入的表名来计算总页数，想法是通过拼接之后再执行SQL语句获取结果。  select count(*) into v_out_totalrecords from emp2;  --计算总页数  if mod(v_out_totalrecords,v_in_pagesize) = 0 then    v_out_totalpage := v_out_totalrecords / v_in_pagesize;  else    v_out_totalpage := (v_out_totalrecords / v_in_pagesize) + 1;  end if;end;/

/** * 调用分页存储过程 */package com.oracle.db;import java.sql.*;public class db11 {public static void main(String[] args) {Connection ct = null;CallableStatement cs = null;ResultSet rs = null;try {// 1.加载驱动Class.forName("oracle.jdbc.driver.OracleDriver");// 2.获取连接ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:Switch", "scott","123456");// 3.创建CallableStatement// p19过程参数// v_in_table varchar2// v_in_pagesize number// v_in_currpage number// v_in_order varchar2// v_out_totalrecords number// v_out_totalpage number// v_out_results pack3.my_cursorcs = ct.prepareCall("{call p19(?,?,?,?,?,?,?)}");// 4.f给?赋值// 设置表名cs.setString(1, "emp2");// 设置每页显示记录数cs.setInt(2, 6);// 设置当前页cs.setInt(3, 3);// 设置排序列cs.setString(4, "empno");// 注册返回类型参数// 注册总记录数cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);// 注册总页数cs.registerOutParameter(6, oracle.jdbc.OracleTypes.INTEGER);// 注册返回结果集cs.registerOutParameter(7, oracle.jdbc.OracleTypes.CURSOR);// 5.执行cs.execute();// 6.从返回参数获取相应值int totalRecords = cs.getInt(5);int totalPage = cs.getInt(6);rs = (ResultSet) cs.getObject(7);// 循环输出while (rs.next()) {String ename = rs.getString("ename");int empno = rs.getInt("empno");System.out.println("员工的编号是：" + empno + " 员工的姓名是：" + ename);}System.out.println("总记录数为：" + totalRecords);System.out.println("总页数为：" + totalPage);} catch (Exception e) {// TODO: handle exceptione.printStackTrace();// 抛出运行异常throw new RuntimeException(e.getMessage());} finally {// 关闭资源try {if (rs != null) {rs.close();}if (cs != null) {cs.close();}if (ct != null) {ct.close();}} catch (Exception e2) {// TODO: handle exceptione2.printStackTrace();}}}}

DividePage.jsp

<%@ page language="java" import="java.util.*,java.sql.*" pageEncoding="UTF-8"%><%String path = request.getContextPath();String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";%><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html>  <head>    <base href="<%=basePath%>">    <title>My JSP 'DividePage.jsp' starting page</title>  </head>  <body>    <h2>oracle分页案例</h2><br><table><tr><td>用户名</td><td>薪水</td></tr>    <%    //接收pageCurrString sPageCurr = request.getParameter("pageCurr");int pageCurr = 1;if(sPageCurr != null){pageCurr = Integer.parseInt(sPageCurr);}    Connection ct = null;CallableStatement cs = null;ResultSet rs = null;try {// 1.加载驱动Class.forName("oracle.jdbc.driver.OracleDriver");// 2.获取连接ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:Switch", "scott","123456");// 3.创建CallableStatement// p19过程参数// v_in_table varchar2// v_in_pagesize number// v_in_currpage number// v_in_order varchar2// v_out_totalrecords number// v_out_totalpage number// v_out_results pack3.my_cursorcs = ct.prepareCall("{call p19(?,?,?,?,?,?,?)}");// 4.f给?赋值// 设置表名cs.setString(1, "emp2");// 设置每页显示记录数cs.setInt(2, 4);// 设置当前页cs.setInt(3, pageCurr);// 设置排序列cs.setString(4, "empno");// 注册返回类型参数// 注册总记录数cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);// 注册总页数cs.registerOutParameter(6, oracle.jdbc.OracleTypes.INTEGER);// 注册返回结果集cs.registerOutParameter(7, oracle.jdbc.OracleTypes.CURSOR);// 5.执行cs.execute();// 6.从返回参数获取相应值int totalRecords = cs.getInt(5);int totalPage = cs.getInt(6);rs = (ResultSet) cs.getObject(7);// 循环输出while (rs.next()) {out.println("<tr>");out.println("<td>" + rs.getString("ename") + "</td>");out.println("<td>" + rs.getString("sal") + "</td>");out.println("</tr>");}out.println("</table>");out.println("总记录数为：" + totalRecords);out.println("总页数为：" + totalPage);out.println("当前页为：" + pageCurr);//打印总页数for(int i = 1; i <= totalPage; i++){out.print("<a href=DividePage.jsp?pageCurr="+i+ "> [" + i +  "] </a>");}} catch (Exception e) {// TODO: handle exceptione.printStackTrace();// 抛出运行异常throw new RuntimeException(e.getMessage());} finally {// 关闭资源try {if (rs != null) {rs.close();}if (cs != null) {cs.close();}if (ct != null) {ct.close();}} catch (Exception e2) {// TODO: handle exceptione2.printStackTrace();}}     %>  </body></html>

----------参考《韩顺平玩转Oracle》