devbox
从前慢现在也慢
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

Oracle PL/SQL进阶_oracle sql进阶语法

作者：从前慢现在也慢 | 2024-07-31 08:34:22

赞

踩

oracle sql进阶语法

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;


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

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

基本语法：

if 条件表达式 then

执行语句;

else

执行语句;

end if;

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

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

基本语法：

if 条件表达式 then

执行语句;

elsif 条件表达式 then

执行语句;

else

执行语句;

end if;


  1. --编写过程，输入员工编号，如果职位是PRESIDENT则工资加1000，如果是MANAGER则加500,其他职位加200
  2. create or replace procedure p12(v_in_empno in emp2.empno%type) is
  3. v_sal emp2.sal%type;
  4. v_job emp2.job%type;
  5. begin
  6.   select sal,job into v_sal,v_job from emp2 where empno = v_in_empno;
  7.   if v_job = 'PRESIDENT' then
  8.     v_sal := v_sal + 1000;
  9.   elsif v_job = 'MANAGER' then
  10.     v_sal := v_sal + 500;
  11.                                                                                                           
  12.     v_sal := v_sal + 200;
  13.   end if;
  14.   update emp2 set sal = v_sal where empno = v_in_empno;
  15. end;
  16. /


循环结构

loop循环

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

基本语法：

loop

执行语句;

exit when 条件表达式;

end loop;

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

while循环

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

基本语法：

while 条件表达式 loop

执行语句;

end loop;

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

 

说明：

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

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

 

for循环

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

begin

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

     执行语句;

end loop;

end;


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

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


goto,null

1、goto语句

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

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

 

基本语法：goto 标号;

标号定义：<<标号>>


例：

  1. --输出1至12 循环结束。
  2. declare
  3. i number:=1;
  4. begin
  5. <<start_loop>>
  6. 	loop
  7. 		dbms_output.put_line('输出i='||i);
  8. 		if i=12 then
  9. 			goto end_loop;
  10. 		end if;
  11. 		i:=i+1;
  12. 		if i=10 then
  13. 			goto start_loop;
  14. 		end if;
  15. 	end loop;
  16. <<end_loop>>
  17. 	dbms_output.put_line('循环结束');
  18. end;

2、null

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


例：

  1. declare
  2. v_sal emp.sal%type;
  3. v_ename emp.ename%type;
  4. begin
  5. 	select ename,sal into v_ename,v_sal from emp where empno=&no;
  6. 	if v_sal<3000 then
  7. 		update emp set comm=sal*0.1 where ename=v_ename;
  8. 	else
  9. 		null;
  10. 	end if;
  11. end;


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

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


无返回值的存储过程

实例：

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

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

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

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

定义变量..;

begin

执行语句..;

exception

when 错误提示 then

处理或提示语句;

end;


实例：

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

  1. /**
  2.  * 调用返回非列表的值
  3.  */
  4. package com.oracle.db;
  5. import java.sql.*;
  6. public class db8 {
  7. 	public static void main(String[] args) {
  8. 		Connection ct = null;
  9. 		CallableStatement cs = null;
  10. 		ResultSet rs = null;
  11.  
  12. 		try {
  13. 			// 加载驱动
  14. 			Class.forName("oracle.jdbc.driver.OracleDriver");
  15. 			// 得到连接
  16. 			ct = DriverManager.getConnection(
  17. 					"jdbc:oracle:thin:@127.0.0.1:1521:Switch", "scott",
  18. 					"123456");
  19. 			// 创建CallableStatement
  20. 			cs = ct.prepareCall("{call p16(?,?)}");
  21. 			// 给?赋值
  22. 			cs.setString(1, "7839");
  23. 			cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
  24.  
  25. 			// 执行
  26. 			cs.execute();
  27.  
  28. 			// 取出输出值
  29. 			String ename = cs.getString(2);
  30. 			System.out.println("名字是：" + ename);
  31. 		} catch (Exception e) {
  32. 			// TODO: handle exception
  33. 			e.printStackTrace();
  34. 		}
  35. 	}
  36. }

说明：

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

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


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

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

 

