SQL Помещение запроса в функцию PL/SQL

Question

SQL Помещение запроса в функцию PL/SQL

Рейтинг: 0Ответов: 1Опубликовано: 12.06.2023

Есть SQL-запрос, который определяет максимальное и минимальное время между событиями:

SELECT CONCAT(person.NAME, ' ', person.PATRONYMIC) AS Worker, MIN(DATEDIFF(SDATE,   LDATE)+1) AS mindiff, MAX(DATEDIFF(SDATE, LDATE)+1) AS maxdiff
    FROM (SELECT SDATE, LAG(SDATE)OVER(ORDER BY SDATE) AS LDATE
    FROM schedule_
    WHERE PERSON_1 = 3 OR PERSON_2 = 3) AS rest, person
    WHERE LDATE IS NOT NULL AND person.ID=3

Необходимо на основе этого запроса создать функцию PL/SQL, которая должна возвращать курсор с результатом. Синтаксис Pl/SQL знаю весьма поверхностно, можете помочь со структурой запроса? Что должно быть написано после DECLARE, и что нужно поменять в самом запросе, когда его в функцию вставляешь?

sql функции oracle plsql

Источник: Stack Overflow на русском

Answer 1

▲ 1

--функция
create or replace function get_data(p_id_person in number)
    return sys_refcursor
is
    v_cur sys_refcursor;
begin
    open v_cur for 
        SELECT CONCAT(person.NAME, ' ',
               person.PATRONYMIC) AS Worker, 
               MIN(DATEDIFF(SDATE,   LDATE)+1) AS mindiff,
               MAX(DATEDIFF(SDATE, LDATE)+1) AS maxdiff
        FROM (SELECT SDATE, LAG(SDATE)OVER(ORDER BY SDATE) AS LDATE
        FROM schedule_
        WHERE PERSON_1 = p_id_person OR PERSON_2 = p_id_person) AS rest, person
        WHERE LDATE IS NOT NULL AND person.ID=p_id_person;
    return v_cur;
end get_data;

--вызов
declare
    v_cur sys_refcursor;
begin
    v_cur := get_data(p_id_person => 3);
end;

В данном случае можно использовать предопределенный слабый тип ref cursor = sys_refcursor. Решает вашу задачу. Если нужно в анонимном блоке, то помещаете функцию в блок объявлений и вызываете в теле анонимного блока.

declare
    --функция
    function get_data(p_id_person in number)
        return sys_refcursor
    is
        v_cur sys_refcursor;
    begin
        open v_cur for 
            SELECT CONCAT(person.NAME, ' ',
                   person.PATRONYMIC) AS Worker,    
                   MIN(DATEDIFF(SDATE,
                   LDATE)+1) AS mindiff,
                   MAX(DATEDIFF(SDATE, LDATE)+1) AS maxdiff
            FROM (SELECT SDATE, LAG(SDATE) OVER (ORDER BY SDATE) AS LDATE
            FROM schedule_
            WHERE PERSON_1 = p_id_person OR PERSON_2 = p_id_person) AS rest, person
            WHERE LDATE IS NOT NULL AND person.ID=p_id_person;
        return v_cur;
    end get_data;
    v_cur sys_refcursor;
begin
    --вызов
    v_cur := get_data(p_id_person => 3);
end;

SQL Помещение запроса в функцию PL/SQL

Ответы