PL/SQL集合的操作

取集合交集，代码：

set SERVEROUTPUT on;

--取集合交集
declare
	--声明本地类型Employees 
    type Employees is table of varchar2(10);
    --声明Employees类型的变量Enames,Authors,Union_emp 
    Enames Employees := Employees('aa','bb','cc','dd','ee');
    Authors Employees := Employees('aaa','bbb','ccc','ddd','eee');
    Union_emp Employees := Employees();
    --声明存储过程print_enames
    procedure print_enames (heading varchar2) is
        begin
        	--取Enames,Authors的交集
            Union_emp := Enames multiset intersect Authors;
            --如果结果为空，打印结果为空；否则循环打印结果集
            if Union_emp is empty then 
                DBMS_OUTPUT.PUT_LINE('Union_emp is empty');
            else 
                DBMS_OUTPUT.PUT_LINE(heading);
                for i in Union_emp.FIRST .. Union_emp.LAST loop
                dbms_output.put_line(Union_emp(i));
                end loop;
                DBMS_OUTPUT.PUT_LINE('******');
            end if;
        END;
begin
	--调用存储过程，打印enames
    print_enames('Intersect Initial:');
    --更改变量enames的第一个元素为aaa
    enames(1) := 'aaa';
    --重新调用存储过程，打印enames
    print_enames('Updated Initial:');
end;

批量处理结果集，代码

--批量处理结果集
declare
    --创建游标
    cursor c1 is select ename,sal from scott.emp where sal > 1000 and rownum<51 order by sal;
    --创建多属性记录类型
    type reclist is table of c1%rowtype;
    --创建多属性记录变量recs
    recs reclist;
begin
    --打开游标
    open c1;
    --把游标C1的内容批量导入记录recs
    FETCH c1 BULK COLLECT INTO recs;
    --关闭游标
    close c1;
    --循环打印rec对象内的元素
    for i in recs.first..recs.last loop
    dbms_output.put_line('Employees '||recs(i).ename||': $'||recs(i).sal);
    end loop;
end;