oracle存储过程学习

有了之前的PL-sql 的学习之后，再来学习存储过程建感觉好理解。存储过程是可以通过java或是C# 编程语言在程序中调用的。

1、使用存储过程输出Hello World

create or replace procedure sayHelloWorld
as
  --说明部分
begin
   dbms_output.put_line('Hello World');

end;
/
begin
     sayHelloWorld();
     
   end;

2、带参数的存储过程

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？看情况分类讨论 有的可以通过spring 事务管理  有的需要在存储过程中commit 

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

end;
/

3、out 参数在存储过程中使用

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

4、有时候 out 参数会很多 类似于我们java 方法里面参数很多话  我们会去封装到一个对象里面。存储过程也有类似这样的对象 它就是通过游标和包头 包体实现参数封装

包头
CREATE OR REPLACE PACKAGE MYPAKCAGE AS 

  type empcursor is ref cursor;
  procedure queryEmpList(dno in number, empList out empcursor);

END MYPAKCAGE;


包体
CREATE OR REPLACE PACKAGE BODY MYPAKCAGE AS

  procedure queryEmpList(dno in number, empList out empcursor) AS
  BEGIN

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

  END queryEmpList;

END MYPAKCAGE;

5、java  实现调用oracle 存储过程

public class TestOracle {
/*
 * create or replace procedure queryempinfo(eno in number,
                                            pename out varchar2,
                                            psal   out number,
                                            pjob   out varchar2)
 */
	@Test
	public void testProcedure(){
		//{call <procedure-name>[(<arg1>,<arg2>, ...)]}
		String sql = "{call queryempinfo(?,?,?,?)}";
			
		Connection conn = null;
		CallableStatement call = null;
		try {
			conn = JDBCUtils.getConnection();
			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+"\t"+sal+"\t"+job);
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.release(conn, call, null);
		}
	}

/*
 * create or replace function queryempincome(eno in number)
return number
 */
	@Test
	public void testFunction(){
		//{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
		String sql = "{?=call queryempincome(?)}";
		
		Connection conn = null;
		CallableStatement call = null;
		try {
			conn = JDBCUtils.getConnection();
			call = conn.prepareCall(sql);
			
			//第一个是out参数
			call.registerOutParameter(1, OracleTypes.NUMBER);
			//第二个是in参数
			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);
		}		
	}


	@Test
	public void testCursor(){
		String sql = "{call MYPAKCAGE.queryEmpList(?,?)}";
		
		Connection conn = null;
		CallableStatement call = null;
		ResultSet rs = null;
		try {
			conn = JDBCUtils.getConnection();
			call = conn.prepareCall(sql);
			
			//对于in参数，赋值
			call.setInt(1, 20);
			//对于out参数  申明
			call.registerOutParameter(2, OracleTypes.CURSOR);
			
			call.execute();
			
			//取出结果
			rs = ((OracleCallableStatement)call).getCursor(2);
			while(rs.next()){
				//取出一个员工
				String name = rs.getString("ename");
				double sal = rs.getDouble("sal");
				System.out.println(name+"\t"+sal);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			JDBCUtils.release(conn, call, rs);
		}
	}
}