Oracle存储过程编写与调用

写在前面

• 个人博客首页
• 注：学习交流使用！

---

正文

场景描述

最近在做数据初期导入这一块的功能，一个新的项目，数据来源可能来自于不同的系统，我负责写接口对接的功能，需要根据第三方提供的接口，将数据导入我们自己的项目，为了数据的安全性与稳定性，肯定是要采取中间表的，那么就存在中间表如何向目标表转移的问题，开发经理提供了两种方式我解决，通过java代码来进行转表，通过存储过程来进行转表。

先上菜，可以跳过

CREATE OR REPLACE PACKAGE FSP_ETL.FSP_ETL_FUND_PAYMENT_BOE_DATA_IMP_PKG IS

  --收款单报账单头数据同步
  PROCEDURE SP_ETL_PAYMENT_BOE_HEADER(P_IN_PARAM IN VARCHAR2);
  --收款单报账单行数据同步 省略掉不写
  PROCEDURE SP_ETL_PAYMENT_BOE_EXPENSE(P_IN_PARAM IN VARCHAR2);

END FSP_ETL_FUND_PAYMENT_BOE_DATA_IMP_PKG;
/


CREATE OR REPLACE PACKAGE BODY FSP_ETL.FSP_ETL_FUND_PAYMENT_BOE_DATA_IMP_PKG IS
/**
   * DMS系统 收款单数据同步
   * author:jungle
   * date:20211217
   * desc:SAP凭证数据同步，中间表到目标表
   * sourceTable:
   * 1)收款单中间表: FSP_EAS.DMS_MID_PAYMENT
   * 2)
   * 3)
   * targetTable:
   * 1)报账单头表: FSP_EAS.DMS_BOE_HEADER
  **/
PROCEDURE SP_ETL_PAYMENT_BOE_HEADER(P_IN_PARAM IN VARCHAR2) AS

N_INS_ROWS NUMBER;

N_UPD_ROWS NUMBER;

N_DEL_ROWS NUMBER;

V_PROC_NAME VARCHAR2(100);

V_ERROR_INFO VARCHAR2(4000);

V_COUNT INTEGER;

N_CNT NUMBER;

N_SEQ_ID NUMBER;
-- V_ARCHIVE_STATUS  VARCHAR2(100);

BEGIN
-- 初始化插入的行数、更新的行数、删除的行数
N_INS_ROWS := 0;

N_UPD_ROWS := 0;

N_DEL_ROWS := 0;
--游标记录数
 N_CNT := 1;
-- 初始化该过程名称
 V_PROC_NAME := 'FSP_ETL_FUND_PAYMENT_BOE_DATA_IMP_PKG.SP_ETL_PAYMENT_BOE_HEADER';
--写存储过程开始记录
 COM_SYS_LOG_PKG.SP_PROCESS_START(V_PROC_NAME);
--获取最新的id值
 SELECT
	GREATEST((SELECT GEN_VALUE FROM FSP_EAS.SYS_ID_GEN WHERE GEN_NAME = 'seq_dms_boe_header'),(SELECT NVL(MAX(ID), 0) + 1 FROM FSP_EAS.DMS_BOE_HEADER)) INTO
		N_SEQ_ID
	FROM
		DUAL;
--查询
 FOR CUR_1 IN (
SELECT
	CREATION_DATE AS CREATION_DATE,
	LAST_UPDATED_DATE AS LAST_UPDATED_DATE,
	ENABLED_FLAG AS ENABLED_FLAG
FROM
	FSP_EAS.DMS_MID_PAYMENT
WHERE
	ENABLED_FLAG = 'Y' AND ATTRIBUTE19 IS NULL)
--开始循环
 LOOP
--判断是否需要修改,根据 资金单据号
 SELECT
	COUNT(*) INTO
		V_COUNT
	FROM
		FSP_EAS.DMS_BOE_HEADER T
	WHERE
		T.BOE_NO = CUR_1.BOE_NO;

IF V_COUNT > 0 THEN
--修改数据

BEGIN UPDATE
	FSP_EAS.DMS_BOE_HEADER T
SET
	VERSION = VERSION + 1,
	LAST_UPDATED_DATE = CUR_1.LAST_UPDATED_DATE
