第一次写存储过程，写的比较low，请怜惜

首先说一下，要写一个插入多表的数据所以我优先考虑了存储过程。先贴存储过程的代码:

CREATE
OR REPLACE PROCEDURE PROC_SCM_TO_HBSS AS
  v_pri_key          varchar2(100);
	v_dely_user_name   varchar2(100);
  v_data_source_bill varchar2(100);
  v_prof_type        varchar2(1);
  v_dely_org_cd      varchar2(100);
  v_th_count         number;
cursor cur_bills is
	SELECT
		DISTINCT
		T .PROF_TYPE,
		NVL(T1.TASK_CD, T1.ATTR9) TASK_CD,
		T .BILL_NO,
		T .ID
	FROM
		SCM_WMS_INV_PROC_TBL T
	INNER JOIN SCM_WMS_INV_PROC_DTL_TBL T1 ON T . ID = T1.BILL_ID
	INNER JOIN SCM_SYS_HBSS_ONOFF_TBL T2 ON T .PROF_TYPE = T2.PROF_TYPE --业务活动和专业属性开关表
	AND NVL(T1.TASK_CD, T1.ATTR9) = T2.TASK_CD AND T2.ONOFF='1'
	
	WHERE
		1 = 1
	AND(
		(
			T .DATA_SOURCE_BILL in ('ITEMUSE_REQ','REQUEST')
			AND T .APPLY_STATUS = '4'
		)
		OR(
			T .DATA_SOURCE_BILL = 'ITEM_RTW'
			AND T .APPLY_STATUS = '2'
		)
	)
	AND NOT EXISTS (select 1 from SCM_WS_HBSS_BILL_H_TBL t3 where t3.bill_no=t.BILL_NO)
	AND NOT EXISTS (select 1 from SCM_WS_HBSS_BILL_H_HIS_TBL t4 where t4.bill_no=t.BILL_NO)
