oracle function小例子
CREATE OR REPLACE TYPE typetmp AS TABLE OF VARCHAR2(4000);CREATE OR REPLACE FUNCTION functiontmp(l_orders IN VARCHAR2)RETURN typetmpIS l_typetmp typetmp:= typetmp(); --初始化typetmp，后面通过数组的方式使用 noLen INTEGER := 8; l_order VARCHAR2(3000); l_orderIds VARCHAR2(3000); --input data l_strs VARCHAR2(3000); --DNCContactNo Strings l_str VARCHAR2(3000); --each DNCContactNo String l_orderId VARCHAR2(3000); --each order ---------------------------declare cursor start------------------------------------ CURSOR t_cursor IS SELECT coltmp FROM tabletmp WHERE datetmp >= trunc(sysdate-1) and datetmp < trunc(sysdate); ---------------------------declare cursor end--------------------------------------- BEGIN ---------------------------get orderIDs start-------------------------------------- 使用cusor将查询结果集放入字符串l_orderIds中，两次使用fetch into ----------------------------------------------------------------------------------- IF l_orders IS NULL THEN BEGIN OPEN t_cursor; FETCH t_cursor INTO l_order; WHILE t_cursor%FOUND LOOP--judge whether get last record IF l_orderIds IS NULL THEN l_orderIds := l_order||','; ELSE l_orderIds := l_order||','||l_orderIds||','; END IF; FETCH t_cursor INTO l_order;--loop cursor for get each record END LOOP; EXCEPTION WHEN OTHERS THEN CLOSE t_cursor; IF t_cursor%isopen THEN CLOSE t_cursor; END IF; END; END IF; --------------------------get orderIDs end------------------------------------------------ IF l_orders<>'' OR l_orders IS NOT NULL THEN l_orderIds := l_orders||','; END IF; WHILE l_orderIds IS NOT NULL LOOP l_orderId := SUBSTR(l_orderIds, 1, INSTR(l_orderIds, ',')-1);--instr()函数获取字符','在l_orderIds中的位置，返回位置号 ------------------------get orderid value start------------------------------------------采用"select into from"为指定字符插入值，只能接受一个结果，不能接受结果集查询table判断是否有记录插入l_strs，如果没有结果，则赋值为null----------------------------------------------------------------------------------------- IF l_orderId IS NOT NULL THEN BEGIN SELECT b.coltmp1 INTO l_strs FROM tabletmp b where col=l_orderId; EXCEPTION WHEN no_data_found THEN l_strs := NULL; END; END IF; ------------------------get orderid value end-------------------------------------------- WHILE l_strs IS NOT NULL LOOP l_str := substr(l_strs, 1, noLen); l_typetmp.EXTEND(1); l_typetmp(l_typetmp.COUNT) := l_str;--往新类型创建的便利l_typetmp里放入值 l_strs := SUBSTR(l_strs, noLen+2); l_str := NULL; END LOOP; l_strs := NULL; l_orderIds := SUBSTR(l_orderIds, length(l_orderId)+2); END LOOP; RETURN l_typetmp;END;
?
