Test Your Knowledge of Oracle DB Functions with This Quiz

Progress

True / False

In ORACLE, a function is a named PL/SQL block that returns a single value.

True False

A function in ORACLE is a named PL/SQL block designed to perform a specific task and return a single value.

True / False

In ORACLE, functions can be used in SQL statements, such as SELECT or WHERE clauses.

True False

Functions can be used in SQL statements to perform calculations and return values within SELECT, WHERE, and other clauses.

True / False

In ORACLE, functions cannot accept parameters.

True False

Functions can accept parameters, allowing them to perform operations based on the provided input values.

True / False

In ORACLE, a function must include a RETURN clause that specifies the data type of the returned value.

True False

A function must specify a return data type using the RETURN clause and include a RETURN statement to return a value of that type.

True / False

In ORACLE, functions can only perform read operations and cannot modify database data.

True False

While functions are generally used for calculations and returning values, they can modify database data, though it is not a recommended practice due to potential side effects.

True / False

In ORACLE, functions can be used to encapsulate reusable logic and improve code modularity.

True False

Functions help encapsulate reusable logic, promoting code reuse and modularity.

True / False

In ORACLE, a function can be part of a package to group related functions together.

True False

Functions can be included in packages, which group related procedures, functions, and other PL/SQL constructs together for better organization.

True / False

In ORACLE, a function can be overloaded, meaning multiple functions can have the same name but different parameters.

True False

Function overloading allows multiple functions to have the same name as long as their parameter lists differ, enhancing flexibility and usability.

True / False

In ORACLE, a function that modifies data must be declared with the PRAGMA AUTONOMOUS_TRANSACTION directive to avoid affecting the calling transaction.

True False

The PRAGMA AUTONOMOUS_TRANSACTION directive allows a function to run in its own transaction, ensuring that any data modifications do not affect the calling transaction.

Q10

True / False

In ORACLE, a function used in a SELECT statement should not perform DML operations to ensure it does not cause side effects.

True False

To maintain the purity of SQL queries and avoid unintended side effects, functions used in SELECT statements should not perform DML operations.

Q11

Multiple Choice

Identify the SQL statements that correctly create a basic function in Oracle.

SQL Code

CREATE OR REPLACE FUNCTION calculate_bonus
(p_salary NUMBER)
RETURN NUMBER
IS BEGIN
 RETURN p_salary * 0.10;
END;

CREATE OR REPLACE FUNCTION calculate_bonus (p_salary NUMBER) RETURN NUMBER IS BEGIN RETURN p_salary * 0.10; END; CREATE FUNCTION calculate_bonus (p_salary NUMBER) RETURNS NUMBER AS BEGIN RETURN p_salary * 0.10; END; CREATE OR REPLACE FUNCTION calculate_bonus (p_salary NUMBER) RETURNS NUMBER IS BEGIN RETURN p_salary * 0.10; END;CREATE OR REPLACE FUNCTION calculate_bonus AS (p_salary NUMBER) RETURN NUMBER BEGIN RETURN p_salary * 0.10; END;

A basic function in Oracle is created by defining the function name, parameters, return type, and the logic inside the BEGIN-END block. The correct SQL statements will follow this structure.

Q12

Multiple Choice

Select the SQL statements that correctly create a function with input parameters in Oracle.

CREATE OR REPLACE FUNCTION calculate_total_price (p_quantity NUMBER, p_price_per_unit NUMBER) RETURN NUMBER IS BEGIN RETURN p_quantity * p_price_per_unit; END; CREATE FUNCTION calculate_total_price (p_quantity NUMBER, p_price_per_unit NUMBER) RETURNS NUMBER IS BEGIN RETURN p_quantity * p_price_per_unit; END; CREATE OR REPLACE FUNCTION calculate_total_price (p_quantity NUMBER, p_price_per_unit NUMBER) RETURN NUMBER AS BEGIN RETURN p_quantity * p_price_per_unit; END;CREATE FUNCTION calculate_total_price (p_quantity NUMBER, p_price_per_unit NUMBER) RETURN NUMBER AS BEGIN RETURN p_quantity * p_price_per_unit; END;

Functions with input parameters allow you to pass values to the function and return a result based on those values. The correct SQL statements will define the input parameters and include logic that utilizes them.

