PL/SQL: Local function definition in a public procedure / function
Occasionally, we will need to define local used functions in a public procedure or a function. Fortunately, PL/SQL supports embedded functions. I spent more than an hour trying to find the correct way to use this feature. Yes. forgive me, I am a little dumb. According to my retries, the best position to put your local functions is sharp before the BEGIN symbol. And here's a sample.
set serveroutput on;
CREATE OR REPLACE PROCEDURE tmp12345(v_full_name IN VARCHAR2) AS
localv varchar2(100);
-- local function definitaions start ---
FUNCTION FETCH_REAL_NAME(v_statement_name IN VARCHAR2) RETURN VARCHAR2
AS
pattern VARCHAR2(100) := '^(Dr|Ms|Mx|Mr|Mrs)\s+';
full_name varchar2(100);
BEGIN
-- replace possible multiple spaces to one space
full_name := TRIM(REGEXP_REPLACE(v_statement_name, '\s+', ' '));
IF (REGEXP_LIKE(full_name, pattern, 'i')) THEN
-- REGEXP_REPLACE(string, pattern [, replacement_string [, start_position [, nth_appearance [, match_parameter ] ] ] ])
RETURN REGEXP_REPLACE(full_name, pattern, '', 1, 0, 'i');
ELSE
RETURN full_name;
END IF;
END;
FUNCTION FETCH_MIDDLE_NAME(full_name IN VARCHAR) RETURN VARCHAR AS
middle_name VARCHAR2(100);
BEGIN
middle_name := SUBSTR(full_name, INSTR(full_name, ' ') + 1, INSTR(full_name, ' ', 1, 2) - INSTR(full_name, ' ') - 1);
IF (LENGTH(middle_name) > 10) THEN
-- return initial instead
RETURN SUBSTR(middle_name, 1, 1);
ELSE
RETURN middle_name;
END IF;
END;
-- local functions definitaions end ---
BEGIN
-- main logic here
localv := v_full_name;
DBMS_OUTPUT.PUT_LINE(localv);
DBMS_OUTPUT.PUT_LINE(FETCH_MIDDLE_NAME(FETCH_REAL_NAME(v_full_name)));
END;
/
