我的一个PLSQL函数 先查询再插入数据库的函数 动态SQL拼接查询条件、通用游标、记录定义(封装部分查询字段并赋值给游标)、insert select 序列、常量【我】
先查询再插入数据库的函数
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 != '1' and l_identity_type != '2' 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;
测试时，建议去掉 异常处理，避免出现异常不提示。
