Oracle存储过程入门学习

1，Oracle存储过程和自定义函数概述

指存储在数据库中供所有用户程序调用的子程序叫存储过程或存储函数。
存储过程 和 存储函数 的相同点和不同点：
1）相同点：都是完成特定功能的程序；
2）不同点：存储过程不能用return语句返回值，存储函数能用return语句返回值。

2，创建和使用存储过程

1）语法：

creat [or replace] PROCEDURE 过程名（参数列表）
AS
PLSQL子程序体；

2）不带参数的存储过程例子
在sql developer中，新建一个sql文件，输入如下代码：

create or REPLACE PROCEDURE sayhelloworld
AS
begin
  dbms_output.put_line('hello world'); // 打印
end;

执行上面的代码，可以在Procedures(过程)目录下，看到生成的存储过程sayhelloworld。
然后我们执行一下这个存储过程，看它能不能打印出hello world。这里介绍两种执行方式：
<1>使用命令行工具，cmd打开控制台，登录Oracle，然后输入如下命令：

<2>在sql developer中，右键这个存储过程–》运行–》确定，就可以执行这个存储过程了，最后结果如下：

3）带参数的存储过程例子
例子如下：

/*
创建一个带参数的存储过程：
给指定的员工涨100块工资，并且打印涨前和涨后的薪水
调用：
begin
  raisesalary(7839);
  raisesalary(7566);
  commit;
end;
/
*/
create or REPLACE PROCEDURE raisesalary(eno in NUMBER)
as
--定义一个变量保存涨前的薪水
psal emp.sal% TYPE;
BEGIN
  --得到员工涨前的薪水
  SELECT sal INTO psal FROM emp where empno = eno;
  --给员工涨100块
  UPDATE emp set sal= sal+100 where empno = eno;
  
  --需不需要commit?
  --注意：为了保证事务的一致性，一般不在存储过程或存储函数中commit或rollback。
  
  --打印
  dbms_output.put_line('涨前：'|| psal||'    涨后：'||(psal + 100));
END;
/

执行方式如上一个例子，不过因为这是带参数的存储过程，所以我们执行的时候，需要修改参数，如下：

改完之后，点击确认按钮，结果如下:

3，如何调试存储过程

在sql developer中，双击存储过程raisesalary，会打开两个raisesalary的页面，选择第二个，可以进行调试。

如果这个时候报“权限不足”的提示，我们则登录oracle，进行如下操作：

授完权之后，按上面的步骤再次进行调试，可以看到如下界面：

4，存储函数

函数（function）为一命名的存储程序，可带参数，并返回一计算值。
函数和过程的结构类似，但必须有一个return子句，用于返回函数值。
一个小例子：

create or replace function queryempincome(eno in number)
return number
as
--定义变量保存员工的薪水和奖金
	psal emp.sal%type;
	pcomm emp.comm%type;
begin
	select sal,comm into psal,pcomm from emp where empno=eno;
	return psal*12+nvl(pcomm,0); // 若表达式中有null，那么整个表达式就会返回null，所以需要用nvl函数做置0处理。
end;
/

然后运行调试：函数–》右键–》运行，在打开的页面中，修改参数，点击确认，即可得到结果：

运行结果如下：

5，out参数

一般来讲，存储过程和存储函数的区别在于存储函数可以有一个返回值；而存储过程没有返回值。

但是存储过程和存储函数都可以通过out指定一个或多个输出参数。我们可以利用out参数，在存储过程和存储函数中实现返回多个值。

存储过程和存储函数都可以有out参数；
存储过程和存储函数都可以有多个out参数；
存储过程可以通过out参数来实现返回值。

什么时候用存储过程/存储函数？
原则：
–如果只有一个返回值，用存储函数；否则，就用存储过程。

一个小例子：

create or replace procedure queryempinfo(eno in number,
                                         pename out varchar2,
                                         psal out number,
                                         pjob out varchar2)
as
begin
  select ename,sal,job into pename,psal,pjob from emp where empno = eno;
end;
/

然后运行，步骤如上。

6，如何在应用程序中调用存储过程

（1）新建一个Java Project工程，命名为TestOracle，并且导入连接oracle数据库的依赖包；
（2）新建一个数据库连接工具类；

package demo.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class JDBCUtils {

	private static String driver="oracle.jdbc.OracleDriver";
	private static String url="jdbc:oracle:thin:@localhost:1521:orcl";
	private static String user = "scott";
	private static String password = "tiger";
	
	// 注册数据库驱动
	static {
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			throw new ExceptionInInitializerError(e);
		}
	}
	
	// 获取数据库连接
	public static Connection getConnenction() {
		try {
			return DriverManager.getConnection(url, user, password);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	
	//释放数据库资源
	public static void release(Connection conn,Statement st,ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				rs = null;
			}
		}
		if (st != null) {
			try {
				st.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				rs = null;
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				conn = null;
			}
		}
	}
}

（3）新建一个测试类

package demo.oracle;

import java.sql.CallableStatement;
import java.sql.Connection;

import org.junit.Test;

import demo.utils.JDBCUtils;
import oracle.jdbc.internal.OracleTypes;

public class TestProcedure {

