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

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:=CLERK
empno:=7499 job:=SALESMAN
empno:=7521 job:=SALESMAN
empno:=7566 job:=MANAGER
empno:=7654 job:=SALESMAN
empno:=7698 job:=MANAGER
empno:=7782 job:=MANAGER
empno:=7788 job:=ANALYST
empno:=7839 job:=PRESIDENT
empno:=7844 job:=SALESMAN
empno:=7876 job:=CLERK
empno:=7900 job:=CLERK
empno:=7902 job:=ANALYST
empno:=7934 job:=CLERK

PL/SQL procedure successfully completed

SQL>

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 instock
values(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

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

相关推荐