今天写了个function,学习了一些orcale的语法,下面是修改后的简洁代码,以便以后查看。
CREATE OR REPLACE TYPE typetmp AS TABLE OF VARCHAR2(4000);
CREATE OR REPLACE FUNCTION functiontmp(l_orders IN VARCHAR2)
RETURN typetmp
IS
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;