Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > Составление SQL-запросов > Получение «родителей» исходной записи

Автор: mcTep 25.2.2009, 12:23

Здравствуйте!
Очень нужна помощь в составлении хранимой процедуры.

Есть таблица:

Код


CREATE TABLE `test_table` (
  `id` int(11) NOT NULL auto_increment,
  `farid` int(11) default NULL,
  PRIMARY KEY  (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `farid` (`farid`),
  CONSTRAINT `test_table_fk` FOREIGN KEY (`farid`) REFERENCES `test_table` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test_table` (`id`, `farid`) VALUES 
  (1, NULL),
  (10, NULL),
  (2, 1),
  (3, 1),
  (8, 1),
  (4, 2),
  (5, 3),
  (9, 4),
  (6, 5),
  (7, 5),
  (11, 10);

Необходимо составить хранимую процедуру возвращающую список всех «предков» записи, id которой является входным параметром.
Пример:

in_id = 1
+------+-------+
|  id  | farid |
+------+-------+
| null |  null |
+------+-------+

in_id = 5
+------+-------+
|  id  | farid |
+------+-------+
|   3  |   1   |
|   1  |  null |
+------+-------+

Есть аналогичная хранимая процедура для «FireBird» (может поможет   smile

Код


CREATE PROCEDURE TEST_TABLE_ANCESTORS (
    IN_ID INTEGER)
RETURNS (
    OUT_ID INTEGER,
    OUT_FARID INTEGER)
AS
BEGIN
    SELECT 
        "ID", "FARID"
    FROM 
        TEST_TABLE
    WHERE 
        "ID" = :IN_ID
    ORDER BY TEST_TABLE.FARID
    INTO
        :OUT_ID,
        :OUT_FARID;
    SUSPEND;
    WHILE(NOT OUT_FARID IS NULL) DO
    BEGIN
        SELECT "ID", "FARID" 
        FROM TEST_TABLE
        WHERE TEST_TABLE.ID = :OUT_FARID
        ORDER BY TEST_TABLE.FARID
        INTO
            :OUT_ID,
            :OUT_FARID;
        SUSPEND;
    END
END

Заранее благодарен. Всего доброго.

Автор: mcTep 25.2.2009, 16:56

После долгих мучений придумал решение smile

Пришлось использовать временную таблицу и написать дополнительную функцию.

Код


CREATE DEFINER = 'root'@'localhost' FUNCTION `new_func`(in_id INTEGER(11))
    RETURNS int(11)
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    RETURN (select ifnull(`farid`, 0) from `test_table` where `id` = in_id);
END;

CREATE DEFINER = 'root'@'localhost' PROCEDURE `new_proc`(IN in_id INTEGER(11))
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    create temporary table temp (id int(11), farid int(11));
    set @_pid = in_id;
    
    while @_pid > 0 do 
        set @_pid = new_func(@_pid);                            
        insert into `temp` select * from `test_table` where id = @_pid;
    end while;

    select * from `temp`;
    drop table if exists `temp`;
END;