先查询再插入数据库的函数
CREATE OR REPLACE FUNCTION F_REVENUE_SI(l_p_cd in Varchar2,
l_c_cd in Varchar2,
l_prod_type in Varchar2,
l_identity_type in Varchar2,
l_industry_type_id in Varchar2, l_p_id in Varchar2,
l_c_id in Varchar2,
l_region_name in Varchar2,
l_industry_code in Varchar2,
l_industry_name in Varchar2,
l_par_industry_id in Varchar2,
l_industry_grade in Varchar2 ) return varchar2 IS
/*
DECLARE
l_p_cd Varchar2(1000) := 8350000;
l_c_cd Varchar2(1000) := 8350102;
l_prod_type Varchar2(1000) := 10;
l_identity_type Varchar2(1000) := 2;
l_industry_type_id Varchar2(1000) := 2; l_p_id Varchar2(1000) := 1;
l_c_id Varchar2(1000) := 1;
l_region_name Varchar2(1000) := 1;
l_industry_code Varchar2(1000) := 1;
l_industry_name Varchar2(1000) := 1;
l_par_industry_id Varchar2(1000) := 1;
l_industry_grade Varchar2(1000) := 1;
*/
--定义变量接收参数，以便对参数进行修改
ll_identity_type varchar2(1000) := l_identity_type;
ll_industry_type_id varchar2(1000) := l_industry_type_id; --函数返回值
return_val varchar2(8000) := '开始';
--通用游标
cv SYS_REFCURSOR;
--创建记录用于封装部分查询结果
TYPE revenue_type IS RECORD(
r1 REVENUE_STATISTICS_RES.Audit_Cust_Num%TYPE,
r2 REVENUE_STATISTICS_RES.AUDIT_CUST_PARTY_NUM%TYPE, t1 REVENUE_STATISTICS_RES.TY_1%TYPE,
t2 REVENUE_STATISTICS_RES.TY_1%TYPE,
t3 REVENUE_STATISTICS_RES.TY_1%TYPE,
t4 REVENUE_STATISTICS_RES.TY_1%TYPE,
t5 REVENUE_STATISTICS_RES.TY_1%TYPE,
t6 REVENUE_STATISTICS_RES.TY_1%TYPE,
t7 REVENUE_STATISTICS_RES.TY_1%TYPE,
t8 REVENUE_STATISTICS_RES.TY_1%TYPE,
t9 REVENUE_STATISTICS_RES.TY_1%TYPE,
t10 REVENUE_STATISTICS_RES.TY_1%TYPE,
t11 REVENUE_STATISTICS_RES.TY_1%TYPE,
t12 REVENUE_STATISTICS_RES.TY_1%TYPE, y1 REVENUE_STATISTICS_RES.TY_1%TYPE,
y2 REVENUE_STATISTICS_RES.TY_1%TYPE,
y3 REVENUE_STATISTICS_RES.TY_1%TYPE,
y4 REVENUE_STATISTICS_RES.TY_1%TYPE,
y5 REVENUE_STATISTICS_RES.TY_1%TYPE,
y6 REVENUE_STATISTICS_RES.TY_1%TYPE,
y7 REVENUE_STATISTICS_RES.TY_1%TYPE,
y8 REVENUE_STATISTICS_RES.TY_1%TYPE,
y9 REVENUE_STATISTICS_RES.TY_1%TYPE,
y10 REVENUE_STATISTICS_RES.TY_1%TYPE,
y11 REVENUE_STATISTICS_RES.TY_1%TYPE,
y12 REVENUE_STATISTICS_RES.TY_1%TYPE);
--声明变量为自定义记录类型
revenue_rec revenue_type;
--最终查询sql
s_all varchar2(3000) := ''; --基本SQL（注意 UPPER(m.PARTY_ID) != ''NULL'' 中字符串NULL要用4个单引号，否则执行不报错但查询结果是错的）
s1 varchar2(1000) := 'SELECT /*+PARALLEL(12)*/
COUNT(e.CUST_ID) AUDIT_CUST_NUM, COUNT(DISTINCT(m.PARTY_ID)) AUDIT_CUST_PARTY_NUM,
SUM(TY_1) TY1 ,SUM(TY_2) TY2,SUM(TY_3) TY3,SUM(TY_4) TY4,SUM(TY_5) TY5,SUM(TY_6) TY6,SUM(TY_7) TY7,SUM(TY_8) TY8,SUM(TY_9) TY9,SUM(TY_10) TY10,SUM(TY_11) TY11,SUM(TY_12) TY12,
SUM(LY_1) LY1 ,SUM(LY_2) LY2,SUM(LY_3) LY3,SUM(LY_4) LY4,SUM(LY_5) LY5,SUM(LY_6) LY6,SUM(LY_7) LY7,SUM(LY_8) LY8,SUM(LY_9) LY9,SUM(LY_10) LY10,SUM(LY_11) LY11,SUM(LY_12) LY12
FROM EDA_CUST_INC e, CUST_CP_MERGE m
WHERE
e.CUST_ID = m.CUST_ID
AND e.STD_LATN_CD = m.STD_LATN_CD
and m.PARTY_ID IS NOT NULL
AND UPPER(m.PARTY_ID) != ''NULL''
AND e.STD_PRVNCE_CD = ' || l_p_cd ||
' AND e.STD_LATN_CD = ' || l_c_cd; --产品类型
s_prod_type varchar2(1000) := ' AND e.PROD_TYPE =' || l_prod_type; --身份证类型
s_identity varchar2(1000) := ' AND EXISTS (SELECT 1
FROM PARTY P
WHERE m.PARTY_ID = p.PARTY_ID
AND p.IDENTITY_TYPE = ' ||
l_identity_type || ')'; --行业类型
s_industry varchar2(1000) := ' and exists (select 1
from party_org po
where m.party_id = po.party_id
and po.INDUSTRY_TYPE_ID = ' ||
l_industry_type_id || ')'; BEGIN
--处理产品类型参数
/*
IF l_prod_type is null
THEN
s_prod_type := ' AND e.PROD_TYPE is null';
END IF;
*/
--处理身份证类型参数
IF l_identity_type = '-1' THEN
s_identity := ' AND not EXISTS (SELECT 1
FROM PARTY P
WHERE m.PARTY_ID = p.PARTY_ID)';
ELSIF l_identity_type = '-2' THEN
s_identity := ' AND EXISTS (SELECT 1
FROM PARTY P
WHERE m.PARTY_ID = p.PARTY_ID
AND p.IDENTITY_TYPE is null)';
END IF; --处理行业类型参数
IF l_identity_type = '-1' THEN
s_industry := ' and not exists (select 1
from party_org po
where m.party_id = po.party_id)';
ELSIF l_identity_type = '-2' THEN
s_industry := ' and exists (select 1
from party_org po
where m.party_id = po.party_id
and po.INDUSTRY_TYPE_ID is null)';
END IF; --拼接最终查询sql
s_all := s1 || s_prod_type || s_identity || s_industry;
DBMS_OUTPUT.PUT_LINE(s_all);
return_val := '查询sql:' || s_all;
--打开通用游标
OPEN cv FOR s_all;
--将游标赋值给变量
FETCH cv
INTO revenue_rec;
--关闭游标
CLOSE cv; --给本地变量赋值以便后面插入时使用（适用于对传入参数转换后使用）
IF l_identity_type != '' and l_identity_type != '' THEN
ll_identity_type := null;
END IF; IF l_industry_type_id = '-1' or l_industry_type_id = '-2' THEN
ll_industry_type_id := null;
END IF; --执行插入
insert into REVENUE_STATISTICS_RES
(ID,
AUDIT_CUST_NUM,
AUDIT_CUST_PARTY_NUM,
TY_1,
TY_2,
TY_3,
TY_4,
TY_5,
TY_6,
TY_7,
TY_8,
TY_9,
TY_10,
TY_11,
TY_12,
LY_1,
LY_2,
LY_3,
LY_4,
LY_5,
LY_6,
LY_7,
LY_8,
LY_9,
LY_10,
LY_11,
LY_12, PROVINCE_REGION_ID,
CITY_REGION_ID,
REGION_NAME, IDENTITY_TYPE,
PROD_TYPE, INDUSTRY_TYPE_ID,
INDUSTRY_TYPE_CODE,
INDUSTRY_TYPE_NAME,
PAR_INDUSTRY_TYPE_ID,
INDUSTRY_TYPE_GRADE, IN_DATE)
select SEQ_REVENUE_STATISTICS_RES.NEXTVAL, f.*
from (SELECT revenue_rec.r1,
revenue_rec.r2, revenue_rec.t1,
revenue_rec.t2,
revenue_rec.t3,
revenue_rec.t4,
revenue_rec.t5,
revenue_rec.t6,
revenue_rec.t7,
revenue_rec.t8,
revenue_rec.t9,
revenue_rec.t10,
revenue_rec.t11,
revenue_rec.t12, revenue_rec.y1,
revenue_rec.y2,
revenue_rec.y3,
revenue_rec.y4,
revenue_rec.y5,
revenue_rec.y6,
revenue_rec.y7,
revenue_rec.y8,
revenue_rec.y9,
revenue_rec.y10,
revenue_rec.y11,
revenue_rec.y12, l_p_id,
l_c_id,
l_region_name, ll_identity_type,
l_prod_type, ll_industry_type_id,
l_industry_code,
l_industry_name,
l_par_industry_id,
l_industry_grade, sysdate
from dual) f;
COMMIT;
return_val := '插入完成';
return return_val; EXCEPTION
WHEN OTHERS THEN
return_val := return_val || '异常编码：' || SQLCODE || ' 异常信息：' || SQLERRM;
--return return_val;
DBMS_OUTPUT.PUT_LINE(return_val);
--抛出异常
RAISE;
END;
测试时，建议去掉 异常处理，避免出现异常不提示。