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

PostgreSQL

Войти
Регистрация
Восстановить пароль
 
Trigger_name
0 / 0 / 0
Регистрация: 11.11.2016
Сообщений: 28
#1

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

01.12.2016, 23:00. Просмотров 369. Ответов 10
Метки нет (Все метки)

Составить хранимую процедуру для реализации факта аренды яхты и отображения контракта в виде таблицы.
При этом стоимость контракта устанавливается в размере, на 50% большем суммарной зарплаты экипажа за время аренды.
Все необходимые элементы передавать как параметры.

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

Как в postgres запросе сделать динамический limit? - PostgreSQL
Добрый день, Не отрабатывается следующий запрос: UPDATE m_hotels set firm_id = 2 where id in (select id from m_hotels order by...

Как сделать check во второй таблице, чтобы table.f2 > table1.field2 - PostgreSQL
У меня есть две таблицы: table1( field1 integer primary key, field2 integer ); table2( f1 integer primary key, f2...

Подскажите, как сделать хранимую процедуру в MS SQL для постраничного вывода? - Базы данных
Есть следующие мысли: Если можно сделать запрос, который добавит еще одно поле, проиндексированное от 0 до количества записей, я смогу...

Как вызвать хранимую процедуру из VB? - Oracle
Как вызвать хранимую процедуру из VB?

VS 2012 Как найти хранимую процедуру? - Visual Studio
Добрый день. В VS2012 имеется проект отчетов (Reporting Service). В одном из отчетов обнаружил, что запрос данных выполняется не через...

Как написать хранимую процедуру, а не функцию - Visual Basic .NET
Имеется программа (скрина 1), при выборе параметра например: Компания - acer Из бд 50 моделей, покажутся только 10 моделей компании Acer ...

После регистрации реклама в сообщениях будет скрыта и будут доступны все возможности форума.
grgdvo
544 / 480 / 138
Регистрация: 02.09.2012
Сообщений: 1,412
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
Сообщений: 28
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
544 / 480 / 138
Регистрация: 02.09.2012
Сообщений: 1,412
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
Сообщений: 28
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
544 / 480 / 138
Регистрация: 02.09.2012
Сообщений: 1,412
11.12.2016, 00:28     Как сделать хранимую процедуру? #6
Неизвестную переменную надо объявить рядом с "sumSalary".

Вообще смысл моего предложения сводился к тому, чтобы при вставке сразу получить
идентификатор только-что добавленного конракта. У вас до этого использовался повторный запрос. Зачем??
Также обратите внимание на значение "DEFAULT???", вопросики точно надо убрать.
Я так написал, потому что для меня не было достаточно информации, чтобы поставить точное значение.
Возможно DEFAULT надо будет заменить на что-то, либо совсем убрать и убрать тогда поле "ContractId" из INSERT,
если оно у вас получает значение из последовательности (автоинкремент).
Trigger_name
0 / 0 / 0
Регистрация: 11.11.2016
Сообщений: 28
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
544 / 480 / 138
Регистрация: 02.09.2012
Сообщений: 1,412
11.12.2016, 03:28     Как сделать хранимую процедуру? #8
Тогда попробуйте DEFAULT убрать.
Trigger_name
0 / 0 / 0
Регистрация: 11.11.2016
Сообщений: 28
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
544 / 480 / 138
Регистрация: 02.09.2012
Сообщений: 1,412
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")
);
MoreAnswers
Эксперт
37091 / 29110 / 5898
Регистрация: 17.06.2006
Сообщений: 43,301
13.12.2016, 19:58     Как сделать хранимую процедуру?
Еще ссылки по теме:

Как view запихнуть в хранимую процедуру? - Firebird/InterBase
как veiw запихнуть в процедуру? версия интерБейс 6 Код еррорит на CREATE VIEW Create PROCEDURE "NAKLADNAYA_POSTAVKA_1" ( ...

Как правильно вызвать хранимую процедуру - C#
Кто знает как корректно вызвать хранимую процедуру в коде C#???

Как вызвать хранимую процедуру из ASP - C# ASP.NET
Здравствуйте! Не подскажете как правильно вызвать хранимую процедуру, я вот так вызываю,но он ошибку вызывает: var...

Как из VB вызвать Хранимую функцию, а не процедуру? - Visual Basic
Как из VB вызвать Хранимую функцию, а не процедуру


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

Или воспользуйтесь поиском по форуму:
Trigger_name
0 / 0 / 0
Регистрация: 11.11.2016
Сообщений: 28
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     Как сделать хранимую процедуру?
Ответ Создать тему
Опции темы

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