Home
FUNCTIONS should not have OUT parameters
Description
The rule that "FUNCTIONS should not have OUT parameters" means that functions should not have parameters that are used to send data out of the function. This is because functions are used to return a single value, and OUT parameters are used to send multiple values. If a function is used to return multiple values, it should be written as a procedure instead. This rule helps to ensure that functions are used for their intended purpose and that procedures are used for more complex operations.
Key Benefits
- Easier to maintain: By not having OUT parameters, the code is easier to maintain as there is no need to keep track of the values that are being passed in and out of the function.
- More secure: By not having OUT parameters, the code is more secure as it is not possible for the function to modify any of the values passed in from the calling code.
- Better performance: By not having OUT parameters, the code is more efficient as it does not need to allocate memory for the OUT parameters.
Non-compliant Code Example
function GetCompleteCustomerDetails(customerId In INTEGER, FirstName out nvarchar2, --Non compliant code (Out parameters) LastName out nvarchar2, --Non compliant code (Out parameters) Area out nvarchar2, --Non compliant code (Out parameters) City out nvarchar2)return nvarchar2 --Non compliant code (Out parameters) IS BEGIN Select FIRSTNAME, LASTNAME, AREA, CITY Into FirstName, LastName, Area, City FROM CUSTOMERS; RETURN(CONCAT(CONCAT(ADDRESS1,ADDRESS2),CONCAT(Area,City))); END GetCompleteCustomerDetails;
Compliant Code Example
CREATE TYPE CUSTOMER_T AS OBJECT
( FirstName VARCHAR2(50),
LastName VARCHAR2(50),
Area VARCHAR2(100),
City VARCHAR2(100));
function GetCompleteCustomerDetails(customerId In INTEGER) --Compliant code
return CUSTOMER_T
Is
BEGIN
Select FIRSTNAME, LASTNAME, AREA, CITY Into CUSTOMER_T.FirstName, CUSTOMER_T.LastName, CUSTOMER_T.Area, CUSTOMER_T.City FROM CUSTOMERS Where Id = customerId;
RETURN CUSTOMER_T;
END GetCompleteCustomerDetails;