postgres stored procedure pl pgSQL cursor refcursor - ghdrako/doc_snipets GitHub Wiki

Podczas wykonywania operatorów SQL w środowisku PL/pgSQL system zarządzania bazą danych przydziela obszar roboczej pamięci, który zawiera informacje niezbędne do wykonania operatorów SQL oraz zestaw danych zwracanych lub przetwarzanych przez te operatory.

Kursor to wskaźnik na obszar roboczy operatora SQL, a za jego pomocą program PL/pgSQL może zarządzać tym obszarem.

Proces korzystania z jawnych kursorów składa się z następujących kroków:

• Deklaracja kursora.
• Otwarcie kursora.
• Pobieranie danych z kursora.
• Zamknięcie kursora.

{cursor name} [[NO] SCROLL] CURSOR [({параметры})] 
FOR {оператор SELECT};

Kursory są używane do przechodzenia między wierszami zestawu danych znajdującego się w obszarze roboczym.

Jeśli przy deklarowaniu kursora zostanie podane słowo kluczowe SCROLL, możliwe będzie przemieszczanie się zarówno w przód, jak i w tył. Jeśli zostanie podane NO SCROLL, możliwe będzie przemieszczanie się tylko w przód (możliwe jest tylko zwiększanie numeru wiersza)

DECLARE
Cur_Orders_Date CURSOR FOR
SELECT *
FROM Orders
WHERE order_date ='2019-11-02';

Kursor może być zadeklarowany z parametrami. Pozwala to kursorowi generować różne zestawy danych dla różnych wartości parametrów. Przy definiowaniu parametru należy podać jego nazwę i typ

DECLARE
Cur_Orders_Date_P CURSOR (p_date_ord Orders.order_date%TYPE) FOR 
SELECT *
FROM Orders
WHERE order_date = p_date_ord;

Cursors can be used for row-by-row processing, although that is an inefficient use of cursors within PostgreSQL and should be avoided in favor of using SQL to perform set operations. However, in addition to row-by-row processing, cursors are a mechanism of memory management within PostgreSQL. When you have very large data sets, you can avoid memory bottlenecks by using a cursor to process smaller batches of rows. A very common way to use this is in combination with a function.

DO $$ 
DECLARE  
  counter int; 
  filmcrew CURSOR FOR SELECT person_id, job FROM film_crew; 
  rec record; 
BEGIN 
  OPEN filmcrew; 
  counter = 0;
  WHILE counter < 2 LOOP 
    FETCH NEXT FROM filmcrew INTO rec; 
    counter = counter + 1; 
    RAISE NOTICE 'Job: %', rec.job; 
 END LOOP; 
END; $$ 

Z aktywnego zbioru danych pobierany jest wiersz, na który ustawiony jest wskaźnik, a wartości poszczególnych elementów tego wiersza przypisywane są do zmiennych, podanych po słowie kluczowym INTO. Następnie wskaźnik aktywnego zbioru przesuwa się do następnego wiersza.

FETCH {kierunek} FROM {nazwa kursora} INTO {lista zmiennych}I{rekord};

• {kierunek} może mieć następujące wartości: NEXT, PRIOR, FIRST, LAST, ABSOLUTE N, RELATIVE N, FORWARD, BACKWARD.
• Jeśli kierunek nie jest podany, zostanie użyty NEXT.
• Zazwyczaj pobieranie danych z kursora odbywa się wewnątrz pętli, aż do momentu wykrycia końca zestawu danych. Do wykrywania końca aktywnego zestawu używana jest specjalna zmienna FOUND, która ma wartość TRUE, jeśli wiersz został pomyślnie pobrany, i FALSE, jeśli osiągnięto koniec aktywnego zestawu danych.

Podczas pracy z kursorami można używać polecenia MOVE, które przesuwa kursor, ale nie pobiera wiersza.

MOVE {kierunek} {nazwa kursora};

Kursory do zmiany danych nie powinny zawierać operacji grupowania i sortowania i powinny kończyć się frazą FOR UPDATE. Do aktualizacji wiersza, na który wskazuje kursor, używa się operatora UPDATE, który ma następującą składnię

UPDATE {nazwa tabeli} SET {operator aktualizacji} WHERE CURRENT OF {nazwa kursora}

