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

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

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;