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;