1 Тема від Akos_Bond

  • Akos_Bond
  • Новий користувач
  • Поза форумом
  • Дата реєстрації: 16.11.2019
  • Повідомлень: 83
    • Репутація: 5

Тема: Курсори в процедурах у Mysql

Всім добрий вечір!
Почала недавно вивчати Mysql і стикнулася з проблемою, мабуть, неправильного використання курсору у процедурі. Вибиває помилка, що не знайдено стовпець дати, хоча таблиця створена, записи внесені і ніяких змін виконано не було. Викладач сказав, що проблема у використанні курсору. В лекціях інформації майже нема по цьому, тому хотіла би зрозуміти, в чому конкретно проблема по коді.

-- залежно від дати, заносити дані з меншою датою до іншої таблиці, а з даної видаляти
DELIMITER //
CREATE PROCEDURE delete_contracts_by_date (v_date DATE)
BEGIN
DECLARE p INT;
DECLARE finished NUMERIC(1);
DECLARE my_cursor CURSOR
FOR SELECT contract_date, contract_type FROM k_contract;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
IF contract_date < v_date THEN
SET finished = 0;
OPEN my_cursor; -- відкриваємо курсор
FETCH my_cursor INTO p; -- читаємо перший рядок
WHILE( finished != 1) DO
INSERT INTO interim (date_contract, type_contract) 
SELECT contract_date, contract_type FROM k_contract
WHERE contract_date<v_date;
DELETE FROM k_contract
WHERE contract_date<v_date;
FETCH my_cursor INTO p; -- читаємо наступний рядок
END WHILE;
CLOSE my_cursor; -- закриваємо курсор
END IF;
END//
Подякували: plusxx1

2 Відповідь від frz Востаннє редагувалося frz (08.12.2020 22:06:23)

  • frz
  • frz
  • Data / DB Engineer
  • Поза форумом
  • Звідки: PL
  • Дата реєстрації: 28.12.2019
  • Повідомлень: 7 274

Re: Курсори в процедурах у Mysql

CREATE PROCEDURE delete_contracts_by_date (v_date DATE)
BEGIN
DECLARE p_date DATE;
DECLARE p_contract INT;
DECLARE finished NUMERIC(1);
DECLARE my_cursor CURSOR
FOR SELECT contract_date, contract_type FROM k_contract Order By contract_date Desc;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
OPEN my_cursor; -- відкриваємо курсор
FETCH my_cursor INTO p_date, p_contract; -- читаємо перший рядок
IF p_date < v_date THEN
    SET finished = 0;
    WHILE( finished != 1) DO
        INSERT INTO interim (contract_date, contract_type) 
        SELECT p_date, p_contract;
        DELETE FROM k_contract
        WHERE contract_date=p_date and contract_type = p_contract;
        FETCH my_cursor INTO p_date, p_contract; -- читаємо наступний рядок
    END WHILE;
    CLOSE my_cursor; -- закриваємо курсор
END IF;
END

Якщо хочете просто здати лабу, то ось код вище, а якщо хочете розібратися в суті, то завжди відкривайте ґуґл і шукайте приклад схожих конструкцій, наприклад в цьому випадку я шукав "mysql loop table records example". З MySQL давненько не стикався, тому трохи пошукав.

Подякували: Akos_Bond, plusxx, leofun013

frz вебсайт

3 Відповідь від Akos_Bond

  • Akos_Bond
  • Новий користувач
  • Поза форумом
  • Дата реєстрації: 16.11.2019
  • Повідомлень: 83
    • Репутація: 5

Re: Курсори в процедурах у Mysql

Дуже дякую за допомогу, поради і за те, що доступно пояснили).. Бачу, де помилки, спробую розібратися в темі, щоб добре розуміти..

4 Відповідь від Akos_Bond

  • Akos_Bond
  • Новий користувач
  • Поза форумом
  • Дата реєстрації: 16.11.2019
  • Повідомлень: 83
    • Репутація: 5

Re: Курсори в процедурах у Mysql

В мене по ходу ще одне питання виникло. Я змінила тип змінної p_contract на той, що в мене в таблиці логування. Чому тоді записи менші введеної дати не видаляються з таблиці k_contract і не заносяться в створену таблицю логування?

5 Відповідь від Torbins

  • Torbins
  • Torbins
  • Replace Team
  • Поза форумом
  • Дата реєстрації: 07.05.2012
  • Повідомлень: 3 080
    • Репутація: 2279

Re: Курсори в процедурах у Mysql

Наведіть, будь ласка, приклад даних. Та покажіть поточний код своєї процедури.

6 Відповідь від frz

  • frz
  • frz
  • Data / DB Engineer
  • Поза форумом
  • Звідки: PL
  • Дата реєстрації: 28.12.2019
  • Повідомлень: 7 274

Re: Курсори в процедурах у Mysql

В мене працює, пробував тип колонки contract_type boolean і відповідно тип змінної p_contract boolean.

drop table if exists k_contract;
create table k_contract (contract_date date, contract_type boolean);

Insert Into k_contract 
Select curdate() - interval 1 month as contract_date, 0 as contract_type Union All 
Select curdate() - interval 2 month as contract_date, 1 as contract_type;

drop table if exists interim;
create table interim (contract_date date, contract_type boolean);

