以字符串数组替输入参数的存储过程

以字符串数组为输入参数的存储过程
今天项目中需要用到存储过程，使用的是字符串数组作为参数，经过不断尝试，终于搞定了
下面简单的记录下：

1、创建自定义数据类型
--自定义数据类型（可做数组用）

create or replace type jqjk_array AS VARRAY(50) of varchar(50); 

2、创建遍历数组循环的存储过程

CREATE OR REPLACE PROCEDURE testvarray(s_table  in jqjk_array,                                       t_table  in jqjk_array,                                       pk_field in jqjk_array,                                       dd       out varchar) IS  s_tableValue  varchar2(50);  t_tableValue  varchar2(50);  pk_fieldValue varchar2(50);BEGIN  dd := 'success';  for i in 1 .. s_table.count LOOP    BEGIN      s_tableValue  := s_table(i);      t_tableValue  := t_table(i);      pk_fieldValue := pk_field(i);      if s_tableValue is null or t_tableValue is null or         pk_fieldValue is null then        dd := '存在为空的值 ';        exit;      else      --调用另外一个存储过程，传入参数        pro_data_synchronic(s_tableValue, t_tableValue, pk_fieldValue);      end if;            EXCEPTION WHEN dup_val_on_index         THEN dd := 'error ';        exit;      end;      end LOOP;END testvarray;

这是上一个过程中调用的存储过程，用于同步两张结构一样的表的数据

create or replace procedure pro_data_synchronic (s_table nvarchar2, t_table nvarchar2, pk_field nvarchar2)as    step_one_sql varchar2(2000);   step_two_sql varchar2(2000);   step_three_sql varchar2(2000);   delete_sql varchar2(200);   s_pk_field varchar2(50);   t_pk_field varchar2(50);   s_ts varchar2(50);   t_ts varchar2(50);begin    /**   * 数据同步主要分为三步：   * 第一步：先删除目标表中存在的，但是来源表中不存在的数据(主要处理来源表物理删除的数据)   * delete from jq_bd_bdinfo where pk_bdinfo not in (select pk_bdinfo from bd_bdinfo);   *   * 第二步:删除已同步给目标表，但数据在源表中做过更新的数据   * delete from jq_bd_bdinfo where pk_bdinfo in    *         (select  bd_bdinfo.pk_bdinfo from bd_bdinfo   *                    inner join jq_bd_bdinfo on bd_bdinfo.pk_bdinfo=jq_bd_bdinfo.pk_bdinfo and bd_bdinfo.ts != jq_bd_bdinfo.ts);   *   * 第三步:同步源表中存在但目标表中不存在的数据给目标表   * insert into jq_bd_bdinfo select * from bd_bdinfo where bd_bdinfo.pk_bdinfo not in (select pk_bdinfo from jq_bd_bdinfo);   **/      s_pk_field := s_table || '.' || pk_field;   s_ts := s_table || '.' ||'ts';   t_pk_field := t_table || '.' || pk_field;   t_ts := t_table || '.' ||'ts';      delete_sql := 'delete from ' || t_table || ' where '|| pk_field;      step_one_sql := delete_sql || ' not in (select ' || pk_field || ' from ' || s_table || ')';      DBMS_OUTPUT.PUT_LINE(step_one_sql);      step_two_sql := delete_sql || ' in (' || ' select ' || t_pk_field || ' from ' || t_table;   step_two_sql := step_two_sql || ' inner join ' || s_table || ' on ' || s_pk_field || '=' || t_pk_field;   step_two_sql := step_two_sql || ' and ' || s_ts || '!=' || t_ts;   step_two_sql := step_two_sql || ' )';      DBMS_OUTPUT.PUT_LINE(step_two_sql);      step_three_sql := 'insert into ' || t_table || ' select * from ' || s_table || ' where ' || s_pk_field;   step_three_sql := step_three_sql || ' not in ( select ' || t_pk_field || ' from ' || t_table || ')';      DBMS_OUTPUT.PUT_LINE(step_three_sql);      execute immediate step_one_sql;      execute immediate step_two_sql;      execute immediate step_three_sql;      commit;   end;

