Форум программистов, компьютерный форум CyberForum.ru

Как сделать хранимую процедуру? - PostgreSQL

Восстановить пароль Регистрация
 
Trigger_name
0 / 0 / 0
Регистрация: 11.11.2016
Сообщений: 22
01.12.2016, 23:00     Как сделать хранимую процедуру? #1
Составить хранимую процедуру для реализации факта аренды яхты и отображения контракта в виде таблицы.
При этом стоимость контракта устанавливается в размере, на 50% большем суммарной зарплаты экипажа за время аренды.
Все необходимые элементы передавать как параметры.

не могу понять как вообще такое сделать)
Миниатюры
Как сделать хранимую процедуру?  
Similar
Эксперт
41792 / 34177 / 6122
Регистрация: 12.04.2006
Сообщений: 57,940
01.12.2016, 23:00     Как сделать хранимую процедуру?
Посмотрите здесь:

Как сделать check во второй таблице, чтобы table.f2 > table1.field2 PostgreSQL
PostgreSQL Как в postgres запросе сделать динамический limit?

Искать еще темы с ответами

Или воспользуйтесь поиском по форуму:
После регистрации реклама в сообщениях будет скрыта и будут доступны все возможности форума.
grgdvo
500 / 441 / 119
Регистрация: 02.09.2012
Сообщений: 1,283
05.12.2016, 17:21     Как сделать хранимую процедуру? #2
Надо начать с

SQL
1
2
3
4
5
6
7
CREATE OR REPLACE FUNCTION YachtRent(client_id INTEGER, yacht_id INTEGER, rent_from DATE, rent_to DATE)
RETURNS .... AS $$
-- 1. посчитать зарплату коллектив на срок аренды от rent_from до rent_to
-- 2. увеличить в два раза сумму
-- 3. создать контракт
-- 4. вернуть запись этого контракта
$$ LANGUAGE plpgsql;
не очень понятно, что такое "хранимая процедура для отображения контракта в виде таблицы", полагаю возврат записи вновь созданного контракта.
Trigger_name
0 / 0 / 0
Регистрация: 11.11.2016
Сообщений: 22
05.12.2016, 23:56  [ТС]     Как сделать хранимую процедуру? #3
grgdvo, я вот делал так)
не знаю правильно ли)

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
CREATE OR REPLACE FUNCTION factContract ("StartDateV"   DATE,
                    "EndDateV"  DATE,
                    "PriceV"    FLOAT,
                    "YachtIdV"  INT,
                    "ClientIdV" INT)
RETURNS TABLE ("id_contractT"   INT,
        "StartDateT"    DATE,
        "EndDateT"  DATE,
        "PriceT"    FLOAT,
        "YachtIdT"  INT,
        "ClientIdT" INT)
AS $$
DECLARE
"sumSalary" FLOAT;
BEGIN 
    SELECT SUM("Salary") INTO "sumSalary"
    FROM "Crew" cr, "Positions" ps 
    WHERE cr."YachtId" = "YachtIdV" AND cr."PositionId" = ps."PositionId";
    "PriceV" := "PriceV" + "sumSalary" * 0.5;
 
    INSERT INTO "Contract" ("StartDate", "EndDate","Price","YachtId", "ClientId") VALUES 
                ("StartDateV", "EndDateV", "PriceV", "YachtIdV", "ClientIdV");
    
RETURN QUERY
    SELECT *
    FROM "Contract"
    WHERE "StartDate" = "StartDateV" AND "YachtId" = "YachtIdV" AND "ClientId" = "ClientIdV";
END ;
$$ LANGUAGE plpgsql;
grgdvo
500 / 441 / 119
Регистрация: 02.09.2012
Сообщений: 1,283
06.12.2016, 14:47     Как сделать хранимую процедуру? #4
Цитата Сообщение от Trigger_name Посмотреть сообщение
SQL
1
CREATE OR REPLACE FUNCTION factContract ("StartDateV" DATE, "EndDateV" DATE, "PriceV" FLOAT, "YachtIdV" INT, "ClientIdV" INT)
Зачем в параметрах PriceV? В задании сказано, что цена определяется из зарплаты экипажа.

Цитата Сообщение от Trigger_name Посмотреть сообщение
SQL
1
BEGIN SELECT SUM("Salary") INTO "sumSalary" FROM "Crew" cr, "Positions" ps WHERE cr."YachtId" = "YachtIdV" AND cr."PositionId" = ps."PositionId"; "PriceV" := "PriceV" + "sumSalary" * 0.5;
Мне кажется неправильное определение суммы контракта.
Нужно посчитанную сумму зарплаты умножить на кол-во месяце аренды и еще умножить на 2, а не на 0.5.
Как вообще зарплата хранится в positions?? это что зарплата в месяц??
тогда может как-то так с грубым округлением в меньшую сторону

SQL
1
2
3
    SELECT SUM("Salary") * EXTRACT(MONTH FROM age("EndDateV", "StartDateV")) * 2 INTO "sumSalary"
    FROM "Crew" cr, "Positions" ps 
    WHERE cr."YachtId" = "YachtIdV" AND cr."PositionId" = ps."PositionId";
