Miravild

Есть справочник стран — таблица FW_COUNTRY

CREATE TABLE FW_COUNTRY
("ID_COUNTRY" NUMBER(10),
	"V_NAME" VARCHAR2(64),
  "B_DELETED" NUMBER(1) DEFAULT 0
) TABLESPACE MIRAVILD_DATA;
 
COMMENT ON TABLE FW_COUNTRY IS 'Страна';
COMMENT ON COLUMN FW_COUNTRY.ID_COUNTRY IS 'Код страны';
COMMENT ON COLUMN FW_COUNTRY.V_NAME IS 'Наименование страны';
COMMENT ON COLUMN FW_COUNTRY.B_DELETED IS 'Признак удаления (0 - активна)';
/

Напишите процедуру для извлечения данных из таблицы, так, чтобы на вход она могла принимать ID страны – переменная pID (при условии, что она может быть NULL – и тогда нужно выводить все записи), а на выход возвращала курсор (переменная dwr) в виде полей ID_COUNTRY, V_NAME с сортировкой по V_NAME для B_DELETED = 0.

Тестовые данные:

INSERT INTO fw_country c
  (c.id_country, c.v_name, c.b_deleted)
VALUES
  (643, 'Россия', 1);

INSERT INTO fw_country c
  (c.id_country, c.v_name, c.b_deleted)
VALUES
  (840, 'Соединенные Штаты', 2);

INSERT INTO fw_country c
  (c.id_country, c.v_name, c.b_deleted)
VALUES
  (826, 'Соединенное Королевство', 3);

INSERT INTO fw_country c
  (c.id_country, c.v_name, c.b_deleted)
VALUES
  (276, 'Германия', 4);

INSERT INTO fw_country c
  (c.id_country, c.v_name, c.b_deleted)
VALUES
  (268, 'Грузия', DEFAULT);
  
COMMIT WORK;
/

CREATE OR REPLACE PROCEDURE p_cursor(p_id IN fw_country.id_country%TYPE,
                                     dwr  OUT SYS_REFCURSOR) IS
  v_str VARCHAR2(4000 CHAR);
BEGIN
  IF p_id IS NOT NULL THEN
    v_str := 'SELECT c.id_country, c.v_name
      FROM fw_country c
     WHERE c.b_deleted = 0 AND c.id_country = ' || p_id ||
             ' ORDER BY c.v_name ASC';
  ELSE
    v_str := 'SELECT c.id_country, c.v_name
        FROM fw_country c
       WHERE c.b_deleted = 0
       ORDER BY c.v_name ASC';
  END IF;

  OPEN dwr FOR v_str;

END p_cursor;
/

P.S.
Конфигурация СУБД — Oracle Database 11g Express Edition Release 11.2.0.2.0
IDE — PL/SQL Developer Version 11.0.6.1776

Tags: Oracle, PL/SQL, Procedure, SQL