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;
Visual Expert 2024
 VEPLSQLRULE69