Oracle定义数组 Oracle遍历数组 循环取数

Oracle 定义数组 遍历数组 循环取数

记录一次写带有定义数组 遍历数组 循环取数的存储过程的经历。

数组的定义

  --Oracle的基本概念中没有数组一说，需要自定义一个数组的数据类型
  --定义一个数据类型，名为type_array，可以放置两个字符串，每个字符串长度不可超过20
  TYPE type_array is varray(2) of varchar2(20);
  --定义一个名为var_array的为type_array。var_array才是数组的名字。进行数组初始化，赋初值。
  var_array type_array := type_array('col1','col2');

数组的循环

--i是数组中字符串的位置，var_array.count是数组中字符串的总数，var_array(i)就是确定位置的字符串
    for i IN 1..var_array.count
      LOOP
         dbms_output.put_line(var_array(i));
      end loop;

存储过程实例

create or replace procedure replace_JKBH_cs(new_JKBH VARCHAR2)
AS
  y_new_JKBH VARCHAR2(20);
  i_col VARCHAR2(20);
  v_sql varchar2(4000);
  --定义一个数据类型，为type_array
  TYPE type_array is varray(3) of varchar2(20);
  --定义一个名为var_array的为type_array
  var_array type_array := type_array('JLBH','JKBH');
BEGIN
  y_new_JKBH:=new_JKBH;
  execute immediate'DELETE FROM REPLACE_CS';
  execute immediate'INSERT INTO REPLACE_CS(JKBH,MC)SELECT jkbh,MC FROM cs_cs1';
  COMMIT;

    --更新replace_dw中的newJKBH
    FOR e IN(SELECT JKBH FROM REPLACE_CS)
      loop
         v_sql:='update REPLACE_CS set newJKBH='''||y_new_JKBH||''' where JKBH='''||e.JKBH||'''';
		 execute immediate v_sql;
         y_new_JKBH:=y_new_JKBH+1;
  end loop;
  COMMIT;

      --遍历var_array的每一个数据
    for i IN 1..var_array.count
      LOOP
        i_col:=var_array(i);
          FOR e IN (SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM USER_TAB_COLS WHERE COLUMN_NAME=''||i_col||'' ) AND TEMPORARY='N' AND TABLE_NAME NOT IN('REPLACE_CS') AND TABLE_NAME NOT LIKE '%$%')
            LOOP
                FOR d IN (SELECT JKBH FROM REPLACE_CS)
                    LOOP
                      v_sql:='update '||e.table_name||' k set '||i_col||'=(SELECT newJKBH FROM REPLACE_CS WHERE jkbh=k.'||i_col||') where  '||i_col||'='''||d.jkbh||'''';
                      execute immediate v_sql;
                END LOOP;
          END LOOP;
     end loop;
     COMMIT;
END;

本意是想在存储过程中循环遍历所有含有某个字段的表进行数据更换，然后想到了利用一下数组。其实到最后这个方案还是废弃了，选择在程序中写循环，而不是存储过程中循环调用。不过这次也确实学习到了Oracle中数组的定义和应用，如果大家有需要用到数组，希望这篇文章能有所帮助。