;
bill cur_bills%rowtype;

	BEGIN
		
	
		for bill in cur_bills loop
			
			--退货默认发货人
			select nvl(t.data_source_bill, '0'),
						 nvl(t.prof_type, '0'),
						 nvl(t.dely_org_cd, '0')
				into v_data_source_bill, v_prof_type, v_dely_org_cd
				from scm_wms_inv_proc_tbl t
			 where t.id = bill.id;
			if v_data_source_bill = 'ITEM_RTW' then
				select count(1)
					into v_th_count
					from scm_wms_wh_manager_tbl t
				 where t.whs_code = v_dely_org_cd
					 and t.prof_type = v_prof_type
					 and t.enabled_flag = 'Y'
					 and t.delete_flg = '0';
				if v_th_count > 0 then
					select t.whs_worker_user_name
						into v_dely_user_name
						from scm_wms_wh_manager_tbl t
					 where t.whs_code = v_dely_org_cd
						 and t.prof_type = v_prof_type
						 and t.enabled_flag = 'Y'
						 and t.delete_flg = '0'
						 and rownum = 1;
				end if;
			end if;

			select 'SCM' || to_char(sysdate, 'yymmdd') ||
           scm_ws_hbss_inv_seq.nextval
			into v_pri_key
			from dual;

			INSERT INTO SCM_WS_HBSS_BILL_H_TBL
			(	ID,
				PRI_KEY,
				BILL_ID,
				BILL_NO,
				BILL_NAME,
				BILL_TYPE,
				ORGANIZE_CD,
				COMPANY_CD,
				CREATE_USER_NAME,
				CREATE_DATE,
				APPLY_STATUS,
				PROF_TYPE,
				INV_OPERATE_TYPE,
				DELY_ORG_CD,
				DELY_ORG_NAME,
				DELY_ADDRESS,
				DELY_USER_NAME,
				DELY_EMP_NAME,
				DELY_EMP_PHONE,
				DELY_EMP_EMAIL,
				RCPT_ORG_CD,
				RCPT_ORG_NAME,
				FATHER_ORG_CD,
				FATHER_ORG_NAME,
				DELIVERY_ADDRESS,
				RECEIVING_USER_NAME,
				RECEIVING_EMP_NAME,
				RECEIVING_EMP_PHONE,
				RECEIVING_EMP_EMAIL,
				DELY_COMPANY_CD,
				DELY_COMPANY_NAME,
				RCPT_COMPANY_CD,
				RCPT_COMPANY_NAME,
				DELY_ORG_PROP,
				RCPT_ORG_PROP,
				ATTR1,
				ATTR2,
				ATTR3,
				ATTR4,
				ATTR5,
				HANDLEA_FLG,
				ERROR_MESSAGE,
				INSERT_DATETIME,
				INSERT_USER_NAME,
				INSERT_PROGRAM_CD,
				UPDATE_DATETIME,
				UPDATE_USER_NAME,
				UPDATE_PROGRAM_CD
			)
			SELECT
				SCM_WS_HBSS_BILL_H_SEQ.NEXTVAL id,
				v_pri_key PRI_KEY,
				t1.id,
				t1.bill_no,
				t1.bill_name,
				CASE WHEN t1.DATA_SOURCE_BILL = 'ITEMUSE_REQ' THEN 'outbound'
						 WHEN t1.DATA_SOURCE_BILL = 'ITEM_RTW' THEN 'inbound'
						 END AS bill_type,
				t1.ORGANIZE_CD,
				t1.COMPANY_CD,
				t1.CREATE_USER_NAME,
				t1.CREATE_DATE,
				t1.APPLY_STATUS,
				t1.PROF_TYPE,
				t1.INV_OPERATE_TYPE,
				t1.DELY_ORG_CD,
				t1.DELY_ORG_NAME,
				t1.DELY_ADDRESS,
				nvl(v_dely_user_name, t1.dely_user_name) dely_user_name,
				nvl(t1.DELY_EMP_NAME,user1.full_name) DELY_EMP_NAME,
				nvl(t1.DELY_EMP_PHONE,user1.phone) DELY_EMP_PHONE,
				nvl(t1.DELY_EMP_EMAIL,user1.email) DELY_EMP_EMAIL,
				t1.RCPT_ORG_CD,
				nvl(t1.RCPT_ORG_NAME,rcpt.organize_name) RCPT_ORG_NAME,
				father.FATHER_ORG_CD FATHER_ORG_CD,
				father.FATHER_ORG_NAME FATHER_ORG_NAME,
				t1.DELIVERY_ADDRESS,
				t1.RECEIVING_USER_NAME,
				t1.RECEIVING_EMP_NAME,
				t1.RECEIVING_EMP_PHONE,
				t1.RECEIVING_EMP_EMAIL,
				t1.DELY_COMPANY_CD,
				delycom.company_name DELY_COMPANY_NAME,
				t1.RCPT_COMPANY_CD,
				rcptcom.company_name RCPT_COMPANY_NAME,
				t1.DELY_ORG_PROP,
				t1.RCPT_ORG_PROP,
				t1.ATTR1,
				t1.ATTR2,
				t1.ATTR3,
				t1.ATTR4,
				t1.ATTR5,
				'0' HANDLEA_FLG,
				'' ERROR_MESSAGE,
				sysdate INSERT_DATETIME,
				'plsql' INSERT_USER_NAME,
				'plsql' INSERT_PROGRAM_CD,
			  sysdate UPDATE_DATETIME,
			  'plsql' UPDATE_USER_NAME,
			  'plsql' UPDATE_PROGRAM_CD
			FROM SCM_WMS_INV_PROC_TBL T1 
				left join MST_USER_TBL user1
				on t1.dely_user_name = user1.user_name
				left join MST_ORGANIZE_TBL rcpt
				on t1.RCPT_ORG_CD = rcpt.organize_cd
				left join MST_ORGANIZE_TBL dely
				on t1.DELY_ORG_CD = dely.organize_cd
				left join MST_COMPANY_TBL delycom
				on t1.DELY_COMPANY_CD = delycom.company_cd
				left join MST_COMPANY_TBL rcptcom
				on t1.RCPT_COMPANY_CD = rcptcom.company_cd
				left join MST_ORGANIZE_TBL father 
				on t1.rcpt_org_cd = father.organize_cd
			WHERE t1.id=bill.id;

		insert INTO SCM_WS_HBSS_BILL_L_TBL 
			(	ID,
				LINE_ID,
				BILL_ID,
				ROW_ID,
				BRAND_ID,
				BRAND_NAME,
				ITEM_CD,
				ITEM_SPEC_ID,
				ADJ_ITEM_SPEC_ID,
				ITEM_SPEC,
				QUANTITY,
				UNIT,
				UNIT_PRICE,
				BATCH_NO,
				ADJ_BATCH_NO,
				ATTR33,
				PROJECT_CD,
				TASK_CD,
				EXPENDITURE_TYPE,
				INV_ITEM_STATUS,
				SOURCE_INV_ITEM_STATUS,
				NOTES,
				DATA_SOURCE,
				DATA_SOURCE_BILL_NO,
				DATA_SOURCE_ROW_NO,
				SOURCE_ORG_NAME,
				SOURCE_USER_NAME,
				SOURCE_UASER_MOBILE,
				ATTR8,
				ATTR9,
				ATTR16,
				ITEM_CLASSIFY,
				DEMAND_DATE,
				DELY_COMPANY_CD,
				RCPT_COMPANY_CD,
				ATTR1,
				ATTR2,
				ATTR3,
				ATTR4,
				ATTR5,
				HANDLEA_FLG,
				ERROR_MESSAGE,
				INSERT_DATETIME,
				INSERT_USER_NAME,
				INSERT_PROGRAM_CD,
				UPDATE_DATETIME,
				UPDATE_USER_NAME,
				UPDATE_PROGRAM_CD
			)
			SELECT 
				SCM_WS_HBSS_BILL_L_SEQ.NEXTVAL id,
				t2.id,
				t2.bill_id,
				t2.ROW_ID,
				t2.BRAND_ID,
				bt.brand_name,
				t2.ITEM_CD,
				t2.ITEM_SPEC_ID,
				t2.ADJ_ITEM_SPEC_ID,
				st.item_spec,
				t2.QUANTITY,
				t2.UNIT,
				t2.UNIT_PRICE,
				t2.BATCH_NO,
				t2.ADJ_BATCH_NO,
				t2.ATTR33,
				t2.PROJECT_CD,
				case when t2.data_source_bill = 'ITEMTRANS_REQ' 
						 then nvl(t2.task_cd, '-1')
             else t2.task_cd
             end as task_cd,
				t2.EXPENDITURE_TYPE,
				nvl(t2.inv_item_status, 'Y') as inv_item_status,
				t2.SOURCE_INV_ITEM_STATUS,
				t2.NOTES,
				t2.DATA_SOURCE,
				t2.DATA_SOURCE_BILL_NO,
				t2.data_source_row_id DATA_SOURCE_ROW_NO,
				mot.organize_name SOURCE_ORG_NAME,
			  t2.data_source_user_name SOURCE_USER_NAME,
			  ut.mobile_phone SOURCE_UASER_MOBILE,
				case
					 when t2.data_source_bill = 'ITEM_RTW' then
						t2.project_cd
					 else
						t2.attr8
				 end as attr8,
				 case
					 when t2.data_source_bill = 'ITEMTRANS_REQ' then
						nvl(t2.attr9, '-1')
					 else
						t2.attr9
				 end as attr9,
				t2.ATTR16,
				nvl(t2.item_classify,
				 case
					 when substr(t2.batch_no, 1, 1) in ('0') or
								substr(t2.batch_no, 2, 1) != '_' then
						'0'
					 when substr(t2.batch_no, 1, 1) = '1' and
								substr(t2.batch_no, 2, 1) = '_' then
						'1'
					 else
						substr(t2.batch_no, 1, 1)
				 end) as item_classify,
				t2.demand_date,
				case
						 when t2.data_source_bill = 'ITEMUSE_REQ' then
							nvl(t2.dely_company_cd, pt.dely_company_cd) 
						 else
							pt.dely_company_cd
					 end dely_company_cd,
				nvl(pt.rcpt_company_cd, pt.company_cd) rcpt_company_cd,
				 t2.attr1,
				 t2.attr2,
				 t2.attr3,
				 t2.attr4,
				 t2.attr5,
				'0' HANDLEA_FLG,
				'' ERROR_MESSAGE,
				sysdate INSERT_DATETIME,
				'plsql' INSERT_USER_NAME,
				'plsql' INSERT_PROGRAM_CD,
			  sysdate UPDATE_DATETIME,
			  'plsql' UPDATE_USER_NAME,
			  'plsql' UPDATE_PROGRAM_CD
			from SCM_WMS_INV_PROC_DTL_TBL t2
				left join pursite_brand_tbl bt 
				on bt.id = t2.brand_id
				left join scm_mtl_material_spec_tbl st 
				on st.id = t2.item_spec_id
				left join mst_user_tbl ut 
				on ut.user_name = t2.data_source_user_name
        left join mst_organize_tbl mot 
				on mot.organize_cd = t2.data_source_org_cd
				left join scm_wms_inv_proc_tbl pt 
				on pt.id = t2.bill_id
			WHERE t2.bill_id = bill.id;

		END loop;
	commit;
	END ;

