Use SELECT INTO where possible

Cursors can be written with OPEN FETCH clause as explicit cursors or SELECT INTO clause using implicit cursors.

OPEN FETCH or implicit cursors

FUNCTION Check_Curr_Bal_Exist(
   company_ IN VARCHAR2) RETURN VARCHAR2
IS
   temp_       NUMBER;
   check_val_  VARCHAR2(5) := 'TRUE';
   CURSOR check_curr_bal IS
      SELECT 1 
      FROM   accounting_code_part_tab 
      WHERE  company         = company_
      AND code_part_function = 'CURR';
BEGIN
   OPEN check_curr_bal;
   FETCH check_curr_bal INTO temp_;
   IF(check_curr_bal%NOTFOUND)THEN
      CLOSE check_curr_bal;
      check_val_ := 'FALSE';
      RETURN check_val_;
   END IF;

   CLOSE check_curr_bal;
   RETURN check_val_;  
END Check_Curr_Bal_Exist;

SELECT INTO or implicit cursors

FUNCTION Get_Version (
   template_id_ IN VARCHAR2,
   component_   IN VARCHAR2 ) RETURN VARCHAR2
IS
   temp_ create_company_tem_comp_tab.version%TYPE;
BEGIN
   IF (Database_SYS.Get_Installation_Mode) THEN
      IF (template_id_ IS NULL OR component_ IS NULL) THEN
         RETURN NULL;
      END IF;
      SELECT version
         INTO  temp_
         FROM  create_company_tem_comp_tab
         WHERE template_id = template_id_
         AND   component = component_;
      RETURN temp_;
   ELSE   
      RETURN super(template_id_, component_);
   END IF;
   EXCEPTION
      WHEN no_data_found THEN
         RETURN NULL;
      WHEN too_many_rows THEN
         Raise_Too_Many_Rows___(template_id_, component_, 'Get_Version');
END Get_Version;

Use of implicit cursors has shows more performance gain over explicit cursors when the select is executed over multiple times.

When fetching single record it is better to use the implicit cursor instead of the explicit cursors. Keep in mind NO_DATA_FOUND and TOO_MANY_ROWS exceptions may trigger if cursor returns more than one row or no rows, which may needs to be handled depending on the expected outcome of the method.

When to use the implicit SELECT INTO cursors

• When select is going to execute for multiple times repeatedly

• When select returns single records

• When select query is not too complex

Considerations

• Need handling of NO_DATA_FOUND and TOO_MANY_ROWS exceptions to avoid errors.
• When fetching single rows try to see the feasibility of using implicit cursors.
• Performance gain vs usability and maintainability of using implicit cursors