Hi folks,
A user-defined function is a set of PL/SQL statements you can call by name. Functions are very similar to procedures, except that a function returns a value to the environment in which it is called.
Syntax
CREATE OR REPLACE FUNCTION <name> (<paramete1_name parameter1_type, .. ,parametern_name parametern_type>) RETURN <return type> AS --local variables(optional) BEGIN --your code END <name>;
Example 1
The following example is a function responsible to return the next available city id from the table city. It is a simple algorithm, but enough to demonstrate the usage of this feature.
You can use the following script to create and populate this table. create_and_populate_tables_city_and_country
Function
CREATE OR REPLACE FUNCTION F_GET_NEW_CITY_ID RETURN NUMBER AS v_new_id number := 0; v_is_an_used_id varchar2(1) := 'N'; BEGIN select max(nvl(id, 0)) + 1 into v_new_id from city; LOOP BEGIN select 'Y' into v_is_an_used_id from city where id = v_new_id; EXCEPTION WHEN NO_DATA_FOUND THEN v_is_an_used_id := 'N'; END; EXIT WHEN v_is_an_used_id = 'N'; v_new_id := v_new_id +1; END LOOP; RETURN v_new_id; END F_GET_NEW_CITY_ID;
How to test/execute in an anonymous block
No matter where you are executing or calling a function, you always must use a declared variable to receive the return from this function.
DECLARE v_Return NUMBER; BEGIN v_Return := F_GET_NEW_CITY_ID; DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return); END;
Result
Example 2
Function
create or replace FUNCTION F_CHECK_CITY_NAME (p_city_name IN VARCHAR2) RETURN BOOLEAN AS v_is_an_used_id varchar2(1) := 'N'; BEGIN SELECT NVL('Y', 'N') INTO v_is_an_used_id FROM CITY WHERE UPPER(name) = UPPER(p_city_name); IF v_is_an_used_id = 'Y' THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END F_CHECK_CITY_NAME;
How to test/execute in an anonymous block
DECLARE P_CITY_NAME VARCHAR2(200); v_Return BOOLEAN; BEGIN P_CITY_NAME := 'CURITIBA'; v_Return := F_CHECK_CITY_NAME( P_CITY_NAME => P_CITY_NAME ); IF (v_Return) THEN DBMS_OUTPUT.PUT_LINE('v_Return = ' || 'TRUE'); ELSE DBMS_OUTPUT.PUT_LINE('v_Return = ' || 'FALSE'); END IF; END;
Result
This post is part of a series about the most common structures in PL/SQL. You can check the rest of them in the link below.
Please write down in the comments any further questions you may have. I’ll be happy to help you.
1,668 views last month, 3 views today