Oracle学习笔记 -- day08 PL/SQL语法之存储过程、存储函数

最新推荐文章于 2021-10-25 16:40:46 发布

DJTUDaker

最新推荐文章于 2021-10-25 16:40:46 发布

阅读量478

点赞数 1

分类专栏：数据库

本文链接：https://blog.csdn.net/wingzhezhe/article/details/77651650

版权

数据库专栏收录该内容

17 篇文章 1 订阅

订阅专栏

一、存储过程

1、定义

存储过程是一段被命名化的 plsql 语句，是预编译到数据库中的

2、创建语法

create or replace procedure 存储过程名(参数1 [in] /out 数据类型...) -- in 是输入类型的参数，可以省略，默认为 in ， out 是输出类型的参数

as（is） -- as/is的作用替代了declare

begin

执行的动作

end；

3、存储过程的创建

示例：用储存过程计算指定员工的年薪

create or replace  procedure pro_yearsal(v_no   number)
is  --替代了declare
 v_yearsal number(8,2); 
begin
  select sal*12+nvl(comm,0) into v_yearsal from emp where empno=v_no;
  dbms_output.put_line('员工年薪是：'||v_yearsal);
end;

4、存储过程的使用

（1）、使用 call 关键字

call pro_yearsal(7788);
call pro_yearsal(7369);

（2）、使用 plsql 语法

begin
   pro_yearsal(7369);
end;

二、带out参数的存储过程应用

1、示例：用储存过程计算指定员工的年薪把计算出来的年薪放到out参数中

create or replace  procedure pro_yearsal2(v_no number,v_year_sal out number)
is  --替代了declare
 
begin
  select sal*12+nvl(comm,0) into v_year_sal from emp where empno=v_no;
 -- dbms_output.put_line('员工年薪是：'||v_yearsal);
end;

2、使用存储过程

注意：调用带out参数的存储过程的时候，不能使用 call 的方式

declare
  v_yearsal number(8,2);
begin
  pro_yearsal2(7788,v_yearsal);  -- 执行完存储过程之后，v_yearsal变量才会有值
  dbms_output.put_line(v_yearsal);
end;

三、存储函数

1、语法

create or replace function 存储函数名称(参数1 [in] / out 数据类型)

return 数据类型

is（as）

begin

return 具体的值;

end;

2、创建存储函数

示例：计算指定员工的年薪

create or replace function  func_yearsal(v_no in number )
return number 
is
 v_sal number(8,2);
begin

select sal*12+nvl(comm,0) into v_sal from emp where empno=v_no;
  return v_sal;
end;

3、使用存储函数

declare
 v_year_sal number(8,2);
begin
 v_year_sal:= func_yearsal(7788);
 dbms_output.put_line(v_year_sal);
end;

四、存储函数和存储过程的区别

1、语法上不同

2、使用场景不同，

存储函数多被存储过程调用，

项目与项目之间的相互调用使用的是存储过程

3、存储函数可以在sql中使用，存储过程不能在sql中使用

五、使用jdbc的方式调用存储函数和存储过程

1、导入jar包

2、创建工具类：BaseDao

package com.itcast.dao;

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

