oracle的学存储过程和函数的调用

调用存储过程或者函数

package com.itheima.test;

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

import org.junit.Test;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;

public class TestProcedure {
	
	/*
	 * create or replace procedure proc_getyearsal(eno in number,vyearsal out number)
		is  
		begin
		  -- 查询员工年薪
		  select sal*12 + nvl(comm,0) into vyearsal from emp where empno = eno; 
		end;
	 * */
	@Test
	public void test1() throws Exception{
//		1.注册驱动
		Class.forName("oracle.jdbc.driver.OracleDriver");
//        2.创建连接
		String url ="jdbc:oracle:thin:@192.168.80.100:1521:orcl";
		Connection conn = DriverManager.getConnection(url, "***", "root");
//        3.获取执行SQL的对象
		String sql ="{call pro_updatesal(?,?)}";
		CallableStatement call = conn.prepareCall(sql);
//		  封装参数
		call.setInt(1, 7369);
//		注册输出类型的参数
		call.registerOutParameter(2, OracleTypes.NUMBER);
//        4.执行SQL
		call.execute();  // 如果执行的是查询拆操作就返回true , 增删改的操作就是false
//        5.处理结果
		int sum = call.getInt(2);
		System.out.println("年薪:"+sum);
//        6.释放资源
		call.close();
		conn.close();
	}
	
	@Test
	/*
	 * create or replace procedure proc_getemp(dno in number,vrows out sys_refcursor)
		is
		       
		begin
		  open vrows for select * from emp where deptno = dno;
		end;
	 * */
	public void test2() throws Exception{
//		1.注册驱动
		Class.forName("oracle.jdbc.driver.OracleDriver");
//        2.创建连接
		String url ="jdbc:oracle:thin:@192.168.42.100:1521:orcl";
		Connection conn = DriverManager.getConnection(url, "***", "root");
//        3.获取执行SQL的对象
		String sql ="{call proc_getemp(?,?)}";
		CallableStatement call = conn.prepareCall(sql);
//		4.封装输入类型的参数
		call.setInt(1, 10);
//		5.注册输出类型的参数
		call.registerOutParameter(2, OracleTypes.CURSOR);
//		6.执行SQL
		call.execute();
//		7.处理结果
		System.out.println(call.getClass().getName());
		OracleCallableStatement call2 = (OracleCallableStatement)call;
		ResultSet rs = call2.getCursor(2);
		while(rs.next()){
			System.out.println(rs.getObject("empno"));
			System.out.println(rs.getObject("ename"));
			System.out.println(rs.getObject("job"));
			System.out.println(rs.getObject("sal"));
			System.out.println("==================================");
		}
//		8.释放资源
		rs.close();
		call2.close();
		conn.close();
	}
	
	
	
}

 存储函数和过程在oracle中调用

-- 存储过程调用
call proc_updatesal(7369,10)

-- 方式2:
declare

begin
  proc_updatesal(7369,-10);
end;

---存储函数调用,和oracle中一般函数一样

-- 调用函数
declare
  vsum number;
begin
  vsum := func_getyearsal(7369);
  dbms_output.put_line('年薪:'||vsum);
end;


select func_getyearsal(empno) from emp;