Optimize SQL query in form of a Procedure
As far as returning a ref cursor the following should work;

CREATE OR REPLACE PROCEDURE get_table1_data(p_column2 IN table3.column2%TYPE, 
                                            p_tables_recs  OUT SYS_REFCURSOR ) AS
BEGIN

   OPEN p_table_recs FOR 
      SELECT sp.column1
           , sp.column2
           , sp.column3
           , gen.column1
           , NVL(gen.column2, 0) + NVL(gen.column3, 0) gensize
           , gen.column4
           , gen.column5
      FROM   table1 sp
          , (SELECT *
             FROM   table2
             WHERE  table2.column1 IN (SELECT table1.column4
                                       FROM   table1
                                       WHERE  table1.column2 IN (SELECT table3.column1
                                                                 FROM   table3
                                                                 WHERE  table3.column2 = p_column_2))) gen
       WHERE  gen.id = sp.id;
END get_table1_data;