Open antonovdmitriy opened 1 year ago
CREATE TYPE person_typ AS OBJECT ( id NUMBER, first_name VARCHAR2(255), last_name VARCHAR2(255), age NUMBER, city VARCHAR2(255), profession VARCHAR2(255) ); / CREATE OR REPLACE PROCEDURE select_person ( p_first_name IN person.first_name%TYPE DEFAULT NULL, p_last_name IN person.last_name%TYPE DEFAULT NULL, p_age IN person.age%TYPE DEFAULT NULL, p_city IN person.city%TYPE DEFAULT NULL, p_profession IN person.profession%TYPE DEFAULT NULL, p_person OUT person_typ ) AS v_id person.id%TYPE; v_first_name person.first_name%TYPE; v_last_name person.last_name%TYPE; v_age person.age%TYPE; v_city person.city%TYPE; v_profession person.profession%TYPE; BEGIN SELECT id, first_name, last_name, age, city, profession INTO v_id, v_first_name, v_last_name, v_age, v_city, v_profession FROM person WHERE (first_name = NVL(p_first_name, first_name)) AND (last_name = NVL(p_last_name, last_name)) AND (age = NVL(p_age, age)) AND (city = NVL(p_city, city)) AND (profession = NVL(p_profession, profession)); p_person := person_typ(v_id, v_first_name, v_last_name, v_age, v_city, v_profession); EXCEPTION WHEN NO_DATA_FOUND THEN p_person := NULL; END; /