如题,需要的是简单一个查字典功能,如果查不到字典,则将当前项当字典内容录入,最后返回字典 id
总想写成function,但是里面没有commit; 就会抛异常并自动回滚。后来被迫写成带 out 参数的 procedure 。。CREATE OR REPLACE FUNCTION TRMS.f_address_getId(p_name in VARCHAR2)
RETURN number IS PRAGMA AUTONOMOUS_TRANSACTION;
v_id number := 0;
BEGIN
for v_t in (select * from RM_ADDRESS where NAME = p_name) loop
v_id := v_t.id;
exit;
end loop; if v_id = 0 then
v_id := s_seq.nextval;
insert into ADDRESS
(ID,
Name)
values
(v_id,
p_name);
--commit;
end if; RETURN v_id;
END f_address_getId;
总想写成function,但是里面没有commit; 就会抛异常并自动回滚。后来被迫写成带 out 参数的 procedure 。。CREATE OR REPLACE FUNCTION TRMS.f_address_getId(p_name in VARCHAR2)
RETURN number IS PRAGMA AUTONOMOUS_TRANSACTION;
v_id number := 0;
BEGIN
for v_t in (select * from RM_ADDRESS where NAME = p_name) loop
v_id := v_t.id;
exit;
end loop; if v_id = 0 then
v_id := s_seq.nextval;
insert into ADDRESS
(ID,
Name)
values
(v_id,
p_name);
--commit;
end if; RETURN v_id;
END f_address_getId;
insert into t values ...
commit;