gistfile1.sql

CREATE OR REPLACE PACKAGE planes AS
  FUNCTION get_flying_time(plane_name VARCHAR2) RETURN NUMBER;
  FUNCTION get_flying_max_altitude(plane_name VARCHAR2) RETURN NUMBER;
  FUNCTION get_flying_time_in_altitude(plane_name VARCHAR2) RETURN NUMBER;
  PROCEDURE display_all(plane_name VARCHAR2);
END planes;
/

CREATE OR REPLACE PACKAGE BODY planes AS
  TYPE t_seq IS TABLE OF NUMBER;
  
  FUNCTION compute_into_ascii(plane_name VARCHAR2) RETURN NUMBER AS
    seq_number NUMBER := 0;
  BEGIN
    FOR i IN 1..length(plane_name) LOOP
      seq_number := seq_number + ascii(substr(plane_name, i, 1));
    END LOOP;
    RETURN seq_number;
  END compute_into_ascii;
  
  FUNCTION get_seq_numbers(plane_name VARCHAR2) RETURN t_seq AS
    seq_number NUMBER := compute_into_ascii(plane_name);
    seq t_seq;
  BEGIN
    seq := t_seq();
    WHILE seq_number <> 1 LOOP
      IF mod(seq_number, 2) = 0 THEN
        seq_number := seq_number / 2;
      ELSE
        seq_number := seq_number * 3;
        seq_number := seq_number + 1;
      END IF;
      seq.extend();
      seq(seq.count) := seq_number;
    END LOOP;
    RETURN seq;
  END get_seq_numbers;
  
  FUNCTION get_flying_max_altitude(seq t_seq) RETURN NUMBER AS
    seq_max NUMBER := 0;
  BEGIN
    FOR i IN seq.first..seq.last LOOP
      IF seq(i) > seq_max THEN
        seq_max := seq(i);
      END IF;
    END LOOP;
    RETURN seq_max;
  END get_flying_max_altitude;

  FUNCTION get_flying_max_altitude(plane_name VARCHAR2) RETURN NUMBER AS
    seq t_seq := get_seq_numbers(plane_name);
  BEGIN
    RETURN get_flying_max_altitude(seq);
  END get_flying_max_altitude;
  
  FUNCTION get_flying_time_in_altitude(plane_name VARCHAR2) RETURN NUMBER AS
    seq t_seq := get_seq_numbers(plane_name);
    seq_altitude t_seq := t_seq();
    seq_max NUMBER := get_flying_max_altitude(seq);
    after_max BOOLEAN := false;
    min_altitude NUMBER := compute_into_ascii(plane_name);
  BEGIN
    FOR i IN seq.first..seq.last LOOP
      IF seq(i) = seq_max THEN
        after_max := true;
      END IF;
      EXIT WHEN after_max AND seq(i) <= min_altitude;
      seq_altitude.extend();
      seq_altitude(seq_altitude.count) := seq(i);
    END LOOP;
    RETURN seq_altitude.count;
  END get_flying_time_in_altitude;

  FUNCTION get_flying_time(plane_name VARCHAR2) RETURN NUMBER AS
  BEGIN
    RETURN get_seq_numbers(plane_name).count;
  END get_flying_time;
    
  PROCEDURE display_all(plane_name VARCHAR2) AS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Flying time: ' || get_flying_time(plane_name));
    DBMS_OUTPUT.PUT_LINE('Flying time in altitude: ' 
      || get_flying_time_in_altitude(plane_name));
    DBMS_OUTPUT.PUT_LINE('Max altitude: ' || get_flying_max_altitude(plane_name));
  END display_all;

END planes;
/
-- Example : 
EXEC planes.display_all('Enterprise');

SELECT planes.get_flying_max_altitude('Enterprise') "Max altitude",
  planes.get_flying_time_in_altitude('Enterprise') "Flying time",
  planes.get_flying_time('Enterprise') "Flying time in altitude"
FROM dual;