在函数中有一段插入数据的代码,单独执行是好的。但是在函数中一执行到这个语句就保存,执行中断。
后来没办法,把插入数据的代码写成PROCEDURE,单独运行这个PROCEDURE也是好的,但是在函数中运行虽然不报错,但是没有效果。记录没有插入。请大家帮我看看这个是什么原因啊?
后来没办法,把插入数据的代码写成PROCEDURE,单独运行这个PROCEDURE也是好的,但是在函数中运行虽然不报错,但是没有效果。记录没有插入。请大家帮我看看这个是什么原因啊?
INSERT INTO DM
(Vendor_SeqNum,
Vendor_ID,
Vendor_Name,
ORG_ID,
Vendor_SName,
Add_Date,
Valid_Flag)
VALUES
(AI_CODE, AS_FACNO, LS_NAME, AS_DEP, LS_SHORT, SYSDATE, 1); begin
EXECUTE IMMEDIATE 'COMMIT';
end;
PROC_INSERT_FAC(AS_FACNO, AS_DEP, Result);
END;
EXECUTE IMMEDIATE 'COMMIT';??什么意思
create or replace function Fn_EXP_FAC(AS_FACNO IN varchar2,
AS_DEP IN VARCHAR2) return integer is
Result integer;
I INTEGER;begin
--如果基本资料库中已经有工厂资料了就直接返回代码
--否则插入资料,返回代码
-- -1表示出错
if as_facno is null or trim(AS_FACNO) = '' THEN
RETURN - 1;
END IF; if AS_DEP is null or trim(AS_DEP) = '' THEN
RETURN - 1;
END IF; --先找找看有没有资料
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;
IF I = 0 THEN
--插入警告,没有找到供应商的名称
proc_err_info('取供应商',
'没有找到供应商的信息',
AS_FACNO || '-' || AS_DEP,
'');
RETURN(-1);
END IF;
--没有工厂的代码,新增一个.首先取流水号
select VENDOR_SEQ.nextval into Result from dual;
BEGIN
PROC_INSERT_FAC(AS_FACNO, AS_DEP, Result);
END;
END IF; RETURN(Result);
end Fn_EXP_FAC;
AS_DEP IN VARCHAR2,
AI_CODE IN INTEGER) is
LS_NAME VARCHAR2(100);
LS_SHORT VARCHAR2(100);
begin
--按照指定的代理键插入一个供应商的基础资料
SELECT COMPANY, NVL(SHORTNAME, COMPANY)
INTO LS_NAME, LS_SHORT
FROM T_FAC
WHERE CST_NO = AS_FACNO
AND DEP_NO = AS_DEP; INSERT INTO TABLE1
(Vendor_SeqNum,
Vendor_ID,
Vendor_Name,
ORG_ID,
Vendor_SName,
Add_Date,
Valid_Flag)
VALUES
(AI_CODE, AS_FACNO, LS_NAME, AS_DEP, LS_SHORT, SYSDATE, 1); COMMIT;
EXCEPTION
--插入出错的记录
WHEN OTHERS THEN
proc_err_info('插入供应商',
SQLERRM,
AS_FACNO || '@' || AS_DEP || '@' || to_char(AI_CODE),
SQLCODE);
end PROC_INSERT_FAC;
(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>
习惯上是不是用函数做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;
ELSESELECT 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;