Drop PROCEDURE if exists delete_contracts_by_date;
CREATE PROCEDURE delete_contracts_by_date (v_date DATE)
BEGIN
DECLARE p_date DATE;
DECLARE p_contract boolean;
DECLARE finished NUMERIC(1);
DECLARE my_cursor CURSOR
FOR SELECT contract_date, contract_type FROM k_contract Order By contract_date Desc;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
OPEN my_cursor; -- відкриваємо курсор
FETCH my_cursor INTO p_date, p_contract; -- читаємо перший рядок
IF p_date < v_date THEN
    SET finished = 0;
    WHILE( finished != 1) DO
        INSERT INTO interim (contract_date, contract_type) 
        SELECT p_date, p_contract;
        DELETE FROM k_contract
        WHERE contract_date=p_date and contract_type = p_contract;
        FETCH my_cursor INTO p_date, p_contract; -- читаємо наступний рядок
    END WHILE;
    CLOSE my_cursor; -- закриваємо курсор
END IF;
END

call delete_contracts_by_date ( curdate() );

Select * From k_contract;

/*
No results found
*/

Select * From interim;

/*
contract_date,contract_type
2020-11-09,0
2020-10-09,1
*/

Якщо не допомагає, то покажіть свій варіант.

frz вебсайт

7 Відповідь від Akos_Bond

  • Akos_Bond
  • Новий користувач
  • Поза форумом
  • Дата реєстрації: 16.11.2019
  • Повідомлень: 83
    • Репутація: 5

Re: Курсори в процедурах у Mysql

Ойй, то відразу треба було краще показати свій варіант. Я щось протупила з цим трошки...
Таблиця interim:

CREATE TABLE interim
(
num_contract INT not null auto_increment primary key, -- номер контракту
date_contract DATE, -- дата контракту
type_contract ENUM ('A', 'B', 'C') -- тип контракту
)

Таблиця k_contract ідентична + зовнішні ключі.
Скрін з даної таблиці:
https://replace.org.ua/uploads/images/9628/3804531161bccc042412d7e60d3cc0e1.jpg
Відповідно, коли я викликаю процедуру, 

CALL delete_contracts_by_date('2013-11-12');

дані в мене не видаляються з контрактів і не заносяться в таблицю логування.

8 Відповідь від frz Востаннє редагувалося frz (10.12.2020 17:35:08)

  • frz
  • frz
  • Data / DB Engineer
  • Поза форумом
  • Звідки: PL
  • Дата реєстрації: 28.12.2019
  • Повідомлень: 7 274

Re: Курсори в процедурах у Mysql

Таблиця k_contract ідентична

Твердження невірне, оскільки, наприклад, на скріні назва першої колонки "contract_num", а в скрипті Create Table перша колонка називається "num_contract", і т.д.

Якщо ж взяти за постулат що назви співпадають, то нижче дещо видозмінив код для врахування колонки "type_contract", потестував - працює: 

drop table if exists k_contract;
/*create table k_contract (contract_date date, contract_type boolean);*/

CREATE TABLE k_contract (
num_contract INT not null auto_increment primary key, -- номер контракту
date_contract DATE, -- дата контракту
type_contract ENUM ('A', 'B', 'C'), -- тип контракту
k_firm_firm_num INT, 
k_staff_staff_num INT
);

Insert Into k_contract (num_contract, date_contract, type_contract)
Select 1 as num_contract, curdate() - interval 1 month as date_contract, 'A' as type_contract Union All 
Select 2 as num_contract, curdate() - interval 2 month as date_contract, 'B' as type_contract Union All
Select 3 as num_contract, curdate() - interval 3 month as date_contract, 'C' as type_contract;

drop table if exists interim;
/*create table interim (contract_date date, contract_type boolean);*/

CREATE TABLE interim (
num_contract INT not null auto_increment primary key, -- номер контракту
date_contract DATE, -- дата контракту
type_contract ENUM ('A', 'B', 'C') -- тип контракту
);

Drop PROCEDURE if exists delete_contracts_by_date;
CREATE PROCEDURE delete_contracts_by_date (v_date DATE)
BEGIN
DECLARE p_date DATE;
DECLARE p_type ENUM ('A', 'B', 'C');
DECLARE p_num INT;
DECLARE finished NUMERIC(1);
DECLARE my_cursor CURSOR
FOR SELECT num_contract, date_contract, type_contract FROM k_contract Order By date_contract Desc;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
OPEN my_cursor; -- відкриваємо курсор
FETCH my_cursor INTO p_num, p_date, p_type; -- читаємо перший рядок
IF p_date < v_date THEN
    SET finished = 0;
    WHILE( finished != 1) DO
        INSERT INTO interim (num_contract, date_contract, type_contract) 
        SELECT p_num, p_date, p_type;
        DELETE FROM k_contract
        WHERE num_contract = p_num and date_contract = p_date and type_contract = p_type;
        FETCH my_cursor INTO p_num, p_date, p_type; -- читаємо наступний рядок
    END WHILE;
    CLOSE my_cursor; -- закриваємо курсор
END IF;
END

call delete_contracts_by_date ( curdate() );

Select * From k_contract;

/*
No results found
*/

Select * From interim;

/*
num_contract,date_contract,type_contract
1,2020-11-10,A
2,2020-10-10,B
3,2020-09-10,C
*/

Із неспівпадінням назв колонок пропоную розібратися самостійно.

----

▼Upd: зовнішні ключі

Upd: Про зовнішні ключі в описі недостатньо деталізовано, можливо інші таблиці посилаються на k_contract? Якщо так, то потрібно видозмінити ключі on delete cascade або ж спочатку видаляти дані з дочірніх таблиць.

Подякували: Akos_Bond1

frz вебсайт