在此仅贴出包的代码方便回顾，完整例子见附件！
create or replace package pkg_dept is -- Author : YUANHONGBO -- Created : 2011/11/8 21:15:17 -- Purpose : /* *取上级部门id * *根据excel导入顺序，取得上一级部门：比当前行号小的第一个一级部门 */ function get_parent_id(p_busi_id number, p_id number, p_level number) return number; /* *处理部门维护信息 */ procedure process_department(p_busi_id number, p_level number default 6);end pkg_dept;/create or replace package body pkg_dept is /* *记录日志 */ procedure log(p_sql varchar2) is begin dbms_output.put_line(p_sql); exception when others then null; end; /* *取上级部门id * *根据excel导入顺序，取得上一级部门：比当前行号小的第一个一级部门 */ function get_parent_id(p_busi_id number, p_id number, p_level number) return number is v_pid number; c_sql sys_refcursor; v_sql varchar2(2000); v_error_code varchar2(200); v_error_msg varchar2(200); begin begin if p_level = 1 then v_pid := -1; else /*必须使用 order by desc 不能使用rownum*/ v_sql := 'select t.id from hw_ehr_dept_adjust_t t where t.id < ' || to_number(p_id) || ' and t.dept_level = ' || to_number(p_level - 1) || ' and t.busi_id = ' || to_number(p_busi_id) || ' order by t.id desc'; log(v_sql); open c_sql for v_sql; /* 取得第一行记录后结束 */ fetch c_sql into v_pid; log(v_pid); if c_sql%isopen then close c_sql; end if; end if; exception when others then v_pid := -999; v_error_code := sqlcode; v_error_msg := sqlerrm; log('v_error_code:' || v_error_code || ' v_error_msg:' || v_error_msg); end; return v_pid; end; /* *处理部门维护信息 */ procedure process_department(p_busi_id number, p_level number default 6) is type record_row is record( id number, dept_name varchar2(500), dept_code varchar2(500), dept_adjust_name varchar2(500), opt varchar2(50)); c_sql sys_refcursor; v_sql varchar2(4000); v_row record_row; v_error_code varchar2(200); v_error_msg varchar2(200); begin /*需要添加(p_level+1)级部门，用于最后一级部门修改名称*/ for level_index in 1 .. p_level loop v_sql := 'select id, l' || to_char(level_index) || '_name dept_name, l' || to_char(level_index) || '_code dept_code, l' || to_char(level_index + 1) || '_name dept_adjust_name , opt from hw_ehr_dept_t_temp t where t.l' || to_char(level_index) || '_code is not null order by id'; log(v_sql); open c_sql for v_sql; loop fetch c_sql into v_row; exit when c_sql%notfound; log('name:' || v_row.dept_name); insert into hw_ehr_dept_adjust_t (busi_id, id, p_id, dept_name, dept_code, dept_level, opt, attribute1, attribute2, attribute3) values (p_busi_id, v_row.id, get_parent_id(p_busi_id, v_row.id, level_index), v_row.dept_name, v_row.dept_code, level_index, v_row.opt, v_row.dept_adjust_name, null, null); end loop; if c_sql%isopen then close c_sql; end if; end loop; commit; exception when others then v_error_code := sqlcode; v_error_msg := sqlerrm; log('v_error_code:' || v_error_code || ' v_error_msg:' || v_error_msg); end;begin -- initialization null;end pkg_dept;/