Aby usunąć wiersz, na którym ustawiony jest wskaźnik kursora, używa się operatora DELETE, który ma następującą składnię

DELETE FROM {nazwa tabeli}
WHERE CURRENT OF {nazwa kursora}

DO $$ 
DECLARE
Cur_Emp cursor IS SELECT * FROM employees_copy 
FOR UPDATE;
v_add_salary employees.salary%TYPE;
v_sum_salary numeric(10,2):=0; 
BEGIN
FOR v_cur_emp IN Cur_Emp
LOOP ” 
v_add_salary := 
CASE
WHEN v_cur_emp.salary > 10000 THEN 1000
WHEN v_cur_emp.salary > 5000 THEN 500 
ELSE 200
END;
v_sum_salary := v_sum_salary +v_add_salary;
UPDATE Employees_copy
SET salary = salary + v_add_salary 
WHERE CURRENT OF cur_emp;
END LOOP;
RAISE NOTICE 'Результат: ' ;
RAISE notice ' v_sum_salary = %', v_sum_salary; 
END $$;

DO $$
DECLARE
Cur_Orders CURSOR (p_status Orders_copy.status%TYPE)
IS SELECT * FROM Orders_copy
WHERE status = p_status;
FOR UPDATE;
v_credit_limit Customers.credit_limit%TYPE; 
v_orders_sum numeric(10,2);
BEGIN
RAISE notice 'Удалены заказы';
FOR v_cur IN Cur_Orders('Pending') 
LOOP
SELECT credit_limit into v_credit_limit 
FROM Customers
WHERE customer_id = v_cur.customer_id;
SELECT SUM(quantity*unit_price) into v_orders_sum
FROM Orders_copy JOIN order_items_copy USING (order_id) 
WHERE customer_id = v_cur.customer_id
AND status = 'Pending';
IF v_orders_sum > v_credit_limit
THEN DELETE FROM Orders_copy
WHERE CURRENT OF cur_orders;
RAISE notice 'customer_id =%%%', v_cur.customer_id, 
'order_id = ', v_cur.order_id;
END IF;
END LOOP;
END $$;

CREATE OR REPLACE FUNCTION film_crew_info() 
RETURNS TABLE (personname TEXT, title TEXT, 
               department TEXT, job TEXT) 
AS $$ 
BEGIN  
RETURN QUERY 
   SELECT 
 p.name AS personname, 
 f.title, 
 fc.department, 
 fc.job 
   FROM 
 film_crew AS fc   JOIN person AS p ON 
 fc.person_id = p.person_id 
   JOIN film AS f ON 
 fc.film_id = f.film_id; 
END  
$$ LANGUAGE plpgsql;

BEGIN; 
DECLARE 
  filmcrew CURSOR FOR  SELECT * FROM film_crew_info(); 
  FETCH 15 
  FROM 
  filmcrew; 
COMMIT; 

This will pull 15 rows in a batch out of the cursor.

Cursors within PostgreSQL and PL/pgSQL have some utility but are infrequently used.

Petla kursorowa

FOR {zmienna petli} IN 
{nazwa kursora}|{instrukcja SELECT} 
LOOP
{cialo petli} 
END LOOP;

Operacje otwierania, pobierania i zamykania kursora w takich pętlach wykonywane są niejawnie. Zmniejsza to ilość kodu i ułatwia jego zrozumienie. Należy jednak pamiętać, że nie wszystkie zadania przetwarzania danych z użyciem kursorów mogą być realizowane przy użyciu pętli dla kursorów.

