17,086
社区成员
发帖
与我相关
我的任务
分享
---你这函数中没有DML语句
create or replace function Fn_EXP_FAC(AS_FACNO IN varchar2,AS_DEP IN VARCHAR2) return integer
is
Result TABLE1.Vendor_SeqNum%type;
I INTEGER;
begin
if as_facno is null or trim(AS_FACNO) = '' or AS_DEP is null or trim(AS_DEP) = '' THEN
RETURN -1;
ELSE
SELECT COUNT(*) INTO I FROM TABLE1
WHERE Vendor_ID = AS_FACNO AND ORG_ID = AS_DEP AND Valid_Flag = 1;
IF I > 0 THEN
SELECT Vendor_SeqNum
INTO RESULT
FROM TABLE1
WHERE Vendor_ID = AS_FACNO
AND ORG_ID = AS_DEP
AND Valid_Flag = 1;
ELSE
--取供应商的名称
SELECT COUNT(*) INTO I FROM T_FAC
WHERE CST_NO = AS_FACNO AND DEP_NO = AS_DEP;
ELSIF I = 0 THEN
--插入警告,没有找到供应商的名称
proc_err_info('取供应商','没有找到供应商的信息',AS_FACNO || '-' || AS_DEP,'');
RETURN -1;
END IF;
select VENDOR_SEQ.nextval into Result from dual;
PROC_INSERT_FAC(AS_FACNO, AS_DEP, Result);
RETURN(Result);
END IF;
end Fn_EXP_FAC;
create table t_test_func_insert
(id number,
name varchar2(100));
SQL> create or replace function func_test_insert(i_str varchar2)
2 return number
3 as
4 v_num number;
5 begin
6 select count(*) into v_num from t_test_func_insert;
7 if v_num=0 then
8 insert into t_test_func_insert values(1,i_str);
9 return 1;
10 end if;
11 select max(id)+1 into v_num from t_test_func_insert;
12 insert into t_test_func_insert values(v_num,i_str);
13 return v_num;
14 end;
15 /
Function created
SQL> declare
2 v_num number;
3 begin
4 v_num:=func_test_insert(1) ;
5 end;
6 /
PL/SQL procedure successfully completed
SQL> commit;
Commit complete
SQL> select * from t_test_func_insert;
ID NAME
---------- --------------------------------------------------------------------------------
1 1
SQL>