	@Test
	public void testProcedure() {
		String sql = "{call queryempinfo(?,?,?,?)}";
		Connection conn = null;
		CallableStatement call = null;
		try {
			// 得到一个连接
			conn = JDBCUtils.getConnenction();
			// 通过连接创建出statement
			call = conn.prepareCall(sql);
			
			// 对于输入（in）参数，赋值
			call.setInt(1, 7839);
			// 对于输出（out）参数，声明
			call.registerOutParameter(2, OracleTypes.VARCHAR);
			call.registerOutParameter(3, OracleTypes.NUMBER);
			call.registerOutParameter(4, OracleTypes.VARCHAR);

			// 执行调用
			call.execute();
			
			// 取出结果
			String name = call.getString(2);
			double sal = call.getDouble(3);
			String job = call.getString(4);
			System.out.println("姓名："+name + "  薪水："+ sal + "  工作：" + job);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.release(conn, call, null);
		}
	}
}

（4）运行结果

姓名：KING  薪水：5500.0  工作：PRESIDENT

7，如何在应用程序中调用存储函数

（1）新建一个测试类

package demo.oracle;

import java.sql.CallableStatement;
import java.sql.Connection;

import org.junit.Test;

import demo.utils.JDBCUtils;
import oracle.jdbc.internal.OracleTypes;

public class TestFunction {

	@Test
	public void testFunction() {
		String sql = "{?=call queryempincome(?)}";
		Connection conn = null;
		CallableStatement call = null;
		try {
			// 得到数据库连接
			conn = JDBCUtils.getConnenction();
			
			// 基于连接得到statement
			call = conn.prepareCall(sql);
			// 对于输出参数，声明
			call.registerOutParameter(1, OracleTypes.NUMBER);
			// 对于输入参数，赋值
			call.setInt(2, 7839);
			// 执行调用
			call.execute();
			double income = call.getDouble(1);
			System.out.println("该员工的年收入=" + income);
					
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.release(conn, call, null);
		}
	}
}

结果如下：

该员工的年收入=66000.0

8，在out参数中使用光标

从上面的存储过程可以看出，我们的输出参数每一个都是明确定义的，有几个输出参数，就定义几个变量，这种对于输出参数个数较少的时候是适用的，但是如果我们有100个参数，200个参数，难道也一个一个去定义吗？显然这是不科学的。因此这里引入光标CURSOR的概念。

光标（CURSOR，也称游标）：是SQL的一个内存工作区，由系统或用户以变量的形式定义。光标的作用就是用于临时存储从数据库中提取的数据块。

为了使用光标，我们先申明包结构。
包的结构分为包头和包体，包头只负责声明，包体只负责实现，包头和包体也同样是数据库的对象，地位和表，视图，储存过程，存储函数一样。

案例：查询某个部门中所有员工的所有信息。
（1）新建一个包，分包头和包体。
在sql developer中，在数据库连接下面找到“程序包”目录，右键该目录–》新建程序包–》输入程序包名称–》确认，此时会自动创建一些结构代码，我们在这个基础上，加入自己的代码，如下：

create or replace PACKAGE MYPACKAGE AS

  type empcursor is REF CURSOR; // 定义了一个光标类型
  PROCEDURE queryEmpList(dno in NUMBER,empList out empcursor);

END MYPACKAGE;

上面只是包头，只负责声明一些东西，我们还需要再新建一个包体，负责实现。
在“程序包”目录下，已经有了我们刚刚新建的MYPACKAGE的程序包，我们选中这个MYPACKAGE程序包，右键–》创建主体，会生成主体的基本代码，我们加上我们需要的代码，如下：

create or replace PACKAGE BODY MYPACKAGE AS

  PROCEDURE queryEmpList(dno in NUMBER,empList out empcursor) AS
  BEGIN
    --打开光标，该查询语句的结果就赋给了emplist这个光标了
    open emplist for SELECT * from emp WHERE deptno = dno;
  END queryEmpList;

END MYPACKAGE;

（2）新建一个测试类：

package demo.oracle;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;

import org.junit.Test;

import demo.utils.JDBCUtils;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.internal.OracleTypes;

public class TestCursor {

	@Test
	public void testCursor() {
		String sql = "{call MYPACKAGE.queryEmpList(?,?)}";
		Connection conn = null;
		CallableStatement call = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtils.getConnenction();
			call = conn.prepareCall(sql);
			// 对于in参数，赋值
			call.setInt(1, 20); // 表示10号部门
			
			// 对于out参数，声明
			call.registerOutParameter(2, OracleTypes.CURSOR);
			
			call.execute();
			
			//取数
			rs = ((OracleCallableStatement)call).getCursor(2);
			while(rs.next()) {
				// 取出员工号，姓名，薪水和职位
				int empno = rs.getInt("empno");
				String name = rs.getString("ename");
				double salary = rs.getDouble("sal");
				String job = rs.getString("job");
				System.out.println("员工号：" + empno + "  姓名：" + name + "   薪水：" + salary + "   职位：" + job);
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.release(conn, call, rs);
		}
	}
}

（3）结果

员工号：7369  姓名：SMITH   薪水：800.0   职位：CLERK
员工号：7566  姓名：JONES   薪水：3275.0   职位：MANAGER
员工号：7788  姓名：SCOTT   薪水：3000.0   职位：ANALYST
员工号：7876  姓名：ADAMS   薪水：1100.0   职位：CLERK
员工号：7902  姓名：FORD   薪水：3000.0   职位：ANALYST

笔记就记到这里了。