存储过程与存储关系

存储过程与存储关系

定义：指存储在数据库中供所有用户程序调用的子程序叫存储过程、存储函数（相当于java中的方法）。在某种意义上来说存储过程与存储函数没什么区别，唯一的区别为存储过程没有返回值，而存储函数有返回值，但是在之后存储过程中可以传入out输出类型的参数之后，这两就机会没有区别了

存储过程

语法：create [or replace] PROCEDURE 过程名(参数列表) 

AS

     PLSQL子程序体；

调用存储过程

1、exec 存储过程名字();

2、begin

    存储过程名字();

   end;

   /

 

例：给指定的员工涨100工资，并且打印涨前和涨后的薪

create or replace procedure raiseSalary(eno in[刘志跃1]  number)

as

   psal emp.sal%type;

begin

 select sal into psal from emp where empno=eno;

  update empset sal=sal+100 where empno=eno;[刘志跃2] 

  [刘志跃3] 

  打印涨前和涨后的薪水

 dbms_output.put_line('涨前:'||psal||'  涨后:'||(psal+100));

end;

/

 

存储函数

定义：存储函数（Function）为一命名的存储程序，可带参数，并返回一计算值。函数和过程的结构类似，但必须有一个RETURN子句，用于返回函数值。函数说明要指定函数名、结果值的类型，以及参数类型等。

语法：CREATE [OR REPLACE] FUNCTION函数名(参数列表)

 RETURN  函数值类型

AS

PLSQL子程序体；

 

例：查询某个员工的年收入

create or replace function queryEmpIncome(enoin number)

return number[刘志跃4] 

as

  --月薪和奖金

  psalemp.sal%type;

  pcommemp.comm%type;

begin

  --得到该员工的月薪和奖金

 select sal,comm into psal,pcomm from emp where empno=eno;

  returnpsal*12+nvl(pcomm,0);[刘志跃5] 

end;

/

 

out类型的参数

什么时候用存储过程，什么时候用存储函数

建议如果只有一个返回值，用存储函数；否则如果没有返回值或者有多个返回值，就用存储过程，存储过程也可以返回，这时就用到out类型了。

例：查询并返回某个员工的姓名 月薪 职位

create or replace procedure queryEmpInfo(enoin number,

                                         penameout varchar2[刘志跃6] ,

                                         psal   out number,

                                        pjob   out [刘志跃7] varchar2)

as

begin

  --查询并返回某个员工的姓名 月薪 职位

 select ename,sal,empjob into pename,psal,pjob  from emp where empno=eno;

end;

/

 

JDBC调用存储过程和存储函数

public class TestOracle {

	/*
	 * JDBC调用存储过程
	 create or replace procedure queryEmpInfo(eno in number,
                                         pename out varchar2,
                                         psal   out number,
                                         pjob   out varchar2)
	 */
	@Test
	public void testProcedure (){
		String sql = "{call queryEmpInfo(?,?,?,?)} ";
		Connection conn = null;
		CallableStatement  call = null;
		try {
			conn = JDBCUtils.getConnect();
			call = conn.prepareCall(sql);
			//赋值
			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);
		}
	}
	/*
	 * JDBC调用存储函数
	 * create or replace function queryEmpIncome(eno in number)
       return number
	 */
	@Test
	public void testFunction (){
		String sql = "{?=call queryEmpIncome(?)} ";
		Connection conn = null;
		CallableStatement call = null;
		try {
			conn = JDBCUtils.getConnect();
			call = conn.prepareCall(sql);
			//out参数
			call.registerOutParameter(1, OracleTypes.NUMBER);
			//赋值
			call.setInt(2, 7839);
			call.execute();
			//取出结果
			System.out.println(call.getDouble(1));
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtils.release(conn, call, null);
		}
	}

在out参数中使用游标(用来解决out参数太多)

步骤：1、申明程序包结构（包头只负责声明存储过程）

      2、创建包体（包体只负责实现存储过程）

例：查询某个部门中所有员工的所有信息

注：在SQL Developer中的程序包中新建程序包，就出现包头了

包头（声明存储过程）

CREATE OR REPLACE PACKAGEMYPACKAGE AS

  type empcursor  is ref cursor;

  procedure queryEmpList (dno in number,empList out empcursor);

END MYPACKAGE;

包体（在包上点击右键就会出现新建包体，并且会将所有的存储过程与函数写到包体中）

CREATE OR REPLACE PACKAGE BODYMYPACKAGE AS

  procedure queryEmpList(dno in number,empList outempcursor ) AS

  BEGIN

    open empList for  select * from emp where deptno=dno;

  END queryEmpList;

END MYPACKAGE;

 

用java程序调用有光标的存储过程
	@Test
	public void testCursor(){
		String sql = "{call MYPACKAGE.queryEmpList(?,?)}";
		Connection conn = null;
		CallableStatement call = null;
		ResultSet  rs =null;
		try {
			conn = JDBCUtils.getConnect();
			call = conn.prepareCall(sql);
			//赋值
			call.setInt(1, 20);
			//out参数
			call.registerOutParameter(2, OracleTypes.CURSOR );
			//执行
			call.execute();
			//取出集合
			rs = ((OracleCallableStatement  )call).getCursor(2);
			while(rs.next()){
				System.out.println(rs.getString("ename"));
				System.out.println(rs.getString("empjob"));
				System.out.println(rs.getDouble("sal"));
				System.out.println("===================");
			}
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.release(conn, call, rs );
		}
	}
}