oracle中多行增删改

--创建包头
create or replace package jlc_zfh_package2  as

--定义jlc_zfh_student中字段的集合类型
type tab_stuId   is table of jlc_zfh_student.stuId%type index by binary_integer;
-- jlc_zfh_student.stuId为表名称
type tab_stuName is table of jlc_zfh_student.stuname%type index by binary_integer;
type tab_stuHome is table of jlc_zfh_student.stuhome%type index by binary_integer;
type tab_stuSex  is table of jlc_zfh_student.stusex%type index by binary_integer;
type tab_flag    is table of char(1) index by binary_integer;


  --更新jlc_zfh_student信息
  function update_jlc_zfh_student
(
                            para_stuId         in tab_stuId,
                            para_stuName       in tab_stuName,
                            para_stuHome       in tab_stuHome,
                            para_stuSex        in tab_stuSex,
                            para_flag          in tab_flag
)return varchar2;


end jlc_zfh_package2 ;

--创建包体
create or replace package body jlc_zfh_package2  as


 function update_jlc_zfh_student
(
   para_stuId         in tab_stuId,
                            para_stuName       in tab_stuName,
                            para_stuHome       in tab_stuHome,
                            para_stuSex        in tab_stuSex,
                            para_flag          in tab_flag
    )return varchar2  is


  execute_result varchar2(200);


  begin
    execute_result := 'to execute';
    for idx in 1 .. para_flag.count loop


      case
  --标识位为‘1’时 执行insert 操作
        when  para_flag(idx) = '1' then
          insert into jlc_zfh_student
          values
            (
             para_stuId(idx),
             para_stuName(idx),
             para_stuHome(idx),
             para_stuSex(idx)
            );


          --标识位为‘2’时 执行update操作
        when  para_flag(idx) = '2' then


          update jlc_zfh_student


             set jlc_zfh_student.stuId= para_stuId(idx),
                 jlc_zfh_student.stuName= para_stuName(idx),
                 jlc_zfh_student.stuHome= para_stuHome(idx),
                 jlc_zfh_student.stuSex= para_stuSex(idx)


           where jlc_zfh_student.stuId= para_stuId(idx);


      --标识位为‘3’时 执行delete操作
        when  para_flag(idx) = '3' then


          delete from jlc_zfh_student


           where jlc_zfh_student.stuId= para_stuId(idx);


      end case;


    end loop;


    commit;


    execute_result := 'succeed';


    return execute_result;


  exception


    when others then
      execute_result := sqlerrm;
      return execute_result;
      commit;


  end update_jlc_zfh_student;
end;