Q13

Multiple Choice

Determine the SQL statements that demonstrate the creation of a function with a SELECT statement in Oracle.

CREATE OR REPLACE FUNCTION get_salary (p_employee_id NUMBER) RETURN NUMBER IS v_salary NUMBER; BEGIN SELECT salary INTO v_salary FROM employees WHERE employee_id = p_employee_id; RETURN v_salary; END; CREATE FUNCTION get_salary (p_employee_id NUMBER) RETURNS NUMBER IS v_salary NUMBER; BEGIN SELECT salary INTO v_salary FROM employees WHERE employee_id = p_employee_id; RETURN v_salary; END; CREATE OR REPLACE FUNCTION get_salary (p_employee_id NUMBER) RETURN NUMBER AS v_salary NUMBER; BEGIN SELECT salary INTO v_salary FROM employees WHERE employee_id = p_employee_id; RETURN v_salary; END;CREATE FUNCTION get_salary AS (p_employee_id NUMBER) RETURNS NUMBER IS v_salary NUMBER; BEGIN SELECT salary INTO v_salary FROM employees WHERE employee_id = p_employee_id; RETURN v_salary; END;

Functions can include SELECT statements to retrieve data from the database. The correct SQL statements will use a SELECT INTO clause to fetch the data into a variable and return it.

Q14

Multiple Choice

Identify the SQL statements that correctly demonstrate exception handling within a function in Oracle.

CREATE OR REPLACE FUNCTION get_department_name (p_department_id NUMBER) RETURN VARCHAR2 IS v_department_name VARCHAR2(100); BEGIN SELECT department_name INTO v_department_name FROM departments WHERE department_id = p_department_id; RETURN v_department_name; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 'Department ID not found'; END; CREATE OR REPLACE FUNCTION get_department_name (p_department_id NUMBER) RETURN VARCHAR2 IS v_department_name VARCHAR2(100); BEGIN SELECT department_name INTO v_department_name FROM departments WHERE department_id = p_department_id; RETURN v_department_name; EXCEPTION WHEN OTHERS THEN RETURN 'An unexpected error occurred'; END; CREATE OR REPLACE FUNCTION get_department_name (p_department_id NUMBER) RETURN VARCHAR2 IS v_department_name VARCHAR2(100); BEGIN SELECT department_name INTO v_department_name FROM departments WHERE department_id = p_department_id; RETURN v_department_name; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 'Department ID not found'; WHEN OTHERS THEN RETURN 'An unexpected error occurred'; END;CREATE FUNCTION get_department_name AS (p_department_id NUMBER) RETURN VARCHAR2 IS v_department_name VARCHAR2(100); BEGIN SELECT department_name INTO v_department_name FROM departments WHERE department_id = p_department_id; RETURN v_department_name; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 'Department ID not found'; WHEN OTHERS THEN RETURN 'An unexpected error occurred'; END;

Exception handling in functions allows you to manage errors gracefully. The correct SQL statements will include an EXCEPTION block that captures specific exceptions and returns appropriate error messages.

Q15

Multiple Choice

Choose the SQL statements that correctly create a function with a cursor in Oracle.

CREATE OR REPLACE FUNCTION calculate_department_salary (p_department_id NUMBER) RETURN NUMBER IS v_total_salary NUMBER := 0; CURSOR emp_cursor IS SELECT salary FROM employees WHERE department_id = p_department_id; BEGIN FOR emp_rec IN emp_cursor LOOP v_total_salary := v_total_salary + emp_rec.salary; END LOOP; RETURN v_total_salary; END; CREATE OR REPLACE FUNCTION calculate_department_salary (p_department_id NUMBER) RETURN NUMBER AS v_total_salary NUMBER := 0; CURSOR emp_cursor IS SELECT salary FROM employees WHERE department_id = p_department_id; BEGIN FOR emp_rec IN emp_cursor LOOP v_total_salary := v_total_salary + emp_rec.salary; END LOOP; RETURN v_total_salary; END; CREATE OR REPLACE FUNCTION calculate_department_salary (p_department_id NUMBER) RETURN NUMBER IS v_total_salary NUMBER := 0; CURSOR emp_cursor IS SELECT salary FROM employees WHERE department_id = p_department_id; BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO emp_rec; EXIT WHEN emp_cursor%NOTFOUND; v_total_salary := v_total_salary + emp_rec.salary; END LOOP; CLOSE emp_cursor; RETURN v_total_salary; END;CREATE FUNCTION calculate_department_salary AS (p_department_id NUMBER) RETURN NUMBER IS v_total_salary NUMBER := 0; CURSOR emp_cursor IS SELECT salary FROM employees WHERE department_id = p_department_id; BEGIN FOR emp_rec IN emp_cursor LOOP v_total_salary := v_total_salary + emp_rec.salary; END LOOP; RETURN v_total_salary; END;