Цитата Сообщение от Trigger_name Посмотреть сообщение
SQL
1
INSERT INTO "Contract" ("StartDate", "EndDate","Price","YachtId", "ClientId") VALUES ("StartDateV", "EndDateV", "PriceV", "YachtIdV", "ClientIdV");
Ключевое поле "ContractId" не фигурирует в запросе. Там у вас сиквенс (автоинкремент)?
Тогда его лучше вернуть в какую-нибудь переменную и потом в запросе возврата подставить

SQL
1
2
3
INSERT INTO "Contract" ("StartDate", "EndDate","Price","YachtId", "ClientId", "ContractId") VALUES ("StartDateV", "EndDateV", "PriceV", "YachtIdV", "ClientIdV", DEFAULT???) RETURNING "ContractId" INTO "ContractIdV";
 
RETURN QUERY SELECT * FROM "Contract" WHERE "ContractId" = "ContractIdV";
Trigger_name
0 / 0 / 0
Регистрация: 11.11.2016
Сообщений: 22
10.12.2016, 22:58  [ТС]     Как сделать хранимую процедуру? #5
grgdvo, делаю вот так но оно чет мне ругается
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
CREATE OR REPLACE FUNCTION factContract ("StartDateV"   DATE,
                    "EndDateV"  DATE,
                    "PriceV"    FLOAT,
                    "YachtIdV"  INT,
                    "ClientIdV" INT)
RETURNS TABLE ("id_contractT"   INT,
        "StartDateT"    DATE,
        "EndDateT"  DATE,
        "PriceT"    FLOAT,
        "YachtIdT"  INT,
        "ClientIdT" INT)
AS $$
DECLARE
"sumSalary" FLOAT;
BEGIN 
        SELECT SUM("Salary") * EXTRACT(MONTH FROM age("EndDateV", "StartDateV")) * 2 INTO "sumSalary"
        FROM "Crew" cr, "Positions" ps 
        WHERE cr."YachtId" = "YachtIdV" AND cr."PositionId" = ps."PositionId";
 
        
        INSERT INTO "Contract" ("StartDate", "EndDate","Price","YachtId", "ClientId", "ContractId") 
        VALUES ("StartDateV", "EndDateV", "PriceV", "YachtIdV", "ClientIdV", DEFAULT???) RETURNING "ContractId" INTO "ContractIdV";
 
        RETURN QUERY 
        SELECT * 
        FROM "Contract" 
        WHERE "ContractId" = "ContractIdV";
END ;
$$ LANGUAGE plpgsql;
с такой ошибкой

как это можно исправить?
Миниатюры
Как сделать хранимую процедуру?  
grgdvo
500 / 441 / 119
Регистрация: 02.09.2012
Сообщений: 1,283
11.12.2016, 00:28     Как сделать хранимую процедуру? #6
Неизвестную переменную надо объявить рядом с "sumSalary".

Вообще смысл моего предложения сводился к тому, чтобы при вставке сразу получить
идентификатор только-что добавленного конракта. У вас до этого использовался повторный запрос. Зачем??
Также обратите внимание на значение "DEFAULT???", вопросики точно надо убрать.
Я так написал, потому что для меня не было достаточно информации, чтобы поставить точное значение.
Возможно DEFAULT надо будет заменить на что-то, либо совсем убрать и убрать тогда поле "ContractId" из INSERT,
если оно у вас получает значение из последовательности (автоинкремент).
Trigger_name
0 / 0 / 0
Регистрация: 11.11.2016
Сообщений: 22
11.12.2016, 00:52  [ТС]     Как сделать хранимую процедуру? #7
grgdvo, я вот сделал так все заработало
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
CREATE OR REPLACE FUNCTION factContract ("StartDateV"   DATE,
                    "EndDateV"  DATE,
                    "PriceV"    FLOAT,
                    "YachtIdV"  INT,
                    "ClientIdV" INT)
RETURNS TABLE ("id_contractT"   INT,
        "StartDateT"    DATE,
        "EndDateT"  DATE,
        "PriceT"    FLOAT,
        "YachtIdT"  INT,
        "ClientIdT" INT)
AS $$
DECLARE
"sumSalary" FLOAT;
BEGIN 
        SELECT SUM("Salary") * EXTRACT(MONTH FROM age("EndDateV", "StartDateV")) * 2 INTO "sumSalary"
        FROM "Crew" cr, "Positions" ps 
        WHERE cr."YachtId" = "YachtIdV" AND cr."PositionId" = ps."PositionId";
 
        
        INSERT INTO "Contract" ("StartDate", "EndDate","Price","YachtId", "ClientId") 
        VALUES ("StartDateV", "EndDateV", "PriceV", "YachtIdV", "ClientIdV", DEFAULT) RETURNING "ContractId"; 
 
        RETURN QUERY 
        SELECT * 
        FROM "Contract" 
        WHERE "ContractId" = "ContractIdV";