DO $$
DECLARE
Cur_Orders_Date_P CURSOR (p_date_ord Orders.order_date%TYPE) 
FOR SELECT * FROM Orders
WHERE order_date = p_date_ord;
BEGIN
RAISE NOTICE 'Результат: ';
RAISE NOTICE '%%%%% ' ,
LPAD('order_id',8),LPAD('customer_id',13), 
LPAD('status' , 10),LPAD('salesman_id’, 12) , 
LPAD('order_date’,12);
FOR v_cur_orders IN cur_orders_date_P('2019-11-02') 
LOOP
RAISE notice '%%%%% ',
LPAD(v_cur_orders.order_id::text,8),
LPAD(v_cur_orders.customer_id::text,13),
LPAD(v_cur_orders.status,10),
LPAD(v_cur_orders.salesman_id::text,12), 
LPAD(v_cur_orders.order_date::text,12);
END LOOP;
END $$;

Kursory i pętle dla kursorów mogą być zagnieżdżone. W takim przypadku wewnętrzny kursor wykonuje się dla każdego wiersza zewnętrznego kursora. Zewnętrzna pętla kolejno wybiera dane o klientach, a w wewnętrznej pętli dla każdego klienta tworzone są dane o łącznej sumie zamówień tego klienta, znajdujących się w stanie oczekiwania ('Pending').

DO $$
DECLARE
Cur_Customers CURSOR
FOR SELECT * FROM Customers;
cur_orders CURSOR (p_id_customer orders.customer_id%type, 
                   p_status Orders.status%type) FOR 
           SELECT customer_id, status, 
                  SUM(quantity*unit_price) AS order_sum 
             FROM Orders JOIN Order_items USING (order_id) 
            WHERE customer_id = p_id_customer 
              AND status = p_status
        GROUP BY customer_id, status;
BEGIN
  RAISE NOTICE 'Результат: ';
  RAISE notice '% % %', LPAD('customer_id',10),
  LPAD('status' , 10), LPAD('order_sum',12);
  FOR v cur cuct IN cur customers
  LOOP
    FOR v_cur_order IN cur_orders(v_cur_cuct.customer_id,'Pending') 
    LOOP
      RAISE notice '% % %' ,
      LPAD(v_cur_order.customer_id::text,10),
      LPAD(v_cur_order.status,10),
      LPAD(v_cur_order.order_sum::text,12);
   END LOOP;
  END LOOP;
END $$;

W wewnętrznej pętli kursorowej można używać wartości pól zmiennej kursorowej (zmiennej pętli) zewnętrznej pętli. Ponizej przedstawiono przykład użycia wartości pól zmiennej kursorowej zewnętrznej pętli. W pętli zewnętrznej z tabeli Customers pobierane są dane o klientach, a w pętli wewnętrznej obliczana jest całkowita suma zamówień klienta znajdujących się w stanie oczekiwania ('Pending') i wyświetlane są dane o klientach, których całkowita suma zamówień w stanie Pending przekracza ich limit kredytowy.

DO $$
DECLARE
  Cur_Customers CURSOR
  FOR SELECT * FROM Customers;
  Cur_Orders CURSOR (p_id_customer orders.eustomer_id%type, 
                     p_status Orders.status%type)
                FOR SELECT customer_id, status, 
                           SUM(quantity*unit_price) AS order_sum 
                      FROM Orders JOIN Order_items USING (order_id) 
                     WHERE customer_id = p_id_customer
                       AND status = p_status
                    GROUP BY customer_id, status;
BEGIN
  RAISE NOTICE 'Результат: ';
  RAISE NOTICE '% % %', LPAD('customer_id',10), 
  LPAD('credit_limit',12),LPAD('order_sum',12);
  FOR v cur cuct IN Cur Customers 
  LOOP
    FOR v_cur_order IN
      Cur_Orders(v_cur_cuct.customer_id,'Pending')
    LOOP
      IF v_cur_order.order_sum > v_cur_cuct.credit_limit 
      THEN
        RAISE NOTICE '% % %',
        LPAD(v_cur_order.customer_id::text,10),
        LPAD(v_cur_cuct.credit_limit::text,12), 
        LPAD(v_cur_order.order_sum::text,12);
      END IF;
    END LOOP;
  END LOOP;
END $$;

Kursor do modyfikacji danych

Kursory do zmiany danych nie powinny zawierać operacji grupowania i sortowania i powinny kończyć się frazą FOR UPDATE

Do aktualizacji wiersza, na którym ustawiony jest wskaźnik kursora, używa się operatora UPDATE, który ma następującą składnię:

UPDATE {nazwa tabeli} SET {operator aktualizacji} WHERE CURRENT OF {nazwa kursora}

Aby usunąć wiersz, na którym ustawiony jest wskaźnik kursora, używa się operatora DELETE, który ma następującą składnię:

DELETE FROM {nazwa tabeli}
WHERE CURRENT OF {nazwa kursora}

Przyklad modyfikacji:

DO $$ 
DECLARE
Cur_Emp cursor IS SELECT * FROM employees_copy 
FOR UPDATE;
v_add_salary employees.salary%TYPE;
v_sum_salary numeric(10,2):=0; 
BEGIN
FOR v_cur_emp IN Cur_Emp
LOOP ” 
v_add_salary := 
CASE
WHEN v_cur_emp.salary > 10000 THEN 1000
WHEN v_cur_emp.salary > 5000 THEN 500 
ELSE 200
END;
v_sum_salary := v_sum_salary +v_add_salary;
UPDATE Employees_copy
SET salary = salary + v_add_salary 
WHERE CURRENT OF cur_emp;
END LOOP;
RAISE NOTICE 'Результат: ' ;
RAISE notice ' v_sum_salary = %', v_sum_salary; 
END $$;

Przyklad usuwania:

DO $$
DECLARE
Cur_Orders CURSOR (p_status Orders_copy.status%TYPE)
IS SELECT * FROM Orders_copy
WHERE status = p_status;
FOR UPDATE;
v_credit_limit Customers.credit_limit%TYPE; 
v_orders_sum numeric(10,2);
BEGIN
RAISE notice 'Удалены заказы';
FOR v_cur IN Cur_Orders('Pending') 
LOOP
SELECT credit_limit into v_credit_limit 
FROM Customers
WHERE customer_id = v_cur.customer_id;
SELECT SUM(quantity*unit_price) into v_orders_sum
FROM Orders_copy JOIN order_items_copy USING (order_id) 
WHERE customer_id = v_cur.customer_id
AND status = 'Pending';
IF v_orders_sum > v_credit_limit
THEN DELETE FROM Orders_copy
WHERE CURRENT OF cur_orders;
RAISE notice 'customer_id =%%%', v_cur.customer_id, 
'order_id = ', v_cur.order_id;
END IF;
END LOOP;
END $$;

Zmienne kursorowe

W programach PL/pgSQL można używać zmiennych o typie kursora (zmienne kursorowe). Takie zmienne deklaruje się w sekcji deklaracji, a wartość (zestaw danych) przypisuje się w sekcji wykonywalnej. Po zamknięciu kursora powiązanego z zmienną kursorową można przypisać jej nową wartość. Używając zmiennych kursorowych, można przekazywać wyniki wykonania zapytań z jednego programu do drugiego.

Deklaracja zmiennej kursora:

{nazwa zmiennej kursora} REFCURSOR;

Przypisanie wartości zmiennej kursorowej odbywa się w wykonywalnej części przy użyciu operatora:

OPEN {zazwa zmiennej kursora} FOR {instrukcja SELECT};

Zamknięcie zmiennej kursora wykonuje się za pomocą operatora:

CLOSE {nazwa zmiennej kursora};

Podczas wykonywania tego operatora zwalniane są zasoby używane przez operator SELECT, ale aktywny zestaw danych powiązany z zmienną kursora pozostaje w zakresie zmiennej.

Przykład deklaracji i użycia zmiennej wskaźnikowej kursora v_refcur. W sekcji wykonywalnej można jej przypisać różne wartości (wyniki wykonania różnych instrukcji SELECT), które zależą od wartości zmiennej v_t.

DO $$ 
DECLARE 
  v_refcur REFCURSOR; 
  v_id integer;
  v_name varchar(40); 
  v_wh integer;
  v_t integer;
BEGIN
  v_t :=2;
  RAISE NOTICE 'Результат: ' ;
  RAISE notice ' v_t = %',v_t;
  CASE v_t
    WHEN 1 THEN OPEN v_refcur FOR
           SELECT employee_id, 
                  first_name | | ' ' | J last_name, 
                  department_id
            FROM Employees WHERE employee_id <110
           ORDER BY employee_id;
   WHEN 2 THEN OPEN v_refcur FOR
          SELECT department_id, 
                 department_name, 
                 location_id
           FROM Departments WHERE department_id <70 
          ORDER BY department_id;
  END CASE; 
LOOP
  FETCH v_refcur INTO v_id, v_name, v_wh;
  EXIT WHEN NOT FOUND;
  RAISE NOTICE 'v_id= %%%%%', LPAD(v_id::text,5), 
               'v_name= ', RPAD(v_name,20), 
               'v_wh= ',LPAD(v_wh::text,5);
END LOOP;
CLOSE v_refcur;
END $$;