Cursors in functions allow you to fetch and process multiple rows from a result set. The correct SQL statements will declare, open, and fetch from the cursor, and then close it after processing.

Q16

Multiple Choice

Which SQL statements demonstrate the creation of a function that returns a PL/SQL collection in Oracle?

CREATE OR REPLACE FUNCTION get_employee_names (p_department_id NUMBER) RETURN SYS.ODCIVARCHAR2LIST IS v_emp_names SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(); BEGIN SELECT employee_name BULK COLLECT INTO v_emp_names FROM employees WHERE department_id = p_department_id; RETURN v_emp_names; END; CREATE FUNCTION get_employee_names (p_department_id NUMBER) RETURNS SYS.ODCIVARCHAR2LIST IS v_emp_names SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(); BEGIN SELECT employee_name BULK COLLECT INTO v_emp_names FROM employees WHERE department_id = p_department_id; RETURN v_emp_names; END; CREATE OR REPLACE FUNCTION get_employee_names (p_department_id NUMBER) RETURN SYS.ODCIVARCHAR2LIST AS v_emp_names SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(); BEGIN SELECT employee_name BULK COLLECT INTO v_emp_names FROM employees WHERE department_id = p_department_id; RETURN v_emp_names; END;CREATE OR REPLACE FUNCTION get_employee_names (p_department_id NUMBER) RETURN SYS.ODCIVARCHAR2LIST AS v_emp_names SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(); BEGIN SELECT employee_name BULK COLLECT INTO v_emp_names FROM employees WHERE department_id = p_department_id; RETURN v_emp_names; END get_employee_names;

PL/SQL collections are used to store and manipulate sets of data. The correct SQL statements will define a collection type, populate the collection, and return it from the function.

Q17

Multiple Choice

Determine the SQL statements that correctly create a function with dynamic SQL in Oracle.

CREATE OR REPLACE FUNCTION get_row_count (p_table_name VARCHAR2, p_column_name VARCHAR2) RETURN NUMBER IS v_row_count NUMBER; BEGIN EXECUTE IMMEDIATE 'SELECT COUNT(' || p_column_name || ') FROM ' || p_table_name INTO v_row_count; RETURN v_row_count; END; CREATE OR REPLACE FUNCTION get_row_count (p_table_name VARCHAR2, p_column_name VARCHAR2) RETURN NUMBER AS v_row_count NUMBER; BEGIN EXECUTE IMMEDIATE 'SELECT COUNT(' || p_column_name || ') FROM ' || p_table_name INTO v_row_count; RETURN v_row_count; END; CREATE FUNCTION get_row_count (p_table_name VARCHAR2, p_column_name VARCHAR2) RETURNS NUMBER IS v_row_count NUMBER; BEGIN EXECUTE IMMEDIATE 'SELECT COUNT(' || p_column_name || ') FROM ' || p_table_name INTO v_row_count; RETURN v_row_count; END;CREATE FUNCTION get_row_count (p_table_name VARCHAR2, p_column_name VARCHAR2) RETURN NUMBER AS v_row_count NUMBER; BEGIN EXECUTE IMMEDIATE 'SELECT COUNT(' || p_column_name || ') FROM ' || p_table_name INTO v_row_count; RETURN v_row_count; END;

Dynamic SQL allows you to construct and execute SQL statements dynamically at runtime. The correct SQL statements will use the EXECUTE IMMEDIATE command to run the dynamic SQL and return the result.

Q18

Multiple Choice

Identify the SQL statements that correctly create a recursive function in Oracle.

