Oracle interview question Procedure Vs Function | ORACLE PROCEDURE VS FUNCTIONS

Summary

This video delves into the key differences between procedures and functions in Oracle PL/SQL, a common interview question for developers. It highlights that functions must return a value and can be called from a SELECT statement, while procedures can't and may use OUT parameters for output. The video also discusses the use of the 'return' keyword in both, the limitations on using DML in functions, and the introduction of autonomous transaction functions. It concludes with examples and touches on enhancements in Oracle 12.1, allowing functions and procedures to be part of the SQL class.

Takeaways

• 📚 The primary difference between a procedure and a function in PL/SQL is that a function must return a value using the 'return' keyword, while a procedure does not have to return a value but can use 'out' parameters.
• 🔢 A function in PL/SQL can return only a single value, which can be a scalar or composite, whereas a procedure can have multiple 'out' parameters.
• 📈 Functions can be called within a SELECT statement, similar to built-in functions, but procedures cannot be used in this way.
• 🚫 If a function contains a DML statement, it cannot be used in a SELECT statement unless it is an autonomous transaction function.
• 🔄 Autonomous transaction functions can contain DML statements and can be called from a SELECT statement as they run as a child transaction.
• 🛑 The 'return' keyword in functions is used to return a value, while in procedures, it is used to exit the procedure without returning a value.
• 💡 The use of 'return' in procedures is to exit early from the procedure, such as when a condition like 'employee number is null' is met.
• 👀 A function without a 'return' keyword can compile but will cause an error if called from a SELECT statement due to missing a return value.
• 🛠️ Compiler warnings can help identify functions that lack a 'return' statement, which is useful for debugging and maintaining code quality.
• 🌟 From Oracle 12.1 onwards, functions and procedures can be defined within a WITH clause and do not need to be created in the database, allowing for inline execution as part of a SELECT statement.
• 🔑 For experienced professionals, it's important to know that functions and procedures can be part of a class definition from version 12.1, offering more flexibility in their usage.

Q & A

What is the primary difference between a procedure and a function in PL/SQL?

The primary difference is that a function must return a value using the 'return' keyword, whereas a procedure does not have to return a value and can use 'out' or 'in out' parameters to pass information back.

Can a function return multiple values?

A function can only return a single value, which can be a scalar or a composite value like an array, but it cannot return multiple values directly.

How can functions be utilized within a SELECT statement in PL/SQL?

Functions can be called from a SELECT statement just like built-in functions, allowing for computational purposes within the query.

Why can't procedures be called from a SELECT statement?

Procedures cannot be called from a SELECT statement because they do not return a value and are used for implementing logical data flow rather than computation.

What is an autonomous transaction function in Oracle?

An autonomous transaction function is a special type of function that can contain DML statements and can be called from a SELECT statement. It runs as a child transaction independently of the main transaction.

What is the purpose of the 'return' keyword in a function?

In a function, the 'return' keyword is used to return a value to the calling environment, signifying the result of the function's computation.

How is the 'return' keyword used differently in a procedure compared to a function?

In a procedure, the 'return' keyword is used to exit the procedure rather than to return a value. It does not pass any data back to the caller.

What happens if a function in PL/SQL does not have a 'return' keyword?

A function without a 'return' keyword can still compile, but it will result in an error when called from a SELECT statement because the function must return a value.

How can one identify functions without a 'return' keyword in PL/SQL?

By enabling compiler warning settings and recompiling all functions, one can identify functions that do not have a 'return' value, as the compiler will issue warnings for such cases.

What enhancement was introduced in Oracle 12c regarding the creation of functions and procedures?

In Oracle 12c, functions and procedures can be defined within a WITH clause, allowing them to be part of the SELECT statement without being created inside the database as separate schema objects.

Can you provide an example of how to call a user-defined function within a SELECT statement?

Yes, a user-defined function can be called within a SELECT statement like this: 'SELECT my_function(parameters) FROM my_table;' where 'my_function' is the user-defined function and 'parameters' are the required inputs for that function.