oracle pl/sql中模拟数组

在pl/sql没有Array数据这个概念，但是可以用表数据库类型来模拟数组：

实例：

create or replace function f_rpttmp_informationbynosignal ( i_begintime date, i_endtime date, i_closedtype varchar2, i_rptusername varchar2 )return number as v_begintime date; v_endtime date; v_rptusername varchar2(20); v_errorcode varchar2(10); v_errorcase varchar2(200); v_execstr varchar2(300); fuck1 pack_service.t_retdataset; v_cursql varchar2(800); v_closedtype varchar2(10); v_templatecontent varchar2(2000); v_str1 varchar2(200); v_str2 varchar2(100); v_str varchar2(10); type emp_ssn_array is table of varchar2(20) index by BINARY_INTEGER; best_employees emp_ssn_array; begin v_begintime := i_begintime; v_endtime := i_endtime; v_rptusername := i_rptusername; v_closedtype := i_closedtype; best_employees(1) := '宝坻';best_employees(2) := '北辰';best_employees(3) := '大港';best_employees(4) := '东丽';best_employees(5) := '汉沽'; best_employees(6) := '和平';best_employees(7) := '河北';best_employees(8) := '河东';best_employees(9) := '河西';best_employees(10) := '红桥'; best_employees(11) := '蓟县';best_employees(12) := '津南';best_employees(13) := '静海';best_employees(14) := '南开';best_employees(15) := '宁河'; best_employees(16) := '塘沽';best_employees(17) := '武清';best_employees(18) := '西青'; /* best_employees :=('宝坻','北辰', '大港', '东丽', '汉沽', '和平', '河北', '河东','河西', '红桥', '蓟县', '津南', '静海', '南开', '宁河','塘沽', '武清', '西青');*/ delete from t_rpttmp_informationbynosignal t where t.rptusername = v_rptusername; commit; if trunc(i_begintime, 'yy') = trunc(i_endtime, 'yy') then v_execstr :=' and t.accepttime >= :1 and t.accepttime < :2 and t.monthday >= to_char(:3,'||'''mmdd'''|| ') and t.monthday <= to_char(:4,'||'''mmdd'''||')'; else v_execstr :=' and t.accepttime >= :1 and t.accepttime < :2 and (t.monthday >= to_char(:3,'||'''mmdd'''|| ') or t.monthday <= to_char(:4,'||'''mmdd'''||'))'; end if; if i_closedtype = '3' then v_execstr := v_execstr||' and (a.dealtimes >= :5 and :6 = 3)'; else v_execstr := v_execstr||' and (a.dealtimes = :5 or :6 = '||'''99'''||')'; end if; v_cursql :='select t.templatecontent from t_wf_serviceinfohis t, t_wf_disposalstatushis a, t_tmprpt_usergrade c where t.serviceclassid = '||'''00090101'''||' and t.usergrade = c.id and c.username = :0 and t.serialno = a.serialno'||v_execstr; open fuck1 for v_cursql using v_rptusername, v_begintime, v_endtime,v_begintime, v_endtime, v_closedtype, v_closedtype; loop fetch fuck1 into v_templatecontent; exit when fuck1%notfound; v_str := '其他'; v_str1 := substr(v_templatecontent, instr(v_templatecontent,'</td><td>',1,1) + length('</td><td>'), instr(v_templatecontent,'</td></tr>',1,1) - (instr(v_templatecontent,'</td><td>',1,1) + length('</td><td>'))); v_str2 := substr(v_templatecontent, instr(v_templatecontent,'</td><td>',1,2) + length('</td><td>'), instr(v_templatecontent,'</td></tr>',1,2) - (instr(v_templatecontent,'</td><td>',1,2) + length('</td><td>'))); for i in 1 .. best_employees.count loop if instr(v_str1,best_employees(i)) > 0 then v_str := best_employees(i); exit; end if; end loop; insert into t_rpttmp_informationbynosignal(palce, inorout, rptusername) values (v_str, v_str2, v_rptusername); end loop; close fuck1; commit; return 1; exception when others then rollback; v_errorcode := substr(SQLCODe, 1, 10); v_errorcase := substr(SQLERRM, 1, 200); return 0; end f_rpttmp_informationbynosignal;

主要部分：

  type emp_ssn_array is table of varchar2(20) index by BINARY_INTEGER; -- 定义emp_ssn_array为数据表类型
  best_employees    emp_ssn_array;-- 实例化

数据输入：

  best_employees(1) := '宝坻';best_employees(2) := '北辰';best_employees(3) := '大港';

  best_employees(4) := '东丽';best_employees(5) := '汉沽';
  best_employees(6) := '和平';best_employees(7) := '河北';best_employees(8) := '河东';

  best_employees(9) := '河西';best_employees(10) := '红桥';
  best_employees(11) := '蓟县';best_employees(12) := '津南';best_employees(13) := '静海';

  best_employees(14) := '南开';best_employees(15) := '宁河';
  best_employees(16) := '塘沽';best_employees(17) := '武清';best_employees(18) := '西青';

取数据：

            for i in 1 .. best_employees.count loop
              if instr(v_str1,best_employees(i)) > 0 then
                v_str := best_employees(i);
                exit;
              end if;
            end loop;

 

这样使用起来和一维数组没什么区别了.

多维数据则复杂点可以参考:

http://benben.javaeye.com/blog/278140