WHERE
	T.BOE_NO = CUR_1.BOE_NO;
END;

N_UPD_ROWS := N_UPD_ROWS + 1;
ELSE
BEGIN
--新增数据
 INSERT
	INTO
		FSP_EAS.DMS_BOE_HEADER(ID,
		VERSION,
		BOE_TYPE_CODE,
		CREATED_BY,
		CREATION_DATE,
		LAST_UPDATED_BY,
		LAST_UPDATED_DATE,
		ENABLED_FLAG)
	VALUES(N_SEQ_ID,
	1,
	(
	SELECT
		value
	FROM
		fsp_eas.sys_data_code
	WHERE
		code = '20'
		AND parent_id = (
		SELECT
			id
		FROM
			fsp_eas.sys_data_code c
		WHERE
			c.code = 'DMS_BOE_TYPE_CODE_SET')),
	-1,
	CUR_1.CREATION_DATE,
	-1,
	CUR_1.LAST_UPDATED_DATE,
	CUR_1.ENABLED_FLAG);

N_SEQ_ID := N_SEQ_ID + 1;

N_INS_ROWS := N_INS_ROWS + 1;
END;
END IF;

N_CNT := N_CNT + 1;

IF MOD(N_CNT,
1000) = 0 THEN COMMIT;
END IF;
END LOOP;
-- 修改序列值
 UPDATE
	FSP_EAS.SYS_ID_GEN
SET
	GEN_VALUE = N_SEQ_ID
WHERE
	GEN_NAME = 'seq_dms_boe_header';
--写存储过程结束记录
 COM_SYS_LOG_PKG.SP_PROCESS_END(V_PROC_NAME,
N_INS_ROWS,
N_UPD_ROWS,
N_DEL_ROWS);

COMMIT;
--  写异常处理信息，公用模块??成后，切入异常部分
 EXCEPTION
WHEN OTHERS THEN NULL;

ROLLBACK;

V_ERROR_INFO := SUBSTR(SQLERRM, 1, 3600);
--写执行失败信息
 SYS_EXP_PKG.SP_PROCESS_ERROR(V_PROC_NAME,
V_ERROR_INFO || N_CNT);
-- 修改序列值
 UPDATE
	FSP_EAS.SYS_ID_GEN
SET
	GEN_VALUE = (
	SELECT
		NVL(MAX(ID), 0) + 1
	FROM
		FSP_EAS.DMS_BOE_HEADER)
WHERE
	GEN_NAME = 'seq_dms_boe_header';

COMMIT;
END;
END FSP_ETL_FUND_PAYMENT_BOE_DATA_IMP_PKG;

存储过程是什么？

存储过程就是存储的过程 （0.0），存放在数据库中，一次编译以后就可以直接用，那么优点就是服务器开销会很小，缺点就是额外的学习成本，以及后续不太好维护。
PACKAGE ：包，内部可以定义多个过程，

• 分为包的声明（包含过程的声明）
  

  CREATE OR REPLACE PACKAGE 
  TEST_PACKAGE IS
  -- 定义过程一
  PROCEDURE TEST_PROCEDURE_A(IN_PARAM IN VARCHAR2,OUT_PARAM OUT VARCHAR2);
  -- 定义过程二
  PROCEDURE TEST_PROCEDURE_B(IN_PARAM IN VARCHAR2,OUT_PARAM OUT VARCHAR2);
  END TEST_PACKAGE;

• 与包体的编写（包含过程的实现）
  

  CREATE OR REPLACE PACKAGE BODY TEST_PACKAGE IS 
  PROCEDURE TEST_PROCEDURE_A(IN_PARAM IN VARCHAR2,OUT_PARAM OUT VARCHAR2) AS 
  --定义变量
  --XXX INTEGER
  --实际过程
  BEGIN
  --XXXX
  END;
  COMMIT;
  END;
  END TEST_PACKAGE;

• 其他知识点： 循环，游标……

  如何调用存储过程？

  1. 测试存储过程

• 编写完存储过程后，测试如何：
  （1） 找到对应用户下面的package bodies，找到对应的package，如下：

