函数1:
create or replace
FUNCTION splitstr(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
RETURN str_split --CREATE OR REPLACE TYPE str_split IS TABLE OF VARCHAR2 (4000);写在创建函数之前
--函数功能:本函数可以将“目标字符串”以“指定字符串”进行拆分,并通过表结构返回结果
--日期:2009-10-14
PIPELINED--pipelined必须返回一个集合类型
AS
v_length NUMBER := LENGTH(p_string);
v_start NUMBER := 1;
v_index NUMBER;
BEGIN
WHILE(v_start <= v_length)
LOOP
v_index := INSTR(p_string, p_delimiter, v_start);
IF v_index = 0
THEN
PIPE ROW(SUBSTR(p_string, v_start));-- pipe row () 语句来送出要返回的表中的每一行
v_start := v_length + 1;
ELSE
PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
v_start := v_index + 1;
END IF;
END LOOP;
RETURN;
END splitstr;
函数2:
create or replace
function fn_splitData_jqzhxx(p_yearFlag IN VARCHAR2)
return temp_Table
--创建函数之前执行 :
--create or replace type Row_type as object(qyid int,qccpzl varchar2(10),qyssdy int,
--yearflag varchar2(10),nmcyry int,qnzcz int,xcpcz int,gyzjz int,gyxscz int,ckjhz int);--定义行对象
--create or replace type temp_Table as table of Row_type; --定义表对象
--参数说明:p_yearFlag 年份
--函数功能:用自定义的分割函数splitstr将每行的字符串分割,并且插入到temp_Table表中.用于集群综合信息统计
--日期:2009-10-16
PIPELINED
as
tabRow Row_type;--定义v为行对象类型
begin
for thisrow in (select jj.QYID,NVL(qccpzl,'06') qccpzl,jb.QYSSDY,yearflag,
nvl(nmcyry,0) nmcyry,nvl(qnzcz,0) qnzcz,nvl(xcpcz,0) xcpcz,
nvl(gyzjz,0) gyzjz,nvl(gyxscz,0) gyxscz,nvl(ckjhz,0) ckjhz
from QY_JBXX jb,QY_JJXX jj
where jb.QYID=jj.QYID and jj.yearflag =p_yearFlag
and (length(qccpzl)>2 or qccpzl is null)) loop
--第一层循环,查询出企业产品种类有多个的数据
for innerRow in (select * from table(splitstr(thisrow.qccpzl,','))) loop
--第二层循环,用自定义的分割函数splitstr将每行的qccpzl字符串分割,并且插入到temp_Table表中。
--比如01,02就会生成两行数据,除了产品种类不同外,其它数据都相同,这样会将一个企业的数据统计两次
tabRow:=Row_type(thisrow.QYID,innerRow.column_value,thisrow.QYSSDY,thisrow.yearflag,
thisrow.nmcyry,thisrow.qnzcz,thisrow.xcpcz,thisrow.gyzjz,thisrow.gyxscz,thisrow.ckjhz);
pipe row (tabRow);
end loop;
end loop;
return;
end;