CREATE OR REPLACE FUNCTION calculate_factorial (p_number NUMBER) RETURN NUMBER IS BEGIN IF p_number = 0 THEN RETURN 1; ELSE RETURN p_number * calculate_factorial(p_number - 1); END IF; END; CREATE FUNCTION calculate_factorial (p_number NUMBER) RETURNS NUMBER IS BEGIN IF p_number = 0 THEN RETURN 1; ELSE RETURN p_number * calculate_factorial(p_number - 1); END IF; END; CREATE OR REPLACE FUNCTION calculate_factorial (p_number NUMBER) RETURN NUMBER AS BEGIN IF p_number = 0 THEN RETURN 1; ELSE RETURN p_number * calculate_factorial(p_number - 1); END IF; END;CREATE FUNCTION calculate_factorial (p_number NUMBER) RETURNS NUMBER AS BEGIN IF p_number = 0 THEN RETURN 1; ELSE RETURN p_number * calculate_factorial(p_number - 1); END IF; END;

Recursive functions call themselves with modified parameters until a base condition is met. The correct SQL statements will define the recursive logic within the function.

Q19

Multiple Choice

Choose the SQL statements that correctly create a function with a PRAGMA AUTONOMOUS_TRANSACTION directive in Oracle.

CREATE OR REPLACE FUNCTION log_message (p_message VARCHAR2) RETURN NUMBER IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO log_messages (message_text, log_date) VALUES (p_message, SYSDATE); COMMIT; RETURN 1; END; CREATE OR REPLACE FUNCTION log_message (p_message VARCHAR2) RETURN NUMBER PRAGMA AUTONOMOUS_TRANSACTION IS BEGIN INSERT INTO log_messages (message_text, log_date) VALUES (p_message, SYSDATE); COMMIT; RETURN 1; END; CREATE FUNCTION log_message (p_message VARCHAR2) RETURNS NUMBER PRAGMA AUTONOMOUS_TRANSACTION IS BEGIN INSERT INTO log_messages (message_text, log_date) VALUES (p_message, SYSDATE); COMMIT; RETURN 1; END;CREATE FUNCTION log_message (p_message VARCHAR2) RETURN NUMBER AS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO log_messages (message_text, log_date) VALUES (p_message, SYSDATE); COMMIT; RETURN 1; END;

The PRAGMA AUTONOMOUS_TRANSACTION directive allows a function to commit or rollback independently of the main transaction. The correct SQL statements will include this directive within the function.

Q20

Multiple Choice

Select the SQL statements that demonstrate the creation of a function that performs a complex calculation in Oracle.

CREATE OR REPLACE FUNCTION calculate_compound_interest (p_principal NUMBER, p_rate NUMBER, p_periods NUMBER) RETURN NUMBER IS BEGIN RETURN p_principal * POWER((1 + p_rate / 100), p_periods); END; CREATE FUNCTION calculate_compound_interest (p_principal NUMBER, p_rate NUMBER, p_periods NUMBER) RETURNS NUMBER IS BEGIN RETURN p_principal * POWER((1 + p_rate / 100), p_periods); END; CREATE OR REPLACE FUNCTION calculate_compound_interest (p_principal NUMBER, p_rate NUMBER, p_periods NUMBER) RETURN NUMBER AS BEGIN RETURN p_principal * POWER((1 + p_rate / 100), p_periods); END;CREATE FUNCTION calculate_compound_interest (p_principal NUMBER, p_rate NUMBER, p_periods NUMBER) RETURNS NUMBER AS BEGIN RETURN p_principal * POWER((1 + p_rate / 100), p_periods); END;

Complex calculations in functions require the use of mathematical expressions and logic. The correct SQL statements will define the calculation and return the result.

Q21

Single Choice

How would you create a DB function to calculate the annual salary of an employee based on their monthly salary?

SQL Code

CREATE OR REPLACE FUNCTION calculate_annual_salary (
 monthly_salary IN NUMBER
) RETURN NUMBER IS
BEGIN
 RETURN monthly_salary * 12;
END;

