CREATE OR REPLACE
FUNCTION GETF
(
FORMULA IN VARCHAR2, -- 定义输入函数,此处为公式列类型,如果需要其他参数,请在此处以“,” 逗号分隔
TIME1 IN VARCHAR2,
--TIME2 IN VARCHAR2,
CAREERID IN VARCHAR2
) RETURN NUMBER AS
TYPE REF_TYPE IS REF CURSOR; -- 定义游标类型
REFCUR REF_TYPE; -- 定义游标,用于循环结果并进行数据处理
P_FIELD VARCHAR2(2000); -- 存储公式处理运算符号后的表达式
P_FIELD1 VARCHAR2(2000); -- 存储计算公式处理后的字符串
P_FIELD2 VARCHAR2(2000); -- 接收拆分后的字符
P_SUBJECTVALE NUMBER(20,4); -- 存储原始表数据
P_SUBJECTVALE2 NUMBER(20,4); -- 存储计算结果
L_SQL VARCHAR2(1000); -- sql变量,用于动态构造sql语句
L_SQL2 VARCHAR2(1000); -- sql变量,用于动态构造sql语句
F_TIME1 VARCHAR2(10);
--F_TIME2 VARCHAR2(10);
F_CAREERID VARCHAR2(32) ;
ISBEGIN INT;
ISEND INT;
ISTRUE INT;
TEMP VARCHAR2(2000);
BEGIN
F_TIME1 := TIME1;
--F_TIME2 := TIME2;
F_CAREERID := CAREERID;
P_FIELD1 := FORMULA; -- 接收输入参数,用于后面运算
P_FIELD := REGEXP_REPLACE(FORMULA, '[\(\)\+\*\/-]', '/'); -- 将公式中数学运算符替换为“/”
OPEN REFCUR FOR SELECT * FROM TABLE(SPLIT(P_FIELD,'/')) WHERE COLUMN_VALUE IS NOT NULL; -- 用“/”作为分隔符,将公式中每个单元拆分后放入游标
LOOP -- 循环开始
FETCH REFCUR INTO P_FIELD2; -- 将结果放入变量中
EXIT WHEN REFCUR%NOTFOUND; -- 退出循环条件
ISBEGIN := INSTR(P_FIELD2, '@YearBegin');
ISEND := INSTR(P_FIELD2, '@YearEnd');
TEMP := REPLACE(REPLACE(REPLACE(P_FIELD2, '@YearEnd', ''), '@YearBegin', ''), 'R.', '');
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
IF F_CAREERID IS NULL OR F_CAREERID = '' THEN --判断是否存在用户ID
IF ISBEGIN > 0 THEN --判断是否存在期初数标识符
L_SQL2 := 'SELECT NVL(SUM(V_ZH_DATA.SUBJECTVALUE), 0) SUBJECTVALUE FROM V_ZH_DATA WHERE RFNUMBER = SUBSTR(''' || TEMP || ''', 1, INSTR(''' || TEMP || ''', ''.'') - 1)
AND V_ZH_DATA.PROSUBFLAG = SUBSTR(''' || TEMP || ''', INSTR(''' || TEMP || ''', ''.'') + 1)
AND TO_CHAR(V_ZH_DATA.PERIODDATE, ''YYYYMM'') = SUBSTR(''' || F_TIME1 || ''', 1, 4) || ''01''';
ELSE
IF ISEND > 0 THEN --判断是否存在期末数标识符
L_SQL2 := 'SELECT ROWNUM FROM V_ZH_DATA WHERE RFNUMBER = SUBSTR(''' || TEMP || ''', 1, INSTR(''' || TEMP || ''', ''.'') - 1)
AND V_ZH_DATA.PROSUBFLAG = SUBSTR(''' || TEMP || ''', INSTR(''' || TEMP || ''', ''.'') + 1)
AND TO_CHAR(V_ZH_DATA.PERIODDATE, ''YYYYMM'') = SUBSTR(''' || F_TIME1 || ''', 1, 4) || ''12''';
EXECUTE IMMEDIATE L_SQL2 INTO ISTRUE;
IF ISTRUE > 0 THEN --判断是否存在期末数据
L_SQL2 := 'SELECT NVL(SUM(V_ZH_DATA.SUBJECTVALUE), 0) SUBJECTVALUE FROM V_ZH_DATA WHERE RFNUMBER = SUBSTR(''' || TEMP || ''', 1, INSTR(''' || TEMP || ''', ''.'') - 1)
AND V_ZH_DATA.PROSUBFLAG = SUBSTR(''' || TEMP || ''', INSTR(''' || TEMP || ''', ''.'') + 1)
AND TO_CHAR(V_ZH_DATA.PERIODDATE, ''YYYYMM'') = SUBSTR(''' || F_TIME1 || ''', 1, 4) || ''12''';
ELSE--表示不存在12月份数据
L_SQL2 := 'SELECT NVL(SUM(V_ZH_DATA.SUBJECTVALUE), 0) SUBJECTVALUE FROM V_ZH_DATA WHERE RFNUMBER = SUBSTR(''' || TEMP || ''', 1, INSTR(''' || TEMP || ''', ''.'') - 1)
AND V_ZH_DATA.PROSUBFLAG = SUBSTR(''' || TEMP || ''', INSTR(''' || TEMP || ''', ''.'') + 1)
AND TO_CHAR(V_ZH_DATA.PERIODDATE, ''YYYYMM'') = SUBSTR(''' || F_TIME1 || ''', 1, 4) || SUBSTR(TO_DATE(''' || F_TIME1 || ''', ''YYYY-MM-DD''), 6, 7)';
END IF;
ELSE
L_SQL2 := 'SELECT NVL(SUM(V_ZH_DATA.SUBJECTVALuE), 0) SUBJECTVALuE FROM V_ZH_DATA WHERE V_ZH_DATA.RFNUMBER = SUBSTR(''' || TEMP || ''', 1, INSTR(''' || TEMP || ''', ''.'') - 1)
AND V_ZH_DATA.PROSUBFLAG = SUBSTR(''' || TEMP || ''', INSTR(''' || TEMP || ''', ''.'') + 1)
AND V_ZH_DATA.PERIODDATE = TO_DATE(''' || F_TIME1 || ''', ''YYYY-MM-DD'')';
END IF;
END IF;
/******************分割处理**********************/
ELSE
IF ISBEGIN > 0 THEN --判断是否存在期初数标识符
L_SQL2 := 'SELECT NVL(SUM(V_ZH_DATA.SUBJECTVALUE), 0) SUBJECTVALUE FROM V_ZH_DATA WHERE RFNUMBER = SUBSTR(''' || TEMP || ''', 1, INSTR(''' || TEMP || ''', ''.'') - 1)
AND V_ZH_DATA.PROSUBFLAG = SUBSTR(''' || TEMP || ''', INSTR(''' || TEMP || ''', ''.'') + 1)
AND V_ZH_DATA.CAREERID = ''' || F_CAREERID || '''
AND TO_CHAR(V_ZH_DATA.PERIODDATE, ''YYYYMM'') = SUBSTR(''' || F_TIME1 || ''', 1, 4) || ''01''';
ELSE
IF ISEND > 0 THEN --判断是否存在期末数标识符
L_SQL2 := 'SELECT ROWNUM FROM V_ZH_DATA WHERE RFNUMBER = SUBSTR(''' || TEMP || ''', 1, INSTR(''' || TEMP || ''', ''.'') - 1)
AND V_ZH_DATA.PROSUBFLAG = SUBSTR(''' || TEMP || ''', INSTR(''' || TEMP || ''', ''.'') + 1)
AND V_ZH_DATA.CAREERID = ''' || F_CAREERID || '''
AND TO_CHAR(V_ZH_DATA.PERIODDATE, ''YYYYMM'') = SUBSTR(''' || F_TIME1 || ''', 1, 4) || ''12''';
EXECUTE IMMEDIATE L_SQL2 INTO ISTRUE;
IF ISTRUE > 0 THEN --判断是否存在期末数据
L_SQL2 := 'SELECT NVL(SUM(V_ZH_DATA.SUBJECTVALUE), 0) SUBJECTVALUE FROM V_ZH_DATA WHERE RFNUMBER = SUBSTR(''' || TEMP || ''', 1, INSTR(''' || TEMP || ''', ''.'') - 1)
AND V_ZH_DATA.PROSUBFLAG = SUBSTR(''' || TEMP || ''', INSTR(''' || TEMP || ''', ''.'') + 1)
AND V_ZH_DATA.CAREERID = ''' || F_CAREERID || '''
AND TO_CHAR(V_ZH_DATA.PERIODDATE, ''YYYYMM'') = SUBSTR(''' || F_TIME1 || ''', 1, 4) || ''12''';
ELSE --表示不存在12月份数据
L_SQL2 := 'SELECT NVL(SUM(V_ZH_DATA.SUBJECTVALUE), 0) SUBJECTVALUE FROM V_ZH_DATA WHERE RFNUMBER = SUBSTR(''' || TEMP || ''', 1, INSTR(''' || TEMP || ''', ''.'') - 1)
AND V_ZH_DATA.PROSUBFLAG = SUBSTR(''' || TEMP || ''', INSTR(''' || TEMP || ''', ''.'') + 1)
AND V_ZH_DATA.CAREERID = ''' || F_CAREERID || '''
AND TO_CHAR(V_ZH_DATA.PERIODDATE, ''YYYYMM'') = SUBSTR(''' || F_TIME1 || ''', 1, 4) || SUBSTR(TO_DATE(''' || F_TIME1 || ''', ''YYYY-MM-DD''), 6, 7)';
END IF;
ELSE
L_SQL2 := 'SELECT NVL(SUM(V_ZH_DATA.SUBJECTVALuE), 0) SUBJECTVALuE FROM V_ZH_DATA WHERE V_ZH_DATA.RFNUMBER = SUBSTR(''' || TEMP || ''', 1, INSTR(''' || TEMP || ''', ''.'') - 1)
AND V_ZH_DATA.PROSUBFLAG = SUBSTR(''' || TEMP || ''', INSTR(''' || TEMP || ''', ''.'') + 1)
AND V_ZH_DATA.CAREERID = ''' || F_CAREERID || '''
AND V_ZH_DATA.PERIODDATE =TO_DATE(''' || F_TIME1 || ''', ''YYYY-MM-DD'')';
END IF;
END IF;
END IF;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
EXECUTE IMMEDIATE L_SQL2 INTO P_SUBJECTVALE;
P_FIELD1 := REPLACE(P_FIELD1, P_FIELD2, P_SUBJECTVALE); -- 用数据替换公式
END LOOP; -- 结束循环
CLOSE REFCUR; -- 关闭游标
IF INSTR(P_FIELD1, '/0') > 0 then -- 判断表达式中是否存在除数为0,如果有则返回0,否则执行数值运算
RETURN 0;
ELSE
L_SQL := 'SELECT TO_NUMBER(' || P_FIELD1 || ') FROM DUAL'; -- 动态构造sql
EXECUTE IMMEDIATE L_SQL INTO P_SUBJECTVALE2; -- 执行构造后的sql并将结果放入变量 P_SUBJECTVALE2 中
RETURN P_SUBJECTVALE2; -- 返回结果
END IF;
EXCEPTION
when zero_divide then
P_SUBJECTVALE2 := 0;
RETURN P_SUBJECTVALE2;
END GETF;
/
FUNCTION GETF
(
FORMULA IN VARCHAR2, -- 定义输入函数,此处为公式列类型,如果需要其他参数,请在此处以“,” 逗号分隔
TIME1 IN VARCHAR2,
--TIME2 IN VARCHAR2,
CAREERID IN VARCHAR2
) RETURN NUMBER AS
TYPE REF_TYPE IS REF CURSOR; -- 定义游标类型
REFCUR REF_TYPE; -- 定义游标,用于循环结果并进行数据处理
P_FIELD VARCHAR2(2000); -- 存储公式处理运算符号后的表达式
P_FIELD1 VARCHAR2(2000); -- 存储计算公式处理后的字符串
P_FIELD2 VARCHAR2(2000); -- 接收拆分后的字符
P_SUBJECTVALE NUMBER(20,4); -- 存储原始表数据
P_SUBJECTVALE2 NUMBER(20,4); -- 存储计算结果
L_SQL VARCHAR2(1000); -- sql变量,用于动态构造sql语句
L_SQL2 VARCHAR2(1000); -- sql变量,用于动态构造sql语句
F_TIME1 VARCHAR2(10);
--F_TIME2 VARCHAR2(10);
F_CAREERID VARCHAR2(32) ;
ISBEGIN INT;
ISEND INT;
ISTRUE INT;
TEMP VARCHAR2(2000);
BEGIN
F_TIME1 := TIME1;
--F_TIME2 := TIME2;
F_CAREERID := CAREERID;
P_FIELD1 := FORMULA; -- 接收输入参数,用于后面运算
P_FIELD := REGEXP_REPLACE(FORMULA, '[\(\)\+\*\/-]', '/'); -- 将公式中数学运算符替换为“/”
OPEN REFCUR FOR SELECT * FROM TABLE(SPLIT(P_FIELD,'/')) WHERE COLUMN_VALUE IS NOT NULL; -- 用“/”作为分隔符,将公式中每个单元拆分后放入游标
LOOP -- 循环开始
FETCH REFCUR INTO P_FIELD2; -- 将结果放入变量中
EXIT WHEN REFCUR%NOTFOUND; -- 退出循环条件
ISBEGIN := INSTR(P_FIELD2, '@YearBegin');
ISEND := INSTR(P_FIELD2, '@YearEnd');
TEMP := REPLACE(REPLACE(REPLACE(P_FIELD2, '@YearEnd', ''), '@YearBegin', ''), 'R.', '');
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
IF F_CAREERID IS NULL OR F_CAREERID = '' THEN --判断是否存在用户ID
IF ISBEGIN > 0 THEN --判断是否存在期初数标识符
L_SQL2 := 'SELECT NVL(SUM(V_ZH_DATA.SUBJECTVALUE), 0) SUBJECTVALUE FROM V_ZH_DATA WHERE RFNUMBER = SUBSTR(''' || TEMP || ''', 1, INSTR(''' || TEMP || ''', ''.'') - 1)
AND V_ZH_DATA.PROSUBFLAG = SUBSTR(''' || TEMP || ''', INSTR(''' || TEMP || ''', ''.'') + 1)
AND TO_CHAR(V_ZH_DATA.PERIODDATE, ''YYYYMM'') = SUBSTR(''' || F_TIME1 || ''', 1, 4) || ''01''';
ELSE
IF ISEND > 0 THEN --判断是否存在期末数标识符
L_SQL2 := 'SELECT ROWNUM FROM V_ZH_DATA WHERE RFNUMBER = SUBSTR(''' || TEMP || ''', 1, INSTR(''' || TEMP || ''', ''.'') - 1)
AND V_ZH_DATA.PROSUBFLAG = SUBSTR(''' || TEMP || ''', INSTR(''' || TEMP || ''', ''.'') + 1)
AND TO_CHAR(V_ZH_DATA.PERIODDATE, ''YYYYMM'') = SUBSTR(''' || F_TIME1 || ''', 1, 4) || ''12''';
EXECUTE IMMEDIATE L_SQL2 INTO ISTRUE;
IF ISTRUE > 0 THEN --判断是否存在期末数据
L_SQL2 := 'SELECT NVL(SUM(V_ZH_DATA.SUBJECTVALUE), 0) SUBJECTVALUE FROM V_ZH_DATA WHERE RFNUMBER = SUBSTR(''' || TEMP || ''', 1, INSTR(''' || TEMP || ''', ''.'') - 1)
AND V_ZH_DATA.PROSUBFLAG = SUBSTR(''' || TEMP || ''', INSTR(''' || TEMP || ''', ''.'') + 1)
AND TO_CHAR(V_ZH_DATA.PERIODDATE, ''YYYYMM'') = SUBSTR(''' || F_TIME1 || ''', 1, 4) || ''12''';
ELSE--表示不存在12月份数据
L_SQL2 := 'SELECT NVL(SUM(V_ZH_DATA.SUBJECTVALUE), 0) SUBJECTVALUE FROM V_ZH_DATA WHERE RFNUMBER = SUBSTR(''' || TEMP || ''', 1, INSTR(''' || TEMP || ''', ''.'') - 1)
AND V_ZH_DATA.PROSUBFLAG = SUBSTR(''' || TEMP || ''', INSTR(''' || TEMP || ''', ''.'') + 1)
AND TO_CHAR(V_ZH_DATA.PERIODDATE, ''YYYYMM'') = SUBSTR(''' || F_TIME1 || ''', 1, 4) || SUBSTR(TO_DATE(''' || F_TIME1 || ''', ''YYYY-MM-DD''), 6, 7)';
END IF;
ELSE
L_SQL2 := 'SELECT NVL(SUM(V_ZH_DATA.SUBJECTVALuE), 0) SUBJECTVALuE FROM V_ZH_DATA WHERE V_ZH_DATA.RFNUMBER = SUBSTR(''' || TEMP || ''', 1, INSTR(''' || TEMP || ''', ''.'') - 1)
AND V_ZH_DATA.PROSUBFLAG = SUBSTR(''' || TEMP || ''', INSTR(''' || TEMP || ''', ''.'') + 1)
AND V_ZH_DATA.PERIODDATE = TO_DATE(''' || F_TIME1 || ''', ''YYYY-MM-DD'')';
END IF;
END IF;
/******************分割处理**********************/
ELSE
IF ISBEGIN > 0 THEN --判断是否存在期初数标识符
L_SQL2 := 'SELECT NVL(SUM(V_ZH_DATA.SUBJECTVALUE), 0) SUBJECTVALUE FROM V_ZH_DATA WHERE RFNUMBER = SUBSTR(''' || TEMP || ''', 1, INSTR(''' || TEMP || ''', ''.'') - 1)
AND V_ZH_DATA.PROSUBFLAG = SUBSTR(''' || TEMP || ''', INSTR(''' || TEMP || ''', ''.'') + 1)
AND V_ZH_DATA.CAREERID = ''' || F_CAREERID || '''
AND TO_CHAR(V_ZH_DATA.PERIODDATE, ''YYYYMM'') = SUBSTR(''' || F_TIME1 || ''', 1, 4) || ''01''';
ELSE
IF ISEND > 0 THEN --判断是否存在期末数标识符
L_SQL2 := 'SELECT ROWNUM FROM V_ZH_DATA WHERE RFNUMBER = SUBSTR(''' || TEMP || ''', 1, INSTR(''' || TEMP || ''', ''.'') - 1)
AND V_ZH_DATA.PROSUBFLAG = SUBSTR(''' || TEMP || ''', INSTR(''' || TEMP || ''', ''.'') + 1)
AND V_ZH_DATA.CAREERID = ''' || F_CAREERID || '''
AND TO_CHAR(V_ZH_DATA.PERIODDATE, ''YYYYMM'') = SUBSTR(''' || F_TIME1 || ''', 1, 4) || ''12''';
EXECUTE IMMEDIATE L_SQL2 INTO ISTRUE;
IF ISTRUE > 0 THEN --判断是否存在期末数据
L_SQL2 := 'SELECT NVL(SUM(V_ZH_DATA.SUBJECTVALUE), 0) SUBJECTVALUE FROM V_ZH_DATA WHERE RFNUMBER = SUBSTR(''' || TEMP || ''', 1, INSTR(''' || TEMP || ''', ''.'') - 1)
AND V_ZH_DATA.PROSUBFLAG = SUBSTR(''' || TEMP || ''', INSTR(''' || TEMP || ''', ''.'') + 1)
AND V_ZH_DATA.CAREERID = ''' || F_CAREERID || '''
AND TO_CHAR(V_ZH_DATA.PERIODDATE, ''YYYYMM'') = SUBSTR(''' || F_TIME1 || ''', 1, 4) || ''12''';
ELSE --表示不存在12月份数据
L_SQL2 := 'SELECT NVL(SUM(V_ZH_DATA.SUBJECTVALUE), 0) SUBJECTVALUE FROM V_ZH_DATA WHERE RFNUMBER = SUBSTR(''' || TEMP || ''', 1, INSTR(''' || TEMP || ''', ''.'') - 1)
AND V_ZH_DATA.PROSUBFLAG = SUBSTR(''' || TEMP || ''', INSTR(''' || TEMP || ''', ''.'') + 1)
AND V_ZH_DATA.CAREERID = ''' || F_CAREERID || '''
AND TO_CHAR(V_ZH_DATA.PERIODDATE, ''YYYYMM'') = SUBSTR(''' || F_TIME1 || ''', 1, 4) || SUBSTR(TO_DATE(''' || F_TIME1 || ''', ''YYYY-MM-DD''), 6, 7)';
END IF;
ELSE
L_SQL2 := 'SELECT NVL(SUM(V_ZH_DATA.SUBJECTVALuE), 0) SUBJECTVALuE FROM V_ZH_DATA WHERE V_ZH_DATA.RFNUMBER = SUBSTR(''' || TEMP || ''', 1, INSTR(''' || TEMP || ''', ''.'') - 1)
AND V_ZH_DATA.PROSUBFLAG = SUBSTR(''' || TEMP || ''', INSTR(''' || TEMP || ''', ''.'') + 1)
AND V_ZH_DATA.CAREERID = ''' || F_CAREERID || '''
AND V_ZH_DATA.PERIODDATE =TO_DATE(''' || F_TIME1 || ''', ''YYYY-MM-DD'')';
END IF;
END IF;
END IF;
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
EXECUTE IMMEDIATE L_SQL2 INTO P_SUBJECTVALE;
P_FIELD1 := REPLACE(P_FIELD1, P_FIELD2, P_SUBJECTVALE); -- 用数据替换公式
END LOOP; -- 结束循环
CLOSE REFCUR; -- 关闭游标
IF INSTR(P_FIELD1, '/0') > 0 then -- 判断表达式中是否存在除数为0,如果有则返回0,否则执行数值运算
RETURN 0;
ELSE
L_SQL := 'SELECT TO_NUMBER(' || P_FIELD1 || ') FROM DUAL'; -- 动态构造sql
EXECUTE IMMEDIATE L_SQL INTO P_SUBJECTVALE2; -- 执行构造后的sql并将结果放入变量 P_SUBJECTVALE2 中
RETURN P_SUBJECTVALE2; -- 返回结果
END IF;
EXCEPTION
when zero_divide then
P_SUBJECTVALE2 := 0;
RETURN P_SUBJECTVALE2;
END GETF;
/
||相当于连接字符串的'+'还有很多不同的语法 自己去查下联机丛书嘛
http://topic.csdn.net/u/20111018/16/4829667f-2ccf-4f1c-92c2-af8aa8ff6dde.html