实例：

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


  1. /**
  2.  * 3.调用列表值(集合)
  3.  */
  4. package com.oracle.db;
  5. import java.sql.CallableStatement;
  6. import java.sql.Connection;
  7. import java.sql.DriverManager;
  8. import java.sql.ResultSet;
  9. public class db9 {
  10. 	public static void main(String[] args) {
  11. 		Connection ct = null;
  12. 		CallableStatement cs = null;
  13. 		ResultSet rs = null;
  14. 		try {
  15. 			// 加载驱动
  16. 			Class.forName("oracle.jdbc.driver.OracleDriver");
  17. 			// 得到连接
  18. 			ct = DriverManager.getConnection(
  19. 					"jdbc:oracle:thin:@127.0.0.1:1521:Switch", "scott",
  20. 					"123456");
  21. 			// 创建CallableStatement
  22. 			cs = ct.prepareCall("{call p17(?,?)}");
  23. 			// 给?赋值
  24. 			cs.setString(1, "10");
  25. 			// 注册第二个?
  26. 			cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
  27. 			// 执行
  28. 			cs.execute();
  29.  
  30. 			// 取出集合
  31. 			rs = (ResultSet) cs.getObject(2);
  32. 			// 循环取值
  33. 			while (rs.next()) {
  34. 				String ename = rs.getString("ename");
  35. 				int empno = rs.getInt("empno");
  36. 				System.out.println("员工号是：" + empno + " 员工名是：" + ename);
  37. 			}
  38. 		} catch (Exception e) {
  39. 			// TODO: handle exception
  40. 			e.printStackTrace();
  41. 		} finally {
  42. 			try {
  43. 				// 关闭资源
  44. 				if (rs != null) {
  45. 					rs.close();
  46. 				}
  47. 				if (cs != null) {
  48. 					cs.close();
  49. 				}
  50. 				if (ct != null) {
  51. 					ct.close();
  52. 				}
  53. 			} catch (Exception e2) {
  54. 				e2.printStackTrace();
  55. 			}
  56. 		}
  57. 	}
  58. }





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


  1. /**
  2.  * 3.从存储过程中接收游标(集合)
  3.  */
  4. package com.oracle.db;
  5. import java.sql.CallableStatement;
  6. import java.sql.Connection;
  7. import java.sql.DriverManager;
  8. import java.sql.ResultSet;
  9. public class d10 {
  10.  
  11. 	public static void main(String[] args) {
  12. 		Connection ct = null;
  13. 		CallableStatement cs = null;
  14. 		ResultSet rs = null;
  15. 		try {
  16. 			// 1.加载驱动
  17. 			Class.forName("oracle.jdbc.driver.OracleDriver");
  18. 			// 2.获取连接
  19. 			ct = DriverManager.getConnection(
  20. 					"jdbc:oracle:thin:@127.0.0.1:1521:Switch", "scott",
  21. 					"123456");
  22. 			// 3.创建CallableStatement
  23. 			cs = ct.prepareCall("{call p18(?,?)}");
  24. 			// 4.给?赋值
  25. 			cs.setString(1, "7839");
  26. 			// 5.注册返回类型参数
  27. 			cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
  28. 			// 6.执行
  29. 			cs.execute();
  30. 			// 7.从游标中获取集合
  31. 			rs = (ResultSet) cs.getObject(2);
  32. 			if (rs.next()) {
  33. 				String ename = rs.getString("ename");
  34. 				int sal = rs.getInt("sal");
  35. 				String job = rs.getString("job");
  36. 				System.out.println("员工姓名是：" + ename + " 员工的薪水是：" + sal
  37. 						+ " 员工的岗位是：" + job);
  38. 			}
  39. 		} catch (Exception e) {
  40. 			e.printStackTrace();
  41. 			// 抛出运行异常
  42. 			throw new RuntimeException(e.getMessage());
  43. 		} finally {
  44. 			// 关闭资源
  45. 			try {
  46. 				if (rs != null) {
  47. 					rs.close();
  48. 				}
  49. 				if (cs != null) {
  50. 					cs.close();
  51. 				}
  52. 				if (ct != null) {
  53. 					ct.close();
  54. 				}
  55. 			} catch (Exception e2) {
  56. 				// TODO: handle exception
  57. 				e2.printStackTrace();
  58. 			}
  59. 		}
  60. 	}
  61. }


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

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

execute immediate v_sql into myrows;