CREATE OR REPLACE FUNCTION calculate_annual_salary (monthly_salary IN NUMBER) RETURN NUMBER IS BEGIN RETURN monthly_salary * 12; END; CREATE OR REPLACE PROCEDURE calculate_annual_salary (monthly_salary IN NUMBER) RETURN NUMBER IS BEGIN RETURN monthly_salary * 12; END; CREATE OR REPLACE TRIGGER calculate_annual_salary (monthly_salary IN NUMBER) RETURN NUMBER IS BEGIN RETURN monthly_salary * 12; END;CREATE OR REPLACE PACKAGE calculate_annual_salary IS BEGIN RETURN monthly_salary * 12; END;

A DB function in Oracle is created using the CREATE OR REPLACE FUNCTION statement, which includes parameters and a RETURN clause specifying the datatype of the returned value.

Q22

Single Choice

Which clause is mandatory in a DB function to specify the datatype of the returned value?

SQL Code

CREATE OR REPLACE FUNCTION get_employee_name (
 emp_id IN NUMBER
) RETURN VARCHAR2 IS
 emp_name VARCHAR2(100);
BEGIN
 SELECT employee_name INTO emp_name FROM employees WHERE employee_id = emp_id;
 RETURN emp_name;
END;

RETURN IN OUTIS

The RETURN clause is mandatory in a DB function to specify the datatype of the value that the function returns.

Q23

Single Choice

How would you handle an exception within a DB function?

SQL Code

CREATE OR REPLACE FUNCTION get_bonus (
 emp_id IN NUMBER
) RETURN NUMBER IS
 bonus NUMBER;
BEGIN
 SELECT salary * 0.10 INTO bonus FROM employees WHERE employee_id = emp_id;
 RETURN bonus;
EXCEPTION
 WHEN NO_DATA_FOUND THEN
 RETURN 0;
END;

WHEN OTHERS WHEN NO_DATA_FOUND WHEN TOO_MANY_ROWSWHEN VALUE_ERROR

Exceptions in a DB function are handled using the EXCEPTION block, where specific errors can be caught and handled appropriately.

Q24

Single Choice

Which of the following is true about a DB function that has OUT parameters?

SQL Code

CREATE OR REPLACE FUNCTION calculate_bonus (
 emp_id IN NUMBER, bonus OUT NUMBER
) RETURN NUMBER IS
 emp_salary NUMBER;
BEGIN
 SELECT salary INTO emp_salary FROM employees WHERE employee_id = emp_id;
 bonus := emp_salary * 0.10;
 RETURN emp_salary;
END;

OUT parameters are not allowed in functions. Functions can use OUT parameters, but must also return a value. OUT parameters replace the RETURN clause in functions.Functions with OUT parameters cannot be called from SQL.

DB functions can have OUT parameters, but the primary purpose of a function is to return a single value via the RETURN clause. OUT parameters are generally used in procedures.

Q25

Single Choice

How would you create a DB function that performs a calculation based on input parameters and returns the result?

SQL Code

CREATE OR REPLACE FUNCTION calculate_discount (
 original_price IN NUMBER, discount_rate IN NUMBER
) RETURN NUMBER IS
BEGIN
 RETURN original_price * (1 - discount_rate/100);
END;

CREATE OR REPLACE FUNCTION calculate_discount (original_price IN NUMBER, discount_rate IN NUMBER) RETURN NUMBER IS BEGIN RETURN original_price * (1 - discount_rate/100); END; CREATE OR REPLACE PROCEDURE calculate_discount (original_price IN NUMBER, discount_rate IN NUMBER) RETURN NUMBER IS BEGIN RETURN original_price * (1 - discount_rate/100); END; CREATE OR REPLACE TRIGGER calculate_discount (original_price IN NUMBER, discount_rate IN NUMBER) RETURN NUMBER IS BEGIN RETURN original_price * (1 - discount_rate/100); END;CREATE OR REPLACE PACKAGE calculate_discount IS BEGIN RETURN original_price * (1 - discount_rate/100); END;

A DB function is designed to perform a calculation or operation and return a result. The calculation is based on input parameters, and the result is returned using the RETURN clause.

Q26

Single Choice

How would you create a DB function that can be used in a SELECT statement?

SQL Code

CREATE OR REPLACE FUNCTION get_employee_department (
 emp_id IN NUMBER
) RETURN VARCHAR2 IS
 dept_name VARCHAR2(50);
BEGIN
 SELECT department_name INTO dept_name FROM departments d JOIN employees e ON d.department_id = e.department_id WHERE e.employee_id = emp_id;
 RETURN dept_name;
