SQLize Online / PHPize Online  /  SQLtest Online

create table citizens ( id NUMBER PRIMARY KEY,FIO varchar(100)); CREATE TABLE addresses (id NUMBER PRIMARY KEY, address VARCHAR2(200) null,other varchar2(200) null,indeks varchar2(200) null, postal_code DATE NOT NULL, checkout_date date null); create table x_user_on_address (id NUMBER PRIMARY KEY,c_begin DATE NOT NULL,c_end DATE); ------------------------------------------------------ insert into citizens values (1, 'Иванов, Иван, Иванович'); insert into citizens values (2, 'Сидоров, Пётр, Иванович'); insert into citizens values (3, 'Чехов, Павел, Михайлович'); insert into citizens values (4, 'Соколова, Наталья, Степановна'); insert into citizens values (5,'Рубцов, Виктор, Иванович'); insert into citizens values (6,'Петров, Иван, Николаевич'); insert into citizens values (7,'Дмитриев, Сергей, Александрович'); insert into citizens values (8,'Кузнецов, Александр, Михайлович'); insert into citizens values (9,'Львов, Дмитрий, Васильевич'); insert into citizens values (10,'Михайлов, Николай, Иванович'); insert into addresses values (1, 'г. Сызрань, ул. Ильича-15, кв.12,',null, '675485', DATE '2020-01-01',null); insert into addresses values (2, 'г. Москва, ул. Свободы-10, кв.30,',null, '123444', DATE '2020-02-01',null); insert into addresses values (3, 'г. Кунгур, ул. Бочкарева-170,',null, '617475', DATE '2020-03-01',null); insert into addresses values (4, 'г. Самара, ул. Восточная-40,',null,' 332445', DATE '2020-04-01', DATE '2022-12-31'); insert into addresses values (5,'г. Саратов, ул. Пролетарская-55, кв.44,',null,' 685478', DATE '2020-05-01',null); insert into addresses values (6,'г. Пермь, ул. Майская-1, кв.1,',null,' 458796', DATE '2020-06-01',null); insert into addresses values (7,'г. Екатеринбург, ул. Ленина-20, кв.5,',null, '620075', DATE '2020-07-01', DATE '2022-06-30'); insert into addresses values (8,'г. Тюмень, ул. Советская-15, кв.3,',null,' 625000', DATE '2020-08-01',null); insert into addresses values (9,'г. Омск, ул. Маркса-10, кв.2,',null,' 644099', DATE '2020-09-01',null); insert into addresses values (10,'г. Новосибирск, ул. Красный проспект-50, кв.1,','г. Омск, ул. Маркса-10, кв.2,',' 630099', DATE '2020-10-01',null); INSERT INTO x_user_on_address VALUES (1, DATE '2020-01-01', NULL); INSERT INTO x_user_on_address VALUES (2, DATE '2020-02-01', NULL); INSERT INTO x_user_on_address VALUES (3, DATE '2020-03-01', NULL); INSERT INTO x_user_on_address VALUES (4, DATE '2020-04-01', DATE '2022-12-31'); INSERT INTO x_user_on_address VALUES (5, DATE '2023-01-01', NULL); INSERT INTO x_user_on_address VALUES (6, DATE '2020-05-01', NULL); INSERT INTO x_user_on_address VALUES (7, DATE '2020-06-01', DATE '2022-06-30'); INSERT INTO x_user_on_address VALUES (8, DATE '2022-07-01', NULL); INSERT INTO x_user_on_address VALUES (9, DATE '2020-07-01', NULL); INSERT INTO x_user_on_address VALUES (10, DATE '2020-08-01', NULL); ------------------------------------------------------ select * From citizens; select * From addresses; select * From x_user_on_address; CREATE OR REPLACE PROCEDURE self IS p_mode INT 1; e_too_many_rows EXCEPTION; PRAGMA EXCEPTION_INIT(e_too_many_rows, -1422); BEGIN WITH RankedUsers AS ( SELECT citizens.id, citizens.FIO, addresses.address, addresses.indeks, x_user_on_address.c_begin, ROW_NUMBER() OVER (PARTITION BY citizens.id ORDER BY x_user_on_address.c_begin) AS row_num FROM citizens, addresses, x_user_on_address WHERE citizens.id = x_user_on_address.id AND x_user_on_address.id = addresses.id ) SELECT id, FIO, address, indeks, c_begin FROM RankedUsers WHERE row_num = 1; -- Алфавит CREATE TABLE latin_alphabet ( letter CHAR(1) ); INSERT INTO latin_alphabet (letter) VALUES ('a'); INSERT INTO latin_alphabet (letter) VALUES ('b'); INSERT INTO latin_alphabet (letter) VALUES ('c'); INSERT INTO latin_alphabet (letter) VALUES ('d'); INSERT INTO latin_alphabet (letter) VALUES ('e'); INSERT INTO latin_alphabet (letter) VALUES ('f'); INSERT INTO latin_alphabet (letter) VALUES ('g'); INSERT INTO latin_alphabet (letter) VALUES ('h'); INSERT INTO latin_alphabet (letter) VALUES ('i'); INSERT INTO latin_alphabet (letter) VALUES ('j'); INSERT INTO latin_alphabet (letter) VALUES ('k'); INSERT INTO latin_alphabet (letter) VALUES ('l'); INSERT INTO latin_alphabet (letter) VALUES ('m'); INSERT INTO latin_alphabet (letter) VALUES ('n'); INSERT INTO latin_alphabet (letter) VALUES ('o'); INSERT INTO latin_alphabet (letter) VALUES ('p'); INSERT INTO latin_alphabet (letter) VALUES ('q'); INSERT INTO latin_alphabet (letter) VALUES ('r'); INSERT INTO latin_alphabet (letter) VALUES ('s'); INSERT INTO latin_alphabet (letter) VALUES ('t'); INSERT INTO latin_alphabet (letter) VALUES ('u'); INSERT INTO latin_alphabet (letter) VALUES ('v'); INSERT INTO latin_alphabet (letter) VALUES ('w'); INSERT INTO latin_alphabet (letter) VALUES ('x'); INSERT INTO latin_alphabet (letter) VALUES ('y'); INSERT INTO latin_alphabet (letter) VALUES ('z'); SELECT letter FROM latin_alphabet; CREATE OR REPLACE PROCEDURE self IS p_mode INT 1; e_too_many_rows EXCEPTION; PRAGMA EXCEPTION_INIT(e_too_many_rows, -1422); BEGIN FOR rec IN ( SELECT citizens.FIO, addresses.address, addresses.postal_code FROM citizens JOIN addresses ON citizens.id = addresses.citizen_id WHERE (p_mode = 0 OR addresses.checkout_date IS NULL) OR (p_mode = 1 AND addresses.checkout_date IS NULL) OR (p_mode = -1 AND addresses.checkout_date IS NOT NULL AND addresses.registration_date = ( SELECT MAX(registration_date) FROM addresses a WHERE a.citizen_id = citizens.id AND (p_mode = 0 OR a.checkout_date IS NULL) )) ) LOOP DBMS_OUTPUT.PUT_LINE(rec.FIO || ' ' || rec.address || ' ' || rec.postal_code); END LOOP; END;'