3、下面看下java代码中是如何调用的

public void testArrayProceduer() {String[] t_table = { "jq_CORP","jq_BDINFO","jq_CURRTYPE","jq_PSNDOC","jq_DEPTDOC","jq_CUBASDOC","jq_CASHFLOW","jq_JOBBASFIL","jq_GLORG","jq_GLORGBOOK","jq_ACCSUBJ","jq_VOUCHER","jq_DETAIL","jq_FREEVALUE","jq_BALANCE","jq_VERIFYDETAIL","jq_GLBOOK","jq_JobMngFil","jq_cashflowcase","jq_vouchertype","jq_user" };String[] s_table = { "bd_corp", "BD_BDINFO", "BD_CURRTYPE","BD_PSNDOC" , "BD_DEPTDOC", "BD_CUBASDOC", "BD_CASHFLOW","BD_JOBBASFIL", "BD_GLORG", "BD_GLORGBOOK", "BD_ACCSUBJ","GL_VOUCHER", "GL_DETAIL", "GL_FREEVALUE", "GL_BALANCE","GL_VERIFYDETAIL", "BD_GLBOOK", "bd_JobMngFil","GL_CASHFLOWCASE","BD_VOUCHERTYPE", "SM_USER" };String[] pk_field = { "pk_corp", "pk_bdinfo", "pk_currtype","pk_psndoc" ,"pk_deptdoc", "pk_cubasdoc", "pk_cashflow","pk_jobbasfil", "pk_glorg", "pk_glorgbook", "pk_accsubj","pk_voucher", "pk_detail", "pk_freevalue", "pk_balance","pk_verifydetail", "pk_glbook", "pk_jobmngfil","pk_cashflowcase","pk_vouchertype", "cuserid" };ResultSet rs = null;ArrayDescriptor arrDesc = null;CallableStatement proc = null;try { proc = getConn().prepareCall("{ call cs_20120208.testvarray(?,?,?,?) }");arrDesc = ArrayDescriptor.createDescriptor("JQJK_ARRAY", getConn()); // jqjk_array /*重要！如果遇到在存储过程中获取不到参数或者获取到的参数值为null时， 请检查有没有加载该类库orai18n.jar(11g之前:nls_charset12.jar)*/ ARRAY array1 = new ARRAY(arrDesc, getConn(), s_table);ARRAY array2 = new ARRAY(arrDesc, getConn(), t_table);ARRAY array3 = new ARRAY(arrDesc, getConn(), pk_field);proc.setArray(1, array1);proc.setArray(2, array2);proc.setArray(3, array3);proc.registerOutParameter(4, Types.VARCHAR);proc.execute();String ss = (String) proc.getObject(4);System.out.println(" 返回结果： " + ss);} catch (SQLException ex2) {ex2.printStackTrace();} catch (Exception ex2) {ex2.printStackTrace();} finally {try {closeConn();if (rs != null) {rs.close();if (proc != null) {proc.close();}}} catch (SQLException ex1) {}}}

这种情况也可以通过下面两种方式实现：
1、将数组改为一个用逗号连接的字符串，到存储过程中进行拆分；
2、将需要传入的数组放入一个表中，在存储过程中使用游标遍历
如：

CREATE OR REPLACE PROCEDURE optjqr(rs out varchar) IS  s_tableValue  varchar2(50);  t_tableValue  varchar2(50);  pk_fieldValue varchar2(50);  CURSOR  CUR_TEMP is    select s_table, t_table, pk_field from jq_param ORDER BY nm;BEGIN  rs := 'success';  open CUR_TEMP;  LOOP     --把游标的某行值赋值给变量    fetch CUR_TEMP      into s_tableValue, t_tableValue, pk_fieldValue;      --调用存储过程      pro_data_synchronic(s_tableValue, t_tableValue, pk_fieldValue);           --无数据时退出    EXIT WHEN CUR_TEMP%NOTFOUND;  end LOOP;  --关闭游标  if CUR_TEMP%isopen then    close CUR_TEMP;  end if;END optjqr;

查看更多 下一篇