当前位置: 代码迷 >> SQL >> oracle 联系关系数组,bulk collect,forAll,动态SQL
  详细解决方案

oracle 联系关系数组,bulk collect,forAll,动态SQL

热度:103   发布时间:2016-05-05 14:21:41.0
oracle 关联数组,bulk collect,forAll,动态SQL
1.今天花了点时间看了下plsql的关联数组,可谓是几经波折啊,这个类型的格式要求非常的严格
我今天大半天的时间就花在这个格式的调整上,最后还是使用了plsql的美化器搞定.
SQL> set serveroutput on;SQL> SQL> declare  2    type emparr is table of scott.emp.job%type index by pls_integer;  3    arrname  emparr;  4    l_currow pls_integer;  5  begin  6    for currow in (select job, empno from scott.emp) loop  7      arrname(currow.empno) := currow.job;  8    end loop;  9    l_currow := arrname.first; 10    loop 11      exit when l_currow is null; 12      dbms_output.put_line('empno:=' || l_currow || ' job:=' || 13                           arrname(l_currow)); 14      l_currow := arrname.next(l_currow); 15    end loop; 16  end; 17  /empno:=7369 job:=CLERKempno:=7499 job:=SALESMANempno:=7521 job:=SALESMANempno:=7566 job:=MANAGERempno:=7654 job:=SALESMANempno:=7698 job:=MANAGERempno:=7782 job:=MANAGERempno:=7788 job:=ANALYSTempno:=7839 job:=PRESIDENTempno:=7844 job:=SALESMANempno:=7876 job:=CLERKempno:=7900 job:=CLERKempno:=7902 job:=ANALYSTempno:=7934 job:=CLERKPL/SQL procedure successfully completedSQL> 

Hints:
从oracle 9i release2开始,支持的数据的索引类型为
binary_integer,pls_integer,positive,natural,varchar2.
2.Bulk collect优点
1>.单个读取操作中检索数据库多条疾苦可以减少网络之间的通信次数,以改善性能
2>.bulk collect想集合中加载数据,可以极大的减少上下文切换的次数
begin  open cselectjob;  fetch cselectjob bulk collect    into jh_table_array;  close cselectjob;end;

3.forall同bulk collect
  forall i in number_collection.first .. number_collection.last save exceptions  insert into his_dept values number_collection(i);

4.动态SQL
使用using(绑定变量),into,returning的地方都跟在sql语句的字符串之后,并且不包含在引号之中
5.可变数组的例子
创建可变数组的例子:create or replace type mingxitype as object( goodsid varchar(15), incount int, providerid varchar(15));create or replace type arrmingxitype as varray(100) of mingxitype;/create table instock( orderid int primary key, indate date, mingxi arrmingxitype)/insert into instockvalues(1001,to_date('2005-10-19','yyyy-mm-dd'),       arrmingxitype(mingxitype('101',10,'so1'),                     mingxitype('193',30,'j02'),                     mingxitype('104',32,'i92')                    )      )/-----table函数可以把可变数组转换为关系表的形式显示出来;select * from table(select s.mingxi from instock s where orderid=1001)

http://sqcwfiu.iteye.com/blog/622065

  相关解决方案