END ;
$$ LANGUAGE plpgsql;
но теперь когда я делаю так выдает ошибку
SQL
1
2
3
4
5
SELECT * FROM factContract(CAST('12.12.2019' AS DATE),
                CAST('13.12.2019' AS DATE),
                CAST(200000 AS FLOAT),
                11,
                11)
Миниатюры
Как сделать хранимую процедуру?  
grgdvo
500 / 441 / 119
Регистрация: 02.09.2012
Сообщений: 1,283
11.12.2016, 03:28     Как сделать хранимую процедуру? #8
Тогда попробуйте DEFAULT убрать.
Trigger_name
0 / 0 / 0
Регистрация: 11.11.2016
Сообщений: 22
11.12.2016, 20:20  [ТС]     Как сделать хранимую процедуру? #9
grgdvo, убрал DEFAULT
но все равно эта часть не работает когда я пытаюсь сделать вот такой запрос
SQL
1
2
3
4
5
SELECT * FROM factContract(CAST('12.12.2019' AS DATE),
                CAST('13.12.2019' AS DATE),
                CAST(200000 AS FLOAT),
                11,
                11)
grgdvo
500 / 441 / 119
Регистрация: 02.09.2012
Сообщений: 1,283
12.12.2016, 06:49     Как сделать хранимую процедуру? #10
убрал из параметров PriceV. зачем он в параметрах?? в задании сказано вычислять из зарплаты
алгоритм вычисления зарплаты не менял, но думаю он неправильный. формула не учитывает подсчет месяцев при переходе через год (EXTRACT(....))
Второе выражение исходит из того, что задан автоинкремент поля "ContractId", поэтому DEFAULT и "ContractId" убраны из INSERT
Тип возвращаемого значения поставлена таблица "Contract", можно уменьшить перебивание полей.

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
CREATE OR REPLACE FUNCTION factContract ("StartDateV"   DATE,
                    "EndDateV"  DATE,
                    "YachtIdV"  INT,
                    "ClientIdV" INT)
RETURNS setof "Contract"
AS $$
DECLARE
"sumPrice" FLOAT;
"ContractIdV" "Contract"."ContractId"%TYPE;
BEGIN 
        SELECT SUM("Salary") * EXTRACT(MONTH FROM age("EndDateV", "StartDateV")) * 2 INTO "sumPrice"
        FROM "Crew" cr, "Positions" ps 
        WHERE cr."YachtId" = "YachtIdV" AND cr."PositionId" = ps."PositionId";
 
        
        INSERT INTO "Contract" ("StartDate", "EndDate","Price","YachtId", "ClientId") 
        VALUES ("StartDateV", "EndDateV", "sumPrice", "YachtIdV", "ClientIdV") RETURNING "ContractId" INTO "ContractIdV";
 
        RETURN QUERY 
        SELECT *
        FROM "Contract" 
        WHERE "ContractId" = "ContractIdV";
END ;
$$ LANGUAGE plpgsql;
 
CREATE SEQUENCE public."Contract_ContractId_seq"
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;
 
CREATE TABLE public."Contract"
(
  "ContractId" INTEGER NOT NULL DEFAULT NEXTVAL('"Contract_ContractId_seq"'::regclass),
  "ClientId" INTEGER,
  "YachtId" INTEGER,
  "StartDate" DATE,
  "EndDate" DATE,
  "Price" DOUBLE PRECISION,
  CONSTRAINT "Contract_pk" PRIMARY KEY ("ContractId")
);
Trigger_name
0 / 0 / 0
Регистрация: 11.11.2016
Сообщений: 22
13.12.2016, 19:58  [ТС]     Как сделать хранимую процедуру? #11
grgdvo, спасибо решил немного по другому)
немного модифицировал ваш код
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE OR REPLACE FUNCTION FactContract
           ("StartDateV"DATE,
                    "EndDateV"  DATE,
                    "YachtIdV"  INT,
                    "ClientIdV" INT)
RETURNS setof "Contract"
AS $$
 
DECLARE
"sumPrice" FLOAT;
"ContractIdV" "Contract"."ContractId"%TYPE;
BEGIN 
      
    SELECT SUM("Salary") * ("EndDateV" - "StartDateV") * 0.5 INTO "sumPrice"
    FROM "Crew" cr, "Positions" ps 
    WHERE cr."YachtId" = "YachtIdV" AND cr."PositionId" = ps."PositionId";
 
      
        INSERT INTO "Contract" ("StartDate", "EndDate","Price","YachtId", "ClientId") 
        VALUES ("StartDateV", "EndDateV", "sumPrice", "YachtIdV", "ClientIdV") RETURNING "ContractId" INTO "ContractIdV";
 
        RETURN QUERY 
        SELECT *
        FROM "Contract" 
        WHERE "ContractId" = "ContractIdV";
END ;
$$ LANGUAGE plpgsql
Yandex
Объявления
13.12.2016, 19:58     Как сделать хранимую процедуру?
Ответ Создать тему
Опции темы

Текущее время: 02:26. Часовой пояс GMT +3.
КиберФорум - форум программистов, компьютерный форум, программирование
Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2017, vBulletin Solutions, Inc.
Рейтинг@Mail.ru