oracle function小例子

epemrugtfl

　　今天写了个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;