END;

CREATE OR REPLACE PROCEDURE get_employee_department (emp_id IN NUMBER) RETURN VARCHAR2 IS BEGIN SELECT department_name INTO dept_name FROM departments d JOIN employees e ON d.department_id = e.department_id WHERE e.employee_id = emp_id; RETURN dept_name; END; CREATE OR REPLACE FUNCTION get_employee_department (emp_id IN NUMBER) RETURN VARCHAR2 IS BEGIN SELECT department_name INTO dept_name FROM departments d JOIN employees e ON d.department_id = e.department_id WHERE e.employee_id = emp_id; RETURN dept_name; END; CREATE OR REPLACE TRIGGER get_employee_department (emp_id IN NUMBER) RETURN VARCHAR2 IS BEGIN SELECT department_name INTO dept_name FROM departments d JOIN employees e ON d.department_id = e.department_id WHERE e.employee_id = emp_id; RETURN dept_name; END;CREATE OR REPLACE PACKAGE get_employee_department IS BEGIN SELECT department_name INTO dept_name FROM departments d JOIN employees e ON d.department_id = e.department_id WHERE e.employee_id = emp_id; RETURN dept_name; END;

To create a DB function that can be used in a SELECT statement, the function must be deterministic and should return a single value that can be selected.

Q27

Single Choice

What is the best way to ensure that a DB function executes only if a certain condition is met?

SQL Code

CREATE OR REPLACE FUNCTION get_manager_name (
 emp_id IN NUMBER
) RETURN VARCHAR2 IS
 manager_name VARCHAR2(100);
BEGIN
 IF emp_id IS NOT NULL THEN
 SELECT e.manager_name INTO manager_name FROM employees e WHERE e.employee_id = emp_id;
 ELSE
 RETURN 'Unknown';
 END IF;
 RETURN manager_name;
END;

Use a WHERE clause Use an IF-THEN-ELSE statement Use an EXCEPTION blockUse a CURSOR

Conditional logic in a DB function can be implemented using IF-THEN-ELSE statements, ensuring that the function executes only when the specified condition is met.

Q28

Single Choice

How would you return multiple values from a DB function?

SQL Code

CREATE OR REPLACE FUNCTION get_employee_details (
 emp_id IN NUMBER
) RETURN employee_record IS
 emp_details employee_record;
BEGIN
 SELECT employee_name, salary INTO emp_details.employee_name, emp_details.salary FROM employees WHERE employee_id = emp_id;
 RETURN emp_details;
END;

Use multiple RETURN statements Use a composite or user-defined datatype Use multiple OUT parametersUse a CURSOR

To return multiple values from a DB function, you can define a user-defined record or composite datatype and return that as the function's result.

Q29

Single Choice

How can a DB function be used to perform a complex calculation involving multiple steps?

SQL Code

CREATE OR REPLACE FUNCTION calculate_final_grade (
 exam_score IN NUMBER, project_score IN NUMBER
) RETURN NUMBER IS
 total_score NUMBER;
 final_grade NUMBER;
BEGIN
 total_score := exam_score + project_score;
 IF total_score >= 90 THEN
 final_grade := 'A';
 ELSIF total_score >= 80 THEN
 final_grade := 'B';
 ELSE
 final_grade := 'C';
 END IF;
 RETURN final_grade;
END;

Use multiple RETURN statements Use variables and conditional logic within the function Use a CURSOR to perform the calculationUse a TRIGGER to perform the calculation

A DB function can include multiple steps, using variables to store intermediate results, and conditional logic to perform complex calculations before returning the final result.

Q30

Single Choice

What is the purpose of using a deterministic DB function in Oracle?

SQL Code

CREATE OR REPLACE FUNCTION get_tax_rate (
 region IN VARCHAR2
) RETURN NUMBER DETERMINISTIC IS
BEGIN
 RETURN CASE region WHEN 'US' THEN 0.07 WHEN 'EU' THEN 0.20 ELSE 0.15 END;
END;

To optimize function calls by caching results To allow the function to change the database state To enable the function to perform I/O operationsTo allow the function to be used in PL/SQL only

A deterministic function in Oracle is one that always returns the same result for the same input values, allowing the database to optimize calls to the function.

Oracle Database Quiz Questions