oracle存储过程的使用方法

77 阅读 0 评论 51 点赞

我是靠谱客的博主玩命凉面，最近开发中收集的这篇文章主要介绍oracle存储过程的使用方法，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

创建两张表

create table emp_work_plan(
code char(13) primary key,
strut_type varchar2(30),
strut_type_value varchar2(30),
create_time date,
operator varchar2(20),
plan_month char(6),
enable number(2)
);

insert into emp_work_plan(code,create_time,operator,plan_month,enable) values('HDR2014070001',sysdate,'刘伟','201407',0)

create table emp_work_plan_detail(
id number(4) primary key,
emp_id number references employee(emp_id),
work_day date,
work_time_type references work_time_type(id),
plan_id char(13) references emp_work_plan(code)
);
create sequence emp_work_plan_detail_seq increment by 1 start with 1;
insert into emp_work_plan_detail(id,emp_id,work_day,work_time_type,plan_id) 
values(emp_work_plan_detail_seq.nextval,1,to_date('2014-07-01 08:30:00','yyyy-mm-dd hh24:mi:ss'),1,'HDR2014070001');

创建存储过程

--使用存储过程批量添加数据
--排班管理
create or replace type type_emp_work_plan as object(
code nvarchar2(13),
strut_type nvarchar2(30),
strut_type_value nvarchar2(30),
create_time date,
operator nvarchar2(20),
plan_month nvarchar2(6),
enable number(2)
)
--班次类型id
create or replace type type_work_time_type_arr as table of number;
--员工id
create or replace type type_empIds as table of number;
--存储过程
create or replace procedure proc_save_work_plan_condition(
work_plan in type_emp_work_plan,
work_time_type_arr in type_work_time_type_arr,
start_date varchar2,
empIds in type_empIds
)
as
begin
  insert into emp_work_plan(code,strut_type,strut_type_value,create_time,operator,plan_month,enable) 
  values(work_plan.code,work_plan.strut_type,work_plan.strut_type_value,sysdate,work_plan.operator,work_plan.plan_month,work_plan.enable);
  for v_j in 1..empIds.count
  loop  
    for v_i in 1..work_time_type_arr.count
      loop
        insert into emp_work_plan_detail(id,emp_id,work_day,work_time_type,plan_id)
        values(emp_work_plan_detail_seq.nextval,empIds(v_j),(to_date(start_date,'yyyy-mm-dd hh24:mi:ss')+v_i-1),work_time_type_arr(v_i),work_plan.code);
      end loop;
  end loop;
  commit;
end;
--调用存储过程
declare
work_plan type_emp_work_plan:=type_emp_work_plan('HDR2014080002',null,null,sysdate,'刘伟','201408',1);
work_time_type_arr type_work_time_type_arr:=type_work_time_type_arr();
empIds type_empIds:=type_empIds();
begin
work_time_type_arr.extend(2);
work_time_type_arr(1):=1;
work_time_type_arr(2):=2;
empIds.extend(1);
empIds(1):=2;
proc_save_work_plan_condition(work_plan,work_time_type_arr,'2014-08-01',empIds);
end;

java中调用存储过程

public void saveWorkPlan(EmpWorkPlan empWorkPlan, int[] idList, String start, int[] empIds) {
		// TODO Auto-generated method stub
		String sql = "{ call proc_save_work_plan_condition(?,?,?,?) }";
		Connection conn = null;
		SessionFactoryImplementor sf = (SessionFactoryImplementor) (super.getSessionFactory());
		OracleCallableStatement state = null;
		try {
			conn = sf.getConnectionProvider().getConnection();
			state = (OracleCallableStatement) conn.prepareCall(sql);
			StructDescriptor sd = new StructDescriptor("TYPE_EMP_WORK_PLAN", conn);
			Object[] params = new Object[7];
			params[0] = empWorkPlan.getCode();
			params[1] = empWorkPlan.getStrutType();
			params[2] = empWorkPlan.getStrutTypeValue();
			params[3] = DateFormatUtil.toSqlDate(empWorkPlan.getCreateTime());
			params[4] = empWorkPlan.getOperator();
			params[5] = empWorkPlan.getPlanMonth();
			params[6] = (int) empWorkPlan.getEnable();
			STRUCT workPlan = new STRUCT(sd, conn, params);
			state.setSTRUCT(1, workPlan);

			ArrayDescriptor bcAd = new ArrayDescriptor("TYPE_WORK_TIME_TYPE_ARR", conn);
			ARRAY bcArr = new ARRAY(bcAd, conn, idList);
			state.setARRAY(2, bcArr);

			state.setString(3, start);

			ArrayDescriptor bcAd2 = new ArrayDescriptor("TYPE_EMPIDS", conn);
			ARRAY bcArr2 = new ARRAY(bcAd2, conn, empIds);
			state.setARRAY(4, bcArr2);

			state.execute();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				if (state != null)
					state.close();
				if (conn != null)
					conn.close();
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}