该存储过程中，是无参数的，里面有一些变量，和业务相关，具体就不解释了。

设计到了游标，我需要在业务表中找到相关数据并且不存在我插入的接口数据表和接口数据历史表，循环业务表中的数据，插入接口表。

java代码如下:

@Override
	@Transactional(executorType = ExecutorType.BATCH)
	public Job scmToHbss(Job job) {
		String threadId = job.getAttr1();
		
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("threadId", threadId);
		//占据一个单子
		sqlSession.selectOne("LisSQL.callScmProcOcc", params); //线程占用
		String result = UtilString.nullToSpace((params.get("result")));//占用结果
		if(!"0".equals(result)){
			sqlSession.update("HbssSQL.callScmToHbssData");
		}
		return job;
	}

主要是作为一个批处理，数据库相关框架用了mybatis。调用了存储过程。

mybatis配置xml如下:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="HbssSQL"> 
	
	<select id="callScmToHbssData" parameterType="Map" statementType="CALLABLE">
		{call proc_scm_to_hbss()}
	</select>
	
</mapper>

以上就是我写的比较简单的存储过程调用的全部，具体的调用是我们使用job定时服务，按时跑该方法实现的。

触发器--每次删除的时候记录到表：

create or replace trigger trg_ready_task_tbl
  after delete on scm_workflow_ready_task_tbl
  for each row
