What are best practices of using local procedures?

The obvious case for using local procedures is eliminating repetitive code. Are there any other cases? E.g., does it make sense to create a local procedure in the procedure below, to make the main procedure more scannable?

procedure GetData( pi_data in nclob, po_data out nclob, po_errors out ErrorList ) is v_session SESSION.session; v_json json; v_m_id M.id%type; v_result number(1); v_xx number(1); v_yy number(1); procedure SetTimeStamp is begin update M_PERSONS mp set mp.last_stamp = localtimestamp where mp.m_id = v_m_id and mp.employee_id = nvl(.., -..) and mp.active = 1; update MI_PERSONS mp set mp.last_stamp = localtimestamp where mp.employee_id = nvl(.., -..) and exists (select 1 from M m inner join M_ITEMS mai on mai.id = mp.m_item_id and mai.m_id = m.id and mai.m_id = v_m_id and mai.active = 1) and mp.active = 1; end; begin v_session := I_R_PCK.GetSession; v_json := U_JSON_PCK.Parse(pi_data, po_errors); if U_ERROR_PCK.HasErrors(po_errors) then return; end if; v_m_id := u_json_pck.GetIntProperty(v_json, 'id', po_errors); v_xx := u_json_pck.GetBoolProperty(v_json, 'xx', po_errors); v_yy := u_json_pck.GetBoolProperty(v_json, 'yy', po_errors); SetTimeStamp(); v_json := json(); U_JSON_PCK.SetBoolProperty(v_json, '..', ..(v_m_id)); U_JSON_PCK.SetBoolProperty(v_json, '..', ..(v_m_id)); v_json.put('..', ..(v_m_id)); if nvl(v_xx, 0) = 1 then v_json.put('..', ..(v_m_id)); U_JSON_PCK.SetBoolProperty(v_json, '..', ..(v_m_id)); end if; if nvl(v_yy, 0) = 1 then U_JSON_PCK.SetBoolProperty(v_json, '..',..(v_m_id)); U_JSON_PCK.SetBoolProperty(v_json, '..', ..(v_m_id, v_session.user_id, CONST_PCK.m_s)); end if; dbms_lob.createtemporary(po_data, true); v_json.to_clob(po_data); end; 

submitted by /u/somequestions_zz
[link] [comments]


Go to Source of this post
Author Of this post: /u/somequestions_zz
Title Of post: What are best practices of using local procedures?
Author Link: {authorlink}