17,086
社区成员
发帖
与我相关
我的任务
分享
CREATE OR REPLACE FUNCTION F_ADD_CHAR(FIELD_NAME IN VARCHAR2)
RETURN VARCHAR2 IS
V_STR_ADD VARCHAR2(80) := '';
V_ADD_NUMBER NUMBER := 0;
V_1 VARCHAR2(80) := '';
V_LENGTH INTEGER;
BEGIN
SELECT LENGTH(FIELD_NAME) INTO V_LENGTH FROM DUAL;
FOR I IN 1 .. V_LENGTH + 1 LOOP
IF ASCII(SUBSTR(FIELD_NAME, I, 1)) = 43 OR I = V_LENGTH + 1 THEN
V_ADD_NUMBER := V_ADD_NUMBER + TO_NUMBER(V_1);
V_1 := '';
ELSE
V_1 := V_1 || SUBSTR(FIELD_NAME, I, 1);
END IF;
END LOOP;
V_STR_ADD := TO_CHAR(V_ADD_NUMBER);
RETURN V_STR_ADD;
END;
测试结果如下:
select F_ADD_CHAR('1000+1100+1200') from dual;--3300
select F_ADD_CHAR('1000+1100+1200+1000') from dual;--4300
select F_ADD_CHAR('1000+1100') from dual;--2100
select F_ADD_CHAR('1000') from dual;--1000
--给楼主做出来了,自已定义了一个f_add_char()函数:
SQL> select * from t01;
SEQ NAME SALARY
---------- -------- ------------------------------
1 AAA 1000+1100+1200
1 BBB 2000
1 CCC 1100+2000
1 DDD 2000+2200+2400+2600
SQL>
SQL> CREATE OR REPLACE FUNCTION f_add_char(field_name IN varchar2) RETURN varchar2 IS
2 v_str_add varchar2(80) :='';
3 v_add_number number :=0;
4 v_1 varchar2(80) :='';
5 begin
6 For i in 1..100
7 Loop
8 if ascii(substr(field_name,i,1))=43 or i=100 then
9 v_add_number := v_add_number + to_number(v_1);
10 v_1 :='';
11 else
12 v_1 := v_1 || substr(field_name,i,1);
13 end if;
14 End loop;
15 v_str_add :=to_char(v_add_number);
16 return v_str_add;
17 end;
18 /
Function created
SQL> select seq,name,f_add_char(salary) from t01;
SEQ NAME F_ADD_CHAR(SALARY)
---------- -------- --------------------------------------------------------------------------------
1 AAA 3300
1 BBB 2000
1 CCC 3100
1 DDD 9200
SQL>
create or replace function add_num(sql_add in varchar2) return number is
Result number;
begin
execute immediate 'select ' || sql_add || ' from dual' into Result;
return(Result);
end add_num;
select seq,name,add_num(salary) from T01;