基本语法：

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

  1. --分页过程
  2. --编写过程，输入表名、每页显示记录数、当前页、排序字段(deptno降序)。返回总记录数，总页数和返回的结果集
  3. --1.声明一个包含游标类型的包
  4. create or replace package pack3 is
  5.        type my_cursor is ref cursor;
  6. end;
  7. /
  8.  
  9. --2.编写过程
  10. create or replace procedure p19
  11. (v_in_table in varchar2,v_in_pagesize in number,v_in_currpage in number,v_in_order in varchar2
  12. ,v_out_totalrecords out number,v_out_totalpage out number,v_out_results out pack3.my_cursor) is
  13. --定义sql拼接块
  14. v_sql varchar(2000);
  15. --页开始
  16. v_pagebegin number;
  17. --页结束
  18. v_pageend number;
  19.  
  20. begin
  21.   --计算页开始数
  22.   v_pagebegin := v_in_pagesize * (v_in_currpage - 1) + 1;
  23.   --计算页结束数
  24.   v_pageend := v_in_pagesize * v_in_currpage;
  25.   --查询分页结果并放入游标
  26.   --sql语句需要拼接
  27.   v_sql := 'select t2.* from (select t1.*,rownum rn from (select * from ' || v_in_table ||' order by '
  28.   || v_in_order || ') t1 where rownum <= ' || v_pageend || ') t2 where rn >= ' || v_pagebegin;
  29.   --打开游标，指向结果集
  30.   open v_out_results for v_sql;
  31.   --查询总记录数
  32.   --能力有限，并没有实现按输入的表名来计算总页数，想法是通过拼接之后再执行SQL语句获取结果。
  33.   select count(*) into v_out_totalrecords from emp2;
  34.   --计算总页数
  35.   if mod(v_out_totalrecords,v_in_pagesize) = 0 then
  36.     v_out_totalpage := v_out_totalrecords / v_in_pagesize;
  37.   else
  38.     v_out_totalpage := (v_out_totalrecords / v_in_pagesize) + 1;
  39.   end if;
  40. end;
  41. /

  1. /**
  2.  * 调用分页存储过程
  3.  */
  4. package com.oracle.db;
  5. import java.sql.*;
  6. public class db11 {
  7. 	public static void main(String[] args) {
  8. 		Connection ct = null;
  9. 		CallableStatement cs = null;
  10. 		ResultSet rs = null;
  11. 		try {
  12. 			// 1.加载驱动
  13. 			Class.forName("oracle.jdbc.driver.OracleDriver");
  14. 			// 2.获取连接
  15. 			ct = DriverManager.getConnection(
  16. 					"jdbc:oracle:thin:@127.0.0.1:1521:Switch", "scott",
  17. 					"123456");
  18. 			// 3.创建CallableStatement
  19. 			// p19过程参数
  20. 			// v_in_table varchar2
  21. 			// v_in_pagesize number
  22. 			// v_in_currpage number
  23. 			// v_in_order varchar2
  24. 			// v_out_totalrecords number
  25. 			// v_out_totalpage number
  26. 			// v_out_results pack3.my_cursor
  27. 			cs = ct.prepareCall("{call p19(?,?,?,?,?,?,?)}");
  28. 			// 4.f给?赋值
  29. 			// 设置表名
  30. 			cs.setString(1, "emp2");
  31. 			// 设置每页显示记录数
  32. 			cs.setInt(2, 6);
  33. 			// 设置当前页
  34. 			cs.setInt(3, 3);
  35. 			// 设置排序列
  36. 			cs.setString(4, "empno");
  37. 			// 注册返回类型参数
  38. 			// 注册总记录数
  39. 			cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
  40. 			// 注册总页数
  41. 			cs.registerOutParameter(6, oracle.jdbc.OracleTypes.INTEGER);
  42. 			// 注册返回结果集
  43. 			cs.registerOutParameter(7, oracle.jdbc.OracleTypes.CURSOR);
  44. 			// 5.执行
  45. 			cs.execute();
  46. 			// 6.从返回参数获取相应值
  47. 			int totalRecords = cs.getInt(5);
  48. 			int totalPage = cs.getInt(6);
  49. 			rs = (ResultSet) cs.getObject(7);
  50. 			// 循环输出
  51. 			while (rs.next()) {
  52. 				String ename = rs.getString("ename");
  53. 				int empno = rs.getInt("empno");
  54. 				System.out.println("员工的编号是：" + empno + " 员工的姓名是：" + ename);
  55. 			}
  56. 			System.out.println("总记录数为：" + totalRecords);
  57. 			System.out.println("总页数为：" + totalPage);
  58. 		} catch (Exception e) {
  59. 			// TODO: handle exception
  60. 			e.printStackTrace();
  61. 			// 抛出运行异常
  62. 			throw new RuntimeException(e.getMessage());
  63. 		} finally {
  64. 			// 关闭资源
  65. 			try {
  66. 				if (rs != null) {
  67. 					rs.close();
  68. 				}
  69. 				if (cs != null) {
  70. 					cs.close();
  71. 				}
  72. 				if (ct != null) {
  73. 					ct.close();
  74. 				}
  75. 			} catch (Exception e2) {
  76. 				// TODO: handle exception
  77. 				e2.printStackTrace();
  78. 			}
  79. 		}
  80. 	}
  81. }


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>
    <%
    	//接收pageCurr
		String 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_cursor
			cs = 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 exception
			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 exception
				e2.printStackTrace();
			}
		}
     %>
  </body>
</html>



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


声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/从前慢现在也慢/article/detail/907923
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号