public class BaseDao {
	/**
	 * 加载驱动
	 */
	static{
		try {
			Class.forName("oracle.jdbc.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 获取连接
	 * @return
	 * @throws SQLException
	 */
	public static Connection getConn() throws SQLException{
		String url="jdbc:oracle:thin:@192.168.204.10:1521:orcl";
		String user="scott";
		String password="tiger";
		return DriverManager.getConnection(url, user, password);
	}
	
	public static void closeAll(ResultSet rs,Statement stmt,Connection conn){
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(stmt!=null){
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
}

3、测试类普通sql

package com.itcast.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class EmpDao {
	
	/**
	 * 输出指定部门的员工信息
	 * @param deptno
	 */
	public static void getEmp(Long deptno){
		Connection conn = null;
		PreparedStatement stmt =null;
		ResultSet rs = null;
		
		try {
			conn = BaseDao.getConn();
		    stmt = conn.prepareStatement("select * from emp where deptno=?");
		    stmt.setLong(1, deptno);
		    rs = stmt.executeQuery();
			while(rs.next()){
				System.out.println(rs.getLong(1)+rs.getString("ENAME"));
			}
			
		} catch (SQLException e) {
			 
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(rs, stmt, conn);
		}
		
		
	}
}

4、调用存储过程

package com.itcast.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import oracle.jdbc.driver.OracleTypes;


public class ProcedureDao {
//	调用的存储过程：procedure pro_yearsal2(v_no number,v_year_sal out number)
	
	/**
	 * 根据员工编号，查询年薪，调用的存储过程是pro_yearsal2
	 */
	public static void getyearsal(Long v_no){
		Connection conn = null;
		CallableStatement stmt = null;   //CallableStatement 用来出来处理存储过程、存储函数
		Long yearsal = 0l;
//		{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
//		{call <procedure-name>[(<arg1>,<arg2>, ...)]}

		try {
			conn = BaseDao.getConn();
//			procedure pro_yearsal2(v_no number,v_year_sal out number)
			stmt = conn.prepareCall("{call pro_yearsal2(?,?)}");
			stmt.setLong(1, v_no);
			stmt.registerOutParameter(2, OracleTypes.NUMBER);  // 对out参数指定数据类型
			stmt.execute();  //本行代码执行完成之后，out参数中已经有值
			yearsal = stmt.getLong(2); //获取out参数中的值
			System.out.println(yearsal);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(null, stmt, conn);
		}
		
	}
}

5、调用存储函数

package com.itcast.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import oracle.jdbc.driver.OracleTypes;

public class FunctionDao {
//	function  func_yearsal(v_no in number )
	/**
	 * 调用的存储函数是func_yearsal
	 */
	public static void getyearsal(Long v_no){
		Connection conn = null;
		CallableStatement stmt = null;   //CallableStatement 用来出来处理存储过程、存储函数
		Long yearsal = 0l;
//		{?= call <procedure-name>[(<arg1>,<arg2>, ...)]} -- 存储函数写法
//		{call <procedure-name>[(<arg1>,<arg2>, ...)]}  -- 存储过程写法

		try {
			conn = BaseDao.getConn();
			stmt = conn.prepareCall("{?=call func_yearsal(?)}");
			stmt.setLong(2, v_no);
			stmt.registerOutParameter(1, OracleTypes.NUMBER);  // 对返回值参数指定数据类型
			stmt.execute();
			yearsal = stmt.getLong(1);
			System.out.println(yearsal);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(null, stmt, conn);
		}
		
	}
}

6、调用out类型参数是游标的存储过程

package com.itcast.dao;

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

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

public class ProcedureCursorDao {
//	 pro_cursor(v_no in number,v_cursor out sys_refcursor)	
	/**
	 * 调用的存储过程是pro_cursor
	 */
	public static void getEmpInfo(Long v_no){
		Connection conn = null;
		CallableStatement stmt = null;   //CallableStatement 用来出来处理存储过程、存储函数
		ResultSet rs = null;
//		{?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
//		{call <procedure-name>[(<arg1>,<arg2>, ...)]}

		try {
			conn = BaseDao.getConn();
//			procedure pro_yearsal2(v_no number,v_year_sal out number)
			stmt = conn.prepareCall("{call pro_cursor(?,?)}");
			stmt.setLong(1, v_no);
			stmt.registerOutParameter(2, OracleTypes.CURSOR);  // 对out参数指定数据类型
			stmt.execute();
			rs = ((OracleCallableStatement)stmt).getCursor(2);
			while(rs.next()){
				System.out.println(rs.getLong(1)+"--"+rs.getString("ENAME"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			BaseDao.closeAll(rs, stmt, conn);
		}
		
	}
}

DJTUDaker

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle学习笔记 -- day08 PL/SQL语法之存储过程、存储函数

一、存储过程1、定义存储过程是一段被命名化的 plsql 语句，是预编译到数据库中的2、创建语法create or replace procedure 存储过程名(参数1 [in] /out 数据类型...) -- in 是输入类型的参数，可以省略，默认为 in ， out 是输出类型的参数as（is） -- as/is的作用替代了declarebe
复制链接

扫一扫