declare
begin
  insert into ready_task_chg
    (id,
     bill_type,
     bill_no,
     bill_title,
     apply_id,
     piid,
     node,
     node_display_name,
     handler,
     handler_emp_name,
     handler_url,
     submit_user_name,
     submit_datetime,
     flow_start_user,
     flow_start_datetime,
     system_cd,
     submit_org_cd,
     submit_org_name,
     submit_emp_name,
     insert_date_time,
     update_date_time,
     create_date_time,
     tiid,
     company_cd,
     delete_flag,
     delete_date)
   values(
     :old.id,
    :old.bill_type,
    :old.bill_no,
    :old.bill_title,
    :old.apply_id,
    :old.piid,
    :old.node,
    :old.node_display_name,
    :old.handler,
    :old.handler_emp_name,
    :old.handler_url,
    :old.submit_user_name,
    :old.submit_datetime,
    :old.flow_start_user,
    :old.flow_start_datetime,
    :old.system_cd,
    :old.submit_org_cd,
    :old.submit_org_name,
    :old.submit_emp_name,
    :old.insert_date_time,
    :old.update_date_time,
    :old.create_date_time,
    :old.tiid,
    :old.company_cd,
    :old.delete_flag,
    sysdate
   );
end;

oracle job 注册：

DECLARE
  jobno NUMBER;
BEGIN
  dbms_job.submit(job       => jobno,
                  what      => 'PROC_INSERT_WORKFLOW_TASK;',
                  next_date =>  to_date('01-12-2016 14:44:41', 'dd-mm-yyyy hh24:mi:ss'),
                  INTERVAL  => 'trunc(sysdate+1)+143/144');
  dbms_output.put_line('job_no = ' || jobno);
  COMMIT;
END;

上面的job是每晚的11.50分 开始跑一个名字叫做 PROC_INSERT_WORKFLOW_TASK的存储过程，next_date是指跑这个job的第一次时间，相当于注册，后面每次跑的时间会根据interval来计算。