<img src="https://img-blog.csdnimg.cn/ceb8a429727f472a9c8fbd02322e8421.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA5a2m5pWy5Luj56CB55qE5bCP5L-K5qGR,size_12,color_FFFFFF,t_70,g_se,x_16#pic_center" width=50%></img>

(2) 右键过程测试

（3)点击运行即可等待即可，调试栏可以进行调试操作，为了好观察错误，最好还是要写异常打印，起码找到问题大致点出现在哪。我的存储过程写了，在那条数据出错，会给我返回那条数据的行号。

2. Java代码调用存储过程

此处比较长，首先贴出来关键代码

public class DmsVoucherDataTransDaoImpl  implements DmsVoucherDataTransDao {
		
	private JdbcTemplate jdbcTemplate;

	@Autowired
	@Qualifier("dataSource")
	public void setDataSource(DataSource dataSource) {
		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}

	@Override
	public void callProcedure() {

		//数据转存到凭证头
		String spNameHeader = "FSP_ETL_SQ_VOUCHER_DATA_IMP_PKG.SP_ETL_VOUCHER_HEADER_IMP";
		try {
			SqlParameterSource in = new MapSqlParameterSource().addValue("P_IN_PARAM","", Types.VARCHAR);;
			SimpleJdbcCall jdbcCall = new SimpleJdbcCall(this.jdbcTemplate)
					.withCatalogName("FSP_ETL")
					.withProcedureName(spNameHeader)
					.withoutProcedureColumnMetaDataAccess()
					.declareParameters(
							new SqlParameter("P_IN_PARAM", Types.VARCHAR));
			jdbcCall.execute(in);
		} catch (Exception e) {
			e.printStackTrace();
		}

}

关键点：
（1） 此处用到了dataSource取的是applicationContext.xml所配置的数据源。

<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">   
      <!-- 基本属性 url、user、password -->  
      	<property name="driverClassName" value="${jdbc.driver}" />
     	<property name="url" value="${jdbc.url}" />
		<property name="username" value="${jdbc.username}" />
		<property name="password" value="${jdbc.password}" />
</bean>

jdbc.driver=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@192.168.0.1:1521/jungle
jdbc.username=Jungle
jdbc.password=Jungle_123

（2） 通过取到的数据源对象，创建JdbcTemplate ，准备好与数据库的连接等操作，这个地方公司其实有其他的写法，因为Dao层，公司根据JbdcTemplate进行了二次开发，但是后来项目上，都是再用Hibernate所以，就没见用过这一套，此处直接改为通用版本。

 public class DmsVoucherDataTransDaoImpl implements DmsVoucherDataTransDao {

private JdbcTemplate jdbcTemplate;

@Autowired
@Qualifier("dataSource")
public void setJdbcTemplate(DataSource dataSource) {
	this.jdbcTemplate = new JdbcTemplate(dataSource);
}

（3）详解具体执行过程

//设置存储过程名
String spName = "FSP_ETL_SQ_VOUCHER_DATA_IMP_PKG.SP_ETL_VOUCHER_HEADER_IMP";
//设置输入参数的类型，名称，以及内容，需要与存储过程一致，此处为空。
SqlParameterSource in = new MapSqlParameterSource().addValue("P_IN_PARAM","", Types.VARCHAR);
//声明SimpleJbbcCall，设置对应存储过程参数，jdbcTemplate，用户，过程名，以及输入参数等。
SimpleJdbcCall jdbcCall = new SimpleJdbcCall(this.jdbcTemplate)
		.withCatalogName("FSP_ETL")
		.withProcedureName(spName)
		.withoutProcedureColumnMetaDataAccess()
		.declareParameters(new SqlParameter("P_IN_PARAM", Types.VARCHAR));
//调用执行，实际上执行语句为,本质就是数据库中执行call ....
jdbcCall.execute(in);

小结

存储过程的重难点是在维护与调试上，相对于Java代码的清晰明了，如何做到编写可维护的存储过程是关键：写好注释，编写返回异常的内容，查询时常用AS（看实际情况）。我用到存储过程，都是临时表转正式表时使用，拿别人系统数据，并适应自己系统的规范，其他地方的使用还未尝试。

---

参考资料

• 参考一
• 参考二

  ---