DML function inside, transaction management in other places, have the means to achieve?-CodePudding

As title, need is a simple function, the dictionary if you do not check the dictionary, will the current item when the content of the dictionary entry, finally return the dictionary id
Always want to write the function, but there is no commit; Will throw exceptions and automatically rolled back, was forced to take out parameters of procedure, collection,

 CREATE OR REPLACE FUNCTION TRMS. F_address_getId (p_name in VARCHAR2) 
RETURN the number of IS PRAGMA AUTONOMOUS_TRANSACTION; 
V_id number:=0; 
The BEGIN 
For v_t in (select * from RM_ADDRESS where NAME=p_name) loop 
V_id:=v_t. Id; 
The exit; 
End loop; 

If v_id=0 then 
V_id:=s_seq. Nextval; 

Insert into the ADDRESS 
(ID, 
Name) 
Values 
(v_id, 
P_name); 

- commit; 
end if; 

RETURN v_id; 
END f_address_getId;

CodePudding user response:

DML function, in the call, use the following method, can achieve your requirements:

X:=fn ();
Insert into t values...
commit;