其中TT1是表1,TT2是表2
定义mo_id ,name ,v_name ...字符长度时,是实际表中的字段定义长度
//创建类型
create or replace type myScalarType
as object (mo_id varchar2(100),name varchar2(100))
create or replace type myTableType as table of myScalarType
//分割字符
CREATE OR REPLACE PROCEDURE SPLIT_STRING_SP(P_INSTR IN VARCHAR2,--传入字符串,逗号分隔,输入参数
P_STRS OUT VARCHAR2,--截取第1个值之后剩下的字符串,输出参数
P_CURR OUT VARCHAR2, --截取到的第1个值,输出参数
P_CHAR IN VARCHAR2 --以什么字符为分隔字符
) IS
CURR_LOCA NUMBER;
BEGIN
CURR_LOCA := INSTR(P_INSTR, P_CHAR, 1, 1);
P_CURR := SUBSTR(P_INSTR, 0, CURR_LOCA - 1);
IF P_CURR IS NULL THEN
P_CURR := P_INSTR;
P_STRS := NULL;
ELSE
P_STRS := SUBSTR(P_INSTR, CURR_LOCA + 1);
END IF;
END;
//创建函数
create or replace function getallEx return myTableType Pipelined
as
TYPE c1 IS REF CURSOR;
cur_name c1;
cur_my c1;
v_mo_id varchar2(100);
v_fail_acc_unit varchar2(50);
v_new_fail_acc_unit varchar2(50);
v_name varchar2(100);
v_return varchar2(10);
v_current varchar2(10);
begin
open cur_my for 'select mo_id, fail_acc_unit from TT1';
loop
FETCH cur_my INTO v_mo_id,v_fail_acc_unit;
EXIT WHEN cur_my%NOTFOUND;
--循环AA;BB;CC字段
while v_fail_acc_unit IS not NULL
loop
--EXIT WHEN v_fail_acc_unit IS NULL;
SPLIT_STRING_SP(v_fail_acc_unit, v_return, v_current, ';');
--查询name
open cur_name for 'select name from TT2 where party_id = ''' || v_current || '''';
loop
FETCH cur_name INTO v_name;
EXIT WHEN cur_name%NOTFOUND;
end loop;
close cur_name;
PIPE Row(myScalarType(v_mo_id,v_name));
v_fail_acc_unit := v_return;
end loop;
DBMS_OUTPUT.PUT_LINE(v_mo_id);
DBMS_OUTPUT.PUT_LINE(v_new_fail_acc_unit);
v_new_fail_acc_unit := null;
end loop;
close cur_my;
return;
end;
//C++调用
select * from table(getallex())