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