集合运算

ORACLE10g增加了集合运算，方便各种操作，见具体实例：[@more@]

DECLARE
TYPE t_tbls IS TABLE OF VARCHAR2(20);
t_tbl1 t_tbls;
t_tbl2 t_tbls;
t_tbl3 t_tbls;
t_tbl4 t_tbls;
t_tbl5 t_tbls;
BEGIN
t_tbl1 := t_tbls('a', 'b', 'c');
t_tbl2 := t_tbls('a', 'b', 'd', 'c');
t_tbl3 := t_tbls('a', 'c', 'b');
t_tbl5 := t_tbls('a', 'b', 'a', 'c');

-- 1、 =的用法
IF t_tbl1 = t_tbl3 THEN
dbms_output.put_line('1、' || q'{t_tbls('a', 'b', 'c')}' || ' = ' ||
q'{t_tbls('a', 'c', 'b')}');
END IF;

-- 2、<>的用法
IF t_tbl1 <> t_tbl2 THEN
dbms_output.put_line('2、' || q'' || ' <> ' ||
q'');
END IF;

-- 3、IN的用法
IF t_tbl1 IN (t_tbl3) THEN
dbms_output.put_line('3、' || q'[t_tbls('a','b','c')]' || ' IN ' ||
q'[t_tbls('a', 'c', 'b')]');
END IF;

-- 4、NOT IN的用法
IF t_tbl1 NOT IN (t_tbl2) THEN
dbms_output.put_line('4、' || q'[t_tbls('a','b','c')]' ||
' NOT IN ' || q'[t_tbls('a','b','d','c')]' ||
' ,注意我,in不是子集的概念');

END IF;

-- 5、submultiset用法
IF t_tbl1 submultiset OF t_tbl2 THEN
dbms_output.put_line('5、' || q'[t_tbls('a','b','c')]' || ' 是 ' ||
q'[t_tbls('a','b','d','c')]' || ' 的子集');
END IF;

--6、multiset union用法
t_tbl4 := t_tbl1 MULTISET UNION t_tbl2;
dbms_output.put('6、' || q'[t_tbls('a', 'b', 'c')]' ||
' MULTISET UNION ' || q'[t_tbls('a', 'b', 'd', 'c')]' ||
' is：');
FOR i IN t_tbl4.first .. t_tbl4.last LOOP
dbms_output.put(t_tbl4(i) || ' ');
END LOOP;
dbms_output.put_line('');

--7、multiset union distinct 用法
t_tbl4 := t_tbl1 MULTISET UNION DISTINCT t_tbl2;
dbms_output.put('7、' || q'[t_tbls('a', 'b', 'c')]' ||
' MULTISET UNION DISTINCT ' ||
q'[t_tbls('a', 'b', 'd', 'c')]' || ' is：');
FOR i IN t_tbl4.first .. t_tbl4.last LOOP
dbms_output.put(t_tbl4(i) || ' ');
END LOOP;
dbms_output.put_line('');

--8、multiset intersect 用法
t_tbl4 := t_tbl1 MULTISET INTERSECT t_tbl2;
dbms_output.put('8、' || q'[t_tbls('a', 'b', 'c')]' ||
' MULTISET INTERSECT ' ||
q'[t_tbls('a', 'b', 'd', 'c')]' || ' is：');
FOR i IN t_tbl4.first .. t_tbl4.last LOOP
dbms_output.put(t_tbl4(i) || ' ');
END LOOP;
dbms_output.put_line('');

--9、multiset except 用法
t_tbl4 := t_tbl2 MULTISET except t_tbl3;
dbms_output.put('9、' || q'[t_tbls('a', 'b', 'd', 'c')]' ||
' MULTISET INTERSECT ' || q'[t_tbls('a', 'c', 'b')]' ||
' is：');
FOR i IN t_tbl4.first .. t_tbl4.last LOOP
dbms_output.put(t_tbl4(i) || ' ');
END LOOP;
dbms_output.put_line('');

--10、cardinality 用法，不知与count的区别
dbms_output.put_line('10、' || q'[t_tbls('a', 'b', 'c')]' ||
' 的元素个数(cardinality)为：' || cardinality(t_tbl1));

--11、member of 用法
IF 'a' MEMBER OF t_tbl1 THEN
dbms_output.put_line('11、' || q'['a']' || ' is member of ' ||
q'{t_tbls('a', 'b', 'c')}');
END IF;

--12、set 用法，去年嵌套表中的重复值
t_tbl5 := SET(t_tbl5);
dbms_output.put('12、SET(' || q'[t_tbls('a', 'b', 'a', 'c')]' ||
' ) is：');
FOR i IN t_tbl5.first .. t_tbl5.last LOOP
dbms_output.put(t_tbl5(i) || ' ');
END LOOP;
dbms_output.put_line('');

--13、is a set：判断嵌套表是否有重复值
IF t_tbl5 IS a SET THEN
dbms_output.put_line('13、' || q'[t_tbls('a', 'b', 'a', 'c')]' ||
' is a set：有重复元素!');
END IF;

--14、is empty：是否为空
IF t_tbl5 IS NOT empty THEN
dbms_output.put_line('14、' || q'[t_tbls('a', 'b', 'a', 'c')]' ||
' is not empty：不为空');
END IF;

--15、collect：将值转换为嵌套表，SQL函数，需全局type
--select cast(COLLECT('a,b') AS t_tbls) INTO t_tbl5 from dual;

END;
0
0

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10455206/viewspace-1037206/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10455206/viewspace-1037206/